SQL笔记

DB数据库：database，存储数据的仓库 DBMS数据库管理系统：Database Management System，数据库软件，用于管理DB中的数据 SQL：Structure Query Language，与DBMS通信的语言 My SQL服务的启动和停止

• 通过命令行：net start 服务名、net stop 服务名
• 计算机-右击-管理-服务

My SQL服务的登陆和退出

• 登陆：mysql【-h 主机名-P端口号】-u 用户名-p密码
• 退出：exit 或 ctrl+C

常见命令（列=字段）

• 查看当前所有的数据库：show databases
• 打开指定的库：use 库名
• 查看当前库的所有表：show tables;
• 查看其他库的所有表：show tables from 库名；
• 创建表：create table 表名（列名 列类型，列名 列类型，。。。）
• 查看表结构：desc 表名

注释：

• 单行注释：#注释文字
• 单行注释：—注释文字
• 多行注释：/*注释文字*/

一、DQL语言 1、基础查询

• 基本句式：select 查询列表 from 表名；
• 查询全部字段：select *
• 查询函数：select version();
• 起别名：使用as/使用空格
• 去重：select distinct 查询列表
• 拼接：select contact（’列名’,’列名’,’列名’）as 结果
• 空白显示自定义：select IFNULL（列名，0）as XX
• 判断是否为null：is null，如果是返回1，不是返回0

TIP：

• 查询列表可以是：表中的字段、常量值、表达式、函数(字符型和日期型的常量值必须用单引号)
• 查询结果是一个虚拟的表格

2、条件查询

• 基本句式：select 查询列表 from 表名 where 筛选条件
• 条件表达式筛选：> < = <> >= <=
• 逻辑表达式筛选：逻辑运算符 and or not
• 模糊查询：like，between and，in，is null，is not null
• 安全等于<=>，可以判断 null值

TIP：

• 模糊查询一般和通配符搭配使用：%任意多个字符（包含0）、_任意单个字符
• 转译：\，like ‘_$_%’ escape’$'
• 模糊查询 between and包含临界值，临界值的顺序不可调换（between 100 and 120）
• in 列表的值类型必须一致或兼容
• =或<>不能用于判断null值，is null或is not null可以判断null值

3、排序查询

• 基本语法：select 查询列表 from 表 where 筛选条件 order by 排序列表【asc/desc】
• 字节长度排序：select length(查询列表) XX，查询列表 from表 order by length（查询列表）
• order by子句中可以支持单个字段、多个字段、表达式、函数、别名
• order by子句一般是放在查询语句的后面，limit子句除外

4、常见函数

• 基本语法：select 函数名（实参列表）from 表
• 单行函数：concat、length、ifnull等，包括字符函数、数学函数、日期函数、流程控制函数和其他
• 分组函数：统计函数、聚合函数

4.1单行函数 字符函数：

• length获取参数值的字节个数；concat拼接字符串；upper、lower
• substr、substring：截取从指定索引处后面所有字符 select substr(‘李莫愁爱上了陆展元’,7)out_put;———陆展元；；截取从指定索引处指定字符长度的字符select substr(‘李莫愁爱上了陆展元’,1,3)out_put;——李莫愁
• eg.姓名中首字符大写，其他字符小写然后用_拼接显示
• select concat(upper(substr(last_name,1,1)),’_’lower(substr(last_name,2)))out_put from employees;
• instr返回子串第一次出现的索引，如果找不到返回0
• trim：去除空格或重复字段 select trim( ‘张翠山’ ) as out_put; select trim(‘a’from’aaa张翠山aaaa’) as out_put;
• lpad用指定的字符实现左填充指定长度：select lpad(‘殷素素’,10,’*’) as out_put;
• rpad用指定的字符实现右填充指定长度：select rpad(‘殷素素’,12,’ab’) as out_put;
• replace替换：select replace(‘周芷若周芷若周芷若张无忌爱上了周芷若’,’周芷若’,’赵敏’) as out_put

数学函数：

• round四舍五入：select round（数值）、select round（数值，3）----保留小数位数
• ceil 向上取整：返回>=该参数的最小整数 select ceil(1.001);
• floor向下取整
• truncate截断（不进行四舍五入）
• rand获取随机数，返回0-1之间的小数
• mod取余：mod(a,b): a-a/b*b（整数除不尽的部分）

日期函数

