数据的增删改
一 介绍
MySQL数据操作： DML
========================================================
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
-
- 使用INSERT实现数据的插入
- UPDATE实现数据的更新
- 使用DELETE实现数据的删除
- 使用SELECT查询数据以及。
========================================================
二 插入数据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=’’;练习： 更新MySQL root用户密码为mysql123 删除除从本地登录的root用户以外的所有用户
五 权限管理
#授权表user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段columns_priv #该表放行的权限，针对：某一个字段#按图解释：user：放行db1，db2及其包含的所有db：放行db1，及其db1包含的所有tables_priv:放行db1.table1，及其该表包含的所有columns_prive:放行db1.table1.column1，只放行该字段
#创建用户create user 'egon'@'1.1.1.1' identified by '123';create user 'egon'@'192.168.1.%' identified by '123';create user 'egon'@'%' identified by '123';#授权：对文件夹，对文件，对文件某一字段的权限查看帮助：help grant常用权限有：select,update,alter,deleteall可以代表除了grant之外的所有权限#针对所有库的授权:*.*grant select on *.* to 'egon1'@'localhost' identified by '123'; #只在user表中可以查到egon1用户的select权限被设置为Y#针对某一数据库：db1.*grant select on db1.* to 'egon2'@'%' identified by '123'; #只在db表中可以查到egon2用户的select权限被设置为Y#针对某一个表：db1.t1grant select on db1.t1 to 'egon3'@'%' identified by '123'; #只在tables_priv表中可以查到egon3用户的select权限#针对某一个字段：mysql> select * from t3;+------+-------+------+| id | name | age |+------+-------+------+| 1 | egon1 | 18 || 2 | egon2 | 19 || 3 | egon3 | 29 |+------+-------+------+grant select (id,name),update (age) on db1.t3 to 'egon4'@'localhost' identified by '123'; #可以在tables_priv和columns_priv中看到相应的权限mysql> select * from tables_priv where user='egon4'\G*************************** 1. row *************************** Host: localhost Db: db1 User: egon4 Table_name: t3 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv:Column_priv: Select,Updaterow in set (0.00 sec)mysql> select * from columns_priv where user='egon4'\G*************************** 1. row *************************** Host: localhost Db: db1 User: egon4 Table_name: t3Column_name: id Timestamp: 0000-00-00 00:00:00Column_priv: Select*************************** 2. row *************************** Host: localhost Db: db1 User: egon4 Table_name: t3Column_name: name Timestamp: 0000-00-00 00:00:00Column_priv: Select*************************** 3. row *************************** Host: localhost Db: db1 User: egon4 Table_name: t3Column_name: age Timestamp: 0000-00-00 00:00:00Column_priv: Updaterows in set (0.00 sec)#删除权限revoke select on db1.* to 'alex'@'%';
单表查询
一 单表查询的语法
SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数
二 关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级fromwheregroup byhavingselectdistinctorder bylimit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
三 简单查询
#创建表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 #简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee;#避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee; select concat(' <名字:',name,'> ',' <薪资:',salary,'> ') from employee; select distinct depart_id from employee; select name,salary*12 annual_salary from employee; 四 WHERE约束
where字句中可以使用：
- 比较运算符：><>= <= <> !=
- between 80 and 100 值在10到20之间
- in(80,90,100) 值是10或20或30
- like 'egon%' pattern可以是%或_， %表示任意多字符 _表示一个字符
- 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询 SELECT name FROM employee WHERE post='sale';#2:多条件查询 SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000;#3:关键字BETWEEN AND SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT name,post_comment FROM employee WHERE post_comment IS NULL; SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL; SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null ps： 执行 update employee set post_comment='' where id=2; 再用上条查看，就会有结果了#5:关键字IN集合查询 SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询 通配符’%’ SELECT * FROM employee WHERE name LIKE 'eg%'; 通配符’_’ SELECT * FROM employee WHERE name LIKE 'al__';
1. 查看岗位是teacher的员工姓名、年龄2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资4. 查看岗位描述不为NULL的员工信息5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪select name,age from employee where post = 'teacher';select name,age from employee where post='teacher' and age > 30; select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;select * from employee where post_comment is not null;select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);select name,salary*12 from employee where post='teacher' and name like 'jin%';
五 分组查询:GROUP BY
一 什么是分组？为什么要分组？
1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行等。
3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
二 ONLY_FULL_GROUP_BY
查看MySQL 默认的sql_mode如下
mysql> select @@global.sql_mode;
注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
mysql> select @@global.sql_mode;+-------------------+| @@global.sql_mode |+-------------------+| |+-------------------+row in set (0.00 sec)mysql> select * from emp group by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 || 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 || 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+rows in set (0.00 sec)#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的mysql> set global sql_mode='ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)mysql> quit #设置成功后，一定要退出，然后重新登录方可生效Byemysql> use db1;Database changedmysql> select * from emp group by post; #报错ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BYmysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数+----------------------------+-----------+| post | count(id) |+----------------------------+-----------+| operation | 5 || sale | 5 || teacher | 7 || 老男孩驻沙河办事处外交大使 | 1 |+----------------------------+-----------+rows in set (0.00 sec)
三 GROUP BY
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成远名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人。
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
四 聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;
五 小练习：
1. 查询岗位名以及岗位包含的所有员工名字2. 查询岗位名以及各岗位内包含的员工个数3. 查询公司内男员工和女员工的个数4. 查询岗位名以及各岗位的平均薪资5. 查询岗位名以及各岗位的最高薪资6. 查询岗位名以及各岗位的最低薪资7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资#题1：分组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 |+-----------------------------------------+---------------------------------------------------------+#题目2：mysql> select post,count(id) from employee group by post;+-----------------------------------------+-----------+| post | count(id) |+-----------------------------------------+-----------+| operation | 5 || sale | 5 || teacher | 7 || 老男孩驻沙河办事处外交大使 | 1 |+-----------------------------------------+-----------+#题目3：mysql> select sex,count(id) from employee group by sex;+--------+-----------+| sex | count(id) |+--------+-----------+| male | 10 || female | 8 |+--------+-----------+#题目4：mysql> select post,avg(salary) from employee group by post;+-----------------------------------------+---------------+| post | avg(salary) |+-----------------------------------------+---------------+| operation | 16800.026000 || sale | 2600.294000 || teacher | 151842.901429 || 老男孩驻沙河办事处外交大使 | 7300.330000 |+-----------------------------------------+---------------+#题目5mysql> select post,max(salary) from employee group by post;+-----------------------------------------+-------------+| post | max(salary) |+-----------------------------------------+-------------+| operation | 20000.00 || sale | 4000.33 || teacher | 1000000.31 || 老男孩驻沙河办事处外交大使 | 7300.33 |+-----------------------------------------+-------------+#题目6mysql> select post,min(salary) from employee group by post;+-----------------------------------------+-------------+| post | min(salary) |+-----------------------------------------+-------------+| operation | 10000.13 || sale | 1000.37 || teacher | 2100.00 || 老男孩驻沙河办事处外交大使 | 7300.33 |+-----------------------------------------+-------------+#题目七mysql> select sex,avg(salary) from employee group by sex;+--------+---------------+| sex | avg(salary) |+--------+---------------+| male | 110920.077000 || female | 7250.183750 |+--------+---------------+
六 HAVING过滤
HAVING与WHERE不一样的地方在于：
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数。
mysql> select * from emp where salary > 100000;+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+row in set (0.00 sec)mysql> select * from emp having salary > 100000;ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clausemysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;+-----------+-------------------------------------------------------+| post | group_concat(name) |+-----------+-------------------------------------------------------+| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 || teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |+-----------+-------------------------------------------------------+rows in set (0.00 sec)
小练习：
1. 查询各岗位内包含的员工个数小于
2.的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
#题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 |+-----------------------------------------+--------------------+-----------+#题目2：mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;+-----------+---------------+| post | avg(salary) |+-----------+---------------+| operation | 16800.026000 || teacher | 151842.901429 |+-----------+---------------+#题目3：mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;+-----------+--------------+| post | avg(salary) |+-----------+--------------+| operation | 16800.026000 |+-----------+--------------+
七 查询排序:ORDER BY
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee ORDER BY age, salary DESC;
小练习：
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
#题目1mysql> select * from employee ORDER BY age asc,hire_date desc;#题目2mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;+-----------+---------------+| post | avg(salary) |+-----------+---------------+| operation | 16800.026000 || teacher | 151842.901429 |+-----------+---------------+#题目3mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;+-----------+---------------+| post | avg(salary) |+-----------+---------------+| teacher | 151842.901429 || operation | 16800.026000 |+-----------+---------------+
八 限制查询的记录数:LIMIT
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
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条
小练习：
1. 分页显示，每页5条
mysql> select * from employee limit 0,5;+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 || 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 || 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+rows in set (0.00 sec)mysql> select * from employee limit 5,5;+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 || 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 || 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+rows in set (0.00 sec)mysql> select * from employee limit 10,5;+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 || 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 || 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+rows in set (0.00 sec)
九 使用正则表达式查询
SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
小练习：
查看所有员工中名字是jin开头，n或者g结果的员工信息select * from employee where name regexp '^jin.*[gn]$';
多表查询
一 介绍
准备表
#建表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> desc department;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+mysql> desc employee;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | || sex | enum('male','female') | NO | | male | || age | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+--------+-----------------------+------+-----+---------+----------------+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 二 多表连接查询
重点：外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
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 employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;+----+------------+--------------+| id | name | depart_name |+----+------------+--------------+| 1 | egon | 技术 || 5 | liwenzhou | 技术 || 2 | alex | 人力资源 || 3 | wupeiqi | 人力资源 || 4 | yuanhao | 销售 || 6 | jingliyang | NULL |+----+------------+--------------+
4 外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门#本质就是：在内连接的基础上增加右边有左边没有的结果mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;+------+-----------+--------------+| id | name | depart_name |+------+-----------+--------------+| 1 | egon | 技术 || 2 | alex | 人力资源 || 3 | wupeiqi | 人力资源 || 4 | yuanhao | 销售 || 5 | liwenzhou | 技术 || NULL | NULL | 运营 |+------+-----------+--------------+
5 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意：mysql不支持全外连接 full JOIN；
强调：mysql可以使用此种方式间接实现全外连接。
select * from employee left join department on employee.dep_id = department.idunionselect * 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会去掉相同的纪录select * from employee left join department on employee.dep_id = department.idunion allselect * 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 || 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 | 运营 |+------+------------+--------+------+--------+------+--------------+
三 符合条件连接查询
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
四 子查询
1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等
1 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);#查看技术部员工姓名select name from employee where dep_id in (select id from department where name='技术');#查看不足1人的部门名select name from department where id not in (select dep_id from employee group by dep_id having count(dep_id)>1);
2 带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>#查询大于所有人平均年龄的员工名与年龄mysql> select name,age from emp where age > (select avg(age) from emp);+---------+------+| name | age |+---------+------+| alex | 48 || wupeiqi | 38 |+---------+------+rows in set (0.00 sec)#查询大于部门内平均年c龄的员工名、年龄select t1.name,t1.age from emp t1inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2on t1.dep_id = t2.dep_idwhere t1.age > t2.avg_age;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Turemysql> select * from employee -> where exists -> (select id from department where id=200);+----+------------+--------+------+--------+| 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表中存在dept_id=205，Falsemysql> select * from employee -> where exists -> (select id from department where id=205);Empty set (0.00 sec)
练习：查询每个部门最新入职的那位员工
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
1 #创建表 2 create table employee( 3 id int not null unique auto_increment, 4 name varchar(20) not null, 5 sex enum('male','female') not null default 'male', #大部分是男的 6 age int(3) unsigned not null default 28, 7 hire_date date not null, 8 post varchar(50), 9 post_comment varchar(100),10 salary double(15,2),11 office int, #一个部门一个屋子12 depart_id int13 );14 15 #查看表结构16 mysql> desc employee;17 +--------------+-----------------------+------+-----+---------+----------------+18 | Field | Type | Null | Key | Default | Extra |19 +--------------+-----------------------+------+-----+---------+----------------+20 | id | int(11) | NO | PRI | NULL | auto_increment |21 | name | varchar(20) | NO | | NULL | |22 | sex | enum('male','female') | NO | | male | |23 | age | int(3) unsigned | NO | | 28 | |24 | hire_date | date | NO | | NULL | |25 | post | varchar(50) | YES | | NULL | |26 | post_comment | varchar(100) | YES | | NULL | |27 | salary | double(15,2) | YES | | NULL | |28 | office | int(11) | YES | | NULL | |29 | depart_id | int(11) | YES | | NULL | |30 +--------------+-----------------------+------+-----+---------+----------------+31 32 #插入记录33 #三个部门：教学，销售，运营34 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values35 ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部36 ('alex','male',78,'20150302','teacher',1000000.31,401,1),37 ('wupeiqi','male',81,'20130305','teacher',8300,401,1),38 ('yuanhao','male',73,'20140701','teacher',3500,401,1),39 ('liwenzhou','male',28,'20121101','teacher',2100,401,1),40 ('jingliyang','female',18,'20110211','teacher',9000,401,1),41 ('jinxin','male',18,'19000301','teacher',30000,401,1),42 ('成龙','male',48,'20101111','teacher',10000,401,1),43 44 ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门45 ('丫丫','female',38,'20101101','sale',2000.35,402,2),46 ('丁丁','female',18,'20110312','sale',1000.37,402,2),47 ('星星','female',18,'20160513','sale',3000.29,402,2),48 ('格格','female',28,'20170127','sale',4000.33,402,2),49 50 ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门51 ('程咬金','male',18,'19970312','operation',20000,403,3),52 ('程咬银','female',18,'20130311','operation',19000,403,3),53 ('程咬铜','male',18,'20150411','operation',18000,403,3),54 ('程咬铁','female',18,'20140512','operation',17000,403,3)55 ;56 57 #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk 答案一（链表）
SELECT *FROM emp AS t1INNER JOIN ( SELECT post, max(hire_date) max_date FROM emp GROUP BY post) AS t2 ON t1.post = t2.postWHERE t1.hire_date = t2.max_date;
答案二（子查询）
mysql> select (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post;+---------------------------------------------------------------------------------------+| (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) |+---------------------------------------------------------------------------------------+| 张野 || 格格 || alex || egon |+---------------------------------------------------------------------------------------+rows in set (0.00 sec)mysql> select (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post;+-------------------------------------------------------------------------------------+| (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) |+-------------------------------------------------------------------------------------+| 14 || 13 || 2 || 1 |+-------------------------------------------------------------------------------------+rows in set (0.00 sec)#正确答案mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post);+--------+-----------------------------------------+------------+| name | post | hire_date |+--------+-----------------------------------------+------------+| egon | 老男孩驻沙河办事处外交大使 | 2017-03-01 || alex | teacher | 2015-03-02 || 格格 | sale | 2017-01-27 || 张野 | operation | 2016-03-11 |+--------+-----------------------------------------+------------+rows in set (0.00 sec)
答案一为正确答案，答案二中的limit 1有问题（每个部门可能有>1个为同一时间入职的新员工），我只是想用该例子来说明可以在select后使用子查询