DQL
基本查询
- 语法：select 查询列表 from 表名;
- 查询结果虚拟表，不是真实存在
- 查询列表，可以是常量、字段名、表达式、函数和组合
条件查询
-
语法：select 查询列表 from 表名 where 条件;
-
条件形式
- 条件表达式: > < >= <= != 不等于<>
- 逻辑表达式: and并且 or获取 not取反
- 模糊条件: like / between(包含开始和结束). . .and / in / not null
- 去除重复 distinct
排序查询
- 语法：
select 查询列表
from 表名
where 条件
order by 排序列表 asc|desc; - asc升序默认 desc降序
- 排序的列表可以是单个字段、多个字段、函数、表达式、别名或者组合
常见函数
-
单行函数、聚合函数、多行函数、日期函数、数值函数
-
单行函数：函数中传递一个参数返回一个值
- concat(str1,str2)：连接
- substr(str,索引,截取几个)：截取字串
- upper()：变大写
- lower()：变小写
- replace(str,旧字符,新字符)：替换
- length()：获取字节长度
- trim()：去前后空格
- lpad(str,字符总长度,填充的内容)：左填充
- rpad(str,字符总长度,填充的内容)：右填充
- instr()：获取字串第一次出现的索引
-
数值函数
- ceil()：向上取整
- floor()：向下取整
- mod(被除数,除数)：取模
- round()：四舍五入
- truncate():截断
- rand():随机数
-
日期函数
- now();#获取当前的时间日期+时间
- year(now())/month(now())/day(now())#年/月/日
- date_format(日期,'%Y年%m月%d日 %H时%i分%s秒'');#指定日期的格式
- curdate();#当前日期
- curtime();#当前时间
- str_to_date：将字符串转换成日期
- datediff：返回两个日期相差的天数
- monthname：以英文形式返回月
-
流程控制结构
- if(表达式,"值1","值2");如果表达式成功就取值1否则取值2
- case
when条件表达式 then 值或者表达式
when条件表达式 then 值或者表达式
...
else 值或者表达式
end
-
多行函数(聚合、分组)
-
语法:select函数名(实参列表);
-
最大值max()
-
最小值min()
-
平均值avg()
-
总和sum()
-
总记录数count()
- count (*):统计结果集中的行数【推荐使用】
- count(字段):统计该字段中非空值的个数
-
聚合和单行函数区别
-
单行函数
- 将一个数据进行处理，返回一个值
-
聚合函数
- 将虚拟表看作一个组，处理一组数据，返回一个值，忽略null值
-
-
分组查询
-
语法：
select 分组字段和分组函数 ---- ④
from 表名 ---- ①
where 条件 ---- ②
group by 分组字段 ---- ③
having 分组后筛选 ---- ④
order by 排序列表 asc|desc; ---- ⑤ -
where和having区别
- 1、where是用于分组前使用、having是用于分组后使用
- 2、where后不可以跟聚合函数、having后可以跟聚合函数
- 3、where执行的时机要优于having
分页查询
- 语法：
select 查询列表
from 表名
where 条件
group by 分组字段
having 分组后筛选
order by 排序列表 asc|desc
limit 偏移量,查询个数 - limit（page-1）*size,size; page页数 size每页显示的行数
表关系
-
一对一建表规则
-
在任意一方选择添加一个外键字段、指向另一方的主键字段
-
主键的类型和外键的类型必须一致
-
先插主表、再差从表
#创建人员表 create table person( #主键 pid int primary key, name varchar(20), sex CHAR(1) ); #创建身份证号表 create table idCard( id int, number1 varchar(18), #外键 pid int ); #外键约束:描述表与表之间的关系保证数据的完整性和有效性 #给已存在的表添加外键约束 #alter table 从表 add constraint foreign key(外键列) references 主表(主键列); #打开系统架构器 ctrl+alt+d alter table idCard add constraint foreign key(pid) references person(pid); #先插主表 insert into person (pid,name,sex) values(1001,'pgone','男'),(1002,'李小璐','女'),(1003,'白百何','女'); insert into idCard (id,number1,pid) values(1,'412828...',1001); insert into idCard (id,number1,pid) values(2,'412828...',1002); insert into idCard (id,number1,pid) values(3,'412828...',1003);
-
-
一对多建表规则
-
在多的一方添加一个外键字段指向的一方主键
-
主表中必须设置主键
-
先插主键、再差从表
#创建部门表 create table dept( #部门编号 deptno int primary key, #部门名称 dname varchar(20), #部门所在位置 loc varchar(50) ); #创建员工表 create table emp( #员工编号 empno int primary key, #员工姓名 ename varchar(20), #薪资 sal double, #外键列 deptno int, #创建外键关系 constraint foreign key(deptno) references dept(deptno) ); #先插主表 insert into dept (deptno,dname,loc)values(10,'搓澡部','地下一层'),(20,'spa部','二楼西门'),(30,'保健部','三郎暗格'); #再插从表 insert into emp (empno,ename,sal) values(1001,'马蓉',3500),(1002,'马苏',4300),(1003,'白百何',7600), (1004,'杨幂',8900),(1005,'都美竹',12000),(1006,'迪丽热巴',24000);
-
-
多对多建表规则
-
创建一个中间表，在中间表中至少含有2个字段分别表示两主表的外键字段、指向量主表的主表的主键字段
#创建学生表 create table student( sid int primary key, sname varchar(20) ); #创建课程表 create table course( cid int primary key, cname varchar(20) ); #创建中间表 create table c_u( sid int, cid int ); #给已存在的表添加外键关系 alter table c_u add constraint foreign key(sid) references student(sid); alter table c_u add constraint foreign key(cid) references course(cid); #先插主表 insert into student(sid,sname) values(1001,'段誉'),(1002,'虚竹'),(1003,'乔峰'),(1004,'岳不群'); insert into course(cid,cname) values(10,'辟邪剑法'),(20,'欲女心经'),(30,'葵花宝典'); #插入中间表 insert into c_u(sid,cid) values(1001,20),(1001,30),(1002,20),(1002,30),(1003,10),(1003,20), (1003,30),(1004,10);
-
多表查询
-
内连接：查询量表中有关联关系的数据(从表中的外=主表中的主键)
-
隐式内连接
- 语法：
select
查询的内容
from
表1,表2
where 关联关系
and 筛选
- 语法：
-
显示内连接(推荐)
- 语法：
select
查询的内容
from
表1 inner join 表2
on 关联关系
and 筛选条件
- 语法：
-
-
外连接：查询基表中的所有数据、关联表中有关联关系的数据
-
左外连接
- 语法：
select 查询列表
from
表1 left join 表2
on 关联关系
- 语法：
-
右外连接
- 语法：
select 查询列表
from
表1 right join 表2
on 关联关系
- 语法：
-
-
基表：展示所有内容的表
-
区分
- 左外连接中left关键字左侧的表称为基表
- 右外连接中right关键字右侧的表称为基表
子查询
-
理解：一个查询语句中内嵌另一个完整的查询语句，被嵌套的语句 称为子查询 或者内查询外面的语句称为主查询 或者外查询
-
语法：
select
查询列表
from 表
where (子查询) -
特点
- 子查询 必须写在小括号中
- 子查询 优先执行，主查询需要用到子查询的结果
- 子查询结果
-
单行子查询
- 结果只有一个
- 搭配的符号 = > < >= <= <>
-
多行子查询
- 结果多个
- 搭配的符号 in / not in
DCL
查看当前数据库系统中的用户
- select * from mysql.user;
常见新用户，默认没有权限
- create user ‘用户名’@‘%|local host本地’ identified by ‘密码’;
%:允许远程操作
分配权限
- grant 权限 on 库.表 to 用户;
- all：表示所有权限
*：表示所有库|所有表
查看用户权限
- show grants for 用户;
取消权限
- revoke 权限 on 库.表 from 用户;
删除用户
- drop user 用户;
事务
理解
- 一组预编译的sql语句要么全部执行成功、要么全部执行失败。
- 完成某个功能的整个过程为一个事务
- 同生共死：整个过程中如果出现一点错误，整个事务失败。
语法
-
开启事务
- begin|start transatcion
-
回滚事务
- rollback
-
提交事务
- commit
特性
-
原子性
- 事务座位一个完整单元、此单元中多条sql作为一个整体，要么全部执行成功、要么全部执行失败，作为一个单元密不可分
-
一致性
- 事务执行前和执行后状态一直、要么都成功，要么都失败
-
持久性
- 事务一旦提交数据将永久发生更改、不受其他因素干扰
-
隔离性
- 事务与事务之间相互隔离、互不影响
隔离级别：低--->高
-
读未提交
- read uncommitted
- 引发问题：脏读、不可重复读、幻读
-
读已提交（ oracle、sqlServer默认隔离级别 ）
- read committed
- 引发问题：不能重复读、幻读
-
可重复读（MySQL默认隔离级别）
- repeatble read
- 引发问题：幻读
-
串行化
- serializable
#脏读:读取到别的事务未提交的代码
#不可重复读:在一个事务中两次读取的结果不一致
#幻读:在重复读级别下我们不允许对表进行更新操作、但是允许对表insert操作、一个事务插入数据
#第二个事务无论如何也无法查询、第二个事务此时再进入插入发现违法主键约束仿佛出现了幻觉一样
实现事务过程
-
查看当前数据库事务的提交方式
- 将事务的提交方式设置手动：set autocommit=0
0：手动 1：自动
- 将事务的提交方式设置手动：set autocommit=0
-
查看当前系统的隔离级别
- select @@tx_isolation;
-
设置当前系统的隔离级别
- set global transaction isolation level 隔离级别;
视图
概念
- 视图是简化查询过程，提高数据库安全性的虚拟表。
- 视图中保存的只是一条select语句，保存的是视图的定义，并没有保存正在的数据，视图中的源数据都来自数据库表，数据库表称为基本表或基表，视图称为虚拟表
作用
- 防止未经许可的用户访问敏感数据，确保数据的安全性
- 封装sql语句，简化查询过程
- 视图可对用户屏蔽真实表结构
语法
-
创建视图
- create view 视图名称 as select语句;
-
查询视图
- select * from 视图名;
-
查询视图结构
- desc 视图名;
-
查看创建视图的文本信息
- show create view 视图名;
-
修饰视图
- alter view 视图名称 as sql语句;
-
删除视图
- drop view 视图名称;
注意
- 修改视图会影响真实表、真实表修改也会影响视图
- 视图主要为了简化查询、一般不进行增删改
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