• now：返回当前系统日期+时间 select now();
• curdate：返回当前系统日期 不包含时间 select curdate();
• curtime：返回当前系统时间 不包含日期
• 获取制定的部分，年，月，日，小时，分钟，秒：select year(now()) as 年
• str_to_date：将日期格式的字符转换成指定日期格式 str_to_date(‘9-13-1999’,’%m-%d-%y’)
• date_format：将日期转换成字符 date_format(‘2018/6/6’,’%y年%m月%d日’)
• datediff：求日期间差的天数 select datediff(2022-3-21,2022-2-12)
• monthname：以英文形式返回月
• TIP：
• %Y 四位年份、%y 2位年份、%m 2位月份、%c 月份、%d 补0日、%H 24制小时、%h 12制小时、%i 分钟、%s 秒

流程控制函数

• if：select if（函数，函数成立返回值，函数不成立返回值）
• case: case （要判断的字段或表达式，区间判断不+） when 常量1 then 要现实的值1或语句1；when 常量2 then 要现实的值2或语句2…else 要显示的值n或语句n;end
• eg.select salary 原始工资,department_id,case department_id when 30 then salary*1.1 when 40 then salary*1.2 else salary end as 新工资 from employees

4.2分组函数

• 用作统计使用，又叫聚合函数和统计函数
• 分类：sum、avg、max、min、count计算个数
• 特点：sum、avg一般用于处理数值型、max、min、count可以处理任何类型、以上函数均忽略null值
• 与distinct搭配：select sum(distinct字段)
• count：select count(字段)/count（*）—统计行数
• 和分组函数一同查询的字段要求是group by后的字段

5、分组查询（group by）

• 语法：可以按照字段、表达式、函数分组
• select 分组函数,列（出现在group by后面）
• from 表
• where 分组前的筛选
• group by 分组的列
• having 分组后的筛选
• order by子句
• TIP：
• 查询列表必须是分组函数和group by后出现的字段
• where：分组前筛选，针对原始表数据筛选，放在group by前使用
• having：分组后的筛选，针对分组后的结果集筛选，放在group by后
• 多个字段分组：将分组内容放在group by后面，用逗号隔开

6、连接查询

• 含义：当查询的字段来自多个表时，就会用到连接查询
• 分类：按年代分类（sql92标准、sql99标准：内连+左右外+交叉连接）、内连接（等值连接、非等值连接、自连接）、外连接（左外连接、右外连接、全外连接）、交叉连接

6.1 sql92等值连接

• 语法：select 字段1，字段2
• from表1 别名1，表2 别名2
• where 别名1.’字段1’=别名2.’字段2’
• and 筛选条件
• group by 分组字段
• having 分组后的筛选
• order by 排序字段
• 给表起别名：from 表名 别名（as可以省略），如果为表起了别名，则select字段就不能使用原名。
• 筛选条件：where and
• TIP:
• 多表等值连接的结果为多表的交集部分
• n表连接，至少要n-1个连接条件
• 一般需要为表起别名
• 可以搭配使用前面介绍的所有子句，比如排序、分组、筛选

6.2 sql92非等值连接

• where 表1.’字段1’between 表2.’字段2’and表2.’字段3’、>= <= <>
• 可以搭配使用前面介绍的所有子句，比如排序、分组、筛选

6.3 sql92自连接

• 把一张表当成多张表匹配查询

6.4 sql99 语法

• select 查询列表
• from 表1 别名
• 【连接类型】join 表2 别名 on 连接条件
• 【连接类型】join 表3 别名 on 连接条件
• 【where 筛选条件】
• 【group by 分组】
• 【having 筛选条件】
• 【order by 排序列表】
• 内连接：inner
• 外连接：左外 left 【outer】、右外 right 【outer】、全外：full【outer】
• 交叉连接：cross（笛卡尔乘积）
• 非等值连接：inner join 表N on 字段 between XX and XX
• 外连接：用于查询一个表中有，另一个表中没有的记录。
• TIP：外连接的查询结果为主表中的所有记录，如果从表中没有和它匹配的，则显示null
• 外连接查询结果=内连接结果+主表中有而从表没有的记录。
• 左外连接：left join左边的是主表；右外连接：right join 右边的是主表
• 全外连接=内连接的结果+表1中有但表2没有+表2中有但表1没有的

7、子查询 出现在其他语句中的select语句，成为子查询或内查询 内部嵌套其他select语句的查询，成为外查询或主查询 分类： 按子查询出现的位置：

