一、介绍
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据以及。
二、插入数据
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表2WHERE …;
三、更新数据
语法：UPDATE 表名 SET字段1=值1,
字段2=值2,WHERE CONDITION;
四、删除数据
语法：DELETE FROM表名WHERE CONITION;
五、查询数据
单表查询语法：SELECT 字段1,字段2... FROM表名WHERE条件GROUP BYfieldHAVING筛选ORDER BYfield
LIMIT 限制条数
多表查询语法：SELECT字段列表FROM 表1 INNER|LEFT|RIGHT JOIN表2ON 表1.字段 = 表2.字段;
select关键字的定义顺序：
SELECT DISTINCT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY LIMIT
select关键字的执行顺序：
(7) SELECT(8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) HAVING (9) ORDER BY (10) LIMIT
(一)、单表查询
company.employee
员工id idint姓名 emp_namevarchar性别 sex enum
年龄 ageint入职日期 hire_date date
岗位 postvarchar职位描述 post_commentvarchar薪水 salarydouble办公室 officeint部门编号 depart_idint#创建表create tableemployee(
idint not null uniqueauto_increment,
namevarchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
ageint(3) unsigned not null default 28,
hire_date datenot null,
postvarchar(50),
post_commentvarchar(100),
salarydouble(15,2),
officeint, #一个部门一个屋子
depart_idint);
#插入记录
#三个部门：教学，销售，运营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
表数据
简单查询：
#简单查询SELECTid,name,sex,age,hire_date,post,post_comment,salary,office,depart_idFROMemployee;SELECT * FROMemployee;SELECT name,salary FROMemployee;
#避免重复DISTINCTSELECT DISTINCT post FROMemployee;
#通过四则运算查询SELECT name, salary*12 FROMemployee;SELECT name, salary*12 AS Annual_salary FROMemployee;SELECT name, salary*12 Annual_salary FROMemployee;
#定义显示格式
CONCAT() 函数用于连接字符串SELECT CONCAT('姓名:',name,'年薪:', salary*12) ASAnnual_salaryFROMemployee;
CONCAT_WS() 第一个参数为分隔符SELECT CONCAT_WS(':',name,salary*12) ASAnnual_salaryFROMemployee;
结合CASE语句：SELECT(CASE
WHEN NAME = 'egon' THENNAMEWHEN NAME = 'alex' THENCONCAT(name,'_BIGSB')ELSEconcat(NAME,'SB')END)asnew_nameFROMemp;
1.where约束：
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(10,20,30) 值是10或20或30
4. like 'abc%'
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 not and or
#1:单条件查询SELECT name FROMemployeeWHERE post='sale';
#2:多条件查询SELECT name,salary FROMemployeeWHERE post='teacher' AND salary>10000;
#3:关键字BETWEEN AND
SELECT name,salary FROMemployeeWHERE salary BETWEEN 10000 AND 20000;SELECT name,salary FROMemployeeWHERE salary NOT BETWEEN 10000 AND 20000;
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)SELECT name,post_comment FROMemployeeWHERE post_comment IS NULL;SELECT name,post_comment FROMemployeeWHERE post_comment IS NOT NULL;SELECT name,post_comment FROMemployeeWHERE post_comment=''; 注意''是空字符串，不是null
ps：
执行update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
#5:关键字IN集合查询SELECT name,salary FROMemployeeWHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000;SELECT name,salary FROMemployeeWHERE salary IN (3000,3500,4000,9000) ;SELECT name,salary FROMemployeeWHERE salary NOT IN (3000,3500,4000,9000) ;
#6:关键字LIKE模糊查询
通配符’%’SELECT * FROMemployeeWHERE name LIKE 'eg%';
通配符’_’SELECT * FROMemployeeWHERE name LIKE 'al__';
2.正则表达式查询：
SELECT * FROM employee WHERE name REGEXP 'on$';
除了模糊查询 还可以使用正则表达式查询
小结：对字符串匹配的方式WHERE name = 'onclose';WHERE name LIKE 'on%';WHERE name REGEXP 'on$';
3.group by 分组：
#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#！！！注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于groupbylist中的表达式的值。
#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
语法：
单独使用GROUP BY关键字分组SELECT post FROM employee GROUP BYpost;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUPBY关键字和GROUP_CONCAT()函数一起使用SELECT post,GROUP_CONCAT(name) FROM employee GROUP BYpost;#按照岗位分组，并查看组内成员名SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BYpost;GROUPBY与聚合函数一起使用select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
#如果没有设置ONLY_FULL_GROUP_BY,也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK,0 rows affected (0.00sec)
mysql>quit #设置成功后，一定要退出，然后重新登录方可生效
Bye
mysql> usedb1;Databasechanged
mysql> select * from emp group bypost; #报错
ERROR1055 (42000): 'db1.emp.id' isn't in GROUP BY
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
4.聚合函数：
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：SELECT COUNT(*) FROMemployee;SELECT COUNT(*) FROM employee WHERE depart_id=1;SELECT MAX(salary) FROMemployee;SELECT MIN(salary) FROMemployee;SELECT AVG(salary) FROMemployee;SELECT SUM(salary) FROMemployee;SELECT SUM(salary) FROM employee WHERE depart_id=3;
5.having过滤：
“Where” 是一个约束声明，使用Where来约束来自数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
执行优先级从高到低：where > group by > having
注：用having就一定要用group by， 用group by不一定要有having。只要条件里面的字段, 不是表里面原先有的字段就需要用having。
6.order by 排序：
按单列排序SELECT * FROM employee ORDER BYsalary;#默认ASC升序SELECT * FROM employee ORDER BY salary ASC;SELECT * FROM employee ORDER BY salary DESC;#降序
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序SELECT * fromemployeeORDER BYage,
salaryDESC;
7.limit 限制查询记录数：
示例：SELECT * FROM employee ORDER BY salary DESCLIMIT3; #默认初始位置为0 ，查询3条数据SELECT * FROM employee ORDER BY salary DESCLIMIT0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条SELECT * FROM employee ORDER BY salary DESCLIMIT5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
(二)、多表查询
SELECT字段列表FROM 表1 INNER|LEFT|RIGHT JOIN表2ON 表1.字段 = 表2.字段;
1.交叉连接(笛卡尔积)：
交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合，结果集的行数是两个表的行数的乘积。(效率最低)
mysql> select * fromemployee,department;+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | A | male | 18 | 200 | 200 | 技术 |
| 1 | A | male | 18 | 200 | 201 | 人力资源 |
| 1 | A | male | 18 | 200 | 202 | 销售 |
| 1 | A | male | 18 | 200 | 203 | 运营 |
| 2 | B | female | 48 | 201 | 200 | 技术 |
| 2 | B | female | 48 | 201 | 201 | 人力资源 |
| 2 | B | female | 48 | 201 | 202 | 销售 |
| 2 | B | female | 48 | 201 | 203 | 运营 |
| 3 | C | male | 38 | 201 | 200 | 技术 |
| 3 | C | male | 38 | 201 | 201 | 人力资源 |
| 3 | C | male | 38 | 201 | 202 | 销售 |
| 3 | C | male | 38 | 201 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
2.内连接：只连接匹配的行
INNER JOIN(内连接)，也成为自然连接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果，内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
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 | A | 18 | male | 技术 |
| 2 | B | 48 | female | 人力资源 |
| 3 | C | 38 | male | 人力资源 |
+----+-----------+------+--------+--------------+
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
3.外连接之左连接：
返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
4.外连接之右连接：
与左连接正好相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
5.全外连接：显示左右两个表全部记录
在内连接的基础上增加左边有右边没有的，右边有左边没有的。是左外连接和右外连接的并集。
注：mysql不支持全外连接full join 但是可以用下面的方式实现(union与union all的区别：union会去掉相同的纪录)
select * from employee left join department on employee.dep_id =department.idunion
select * from employee right join department on employee.dep_id =department.id
;
(三)、子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOTEXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、
1.带in关键字的子查询
#查询平均年龄在25岁以上的部门名select id,name fromdepartmentwhere id in(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名select name fromemployeewhere dep_id in(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)select name from department where id not in (select distinct dep_id from employee);
2.带比较运算符的子查询
#查询大于部门内平均年龄的员工名、年龄select t1.name,t1.age fromemp t1inner join(select dep_id,avg(age) avg_age from emp group bydep_id) t2on t1.dep_id =t2.dep_idwhere t1.age > t2.avg_age;
3.带exists关键字的子查询
#department表中存在dept_id=203，Ture
mysql> select * fromemployee-> where exists
-> (select id from department where id=200);
#department表中存在dept_id=205，False
mysql> select * fromemployee-> where exists
-> (select id from department where id=204);
Emptyset (0.00 sec)
六、权限管理
#授权表user#该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv #该表放行的权限，针对：某一个字段
#查看自己的权限
show grants;
#创建用户create user 用户名@"主机地址" identified by"密码";create user 'A'@'1127.0.0.1' identified by '123';create user 'B'@'192.168.1.%' identified by '123';#指定ipcreate user 'C'@'%' identified by '123';#任意用户
#授权：对文件夹，对文件，对文件某一字段的权限
查看帮助：helpgrant常用权限有：select,update,alter,deleteall可以代表除了grant之外的所有权限
语法:grant [权限的名称 select insert.... | all] on 数据库.表名 to 用户名@主机地址;
特点: 如果授权时，用户不存在则直接自动创建用户
#针对所有库的授权:*.*
grant select on *.* to 'A'@'localhost' identified by '123'; #只在user表中可以查到A用户的select权限被设置为Y
没有 Grant_priv(授权)的权限
#针对某一数据库：db1.*
grant select on db1.* to 'B'@'%' identified by '123'; #只在db表中可以查到B用户的select权限被设置为Y
#针对某一个表：db1.t1grant select on db1.t1 to 'C'@'%' identified by '123'; #只在tables_priv表中可以查到C用户的select权限
#针对某一个字段：grant select (id,name),update (age) on db1.t3 to 'D'@'localhost' identified by '123';
#可以在tables_priv和columns_priv中看到相应的权限
#删除权限revoke 权限的名称 on 数据库.表名 from用户名@"主机名" ;revoke select on db1.* from 'A'@'%';
#删除用户drop user 用户名@"主机地址";
注：添加权限之后，要记得刷新权限
flush privileges;
如果要让用户拥有所有权限，可以执行下面的命令：
grant [权限的名称 select insert.... | all] on 数据库.表名 to 用户名@主机地址 with grant option;with grant option 这个用户可以将他有的权限授予别的账户
708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
