MySQL的奇巧淫技
|
《MySQL的奇巧淫技》要点：
mysql是目前最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一.在给大家分享之前,先介绍下 小编组织的一个学习交流企鹅群：526929231 有什么不懂的问题,都可以在群里问,学习气氛很好,众多大神都很热情.群文件里面也有各种全面的python材料,以及自动化运维学习材料,是一个非常适合学习的地方,小编期待大家的加入,大家一起学习,共同成长! mysql中的根本逻辑对象 mysql有这么几种工具 mysqld--->库---->表---->记录(由行和列构成)一条记录中的一列叫做字段 什么是关系型数据库 表与表产生关系,叫关系型 部分表(部分ID、部分名称) 雇员表(员工ID、员工姓名、部分ID) 可以通过上面的例子知道在雇员表中写入了部分id这个字段在部分表中也存在,2个表就通过部分id这个字段联系起来了 操作数据库的4种方式(使用SQL语句):
常见的SQL尺度mysql=国际尺度化＋mysql官方的自定义尺度 pl/sql=国际尺度化＋oracle官方的自定义尺度 Tsql=国际尺度化＋microsoft官方的自定义尺度 总结: 所有大家在学习一些SQL语句的时候会出现 我虽然没有学过这个数据库,但是它的语法我基本也能看的懂,便是因为SQL有国际标准化的语句 常用的SQL语句
mysql> create database ceshi1; #创建数据库ceshi1Query OK,1 row affected (0.00 sec)mysql> show create database ceshi1; 查看数据库是如何建立的+----------+-------------------------------------------------------------------+| Database | Create Database |+----------+-------------------------------------------------------------------+| ceshi1 | CREATE DATABASE `ceshi1` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+-------------------------------------------------------------------+1 row in set (0.00 sec)mysql> create database ceshi2 default charset utf8; 设置默认的字符编码集为utf-8Query OK,1 row affected (0.00 sec)mysql> create database IF NOT EXISTS ceshi1 default charset utf8; #创建一个数据库如果不存在就创建,如果存在就告警Query OK,1 row affected,1 warning (0.00 sec)mysql> show warnings; #查看最后一次的waring的信息+-------+------+-------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------+| Note | 1007 | Can't create database 'ceshi1'; database exists |+-------+------+-------------------------------------------------+1 row in set (0.00 sec) 创建表 二维表(表由行和列组成,表必需存在于某一个库当中)2个变量 varchar ----不定长 好比我设一个字段为50个字符 那么我只占用了30个字符 那么数据库给我计算的时候只给我算30个字符 char ---定长 好比我设置一个字段为50个字符,我只占用了10个字符,但是数据库还是会给我算50个字符的空间的占用 mysql> use ceshi1; #进入ceshi1库Database changedmysql> create table ceshi1.t1(id int,name varchar(50),salary decimal(10,2),dept varchar(50));Query OK,0 rows affected (0.02 sec)解析: 在ceshi1库中创建t1表 并创建4个字段1. 第一个字段 id 设置int类型2. 第二个字段 name 指定字符长度为503. 第三个字段 salary 指定保存小数点2位4. 第四个字段 dept 执行字符长度为50mysql> DESCRIBE t1; #查看表结构 可以看到有4个字段+--------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(50) | YES | | NULL | || salary | decimal(10,2) | YES | | NULL | || dept | varchar(50) | YES | | NULL | |+--------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> select * from ceshi1.t1; 查询表所有内容Empty set (0.00 sec)mysql> insert into db01.t1 set id=1,name='zhang3',salary=5000,dept='生产部'; 插入数据Query OK,1 warning (0.07 sec)mysql> select * from ceshi1.t1; #可以看到数据已经插入进去了+------+--------+---------+-----------+| id | name | salary | dept |+------+--------+---------+-----------+| 1 | zhang3 | 5000.00 | 生产部 |+------+--------+---------+-----------+1 row in set (0.00 sec)mysql> show create table ceshi1.t1; #可以查看表示任何创建的| Table | Create Table | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`dept` varchar(50) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 可以查出使用神没存储引擎以及语言编码1 row in set (0.00 sec)mysql> create table ceshi1.t2 ( 列数多的情况可以使用一下方式创建表 -> `id` int(11) DEFAULT NULL,-> `name` varchar(50) DEFAULT NULL,-> `salary` decimal(10,-> `dept` varchar(50) DEFAULT NULL -> );Query OK,0 rows affected (0.11 sec)
创建一个用户 user01用户名 123暗码mysql> create user user01@'localhost' identified by '123';Query OK,0 rows affected (0.00 sec)mysql> set password for user01@'localhost'=password('123'); --设置用户的暗码mysql> create user u01@'localhost' ; --创建一个用户mysql> select user,host,password from mysql.user; －－查询用户是否创建成功| root | localhost | *425F1EBD8227A2B1E01C475B523E27A592CFF59A || root | vagrant-centos65.vagrantup.com | || root | 127.0.0.1 | || | localhost | || | vagrant-centos65.vagrantup.com | || user01 | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257mysql> show grants; #查看用户权限| Grants for root@localhost || GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*425F1EBD8227A2B1E01C475B523E27A592CFF59A' WITH GRANT OPTION |1 row in set (0.00 sec)drop --删除数据库对象(表/库/用户) 删除表/库 mysql> use ceshi1mysql> show tables;mysql> drop table t1; --删除表mysql> drop table t2; mysql> use ceshi1 --验证表是否删除胜利mysql> show tablesmysql> drop database ceshi1; --删除库Query OK,0 rows affected (0.00 sec)mysql> drop database ceshi1 ;mysql> show databases; --验证库是否删除胜利
mysql> drop user user01@'localhost'; --删除用户Query OK,0 rows affected (0.00 sec)mysql> select user from mysql.user where user='user01'; --验证用户是否删除胜利mysql> drop user ''@'192.168.1.1'; 删除一个匿名用户
mysql> ALTER DATABASE db01 DEFAULT CHARACTER SET latin1; －－修改数据库语言编码Query OK,1 row affected (0.05 sec)mysql> show create database db01; －－验证修改是否胜利 修改表的属性：mysql> create table t2(name varchar(50));mysql> ALTER TABLE t2 ADD id int FIRST; --增加一列成为第一列mysql> ALTER TABLE t2 add id2 int AFTER id; --在id后面增加一列叫id2mysql> alter table t2 drop id2; --删除id2这个列mysql> alter table t2 change id ID bigint; --修改列名和数据类型mysql> alter table t2 modify ID int; --修改列的数据类型mysql> alter table t2 rename t20; --重命名表mysql>show engines; --查看数据库有哪些存储引擎mysql> alter table t20 engine MyISAM; --修改表的存储引擎mysql> show create table t20; --查看修改存储引擎是否胜利mysql> alter table t20 DEFAULT CHARSET=utf8; －－修改表的语言编码
mysql> insert into ceshi1.t1 set id=1,name='zhang3'; --向指定列插入数据mysql> insert into ceshi1.t1 values(2,'li4'),(3,'wang5'),(4,'zhao6'); --插入多条记录mysql> insert into ceshi1.t1(NAME) values('test01'),('test02'),('test03'); --不写列名,表现按顺序往所有的列插入数据mysql> select * from ceshi1.t1 where id<5; 查出前4张表mysql> insert into ceshi1.t2 select * from ceshi1.t1 where id<5; 查出前4张表,插入到当前表(注意表结构要一致)mysql> rename tables t1 to t01;
mysql> update db01.t1 set id=5 where NAME='test01';mysql> update db01.t1 set id=6 where NAME='test02';mysql> update db01.t1 set id=7 where NAME='test03';
mysql> delete from mysql.user where user=''; －－删除mysql中的匿名用户mysql> flush privileges;mysql> create table t3 like t1; 复制表布局不复制数据mysql> insert into t3 select * from t1;mysql> delete from t3; --删除整个表,一行一行删除,所有的操作都会被记录至事务日志中mysql> insert into t3 select * from t1;mysql> delete from t3 where id=1; --指定条件删除mysql> truncate from t2 where id=2; --报错
通配符： % 匹配0个或任意多个字符 _ 匹配一个字符 ＝ 精确匹配 like 模糊匹配 regex(^ . .* .....) 使用正则表达式来匹配排序： order by 排序 asc 升序排列结果 desc 降序排列结果 group by 聚合 distinct 去除重复的行 mysql> use ceshi2mysql> create table ceshi2.t1(id int,math tinyint,english tinyint);mysql> insert into ceshi2.t1 set id=1,name='zhangsan',math=55,english=66;mysql> insert into ceshi2.t1 set id=2,name='lisi',math=66,english=77;mysql> insert into ceshi2.t1 set id=3,name='wangwu',math=65,english=30;mysql> insert into ceshi2.t1 set id=2,name='li04',math=88,english=99;mysql> insert into ceshi2.t1 set id=3,name='wang5',math=75,english=73;mysql> insert into ceshi2.t1 set id=4,name='zhao6',english=73;mysql> insert into ceshi2.t1 set id=5,name='liu3',math=85,english=43;mysql> select * from ceshi2.t1; --查询表中所有的列对应的值(全表扫描)mysql> select id,name from t1; --查询表中指定列mysql> select host,password,user from mysql.user; --查询表中指定列mysql> select host as '主机名',password as '暗码',user as '用户名' from mysql.user; --给列取别名,增加可读性mysql> select * from t1 where name='i';mysql> select * from t1 where name like 'i';mysql> select * from t1 where name like '%i%'; --模糊匹配mysql> select * from t1 where name like '____';mysql> select * from t1 where id=2;mysql> select * from t1 where name='wang5'; --精确匹配mysql> select * from t1 where english < 60;mysql> select * from t1 where name regexp '.*[0-9]?.*' ; --支持正则表达式mysql> select * from t1 where name regexp '.*[0-9]+.*' ; 任意数字一次或一次以上mysql> select * from t1 where name regexp '.*[0-9]{2}.*' ;mysql> select user,host from mysql.user where host regexp '^l'; --支持正则表达式mysql> select user,host from mysql.user where host regexp '([0-9]{1,3}.){3}([0-9]{1,3})';排序mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id asc; 升序mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id desc; 降序 去除重复行 distinctmysql> select distinct id from t1 ;聚合 group by 聚合以第一个为标准mysql> select * from t1 group by id;mysql> select * from t1 group by id having id <= 2;gruop by 不能用where语句 用having语句
mysql> select * from t1 limit 10; --显示前10行mysql> select * from t1 limit 10,10; --显示11至20行mysql> select * from t1 limit 1; 显示第1行mysql> select * from t1 limit 0,2; 显示第1,2行mysql> select * from t1 limit 2,2; 显示3,4行起始地位,偏移量 第二行的下2行mysql> select * from t1 limit 4,2; 显示5,6行mysql> select * from t1 limit 6,2; 显示第七行 mysql> select * from t1 order by english desc limit 3; 显示英语前三名 mysql> select name,(math+english) as sum from t1 order by sum desc; 显示总分,以降序分列 mysql> select name,(math+english) as sum from t1 order by sum desc limit 1; 显示总分第一名 mysql> select name,max((math+english)) from t1; 显示总分第一名 mysql> select name,max(math+english) from t1 order by (math+english) desc; 显示总分第一名 mysql> select name,english from t1; mysql> select max(english) from t1; 显示英语的最高分 mysql> select min(english) from t1; 显示英语的最低分 mysql> select max(english) from t1; 显示英语的最高分 mysql> select min(english) from t1; 显示英语的最低分 mysql> select name,english from t1 where english in (select max(english) from t1); 子查询 显示英语的最高分 * and or not 逻辑运算 1.and mysql> select * from t1 where math >= 60 and english >= 60; 2.or mysql> select * from t1 where math >= 60 or english >= 60; 此中一科及格 3.not mysql> select * from t1 where math >= 60 and not english >= 60; +------+--------+------+---------+ | id | name | math | english | +------+--------+------+---------+ | 3 | wangwu | 65 | 30 | | 5 | liu3 | 85 | 43 | +------+--------+------+---------+ 2 rows in set (0.00 sec) * mysql常用函数 sum() avg() max() min() count() mysql> select name,sum(math),sum(english) from t1; +----------+-----------+--------------+ | name | sum(math) | sum(english) | +----------+-----------+--------------+ | zhangsan | 215 | 252 | +----------+-----------+--------------+ 1 row in set (0.00 sec) mysql> select name,avg(math),avg(english) from t1; +----------+-----------+--------------+ | name | avg(math) | avg(english) | +----------+-----------+--------------+ | zhangsan | 53.7500 | 63.0000 | +----------+-----------+--------------+ 1 row in set (0.00 sec) mysql> select name,max(english) from t1; +----------+--------------+ | name | max(english) | +----------+--------------+ | zhangsan | 90 | +----------+--------------+ 1 row in set (0.00 sec) mysql> select name,min(english) from t1; +----------+--------------+ | name | min(english) | +----------+--------------+ | zhangsan | 33 | +----------+--------------+ 1 row in set (0.00 sec) mysql> select count() from t1; +----------+ | count() | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> select max(english) - min(math) from t1; --英语的最高分与数学最低的差距 +--------------------------+ | max(english) - min(math) | +--------------------------+ | 50 | +--------------------------+ 1 row in set (0.00 sec) mysql> select * from t1 order by english desc limit 3; +------+----------+------+---------+ | id | name | math | english | +------+----------+------+---------+ | 1 | zhangsan | 70 | 90 | | 4 | lisi02 | 55 | 69 | | 2 | lisi | 50 | 60 | +------+----------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from t1 order by english asc limit 1,2; 显示第二名和第三名 +------+--------+------+---------+ | id | name | math | english | +------+--------+------+---------+ | 4 | lisi02 | 55 | 69 | | 2 | lisi | 50 | 60 | +------+--------+------+---------+ 2 rows in set (0.00 sec) mysql> select * from ceshi1.t1 where math >= 60 or english >=60 ; +------+----------+------+---------+ | id | name | math | english | +------+----------+------+---------+ | 1 | zhangsan | 70 | 90 | | 2 | lisi | 50 | 60 | | 4 | lisi02 | 55 | 69 | +------+----------+------+---------+ 3 rows in set (0.00 sec) * 复制表布局 表布局的复制 mysql> create table t3 like t1; --复制表布局 mysql> create database db03; mysql> use db03 mysql> create table t1(id int,name varchar(50)); mysql> create table t2(id int,socre int); mysql> insert into t1 set id=1,name='lee'; mysql> insert into t1 set id=2,name='zhang'; mysql> insert into t1 set id=4,name='wang'; mysql> insert into t2 set id=1,socre='90'; mysql> insert into t2 set id=2,socre='100'; mysql> insert into t2 set id=3,socre='90'; mysql> select * from t1; * 列类型 整数 create table t1(id tinyint(2),name varchar(50)) · TINYINT[(M)] [UNSIGNED] [ZEROFILL] [not null] [comment] 很小的整数.带符号的规模是-128到127.无符号的规模是0到255. · MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等年夜小的整数.带符号的范围是-8388608到8388607.无符号的范围是0到16777215. · INT[(M)] [UNSIGNED] [ZEROFILL] 普通年夜小的整数.带符号的范围是-2147483648到2147483647.无符号的范围是0到4294967295. · INTEGER[(M)] [UNSIGNED] [ZEROFILL] 这是INT的同义词. · BIGINT[(M)] [UNSIGNED] [ZEROFILL] 年夜整数.带符号的范围是-9223372036854775808到9223372036854775807.无符号的范围是0到18446744073709551615. mysql> use db01 mysql> show tables; +----------------+ | Tables_in_db01 | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) mysql> create table t4(id int,name char(50),2)); mysql> desc t4; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(50) | YES | | NULL | | | salary | decimal(10,2) | YES | | NULL | | +--------+---------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t4 set id='test01',name='asdasdad',salary='dadadadasd' ; Query OK,2 warnings (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: 'test01' for column 'id' at row 1 | | Warning | 1366 | Incorrect decimal value: 'dadadadasd' for column 'salary' at row 1 | +---------+------+--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from t4; +------+----------+--------+ | id | name | salary | +------+----------+--------+ | 0 | asdasdad | 0.00 | +------+----------+--------+ 1 row in set (0.00 sec) ============= mysql> create table t5(id tinyint); Query OK,0 rows affected (0.01 sec) mysql> insert into t5 set id=-10; Query OK,1 row affected (0.00 sec) mysql> select * from t5; +------+ | id | +------+ | -10 | +------+ 1 row in set (0.00 sec) mysql> insert into t5 set id=-300; Query OK,1 warning (0.01 sec) mysql> select * from t5; +------+ | id | +------+ | -10 | | -128 | +------+ 2 rows in set (0.00 sec) mysql> alter table t5 modify id tinyint unsigned; mysql> insert t4 set id=2500; Query OK,1 warning (0.00 sec) mysql> select * from t5; +------+ | id | +------+ | 0 | | 0 | | 250 | | 255 | +------+ 4 rows in set (0.00 sec) 浮点数 decimal指定小数点的位数 mysql> alter table t5 add salary decimal(7,2) unsigned after id; mysql> update t5 set salary=1000000 where id=255; mysql> select * from t5; +------+-----------+ | id | salary | +------+-----------+ | 0 | NULL | | 0 | NULL | | 250 | NULL | | 255 | 999999.99 | 日期和光阴类型概述 mysql> alter table t5 add date date; mysql> alter table t5 add datetime datetime; mysql> alter table t5 add time TIMESTAMP; mysql> insert into t5(id,salary,date,datetime) values(1,10000,'2011-09-01','2011-09-01 11:28:01'); mysql> select * from t5; +------+----------+---------------------+------------+---------------------+ | id | salary | time | date | datetime | +------+----------+---------------------+------------+---------------------+ | 1 | 10000.00 | 2011-09-01 11:28:27 | 2011-09-01 | 2011-09-01 11:28:01 | +------+----------+---------------------+------------+---------------------+ 1 row in set (0.00 sec) 写在末了: 编程之家PHP培训学院每天发布《MySQL的奇巧淫技》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
