文章目录
数据库相关术语
- DB：数据库
- DBMS：数据库管理系统 database management system
- SQL：结构化查询语言 Structured Query Language
DBMS（数据库管理系统）的分类
- 基于共享文件系统的 DBMS （Access）
- 基于C/S 的DBMS （MySQL、Oracle、SQL server）
SQL 分类
- DQL 数据查询语言 Data Query Language
- DML 数据操作语言 Data Manipulation Language
- DDL 数据定义语言 Data Definition Language
- TCL 事务控制语言 Transaction Control Language
基础查询
- 查询字段
- 查询常量
- 查询表达式
- 查询函数（执行函数）
起别名
select 156*556 as result;
select 156*556 result;
select a as 姓名 from t1;
select a 姓名 from t1;
select a as "姓名" from t1;
查询去重
select distinct a as 姓名 from t1;
+号 数学运算
+号 在 mysql 只作为数学运算符 ：
- 当两个操作数都为数值型时，则做加法运算
- 当两个操作数一个为数值型，另一个为字符型时，则试图将字符型转换为数值型，如果转换成功，则做加法运算。若不成功，则将字符型转换为 0 再做加法运算
- 只要其中一个操作数为 null，则结果为 null
CONCAT 字符拼接 函数
select concat( a , b) from t1;
条件查询
- 按条件表达式筛选： < 、>、=、<>、<=、>=
- 按逻辑表达式筛选： &&、||、!、and、or、not
- 模糊查询： like、between and、in、is null
like 查询
% 百分号 表示 任意多个（含0）字符
_ 下划线 表示 单个字符
escape 指定 转义字符
select b from t1 WHERE b like 's$_%' ESCAPE '$';
between and 查询
select a from t1 WHERE a BETWEEN 1 and 10
in 查询
表示 要符合其中一个
in 列表中的值类型必须一致或兼容
不支持通配符
select a from t1 WHERE a in (1,2,3)
is null 查询
不能用 = null
也不能 is 常量
安全等于：<=>
select a from t1 WHERE a <=> null
select a from t1 WHERE a <=> 4156
ifnull 函数
ifnull(num,other) 若 num 不为 null 则返回 num，否则返回 other
order by
order by 子句 一般放在查询的最后，limit 子句除外
默认 升序（ASC）
select a from t1 ORDER BY a DESC
select a 姓名 from t1 ORDER BY 姓名 ASC
select a 姓名 from t1 ORDER BY length(姓名) ASC length 是一个函数
select a 姓名 from t1 ORDER BY a ASC,b DESC
函数
单行函数
concat、length、ifnull
分组函数（统计函数、聚合函数、组函数）
功能：做统计使用
字符函数
length 函数
select length("sft")
select length("是法国")
utf8 字符集下 一个汉字占 3 个字节
concat 函数
拼接字符
支持参数列表
upper、lower 函数
大小写转换
substr、substring 函数
子字符串
MySQL 下标从 1 开始
instr 函数
返回子串在字符串中的索引
trim 函数
过滤指定的字符串
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); --删除指定的首字符 x
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); --删除指定的首尾字符 x
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); --删除指定的尾字符 x
-> 'barx'
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
lpqd 函数
左填充 指定字符 到指定长度
rpad 函数
replace 函数
数学函数
round 函数
四舍五入
ceil 函数
向上取整
floor 函数
向下取整
truncate 函数
小数点后保留几位
mod 函数
取模
日期函数
now 函数
当前日期和时间
curdate 函数
当前日期
curtime 函数
当前时间
YEAR() MONTH() MONTHNAME() DAY() HOUR() MINITE() SECOND()
获取指定的部分
str_to_date 函数
str_to_date('10-1-2010','%m-%d-%Y')
按所给模式将字符串转换为 date
date_format 函数
将日期格式化为字符串
其他函数
VERSION()
DATABASE()
USER()
流程控制函数
可放在任何位置
if(expr,true,false)
case 要判断的字段或表达式
when 常量1 then 语句1
when 常量2 then 语句2
else 语句
end
case
when 条件1 then 语句1
when 条件2 then 语句2
else 语句
end
BEGIN
case
when 条件1 then 语句1
when 条件2 then 语句2
else 语句
end case
END
分组函数
sum() avg() max() min() count()
select sum(distinct salary) from t1;
count 函数
统计表中数据的行数
方法一：
select COUNT(*) from employees
方法二：
select COUNT(1) from employees
该方法相当于在当前表的每一列前面都加了一行 “1”，然后统计 1 的个数
在 INNODB 的数据引擎下，count(*)和count(1)的效率相差不大，但是相对于count(字段)的效率要高很多
不同引擎统计记录数的效率
MYISAM 引擎下，count(*) 效率最高，因为其设置了一个计数器 （ O(1) ）
INNODB 引擎下， count(*) 和 count(1) 的效率差不多，但比 count(字段) 要高一些 ，因为 count(字段) 要考虑null的情况
DATEDIFF 函数
计算两个日期的差
group by
select
from
where
group by
having
优先使用分组前筛选，即 where 子句
分组函数一般在 having 子句中使用
连接查询
按年代分类：
sql92 标准：仅支持内连接
sql99 标准（推荐使用）：内连接、外连接（左外连接和右外连接）、交叉连接
内连接
等值连接
即 交集
O (m*n)
sql92 语法
SELECT t1.a, t2.a , c
from t1,t2
where 连接条件
and 筛选条件
sql99 语法
SELECT t1.a, t2.a , c
from t1
[innner|left|right|full] join t2
on 连接条件
[innner|left|right|full] join t3
on 连接条件
where 筛选条件
group by
having 分组后条件
inner 可以省略
如果为表起了别名，则查询的字段就不能用原来的表名来限定
非等值连接
自连接
SELECT m.a,n.c
from t2 m,t2 n
WHERE m.a = n.a
外连接
左外连接
SELECT m.a,n.c
from t1 m
left (outer) join t2 n
on m.a = n.a
a c
1 sadf
2 dasf
3 dfgdsgh
4 <null>
5 <null>
右外连接
全外连接
交叉连接
即 笛卡尔乘积
子查询（内查询）
子查询：出现在其他（不止select）语句中的 select 语句
子查询优先于主查询执行
子查询语句 要包在括号内
分类：按结果集的行列数
-
标量子查询（结果集只有一行一列）
-
列子查询（结果集有多行一列）
-
行子查询（结果集有一行多列）
SELECT * FROM employees WHERE (employee_id,salary) = { SELECT MIN(employee_id),MAX(salary) FROM employees } -
表子查询（结果集有多行多列）
分类：按子查询出现的位置
select 后面 （只支持标量子查询）
from 后面（支持表 子查询）
where/having 后面（标量/列/行 子查询）
exists 后面（表子查询）
多行比较操作符
- IN/NOT IN
- ANY（SOME）
- ALL
分页查询
limit offset,size
如 limit (page -1)*size,size
联合查询
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
Employees_China:
E_ID E_Name
-------------------
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming
Employees_USA:
E_ID E_Name
-------------------
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
E_Name
---------
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
E_Name
----------
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill
插入语句
方式1
支持插入多行
支持子查询
insert into 表名 values (数据1，数据2，...),(数据1，数据2，...)
insert into t1 (SELECT * FROM t2 where a = 10)
方式2
不支持插入多行
不支持子查询
insert into 表名 set 列名1 =值1,列名2 =值2,...
修改语句
sql92 标准
UPDATE 表1 别名,表2 别名
set 列=新值
WHERE 连接条件
and 筛选条件
sql99 标准
UPDATE 表1 别名
inner|left|right join表2 别名
on 连接条件
set 列=新值
where 筛选条件
删除语句
sql92 语法
delete 表1别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件
sql99 语法
delete 表1别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件
没加 where子句 就全删了
如要清空表则可以使用 trancate：
truncate table 表
如果要删除的表中有自增长列，用 delete 删除后再插入数据，自增长列的值从断点开始；而用 truncate 删除后再插入数据，自增长列的值从 1 开始
delete 语句返回有几行受影响
truncate 语句没有返回值
delete 可以回滚
truncate 不可以回滚
DDL
库的管理
CREATE DATABASE [IF NOT EXISTS] b
RENAME DATABASE b new_b （已废弃）
ALTER DATABASE b CHARACTER SET gbk
DROP DATABASE b
表的管理
CREATE TABLE [IF NOT EXISTS] t3(
列名 类型 [UNSIGNED|ZEROFILL] [长度 约束]
列名 类型 [长度 约束]
a enum('a','b','c') 不区分大小写
b set('a','b','c') 不区分大小写
。。。
)
CREATE TABLE t3 LIKE t2 表的复制,仅仅复制表的结构
CREATE TABLE t3 SELECT *(column_names) FROM t2 复制结构和(部分)数据
CREATE TABLE t3 SELECT column_names FROM t2 where FALSE 复制表的部分结构,不复制数据
ALTER TABLE t3 CHANGE [COLUMN] old_column_name new_column_name [type]
ALTER TABLE t3 MODIFY COLUMN column_name new_type
ALTER TABLE t3 ADD COLUMN column_name column_type
ALTER TABLE t3 DROP [IF EXISTS] COLUMN column_name
ALTER TABLE t3 RENAME TO t4
DROP TABLE [IF EXISTS] t3
数据类型
- 数值型
- 整数：tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int/integer(4个字节)、bigint(8个字节)
- 小数
- 定点数：dec/decimal[(M=10,D=0)] (如果M>D，为M+2否则为D+2 个字节) M 表示该值的总共长度，D 为小数位
- 浮点数：float[(M,D)] (4个字节)、double[(M,D)] (8个字节)
- 字符型
- 短文本：char(M1=1) 固定长度效率高、varchar(M2 不可省略) 可变长度效率低 M为最多字符数 M1为0~ 255 M2为 0~65535
- 二进制短文本：binary、varbinary
- 长文本：text、blob（长的二进制数据）
- 日期型：date、datetime(8个字节)、timestamp(4个字节)、time、year
- 枚举型：enum(只能选取一个成员)、set(可选取多个成员)
DATETIME 取值范围 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP 取值范围 ‘1970-01-01 00:00:01’ UTC 到 '2038-01-19 03:14:07’UTC （准备到期了）
5.7 之后的版本（其实应该说5.6.5），在默认的秒精确度上，可以带小数，最多带6位小数
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
timestamp 和实际时区有关，更能反应处实际的日期，而datemine只能反应处插入时的当地时区
timestamp 的属性受 MySQL版本和 SQLMode 的影响
约束
六大约束：
- NOT NULL
- DEFAULT
- PRIMARY KET 保证唯一且非空
- UNIQUE
- CHECK 检查约束 mysql不支持
- FOREIGN KEY 外键约束 主表的关联列必须是一个key(主键、唯一)
列级约束：
六大约束在语法上都支持，但外键和 check 约束没有效果
表级约束：
除了非空、default，其他都支持
CREATE TABLE stuinfo(
id INT PRIMARY KEY ,
stuName VARCHAR(20) NOT NULL,
gender CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 18,
majorId INT,
CONSTRAINT [name] PRIMARY KEY(id)
CONSTRAINT [name] UNIQUE(seat)
CONSTRAINT [name] CHECK(gender='男' or gender ='女')
CONSTRAINT [name] FOREIGN KEY(majorId) REFERENCES tableName(id)
)
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
)
PRIMARY KEY 和 UNIQUE 的区别
主键和唯一 都保证 唯一性
主键不允许为 null ；唯一允许为 null
一个表中只能有一个主键；一个表中可以有多个 UNIQUE
主键能组合：PRRIMARY KEY(id,stuname) 不推荐
唯一也能组合：UNIQUE(id,stuname) 不推荐
修改表时添加/删除约束
添加
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键引用]
ALTER TABLE major MODIFY COLUMN column_name varchar(20) UNIQUE
ALTER TABLE major ADD COLUMN UNIQUE(column_name)
ALTER TABLE major ADD COLUMN PRIMARY KEY(column_name)
ALTER TABLE major ADD [CONSTRAINT name] COLUMN FOREIGN KEY(column_name) REFERENCES table_name(id)
删除
ALTER TABLE major MODIFY COLUMN column_name varchar(20) # 置空或写 null
ALTER TABLE major DROP PRIMARY KEY
ALTER TABLE major DROP FOREIGN KEY key_name
ALTER TABLE major DROP INDEX index_name
标识列（自增列）
默认从 1 开始
标识列的类型只能是 数值型
一个表只能有一个 标识列
标识列必须和一个 key搭配
CREATE TABLE a(
id INT AUTO_INCREMENT
)
ALTER TABLE a MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT
ALTER TABLE a MODIFY COLUMN id INT
INSERT INTO a VALUES(null)
INSERT INTO a VALUES(10) 重新设置起始值
INSERT INTO a VALUES(null)
SHOW VARiables LIKE 'auto_increment%'
SET auto_increment_increment=3 设置步长
事务
事务的 ACID 属性：
- Atomicity 原子性
- Consistency 一致性：事务必须使 数据库从 一个一致性状态变换到 另一个一致性状态
- Isolation 隔离性
- Durability 持久性：事务一旦被提交，它对数据库的改变就是永久性的
创建事务
隐式事务：事务没有显式的开启和结束的标记，如 insert、update、delete语句
SHOW VARIABLES LIKE 'autocommit'
SET autocommit = FALSE
显式事务：前提是先设置 禁用 自动提交
SET autocommit = FALSE
START TRANSACTION;
SELECT * FROM T1;
INSERT INTO T1 VALUES(56,'AGF')
COMMIT/ROLLBACK
SAVEPOINT
在事务种启用保存点
SAVEPOINT savepoint_name
。。。
ROLLBACK TO savepoint_name
事务并发问题
- 脏读
- 不可重复读
- 幻读
事务隔离级别
- READ UNCOMMITTED 读未提交
- READ COMMITTED 读已提交
可避免脏读 - REPEATABLE READ 可重复读
保证在事务期间对该字段的查询结果是相同的，可避免脏读、不可重复读 - SERIALIZABLE 串行化：一个一个事务顺序进行
Oracle 支持 READ COMMITTED 和 SERIALIZABLE ，默认为 READ COMMITTED
MySQL 支持以上4种，默认为 REPEATABLE READ
查看/更改事务隔离级别
SELECTt @@transaction_isolation;
SELECTt @@ix_isolation;
SET [SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
存储引擎（表类型）
SHOW ENGINES
innodb 支持事务
myiasm、memory 不支持事务
视图
视图含义：虚拟表
MySQL 5.1 版本新特性
是通过表动态生成的数据，只保存 sql逻辑，不保存查询结果
优点：
- 重用
- 简化，封装
- 保护数据（隐藏原表细节）
创建/使用/修改
CREATE VIEW view_name
AS
查询语句
SELECT */column_names FROM view_name [where 。。。] 像一个表一样使用一个视图
CREATE OR REPLACE VIEW view_name
AS
查询语句
ALTER VIEW view_name
AS
查询语句
DROP VIEW view_name
DESC view_name
SHOW CREATE VIEW view_name
可以插入/修改/删除数据 到视图，其影响会传递到 表（一般不进行插入/修改/删除）
不允许更新的视图有：
- 若视图是由两个以上基本表导出的，则此视图不允许更新
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE
- 若视图字段来自聚集函数，则此视图不允许更新
- 若视图定义中含有GROUP BY 子句，则此视图不允许更新
- 若视图中含有DISTINCT语句，则此视图不允许更新
- 若视图定义中含有嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，则此视图不允许更新
- 一个不允许更新的视图上定义的视图也不允许更新
级联删除/级联置空
添加级联删除
ALTER TABLE major ADD [CONSTRAINT name] COLUMN FOREIGN KEY(column_name) REFERENCES table_name(id)
ON DELETE CASCADE
添加级联置空
ALTER TABLE major ADD [CONSTRAINT name] COLUMN FOREIGN KEY(column_name) REFERENCES table_name(id)
ON DELETE SET NULL
变量
系统变量
SHOW [GLOBAL/SESSION] VARIABLES 默认 SESSION
SHOW VARIABLES LIKE '%shg%'
SELECT @@GLOBAL.变量名
SELECT @@GLOBAL.autocommit
SELECT @@GLOBAL.transaction_isolation
SET [GLOBAL/SESSION] 变量名 = 值
SET @@SESSION.变量名 = 值
-
全局变量
不能跨重启，要跨重启 只能修改配置文件 -
会话变量
自定义变量
用户变量
作用域相当于会话变量
SET @变量名=值
SET @变量名:=值
SELECT @变量名:=值
SELECT 字段 INTO @变量名 FROM 表
局部变量
作用域：在定义它的 begin end 中
声明语句只能放在begin end 中的第一句
DECLARE 变量名 类型
DECLARE 变量名 类型 DEFAULT 值
SET 变量名=值
SET 变量名:=值
SELECT @变量名:=值
SELECT 字段 INTO 变量名 FROM 表
SELECT 变量名
使用的时候用户变量前面加@，局部变量不用加@
存储过程
存储过程含义：一组编译好的sql语句集合
重用、简化、减少编译次数、减少和数据库的连接次数
创建
CREATE PROCEDURE procedure_name(参数模式 参数名 参数类型,)
BEGIN
END
参数模式:
- IN 该参数可以作为输入
- OUT 该参数可以作为输出（返回值）
- INOUT
默认为 IN
如果存储过程只有一句话，则 begin end 可以省略
存储过程中的每条语句必须用 分号 结尾
存储过程中语句结尾 可以使用 DELIMITER 设置
DELIMITER $
调用
CALL 存储过程名(实参列表);
删除
DROP PROCEDURE 存储过程名
查看
SHOW CREATE PROCEDURE 存储过程名
自定义函数
与存储过程的区别：存储过程可以有 0到多 个返回值，而函数只能有 1个 返回值
创建
CREATE FUNCTION 函数名(参数名 参数类型) RETURNS 返回类型
BEGIN
RETURN
END
调用
SELECT 函数名(实参列表)
查看
SHOW CREATE FUNCTION 函数名
删除
DROP FUNCTION 函数名
分支结构
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
[ELSE]
END IF
只能在begin end 中
循环结构
只能放在 begin end 中
可以省略标签，但使用循环控制语句（iterate、leave）时需要使用 标签
-
while 相当于 while
-
loop 相当于（有条件的）死循环
-
repeat 相当于 do while
-
iterate
相当于 continue -
leave
相当于 break
[标签:] WHILE 循环条件 DO
循环体
END WHILE [标签];
[label:] LOOP
循环体
IF exit_condition THEN
LEAVE label;
END IF;
END LOOP [label];
[label:] REPEAT
循环体
UNTIL 结束循环的条件 END REPEAT [label];
1161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
