一、介绍
在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 这个用户可以将他有的权限授予别的账户