DQL数据查询语言

1. 普通查询

use 表名; #先打开数据表
select 查询列表 from 表名;

查询列表可以是：表中字段、常量值、表达式、函数；查询结果是虚拟表格，不能保存
查询常量值、表达式、函数，查询后的字段名（列名）就是其本身，字符型和日期型的常量值必须用单引号，数值型不需要

别名：区分重名的字段名；如果别名有特殊字符建议加上双引号或单引号

-- 方式1：使用as 
select 表达式 as 列名， 表达式 as 列名 from 表名;
-- 方式2:使用空格 
select 表达式 列名， 表达式 列名 from 表名;

去重

#查询a不重复或者b不重复的并集
select distinct 查询字段 from 表名 select distinct a,b from table；

MySQL中的+
- select 100+90; 两个操作数均为数值型，做加法运算
- select '123'+90;有一方为字符型，则试图将字符型数值转换成数值型，若转换成成功则进行加法运算;若失败，则将字符型数值转为0
- select null+90：如果其中一方为空值，则返回null

连接查询：连接的字段不能为null值，否则将返回null

-- 返回字段值为空的行
select ifnull (字段名，为空的返回值) as 别名
-- 可以将属性123 连接成完整的字符串
select concat ('属性1'，'属性2'，'属性3') as 别名；

排序查询

-- order by 一般放在查询语句最后，limit语句除外
select 查询列表
from 表
where 筛选条件
order by 排序列表（支持多个排序，放在前面的就先排） asc/desc;(升序或降序,默认升序)

2. 条件查询

select 查询列表 from 表名 where 筛选条件;

按条件表达式筛选：> < = != >= <= <=>(安全等于，既可以判断null值，也可判断普通数值) <>(不等于的意思)
按逻辑表达式筛选，用于连接条件表达式：&& || ！ and or not
模糊查询
- like：一般和通配符搭配使用，可以判断字符型或数值型
- %：任意多个字符，包含0个字符
- _：任意单个字符
- \：转移字符，忽略当前位
- 连接条件
  - （not）between and ：在什么之间
  - in：判断某字段的值是否属于in列表中的某一项，提高语句简洁度，列表值类型必须统一或兼容
  - is （not） null ：当=，<>不能用于判断值时，用is not null可以判断null值

3.常见函数

单行函数 concat(连接函数)、length（长度函数）、ifnull（判断函数）等

字符函数

-- 获取参数值的字节个数  
select length('张haha');   值为7，一个汉字占三个字节
-- 拼接字符串  
 select concat(a,'+',b);    值为a+b
-- 大小写upper，lower  
select upper ('a');     值为A
-- 截取字符串，索引从1开始，截取从指定索引字符长度的字符substr=substring  
select substr('lisihid',2); 值为isihid  
select substr('lisihid',2,3); 值为isi  
-- 返回子串第一次出现的索引，如果找不到返回0  
select instr("adribaihroqwrino","ri"); 值为3  
-- 去掉前后重复的空格或者字符  
select trim('');去空格
select trim('a' from 'aaazhanng aa baoyu aaaa');返回zhanng aa baoyu  
-- 用指定的字符实现左填充指定长度,超出的话就截断，lpad,rpad
select lpad('asb',10,'*'); 值为*******asb  
-- replace替换函数
select replace('adbudf','bu','df'); --值为addfdf

数学函数

-- 四舍五入函数,绝对值四舍五入 
select round（-1.7880，2） # 值为1.79，保留两位小数
-- 向上取整，返回>=该参数的最小整数 ceil
-- 向下取整，返回<=该参数的最大整数 floor
-- 截断， truncate
-- 取余，mod(a,b)  值为a-a/b*b  
-- rand(),取随机数，返回0-1之间的值

- 日期函数
  - 返回当前系统日期+时间， now()
  - 返回当前系统日期，不包含时间， curdate()
  - 返回当前系统时间，不包含日期， curtime()
  - 获取指定的部分，年月日，小时，分钟 year,month,monthname（以英文形式返回月份）,date,hour,minute,second year(now())
  - str_to_date('9-19-1990','%m-%d-%Y') 将日期格式的字符解析转换数据库指定的格式，输出为1990-09-19 date_format('2019/2/27','%Y年%m月%d日') 将日期转换成字符，输出为2019年2月27日
