一、SQL:DML数据操作语言
数据操纵语言:操纵数据,针对数据的增删改查;
核心语句:
(1)增insert:
①insert into 表名 values(数据的集合);//添加一条数据;
②insert into 表名(字段名1,字段名2,字段名3) values(“数据1”,“数据2”,“数据3”); //添加指定字段数据,其他系统默认;
③insert into 表名 values(数据集合1),(数据集合2),(数据集合3);//insert进行小批量数据插入
(2)删delete:
①delete from 表名;//删除表中所有数据
②delete from 表名 where 字段名 = “数据”; //过滤数据,符合该条件的删除;
(3)改update:
①update 表名 set 字段名 = “新的数据” where 字段名 = “旧的数据”;//修改数据,有过滤条件。
②update 表名 set 字段名 = “新的数据”; //修改表中所有数据
(4)查select:
1.普通查询
①select * from 表名;// * 表示通匹表中所有字段,不建议使用,易泄露信息;
②select 字段名1,字段名2,字段名3… from 表名;//等值于上面的查询
③select * from 表名 where 字段名 = “数据”; //有过滤条件
2.去重查询
①select distinct 字段名 from 表名;//重复的只出现一次
3.排序查询
①select distinct 字段名 from 表名 order by 字段名;//不加asc,系统默认加升序查询
②select distinct 字段名 from 表名 order by 字段名 asc;//升序排序查询
③select distinct 字段名 from 表名 order by 字段名 desc;//降序排序查询
4.分组查询:Sum()相加,count统计数据个数,year获取年份
①select 字段名,操作名(字段名2) from 表名 group by 字段名; //进行分组查询:先分组,再在组内查询。
5.等值查询:按照笛卡尔乘积方式处理,效率太低
①select 表名1.字段名1,字段名2 from 表名1,表名2 where 表名1.字段名1 = 表名2.字段名1 and 字段名3 判断条件 and 字段名2 判断条件;//等值查询
例如:
6.1连接查询:先将范围缩小,再按照笛卡尔乘积方式处理,效率提高。
1.外连接查询
①左外连接查询:左表过滤的结果必须全部存在;右表匹配左表过滤后的结果若匹配成功,补成功的数据,否则补NULL; 左外连接使用的十分多。
select 左表.字段名1,字段名2
from
select 字段名1,字段名2 from 左表 where 字段名2 判断条件 //过滤左表数据
left join
select 字段名1, 字段名3 from 右表 where 字段名 判断条件 //过滤右表数据
on 左表.字段名1 = 右表.字段名1;
例如:a,b为表的别名。
若左外连接不想出现NULL的话,可在最后加上过滤条件:where 表名.字段名 is not null;使用如图:
②右外连接查询:右表过滤的结果必须全部存在;左表匹配右表过滤后的结果若匹配成功,补成功的数据,否则补NULL; 右外连接使用的不是非常多。
select 左表.字段名1,字段名2
from
select 字段名1,字段名2 from 左表 where 字段名2 判断条件 //过滤左表数据
right join
select 字段名1, 字段名3 from 右表 where 字段名 判断条件 //过滤右表数据
on 左表.字段名1 = 右表.字段名1;
例如:a,b为表的别名。
若左外连接不想出现NULL的话,可在最后加上过滤条件:where 表名.字段名 is not null;使用如图:
③全外连接查询: 左表和右表数据全部都存在,与左外连接、右对应一边不存在则补空。
select 左表.字段名1,字段名2
from
select 字段名1,字段名2 from 左表 where 字段名2 判断条件 //过滤左表数据
full join
select 字段名1, 字段名3 from 右表 where 字段名 判断条件 //过滤右表数据
on 左表.字段名1 = 右表.字段名1;
例如:a,b为表的别名。
6.2内连接查询:只筛选匹配项,不匹配不显示。
select 左表.字段名1,字段名2
from
select 字段名1,字段名2 from 左表 where 字段名2 判断条件 //过滤左表数据
inner join
select 字段名1, 字段名3 from 右表 where 字段名 判断条件 //过滤右表数据
on 左表.字段名1 = 右表.字段名1;
例如:a,b为表的别名。
7.聚合查询:
①聚合查询1:带去重效果;
select * from 表1
union
select * from 表2;
①聚合查询2:不带去重效果;
select * from 表1
union all
select * from 表2;
使用实例:
◉ 将数据下列数据添加进学生表中:使用insert语句;
001 zhangsan man 20;
002 lisi woman 19;
003 wangwu man 22;
004 zhaoliu man 21;
005 kaixin woman 18;
006 gaoxing man 23;
◉ 将数据下列数据添加进学生表中:使用insert语句2,只添加三个数据,最后一个用默认值代替;
◉ insert进行小批量数据插入;
◉ 删除符合条件的数据;
◉ 将002的年龄改为19;
◉ 普通查询1:select * 使用简单,但容易泄露信息;
◉ 普通查询2:select id,name,sex,age form stu;与上一条查询语句等同。
与上条对比一下,虽然第一个查询简单,但是查询时候一下就查询到客户的所有信息,容易造成信息泄露,而第二种方法虽然麻烦点,但是安全程度更高。
◉ 去重查询:
先将005的年龄修改为22岁,此时表中有重复年龄;
去重查询:重复的只出现一次。
◉ 升序排序查询:
◉ 降序排序查询:
◉ 分组查询:查询每组总成绩
先创建一个成绩表:
分组,在小组内进行查询再算出组内总成绩。
◉ 等值查询:效率太低
从这两个表中查询年龄小于20岁学生的不及格成绩;
◉ 左外连接查询 :
案例1: 左表过滤id与年龄小于20的,右表过滤id与成绩小于60的;缩小了查询范围,效率提高。
案例2: 假如我们现在有这样一张学生表与成绩表,再次对其进行左外连接查询。
当我们再次执行左外连接查询时,出现如下结果。
出现了004这条数据,它并没有成功匹配,它的score为NULL,有就是说右表补空;其规则为:左表过滤的结果必须全部存在,右表匹配左表过滤后的结果若成功,补成功的数据,否则补NULL;
若想让其匹配后不出现空的话,使用如下:
◉ 右外连接查询 假如我们现在有这样一张学生表与成绩表,对其进行右外连接查询。
查询结果如图:右表过滤的结果必须全部存在,左表匹配右表过滤后的结果若成功,补成功的数据,否则补NULL;
其大致匹配过程如图:
若想让其匹配后不出现空的话,使用如下:
◉ 全外连接查询 :查询方法如图,由于我们使用的MySQL版本不支持,这里就不演示了。
其大致流程如图:
◉ 内连接查询:只筛选匹配项,不匹配不显示
其大致流程如图:
◉ 聚合查询:
例如我们现在有这样两张表,想要查询全校的师生信息。
将两张表信息拿到拼接成为一张表。
若学生表中教与师表中有一条一模一样的信息,再次查询:
重复的信息只出现了一次,自带去重。
如果不想去除重复的:则使用下面操作。
二、SQL:DCL数据控制语言
数据控制语言:针对于权限的控制。
核心语句:
(1)授予权限grant: grant 权限 on 库名.* to “用户名”;
select//查询权限
update//修改权限
insert//插入权限
delete//删除权限
create//创建数据库、表的权限
drop//删除数据库、表的权限
all//所有权限
(2)回收权限revoke: revoke 权限 on 库名.* from “用户名”;//权限回收不能越级,也不能反祖。
//也可以授予多个权限只回收部分权限。
select//查询权限
update//修改权限
insert//插入权限
delete//删除权限
create//创建数据库、表的权限
drop//删除数据库、表的权限
all//所有权限
使用实例:
◉ 授予权限 :
我们MySQL中现在是以管理员权限登录的,现在创建一个新用户。
拿到该用户信息:root为超级管理员,只允许在本机上登录。
用新创建的用户登录,查看所有的库;我们发现只能查看这两个库,其他的没有权限查看。新用户创建后,对任何表,任何库都没有操作权限。
现在我们重新登录root用户为其授予权限:让其能够查看CY1212库;
再用新创建的用户登录:多出了CY1212库
对CY1212库仅有查看权限,无法执行其他权限
◉ 回收权限 :
实例1:root用户下回收新用户对CY1212的查看权限;
再登录cy1212用户则没有查看CY1212库权限了;
实例2:反祖问题:假如root用户将所有权限给了u1,u1将所有权限给了u2,u2将所有权限给了u3,u3将所有权限给了u4;那么u4能回收u2或者u3的权限吗?
答案是不行,若u4把u2回收,那u3没有权限授予u4权限了,u4则无权限,所有权限都失效了。