MySQL基础版

最近在工作中主要是测试SQL语句，所以看了一下SQL语句的语法

1. select 查询列表 from 表名
   查询列表可以是：表中的字段、常量值、表达式、函数
   查询的结果是一个虚拟的表格
2. 起别名可以用as,也可以用空格，如果别名为关键字或者含有空格，可以加单引号
3. 去重可以用distinct，比如

   select distinct department_id from employees;
   

4. mysql中的+号只能表示运算符
   如果操作数一方为字符型，则试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算，如果失败，在将字符型数值转换成0；只要其中一方为null，则结果为null
5. concat可以用来连接字符即字符串
6. 模糊查询：like, between and, in, is null | is not null
   like：和通配符搭配使用：%任意多个字符，包含0个；_任意单个字符
   between and 包含临界值
7. order by可以接多个排序条件
   select * from employees order by salary ASC，employee_id DESC #先按工资升序，再按员工编号降序
   ASC代表的升序，DESC代表的是降序，如果不写，默认的是升序
   order by子句中可以支持单个字段，多个字段，表达式，函数，别名
   order by子句一般放在查询语句的最后面，limit子句除外
8. 常见函数
   8.1 字符函数
   length，concat，upper，lower
   substr(substring) — substr(‘supengfei’,7)截取从指定索引处后面所有字符，substr(‘supengfei’,1,3)截取从指定索引处指定字符长度的字符，MySQL索引从1开始
   instr(‘supengfei’,‘peng’),返回子串第一次出现的索引，如果没找到返回0
   trim(’ su ‘),删除首位的空格；trim(‘aa’ from ‘aaaaaaasupengfeiaaaaaaa’),删除首位的aa
   lpad,rpad(‘su’,12,’’)，用指定的字符实现左填充或右填充指定长度
   replace(‘supengfeisupengfeisupengfei’,‘su’,‘li’);
   8.2 数学函数
   round 四舍五入
   ceil 向上取整
   floor 向下取整
   truncate 截断
   mod 取余 mod(a,b)=a-a/bb
   8.3 日期函数
   now() 返回当前系统日期+时间
   curdate() 返回当前系统日期，不包含时间
   curtime() 返回当前时间，不包含日期
   year(),month()等
   8.4 分组函数
   sum，avg一般用于处理数值型，max，min，count可用处理任何类型，以上均忽略null；可以和distinct搭配实现去重，一般使用count( * )用作统计行数，和分组函数一同查询的字段要求是group by后的字段
   注意：查询列表必须特殊，要求是分组函数和group by后出现的字段
   特点：
   
   分组函数做条件肯定是放在having子句中
   能用分组前筛选的，就优先考虑使用分组前筛选
   在MYISAM存储引擎下，count( * )的效率高
   INNODB存储引擎下，count( * )和count(1)的效率差不多，比count(字段)要高一些
