mysql记录操作
一介绍
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用户以外的所有用户
五 查询数据SELECT
1.单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
2.关键字的执行优先级(重点)
1.找到表：from
2.拿到where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体做为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序:order by
8.限制结果的显示条数
3.简单查询
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
岗位 post varchar
薪水 salary double
create table emp(
id int primary key auto_increment,
name varchar(10) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null,
hire_date date not null,
post varchar(10),
salary int
);
#查看表的结构
mysql> desc emp;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | enum('male','femle') | NO | | male | |
| age | int(3) unsigned | NO | | NULL | |
| post | varchar(10) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
+--------+----------------------+------+-----+---------+----------------+
#插入记录
insert emp(name,sex,age,hire_date,post,salary) values
('alex','male',78,'20150302','teacher',1000000),
('wupeiqi','male',81,'20130305','teacher',8300),
('yuanhao','male',73,'20140701','teacher',3500),
('liwenzhou','male',28,'20121101','teacher',2100),
('yy','female',48,'20150311','sale',3000),
('dd','female',38,'20101101','sale',2000),
('mm','female',18,'20110312','sale',1000),
('arther','male',28,'20160311','operation',10000),
('egon','male',18,'19970312','operation',20000),
('tank','female',18,'20130311','operation',19000),
('gg','male',18,'20150411','operation',18000);
#简单查询
=====查看全部=====
select * from emp;
=====查看指定字段下的记录=====
select name,sarlary from emp;
#避免重复distinct
select distinct post from emp;
#通过四则运算查询
select name,salary*12 from emp;
select name,salary*12 as year_salary from emp;
为新的记录取字段名，不然会默认salary*12为字段
#定义显示格式
concat() 函数用于连接字符串
select concat()
select concat('nick_name: ',name,' year_salary: ', salary*12) as annual_salary
from emp;
+-------------------------------------------+
| annual_salary |
+-------------------------------------------+
| nick_name: alex year_salary: 12000000 |
| nick_name: wupeiqi year_salary: 99600 |
| nick_name: yuanhao year_salary: 42000 |
| nick_name: liwenzhou year_salary: 25200 |
| nick_name: yy year_salary: 36000 |
| nick_name: dd year_salary: 24000 |
| nick_name: mm year_salary: 12000 |
| nick_name: arther year_salary: 120000 |
| nick_name: egon year_salary: 240000 |
| nick_name: tank year_salary: 228000 |
| nick_name: gg year_salary: 216000 |
+-------------------------------------------+
合并字符串并且成为新的记录在annual_salary字段下
concat_ws() 第一个参数为分隔符
select concat_ws(':',name,salary*12) as year_sarlary from emp;
+-----------------+
| year_sarlary |
+-----------------+
| alex:12000000 |
| wupeiqi:99600 |
| yuanhao:42000 |
| liwenzhou:25200 |
| yy:36000 |
| dd:24000 |
| mm:12000 |
| arther:120000 |
| egon:240000 |
| tank:228000 |
| gg:216000 |
+-----------------+
结合case语句
select(case when name='egon' then name when name='alex' then concat(name,'_bigsb')
else concat(name,'_sb') end) as new_name from emp;
+--------------+
| new_name |
+--------------+
| alex_bigsb |
| wupeiqi_sb |
| yuanhao_sb |
| liwenzhou_sb |
| yy_sb |
| dd_sb |
| mm_sb |
| arther_sb |
| egon |
| tank_sb |
| gg_sb |
+--------------+
小练习：
1.查出所有员工的名字，薪资，格式为<名字:egon> <薪资:3000>
select concat('<name:',name,'>') as new_name from emp;
select concat('<salary:',salary,'>') as new_salary from emp;
2.查出所有的岗位(去重复)
select distinct post from emp;
3.查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select concat_ws(':',name,12*salary) as annual_year from emp;
4.where约束
where字句中可以使用：
1.比较运算符：> < >= <= <> !=
2.between 80 and 100 值在80到100之间
3.in(80,90,100)值是80或90或100
4.like'egon%'
pattern可以是%或_,
%表示任意多字符
_表示一个字符
5.逻辑运算符：在多个条件直接可以适用逻辑运算符and or not
#1.单条件查询
select name from emp where post='sale';
#2.多条件查询
select name,salary from emp where post='teacher' and salary>10000;
#3.关键字between and
select name,salary from emp where salary between 10000 and 20000;
#4.关键字is null(判断某个字段是否为null不能用等号，需要用is)
select name,post from emp where post is null;
select name,post from emp where post is not null;
#5.关键字in 集合查询
select name,salary from emp where salary in (3000,3500,4000,9000);
select concat_ws(':',name,salary) as t1 from emp where salary in (3000,3500,4000,9000);
#6.关键字like模糊查询
通配符'%',表示后面的多个字符
select * from emp where name like'eg%';
通配符'_'，表示后面的一个字符
select * from emp where name like'ego_';
正则：
select * from emp where name regexp'^ale';
确定头部，通过尾部模糊查找
select * from emp where name regexp'her$';
确定尾部，通过头部模糊查找
select * from emp where name regexp'm{2}';
5.分组查询：group by
5.1什么是分组？为什么分组？
#1.首先明确一点：分组发生在where之后，即分组是基于where之后的到的记录而进行的
#2.分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按性别分组
#3.为何要分组呢？
取每个部门的最高工资（没有分组取得就是所有部门的最高工资）
取每个部门的员工数
取男人数女人数
小窍门：'每'这个字后面的字段就是我们分组的依据
#4.大前提：
可以按照任意字段分组，但是分组完毕后，看不到组内信息，只能查看到字段，
但该字段相当于接口，我们借助聚合函数操作此接口即可得到信息
应用：
select * from emp group by post; #报错
ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
单独使用group by 关键字分组
select post from emp group by post;
注意：我们按照Post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。(如果select * from emp group by post;成立，那么查看的是所有组的信息，那么分组就没有他的意义了)，简而言之需要对每个字段下的信息进行个性化的操作，就有其意义。
如：
取出每个部门下的最高薪资，那么有了其分组的意义。
select post, max(salary) from emp group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 20000 |
| sale | 3000 |
| teacher | 1000000 |
+-----------+-------------+
group by 关键字和group_concat()一起使用
查看每个部门员工的名字
select post,group_concat(name) from emp group by post;
select post,group_concat(name) as emp_member from emp group by post;
由于只能查询到的字段是post，当需要显示字段name下的信息时，需要用到group_concat()
group by 与聚合函数一起使用
select post,count(id) as count from emp group by post;
查看每个组内有多少人（一个id号对应一个人，且不会重复）
强调:
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义，
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
在单表操作时select 的字段会受到group by 字段的影响，只能select该字段以及聚合函数的字段，它的原理是比如当以publish_id分组，来封装每个出版社的图书以及每本书的属性，但是select字段的话由于只能取字段下的唯一记录，此时只有publish_id以及聚合函数出来的结果是唯一，而其他字段下可能会存在多条记录那么就不能取出。
多表操作同理，但是根据publish_id建立的连表，然后以publish_id分组后，对应的publish表每个字段下的记录是唯一由于是用其主键分组，所以publish表的其他字段的记录能取出，而被分组的基表book表的字段不能取出。
例子
# book表 # publish表
id name publish_id id name addr
1 诛仙 1 1 北京出版社 北京
2 Python 1 2 东京出版社 东京
3 霸王 2
4 hello 2
# 单表根据publish_id分组
id name publish_id
1、2 诛仙、Python 1
3、4 霸王、hello 2
# select取的字段由于严格模式只能取当下字段下唯一的记录，所以只能取publish_id以及聚合函数结果
# 多表根据publish_id进行连表以及分组
id name publish_id id name addr
1、2 诛仙、Python 1 1 北京出版社 北京
3、4 霸王、hello 2 2 东京出版社 东京
# 此时由于根据publish_id分组，publish_id唯一那么关联的publish表中的相关字段唯一
# 可以取出publish表中所有字段以及聚合函数结果
5.2聚合函数
#强调：聚合函数聚合的组的内容，若是没有分组，则默认一组
select count(*) from emp;
select count(*) from emp where id=1;
select max(salary) from emp;
select min(salary) from emp;
select avg(salary) from emp;
select sum(salary) from emp;
select sum(salary) from emp where id=3;
#在指定id情况下salary唯一，此聚合函数没有意义
6.having过滤
having与where的区别：
执行优先级从高到低：where>group>having
1.where发生在分组group之前，因而where中可以有任意字段，但是绝对不能使用聚合函数
2.having发生在分组group by之后，因为having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数
ps:分组group by之后才可以使用聚合函数，如果where中使用聚合函数，代表已经分完组了，那么将完全违背其执行的优先级,同理分完组之后having只能使用聚合函数不是其他字段。
例子：
mysql> select * from emp where salary>99999;
+----+------+------+-----+------------+---------+---------+
| id | name | sex | age | hire_date | post | salary |
+----+------+------+-----+------------+---------+---------+
| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |
+----+------+------+-----+------------+---------+---------+
mysql> select * from emp having salary>99999;
+----+------+------+-----+------------+---------+---------+
| id | name | sex | age | hire_date | post | salary |
+----+------+------+-----+------------+---------+---------+
| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |
+----+------+------+-----+------------+---------+---------+
1 row in set (0.00 sec)
#没有指定分组，默认了group by 的是总表
mysql> select post from emp group by post having salary>99999;
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
#指定分组之后
========================================
mysql> select post,max(salary) from emp group by post having salary>99999;
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,max(salary) from emp group by post having max(salary)>99999;
+---------+-------------+
| post | max(salary) |
+---------+-------------+
| teacher | 1000000 |
+---------+-------------+
显示每个部门最高薪资大于99999的薪资
练习：
1.查询男生平均薪资大于3000的职位
select post,avg(salary) from emp where sex='male' group by post having avg(salary)>3000;
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| operation | 16000.0000 |
| teacher | 253475.0000 |
+-----------+-------------+
2.查询各岗位包含的员工个数大于3的岗位名（岗位内包含员工名字、个数）
select post,count(id),group_concat(name) from emp group by post having count(id)>3;
+-----------+-----------+--------------------------------+
| post | count(id) | group_concat(name) |
+-----------+-----------+--------------------------------+
| operation | 4 | arther,egon,tank,gg |
| teacher | 4 | alex,wupeiqi,yuanhao,liwenzhou |
+-----------+-----------+--------------------------------+
3.查询各岗位平均薪资大于10000的岗位、平均工资
select post , avg(salary) from emp group by post having avg(salary)>10000;
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| operation | 16750.0000 |
| teacher | 253475.0000 |
+-----------+-------------+
4.查询各岗位平均薪资大于10000且小于10000的岗位名、平均工资
select post , avg(salary) from emp group by post having avg(salary) between 10000 and 20000;
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| operation | 16750.0000 |
+-----------+-------------+
7.查询排序：order by
按单列排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc; #从大到小
按多列排序
select * from emp order by age,salary desc;
#先按照age排序，如果年纪相同，则按照薪资排序
8.限制查询的记录数:limit
示例：
select * from emp order by salary desc limit 3;
+----+-----------+--------+-----+------------+---------+---------+
| id | name | sex | age | hire_date | post | salary |
+----+-----------+--------+-----+------------+---------+---------+
| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |
| 2 | wupeiqi | male | 81 | 2013-03-05 | teacher | 8300 |
| 3 | yuanhao | male | 73 | 2014-07-01 | teacher | 3500 |
| 4 | liwenzhou | male | 28 | 2012-11-01 | teacher | 2100 |
| 5 | yy | female | 48 | 2015-03-11 | sale | 3000 |
+----+-----------+--------+-----+------------+---------+---------+
被限制只能看到三条信息，默认初始位置为0
select * from emp limit 5,5;
从第5条开始，即先查询出第6条，然后包含这一条在内往后差5条
+----+--------+--------+-----+------------+-----------+--------+
| id | name | sex | age | hire_date | post | salary |
+----+--------+--------+-----+------------+-----------+--------+
| 6 | dd | female | 38 | 2010-11-01 | sale | 2000 |
| 7 | mm | female | 18 | 2011-03-12 | sale | 1000 |
| 8 | arther | male | 28 | 2016-03-11 | operation | 10000 |
| 9 | egon | male | 18 | 1997-03-12 | operation | 20000 |
| 10 | tank | female | 18 | 2013-03-11 | operation | 19000 |
+----+--------+--------+-----+------------+-----------+--------+
六 权限管理
#授权表
user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv #该表放行的权限，针对：某一个字段
#按图解释：
user：放行db1，db2及其包含的所有
db：放行db1，及其db1包含的所有
tables_priv:放行db1.table1，及其该表包含的所有
columns_prive:放行db1.table1.column1，只放行该字段
