mysql数据库基础语法与含义总结_请分别说明对应语法字段的含义和功能

数据库 DESC+表明 显示

    DISTINCT 		去重
    CONCAT('a','b','c') 	连接
    ESCAPE 			转义
    BETWEEN a AND b 	在a到b之间 就是大于等于a 小于等于b
    IN('a','b','c') 	abc为同类型 
	orderby by 		排序列表【asc|desc】 asc升序 desc降序 默认升序
	UPPER() 		变大写函数
	LOWER() 		变小写函数
	SUBSTR(a,b,c)	 	返回字段 a为字段 b为索引位置 c为字段长度
	INSTR（a，b）		返回位置 a为字段 b为子串
	TRIM(b from a)		去掉a中前后的b字符
	LPAD(a,b,c) 		用指定的字符实现左填充指定长度 向a的左边填充c字符 直到	总长度为b
	RPAD 			右填充
	REPLACE（a，b，c） 	替换 将字符a中的字符b替换为字符c
1
2
3
4
5
6
7
8
9
10
11
12
13
14

数学函数

ROUND（a，b） 		四舍五入 a为字符，b为小数点位数 
CELL（a）		向上取整 返回大于等于参数的最小整数
FLOOR(a)		向下取整 返回小于等于参数的最大整数
TRUNCATE（a,b）		截断 返回a中小数点后b位
MOD（a，b）		取余a%b
1
2
3
4
5

日期函数

NOW（） 		返回当前系统日期，不包含时间
CURTIME()		返回当前时间，不包含日期
YEAR（）		返回年
MONTH（） 		返回月
str_to_date: 		将日期格式的字符转换为指定格式的日期
STR_TO_DATE('9-13-1999','%m-%d-%Y')    1999-09-13
DATE_FORMAT('2018/6/6','%Y年%m月%d日')    2018年6月06日
1
2
3
4
5
6
7

流程控制函数

IF(a,b,c) 		如果a为true则返回b 为flase则返回c 
CASE使用方法一
CASE 要判断的字段或者表达式
WHEN 常量 THEN 要显示的值或语句
WHEN 常量 THEN 要显示的值或语句
else 要显示的值或语句
end
CASE使用方法二
CASE
WHEN 常量 THEN 要显示的值或语句
WHEN 常量 THEN 要显示的值或语句
WHEN 常量 THEN 要显示的值或语句
else 要显示的值或语句
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14

分组函数

功能：用作统计使用，又称为聚合函数或统计函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
1
2

分组查询

筛选
GROUP BY 分组的列表/表达式  分组列表 
HAVING   分组条件
1
2
3

连接查询（多表查询，当查询的字段来自于多个表）

等值连接 
非等值连接
自连接
1
2
3

sql99语法

SELECT 查询列表
FROM 表名 别名 【连接类型】
JOIN 表2 别名
on 连接条件
1
2
3
4

分类

内连接	inner
外连接
左外left【outer】	左边的是主表
右外right【outer】	右边的是主表
全外full【outer】	内连接的结果+表1中有的但表2中没有的+表2中有的但表1中没有的
交叉连接 cross 笛卡尔乘积
1
2
3
4
5
6

子查询 出现在其他语句中的select语句，称为子查询或内查询

分类 按子查询出现的位置
	select后面 标量子查询
	from后面 表子查询
	where 或者having 标量子查询（单行） 列子查询（多行） 行子查询
	exists(相关子查询) 表子查询
按结果集的行列数不同
	标量子查询（结果只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）
1
2
3
4
5
6
7
8
9
10

多行比较操作符

	IN/NOT IN 		等于列表中的任意一个
	ANY|SOME 		和子查询返回的某一个值比较
	ALL			和子查询返回的所有值比较
1
2
3

分页查询 要显示的数据，一页显示不全，需要分页提交sql请求

	limit offset，size；
	offset			要显示条目的起始索引（从0开始） 省略从0开始
	size			要显示的条目个数
1
2
3

联合查询 适用于多个表之间没有直接的连接关系，但是内容一致 比如国内用户和国外用户

	union 合并 将多条查询语句的结果合并成一个结果
1

数据操作语言

插入	insert
修改	update
删除	delete
1
2
3

插入语句

方式一 支持插入多行 支持子查询
	insert into 表名(列名，...)
	values(值1，值2，.....)
	值的类型要与列的类型一致或者兼容 省略列名默认为所有列
方式二
	insert inro 表明
	set 列名=值，...
1
2
3
4
5
6
7

修改语句

修改单表的记录
update 表名
set 列=新值，列=新值
where 筛选条件