9. 连接
   9.1 SQL92标准
   9.1.1 等值连接：
   多表等值连接的结果为多表的交集部分
   n表等值连接，至少需要n-1个连接条件
   多表的顺序没有要求
   一般需要为表起别名
   可以搭配排序、分组、筛选使用
   9.2 SQL99语法
   9.2.1 语法：
   select 查询列表
   from 表1 别名 【连接类型】
   join 表2 别名
   on 连接条件
   【where 筛选条件】
   【group by 分组】
   【having 筛选条件】
   【order by 排序列表】
   9.2.1 分类：
   内连接：inner
   外连接：左外 left 【outer】、右外 right 【outer】，全外 full 【outer】
   交叉连接： cross
   9.2.3 内连接
   分类：等值、非等值、自连接
   特点：
   添加排序，分组，筛选
   inner可以省略
   筛选条件放在where后面，连接条件放在on后面
   inner join连接和SQL92语法中的等值连接效果是一样的，都是查询多表的交集
   9.2.4 外连接
   应用场景：用于查询一个表中有，一个表中没有的记录
   特点：
   （1）外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null，外连接的查询结果=内连接结果+主表中有而从表中没有的记录
   （2）左外连接，left join左边的是主表，右外连接，right left右边的是主表
   9.2.5 全外连接=内连接+表1中有但表2中没有+表2中有但表1中没有的
   9.2.6 交叉连接：笛卡尔集
   10 子查询
   10.1 分类：
   （1）按子查询出现的位置：
   select后面：仅仅支持标量子查询
   from后面：支持表子查询
   where或having后面：标量子查询、列子查询、行子查询
   exists后面：表子查询
   （2）按结果集的行列数不同：
   标量子查询：结果集只有一行一列
   列子查询：结果集只有一列多行
   行子查询：结果集只有一行多列
   表子查询：结果集一般为多行多列
   10.2 where或having后面：
   标量子查询（单行子查询）
   列子查询（多行子查询）
   行子查询（多列多行）
   特点：
   子查询放在小括号内
   子查询一般放在条件的右侧
   标量子查询，一般搭配着单行操作符使用（>,<,>=,<=,<>）
   列子查询，一般搭配着多行操作符使用，in，any/some，all
   【补充】
   多行子查询：in/not in 等于列表中的任意一个；any/some 和子查询返回的某一个值比较；all 和子查询返回的所有值比较
   10.3 select后面：
   仅仅支持标量子查询
   10.4 from后面将子查询充当一张表，要求必须起别名
   10.5 exists后面：
   exists(完整的查询语句)，结果：0或1
   11 分页查询
   应用场景：当要显示的数据，一页显示不全，需要分页提交SQL请求
   语法：
   select 查询列表 （7）
   from 表 （1）
   【join type join 表2 （2）
   on 连接条件 （3）
   where 筛选条件 （4）
   group by 分组字段 （5）
   having 分组后的筛选（6）
   order by排序的字段】 （8）
   limit 【offset，】 size； （9）
   offset 要显示条目的起始索引（起始索引从0开始）
   size 要显示的条目个数
   12 联合查询
   union：将多条查询语句的结果合并成一个结果
   语法：
   查询语句1
   union
   查询语句2
   union
   ……
   应用场景：
   要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
   特点：
   要求多条查询语句的查询列数是一致的
   要求多条查询语句的查询的每一列的类型和顺序最好一致
   union关键字默认去重，如果用union all可以包含重复项
   13 插入语句：insert into
   方式一：insert into 表名（列名，……） values（值1，……），不可以为null的列必须插入值，列的顺序可以交换，列数和值的个数必须一致，可以省略列名，默认所有列且列的顺序和表中列的顺序一致
   方式二：insert into 表名 set 列名=值，列名=值，……
   方式一支持同时插入多行，用逗号隔开，方式二不支持；方式一支持子查询，方式二不支持
   14 更新（修改）：update
   修改单表的记录：update 表名 set 列=新值，列=新值，……where 筛选条件
   修改多表的记录：
   92语法：update 表1 别名，表2 别名 set 列=值，…… where 连接条件 and 筛选条件
   99语法：update 表1 别名 inner | left | right join 表2 别名 on 连接条件 set 列=值，…… where 筛选条件
   15 删除语句：delete，truncate
   单表的删除：delete from 表名 where 筛选条件
   多表的删除：
   92语法：delete 表1 别名，表2 别名 from 表1 别名，表2 别名 where 连接条件 and 筛选条件
   99语法：delete 表1的别名，表2 的别名 from 表1 别名 inner | left | right join 表2 别名 on 连接条件 where 筛选条件
   假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始
   truncate删除后没有返回值，delete删除有返回值
   truncate删除不能回滚，delete删除可以回滚
   16 DDL：数据定义语言，库和表的管理
   16.1 库的管理
   创建、修改、删除
   16.2 表的管理
   创建、修改、删除
   创建：create
   修改：alter
   删除：drop
   16.3 库的创建
   create datebase 库名
   create database 【if not exists】 books；
   16.4 库的修改
   rename database 旧库名 to 新库名 // 更改库名
   alter database books character set gbk; // 更改库的字符集
   drop database 库名； //删除库
   drop database 【if exists】books；
   16.5 表的创建
   create table 表名（列名 列的类型 【（长度）约束】，列名 列的类型 【（长度）约束】，列名 列的类型 【（长度）约束】……列名 列的类型 【（长度）约束】）；
   16.6 表的修改
   修改列名
   alter table 表名 change column 旧列名 新列名 类型
   修改列的类型或约束
   alter table 表名 modify column 列名 新类型
   添加新列
   alter table 表名 add column 列名 类型
   删除列
   alert table 表名 drop column 列名
   修改表名
   alter table 旧表名 rename to 新表名
   16.7 表的删除
   drop table if exists 表名
   16.8 表的复制
   16.8.1 仅复制表的结构
   create table 新表名 like 旧表名
   16.8.2 复制表的结构+数据
   create table 新表名 select * from 旧表名
   16.8.3 只复制部分数据
   create table 新表名 select id，au_name from 旧表名 where nation=‘中国’；
   17 常见的数据类型
   17.1 数值型：整型、小数（定点数，浮点数）
   17.2 字符型：短文本：char、varchar，较长的文本：text、blob（较长的二进制数据）
   17.3 日期型：
   17.4 整型：tinyint、smallint、mediumint、int/integer、bigint
   如果不设置有无符号，默认是有符号，如果想设置无符号，需要添加unsigned 关键字
   如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
   如果不设置长度，会有默认的长度
   长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
   17.4.1 设置有无符号：

   drop table if exists tab_int;
   create table tab_int (t1 INT,t2 INT UNSIGNED);
   