• select后面：仅标量子查询
• from后面：支持表子查询（将子查询结果充当一张表，要求必须起别名）
• where或having后面（常见）：标量子查询、列子查询、行子查询
• exists后面：表子查询（结果1或者0）、标量、列子查询、行子查询

按结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集一般为多行多列）

7.1 where或having后面

• 标量子查询（单行子查询）
• 列子查询（多行子查询）
• 行子查询（多行多列）

TIP：

• 标量子查询一般搭配单行操作符使用：> < >= <= = <>
• 列子查询一般搭配多行操作符使用：
• in：等于列表中的任意一个
• any/some：和子查询返回的某一个值比较
• all：和子查询返回的所有值比较
• 行子查询：where (字段1、字段2、字段3)=（select 分组函数字段1、分组函数字段2、分组函数字段3 from 表）

8、分页查询

• 应用场景：当要显示的数据，一页显示不全，需要分页提交SQL请求
• 语法：
• select 查询列表
• from 表1 别名
• 【连接类型】join 表2 别名 on 连接条件
• 【连接类型】join 表3 别名 on 连接条件
• 【where 筛选条件】
• 【group by 分组】
• 【having 筛选条件】
• 【order by 排序列表】
• limit offset,size
• TIP：
• offset要显示条目的起始索引（起始索引从0开始）
• size要显示的条目个数
• 要显示的页数page,每页的条目数size：limit（page-1）*size,size

9、联合查询

• union 联合 合并：将多条查询语句的结果合并成一个结果
• 语法：
• 查询语句1
• union
• 查询语句2
• union
• ...
• 应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致
• 特点：要求多条查询语句的查询列数是一致的、多条查询语句的查询的每一列的类型和顺序最好一致、union关键字默认去重，如果使用union all可以包含重复项

二、DML语言（数据操作语言）

• 插入：insert
• 修改：update
• 删除：delete

1、插入语句

• 语法 1：insert into 表名（列名1，列名2…）values(值1，值2…)
• 注意：插入的值的类型要与列的类型一致或兼容、可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
• 语法 2：insert into 表名 set 列名=值，列名=值...

2、修改语句

• 修改单表的记录
• 语法：update 表名 set 列=新值，列=新值…where筛选条件；
• 修改多表的记录（补充）
• 语法（sql92）：update 表1 别名，表2 别名 set 列=值...where连接条件 and 筛选条件；
• 语法（sql99）：update 表1 别名 inner/left/right join 表2 别名 on 连接条件 set 列=值…where 筛选条件；

3、删除语句

• 单表删除：
• 方式1:delete from 表名 where 筛选条件
• 方式2:truncate table 表名（整张表删除）
• 多表删除：
• 语法（sql92）：delete 表1 别名，表2别名 from 表1 别名，表2 别名 where 连接条件 and 筛选条件
• 语法（sql99）：delete 表1的别名，表2的别名 from 表1 别名 inner/left/right join 表2 别名 on 连接条件 where 筛选条件；
• 区别：
• delete 可以加where条件，truncate不能加
• 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate从1开始
• truncate删除不能回滚，delete删除可以回滚

三、DDL语言（数据定义）

• 创建：create
• 修改：alter
• 删除：drop

1、库的管理

• 库的创建：
• 语法：create database （if not exists）库名
• 更改库的字符集：alter database 库名 character 字符集
• 库的删除：drop database （if exists）库名

2、表的管理

• 表的创建：creat table 表名（列名 列的类型【(长度)约束】，列名 列的类型【(长度)约束】，列名 列的类型【(长度)约束】，列名 列的类型【(长度)约束】）
• 表的修改：alter table 表名 add/drop/modify/change column 列名【列类型 约束】【first/after字段名】；
• 表的删除：drop table 【if exists】表名
• 通用写法：
• drop table if exists 旧表名；
• create table 表名（）；
• 细则：
• 修改列名：alter table 表名 change column 原列名 新列名 列类型；
• 修改列类型：alter table 表名 modify column 列名 新列类型；
• 添加新列：alter table 表名 add column 列名 列类型；
• 删除列：alter table 表名 drop column 列名；
• 修改表名：alter table 表名 rename to 新表名
• 表的复制：
• 仅复制表的结构：create table 新表名 like 旧表名
• 复制表的结构+数据：create table 新表名 select *from 旧表名
• 仅复制某些字段：create table 新表名 select 字段1、字段2 from 旧表名 where 0【1=2】

3、常见的数据类型 3.1、数值型