修改多表的记录
92语法
update 表1 别名，表2 别名
set 列=值，....
where 连接条件
and 筛选条件；

99语法
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值，....
where 筛选条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

删除语句

方式一
delete from 表名 where 筛选条件
方式二
truncate（不能加条件，删除全表）
truncate table 表名;
				数据定义语言	库和表的管理
1
2
3
4
5
6

创建:create
修改:alter
删除:drop

一、库的管理

创建，修改，删除
创建数据库 create database （if not exists）库名；
修改数据库（基本不修改） 
改名字 rename database 库名 to 新库名；
改字符集 alter database 库名 character set 新库名
删除数据库
drop database if exists 库名;
1
2
3
4
5
6
7

二、表的管理

	创建，修改，删除
创建表
create table 表名(
	列名 列的类型【（长度） 约束】，
	列名 列的类型【（长度） 约束】，
	列名 列的类型【（长度） 约束】，
	列名 列的类型【（长度） 约束】，
	表级约束
)
1
2
3
4
5
6
7
8
9

修改表

①修改列名
alter table 表名 change column 旧列名 新列名 类型（约束）
②修改列的类型或者约束
alter table 表名 mopify 列名 类型（约束）
③添加新列
alter table 表名 add column 新列名 类型（约束）
④删除列
alter table 表名 drop column 列名
⑤修改表名
alter table 表名 rename to 新表名
1
2
3
4
5
6
7
8
9
10

删除表

	drop table 表名
1

表的复制

①仅仅复制表的结构
create table 新表名 like 表名；
②复制表的结构+表的数据
create table 新表名 select * from 表名
1
2
3
4

数据类型和约束

常见的数据类型

①数值型 默认有符号 类型后添加 unsigned 就是无符号
整型 Tinyint(字节1，范围0~255 或 -128~127)
     Smallint(字节2，范围0~65535 或 -32768~32767)
     mediumint（字节3）
     int integer（字节4）
     bigint（字节8）
小数： 
	定点数
	浮点数
②字符型
较短的文本	char（固定长度的字符 效率高）、varchar（可变长度的字符）
较长的文本	text、blob(较长的二进制数据)
binary和varbinary 用于保存较短的二进制
enum 枚举
set 集合
③日期型
date（4字节）
datetime（8字节 1000-01-01 00：00:00 不受时区影响）timestamp（4字节 1970 01 01 08 00 01 受时区影响）
time（3字节）year（1字节）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

常见的数据约束

一种用于限制表中的数据，为了保证数据的准确和可靠性
①NOT NULL 	非空
②DEFAULT  	默认 保证该字段有默认值
③PRIMARY KEY 	主键 保证该字段的值具有唯一性 非空
④UNIQUE   	唯一 保证该字段的值具有唯一性   可以为空
⑤CHECK    	检查（mysql中不支持）
⑥FOREIGN KEY   外键 限制两个表的关系 保证该字段的值必须来自主表的关联列的值（在从表中添加外键，用于引用主表中某列的值）
添加约束的时机	数据录入之前
	在创建表时
	在修改表时
列级约束	语法上都支持，但外键约束没有效果
表级约束	除了非空、默认 其他都支持
CONSTRAINT 约束名 PRIMARY KEY(为谁添加主键)
CONSTRAINT 约束名 UNIQUE(为谁添加唯一键)
 CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 表名（列名）
 CONSTRAINT 约束名 可以去掉
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

标识列

又称自增长列 可以不用手动的插入值，系统提供默认的序列值
类型后面添加AUTO_INCREMENT
SET auto_increment_increment=3 设置自增长度
1
2
3

事务控制语言

事务，一个或者一组sql语句组成一个执行单元，要么全部执行，要么全部不执行 如果中间发生以往，整个单元回滚
使用InnoDB存储引擎支持事务
原子性：事务是一个不可分割的工作单位
一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对兵发的其他事务是隔离的，并发执行的各个事务之间相互不干扰
持久性：一个事务一旦被提交，则会永久改变数据库的数据
SET autocommit=0;   开始事务
start transaction; 可选填写
语句1;
语句2;
语句3;
commit; 提交事务
1
2
3
4
5
6
7
8
9
10
11
12

rollback;回滚事务
savepoint 节点名; 设置保存点 rollback to 节点名 回滚到保存点

事务并发问题

脏读：对于两个事务T1,T2 T1读取了已经被T2更新但是还没有被提交的字段。
不可重复读：T1读取了一个字段，T2更新了该字段
幻读：T1从表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1在此读取同一个表，就会多出几行
1
2
3

