MySQL基础
学习目标
一、为什么要学习数据库
二、数据库的相关概念
DBMS、DB、SQL
三、数据库存储数据的特点
四、初始MySQL
MySQL产品的介绍
MySQL产品的安装 ★
MySQL服务的启动和停止 ★
MySQL服务的登录和退出 ★
MySQL的常见命令和语法规范
五、DQL语言的学习 ★
基础查询 ★
条件查询 ★
排序查询 ★
常见函数 ★
分组函数 ★
分组查询 ★
连接查询 ★
子查询 √
分页查询 ★
union联合查询 √
六、DML语言的学习 ★
插入语句
修改语句
删除语句
七、DDL语言的学习
库和表的管理 √
常见数据类型介绍 √
常见约束 √
八、TCL语言的学习
事务和事务处理
九、视图的讲解 √
十、变量
十一、存储过程和函数
十二、流程控制结构
数据库基础
数据与信息
数据：描述事物的符号记录，是数据库存储的基本对象，文本、图表、图形、图像、声音、语言、视频；
信息：现实世界事物的存在方式或运动状态的反映；信息是被加工为特定形式的数据
信息特征：传递需要物质载体、获取和传递消耗能量；可感知、存储、压缩、加工、传递、共享、扩散、再生和增值；不随数据形式的变化而变化
数据库相关概念
1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件，是维护和管理数据库的软件，是数据库与用户之间的界面，用于管理DB中的数据。
注 功能：数据定义、数据操纵、数据库的运行管理、数据库的建立和维护
3、DBS：数据库系统，引入数据库后的计算机应用系统，包括软、硬件，由数据库、数据库管理系统、应用系统、数据库管理员构成
4、SQL:结构化查询语言，用于和DBMS通信的语言
数据库的好处
1.持久化数据到本地
2.可以实现结构化查询，方便管理
数据管理技术的发展阶段
1、人工管理阶段：数据不能保存在存储设备上，面向特定的应用程序；共享程度低、冗余度打、数据没有独立性；数据无结构
2、文件管理阶段：可长期存储在设备上，仅面向特定应用程序；共享程度低、冗余度大、数据无结构、独立性差
3、数据库系统阶段：数据结构化、共享程度高、冗余度小、易扩充、数据独立性高
数据库存储数据的特点
1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。
数据库的体系结构
外模式：数据库用户可以看见的局部数据库逻辑结构和特征，数据库用户的数据视图
外模式/模式映像：保证逻辑独立性
模式：数据库中全体数据逻辑结构和特征描述，所有用户公共数据视图
模式/内模式映像：保证物理独立性
内模式：数据物理结构和存储方式的描述，数据在数据库内部表示方式
数据模型
现实世界数据特征的抽象；
三要素：数据结构（静态特性）、数据操作（动态特性）、完整性约束
分类：层次模型、网状模型、关系模型；
概念模型
用于信息世界建模，根据需求分析所得到的E-R模型；
实体（entity）、属性（attitude）、域（domain）、码（key）、实体型（entity type）、实体集、
联系（relationship） 一对一联系（1:1）、一对多联系（1:n）、多对多联系（m:n）
实体-联系方法（E-R）
从现实世界抽象出实体和实体间的联系
关系数据模型
关系数据结构、关系操作集合、关系关系完整性约束；
关系：二维表；
元组（记录）：行；属性（字段）：列；
关系模式：关系描述；域：属性取值范围；
关键字/码：标识唯一元组的属性；候选关键字/候选码：唯一标识不同的元组的属性或组合；
主码（主键）：从候选码中挑选的一个；外码（外键）：不属于某个表的主码
分量：元组的一个属性值；主属性：包含在任何候选关键字中的各个属性；非主属性：不包含在任一候选码中的属性
所有属性都是原子的；元组、属性非排序；同一属性名下的诸属性值是同类型数据，来自同一域；关系没有重复元组；
属性必须有不同属性名；不同属性名来自同一个域
关系的完整性规则
实体完整性、参照完整性、用户自定义完整性
关系运算
传统集合运算（二元运算）：并∪、交∩、差－、笛卡尔乘积∧
专门关系运算：选择（限制）σ、投影π、连接
MySQL产品的介绍和安装
MySQL服务的启动和停止
方式一：计算机——右击管理——服务
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）
MySQL服务的登录和退出
登录：
方式一：通过mysql自带的客户端
只限于root用户
方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码
退出：
exit或ctrl+C
MySQL的常见命令
#1.查看当前所有的数据库
show databases;
#2.打开指定的库
use 库名
#3.查看当前库的所有表
show tables;
#4.查看其它库的所有表
show tables from 库名;
#5.创建表
create table 表名(
列名 列类型,
列名 列类型，
。。。
);
#6.查看表结构
desc 表名;
#7.查看服务器的版本
#方式一：登录到mysql服务端
select version();
#方式二：没有登录到mysql服务端
mysql --version 或 mysql --V
MySQL的语法规范
1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释:
单行注释：# 注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */
SQL的语言分类
- DQL(Data Query Language): 数据查询语言
select - DML(Data Manipulate Language): 数据操作语言
insert、update、delete - DDL(Data Define Language): 数据定义语言
create、drop、alter - T/DCL(Transaction/Data Control Language): 事务控制语言
commit、rollback、GRANT、REVOKE
SQL的常见命令
show databases; #查看所有的数据库
use 库名; #打开指定的库
show tables; #显示库中的所有表
show tables from 库名; #显示指定库中的所有表
create table 表名(
字段名 字段类型,
字段名 字段类型
); #创建表
desc 表名; #查看指定表的结构
select * from 表名; #显示表中的所有数据
SQL编程基础
常量
-
字符串常量
- 指单引号或双引号括起来的字符序列
- 分ASCII字符串常量和Unicode字符串常量
-
数值常量
- 分整型常量和实型常量
-
日期/时间常量
- 指用单引号表示日期/时间的字符串括起来构成
- 包括年、月、日/小时数、分钟数、秒数、微秒数，数据类型为DATE/TIME
-
布尔常量
- 包括TRUE和FALSE，其中（true代表1，false代表0）
-
NULL值
变量
系统自带变量
一、全局变量
作用域：针对于所有会话（连接）有效，但不能跨重启
SHOW GLOBAL VARIABLES; # 查看所有全局变量
SHOW GLOBAL VARIABLES LIKE '%char%'; # 查看满足条件的部分系统变量
SELECT @@global.var_name; # 查看指定的系统变量的值
# 为某个系统变量赋值
SET @@global.var_name=value; # 方法一
SET GLOBAL var_name=value; # 方法二
二、会话变量
作用域：针对于当前会话（连接）有效
SHOW SESSION VARIABLES; # 查看所有会话变量
SHOW SESSION VARIABLES LIKE '%char%'; # 查看满足条件的部分会话变量
# 查看指定的会话变量的值
SELECT @@var_name;
SELECT @@session.var_name;
# 为某个会话变量赋值
SET @@session.var_name=value;
SET SESSION var_name=value;
SET var_name=value;
自定义变量
一、用户变量
声明并初始化：
SET @user_var_name=value;
SET @user_var_name:=value;
SELECT @user_var_name:=value;
赋值：
方式一：一般用于赋简单的值
SET user_var_name=value;
SET user_var_name:=value;
SELECT user_var_name:=value;
方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;
使用：
select @变量名;
二、局部变量
声明：
declare 变量名 类型 [default 值];
赋值：
方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;
使用：
select 变量名
二者的区别：
| 作用域 | 定义位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话的任何地方 | 加@符号，不用指定类型 |
| 局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |
运算符与表达式
运算符
-
算术运算符
算术运算符 说明 + 加法运算 - 减法运算 * 乘法运算 / 除法运算，返回商 % 求余运算，返回余数 -
比较运算符
比较运算符 说明 = 等于 < 小于 <= 小于等于 > 大于 >= 大于等于 <=> 安全等于，不会返回UNKNOWN <>或!= 不等于 IS NULL 或 ISNULL() 判断是否为NULL IS NOT NULL 判断是否不为NULL LEAST 返回最小值 GREATEST 返回最大值 BETWEEN AND 判断值是否落在两个值之间 IN 判断值是否是IN列表中的任意一值 NOT IN 判断值是否不是IN列表中的任意一值 LIKE 通配符匹配 REGEXP 正则表达式匹配 -
逻辑运算符
逻辑运算符 说明 NOT 或 ！ 逻辑非 AND 或 && 逻辑与 OR 或 || 逻辑或 XOR 逻辑异或 -
位运算符
位运算符 说明 | 位或 & 位与 ^ 位异或 << 位左移 >> 位右移 ~ 位取反 -
优先级
表达式
流程控制结构
判断
-
if函数
-
语法：
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]… [ELSE statement_list] END IF -
特点：可以用在任何位置
-
-
case语句
-
语法：
情况一：类似于switch CASE case_expr WHEN when_value THEN 结果1或语句1(如果是语句，需要加分号) [WHEN when_value THEN 结果2或语句2(如果是语句，需要加分号)]… [ELSE 结果n或语句n(如果是语句，需要加分号)] END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要） 情况二：类似于多重if CASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) [WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)]… ELSE 结果n或语句n(如果是语句，需要加分号) END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要） -
特点：
可以用在任何位置
-
-
if elseif语句
-
语法：
IF 情况1 THEN 语句1; ELSEIF 情况2 THEN 语句2; ... ELSE 语句n; END IF;
- 特点：
只能用在begin end中！！！！！！！！！！！！！！！
-
-
三者比较：
应用场合
if函数 简单双分支
case结构 等值判断 的多分支
if结构 区间判断 的多分支
循环
-
LOOP语句
-
实现简单的循环，使系统能够重复执行循环结构内的语句列表；
-
语法
[loop_label:]LOOP statement_list [LEAVE label;] END LOOP [end label];-
如果不在statement_list中增加退出循环的语句，LOOP语句容易进入死循环；
-
statement_list表示需要循环执行的SQL语句，若由多条语句组成，每条语句用
;隔开。
-
-
-
REPEAT语句
-
实现一个带条件判断的循环结构
-
语法
[repeat_label]REPEAT statement_list UNTIL search_condition END REPEAT[repeat_label];- 先执行statement_list完毕后，都会对search_condition进行判断，如果TRUE循环终止，否则循环继续执行
-
-
WHILE语句
- 可以实现一个带条件判断的循环结构
-
语法：
[while_lable:]WHILE search_condition DO statement_list END WHILE [while_label];- 先对条件进行判断，如果为TRUE，才执行statement_list，否则终止执行
-
ITERATE语句
-
再次执行循环；
-
只能出现在LOOP语句、REPEAT语句、WHILE语句中；
-
语法
ITERATE label;
-
函数
常见内置函数
分组函数
- 聚合函数
| 聚合函数 | 解释 |
|---|---|
| MAX() | 查询指定列的最大值 |
| MIN() | 查询指定列的最小值 |
| COUNT() | 统计查询结果的行数 |
| SUM() | 求和，返回指定列的总和 |
| AVG() | 求平均值，返回指定列数据的平均值 |
特点：
- 以上五个分组函数都忽略null值，除了count(*)
- sum和avg一般用于处理数值型 max、min、count可以处理任何数据类型
- 都可以搭配distinct使用，用于统计去重后的结果
- count的参数可以支持：字段、常量值，一般放1 建议使用 count(*)
单行函数
- 数学函数
| 数学函数 | 解释 |
|---|---|
| ABS() | 返回原数的绝对值 |
| SQRT() | 返回原数的二次方根 |
| MOD() | 返回原数的余数 |
| CEIL()/CEILING() | 返回不小于参数的最小整数，即向上取整 |
| FLOOR() | 返回不大于参数的最小整数，值转化为一个BIGINT，即向下取整 |
| RAND() | 生成一个0~1的随机数 |
| ROUND() | 四舍五入 |
| SIGN() | 返回参数符号 |
| TRUNCATE() | 返回截取后的结果值 |
| POW()/POWER() | 返回参数次方的结果值 |
| SIN() | 返回原数的正弦值 |
| ASIN() | 返回原数的反正弦值 |
| COS() | 返回原数的余弦值 |
| ACOS() | 返回原数的反余弦值 |
| TAN() | 返回原数的正切值 |
| ATAN() | 返回原数的反正切值 |
| COT() | 返回原数的余切值 |
- 字符函数
| 字符函数 | 解释 |
|---|---|
| LENGTH()/length() | 计算字符串长度函数，返回字符串字节长度 |
| CONCAT()/concat() | 合并字符串，返回拼接后的字符串 |
| INSERT()/insert() | 替换字符串函数 |
| LOWER()/lower() | 将字符串中字母转化为小写 |
| UPPER()/upper() | 将字符串中字母转化为大写 |
| LEFT()/left() | 从左侧截取字符串，返回字符串左边的若干个字符 |
| RIGHT()/right() | 从右侧截取字符串，返回字符串右边的若干个字符 |
| TRIM()/trim() | 删除字符串左右两侧空格，LTRIM/RTRIM去左/右边空格 |
| REPLACE()/replace() | 替换函数，返回替换后的新字符串 |
| SUBSTRING() | 截取字符串，返回从指定位置开始指定长度的字符串 |
| REVERSE() | 反转字符串，返回与原字符串顺序相反的字符串 |
| LPAD()/RPAD() | 在原字符串左/右边填充参数字符串 |
| INSTR()/instr() | 返回子串第一次出现的索引 |
- 日期函数
| 日期函数 | 解释 |
|---|---|
| CURDATE()/CURRENT_DATE() | 返回当前系统的日期值 |
| CURTIME()/CURRENT_TIME() | 返回当前系统的时间值 |
| NOW()/SYSDATE() | 返回当前系统的日期和时间值 |
| UNIX_TIMESTAMP() | 获取UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数 |
| FROM_UNIXTIME() | 将UNIX时间戳转换为时间格式，与UNIX_TIMESTAMP()互为反函数 |
| MONTH() | 返回指定日期中的月份 |
| MONTHNAME() | 返回指定日期中对应月份的英文名称 |
| DAYNAME() | 返回指定日期中对应星期的英文名称 |
| DAYOFWEEK() | 返回指定日期中对应一周的索引位置值 |
| WEEK() | 返回指定日期事一年中第几周，返回值范围[0,52]/[1,53] |
| DAYOFYEAR() | 返回指定事一年中第几天，返回值范围[1,366] |
| DAYOFMONTH() | 返回指定日期事一月中第几天，返回值范围[1,31] |
| YEAR() | 返回年份，返回值范围[1970,2069] |
| TIME_TO_SEC() | 将时间参数转换为秒数 |
| SEC_TO_TIME() | 将秒数转换为时间，与TIME_TO_SEC()互为反函数 |
| DATE_ADD()/ADDDATE() | 向日期添加指定的时间间隔 |
| DATE_SUB()/SUBDATE() | 向日期减去指定的时间间隔 |
| ADDTIME() | 时间加法运算，返回原始时间与指定时间之和 |
| SUBTIME() | 时间减法运算，返回原始时间与指定时间之差 |
| DATEDIFF() | 返回两个日期的间隔，即参数1与参数2之差 |
| DATE_FORMAT() | 格式化指定日期，返回指定格式值 |
| WEEKDAY() | 返回指定日期一周内对应的工作日索引 |
- 流程控制函数
| 函数名称 | 作用 |
|---|---|
| IF() | 判断 |
| IFNULL() | 判断是否为空 |
| NULLIF() | 判断是否相等（若相等，返回NULL） |
| CASE WHEN() | 搜索语句 |
- 加密函数
| 函数名称 | 作用 |
|---|---|
| MD5() | 计算str的MD5校验和 |
| SHA() | 计算str的安全散列算法校验和 |
| SHA2(str,hash_length) |
#3.日期函数
now 当前系统日期+时间
curdate 当前系统日期
curtime 当前系统时间
str_to_date 将字符转换成日期
date_format 将日期转换成字符
#4.流程控制函数
if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断
#5.其他函数
version 版本
database 当前库
user 当前连接用户
自定义函数
-
创建函数
CREATE FUNTION function_name([para_list]) returns data_type BEGIN sql 语句; return value; end; -
调用函数
SELECT function_name(); SELECT function_name([para_list]); -
查看函数
# 查询单个函数 SHOW CREATE FUNCTION function_name; # 查询所有函数 SHOW FUNCTION STATUS [like 'pattern']; -
修改函数
ALTER FUNCTION function_name options; -
删除函数
DROP FUNCTION function_name;
DQL语言
基础查询
语法：
SELECT 查询输出列表
[FROM 表名];
#类似于Java中 :System.out.println(要打印的东西);
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
②要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
条件查询
条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
select 要查询的字段|表达式|常量值|函数
from 表
where 条件;
分类：
-
条件表达式 示例：salary>10000条件运算符：
> < >= <= = != <> -
逻辑表达式示例：salary>10000 && salary<20000
-
逻辑运算符：
-
and（&&）:两个条件如果同时成立，结果为true，否则为false
-
or(||)：两个条件只要有一个成立，结果为true，否则为false
-
not(!)：如果条件成立，则not后为false，否则为true
-
-
模糊查询 示例：last_name like ‘a%’
排序查询
语法：
select 要查询的东西
from 表
where 条件
order by 排序的字段|表达式|函数|别名 [asc|desc]
分组查询
语法：
select 查询的字段，分组函数
from 表
group by 分组的字段;
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
针对的表 位置 关键字
分组前筛选： 原始表 group by的前面 where
分组后筛选： 分组后的结果集 group by的后面 having
4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
多表连接查询
笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件
一、传统模式下的连接 ：等值连接——非等值连接
- 等值连接的结果 = 多个表的交集
- n表连接，至少需要n-1个连接条件
- 多个表不分主次，没有顺序要求
- 一般为表起别名，提高阅读性和性能
二、SQL99语法：通过join关键字实现连接
含义：1999年推出的sql语法
支持：
- 等值连接、非等值连接 （内连接）
- 外连接
- 交叉连接
语法：
select 字段，...
from 表1
[inner|left outer|right outer|cross]join 表2 on 连接条件
[inner|left outer|right outer|cross]join 表3 on 连接条件
[where 筛选条件]
[group by 分组字段]
[having 分组后的筛选条件]
[order by 排序的字段或表达式]
好处：
语句上，连接条件和筛选条件实现了分离，简洁明了！
查询字段来自多个表时，用连接查询；
笛卡尔乘积现象：表1 有m行，表2 有n行，结果为 m*n行；避免：添加有效连接条件；
分类：
年代： SQL92标准、SQL99标准；
功能：
-
内连接（等值连接、非等值连接、自然连接）
-
内连接：系统默认，只返回满足连接条件的数据行，两个表在进行连接时，连接字段的名称可以不同，但要求必须具有相同的数据类型、长度和精度，且表达同一范畴的意义，连接字段一般是数据表的主键和外键。
-
格式：
#方式1： SELECT 输出列表 FROM 表1 [INNER] JION 表2 ON 表1.字段名 比较运算符 表2.字段名; #方式2 SELECT 输出列表 FROM 表1 , 表2 WHERE 表1.字段名 比较运算符 表2.字段名; #在列名相同且连接条件为列名相同的条件下使用 SELECT 输出列表 FROM 表1 join 表2 USING (字段名);- 等值连接： = 查询结果中包含被连接表的所有字段，并包括重复字段
- 非等值连接： > >= < <= != 查询结果满足比较值的连接字段
- 自然连接： 特殊的等值连接，其结果集中且不包括重复字段
-
注意：
- 输出字段在表中出现歧义则指明字段所在的表名以区分
table_name.col_name - 列名相同且连接条件也是列名相同，则
ON(连接条件)可以替换成USING(同系列的列名)
- 输出字段在表中出现歧义则指明字段所在的表名以区分
-
-
外连接（左外连接、右外连接、全外连接）
-
外连接：返回的结果包含符合条件的记录、FROM子句中至少一个表中所有行（不满足条件的数据行显示
NULL） -
格式：
SELECT 输出列表 FROM 表名 1 LEFT | RIGHT JOIN 表名2 ON 表名1.字段名1=表名2.字段名2;- 左外连接：结果集包括满足连接条件的行、左表中不满足条件的记录行（左表不满足条件的记录与右表组合时，右表相应列为
NULL）。 - 右外连接：结果集包括满足连接条件的行、右表中不满足条件的记录行（右表不满足条件的记录与左表组合时，左表相应列为
NULL）。
- 左外连接：结果集包括满足连接条件的行、左表中不满足条件的记录行（左表不满足条件的记录与右表组合时，右表相应列为
-
-
交叉连接
-
交叉连接：在没有
WHERE子句情况下，产生表的笛卡尔乘积（结果集大小为两者行数乘积）。 -
格式：
SELECT 字段名列表 FROM 表1 CROSS JOIN 表2;
-
-
自连接
-
自连接：一个表的两个副本之间进行的内连接（同一个表名在FROM子句中出现两次）
-
格式：
-
SELECT 字段名列表 FROM 表1 AS 别名1,表1 AS 别名2 where 条件表达式;
-
-
注意：为区别，必须指定不同别名，字段名必须加表的别名区分
-
SQL92 标准
select 查询列表
from 表1 as 别名1 , 表2 as 别名2
[where 连接条件]
[and 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
SQL99 标准
select 查询列表
from 表1 as 别名1
[连接类型] join 表2 as 别名2
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
三、自连接
案例：查询员工名和直接上级的名称
sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
子查询
含义：
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询
特点：
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空``
② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
-
分类：
-
比较子查询：
SELECT 查询输出列表 FROM 表 WHERE 表达式 比较运算符 (子查询);
-
带IN关键字的子查询：
SELECT 查询输出列表 FROM 表 WHERE 表达式 [NOT] IN (子查询);
-
批量比较查询：
SELECT 查询输出列表 FROM 表 WHERE 表达式 比较运算符 {ANY|SOME|ALL} (子查询); -
EXISTS子查询：
SELECT 查询输出列表 FROM 表 WHERE [NOT] EXISTS (子查询);
-
-
基本操作：
-
插入
INSERT INTO 表 SELECT 插入字段 FROM 表 WHERE 条件表达式; -
更新
UPDATE 表 SET 更新字段 WHERE (子查询);
-
删除
DELETE 表 WHERE (子查询);
-
模糊查询
LIKE的模糊查询
-
格式：
SELECT 查询输出列表 FROM table_name WHERE col_name [NOT]LIKE '字符串' [ESCAPE '转义字符']; -
通配符：
AB%匹配以AB开始的任意字符串%AB匹配以AB结束的任意字符串%AB%匹配任意包含AB的任意字符串_AB匹配以AB结束的三个字符的字符串
正则表达式的模糊查询
-
格式：
SELECT 查询输出列表 FROM table_name WHERE col_name [NOT] [REGEXP|RLIKE] 表达式; -
正则表达式：
匹配字符 说明 例子解释 ^匹配字符串的开始字符 ^A匹配以A字符串为开头的字符串$匹配字符串的结束字符 ING$匹配以ING字符串为结尾的字符串.匹配任意单个字符，包括回车和换行 g.e匹配任意g和e之间单个字符，包括回车和换行*匹配*之前的0个或多个字符 *e匹配e前面任意的0个或多个字符+匹配+之前的字符1次或多次 x+i匹配i前面有x字符1次或多次<字符串>匹配包含指定字符串的文本 <fa>匹配包含fa字符串的文本[字符集合]匹配字符集合中任意一个字符 [abc]匹配abc字符集合中任意一个字符[a-z]匹配出现a~z的1个字符 [a-z]匹配a~z的任意小写字母字符[^A]匹配不在括号中的任意字符 [^A]匹配任意不包含A的字符串字符串{n}匹配确定的n次 ABC{2}匹配ABCABC的字符串字符串{n,}匹配前面的字符串至少n次 WO{2,}匹配两个或多个WO的字符串字符串{m,n}匹配前面的字符串至少m次，至多n次 PI{2,4}匹配至少2个PI，至多4个PI的字符串
分页查询
-
应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
-
语法：
select 字段|表达式,... from 表 [where 条件] [group by 分组字段] [having 条件] [order by 排序的字段] limit [起始的条目索引,]条目数; -
特点：
- 起始条目索引从0开始
- limit子句放在查询语句的最后
- 公式：
select * from 表 limit （page-1） * sizePerPage,sizePerPage- 假如:每页显示条目数sizePerPage,要显示的页数 page
联合查询
-
引入：union 联合、合并
-
语法：
select 字段|常量|表达式|函数 [from 表] [where 条件] union [all] select 字段|常量|表达式|函数 [from 表] [where 条件] union [all] select 字段|常量|表达式|函数 [from 表] [where 条件] union [all] ..... select 字段|常量|表达式|函数 [from 表] [where 条件]; -
特点：
- 多条查询语句的查询的列数必须是一致的
- 多条查询语句的查询的列的类型几乎相同
- union代表去重，union all代表不去重
DML语言
插入
语法：
insert into 表名(字段名，...)
values(值1，...);
特点：
- 字段类型和值类型一致或兼容，而且一一对应
- 可以为空的字段，可以不用插入值，或用null填充
- 不可以为空的字段，必须插入值
- 字段个数和值的个数必须一致
- 字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
修改
修改单表语法：
update 表名 set 字段=新值,字段=新值
[where 条件]
修改多表语法：
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件
删除
方式1：delete语句
单表的删除： ★
delete from 表名 【where 筛选条件】
多表的删除：
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
方式2：truncate语句
truncate table 表名
两种方式的区别【面试题】
1.truncate不能加where条件，而delete可以加where条件
#2.truncate的效率高一丢丢
#3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
#4.truncate删除不能回滚，delete删除可以回滚
DDL语言
库和表的管理
库的管理：
# 创建库
create database database_name;
# 删除库
drop database database_name;
表的管理：
1.创建表
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME
);
DESC studentinfo;
2.修改表 alter
ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 [字段类型];
①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;
②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;
③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;
3.删除表
DROP TABLE [IF EXISTS] studentinfo;
常见类型
整型、小数（浮点型、定点型）、字符型、日期型、Blob型
常见约束
NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
视图
-
含义
理解成一张虚拟的表
-
区别
类型\区别 使用方式 占用物理空间 视图 不占用，仅保存的是SQL逻辑 表 占用 -
优点
- sql语句提高重用性，效率高
- 和表实现了分离，提高了安全性
-
创建
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] [DEFINER = {user|CURRENT_USER}] [SQL SECURITY {DEFINER|INVOKER}] VIEW view_name [(col_list)] AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION]; -
增删改查
#1.查看视图的数据 ★ SELECT * FROM my_v4; SELECT * FROM my_v1 WHERE last_name='Partners'; #2.插入视图的数据 INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90); #3.修改视图的数据 UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹'; #4.删除视图的数据 DELETE FROM my_v4;
-
某些视图不能更新
包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表 -
视图逻辑的更新
# 方式一： CREATE OR REPLACE VIEW test_v7 AS SELECT last_name FROM employees WHERE employee_id>100; # 方式二: ALTER VIEW test_v7 AS SELECT employee_id FROM employees; SELECT * FROM test_v7; -
视图的删除
DROP VIEW test_v1,test_v2,test_v3; -
视图结构的查看
DESC test_v7; SHOW CREATE VIEW test_v7;
存储过程
-
含义
一组经过预先编译的SQL语句的集合
-
好处
- 允许标准组件式编程：提高了SQL语句的重用性、共享性和可移植性；
- 较快的执行速度：提高了效率；
- 减少了传输次数，减少网络流量，降低网络负载；
- 安全：可限制相应数据的访问权限，避免非授权用户对数据的访问，保证数据安全；
-
分类：
-
无返回无参
-
仅仅带in类型，无返回有参
-
仅仅带out类型，有返回无参
-
既带in又带out，有返回有参
-
带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个
-
-
创建存储过程
- 语法：
CREATE PROCEDURE proc_name([in|out|inout [para_name para_type,...]]) begin routine_body end类似于JAVA方法：
修饰符 返回类型 方法名([参数类型 参数名,…]){
方法体;
}注意：
-
需要设置新的结束标记
delimiter 新的结束标记 -
存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end
-
参数前面的符号的意思
in：该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出
-
示例：
DELIMITER $$ CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) BEGIN sql语句1; sql语句2; END $$
-
调用存储过程
CALL pro_name([para_actual[,…]]]) -
查看
-
查看状态
SHOW PROCEDURE|FUNCTION STATUS [LIKE 'pf_nmae'];Db–存储过程所属的数据库
Name–存储过程名称/函数名称
Type–存储过程/函数
Definer–创建存储过程/函数的用户
Modified–最后修改日期
Created–创建日期
Security_type–MySQL在执行存储过程/函数是以创建用户的权限来执行，或是以调用者的权限来执行；
-
查看定义
SHOW CREATE PROCEDURE|FUNCTION pf_name;Procedure–存储过程名
sql_mode–SQL语句模式
Create Procedure–存储过程定义语句
-
查看信息
SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='pf_name' ROUTINE_TYPE='pf_type';ROUTINE_CATALOG–存储过程/函数目录
ROUTINE_SCHEMA–存储过程/函数所属数据库
ROUTINE_NAME–存储过程/函数名
ROUTINE_TYPE–存储过程/函数
ROUTINE_DEFINITION–BEGIN…END语句
SECURITY_TYPE–MySQL在执行存储过程/函数是以创建用户的权限来执行，或是以调用者的权限来执行；
-
-
修改
ALTER PROCEDURE proc_name[characteristic…];characteristic–存储过程/函数特性，与上同；
-
删除
DROP PROCEDURE [IF EXISTS] proc_name; -
函数和存储过程的区别与联系
- 区别
关键字 调用语法 返回值 应用场景 函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回即有且仅有一个返回值 存储过程 PROCEDURE CALL 存储过程() 可以有0个或多个 一般用于更新 - 联系
- 存储过程和函数都是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户调用过程名和函数给出参数来调用它们。
- 都能接收输入参数、并以输出参数的形式为调用过程语句返回一个或多个结果集；
- 都能调用存储过程和函数；
- 都会返回执行状态值和函数值，以表示执行失败或成功。
游标
-
含义
在存储过程和函数中，当查询语句返回多条记录，使用游标对结果集进行逐条读取
-
定义
DECLARE cursor_name CURSOR FOR select_statement; -
打开
OPEN cursor_name; -
使用
FETCH cursor_name INTO var_name[,var_name…]; -
关闭
CLOSE cursor_name;
触发器
-
认识
-
初始
是预先编写的一段程序代码来在事件激发而执行某个操作，表中出现特定事件时会自动激发该对象。是特殊的存储过程，不需要
CALL语句来调用，而自动调用 -
优点
- 自动执行，即对触发器相关表的数据进行修改后可立即执行
- 层叠修改，即触发器可以通过数据库中相关的表层叠修改另外的表
- 复杂操作，即触发器可以实现比
FOREGIN KEY约束、CHECK约束更为复杂的检查和操作
-
分类
-
触发时机：
一般分为执行之前
BEFORE或之后AFTER响应的触发器，又分为以下类型的触发器 -
触发类型：
-
INSERT触发器注意：
- 在INSERT触发器代码内，可引用一个名为NEW的虚拟表来访问被插入的行
- 在BEFORE INSERT触发器中，NEW中的值也可以被更新，即允许更改被插入的值（但需具有相应的权限）
- 对于AUTO_INCREAMENT列，NEW在INSERT执行前包含的值是0，在INSERT执行后包含新的自动生成值
-
UPDATE触发器注意：
- 在UPDATE触发器代码内，可引用一个名为NEW的虚拟表来访问更新的值
- 在UPDATE触发器代码内，可引用一个名为OLD的虚拟表来访问UPDATE语句执行前的值
- 在BEFORE UPDATE触发器中，NEW的值可能也会被更新，即允许更改将要用于UPDATE语句中的值（但需具有相应权限）
- OLD中的值全部是只读，不能被更新
-
DELETE触发器注意：
- 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表来访问被删除的行
- OLD中的值全部是只读，不能被更新
两者需组合使用
-
-
作用
加强安全性管理、调用存储功能、强化数据条件约束、跟踪变化、级联运行
-
-
创建
CREATE TRIGGER <trigger_name> <BEFORE|AFTER> <INSERT|UPDATE|DELETE> ON <table_name> FOR EACH ROW <trigger_statement>注意：
- INSERT–将新行插入表时激活，其BEFORE触发器不仅能被INSERT语句激活，也能被LOAD DATA语句激活
- DELETE触发器–从表中删除某行数据时激活，不仅能被DELETE语句激活，也能被REPLACE语句激活
- UPDATE触发器–更改表中某行数据时激活，被UPDATE语句激活
- 每个表最多支持6个触发器，每个表的每个事件每次只允许有一个触发器，即单一触发器不能与多个事件或多个表关联
-
查看
-
查看一个或多个触发器
SHOW TRIGGERS \G; # 查看所有触发器 SHOW TRIGGERS FROM db_name LIKE 'table_name' \G; # 查看某个表上的触发器 SHOW TRIGGERS WHERE `TRIGGER` LIKE 'trigger_name' \G; # 查看某一个触发器查询结果:
- Trigger–触发器的名称
- Event–触发器的激发事件，有
INSERT、UPDATE、DELETE三种情况 - Table–定义触发器的表
- Statement–触发器体，即触发器激活时执行的语句
- Timing–触发器执行时机，有
BEFORE和AFTER两种情况
-
查看触发器的详细信息
SELECT * FROM information_schema.triggers;
-
-
删除
DROP TRIGGER data_name.trigger_name;
DCL语言
事务
-
含义
用户定义的一个操作序列，通过一组逻辑操作单元（一组DML语句），将数据从一种状态切换到另外一种状态，不存在中间态，即该事务要么执行，要么不执行。
-
特点（即ACID）
- 原子性(Atomicity)：一个事务必须被视为一个不可分隔的逻辑工作单元，要么都执行，要么都回滚
- 一致性(Consistency)：保证数据的状态操作前和操作后保持数据库状态一致性
- 隔离性(Isolation)：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
- 持久性(Durability)：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改
-
相关步骤
-
开启事务
START TRANSACTION; -
编写事务的一组逻辑操作单元（多条SQL语句）
-
提交事务或回滚事务
# 提交事务 COMMIT TRANSACTION tran_name; SET AUTOCOMMIT = 1; # 开启自动提交功能 SET AUTOCOMMIT = 0; # 关闭自动提交功能 # 回滚事务 ROLLBACK TRANSACTION tran_name; SAVEPOINT point_name; # 设置保存点 ROLLBACK TO SAVEPOINT point_name; # 回滚到保存点 RELEASE SAVEPOINT point_name; # 删除保存点
-
-
事务的分类
-
隐式事务，没有明显的开启和结束事务的标志
例如，insert、update、delete语句也是一个事务，但无明显的开始结束事务标志
-
显式事务，具有明显的开启和结束事务的标志
# 1.开启事务 START TRANSACTION tran_name; SET AUTOCOMMIT = 0; # 建议关闭自动提交功能 # 2.编写事务 BEGIN tran_statement # 多条SQL语句 END # 3.提交事务/回滚事务 COMMIT TRANSACTION tran_name; # 提交事务 ROLLBACK TRANSACTION tran_name; # 回滚事务
-
-
事务的隔离级别
-
发生事务并发问题
当多个事务同时操作同一个数据库的相同数据时
-
事务的并发问题
- 更新丢失：一个事务覆盖另一个事务的更新；本质：写操作的冲突，解决方法：逐个写入
- 脏读：一个事务读取到了另外一个事务未提交的数据；本质：读/写操作的冲突，解决方法：写入后再读取
- 不可重复读：同一个事务中，多次读取到的数据不一致；本质：读/写操作的冲突，解决方法：读取后再写入
- 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据
- 死锁：两个或两个以上的进程在执行过程中，因争夺资源而造成的一种相互等待对方释放资源而导致双方一直处于等待状态的现象，若无外力作用，将一直持续下去。
-
避免事务的并发问题
通过设置事务的隔离级别
READ UNCOMMITTED未提交读，
READ COMMITTED提交读，可以避免脏读
REPEATABLE READ可重复读，可以避免脏读、不可重复读和一部分幻读
SERIALIZABLE可串行读，可以避免脏读、不可重复读和幻读隔离级别/可能会导致的问题 脏读 不可重复读 幻读 READ UNCOMMITTED √ √ √ READ COMMITTED × √ √ REPEATABLE READ × × √ SERIALIZABLE × × -
设置隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE|REPEATABLE READ|READ COMMITTED|READ UNCOMMITTED;GLOBAL/SESSION：隔离级别适用于之后所有会话/当前会话之后的所有事务，默认为当前SESSION内的下一个仍未开始的事务
隔离级别：默认为REPEATABLE READ隔离级别
-
查看隔离级别
SELECT @@global.transaction_isolation; # 查看全局的隔离级别 SELECT @@session.transaction_isolation; # 查看当前会话的隔离级别 SELECT @@transaction_isolation; # 查看下一个事务的隔离级别全局隔离级别–影响所有连接MySQL的用户
会话隔离级别–影响当前正在登录MySQL服务器的用户，不影响其他用户
事务隔离级别–仅影响当前用户的下一个事务操作
-
修改隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL SERIALIZABLE|REPEATABLE READ|READ COMMITTED|READ UNCOMMITTED;SET [@@global.transaction_isolation|@@session.transaction_isolation|@@transaction_isolation] =['SERIALIZABLE'|'REPEATABLE READ'|'READ COMMITTED'|'READ UNCOMMITTED'];
-
锁机制
-
简述
防止其他事务访问指定资源的手段，是实现并发控制的主要方法，是多个用户能够同时操作同一个数据库中的数据而不发生数据不一致性现象的重要保障。
-
分类
-
锁粒度（作用范围）
-
表级锁
整个表被锁定；分为读锁（用户从表中读取数据受到限制）和写锁（用户向表中插入数据受到限制）。
开销小，加锁快，不会出现死锁，锁粒度大，发生锁冲突的概率最高，并发度最低。
-
行级锁
只有线程使用的行会被锁定，表中其他行对于去其他线程都是可用的；分为排他锁、共享锁和意向锁。
开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突的概率最低，并发度最高
-
页面锁
开销和加锁事件介于表锁和行锁之间，会出现死锁，锁粒度介于表锁和行锁之间，并发度一般
锁粒度越小越适合做并发更新操作，锁粒度越大越适合做并发查询操作。
数据库引擎允许多粒度锁定
-
-
类型
-
共享锁（读锁/S锁）和排他锁（写锁/X锁）
共享锁：一个事务在某行加S锁，可读取数据改行数据，另一个事务也能加S锁，但不能加X锁
排他锁：一个事务在某行加X锁，可修改改行数据，另一个事务无法加S锁或X锁，必须等上个事务释放X锁才能加锁
-
意向锁（I锁）
一个事务有意对数据加共享锁或排他锁
X锁 IX锁 S锁 IS锁 X锁 冲突 冲突 冲突 冲突 IX锁 冲突 兼容 冲突 兼容 S锁 冲突 冲突 兼容 兼容 IS锁 冲突 兼容 兼容 兼容 -
-
算法
-
Record Locks(行锁)
通过对索引行加锁实现
-
Gap Locks(间隙锁)
锁定索引的记录间隙，确保索引记录的间隙不变
-
Next-Key Locks
结合Gap Locks和Record Locks的一种锁定算法，可锁定一个范围，并且锁定记录本身。
-
-
-
管理
-
表级锁
-
设锁
LOCK TABLES table_name1 [[AS] alias] lock_type, [table_name2,[[AS] alias] lock_type …]lock_type–锁定类型，READ/WRITE两种方式
-
解锁
UNLOCK TABLES;
-
-
行级锁
-
设锁
SELECT statement FOR SHARE [NOWAIT|SKIPLOCKED]|LOCK IN SHARE MODE; SELECT statement FOR UPDATE [NOWAIT|SKIPLOCKED]; -
解锁
UNLOCK TABLES;
-
-
备份与恢复
表数据的导入与导出
-
用
select…into outfile命令导出数据SELECT [导出列表] FROM table [WHERE 语句] INTO OUTFILE "目标文件" [OPTIONS];六个常用选项：
FIELDS TERMINATED BY '字符串'–设置字段的分隔符为该字符串，默认分隔符为’\t’;FIELDS ENCLOSED BY '字符'–设置字符为包裹字段的符号，默认为不使用任何符号;FIELDS OPTIONALLY ENCLOSED BY '字符'–设置该字符来包裹CHAR和VARCHAR等字符型数据;FIELDS ESCAPED BY '字符'–设置该字符为转义字符，默认为" \ ";LINES STARTING BY '字符串'–设置每一行的开始字符，默认情况无任何字符;LINES TERMINATED BY '字符串'–设置每一行的结束字符，默认为’\n’;
-
用
mysqldump命令导出数据导出文本文件
mysqldump -u root -pPassword -T 目标目录 db_name table_name [options]导出xml文件
mysqldump -u root -pPassword --xml|-X db_name >导出文件路径; -
用其他命令导出数据
导出文本文件
mysql -u root -pPassword -e|--execute="SELECT 语句" db_name>导出文件导出XML文件
mysql -u root -pPassword --xml|-X -e"SELECT 语句" db_name>导出文件导出HTML文件
mysql -u root -pPassword --html|-H -e"SELECT 语句" db_name>导出文件 -
用
load data infile命令导入文本文件LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE '导出文件名.txt' [REPLACE|IGNORE] INTO TABLE 表名 [OPTIONS];
备份数据
-
备份一个数据库中的表
mysqldump -h host -u user -pPassword db_name [tbname1[,tbname2…]]>filename.sql -
备份多个数据库
mysqldump -h host -u user -pPassword --databases db_name1[,db_name2…]>filename.sql -
备份所有数据库
mysqldump -h host -u user -pPassword --all-databases>filename.sql
数据恢复
-
source命令恢复表和数据库source file_name.sql; -
mysql命令还原mysql -u user_name -pPassword [db_name]<file_name.sql
用户与权限
MySQL代码模板
创建数据库
CREATE DATABASE database_name;
查看数据库
显示数据库创建信息
SHOW CREATE DATABASE database_name \G;
显示数据库
SHOW DATABASES;
选择数据库
USE database_name;
修改数据库
ALTER DATABASE database_name
DEFAULT CHARACTE SET charset_name
DEFAULT COLLATE collate_name;
CREATE TABLE 表名(
PRIMARY KEY(字段名1,字段名2,……)
……
);
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2,……);
查看当前表的完整性约束信息
SHOW INDEX FROM 表名;
表的数据完整性
主键约束
创建主键约束
1.单一主键
CREATE TABLE 表名(
字段名 数据类型 PRIMARY KEY
……
);
2.复合主键
CREATE TABLE 表名(
PRIMARY KEY(字段名1,字段名2,……)
……
);
为已存在表添加主键约束
1.单一主键
ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY;
2.复合主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2,……);
删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
外键约束
创建外键约束
CREATE TABLE 表名(
PRIMARY KEY [索引类型] (索引列名),
FOREIGN KEY [索引名] (索引列名)
[参照性定义] REFERENCES 表名 [(索引列名)]
[参照动作定义] ON DELETE|ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}
);
为已存在的表添加外键约束
ALTER TABLE 子表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(字段名) REFERENCES 父表(字段名);
删除外键约束
ALTER TABLE 子表名 DROP FOREIGN KEY 外键名;
唯一性约束
创建唯一性约束
CREATE TABLE 表名(
字段名 数据类型 UNIQUE /*列级完整性约束*/,
UNIQUE(字段名) /*表级完整性约束*/
);
添加唯一性约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE(字段名1,字段名2,……);
删除唯一性约束
ALTER TABLE 表名 DROP INDEX 唯一性约束名;
非空约束
创建非空约束
CREATE TABLE 表名(
字段名 数据类型 NOT NULL
);
添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
删除非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
默认值约束
创建默认值约束
CREATE TABLE 表名(
字段名 数据类型 DEFAULT 默认值
);
添加默认值约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT 默认值;
删除默认值约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
数据查询
基本查询语句
SELECT [ALL|DISTINCT|DISTINCTROW] select_expr[,select_expr …]
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name|expr|position},…[WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name|expr|position} [ASC|DESC],…[WITH ROLLUP]]
[LIMIT {[offset,] row_count|row_count OFFSET offset}]
[INTO OUTFILE 'file_name'| INTO DUMPFILE 'file_name'| INTO var_name[,var_name]]
单表查询
查询所有字段数据
SELECT * FROM table_name;
查询指定字段数据
SELECT col_name,… FROM table_name;
去除重复记录
SELECT DISTINCT|DISTINCTROW col_name FROM table_name;
按表达式查询
SELECT 表达式 … FROM table_name;
设置别名
/*设置字段别名*/
SELECT col_name1 [AS] 别名1,col_name2 [AS] 别名2 [,…] FROM table_name;
/*设置表的别名*/
SELECT 表别名.字段 [,…] FROM table_name [AS] 表别名;
查询指定记录
SELECT col_name FROM table_name
WHERE where_condition;
带IN关键字查询
SELECT 查询输出列表 FROM table_name
WHERE col_name
[NOT] IN (ele1,ele2,……);
带BETWEEN AND关键字查询
SELECT 查询输出列表 FROM table_name
WHERE col_name
[NOT] BETWEEN 取值1 AND 取值2;
带LIKE模糊查询
SELECT 查询输出列表 FROM table_name
WHERE col_name
[NOT]LIKE '字符串' [ESCAPE '转义字符'];
带IS NULL空值查询
SELECT 查询输出列表 FROM table_name
WHERE col_name
IS [NOT] NULL;
带AND|OR多条件查询
SELECT 查询输出列表 FROM table_name
WHERE 条件1
AND 条件2 […… AND 条件n];
SELECT 查询输出列表 FROM table_name
WHERE 条件1
OR 条件2 […… OR 条件n];
多表查询
基础连接：
SELECT [ALL|DISTINCT|*] 查询输出列表
FROM 表1 [别名1] 连接类型 表2 [别名2]
[ON 表1.相关列=表2.相关列]
[WHERE 条件表达式];
内连接
方式1：
SELECT 查询输出列表
FROM 表1 [INNER] JOIN 表2
ON 表1.字段名 比较运算符 表2.字段名;
方式2：
SELECT 查询输出列表
FROM 表1 ,表2
WHERE 表1.字段名 比较运算符 表2.字段名;
外连接
SELECT 查询输出列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段名1=表2.字段名2;
交叉连接
SELECT 查询输出列表
FROM 表1 CROSS JOIN 表2;
自连接
SELECT 查询输出列表
FROM 表1 as 别名1,表1 as 别名2
where 别名1.字段1=别名2.字段1 [……];
索引
创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON table_name (col_name[length])|(expr)|[ASC|DESC];
创建表时
CREATE TABLE table_name(
字段名 数据类型[完整性约束条件],
……
字段名 数据类型[完整性约束条件],
PRIMARY KEY [索引类型](索引字段名…)
|FOREIGN KEY (索引字段名…)[参照性定义]
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名[(长度)])[ASC|DESC]
);
存在的表创建索引
ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名](字段名[(长度)][ASC|DESC]);
查看索引
创建情况
SHOW INDEX|KEYS FROM table_name;
SHOW CREATE TABLE table_name;
使用情况
EXPLAIN SELECT * FROM table_name WHERE expr;
删除索引
DROP INDEX col_name ON table_name;
ALTER TABLE table_name
DROP INDEX index_name
|DROP PRIMARY KEY
|DROP FOREIGN KEY foregin_key_name;
视图
创建视图
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
[DEFINER = {user|CURRENT_USER}]
[SQL SECURITY {DEFINER|INVOKER}]
VIEW view_name [(col_list)]
AS select_statement
[WITH [CASCADED|LOCAL] CHECK OPTION];
查看视图
-
结构
DESCRIBE view_name; DESC view_name; -
状态
SHOW TABLE STATUS LIKE 'view_name'; /* view_name 可为具体视图名, 也可为包含通配符的视图字符串;*/ -
创建语句
SHOW CREATE VIEW view_name; -
详细信息
/*查询语句信息即可*/ SELECT view_definition from information_schema.views WHERE expr;
修改视图
-
创建视图时
CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION]; -
修改视图时
ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(col_list)] AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION];
查询视图
/*可直接想查询基表一样对视图进行查询*/
SELECT * FROM view_name;
更新视图
-
插入数据
INSERT INTO view_name VALUES(); -
更新数据
UPDATE view_name SET cols=values WHERE expr; -
删除数据
DELETE FROM view_name WHERE expr;
删除视图
DROP VIEW
[IF EXISTS] view_name1 [,view_name2] …
[RESTRICT|CASCADE];
思维导图
490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