• 整型：tinyint 、samllint、mediumint、int/integer、biting
• 1 2 3 4 8
• 特点：
• 默认是有符号，如果想设置无符号，需要添加unsigned关键字
• 如果插入的数值超出了整型的范围，会爆out of range异常，并插入临界值
• 如果不设置长度，会有默认的长度
• 长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
• 浮点型：float(M,D)、double(M,D)
• 定点型：dec/decimal(M,D)

特点：

• M：整数部位+小数部位总长度
• D：小数部位
• 如果超出范围，则插入临界值
• M和D都可以省略
• 如果是decimal，则M默认为10，D默认为0
• 如果是float和double，则会根据插入的数值的精度来决定精度
• 定点型的精确度较高，如果要求插入的数值精确度较高如货币运算等则考虑使用
• 原则：所选择的类型越简单越好，能保存数值的类型越小越好

3.2、字符型：

• 较短的文本：char(M)、varchar(M)
• 较长的文本：text、blob(较大的二进制)
• 特点：
• M的意思是最大的字符数
• char的特点是固定长度的字符（耗费空间，效率高）可以省略M 默认为1、varchar是可变长度的字符（节省空间，效率低）不可以省略M
• 其他：
• binary和varbinary用于保存较短的二进制
• enum用于保存枚举
• set用于保存集合

3.3、日期型

• date只保存日期
• time只保存时间
• year只保存年
• datetime：保存日期+时间，占8个字节，范围1000-9999，不受时区影响
• timestamp：保存日期+时间，占4个字节，范围1970-2038，受时区影响

4、常见约束

• 一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
• not null：非空
• default：默认，保证该字段有默认值
• primary key：主键，用于保证该字段的值具有唯一性，并且非空
• unique：唯一，保证该字段的值具有唯一性，可以为空
• check：检查约束（mysql不支持）
• foreign key（列级约束没有效果）：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
• 表级约束除了非空和默认，其他都支持
• 添加列约束：直接在字段名和类型后面追加约束类型，只支持：默认、非空、主键、唯一
• 添加表约束：在各个字段的最下面【constraint 约束名】约束类型（字段名）
• 外键：要求再从表设置外键关系；主表的关联列必须是一个key（一般是主键或唯一）；插入数据时先插入主表再插入从表
• 修改表时添加约束：
• 列约束：alter table表名 modify column 字段名 字段类型 新约束
• 表约束：alter table 表名 add【constraint 约束名】约束类型（字段名）【外键的引用】
• eg添加外键：alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

5、标识列

• 又称为自增长列，可以不用手动的插入值，系统提供默认的序列值。
• 创建表时设置标识列 auto_increment;
• show variables like ‘%auto_increment%'
• 创建表时：create table tab_identity(id int unique auto_increment)
• 修改表时：alter table tab_identity modify column id int primary key auto_increment;

TIP：

• 标识列不一定和主键搭配，但必须是一个key
• 一个表最多只能有一个标识列
• 标识列类型只能是数值型
• set auto_increment_increment=3；设置步长，通过手动插入值设置起始值。

四、TCL语言

• 事务的介绍：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行要么全部不执行
• 事务的特性：
• ACID：原子性（一个事务不可再分割，要么都执行要么都不执行）、一致性（一个事务执行会使数据从一个一致状态切换到另一个一致状态）、隔离性（一个事务的执行不受其他事务的干扰）、持久性（一个事务一旦提交则会永久的改变数据库的数据）
• show variables like ‘autocommit'

1、事务的创建：

• 隐式事务：事务没有明显的开启和结束的标记，比如insert、update、delete语句
• 显式事务：事务具有明显的开启和结束的标记。前提：必须先设置自动提交功能为禁用
• 步骤：
• 开启事务：set autocommit=0;【start transaction（可选）】
• 编写事务：select insert update delete（sql语句）
• 结束事务：commit 提交事务 rollback 回滚事务

2、隔离级别

• 查看当前隔离级别：select @@tx_isolation
• 设置当前sql连接的隔离级别：set transaction isolation level read committed
• 设置数据库系统的全局隔离级别：set global transaction isolation level read committed
• read uncommitted：脏读、不可重复读、幻读
• read committed：不可重复读、幻读（oracle默认）
• repeatable read：幻读（mysql默认）
• serializable：都没有
• savepoint保存点的使用：
• set autocommit=0
• start transaction
• delete from account where id=25
• savepoint a
• delete from account where id=19
• rollback a

3、视图