四种隔离级别

①READ UNCOMMITTED（读未提交数据） 允许事务读取未被其他事务提交的变更、脏读，不可重复读和幻读的问题都会出现
②READ COMMITEN （读已提交数据）只允许事务读取已经被其他事务提交的变更。可避免脏读，但不可重复读和幻读问题仍然可能出现，oracle默认隔离级别
③REPEATABLE READ（可重复读）可以避免脏读和不可重复读，但幻读问题存在 Mysql默认隔离级别
④SERIALIZABLE（串行化）可以避免所有的并发问题，但性能十分低下

查看隔离级别
select @@tx_isolation;
设置隔离级别
当前连接隔离级别
set transaction isolation level 隔离级别
数据库系统全局隔离级别
set global transaction isolation level 隔离级别

delete（可以回滚） 和truncate（无法回滚）在事务使用时的区别
1
2
3
4
5
6
7
8
9
10
11
12
13
14

视图

含义：虚拟表，和普通表一样使用
①创建create view 视图名 AS（等于创建了一个虚拟表）
②修改视图
create or replace view 视图名 AS
③删除视图
deop view 视图名，视图名....
④查看视图
desc 视图名
show create view 视图名
⑤视图的更新
具备以下关键字的sql语句：分组函数、distinct、group by、having、union all 无法更新视图
1
2
3
4
5
6
7
8
9
10
11

变量

系统变量 全局加global 会话加session 默认session

查看所有的系统变量
SHOW GLOBAL[session] VARIABLES 
查看满足条件的部分系统变量
show global[session] variables like '条件'
查看指定的某个系统变量的值
select @@global[session] 系统变量名
为某个系统变量赋值
set global[session] 系统变量名 = 赋值
全局变量
	作用域 服务器每次启动为所有的全局变量赋初始值，针对所有连接有效，但不能跨重启
会话变量
	作用域 仅仅的对本连接有效
1
2
3
4
5
6
7
8
9
10
11
12

自定义变量

	用户变量
	作用域针对于当前连接有效，同于会话变量的作用域 可以在begin end中 也可以在begin end外
声明并初始化
set @用户变量名=值	
赋值
set @用户变量名=值
查看
select @用户变量名
局部变量
	作用域仅仅在他的begin end中有效
声明并初始化
declare 变量名 类型 default 值	
赋值
set 变量名=值
查看
select 变量名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

存储过程

存储过程 一组预先编译好的sql语句的集合，理解成批处理语句  适合做批量的插入，更新
①创建语法
create procedure 存储过程名（参数列表）
begin
	存储过程体
end 结束标记
参数列表包含三部分 参数模式 参数名 参数类型
参数模式：in（用in参数可以做为输入，需要传入值） out（做为输出，需要返回值） inout（可以做为输入，也可以做为输出）
存储过程提中每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用delimiter 重新设置
语法 delimiter 结束标记
②调用语法
call 存储过程名（实参列表）
删除存储过程
drop procedure 存储过程名
查看存储过程的信息
show create procedure 存储过程名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

函数

与存储过程的区别： 返回值 函数必须只有一个返回值 适合做处理数据后返回一个结果
①创建语法
create function 函数名（参数列表） returns 返回类型
begin
	函数体
end
参数列表 包含两部分 参数名，参数类型
函数体必须包含return值
函数的结束标记可以使用delimiter 重新设置
语法 delimiter 结束标记
②调用语法
select 函数名（参数列表）
查看函数
show create function 函数名
删除函数
drop function 函数名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

流程控制结构

分支结构

①if(表达式1，表达式2，表达式3)
如果表达式1成立，则返回表达式2的值，否则返回表达式3的值
②case结构
一般实现等值判断
case 变量|表达式|字段
when 要判断的值 then 返回的值1或者语句1
when 要判断的值 then 返回的值2或者语句2
when 要判断的值 then 返回的值3或者语句3
else 要返回的值n
end case
区间判断
case
when 要判断条件1 then 返回的值1或者语句1
when 要判断条件2 then 返回的值1或者语句2
when 要判断条件3 then 返回的值1或者语句3
else 要返回的值n
end case
③if结构实现多重分支
if 条件1 then 语句1；
elseif 条件2 then 语句2
应用在begin end中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

循环结构

iterate 结束本次循环，继续下一次
leave 结束当前所在循环
①while
【标签：】while 循环条件 do
循环体;
end while【标签】
②loop
【标签：】loop
循环体;
end loop【标签】
③repeat
【标签：】 repeat
循环体;
until 结束循环的条件
end repeat【标签】;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15