第一章 概念
一、数据库的好处
1、可以持久化数据到本地
2、结构化查询
二、数据库的常见概念 ★
1、DB：数据库，存储数据的容器
2、DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3、SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
三、数据库存储数据的特点
1、数据存放到表中，然后表再放到库中
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4、表中的每一行数据，相当于java中“对象”
四、常见的数据库管理系统
mysql、oracle、db2、sqlserver
第二章 简单实用
0-1、MySQL服务器的启动和停止
方式一：系统偏好设置—>(右下角)mysql—>start mysql server方式二：Mac终端启动(待补充)
0-2、MySQL的登录与退出
0、首先要保证服务器处于启动状态
1、登录：
- 连接本机：
mysql -u账号 -p密码 - 连接其他主机
-
mysql -h要连接的主机名 -P 3306 -u用户名 -penter后输入密码 -
mysql -h要连接的主机名 -P 3306 -u用户名 -p密码(p和密码之间一定是没有空格的
2、退出：exit
一、常用命令(在登录服务器的前提下)
1、 查看mysql中所有数据库：show databases；
2、 使用/打开一个数据库：use 库名；(此时我会身处在这个数据库中)
3、查看当前库的所有表：show tables；
4、查看其他库的所有表：show tables from 库名；(此时我仍身处use的那个库)
5、创建表
CREATE TABLE students(
num int,
age int
);
1234create table 表名(
列名 列类型，
列名 列类型，
。。。(此处没逗号)
)；
6、查看表结构：desc 表名； (要记住自己位于哪个库，只能看这个库的表)
7、查看服务器版本
在已登录mysql情况下在terminal使用select version()；
select version();
1在没登录mysql情况下在terminal使用mysql --version或mysql -V
mysql --version
musql -V
12二、命令规范
1、 每条命令最好是，结尾分号(terminal中)
2、不区分大小写，建议关键字大写，表名，列表名小写
3、可以缩进、可以换行，->换行的标志
4、注释
#这是单行注释
--这是单行注释
/*
这是多行注释
*/
12345单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* zhushi */
第三章 DQL
一、基础查询
(一)语法
SELECT 查询字段
FROM 表名;(二)特点
- 查询列表可以是：字段、常量、表达式、函数(都可以是单个或者多个)
- 查询结果是一个虚拟表
(三)实例
- 查询单个字段
SELECT email
FROM employees;- 英文分号结尾
- 查询多个字段(英文逗号隔开)
SELECT email,job_id,last_name
FROM employees;- 英文逗号隔开字段
- 查询所有字段
SELECT *
FROM employees;- 操作技巧：可以通过双击Navicat右侧的字段来实现查询(如下)
SELECT `employees`.`email`,`employees`.`first_name`
FROM `employees`;- 查询常量值(数值型、字符型、日期型)(暂时不知道有何用？？？)
- 查询表达式
SELECT 100%3;
FROM employees;- 查询函数
SELECT 函数名(实参列表)
SELECT version();
FROM employees;- 起别名：在字段后面+AS或空格+别名
- 去重：DISTINCT关键字
SELECT DISTINCT `employees`.`department_id`
FROM `employees`;- +：mysql中“+”只有一个作用就是做加法运算
- select 数值+数值：直接运算
- select 字符+数值：先试图将字符转换为数值，成功则继续运算，失败就把字符转换为0再运算
- select null+值：结果都是null
- 补充
- CONCAT函数：拼接字符
CONCAT(str1,str2,...)- IFNULL函数:判断字段或表达式(expr1)是否是null，是null返回指定值(expr2)，不是null返回原本的值
IFNULL(expr1,expr2)- ISNULL函数:判断字段或表达式是否为null，是null返回1，不是null返回0
二、条件查询
(一)、语法
select 查询列表
from 表名
where 筛选条件(二)、筛选条件的分类
1、简单条件运算符
<
>
=
<> !=
>=
<=
<=>安全等于2、逻辑运算符
&& and
|| or
! not3、模糊查询
- like：查询类似的值，一般搭配通配符使用，可以判断字符型或数值型
通配符：
- %任意多个字符
- _任意单个字符
- 和关键字重复时，可以用\来转义，也可以自定义转义
where lastname LIKE ‘_$_’ESCAPE '$';eg：查询姓名中第二个字母是o的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';- between and：用于显示在某个范围内的值，包含边界
eg：查询工资在2500到3500之间的员工姓名
SELECT last_name
FROM employees
WHERE salary IN(2500,3500);sql
- in：查询某个字段是某个/几个确定值的
- 好处：可以代替OR，简化代码
- eg：查询部门号为100，101，102的员工姓名和部门号
SELECT manager_id,last_name
FROM employees
WHERE manager_id IN(100,101,102);- is null /is not null：用于判断null值
eg：查询没有分配部门的员工姓名
SELECT last_name
FROM employees
WHERE manager_id IS NULL;(三)、总结
1、is null和<=>安全等与的区别(待补充)
三、排序查询
(一)、语法
select 查询列表
from 表名
where 筛选条件
order by 排序列表【asc或desc】eg：按照入职日期升序查询100号部门的员工的姓名和入职日期
SELECT last_name,hiredate
FROM employees
WHERE manager_id=100
ORDER BY hiredate- asc(ascend)：升序(可省略，默认升序)
- desc(descend)：降序
(二)、特点
- 可以查询多个字段，每个字段后面加asc或desc(查询结果是在第一个字段顺序的基础上为第二个字段排序)
- 可以按别名查询
- order by 放在select语句的结尾
四、常见函数
(一)、概述
- 功能：类似于java中的方法
- 好处：提高重用性和隐藏实现细节
- 调用：select 函数名(实参列表);
(二)、单行函数
(本来觉得要记得详细一些，后来发现，详细的用法在开发中都会提示出来，所以只需要记住这个有函数，用的时候可以记起来就ok啦)
1、字符函数
函数 | 作用 | 注意 |
CONCAT(str1,str2,...) | 连接 | |
SUBSTR(str FROM pos FOR len) | 截取子串 | 下标从1开始 |
UPPER(str)、LOWER(str) | 变大写、小写 | |
LPAD(str,len,padstr)、RPAD(str,len,padstr) | 左填充、右填充 | |
REPLACE(str,from_str,to_str) | 替换 | |
LENGTH(str) | 获取字节数 | |
TRIM([remstr FROM] str) | 去前后空格 | |
INSTR(str,substr) | 获取子串第一次出现的索引 |
2、数学函数
函数 | 作用 | 注意 |
CEIL(X)、FLOOR(X) | 向上、下取整 | |
MOD(N,M) | 取模 | |
ROUND(X)、ROUND(X,D) | 四舍五入 | |
RAND() | 0~1直接的随机数 | |
TRUNCATE(X,D) | 截断 |
3、日期函数
函数 | 作用 | 注意 |
now() | 返回日期+时间 | |
YEAR(date)、MONTH(date)、DAY(date) | 返回年、月、日 | date可以用now()获取 |
date_format、str_to_date | 日期转字符、字符转日期 | |
datediff | 日期天数差 | |
curdate | 返回当前日期 | |
curtime | 返回当前时间 | |
hour、minute、second | 返回时、分、秒 | |
monthname | 返回英文月份 |
4、其他函数
函数 | 作用 | 注意 |
version | 服务器版本 | |
database | 当前打开的数据库 | |
user | 当前用户 | |
password | 返回字符的密码形式 | |
md5 | 返回字符的md5加密形式 |
5、流程控制函数
①、if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
IF(expr1,expr2,expr3)②、case情况1
case 变量或表达式或字段
when 常量1 then 值1/语句 (值不加分号，语句加分号)
when 常量2 then 值2/语句
...
else 值n
end③case情况2
case
when 条件1 then 值1/语句
when 条件2 then 值2/语句
...
else 值n
end(三)、分组函数
0、说明
分组函数作用于一组数据，然后返回一个值
1、分类
函数 | 作用 | 注意 |
min、max | 最小、大值 | |
sum、avg | 和、平均值 | |
count | 计算个数 |
2、特点
- 语法
select max(字段)
from 表名- 支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型 - 以上分组函数都忽略null
- 都可以搭配distinct使用，实现去重的统计
select sum(distinct dep_id)
from employees;- count函数
- count(字段)：统计该字段非空值的个数
- count(*):统计结果集的行数 (涉及到个数，用count(*))
- count(1):统计结果集的行数(相当于给原表加了一列1，当然1可以换做任何常量值)
效率上：
以前：MyISAM存储引擎，count(*)最高
现在：InnoDB存储引擎，count(*)和count(1)效率>count(字段)
- 和分组函数一同查询的字段，要求是group by后出现的字段(因为查询结果要求是一个规则的表格，如果查询普通字段，表格不是矩形，没意义，如下查询是没意义的)
select avg(salary)，job_id
from employees；五、分组查询
一、语法
select 分组函数，列(分组的条件，对应“按照每个”等字眼)(要求出现在group by后面)
from 表
【where 筛选条件】
group by 分组的字段(多个字段没有顺序)
【having 分组后的筛选】(在where筛选条件得到的结果上再次筛选)
【order by 排序列表】 (order by在最后)- eg：查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id- 如eg：查询部门员工数大于2的部门
select count(*),department_id
from employees
group by department_id
#到这里我查询到的结果是每个部门员工个数
having count(*)>2；- eg：
#eg:查询每个工种有奖金的员工的最高工资 大于12000的员工编号和最高工资
#第一步：查询每个工种有奖金的员工的最高工资
#1-1 先把框架写出来
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
#1-2 在根据条件写where和having，因为“有奖金”在employee表中就有，所以用紧跟from的where
SELECT MAX(salary),job_id
FROM employees
WHERE `employees`.`commission_pct` is not null
GROUP BY job_id
#第二步：大于12000的员工编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE `employees`.`commission_pct` is not null
GROUP BY job_id
HAVING MAX(salary)>12000二、特点
关键字 | 筛选的表 | 位置 | |
分组前筛选 | where | 原始表 | group by前 |
分组后筛选 | having | 分组后的中间表 | group by后 |
- 补充
- 分组函数做条件，一定是放在having中
- 能用分组前，用分组前，提高性能
- 可以按多个字段分组
六、连接查询
(一)、含义·
- 当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,...; - 笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
- 如何解决：添加有效的连接条件
(二)、分类
按年代分类
- sql92
- sql99(推荐使用)
按照功能分类
- 内连接
- 等值连接
- 非等值
- 自连接
- 外连接
- 左外
- 右外
- 全外
- 交叉连接
(三)、SQL92语法
1. 等值连接
- 语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】(多表连接and后面可以是表2.key2=表3.key2)
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】- 特点：
- 一般为表起别名
- 有别名后，查询字段不能再用原来的表名去限定
- 多表的顺序可以调换
- n表连接至少需要n-1个连接条件
- 等值连接的结果是多表的交集部分
2. 非等值连接
- 语法：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】3. 自连接
- 语法：
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】(四)、SQL99语法
1. 内连接(重要)
- 语法：
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
【【inner】 join 表3 别名 on 连接条件】
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;- 特点：
- 表的顺序可以调换(多表连接时，需要有关联列可以保证两者可以连接)
- 内连接的结果=多表的交集
- n表连接至少需要n-1个连接条件
- 分类：
- 等值连接
- 非等值连接
- 自连接
2. 外连接
- 应用场景：没有关联列
- 语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;- 特点：
- 查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
- left join 左边的就是主表，right join 右边的就是主表, full join 两边都是主表
- 一般用于查询除了交集部分的剩余的不匹配的行
- 分类
- 左外连接
- 右外连接
- 全外连接(不支持mysql)
3. 交叉连接
- 语法：
- select 查询列表
- from 表1 别名
- cross join 表2 别名;
- 特点：
- 类似于笛卡尔乘积
七、子查询
(一)、含义
- 嵌套在其他语句内部的select语句称为子查询或内查询
- 外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
- 外面如果为select语句，则此语句称为外查询或主查询
(二)、分类
1. 按出现位置
- select后面：
- 仅仅支持标量子查询
- from后面：
- 表子查询
- where或having后面：(重要)
- 标量子查询 (重要)
- 列子查询 (重要)
- 行子查询(用的较少)
- exists后面：
- 标量子查询
- 列子查询
- 行子查询
- 表子查询
2. 按结果集的行列
- 标量子查询(单行子查询)：结果集为一行一列
- 列子查询(多行子查询)：结果集为多行一列
- 行子查询：结果集为多行多列
- 表子查询：结果集为多行多列
(三)、特点
- 子查询放在小括号
- 子查询一般放在条件的右侧
- 标量子查询，搭配单行操作符使用：(因为单行子查询只有一个结果)
>, <, >=, <=, =, <>
- 列子查询，搭配多行操作符使用：(类似in(1，2，3))
操作符 | 含义 | 备注 |
in、not in | 等于列表中的任意一个 | 相当于in(1，2，3)、 in相当于=any、 not in相当于<>all |
</>any、some | 小于/大于任意一个 | 可以用min、max代替 |
</>all | 小于/大于所有的 | 可以用min、max代替 |
- 子查询先于主查询执行
(四)、示例
1. 标量子查询
- where案例：查询最低工资的员工姓名和工资
#①最低工资
select min(salary) from employees
#②查询员工的姓名和工资，要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees #这里没有分号
);- 两个条件的话就用where和and连接起来两个条件
- having案例：查询最低工资大于50号部门最低工资的部门部门id和其最低工资
#①先查询50号部门最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#②再查询各个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#在②的基础上，选择满足min(salary)>①的结果的部门id和最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);- 非法的子查询有：
- 错误一：单行子查询使用单行比较符
- 错误二：写入的子查询不返回任何行，即使空值
- selsect案例：查询每个部门的员工个数
select d.*,(
select count(*)
from employees e
where e.department_id=d.department_id
) 员工个数
from departments d;- select内只支持标量子查询
2. 列子查询
- where案例：查询所有是领导的员工姓名
#①查询所有员工的 manager_id
select manager_id
from employees
②查询姓名，employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
select manager_id
from employees
);from后面的
- -将查询的结果当做一张表，要求必须有别名
- 案例：查询每个部门的平均工资和工资等级
#查询每个部门的平均工资和工资等级
#①.先查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②.连接①的结果集和job_grades表，筛选条件是工资位于最高工资和最低工资之间
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) AS ag,department_id
FROM employees
GROUP BY department_id
) AS avg_dep
JOIN job_grades g
ON avg_dep.ag BETWEEN lowest_sal AND highest_salexists后面(相关子查询)
- exists(完整的查询语句)，结果是布尔值
- 先去执行外查询，在执行exists里面的查询
八、分页查询
(一)、应用场景
- 当要查询的条目数太多，一页显示不全。也是分页向服务器提交请求的
(二)、语法
select 查询列表
from 表
limit 【offset，】size;(三)、注意：
- offset代表的是起始的条目索引，默认从0开始。如果是从0开始可以省略
- size代表的是显示的条目个数
- limit语句放在最后
(四)、公式：
# 假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;九、联合查询
(一)、含义
union：合并、联合，将多次查询结果合并成一个结果
(二)、语法
查询语句1
union 【all】
查询语句2
union 【all】
...(三)、意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候，查询的列基本是一致
(四)、注意
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重，union all包含重复项
第四章 DML
一、插入
(一)、方式一
语法：
insert into 表名(列名1,列名2...) values(值1,值2...);特点：
- 要求值的类型和字段的类型要一致或兼容
- 字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应
- 假如表中有可以为null的字段(nullable)，注意可以通过以下两种方式插入null值
- 字段和值都省略
- 字段写上，值使用null
- 字段和值的个数必须一致
- 字段名可以省略，默认所有列，字段顺序和表中顺序一致
(二)、方式二
语法：
insert into 表名 set 字段=值,字段=值,...;(三)、两种方式区别：
- 方式一支持一次插入多行，语法如下：
insert into 表名【(字段名,..)】
values(值，..),(值，...),...;- 方式一支持子查询，语法如下：
insert into 表名
查询语句;
eg:
insert into beauty(id,name,phone)
select 23,'刘亦菲'，110；(会省略values)二、修改
(一)、修改单表的记录 ★
语法：
update 表名
set 字段=值,字段=值
【where 筛选条件】;(二)、修改多表的记录【补充】
语法：
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;三、删除
(一)、方式一：使用delete
删除单表的记录★
- 语法：
delete from 表名 【where 筛选条件】【limit 条目数】级联删除[补充]
- 语法：
delete 别名1,别名2
from 表1 别名1
inner|left|right join 表2 别名2
on 连接条件
【where 筛选条件】(二)、方式二：使用truncate
- 语法：
truncate table 表名
#删除整个表(三)、两种方式的区别【面试题】★
- truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始 - truncate不可以添加筛选条件
delete可以添加筛选条件 - truncate效率较高
- truncate没有返回值
delete可以返回受影响的行数 - truncate不可以回滚
delete可以回滚
第五章 DDL
1、库的管理
一、创建库
create database 【if not exists】 库名【 character set 字符集名】;二、修改库
alter database 库名 character set 字符集名;- 不是修改库名，而是修改字符集
三、删除库
drop database 【if exists】 库名;2、表的管理
一、创建表 ★
create table 【if not exists】 表名(
字段名 字段类型(长度) 【约束】,
字段名 字段类型(长度) 【约束】,
。。。
字段名 字段类型(长度) 【约束】 #这里没有逗号
)二、修改表
- 添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;- 修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;- 修改列名
alter table 表名 change column 旧列名 新列名 类型;- 删除列
alter table 表名 drop column 列名;- 修改表名
alter table 表名 rename 【to】 新表名;三、删除表
drop table【if exists】 表名;四、复制表
1、复制表的结构
create table 表名 like 旧表;2、复制表的结构+数据
create table 表名
select 查询列表
from 旧表
【where 筛选】;- 用*就是复制所以字段+数据；
- 字段1，字段2...就是复制部分字段+数据；
- 把where条件恒不成立(1=2或0或 false)就可以复制字段而不复制数据
- 夸库执行的时候，加上前缀★
3、数据类型
一、数值型
1、整型
类型 | 占用字节 |
tinyint | 1byte=8bit |
smallint | 2 |
mediumint | 3 |
int/integer | 4 |
bigint | 8 |
- 特点：
①、都可以设置无符号和有符号，默认有符号，通过int unsigned设置无符号
②、如果超出了范围，会报out of range异常，会插入临界值
③、长度可以不指定，默认会有一个长度
④、长度不代表范围(因为范围已经通过不同的int决定了)，代表显示的最大宽度，如果不够则左边用0填充，但需要搭配int zerofill，并且默认变为无符号整型
2、浮点型
- 定点数：decimal/dec(M,D)
- 浮点数:
float(M,D) 4
double(M,D) 8 - 特点：
①、M代表整数部位+小数部位的个数，D代表小数部位
②、如果超出范围，则报out or range异常，并且插入临界值
③、M和D都可以省略，浮点数插入不受限制，只要在范围内就原样输入，但对于定点数，M默认为10，D默认为0
④、如果精度要求较高，则优先考虑使用定点数
3、使用原则
- 简单
- 根据范围选择类型越小越好
二、字符型
char、varchar、binary、varbinary、blob(较长的二进制)enum(枚举)、set(集合)、text(文本)
字符 | 特点 | 空间耗费 | 效率 | 应用场景 |
char | 固定长度，M省略时默认为1 | 耗费多 | 高 | 固定长度，如性别 |
varchar | 可变长度，M不能省略 | 耗费少 | 低 | 长度变化多，如姓名 |
三、日期型
- year年
- date日期
- time时间
- datetime 日期+时间：8字节
- timestamp 日期+时间：4 字节，比较容易受时区、语法模式、mysql版本的影响，更能反映当前时区的真实时间
4、约束
一、常见的约束
- NOT NULL：非空，该字段的值必填
- UNIQUE：唯一，该字段的值不可重复
- DEFAULT：默认，该字段的值不用手动插入，有默认值
- CHECK：检查，mysql不支持(语法不报错，但是没效果)，比如限制性别为男女
- PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
- FOREIGN KEY：外键，从表该字段的值引用了并且必须来自于主表的字段
- 主键和唯一键的异同【面试题】★
1、区别：
①、一个表至多有一个主键，但可以有多个唯一
②、主键不允许为空，唯一可以为空(但是也只能有一个null)
2、相同点
都具有唯一性
都支持组合键，但不推荐 - 外键：
- 在从表设置外间关系
- 用于限制两个表的关系，从表的字段值引用了主表的某字段值
- 外键列和主表的被引用列要求类型一致，意义一样，名称无要求
- 主表的被引用列要求是一个key(一般就是主键)
- 插入数据，先插入主表；删除数据，先删除从表
- 可以通过以下两种方式来删除主表的记录
二、创建表时添加约束
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表(被引用列)
)
#设置通用的写法，即，外键约束用标记约束，其他的都用列级约束
#约束名建议写成：自己起的名字_本表名_引用表名注意：
约束分类 | 支持类型 | 可否起约束名 |
列级约束 | 都支持(但是外键在myqsl无效) | 不可 |
标记约束 | 不支持非空、默认 | 可以(对主键无效) |
说明：列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
三、修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;删除非空
alter table 表名 modify column 字段名 字段类型【null】 ;2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;删除默认
alter table 表名 modify column 字段名 字段类型 ;3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);删除主键
alter table 表名 drop primary key;4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);删除唯一
alter table 表名 drop index 索引名;5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);删除外键
alter table 表名 drop foreign key 约束名;6、总结
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名)【外键的引用】四、自增长列/标识列
1、特点：
1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
- 如果要更改起始值：手动插入值
- 如果要更改步长：更改系统变量：set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
2、语法
#一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
#二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
#三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束第六章 TCL
事务
一、含义
事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
二、特点(ACID)【面试题】★
- A(atomicity)原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
- C(consistency)一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
- I(isolation)隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
- D(durability)持久性：一个事务一旦提交了，则永久的持久化到本地
三、事务的使用步骤 ★
1、了解：
- 隐式(自动)事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
- 显式事务：具有明显的开启和结束
2、使用显式事务：
①、开启事务
set autocommit=0;(禁用自动提交功能)
start transaction;#可以省略
②、编写一组逻辑sql语句注意：事务中的sql语句指的是insert、update、delete
设置回滚点：
savepoint 回滚点名(自己起名);
③、结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方：rollback to 回滚点名;
四、并发事务
1、事务的并发问题是如何发生的？
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些？
- 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
- 不可重复读：一个事务多次读取，结果不一样
- 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”/“删除”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
避免脏读 | 避免不可重复度 | 避免幻读 | 评价 | |
read uncommitted | × | × | × | |
read committed | √ | × | × | |
repeatable read | √ | √ | × | |
serializable | √ | √ | √ | 性能很低 |
5、关于隔离级别的命令
#查看当前隔离级别
select @@tx_isolation;
#设置当前mysql连接的级别
set transaction ioslation level read committed；
#设置数据库系统的全局隔离级别
set global transation ioslation level read committed;
#一般需要重启生效第七章 视图
一、含义
- mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
- 好处：
1、简化sql语句
2、提高了sql的重用性(因为是包装成视图来用)
3、保护基表的数据，提高了安全性
二、创建
create view 视图名
as
查询语句;
#或者
create or replace view 视图名
as
查询语句;三、修改
#方式一：
create or replace view 视图名
as
查询语句;
#方式二：
alter view 视图名
as
查询语句；四、删除
drop view 视图1，视图2,...;五、查看
desc 视图名;
#或者
show create view 视图名;六、视图的更新
- 插入：insert
- 修改：update
- 删除：delete
- 注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①、包含分组函数、group by、distinct、having、union、
②、包含join
③、常量视图
④、where后的子查询用到了from中的表
⑤、用到了(即from了)不可更新的视图
七、视图和表的对比
关键字 | 物理内存 | 使用场景 | |
表 | table | 保存实际的数据 | 增删改查 |
视图 | view | 占用小，只保存sql逻辑，不保存查询结果 | 一般用于查询 |
第八章 变量
一、系统变量
1、说明：
- 系统变量由系统提供的，不用自定义
2、语法：
①、查看系统变量
#查看所有的系统变量
show 【global|session 】variables
#查看满足条件的系统变量
show 【global|session 】variables like '';
#如果没有显式声明global还是session，则默认是session②、查看指定的系统变量的值
select @@【global.|session.】变量名;
#如果没有显式声明global还是session，则默认是session③为系统变量赋值
方式一：
set 【global|session 】 变量名=值;
#如果没有显式声明global还是session，则默认是session方式二：
set @@global.变量名=值;
set @@变量名=值；
# @@和.是一起出现的3、注意：
- 全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接(会话)有效，但是不能跨重启(跨重启需要改配置文件) - 会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接(会话)
二、自定义变量
1、用户变量
- 作用域：针对于当前连接(会话)生效
- 位置：begin end里面，也可以放在外面--->任何地方
- 使用：(使用变量的时候也要加上@)
- 声明并赋值：(三种)
set @变量名=值;
set @变量名:=值;
select @变量名:=值;- 更新值
方式一：(三种，和声明赋值一样)
set @变量名=值;
set @变量名:=值;
select @变量名:=值;方式二：
select xx into @变量名 from 表;- 使用/查看
select @变量名;2、局部变量
- 作用域：仅仅在定义它的begin end中有效
- 位置：只能放在begin end中，而且只能放在第一句
- 使用：
- 声明
declare 变量名 类型 【default 值】;- 赋值或更新
方式一：
set 变量名=值;
set 变量名:=值;
select @变量名:=值;方式二：
select xx into 变量名 from 表;- 使用
select 变量名;第九章 存储过程和函数
1、说明：
- 相同点：都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字
- 区别：
返回值 | 适用场景 | |
存储过程 | 0个、多个 | 批量插入、批量更新 |
函数 | 1个 | 数据处理后返回一个结果 |
2、存储过程
零、含义
- 一组预先编译好的SQL语句的集合，理解成批处理语句，后续使用就
零、好处
- 提高重用性
- sql语句简单
- 减少了和数据库服务器连接的次数，提高了效率
一、创建 ★
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end- 注意：
1、参数模式：in(表示这个参数需要传入值)、out(这个参数可以作为返回值)、inout，其中in可以省略
2、存储过程体的每一条sql语句都需要用分号结尾
3、存储过程体内只有一条SQL语句时，可以省略begin...end
4、存储过程体的结尾可以加上上文中使用delimiter设置的结束标志
二、调用
call 存储过程名(实参列表)- 举例：
- 创建并调用空参列表
#创建
delimiter $
create procedure myp1()
begin
insert into admin(username,password)
values('zhao','1111'),('qian','2222'),('sun','3333');
end $
#调用
call myp1() $- 创建并调用in模式的参数：call sp1(‘值’);
- 创建并调用out模式的参数：set @name; call sp1(@name);select @name;
- 创建并调用inout模式的参数：set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存储过程名;四、删除
drop procedure 存储过程名;3、函数
一、创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end- 注意：
- 函数体中肯定需要有return语句
- 函数只有一句时，begin...end可以省略
- delimiter $，然后用$结尾；
- 自己生命变量接受查询值，然后返回
二、调用
select 函数名(实参列表);三、查看
show create function 函数名;四、删除
drop function 函数名；第十章 流程控制结构
一、分支结构
1、if函数
- 功能：实现简单双分支
- 语法：
if(表达式1，表达式2，表达式3) -- 如果1成立返回2，否则返回3- 位置：
- 可以作为表达式放在任何位置
- else语句可以省略，如果省略，且when的条件都不满足，返回null
2、case结构
- 功能：实现多分支
- 语法1：(等值判断)
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2；
...
else 语句n;
end case;- 语法2：(区间判断)
case
when 条件1 then 语句1;
when 条件2 then 语句2；
..
else 语句n;
end case;- 位置：
- 可以放在任何位置，
- 如果放在begin end 外面，作为表达式结合着其他语句使用
- 如果放在begin end 里面，一般作为独立的语句使用
- else语句可以省略，如果省略，且when的条件都不满足，返回null
3、if结构
- 功能：实现多分支
- 语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;- 位置：
只能放在begin end中
二、循环结构
位置：
都只能放在begin end中
对比：
①、这三种循环都可以省略名称，如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
②、loop 一般用于实现简单的死循环；while 先判断后执行；repeat 先执行后判断，无条件至少执行一次
1、while
语法：
【名称:】while 循环条件 do
循环体
end while 【名称】;2、loop(可以用来模拟简单的死循环)
#语法：
【名称：】loop
循环体
end loop 【名称】;3、repeat
#语法：
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;4、循环控制语句
leave：类似于break，用于跳出所在的循环
iterate：类似于continue，用于结束本次循环，继续下一次
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