- 其他函数
  - version() 数据库服务器版本
  - database() 数据库
  - user() 数据库用户
  - password（'加密字符'）；
  - MD5（'加密字符'）；流程控制函数
分组函数（组函数、统计函数）
- 功能：用作统计使用，又称为聚合函数或统计函数或组函数
- 分类：求和sum，平均值avg，最大值max，最小值min，计算个数count，datediff（exp1,exp2）求相差天数
- 特点
  - 参数支持类型，sum，avg一般支持数值型，其他类型不报错但不建议；
  - 其他分组函数类型都支持，count只计算不为null的字段
  - 所有分组函数都忽略null值
  - 可以和distinct搭配起到去重的效果
  - 和分组函数一同查询的字段要求是group by 后的字段
  - MYISAM存储引擎下 count( * ) 效率高; INNODB存储引擎下,count(*) 和 count(常量值)效率差不多，比count（字段）要高一些

4. 分组查询　

-- 查询列表要求是分组函数和group by 后出现的字段
select 分组函数, 列（要求出现在group by的后面）
from 表
【where 表里可以执行的筛选条件】
group by 分组的列表
【order by 子句】            
 having 分组后的筛选条件

分组查询中的筛选条件分为两类，能在分组前筛选的就放在前面where；后面放在having中
可以按表达式或函数分组
支持多个字段分组，多个字段之间用都逗号隔开，没有先后区别
可以添加排序，放在整个查询最后多表关系

5. 连接查询

左外 left【outer】，左边是主表右外：right【outer】，右边是主表全外：full【outer】，查询结果=内连接+表1有表2没有+表2有表1没有
```
select 查询列表
  from 表1 别名【连接类型】
  join 表2 别名 
  on 连接条件
 【where 筛选条件】
 【group by 排序列表】
```

6. 子查询

出现在其他语句内部的select语句，内查询；内部嵌套其他的select语句的查询，为外查询或主查询
分类：
- 按子查询出现的位置：select后面，只支持标量子查询；from后面，一般支持表子查询；where和having后面；exists后面，相关子查询，一般支持表子查询
- 按功能，子查询结果集的分类：标量子查询，结果集只有一行一列；列子查询，结果集只有一列但多行；行子查询，结果集有一行多列；表子查询，结果集多行多列
where或having后面：子查询放在小括号内；放在条件右侧；子查询的执行优先于主查询，主查询的条件用到子查询的结果；行子查询要求查询的字段有同样的筛选条件规律
from后面：将子查询结果充当一张表，要求必须起别名
exists后面，相关子查询：判断子查询结果有没有值，返回0或1

7. 分页查询

limit语句放在查询语句最后

设置页码page和条目数size

select 查询列表
from 表  
【join type join表2  
on 连接条件  
where 筛选条件  
group by 分组字段  
having 分组后的筛选  
order by排序后的字段】
--offset要显示条目的起始索引，从0开始，size表示要显示的条目个数，如果从第一条开始，可省略offset  
limit offset，size；

-- limit （page-1）*size，size

联合查询：union 关键字联合查询语句，查询的结果来自多个表，多个表之间没有关联，但查询的信息一致；查询的每一列类型和顺序最好一致；使用union关键字默认去重，使用union all 不去重

8. 常见约束

用于限制表中的数据，保证表中的数据准确可靠，可以对一个字段增加多个约束，用空格隔开
- NOT NULL，保证该字段值不能为空
- DEFAULT 默认约束，保证该字段有默认值　　　
- PRIMARY KEY，主键，保证该字段值具有唯一性，并且为非空值　　　
- UNIQUE，唯一，保证该字段值具有唯一性，可以为空　
  
  类型保证唯一性是否允许为空一个表中是否允许多个是否允许组合,多个列组合成一个键
  
  主键是不允许不允许允许
  
  唯一是允许允许允许
- CHECK， MySQL不支持，检查约束 check(a='1'or a='2')
- FOREIGN KEY，限制两个表的关系，保证该字段的值必须来自于主表的关联列的值，从表添加外键约束，用于引用主表的关联列值
  - 要求在从表设置外键关系
  - 表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  - 主表的关联列必须是一个key （一般是主键或唯一键）
  - 插入数据是先插入主表，在插入从表
  - 删除数据是先删除从表，再删除主表
