mysql 综合
一、库操作
二、表操作
1.存储引擎介绍
show engines; 查看数据库支持的引擎
MySQL 使用 InnoDB
指定表类型/存储引擎
create table t1(id int)engine = innodb;i
create table t2(id int)engine = memory;
create table t3(id int)engine = blackhole;
create table t4(id int)engine = myisam;
- innodb 对应 t1 ,frm:表结构 ibd:innodb缩写 是t1 的数据文件
- memory 对应 t2, 数据是放在内存里的，不对存到硬盘里，所以只有一个文件 t2.frm
- blackhole 对应 t3 ,黑洞，也是只有 t3.frm ，即表结构文件， 数据都进去就没了
- myisam 对应 t4, t4.frm:表结构文件 、 t4.MYD:数据文件 、 t4.MYI :索引文件
2. 增删查改
show create table t1; 查看表结构信息
-- 查看MySQL 的用户表 信息
show create table mysql.user\G;
-- \G ,可以让显示的内容按行显示 ，有利于阅读
mysql> create database mygd_test01 charset utf8;
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | publiccms |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
-- 复制表, 把 查询结果 复制到新创建的表里
mysql> create table t1 select host,user from mysql.user;
-- 查询 表 的字段列表信息
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(32) | NO | | | |
+-------+----------+------+-----+---------+-------+
mysql> select * from t1; --看一下，表已经复制过来了
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | publiccms |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
-- 可以加一个不满足的条件 ，这样可以到复制表结构的目的
mysql> create table t2 select host,user from mysql.user where 1>2;
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(32) | NO | | | |
+-------+----------+------+-----+---------+-------+
-- 只想拷贝表结构,不拷贝数据 的另一种方式
mysql> create table t3 like mysql.user;
3.数据类型
(1)数值类型
整数类型
mysql> create table t1(x tinyint);
insert into t1 valuse(-1);
mysql> select * from t1;
+------+
| x |
+------+
| -1 |
+------+
-- 插入-129，超出范围了
mysql> insert into t1 values(-129);
ERROR 1264 (22003): Out of range value for column 'x' at row 1
-- 创建一个无符号的
mysql> create table t2(x tinyint unsigned);
-- 插入超出无符号范围的值，有时候超范围的数是可以插入的，插入256，会变为255，可能还版本的原因
mysql> insert into t2 values(-1);
ERROR 1264 (22003): Out of range value for column 'x' at row 1
mysql> insert into t2 values(256);
ERROR 1264 (22003): Out of range value for column 'x' at row 1
-- 创建表，指定字段长度
mysql> create table t3(x tinyint(1) unsigned);
--看一下创建的表机构，没问题
mysql> desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| x | tinyint(1) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
--然后我们插入一个超出范围的很大的数
--创建表，如上图，int字节大小为 4个字节，宽度是没有必要加的，如上图，int 的数据长度是固定好的
mysql> create table t3(id int(1));
-- 另外，我们在设计字段的时候写的 int(5) 这个不是存储宽度，而是显示宽度
-- 创建表，无符号， zerofill 表示用 0 填充
mysql> create table t4(uid int(5) unsigned zerofill);
mysql> insert into t4 values(6);
mysql> select * from t4;
+-------+
| uid |
+-------+
| 00006 |
+-------+
-- int 无符号，默认显示宽度为 10，如上图,int无符号 范围是(0,4294 967 295) ,正好10位，
-- int 有符号,默认宽度就是 11，因为要加一个 负号
mysql> create table t5(id int unsigned);
mysql> desc t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
浮点型
定点数类型 DEC等同于DECIMAL
浮点类型：FLOAT DOUBLE
======================================
#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
无符号：
1.175494351E-38 to 3.402823466E+38
精确度：
**** 随着小数的增多，精度变得不准确 ****
======================================
#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
无符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
精确度：
****随着小数的增多，精度比float要高，但也会变得不准确 ****
======================================
decimal[(m[,d])] [unsigned] [zerofill]
定义：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
精确度：
**** 随着小数的增多，精度始终准确 ****
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
-- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
-- 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
--精确度： **** 随着小数的增多，精度变得不准确 ****
-- 255 整数位数，30小数位数
mysql> create table t8(x float(255,30));
mysql> create table t9(x double(255,30));
mysql> create table t10(x decimal(255,30));
ERROR 1426 (42000): Too-big precision 255 specified for 'x'. Maximum is 65.
mysql> create table t10(x decimal(65,30));
-- 插入数据
mysql> insert into t8 values(1.111111111111111111111111111111);
mysql> insert into t9 values(1.111111111111111111111111111111);
mysql> insert into t10 values(1.111111111111111111111111111111);
--查询结果
mysql> select * from t8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
mysql> select * from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
mysql> select * from t10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
(2)日期类型
-- 创建表
mysql> create table student(id int,name char(6),born_year year,birth_date date,class_time time,reg_time datetime);
-- 插入数据
mysql> insert into student values(1,'gudon',now(),now(),now(),now());
mysql> select * from student;
+------+-------+-----------+------------+------------+---------------------+
| id | name | born_year | birth_date | class_time | reg_time |
+------+-------+-----------+------------+------------+---------------------+
| 1 | gudon | 2018 | 2018-09-25 | 10:43:38 | 2018-09-25 10:43:38 |
+------+-------+-----------+------------+------------+---------------------+
-- 按时间格式插入数据
mysql> insert into student values(3,'Astro',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");
mysql> select * from student;
+------+-------+-----------+------------+------------+---------------------+
| id | name | born_year | birth_date | class_time | reg_time |
+------+-------+-----------+------------+------------+---------------------+
| 1 | gudon | 2018 | 2018-09-25 | 10:43:38 | 2018-09-25 10:43:38 |
| 3 | Astro | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 |
+------+-------+-----------+------------+------------+---------------------+
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
还是使用datatime 较多，时间范围比较大。
时间范围：
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
(3).字符类型
char :定长
varchar: 变长
-- #宽度指的是 字符的个数
mysql> create table t13(name char(5));
mysql> create table t14(name varchar(5));
mysql> insert into t13 values('孟浩 '); --后面加了3个空格
mysql> select char_length(name) from t13; --存了了5个，查询结果是2个
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
-- 即，存的时候，按5个存的，取得时候，把空格给去掉了
-- 设置mysql模式，填充字符到完整的长度
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
-- 再查询一下试试
mysql> select char_length(name) from t13;
+-------------------+
| char_length(name) |
+-------------------+
| 5 |
+-------------------+
-- 数据库里存的是 孟浩 + 3个空格，但是我们通过如下的方式，不带空格也可以查询到结果，即MySQL在查询的时候，将默认的空格给去掉了。
mysql> select * from t13 where name = '孟浩';
+---------+
| name |
+---------+
| 孟浩 |
+---------+
--但是只针对末尾的空格，字符前面有空格是没法查的，另外，如果条件查询是like,不会去掉末尾的空格去查询
mysql> select * from t13 where name = ' 孟浩';
Empty set (0.00 sec)
#char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
存储char类型的值时，会往右填充空格来满足长度
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
#varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
name char(5)
jack |rose |sky |
name varchar(5)
1byte+jack|1byte+rose|1byte+sky|
varchar, 字符串前面会有一个byte 来存目标数据 的长度，一个byte 是255，如果目标数据的长度超过 255，记录长度 就用两个byte, 两个byte 还 65535，MySQL要求规定这是最大的地址长度
- 更多的使用char类型，因为存取速度快，而且存储空间现在不是太大问题了
- 如果是数据查询频率不高，可以使用varchar 类型
- 在同一张表里，最好不要 char 和 varchar 混合使用
(4) 枚举类型和集合类型
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
mysql> create table consumer(id int,name char(16),sex enum('male','female','other'),level enum('A','B','C'),hobbies set('game','music','read','run'));
mysql> insert into consumer values(1,'astro','male','A','music');
mysql> insert into consumer values(1,'astro','male','W','music'); --传入不存在的值
mysql> insert into consumer values(1,'Nurato','other','A','read,run');--传入多个值
4.完整性约束
mysql> create table t16(id int, name char(6), sex enum('male','famale') not null default 'male');
mysql> desc t16;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(6) | YES | | NULL | |
| sex | enum('male','famale') | NO | | male | |
+-------+-----------------------+------+-----+---------+-------+
mysql> insert into t16 (id,name) values(1,'zs');
mysql> select * from t16;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | zs | male |
+------+--------+------+
unique
单列唯一：
--方式一：
mysql> create table department(id int,name char(10) unique);
--插入两条 name 一样记录，会报错
mysql> insert into department values(1,'zs'),(2,'zs');
ERROR 1062 (23000): Duplicate entry 'zs ' for key 'name'
-- 方式二
create table department(id int, name char(10),unique(id),unique(name))
联合唯一：
-- ip port 联合唯一
mysql> create table services(id int,ip char(15),port int,unique(id),unique(ip,port));
mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| ip | char(15) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into services values(1,'192.168.10.1',80),(2,'192.168.10.1',80);
ERROR 1062 (23000): Duplicate entry '192.168.10.1 -80' for key 'ip'
mysql> insert into services values(1,'192.168.10.1',80),(2,'192.168.10.1',82);
mysql> select * from services;
+------+-----------------+------+
| id | ip | port |
+------+-----------------+------+
| 1 | 192.168.10.1 | 80 |
| 2 | 192.168.10.1 | 82 |
+------+-----------------+------+
primary key (not null unique)
对于 innodb 存储引擎来说，一张表里，必须要有一个主键
单列主键：
mysql> create table t17(id int primary key,name char(16));
mysql> desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | | --主键 PRI
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t17 values(1,'gd'),(2,'astro');
mysql> insert into t17 values(2,'zs');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' --报错
mysql> select * from t17;
+----+------------------+
| id | name |
+----+------------------+
| 0 | jack |
| 1 | gd |
| 2 | astro |
+----+------------------+
-- 如果不指定主键，MySQL会默认找一个
-- not null unique 就是主键
mysql> create table t18(id int not null unique,name char(16));
mysql> desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
复合主键:
mysql> create table t19(ip char(15),port int,primary key(ip,port));
mysql> desc t19;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip | char(15) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
-- 插入相同的数据，报错
mysql> insert into t19 values('1.1.1.1',80),('1.1.1.1',80);
ERROR 1062 (23000): Duplicate entry '1.1.1.1 -80' for key 'PRIMARY'
-- 插入不同的数据
mysql> insert into t19 values('1.1.1.1',80),('1.1.1.1',81);
mysql> select * from t19;
+-----------------+------+
| ip | port |
+-----------------+------+
| 1.1.1.1 | 80 |
| 1.1.1.1 | 81 |
+-----------------+------+
auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束
mysql> create table t20(id int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
ined as a key
mysql> create table t20(id int primary key auto_increment,name char(16));
mysql> desc t20;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
mysql> insert into t20 (name) values('astro'),('nurato');
mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| 1 | astro |
| 2 | nurato |
+----+------------------+
-- id不用自己传，但是也可以传的，如果现在新增数据 id 为7，然后再插入2条数据(不传入id)，那这两条数据的id会从7开始增加，为 8 和 9
-- 自动增长的步长也可以修改
mysql> show variables like 'auto_inc%' -- %代表任意内容
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | --表示步长
| auto_increment_offset | 1 | -- 表示起始位置，从1开始
+--------------------------+-------+
-- 设置步长
mysql> set session auto_increment_increment = 5; --当前会话级别
mysql> set global auto_increment_increment = 5; --全局级别，需要关闭当前会话重新开启后生效
-- 设置偏移量 [起始偏移量的设置要小于等于步长,否则失效 ]
set global auto_increment_offset = 5；
-- 这种删除方式，会把数据删除，但是删除后重新插入数据，id会沿着之前的id值往后累加
delete from t20;
-- 这种方式，全部清空数据，会把自动增长的也清除掉，下次再新增数据，会从初始值开始累加
truncate t20;
foreign key 用来建立表之间的关系
-- 先建被关联的表，被关联的字段必须是唯一的
create table dep(
id int primary key,
name char(16),
comment char(50)
);
-- 再建关联表
create table emp(
id int primary key,
name char(10),
sex enum('male','female'),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade --这句，可以在删除 dep(被关联表)的时候，关联表里相关的内容也一并被删除
on update cascade -- 使得更新 dep 的时候，emp也会跟更新
);
-- 插入数据
-- 先在被关联表中插入数据
insert into dep values
(1,'IT','技术部门'),
(2,'销售','销售部门'),
(3,'财务','花钱最多部门');
-- 再向关联表插入数据
insert into emp values
(1,'astro','male',1),
(2,'Nurato','male',1),
(3,'jack','female',2),
(4,'zs','male',3),
(5,'sky','female',2);
-- 如果要删除技术部门以及技术部门下的所有人员，要先删除 emp下的人员，再删除 dep下的部门
-- 但是，我们在创建 emp 表的时候 ，foreign key(dep_id) references dep(id) on delete cascade
-- on delete cascade 使得在删除 dep 的时候，可以把 emp下相关的人员也全部删除掉
-- on update cascade 使得，也可以在 dep 更新 dep_id 的时候,自动把 emp 下的相关内容(id)也更新
一般从逻辑意义上设计表之间的关联，不在数据库中真正去使用 外键 做关联，因为外键使得表之间的耦合度变高，在有需要做扩展的时候，可能会有麻烦
表之间的关系：
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
===================== 多对一 =====================
-- 一对多（或多对一）：一个出版社可以出版多本书
-- 关联方式：foreign key
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3);
=====================多对多=====================
--多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
--关联方式：foreign key+一张新的表
-- 三张表：出版社，作者信息，书
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);
-----------------------------------------------------------------------------
-- ======================= 一对一 =========================
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：foreign key+unique
#一定是student来foreign key表customer，这样就保证了：
#1 学生一定是一个客户，
#2 客户不一定是学生，但有可能成为一个学生
create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;
#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
三、数据操作
1.数据的增删改
DML
INSERT
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
UPDATE
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
DELETE
语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;
2.单表查询
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
-- 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','总统',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
准备表和记录
---------------------------------------------------------------
-- 定义显示格式
mysql> select concat('姓名:',name, '年薪:',salary*12) as TNT from employee;
+-------------------------------+
| TNT |
+-------------------------------+
| 姓名:egon年薪:87603.96 |
| 姓名:alex年薪:12000003.72 |
| 姓名:wupeiqi年薪:99600.00 |
| 姓名:yuanhao年薪:42000.00 |
| 姓名:liwenzhou年薪:25200.00 |
| 姓名:jingliyang年薪:108000.00 |
| 姓名:jinxin年薪:360000.00 |
| 姓名:成龙年薪:120000.00 |
| 姓名:歪歪年薪:36001.56 |
| 姓名:丫丫年薪:24004.20 |
| 姓名:丁丁年薪:12004.44 |
| 姓名:星星年薪:36003.48 |
| 姓名:格格年薪:48003.96 |
| 姓名:张野年薪:120001.56 |
| 姓名:程咬金年薪:240000.00 |
| 姓名:程咬银年薪:228000.00 |
| 姓名:程咬铜年薪:216000.00 |
| 姓名:程咬铁年薪:204000.00 |
+-------------------------------+
-- CONCAT_WS() 第一个参数为分隔符
mysql> select concat_ws('---',name,salary) as TNT from employee;
+----------------------+
| TNT |
+----------------------+
| egon---7300.33 |
| alex---1000000.31 |
| wupeiqi---8300.00 |
| yuanhao---3500.00 |
| liwenzhou---2100.00 |
| jingliyang---9000.00 |
| jinxin---30000.00 |
| 成龙---10000.00 |
| 歪歪---3000.13 |
| 丫丫---2000.35 |
| 丁丁---1000.37 |
| 星星---3000.29 |
| 格格---4000.33 |
| 张野---10000.13 |
| 程咬金---20000.00 |
| 程咬银---19000.00 |
| 程咬铜---18000.00 |
| 程咬铁---17000.00 |
+----------------------+
-- 下面两句是一样的结果
mysql> select * from employee where salary >=20000 and salary<=30000;
mysql> select * from employee where salary between 20000 and 30000;
-- 模糊查询
mysql> select name from employee where name like 'j%'; -- j + 后面任意多个字符
mysql> select name from employee where name like 'jin___'; -- 一个下划线代表一个字符
--group by
-- 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
-- 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
mysql> select post,count(name) from employee group by post;
-- 没有 group by ，默认整体算一组
mysql> select max(salary) from employee;
-- group_concat
mysql> select post,group_concat(name) from employee group by post;
+-----------+-------------------------------------------------------+
| post | group_concat(name) |
+-----------+-------------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 总统 | egon |
+-----------+-------------------------------------------------------+
having
执行顺序：
执行优先级从高到低：where > group by > having > order by > limit n
- Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select post,group_concat(name),count(id) from employee group by post;
+-----------+-------------------------------------------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------+-------------------------------------------------------+-----------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | 5 |
| sale | 歪歪,丫丫,丁丁,星星,格格 | 5 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | 7 |
| 总统 | egon | 1 |
+-----------+-------------------------------------------------------+-----------+
mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
+------+--------------------+-----------+
| post | group_concat(name) | count(id) |
+------+--------------------+-----------+
| 总统 | egon | 1 |
+------+--------------------+-----------+
order by
asc 升序(默认)
desc 降序
-- 先按age 升序排， 再按id降序排
mysql> select * from employee order by age asc,id desc;
-- 关于执行顺序
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
1.from 库.表
2.where
3.group by
4.having
5.distinct（其他去和函数等）
6.order by
7.limit n
limit
-- 查询 salary 最高的前三个
mysql> select id,name,salary from employee order by salary desc limit 3;
+----+--------+------------+
| id | name | salary |
+----+--------+------------+
| 2 | alex | 1000000.31 |
| 7 | jinxin | 30000.00 |
| 15 | 程咬金 | 20000.00 |
+----+--------+------------+
SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; --从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; --从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
使用正则表达式查询
-- 用正则表达式的规则查询
mysql> select name,age from employee where name regexp '^程'; --以程 开头的
+--------+-----+
| name | age |
+--------+-----+
| 程咬金 | 18 |
| 程咬银 | 18 |
| 程咬铜 | 18 |
| 程咬铁 | 18 |
+--------+-----+
-- 查看所有员工中名字是jin开头，n或者g结果的员工信息
select name,age from employee where name regexp '^jin.*[gn]$';
+------------+-----+
| name | age |
+------------+-----+
| jingliyang | 18 |
| jinxin | 18 |
+------------+-----+
3.多表查询
(1) 连接查询
#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
-- 查询表
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
表department与employee
1 交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
2 内连接：只连接匹配的行
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
3 外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select * from employee left join department on employee.dep_id=department.id;
+----+------------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
+----+------------+--------+------+--------+------+----------+
4 外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select * from employee right join department on employee.dep_id=department.id;
+------+-----------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+------+-----------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-----------+--------+------+--------+------+----------+
5 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
#注意 union与union all的区别：union会去掉相同的纪录
(2)符合条件的连接查询
查询平均年龄大于 30 的部门名称
select avg(employee.age),department.name from employee inner join department on employee.dep_id = department.id
group by department.id
having avg(employee.age) > 30;
+-------------------+----------+
| avg(employee.age) | name |
+-------------------+----------+
| 43.0000 | 人力资源 |
+-------------------+----------+
(3) 子查询
带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select id,name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名
select id,name from department where id not in
(
select distinct dep_id from employee
);
带比较运算符的子查询
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from employee where age > (select avg(age) from employee);
#查询大于部门内平均年龄的员工名、年龄
select emp.name,emp.age from employee emp inner join
(select department.id departmentID,avg(employee.age) avg_age from employee inner join department on department.id=employee.dep_id
group by department.id
) B
on emp.dep_id = B.departmentID
where emp.age > B.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select * from employee
where exists
(select * from department where name = "木叶医疗班");
-- 返回False ，所以查询不到结果
4.mysql 权限管理
权限管理
#1、创建账号
# 本地账号
create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123
# 远程帐号
create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon2 -p123 -h 服务端ip
create user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
create user 'egon3'@'%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
#2、授权
user:*.* -- 全部权限
db:db1.* -- 数据库级别
tables_priv:db1.t1 -- 表级别
columns_priv:id,name -- 字段级别
grant all on *.* to 'egon1'@'localhost'; -- 授权全部权限
grant select on *.* to 'egon1'@'localhost'; -- 授权 查询权限
revoke select on *.* from 'egon1'@'localhost'; -- 删除删除权限
grant select on db1.* to 'egon1'@'localhost';
revoke select on db1.* from 'egon1'@'localhost';
grant select on db1.t2 to 'egon1'@'localhost';
revoke select on db1.t2 from 'egon1'@'localhost';
grant select(id,name),update(age) on db1.t2 to 'egon1'@'localhost'; -- 授权查询 id,name ，更新 age 的权限
-- 可以查询字段操作权限 select * from mysql.columns_priv;
