第八章| 2. MySQL数据库|数据操作| 权限管理
1、数据操作
SQL（结构化查询语言），可以操作关系型数据库
通过sql可以创建、修改账号并控制账号权限； 通过sql可以创建、修改数据库、表； 通过sql可以增删改查数据；
可以通过SQL语句中的DML语言来实现数据的操作，包括
- 使用INSERT实现数据的插入
- UPDATE实现数据的更新
- 使用DELETE实现数据的删除
- 使用SELECT查询数据以及。
1.1数据的增删改查
插入数据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用户以外的所有用户
1.2单表查询
把表中所有行和列都列举出来使用“*” 来表示所有的列如select * from student;
查询部分行和列需要列举不同的列名，而查询部分分行需要where子句进行条件限制，如select studentNo,studentName,address from student where address = '河南新乡';
在查询中使用列的别名，AS子句可以用来改变结果集中列的名称，也可以为组合或计算出的列指定名称，让标题列的信息更易懂。如select studentNo AS 学生编号，studentName as 学生姓名，address as 学生地址 from student where address = ‘河南新乡’; 还有一种情况是为通过计算、合并得到的新列命名 如select firstName+ ‘.’+lastName as 姓名 from employee
单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤 having运行完之后不是去运行order，而是去运行distinct后边的字段，进行去重；distinct运行完之后再运行order by
order by 排序字段
limit n; 最后再运行limit
#########简单查询 mysql> use db5; Database changed 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 | | +--------------+-----------------------+------+-----+---------+----------------+ 10 rows in set (0.16 sec) mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 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 | NULL | 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 | | 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 | | 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 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
mysql> select id,name,salary from employee; +----+------------+------------+ | id | name | salary | +----+------------+------------+ | 1 | egon | 7300.33 | | 2 | alex | 1000000.31 | | 3 | wupeiqi | 8300.00 | | 4 | yuanhao | 3500.00 | | 5 | liwenzhou | 2100.00 | | 6 | jingliyang | 9000.00 | | 7 | jinxin | 30000.00 | | 8 | 成龙 | 10000.00 | | 9 | 歪歪 | 3000.13 | | 10 | 丫丫 | 2000.35 | | 11 | 丁丁 | 1000.37 | | 12 | 星星 | 3000.29 | | 13 | 格格 | 4000.33 | | 14 | 张野 | 10000.13 | | 15 | 程咬金 | 20000.00 | | 16 | 程咬银 | 19000.00 | | 17 | 程咬铜 | 18000.00 | | 18 | 程咬铁 | 17000.00 | +----+------------+------------+ 18 rows in set (0.00 sec) mysql> select post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | sale | | sale | | sale | | sale | | sale | | operation | | operation | | operation | | operation | | operation | +-----------------------------------------+ 18 rows in set (0.00 sec) mysql> select distinct post from employee; ##distinct是避免重复 +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.08 sec) mysql> select name,salary*12 from employee; ##通过四则运算查询 +------------+-------------+ | name | salary*12 | +------------+-------------+ | 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 | +------------+-------------+ 18 rows in set (0.09 sec) mysql> select name,salary*12 annual_salary from employee; +------------+---------------+ | name | annual_salary | +------------+---------------+ | 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 | +------------+---------------+ 18 rows in set (0.00 sec) mysql> select concat('姓名:', name, '性别:', sex)as info from employee; ##concat通常用于连接字符串 +--------------------------------+ | info | +--------------------------------+ | 姓名:egon性别:male | | 姓名:alex性别:male | | 姓名:wupeiqi性别:male | | 姓名:yuanhao性别:male | | 姓名:liwenzhou性别:male | | 姓名:jingliyang性别:female | | 姓名:jinxin性别:male | | 姓名:成龙性别:male | | 姓名:歪歪性别:female | | 姓名:丫丫性别:female | | 姓名:丁丁性别:female | | 姓名:星星性别:female | | 姓名:格格性别:female | | 姓名:张野性别:male | | 姓名:程咬金性别:male | | 姓名:程咬银性别:female | | 姓名:程咬铜性别:male | | 姓名:程咬铁性别:female | +--------------------------------+ 18 rows in set (0.14 sec) mysql> select concat('姓名:', name, '性别:', sex) as info, concat('年薪:', salar y*12) as annual_salary from employee; +--------------------------------+--------------------+ | info | annual_salary | +--------------------------------+--------------------+ | 姓名:egon性别:male | 年薪:87603.96 | | 姓名:alex性别:male | 年薪:12000003.72 | | 姓名:wupeiqi性别:male | 年薪:99600.00 | | 姓名:yuanhao性别:male | 年薪:42000.00 | | 姓名:liwenzhou性别:male | 年薪:25200.00 | | 姓名:jingliyang性别:female | 年薪:108000.00 | | 姓名:jinxin性别:male | 年薪:360000.00 | | 姓名:成龙性别:male | 年薪:120000.00 | | 姓名:歪歪性别:female | 年薪:36001.56 | | 姓名:丫丫性别:female | 年薪:24004.20 | | 姓名:丁丁性别:female | 年薪:12004.44 | | 姓名:星星性别:female | 年薪:36003.48 | | 姓名:格格性别:female | 年薪:48003.96 | | 姓名:张野性别:male | 年薪:120001.56 | | 姓名:程咬金性别:male | 年薪:240000.00 | | 姓名:程咬银性别:female | 年薪:228000.00 | | 姓名:程咬铜性别:male | 年薪:216000.00 | | 姓名:程咬铁性别:female | 年薪:204000.00 | +--------------------------------+--------------------+ 18 rows in set (0.00 sec)
mysql> select concat(name, ':', age) from employee; +------------------------+ | concat(name, ':', age) | +------------------------+ | egon:18 | | alex:78 | | wupeiqi:81 | | yuanhao:73 | | liwenzhou:28 | | jingliyang:18 | | jinxin:18 | | 成龙:48 | | 歪歪:48 | | 丫丫:38 | | 丁丁:18 | | 星星:18 | | 格格:28 | | 张野:28 | | 程咬金:18 | | 程咬银:18 | | 程咬铜:18 | | 程咬铁:18 | +------------------------+ 18 rows in set (0.00 sec) mysql> select concat_ws(':', name, sex, age) from employee; ##concat_ws（）第一个参数为分隔符 +--------------------------------+ | concat_ws(':', name, sex, age) | +--------------------------------+ | egon:male:18 | | alex:male:78 | | wupeiqi:male:81 | | yuanhao:male:73 | | liwenzhou:male:28 | | jingliyang:female:18 | | jinxin:male:18 | | 成龙:male:48 | | 歪歪:female:48 | | 丫丫:female:38 | | 丁丁:female:18 | | 星星:female:18 | | 格格:female:28 | | 张野:male:28 | | 程咬金:male:18 | | 程咬银:female:18 | | 程咬铜:male:18 | | 程咬铁:female:18 | +--------------------------------+ 18 rows in set (0.00 sec)
练习：
1 查出所有员工的名字，薪资,格式为 <名字:egon> <薪资:3000> 2 查出所有的岗位（去掉重复） 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
mysql> select concat('<名字>:', name, '>')from employee; +--------------------------------+ | concat('<名字>:', name, '>') | +--------------------------------+ | <名字>:egon> | | <名字>:alex> | | <名字>:wupeiqi> | | <名字>:yuanhao> | | <名字>:liwenzhou> | | <名字>:jingliyang> | | <名字>:jinxin> | | <名字>:成龙> | | <名字>:歪歪> | | <名字>:丫丫> | | <名字>:丁丁> | | <名字>:星星> | | <名字>:格格> | | <名字>:张野> | | <名字>:程咬金> | | <名字>:程咬银> | | <名字>:程咬铜> | | <名字>:程咬铁> | +--------------------------------+ 18 rows in set (0.00 sec) mysql> select concat('<名字>:', name, '>'), concat('<薪资:', salary, '>') from employee; +--------------------------------+---------------------------------+ | concat('<名字>:', name, '>') | concat('<薪资:', salary, '>') | +--------------------------------+---------------------------------+ | <名字>: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> | +--------------------------------+---------------------------------+ 18 rows in set (0.00 sec)
#where约束
select id,name,age from employee where id > 7; #单条件
select name,post,salary from employee where post='teacher' and salary > 8000;
select name,salary from employee where salary >= 20000 and salary <= 30000;
select name,salary from employee where salary between 20000 and 30000;
select name,salary from employee where salary < 20000 or salary > 30000;
select name,salary from employee where salary not between 20000 and 30000;
select * from employee where age = 73 or age = 81 or age = 28;
select * from employee where age in (73,81,28);
select * from employee where post_comment is Null; #判断是否为空
select * from employee where post_comment is not Null;
select * from employee where name like "jin%"; #模糊匹配 %代表任意多个字符，jin开头的不管后边是什么
select * from employee where name like "jin___"; ##___三个下划线代表3个任意字符
mysql> select id,name,age from employee where id > 7; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 8 | 成龙 | 48 | | 9 | 歪歪 | 48 | | 10 | 丫丫 | 38 | | 11 | 丁丁 | 18 | | 12 | 星星 | 18 | | 13 | 格格 | 28 | | 14 | 张野 | 28 | | 15 | 程咬金 | 18 | | 16 | 程咬银 | 18 | | 17 | 程咬铜 | 18 | | 18 | 程咬铁 | 18 | +----+-----------+-----+ 11 rows in set (0.13 sec) mysql> select name,post,salary from employee where post='teacher' and salary > 8 000; +------------+---------+------------+ | name | post | salary | +------------+---------+------------+ | alex | teacher | 1000000.31 | | wupeiqi | teacher | 8300.00 | | jingliyang | teacher | 9000.00 | | jinxin | teacher | 30000.00 | | 成龙 | teacher | 10000.00 | +------------+---------+------------+ 5 rows in set (0.04 sec) mysql> select name,salary from employee where salary >=20000 and salary <=30000; +-----------+----------+ | name | salary | +-----------+----------+ | jinxin | 30000.00 | | 程咬金 | 20000.00 | +-----------+----------+ 2 rows in set (0.00 sec) mysql> select name,salary from employee where salary between 20000 and 30000; +-----------+----------+ | name | salary | +-----------+----------+ | jinxin | 30000.00 | | 程咬金 | 20000.00 | +-----------+----------+ 2 rows in set (0.07 sec) mysql> select name,salary from employee where salary < 20000 or salary > 30000; +------------+------------+ | name | salary | +------------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | wupeiqi | 8300.00 | | yuanhao | 3500.00 | | liwenzhou | 2100.00 | | jingliyang | 9000.00 | | 成龙 | 10000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | | 张野 | 10000.13 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +------------+------------+ 16 rows in set (0.01 sec) mysql> select name,salary from employee where salary not between 20000 and 30000 ; +------------+------------+ | name | salary | +------------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | wupeiqi | 8300.00 | | yuanhao | 3500.00 | | liwenzhou | 2100.00 | | jingliyang | 9000.00 | | 成龙 | 10000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | | 张野 | 10000.13 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +------------+------------+ 16 rows in set (0.00 sec) mysql> select * from employee where age =73 or age = 81 or age = 28;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 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 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec) mysql> select * from employee where post_comment is Null;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 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 | NULL | 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 |
| 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 |
| 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 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec) mysql> select * from employee where name like "jin%"; ##模糊匹配，%代表任意字符，jin开头的不管后边是什么 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 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 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.09 sec) mysql> select * from employee where name like "jin___"; ##___三个下划线代表3个任意字符。 +----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+ | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 |401 | 1 | +----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+ 1 row in set (0.00 sec)
group by 分组查询
#group by分组 在where之后运行
分组就是按照相应字段进行归类，每后边那个字就是要分的字段
mysql> set global sql_mode="ONLY_FULL_GROUP_BY";
分组之后，只能取分组的字段，以及每个组 聚合的结果
select post from employee group by post;
单独使用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;#按照岗位分组，并查看每个组有多少人
#聚合函数 #以组为单位进行统计，不再考虑个人
max
min
avg
sum
count
select post,count(id) as emp_count from employee group by post;##每个部门/职位有多少个员工；先确定你的表
select post,max(salary) as emp_max from employee group by post;
select post,min(salary) as emp_min from employee group by post;
select post,avg(salary) as emp_avg from employee group by post;
select post,sum(age) as emp_sum from employee group by post;
mysql> select * from employee 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 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.08 sec)
#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录（就是按照post分组，如operation组内的第一条记录。），但其实这是没有意义的mysql> set global sql_mode="ONLY_FULL_GROUP_BY"; Query OK, 0 rows affected (0.11 sec) ##设置成功一定要先退出，然后重新登录方可生效。然后你再select * 或者 select name就会报错了。 mysql> exit Bye C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use db5; Database changed mysql> select * from employee group by post; ERROR 1055 (42000): 'db5.employee.id' isn't in GROUP BY mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select post,count(id) as emp_count from employee group by post; #查询组内的id数 +-----------------------------------------+-----------+ | post | emp_count | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.06 sec)
强调：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
#没有group by则默认整体算作一组
select max(salary) from employee; #取所有员工的最高工资
#group_concat(name) 职位包含的所有员工成员的名字
select post,group_concat(name) from employee group by post;mysql> select max(salary) from employee; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.07 sec) mysql> select post,group_concat(name) from employee group by post; ##也可以查询组内成员的性别group_concat(sex)； +-----------------------------------------+------------------------------------- | post | group_concat(name) +-----------------------------------------+------------------------------------- | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+------------------------------------- 4 rows in set (0.01 sec)
#练习：
1. 查询岗位名以及岗位包含的所有员工名字 #先确定你查的是哪张表，有没有过滤条件where，有没有分组分类的； 2. 先取出年龄在50以上的然后再 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select post,group_concat(name) from employee group by post;
select post,count(id) from employee where age > 50 group by post;
select sex,count(id) from employee group by sex;
select sex,avg(salary) from employee group by sex;
mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+------------------------------------ | post | group_concat(name) +-----------------------------------------+------------------------------------ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yunhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+------------------------------------ 4 rows in set (0.00 sec) mysql> select post,count(id) from employee where age >50 group by post; +---------+-----------+ | post | count(id) | +---------+-----------+ | teacher | 3 | +---------+-----------+ 1 row in set (0.00 sec) mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ 2 rows in set (0.00 sec) mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.00 sec)
mysql> select sex,max(age) from employee group by sex; +--------+----------+ | sex | max(age) | +--------+----------+ | male | 81 | | female | 48 | +--------+----------+ 2 rows in set (0.06 sec)
#having 过滤条件 在分组之后进行；where在分组之前 （过滤的是分组组内的成员）
例如： 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 #聚合出员工个数 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 and avg(salary) < 20000;
select post,group_concat(name),count(id) from employee group by post;
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
select post,avg(salary) from employee group by post having avg(salary) > 10000;
mysql> select post,group_concat(name), count(id) from employee group by post; +-----------------------------------------+------------------------------------- | post |count(id) | | group_concat(name) +-----------------------------------------+------------------------------------- | operation | 5 | | 程咬铁,程咬铜,程咬银,程咬金,张野 | sale | 5 | | 格格,星星,丁丁,丫丫,歪歪 | teacher | 7 | | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex
| 老男孩驻沙河办事处外交大使| 1 | | egon +-----------------------------------------+------------------------------------- 4 rows in set (0.00 sec) 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 | +-----------------------------------------+--------------------+-----------+ 1 row in set (0.01 sec) mysql> select post,avg(salary) from employee group by post having avg(salary) > -> \c mysql> select post, avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
#order by 默认就是升序
select * from employee order by age asc; #升序，不加asc也可以，默认 从低到高
select * from employee order by age desc; #降序 从高到低
select * from employee order by age asc,id desc; #先按照age升序排，如果age相同则按照id降序排；
聚合函数不能用在where内；分完组之后才能用聚合函数，having可以用；select max(salary) from employee 这里边运行max的时候就已经分组了； 先找表，再where再分组，...最后才打印select你想打印的内容
select distinct post,count(id) as emp_count from employee
where salary > 1000
group by post
having count(id) > 1 #在这个位置不能用emp_count>1，因为运行having之前还没运行distinct这个字段呢
order by emp_count desc #可以用count(id)，它是在分组之后干的；emp_count也可以用，因为order by 是在distinct后边运行；
;
mysql> select * from employee order by age asc, id desc; #在age asc 的组内进行id排序。 +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
mysql> select distinct post, count(id) as emp_count from employee -> where salary > 1000 -> group by post -> having count(id) > 1 -> order by emp_count desc; +-----------+-----------+ | post | emp_count | +-----------+-----------+ | teacher | 7 | | sale | 5 | | operation | 5 | +-----------+-----------+ 3 rows in set (0.00 sec)
#limit限制打印条数
select * from employee limit 3;
select * from employee order by salary desc limit 1; #找工资最高的那个人的详细信息
select * from employee limit 0,5; #从0开始往后再取5个；基本的分页功能
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;
mysql> select * from employee limit 3; +----+---------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 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 | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+---------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec) mysql> select * from employee order by salary desc limit 1; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 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 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) 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 | NULL | 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 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec)
#总结：
语法顺序：
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
执行顺序：
def from(db,table):
f=open(r'%s\%s' %(db,table)) #打开文件
return f #返回文件对象
def where(condition,f):
for line in f:
if condition:
yield line #满足条件再返回，不停地返回
def group(lines): #分组
pass
def having(group_res): #分组，拿到分组的结果
pass
def distinct(having_res): #拿到having_res的结果
pass
def order(distinct_res): #拿到distinct_res的结果
pass
def limit(order_res) #
pass
def select():
f=from('db1','t1') #找表
lines=where('id>3',f)
group_res=group(lines)
having_res=having(group_res)
distinct_res=distinct(having_res)
order_res=order(distinct_res)
res=limit(order_res)
print(res)
return res #返回值
#正则表达式 查询
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin'; #以什么开头^
select * from employee where name regexp '^jin.*(g|n)$'; # .*()以什么结尾 ，以g或者n结尾
mysql> select * from employee where name regexp '^jin'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 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 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.07 sec) mysql> select * from employee where name regexp '^jin.*(g|n)$'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 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 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.00 sec)
1.3 多表查询
把有关系的表连接合到一起； department和employee表是多对一的关系。
内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;
左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;
右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;
全外连接：在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id ; #mysql不支持full join
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 ;
笛卡尔积；在笛卡尔积的基础之中对表进行筛选，where，这就是连接；不要用where干，它是过滤的，有专门的方法
mysql> create database db6; Query OK, 1 row affected (0.01 sec) mysql> use db6; Database changed mysql> create table department( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.78 sec) mysql> 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 -> ); Query OK, 0 rows affected (1.05 sec) mysql> insert into department values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'); Query OK, 4 rows affected (0.19 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> 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) -> ; Query OK, 6 rows affected (0.15 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) 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 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) 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 | 运营 | +----+------------+--------+------+--------+------+--------------+ 24 rows in set (0.07 sec) mysql> select * from employee inner join department on employee.dep_id = department.id ; #共同的部分是200/201/202 +----+-----------+--------+------+--------+------+--------------+ | 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 | 技术 | +----+-----------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) mysql> select * from employee left join department on employee.dep_id = department.id ; #左边employee独有的204 +----+------------+--------+------+--------+------+--------------+ | 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 | +----+------------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) mysql> select * from employee right join department on employee.dep_id = department.id ; #右边department独有的203 +------+-----------+--------+------+--------+------+--------------+ | 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 | 运营 | +------+-----------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) 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 | 运营 | +------+------------+--------+------+--------+------+--------------+ 7 rows in set (0.11 sec)
#查询平均年龄大于30岁的部门名
select * from employee inner join department on employee.dep_id = department.id;
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
group by department.name
having avg(age) > 30;
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 | | +--------+-----------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select department.name,avg(age) from employee inner join department on employee.dep_id = depa rtment.id; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illeg al if there is no GROUP BY clause mysql> select * from employee inner 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 | 技术 | +----+-----------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) mysql> select department.name,avg(age) from employee inner join department on employee.dep_id = department.id -> group by department.name -> having avg(age) > 30; +--------------+----------+ | name | avg(age) | +--------------+----------+ | 人力资源 | 43.0000 | +--------------+----------+ 1 row in set (0.00 sec)
SELECT语句关键字执行优先级
http://www.cnblogs.com/linhaifeng/articles/7372774.html
子查询
http://www.cnblogs.com/linhaifeng/articles/7267596.html
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
1、带IN关键字的子查询
查询平均年龄在25岁以上的部门名
方法一：
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
-> group by department.name
-> having avg(age) > 25;
方法二：
select name from department where id in
-> (select dep_id from employee
-> group by dep_id
-> having avg(age) > 25);
作为子查询-->> select dep_id from employee
group by dep_id
having avg(age) > 25;
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 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select dep_id from employee -> group by dep_id -> having avg(age) > 25; +--------+ | dep_id | +--------+ | 201 | | 202 | +--------+ 2 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select name from department where id in -> (select dep_id from employee -> group by dep_id -> having avg(age) > 25); +--------------+ | name | +--------------+ | 人力资源 | | 销售 | +--------------+ 2 rows in set (0.11 sec)
查看技术部员工姓名 # 涉及两种表
方法一：（使用join，不用分组）
select employee.name from department inner join employee on department.id=employee.dep_id
where department.name = '技术' ;
方法二：
select * from employee where dep_id = ( select employee.name from employee where dep_id = (
select id from department where name = "技术"); select id from department where name = '技术');
查看不足1人（not >=1）的部门名
方法一：（使用left join ，注意不能使用 where dep_id not in employee ; 这样语法是错误的）
select department.name from department left join employee on employee.dep_id = department.id
where dep_id is nul l ;
方法二：
select name from department where id not in (
select distinct dep_id from employee) ;
mysql> select id from department where name = "技术"; +------+ | id | +------+ | 200 | +------+ 1 row in set (0.00 sec) mysql> select * from employee where dep_id = ( -> select id from department where name = "技术"); +----+-----------+------+------+--------+ | id | name | sex | age | dep_id | +----+-----------+------+------+--------+ | 1 | egon | male | 18 | 200 | | 5 | liwenzhou | male | 18 | 200 | +----+-----------+------+------+--------+ 2 rows in set (0.00 sec) mysql> select name from employee where dep_id = ( -> select id from department where name = "技术"); +-----------+ | name | +-----------+ | egon | | liwenzhou | +-----------+ 2 rows in set (0.00 sec)
mysql> 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 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select dep_id from employee; +--------+ | dep_id | +--------+ | 200 | | 201 | | 201 | | 202 | | 200 | | 204 | +--------+ 6 rows in set (0.00 sec) mysql> select distinct dep_id from employee; +--------+ | dep_id | +--------+ | 200 | | 201 | | 202 | | 204 | +--------+ 4 rows in set (0.00 sec) mysql> select name from department where id not in ( -> select distinct dep_id from employee); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec) 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 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec)
2、带比较运算符的子查询
查询大于所有人平均年龄的员工名与年龄 #先拿到所有人的平均年龄，然后把它作为条件
select name,age from employee where age > (select avg(age) from employee);
mysql> select avg(age) from employee; +----------+ | avg(age) | +----------+ | 28.0000 | +----------+ 1 row in set (0.06 sec) mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec)
3、带EXISTS关键字的子查询 #针对某一个子查询问一问它有没有结果，有就为真了；
条件成立就拿到了
select * from employee
where EXISTS
(select id from department where name = "技术");
mysql> select * from employee -> where EXISTS -> (select id from department where name = "技术"); +----+------------+--------+------+--------+ | 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 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) -> -> \c mysql> select * from employee -> where EXISTS -> (select id from department where name = "IT"); Empty set (0.00 sec)
把在内存中虚拟表留下来以便你查询用，把查询表起个名字当做一个表来用；
mysql> select id,name,sex from employee; +----+------------+--------+ | id | name | sex | +----+------------+--------+ | 1 | egon | male | | 2 | alex | female | | 3 | wupeiqi | male | | 4 | yuanhao | female | | 5 | liwenzhou | male | | 6 | jingliyang | female | +----+------------+--------+ 6 rows in set (0.00 sec) mysql> select * from -> (select id,name,sex from employee)as t1; +----+------------+--------+ | id | name | sex | +----+------------+--------+ | 1 | egon | male | | 2 | alex | female | | 3 | wupeiqi | male | | 4 | yuanhao | female | | 5 | liwenzhou | male | | 6 | jingliyang | female | +----+------------+--------+ 6 rows in set (0.09 sec)
#统计每个（分组，得到聚合的结果）部门最新（时间最大的一个）入职的员工
select post ,max(hire_date) from employee group by post ;
得到的新表和原表连接到一起了
mysql> use db5; Database changed mysql> show tables; +---------------+ | Tables_in_db5 | +---------------+ | customer | | employee | | student | +---------------+ 3 rows in set (0.10 sec) mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 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 | NULL | 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 | | 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 | | 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 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select post ,max(hire_date) from employee group by post; +-----------------------------------------+----------------+ | post | max(hire_date) | +-----------------------------------------+----------------+ | operation | 2016-03-11 | | sale | 2017-01-27 | | teacher | 2015-03-02 | | 老男孩驻沙河办事处外交大使 | 2017-03-01 | +-----------------------------------------+----------------+ 4 rows in set (0.06 sec) mysql> select * from employee as t1 -> inner join -> (select post ,max(hire_date) from employee group by post) as t2 -> on t1.post = t2.post; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+----------------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | post | max(hire_date) | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+----------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 老男孩驻沙河办事处外交大使 | 2017-03-01 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | teacher | 2015-03-02 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | teacher | 2015-03-02 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | teacher | 2015-03-02 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | teacher | 2015-03-02 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | teacher | 2015-03-02 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | teacher | 2015-03-02 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | teacher | 2015-03-02 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | sale | 2017-01-27 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | sale | 2017-01-27 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | sale | 2017-01-27 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | sale | 2017-01-27 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | sale | 2017-01-27 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | operation | 2016-03-11 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | operation | 2016-03-11 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | operation | 2016-03-11 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | operation | 2016-03-11 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | operation | 2016-03-11 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+----------------+ 18 rows in set (0.00 sec) mysql> select post ,max(hire_date) from employee group by post; +-----------------------------------------+----------------+ | post | max(hire_date) | +-----------------------------------------+----------------+ | operation | 2016-03-11 | | sale | 2017-01-27 | | teacher | 2015-03-02 | | 老男孩驻沙河办事处外交大使 | 2017-03-01 | +-----------------------------------------+----------------+ 4 rows in set (0.04 sec) mysql> select * from employee as t1 -> inner join -> (select post ,max(hire_date) as max_hire_date from employee group by post) as t2 -> on t1.post = t2.post -> where t1.hire_date = t2.max_hire_date; +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+---------------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | post | max_hire_date | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+---------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 老男孩驻沙河办事处外交大使 | 2017-03-01 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | teacher | 2015-03-02 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | sale | 2017-01-27 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | operation | 2016-03-11 | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+---------------+ 4 rows in set (0.07 sec)
2. 权限管理
root@%（表示这个root这个账号允许其他机器连接） 和 root@localhost（允许mysql安装的机器连接），是因mysql对安全性的重视。这样的区分，即使别人盗取了数据库的账号和密码，从他的机器连接到mysql的服务端，我们还是可以控制他的操作权限，也就有了更高的安全性。
权限管理 1、创建账号 # 本地账号 create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123 # 远程帐号 #前提是31.10能拼通我服务端的ip这才有意义 create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon2 -p123 -h 指定的是服务端ip ##运行egon2在这台机器之上登录；‘192.168.31.10’是客户端的ip地址 create user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip #表示egon3用户能在31这段网中任意登录 create user 'egon3'@'%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip #所有的客户端ip都能登录，只要你能拼通服务端。 2、授权 这几张表对权限的控制是依次降低的 user:*.* # *.* 所有库下边所有的表 只要user一放权，下面所有的都可以用了 db:db1.* #具体写哪个库，只对某个库有查询权限，库下边所有的表 tables_priv:db1.t1 #表级别的权限 columns_priv:id,name #针对某个字段 grant all on *.* to 'egon1'@'localhost'; #授权多有的权限，*.*是user下所有级别的； 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.t1 to 'kris1'@'localhost';# -uroot 只放行了对t1的权限
#先查看下 select * from mysql.user\G 看select权限有没有看；再看select * from mysql.db\G 查看下select权限有没开
#select * from mysql.tables_priv\G ,看到有权限了；验证下：exit mysql -ukris1 -p123 -->> show databeses; -->use db1; -->show tables;->select * from t1;
revoke select on db1.t2 from 'egon1'@'localhost'; grant select(id,name),update(age) on db1.t2 to 'egon1'@'localhost'; #精确到字段级别
#select * from mysql.columns_priv;
mysql> create user 'kris1'@'localhost' identified by '123'; ##创建本地账号； Query OK, 0 rows affected (0.39 sec) mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y ..... max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N *************************** 2. row *************************** Host: 127.0.0.1 User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Del...... authentication_string: password_expired: N *************************** 3. row *************************** Host: ::1 User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y ...... password_expired: N *************************** 4. row *************************** Host: localhost User: Password: Select_priv: N I.........*************************** 5. row *************************** Host: localhost User: kris1 Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: N #下面的都有N，造出来的权限默认是在user表里边，user没有放行任何权限，但不一定这个用户一定就没有权限； Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N 5 rows in set (0.05 sec) mysql> select * from mysql.db\G #查看下面的db表 *************************** 1. row *************************** Host: % ##有关于库相关的权限，但没有具体到用户，用户都是空的，意味着对任何用户都是有效的，包括刚刚的kris1；%代表任意的，包含localhost Db: test User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N Event_priv: Y Trigger_priv: Y *************************** 2. row *************************** Host: % Db: test\_% #对db下的以 __开头的任意都有权限 User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N Event_priv: Y Trigger_priv: Y 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | db5 | | db6 | | mysql | | performance_schema | | test | +--------------------+ 10 rows in set (0.68 sec) mysql> create database test_1; Query OK, 1 row affected (0.06 sec) mysql> create database test2; Query OK, 1 row affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | db5 | | db6 | | mysql | | performance_schema | | test | | test2 | | test_1 | +--------------------+ 12 rows in set (0.00 sec) mysql> exit Bye C:\Users\Administrator>mysql -ukris1 -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | test_1 | +--------------------+ 3 rows in set (0.00 sec) mysql> drop database test_1; Query OK, 0 rows affected (0.64 sec) mysql> use test; Database changed mysql> show tables; Empty set (0.06 sec)
C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant select on *.* to 'kris1'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N *************************** 2. row *************************** Host: 127.0.0.1 User: root Password: Select_priv: Y Insert_priv: Y *************************** 3. row *************************** Host: ::1 User: root Password: Select_priv: Y ...... password_expired: N *************************** 4. row *************************** Host: localhost User: Password: Select_priv: N Insert_priv: N Update_priv: N ..... Create_tablespace_priv: N ssl_type: .....*************************** 5. row *************************** Host: localhost User: kris1 Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N 5 rows in set (0.05 sec) mysql> exit Bye C:\Users\Administrator>mysql -ukris1 -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.39 MySQL Community Server (GPL) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | db5 | | db6 | | mysql | | performance_schema | | test | | test2 | +--------------------+ 11 rows in set (0.05 sec) mysql> use db1; Database changed mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | SB | | 3 | SB | +------+------+ 2 rows in set (0.24 sec) mysql> insert into t2 values(4, 'kris1',45); ERROR 1142 (42000): INSERT command denied to user 'kris1'@'localhost' for table 't2' mysql>
C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights r Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input mysql> revoke select on *.* from 'kris1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.user\G *************************** 1. row *************************** ...... *************************** 5. row *************************** Host: localhost User: kris1 Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: N #又没有权限了 Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N 5 rows in set (0.00 sec)
mysql> grant select on db1.* to 'kris1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.db\G *************************** 1. row *************************** Host: % Db: test User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N Event_priv: Y Trigger_priv: Y *************************** 2. row *************************** Host: % Db: test\_% User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N Event_priv: Y Trigger_priv: Y *************************** 3. row *************************** Host: localhost Db: db1 User: kris1 Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 3 rows in set (0.00 sec) mysql> exit Bye C:\Users\Administrator>mysql -ukris1 -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> use db1; Database changed mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | SB | | 3 | SB | +------+------+ 2 rows in set (0.00 sec) mysql> insert into t1 values(2,'SB'); ERROR 1142 (42000): INSERT command denied to user 'kris1'@'localhost' for table 't mysql> exit Bye C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> revoke select on db1.* from 'kris1'@'localhost'; Query OK, 0 rows affected (0.00 sec)