```
-- 级联删除：删除主表的内容对应的从表全部删除  
ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (从表约束列) REFERENCES 主表（约束列） ON DELETE CASCADE;  
-- 级联置空：删除主表的内容对应的从表内容为空  
ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (从表约束列) REFERENCES 主表（约束列） ON DELETE SET NULL;  
```
约束类型
- 列级约束：六大约束语法上都支持，但外键约束没有效果；直接在字段名和类型后面追加约束类型，只支持默认，非空，主键，唯一，其他不生效
- 表级约束：除了not null和default，其他都支持；直接在各个字段的最后加，【constraint 约束名】约束类型（字段名）

创建表时添加约束

-- 列表约束对外键没有效果，只能在表级约束添加外键
create table 表(
  字段名1 字段类型 列表约束，
  字段名n 字段类型 列表约束，
  表级约束
)
-- 另一种方式
create index 索引名 on 表名（列名）；

修改表添加约束

-- 列级约束【位置：列的后面，支持的约束类型不包括外键，不能起约束名】
alter table 表名 modify column 字段名 字段类型 新约束;
-- 表级约束【位置：所有列的下面，默认和非空不支持，其他支持，可以起别名，但主键起别名没有效果】
alter table 表名 add【constraint 约束名】 约束类型（字段名） 【外键的引用】;

修改表时删除约束

-- 删除非空  
alter table 表名 modify column 字段名 字段类型 null；
-- 删除默认  
alter table 表名 modify column 字段名 字段类型；
-- 删除主键  
alter table 表名 drop primary key；  
-- 删除唯一  
alter table 表名 drop index 约束名；
-- 删除外键  
alter table 表名 drop foreign key 约束的外键名

9.标识列

标识列：自增长列，可以不用手动插入值，系统提供默认的序列值
- 标识列必须和一个key搭配，但不一定是主键；
- 一个表只能有一个标识列；
- 标识列的类型只能是数值型；
- 标识列可以通过set auto_increment_increment=增长步；也可以通过手动插入值设置增长起始值；

10.事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全不执行
存储引擎：在MySQL中的数据用各种不同的技术存储在文件或内存中；通过show engines来查看；在MySQL中用的最多的存储引擎有innodb,myisam,memory等，其中innodb支持事务，而myiasm/memory等不支持事务。
ACID属性
- 原子性（automicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
- 一致性（consistency）：事务必须使数据库从一个一致性状态到另一个一致性状态
- 隔离性（isolation）：一个事务的执行不能被其他事务干扰，一个事务的内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
- 持久性（durability）：一个事务一旦被提交，他对数据库中数据的改变是永久性的，接下里的其他操作和数据库故障不应对其有任何影响

默认为隐式（自动）事务：事务没有明显的开启和结束的标记，需要设置自动提交autocommit功能为禁用set autocommit=0；转为显示事务

-- 开启事务  
set autocommit=0；  
start transaction；-- 可选的  
--编写事务中的SQL语句支持的是DML语句(select/delete/insert)  
语句1；
语句n;
-- 结束事务  
commit；-- 提交事务  
rollback;
/* 回滚该事务,就是相当于如果没提交该事务，他就保存的是开始该事物的原始状态，常常与savepoint搭配，savepoint是设置节点或回滚点，该节点后的事务与rollback搭配相当于未提交的事务 */
rollback to 回滚点名；-- 回到回滚点

事务易产生的问题
- 脏读：对于两个事务t1,t2，t2更新了数据但没有提交，t1读取了这个数据，若t2回滚，t1读取的内容就是临时且无效的
- 不可重复读：对于两个事务t1,t2，t1读取了一个字段，然后t2更新了该字段，t1再次读取同一个字段值就不同了
- 幻读：对于两个事务t1,t2，t1从一个表中读取了一个字段，然后t2在该表中插入了一些新的行，之后如果t1再次读取同一个字段就会多几行
事务隔离级别
- read uncommitted：允许事务读取违背其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现
- read committed：只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可重复读和幻读问题仍然会出现
- repeatable read：允许读取已经被其他事务提交的变更，可以避免脏读，不可重复读，但幻读问题依然会出现
- serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可以避免，但性能十分低下
Oracle支持2种事务隔离级别：read committed和serializable，默认事务隔离级别：read committed
MySQL支持4种事务隔离级别，默认事务隔离级别为repeatable read
每启动一个MySQL程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@@tx_isolation 表示当前的事务隔离级别
查看当前数据库事务隔离级别：select @@tx_isolation;
设置当前MySQL连接的隔离级别：set transaction isolation level 级别;
设置数据库系统的全局隔离级别：set global transaction isolation level 级别