17.5 小数
浮点型：float（M,D），double（M,D）
定点型：dec（M,D），decimal（M,D）
M：整数部位+小数部位
D：小数部位
如果超过范围，则插入临界值
M和D都可以省略，如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
17.6 字符型

17.7 日期型
date、datetime、timestamp、time、year
timestamp和实际时区有关，更能反映实际的日期，而datatime则只能反映出插入时的当地时区
18 约束
not null：非空，用于保证该字段的值不能为空
default：默认，用于保证该字段有默认值，比如性别
primary key：主键，用于保证该字段的值具有唯一性，并且非空
unique：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号
check：检测约束，MySQL中不支持
foreign key：外键，用于限制两个表的关系用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
添加约束的时机：创建表时，修改表时
约束的添加分类：列级约束（6大约束语法上都支持，但外键约束没有效果）、表级约束（除了非空、默认，其他的都支持）

create table 表名（字段名 字段类型 列级约束，字段名 字段类型，表级约束）

create table major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
	);
create table stuinfo(
	id INT PRIMARYKEY,
	stuName VARCHAR(20) NOT NULL,
	gender char(1) CHECK(gender='男' or gender='女')，
	seat INT DEFAULT 18,
	majorID INT FOREIGN KEY REFERENCES major(id)
	);

18.2 添加表级约束

create table stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender char(1)，
	seat INT,
	majorID INT,
	CONSTRAINT pk PRIMARY KEY(id),
	CONSTRAINT uk UNIQUE(seat),
	CONSTRAINT ck CHECK(gender='男' or gender='女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) 
	);

18.3 通用写法

create table if not exists stuinfo(
	id INT PRIMARYKEY,
	stuName VARCHAR(20) NOT NULL,
	gender char(1) CHECK(gender='男' or gender='女')，
	seat INT DEFAULT 18,
	majorID INT FOREIGN KEY REFERENCES major(id)，
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) 
	);

18.4 外键
要求在从表设置外键关系
从表的外键列的类型和主键的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是个key(一般是主键或唯一)
插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
18.5 修改表时添加约束
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】；
18.6 修改表时删除约束
删除非空约束
alter table stuinfo modify column stuname varchar(20) NULL;
删除默认约束
alter table stuinfo modify column age INT;
删除主键
alter table stuinfo DROP PRIMARY KEY；
删除唯一
alter table stuinfo DROP INDEX seat；
19 标识列：自增长列

create table if not exists stuinfo(
	id INT PRIMARYKEY AUTO_INCREMENT,
	stuName VARCHAR(20) NOT NULL
	);

19.1 标识列不是必须和主键搭配，但是需要是个key；一个表最多一个标识列；标识列的类型只能是数值型；标识列可以通过set auto_increment_increment=3来设置步长
20 事务
20.1 innodb支持事务，myisam不支持
20.2 显示事务：事务具有明显的开启和结束的标记，必须先设置自动提交功能为禁用
set autocommit=0；
步骤1：开启事务
set autocommit=0；
start transaction；可选的
步骤2：编写SQL语句
步骤3：结束事务
commit；提交事务
rollback；回滚事务
20.3 事务的隔离级别
脏读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段，之后，T2回滚，T1读取的内容就是临时且无效的
不可重复读：对于两个事务T1，T2，T1读取了一个字段，之后T2更新了该字段，之后，T1再读取同一个字段值就不同了
幻读：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，T1如果再次读取同一个表，就会多几行
read uncommitted（读未提交）：允许事务读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现
read committed（读已提交）：只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读可能出现
repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，但这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但是幻读依然存在
serializable（串行化）：确保事务可以多次从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但是性能十分低下
oracle默认的事务隔离级别为read committed
MySQL默认的事务隔离级别为repeatable read
20.4
查看当前的隔离级别：select @@tx_isolation
设置当前MySQL连接的隔离级别：set transaction isolation level read committed
设置数据库系统的全局的隔离级别：set global transaction isolation level read committed
20.5
savepoint a; 设置保存点
SQL语句1；
SQL语句2；
rollback to a; 回滚到保存点