• 虚拟表，和普通表一样使用
• 语法：creat view 视图名 as 查询语句
• 修改 1：create or replace view 视图名 as 查询语句
• 修改 2：alter view 视图名 as 查询语句
• 删除：drop view 视图名，视图名...
• 插入：insert into 视图名 values(字段1，字段2...)
• 修改：update 视图名 set 字段1=‘XX’ where XX
• 删除：delete from 视图名 where 限制条件
  • 具备以下特点的视图不允许更新：包含分组函数distinct、group by、having、union、select中包含子查询、join、where子句的子查询引用了from子句中的表

五、变量

• 系统变量：全局变量、会话变量
• 自定义变量：用户变量、局部变量
• 查看系统变量：show global/session variables
• 查看满足条件的部分系统变量：show global/session variables like ‘%char%’
• 查看指定的某个系统变量的值：select @@global/session.系统变量名
• 为某个系统变量赋值：
• set global/session 系统变量名=值
• set @@global/session.系统变量名=值
• 自定义变量使用步骤：声明、赋值、使用（查看、比较、运算）
• 声明并初始化
  • set @用户变量名=值；
  • set @用户变量名:=值
  • select @用户变量名:=值
• 赋值（更新用户变量的值）
  • set @用户变量名=值；
  • set @用户变量名:=值
  • select @用户变量名:=值
  • select 字段 into 变量名 from 表
• 使用
  • select @用户变量名
• 局部变量：仅仅在定义它的begin end中有效
• 声明：
  • declare 变量名 类型
  • declare 变量名 类型 default 值；
• 赋值
  • set 局部变量名=值；
  • set 局部变量名:=值
  • select @局部变量名:=值
  • select 字段 into 局部变量名 from 表
• 使用
  • select 局部变量名
• 存储过程
• 一组预先编译好的sql语句的集合，理解成批处理语句
• 创建语法
  • create procedure 存储过程名（参数列表）
  • begin
  • 存储过程体（一组合法的sql语句）
  • end
• 注意：
  • 参数列表包含三部分：参数模式、参数名、参数类型 eg. in stuname varchar(20)
  • 参数模式：in、out、inout（既可输入又可输出）
  • delimiter $（结束符号）
• 调用语法
  • call 存储过程名（实参列表）；
• 删除存储过程：drop procedure 存储过程名
• 查看存储过程的信息：show create procedure 存储过程名
• 函数
• 一组预先编译好的sql语句的集合，理解成批处理语句
• 区别：存储过程可以有0个或多个返回，适合做批量插入更新、函数有且仅有1个返回，适合做数据处理后返回1个结果
• 创建语法：
  • create function 函数名(参数列表) returns 返回类型
  • begin
  • 函数体
  • end
• 参数列表包括两部分：参数名 参数类型
• 函数体：肯定会有return语句
• 使用delimiter语句设置结束标记
• 函数体中仅有一句话，可以省略begin end
• 调用语法
  • select 函数名（参数列表）
• 查看函数：show create function 函数名
• 删除函数：drop function 函数名
• 流程控制结构
• 顺序结构：程序从上往下依次执行
• 分支结构：程序从两条或多条路径中选择一条去执行
• 循环结构：程序在满足一定条件的基础上，重复执行一段代码
• 分支结构：
• if函数：实现简单的双分支
  • 语法if(表达式1，表达式2，表达式3)
  • 执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
• case结构：
• 等值判断
  • case 变量/表达式/字段
  • when 要判断的值 then 返回值1
  • when 要判断的值 then 返回值2
  • ...
  • else 要返回的值n
  • end
• 区间判断
  • case
  • when 要判断的条件1 then 返回值1
  • when 要判断的条件2 then 返回值2
  • ...
  • else 要返回的值n
  • end
• if结构：实现多重分支
• 语法：只能应用在begin end中
  • if 条件1 then 语句1；
  • else if 条件2 then 语句2；
  • ...
  • 【else 语句n;】
  • end if
• 循环结构
• 分类：while、loop、repeat
• literate 结束本次循环，继续下一次
• leave 结束当前所在的循环
• while 语法（先判断后执行）
  • 【标签：】while 循环条件 do
  • 循环体
  • end while【标签】；
• loop语法：可以模拟简单的死循环
  • 【标签：】loop
  • 循环体；
  • end loop【标签】；
• repeat语法（先执行后判断）
  • 【标签：】repeat
  • 循环体；
  • until 结束循环的条件
  • end repeat【标签】；