11. 视图

是一种虚拟表，和普通表一样使用

方便重用SQL语句；简化复杂的SQL操作，不必知道它的查询细节；保护数据，提高安全性

#创建视图  
create view 视图名【字段列表】
as  
查询语句；

#修改视图  
#方式一  
create or replace view 视图名  
as  
查询语句；  
#方式二  
alter view 视图名  
as  
查询语句；

#删除视图  
drop view 视图名，视图名...；
#查看视图  
desc 视图名；  
show create view 视图名；

不允许更新的视图
- 包含以下关键字的SQL语句：分组函数、distinct、group by、having、union或者union all
- 常量视图
- select 中包含子查询
- join语句，能修改但不能插入
- from 一个不能更新的视图，即select查询的是一个不能更新的视图
- where子句的子查询引用了from子句中的表

12.存储过程

一组预先编译好的SQL语句的集合，批处理语句，提高代码的重用性，简化操作，减少了编译次数并且减少了与数据库服务器的连接次数，提高效率

-- 1. 创建  
create procedure 存储过程名（参数列表）
begin  
    存储过程体（一组合法的SQL语句）  
end 结束标记 
-- 或
delimiter 结束标记
create procedure 存储过程名（参数列表）
begin  
    存储过程体（一组合法的SQL语句）  
end  结束标记

-- 2.调用  
call 存储过程名（实参列表）结束标记  

-- 3. 删除存储过程  
drop procedure 存储过程名  //一次只能删除一个，不能批量删除  

-- 4. 查看存储过程信息
show create procedure 存储过程名

不能修改存储过程中的SQL语句，可以有0个返回，也可有多个返回，适合做批量插入，批量更新
参数列表包含三部分 参数模式参数名参数类型 参数模式：默认参数模式为in
- IN:该参数可以作为输入，该参数需要调用方传入值
- OUT：该参数可以作为输入，该参数可以作为返回值
- INOUT：既可作输入，也可作输出，该参数既需要调用方传入值，也可以作为返回，先要定义输入的用户变量值
如果存储过程中仅仅只有一句话，begin end 可以省略
存储过程体中的每条SQL语句结尾必须加分号，存储过程体的结尾可以使用delimiter重新设置，使用时加上结束标记即可结束使用 delimiter 结束标记 //在客户命令端使用有效

13. 函数

一组预先编译好的SQL语句集合，理解成批处理语句，好处与存储过程一样，有且仅有一个返回，适合做处理数据后返回一个结果

-- 1. 创建  
    create function 函数名（参数列表）returns 返回类型  
    begin  
        函数体  
    return 值；
    end  
-- 2. 调用  
    select 函数名（参数列表）  
-- 3. 查看  
    show create function 函数名；  
-- 4. 删除  
    drop function 函数名；

参数列表包含两部分：参数名参数类型
函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数的最后也不会报错，但不建议
当函数体只有一句话，可以省略begin end
使用delimiter 语句设置结束标记

14. 触发器trigger

通过事件触发与其相关的存储过程

结构

监视地点 table
监视事件 insert/update/delete
时间 before/after

事件触发 insert/update/delete

-- 自定义结束符号
delimiter $  
-- 创建
create trigger 触发器名
-- 触发时间
before/after
-- 监视事件
(insert/update/delete) ...
-- 监视地点
on 表名
-- 行触发器
for each row  
-- 触发事件
begin 
    SQL语句，触发何种事件
end $

posted @ 2021-04-15 15:42 forever_fate 阅读(88) 评论(0) 收藏举报

刷新页面返回顶部

类型	保证唯一性	是否允许为空	一个表中是否允许多个	是否允许组合,多个列组合成一个键
主键	是	不允许	不允许	允许
唯一	是	允许	允许	允许

DQL数据查询语言

1. 普通查询

2. 条件查询

3.常见函数

4. 分组查询

5. 连接查询

6. 子查询

7. 分页查询

8. 常见约束

9.标识列

10.事务

11. 视图

12.存储过程

13. 函数

14. 触发器trigger

公告

4. 分组查询　