目录
1 基本操作
1.1 起别名
- 便于理解
- 如果要查询的字段(列)有重名的情况,使用别名可以区分开来
方式一:使用AS
SELECT 100%88 AS 结果;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
方式二:使用空格
SELECT last_name 姓, first_name 名 FROM employees;
注意:别名中有特殊符号要加双引号
1.2 去重
案例:查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
使用distinct
1.3 +号的作用
在mysql中仅仅有一个功能:运算符
- select 100+90;两个操作数都为数值型,则做加法运算
- select '123'+90;其中一方为字符型,试图将字符型数值转换成数值型,如果转换成功,则继续做加法运算。
- select 'john'+90; 如果转换失败,则继续做加法运算。
- select null+10; 只要其中一方为null,则结果肯定为null.
案例:查询员工和姓名来连接成一个字段,并显示为 姓名
使用CONCAT连接
CONCAT(last_name,first_name) AS 姓名;
1.4 条件查询
SELECT 查询列表
FROM 表名
WHERE 筛选条件;
筛选条件最终为true或false
分类:
1.按条件表达式筛选
条件运算符:> < = != <> >= <=
2.按逻辑表达式筛选
逻辑运算符:&& || ! and or not
作用:用于来连接条件表达式
3.模糊查询
like
案例:查询员工名单中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%'
%任意多个字符,包含0个字符
_任意单个字符
\转义字符
'_$_' ESCAPE '$' 转义,将$当成普通字符
between and
案例:查询员工编号在100到120之间的员工信息
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 200;
包含临界值,即100和120都包含在内
使用between and 可以提高语句的简洁度
in
案例:查询员工的工种编号是IT_PROT,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT *
FROM employees
WHERE job_id in(IT_PORT,AD_VP,AD_PRES);
含义:判断某字段的值是否属于in列表中的某一项
特点:使用in提高语句简洁度
in列表的值类型必须一致或兼容,例如'123'和123
in等同于=,不支持like的用法
is null
案例:查询没有奖金的员工名和奖金率
SELECT *
FROM employees
WHERE commission_pct IS NULL;
IS NOT NULL查询有奖金的
=或<>不能用于判断null值
is nnull 或 is not null 可以判断null值
安全等于:<=>
IS NULL:仅仅可以判断NULL 值,可读性较高
<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低
LIKE:不能判断为NULL的情况
1.5 排序查询
SELECT 查询列表
FROM 表
[WHERE 筛选条件]
ORDER BY 排序列表 [asc|desc]
案例:查询员工信息,要求工资从高到底排序
升序
SELECT *
FROM employees
ORDER BY salary ASC;
降序
SELECT *
FROM employees
ORDER BY salary DESC;
特点:默认升序
ORDER BY 子句中可以支持单个字段,多个字段,表达式,函数,别名
ORDER BY 子句一般是放在查询语句的最后面,limit子句除外
案例:查询部门编号>=90的员工信息,按入职时间先后进行排序
SELECT *
FROM employees
WHERE department_id >=90
ORDER BY hiredate ASC;
按表达式排序
案例:按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
按别名排序
案例:按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
按函数排序
案例:按姓名的长度显示员工的信息和年薪
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM emplotees
ORDER BY LENGTH(last_name) DESC;
按多个字段排序
案例:查询员工信息,要求先按工资排序,再按员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC, employee_id DESC;
谁在前先按谁排
1.6 常见函数
调用:
select 函数名(实参列表) [from 表]
分类:
(一)单行函数
如concat, length, ifnull等
1.字符函数
length:获取参数值的字节个数
SELECT LENGTH('JOIN')
SELECT LENGTH('张三李四zl')
concat:拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
upper,lower :大小写变换
SELECT UPPER('join');
SELECT LOWER('JOIN');
substr , substring:截取指定索引处(区间)的字符,索引从1开始
SELECT SUBSTR('iiiiiii',5);
SELECT SUBSTR('iiiiiii',1,3);
instr:返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('HHUJIGHU','UJI') AS out_put;
trim:删除字符串前后的字符
SELECT TRIM(' UUU ') AS out_put;
SELECT TRIM('a' FROM 'aaaaaaiaaaaaaaaaaiaaaaaaaaaa') AS out_put;
lpad:在字符个数不够制定数目时,在前面填充指定字符
SELETC LPAD('HHH',10,'*') AS out_put;
字符数不够填充,超过截取
replace:替换
SELECT REPLACE('HUHUHUHUKIUI','H','U') AS out_put;
H被U替换,UUUUUUUUKIUI
2.数学函数
round:四舍五入
SELECT ROUND(1.23);
SELECT ROUND(1.557,2);
ceil:向上取整,返回>=该参数的最小整数
SELECT SEIL(1.02)
floor:向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99)
truncate 截断,保留小数点后几位
SELECT TRUNCATE(1.8999,1)
mod 取余
SELECT MOD(10,3)
mod(a,b) a-a/b*b
3.日期函数
now 返回当前系统日期+时间
SELECT NOW();
curdate 返回当前系统日期,不包含时间
SELECT CURDATE()
curtime 返回当前时间,不包含日期
SELECT CURTIME();
可以获取制定的部分,年月日小时分钟秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
str_to_date:解析日期,按默认格式返回
SELECT STR_TO_DATE('9-13-132','%m-%d-%Y);
132-9-13
data_format:将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
2018年6月6日
时间格式
格式符 | 功能 |
%Y | 四位的年份 |
%y | 2位的年份 |
%m | 月份(01,02,...,11,12) |
%c | 月份(1,2,...,11,12) |
%d | 日(01,02,03,...) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01,02,...,58,59) |
%s | 秒(00,01,02,...,58,59) |
4.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
5.流程控制函数
if函数: if else 效果
SELECT IF(10<5,'max','min');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'meiyou','you') 备注
FROM employees;
case函数
使用一:switch case 的效果;
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
案例:查询员工的工资,要求
部门号=30:显示为工资的1.1倍
部门号=40:显示为工资的1.2倍
部门号=50:显示为工资的1.3倍
其他部门:显示的工资为原工资
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
使用二:类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
案例:查询员工的工资的情况,如果:
工资>20000,显示A级别
工资>15000,显示B级别
工资>10000,显示C级别
否则,显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
(二)分组函数
功能:做统计使用,又称为统计函数,聚合函数,组函数
分类:sum求和,avg平均值,max最大值,min最小值,count计算个数
1,简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
2.参数支持哪些类型
sum,avg 一般用于处理数值型
max,min,count可以处理任何类型
3.是否忽略null值
以上分组函数都忽略null值
4.可以和distinct搭配使用
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
5.count 函数的详细介绍
统计一个常量值,等同于统计行数
SELECT COUNT('jujj') FROM employees;
统计行数还有
SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) employees;
效率
MYISAM存储引擎下,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比count(字段)要高一些
6.和分组函数一同查询的字段有限制
要求是group by后的字段
1.7 分组查询
SELECT 分组函数,列(要求出现在group by的后面)
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的列表
[ORDER BY 子句]
查询列表必须特殊,要求是分组函数和group by后面出现的字段
特点:1.分组查询中的筛选条件分为两类
分类 | 数据源 | 位置 | 关键字 |
分组前查询 | 原始表 | group by 子句的前面 | where |
分组后查询 | 分组后的结果集 | group by 子句的前面 | having |
分组函数做条件肯定是放在having子句中
能用分组前筛选,优先使用分组前筛选
2.group by 子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(用的较少)
3.也可以添加排序(排序放在整个分组查询的最后)
案例:查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees;
GROUP BY job_id;
案例:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id
添加复杂的筛选条件
案例:查询那个部门的员工个数>2
-
查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
2.根据1的结果进行筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING GROUP(*)>2;
按表达式或函数分组
案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
-
查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);
2.添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
按多个字段分组
案例:查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
添加排序
案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id;
ORDER BY AVG(salary) desc;
1.8 连接查询
又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:按年代分类:sq192标准,仅仅支持内连接
sq199标准(推荐),支持内连接+外连接(左外和右外)+交叉连接
按功能分类:内连接:等值连接
非等值连接
自连接
外连接:左外连接
右外连接
全外连接
交叉连接:
sq192标准,仅仅支持内连接
(一).等值连接
案例:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.'department_id'=department.'department_id';
2.为表起别名
提高语句的简洁度
区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
案例:查询员工名,工种号,工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.'job_id'=j.'job_id';
3.两个表的顺序可以替换
4.可以加筛选
案例:查询有奖金的员工名,部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.'department_id'=d.'department_id'
AND e.'commission_pct' IS NOT NULL;
5.可以加分组
案例:查询每个城市的不饿美女个数
SELECT COUNT(*) 个数,city
FROM departments d,loations l
WHERE d.'location_id'=l.'location_id'
GROUP BY city;
6.可以查排序
案例:查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.'job_id'=j.'job_id'
GROUP BY job_title
ORDER BY COUNT(*) DESC;
7.实现三表连接
案例:查询员工名,部门名,和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d.locations l
WHERE e.'department_id'=d.'department_id'
AND d.'location_id'=location_id
AND city LIKE 's%'
ORDER BY department_name DESC;
(二)非等值连接
案例:查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.'lowest_sal' AND g.'highest_sal'
AND g.'grade_level'='A';
(三)自连接
案例:查询员工和上级的名称
SELECT e.emp;oyee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.'manager_id'=m.'employee_id';
sq199标准(推荐),支持内连接+外连接(左外和右外)+交叉连接
SELECT 查询列表
FROM 表1 别名 [连接类型]
JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]
内连接 inner
左外 left [outer]
右外 right [outer]
全外 full [outer]
交叉连接[cross]
(一)内连接
特点:添加排序,分组,筛选
inner可以省略
筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
inner join 连接和sql92语法中的等值连接效果是一样的,都是查询交集部分
1,等值连接
案例:查询员工名,部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.'department_id'=d.'department_id';
2.非等值连接
3.自连接
案例:查询员工的姓名,上级的姓名
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.'manager_id'=m.'employee_id'
(二)外连接
特点:
外连接的查询结果为主表的所有记录
如果从表中有和他匹配的,则显示匹配的值
如果从表中没有和他匹配的,则显示null
外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
左外连接,left join左边的是主表
右外连接,right join右边的是主表
左外和右外交换两个表的顺序,可以实现同样的效果
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.'boyfriend_id'=bo.'id'
WHERE bo.'id' IS NULL;
全外连接
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.'boyfriend+id' = bo.'id';
交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo.\;
1.9 子查询
出现在其他语句内部的select语句,成为子查询或内查询
内部嵌套其他select语句的查询,称为外查询或主查询
SELECT first_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id=1700
)
分类:
按子查询出现的位置:
select后面:仅仅支持标量子查询
from后面:支持表子查询
where或having后面:标量子查询,列子查询,行子查询
exists后面(相关子查询):表子查询
按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
(一) where或having后面:标量子查询,列子查询,行子查询
特点:
子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用><>=<=<>
列子查询,一般搭配着多行操作符使用in any/some all
1.标量子查询
案例:谁的工资比Abel高
1查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel;
2.查询员工的信息,满足salary>1结果
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name='Abel
);
2.列子查询(多行子查询)
操作符 | 含义 |
IN/NOT IN | 等于列表中的任意一个 |
ANY/SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
案例:返回location_id是1400或1700的部门中所有员工姓名
1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM employees
WHERE location_id IN (1400,1700)
2.查询员工姓名,要求部门号是1列表中的某一个
SELECT DISTINCT department_id
FROM employees
WHERE location_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
);
3.行子查询(结果集一行多列或多行多列)
案例:查询员工编号最小且员工工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id), MAX(salary)
FROM employees
);
(二)select后面:仅仅支持标量子查询
案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
)个数
FROM departments d;
(三)from后面:支持表子查询
将子查询结果充当一个表,要求必须起别名
案例:
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grade g
ON ag_dp.ag BETWEEN lowest_sal AND highest_sal;
(四)exists后面(相关子查询):表子查询
结果;1/0
案例:查询员工名和部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
1.10 分页查询
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
SELECT 查询列表
FROM 表
[JOIN TYPE] JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后筛选
ORDER BY 排序的字段
LIMIT OFFSET,SIZE;
offset要显示条目的起始索引(起始索引从0开始)
size要显示的条目个数
特点:
limit语句放在查询语句的最后
公式:要显示的页数page,每页的条目数size
limit (page-1)*size,sioze;
案例:查询前五条员工信息
SELECT *
FROM employees
LIMIT 0,5;
1.11 联合查询
union联合 合并:将多条查询语句的结果合并成一个结果
案例:查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM employees
WHERE email LIKE '%a%' OR department_id>90;
SELECT *
FROM employees
WHERE email LIKE '%a%'
UNION
SELECT *
FROM employees
WHERE department_id>90;
特点:
要求多条查询语句的查询列数是一致的。
要求多条查询语句的查询的每一列的类型和顺序最好一致。
union关键字默认去重,如果使用union all 可以包含重复项。
2 DML语言
数据操纵语言:
插入:insert
修改:update
删除:delete
2.1 插入语句
方式一
INSERT INTO 表名(列名,...) VALUES(值1,...);
插入的值的类型要与列的类型一致或兼容
列数和值的个数必须一致
可以省略列名,默认所有列,而且列的顺序和表中的顺序一致。
方式二
INSERT INTO 表名
SET 列名=值,列名=值,...
两种方式比较
方式一支持插入多行,方式二不支持
方式一支持子查询,方式二不支持
2.2 修改语句
1.修改单表的记录
UPDATE 表名
SET 列=新值,列=新值
WHERE 筛选条件;
2.修改多表的记录
sq192
UPDATE 表1 别名,表2 别名
SET 列=值...
WHERE 连接条件
AND 筛选条件;
sq199
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值...
WHERE 筛选条件;
2.3 删除语句
方式一delete
DELETE FROM 表名 WHERE 筛选条件;
方式二truncate
TRUNCATE TABLE 表名;
两种方式的区别
delete可以加where条件,truncate不能加。
truncate删除,效率高一丢丢。
加入要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。
truncate删除没有返回值,delete删除有返回值。
truncate删除不能回滚,delete删除可以回滚。
3 DDL语言
数据定义语言
库和表的管理
一,库的管理
创建,修改,删除
二,表的管理
创建,修改,删除
创建:create
修改:alter
删除:drop
3.1 库的管理
1.库的创建
CREATE DATABASE 库名;
CREATE DATABASE IF NOT EXISTS 库名;
2.库的修改
RENAME DATABASE 库名 TO 新库名;
ALTER DATABASE 库名 CHARACTER SET gbk;
3.库的删除
DROP DATABASE 库名;
DROP DATABASE IF EXISTS 库名;
3.2 表的管理
1.表的创建
CREATE TABLE 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
2.表的修改
修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
删除列
ALTER TABLE author DROP COLUMN annuaL;
修改表名
ALTER TABLE author RENAME TO book_author;
3.表的删除
DROP TABLE book_author;
DROP TABLE IF EXISTS book_author;
4.表的复制
仅仅复制表的结构
CREATE TABLE copy LIKE author;
复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;
3.3 常见的数据类型
数值型:
整型
小数:
定点数
浮点数
字符型
较短的文本:char,varchar
较长的文本:text,blob(较长的二进制数据)
日期型:
整数类型 | 字节 | 范围 |
Tinyint | 1 | 有符号:-128~127 无符号:0~255 |
Smallint | 2 | 有符号:-32768~32767 无符号:0~65535 |
Mediumint | 3 |
|
Int,Integr | 4 |
|
Bigint | 8 |
|
浮点数类型 | 字节 | 范围 |
float | 4 |
|
double | 8 |
|
定点数类型 | 字节 | 范围 |
DEC(M,D) DECIMAL(M,D) | M+2 | 最大取值范围与double相同,给定decimal的有效范围由M和D决定 |
字符串类型 | 最多字符数 | 描述及存储需求 | 特点 | 空间的耗费 | 效率 |
char(M) | M | M为0~255之间的整数 | 固定长度的字符 | 比较耗费 | 高 |
varchar(M) | M | M为0~65535之间的整数 | 可变长度的字符 | 比较节省 | 低 |
日期和时间类型 | 字节 | 最小值 | 最大值 | 时区等影响 |
date | 4 | 1000-01-01 | 9999-12-31 |
|
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 不受 |
timestamp | 4 | 19700101080001 | 2038年的某个时刻 | 受 |
time | 3 | -838:59:59 | 838:59:59 |
|
year | 1 | 1901 | 2155 |
|
3.4 常见约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性。
分类:六大约束
NOT NULL 非空,用于保证该字段的值不能为空,比如姓名,学号等
DEFAULT 默认,用于保证该字段有默认值,比如性别
PRIMARY KEY 主键,用于保证该字段的值具有唯一性,并且非空,比如学号,员工编号等
UNIQUE 唯一,用于保证该字段的值具有唯一性,可以为空,比如座位号
CHECK 检查约束,mysql中不支持
FOREIGN KEY 外键,用于限制两个表的关系,用于保证该字段的值必须来自主表的关联列的值在从表添加外键约束,用于引用主表中某列的值
添加约束的时机
创建表时
修改表时
约束的添加分类
列级约束
六大约束语法上都支持,但外键约束没有效果
表级约束
除了非空,默认,其他都支持
1.创建表时添加约束
添加列级约束
直接在字段名和类型后面追加约束类型即可
只支持:默认,非空,主键,唯一
添加表级约束
在各个字段的最下面
【constraint 约束名】约束类型(字段名)
主键和唯一的区别
| 唯一性 | 空 | 一个表可以有多少个 | 是否允许组合 |
主键 | 有 | 不允许 | 至多有1个 | 允许,但不推荐 |
唯一 | 有 | 允许 | 可以有多个 | 允许,但不推荐 |
外键:
要求在外表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
主表的关联列必须是一个key(一般是主键或唯一)
插入数据时,先插入主表,在插入从表,删除数据时,先删除从表,再删除主表。
2.修改表时添加约束
添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN syuname VACHAR(20) NOT NULL;
添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);
添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
3.修改表时删除约束
删除非空约束
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;
删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;
3.5 标识列(auto_increment)
又称自增长列
含义:可以不用手动的插入值,系统提供默认的序列表,从1开始
特点:
标识列必须和主键搭配吗?不一定,但要求是一个key
一个表可以有几个标识列?至多一个
标识列可以通过SET auto_increment_increment=3设置步长,可以通过手动插入值设置起始值。
1.创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARU KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
改变自增长步长
SET auto_increment_increment=步长;
2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
3.修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
4 TCL语言
事务控制语言
事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
事务的ACID属性:
- 原子性:一个事务不可再分割,要么都执行,要么都不执行
- 一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
- 隔离性:一个事务的执行不受其他事务的干扰
- 持久性:一个事务一旦提交,则会永久的改变数据库的数据。
4.1 事务的创建
隐式事务:事务没有明显的开始和结束的标记
比如insert,update,delete语句
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用。
set autocommit=0;
步骤1:开启事务
set autocommit=0;
start transaction;可选
步骤2:编写事务中的sql语句(select,insert,update,delete)
语句1;
语句2;
...
步骤3:结束事务
commie;提交事务
rollback;回滚事务
savepoint;节点名,设置保存点,之搭配rollback to使用
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
- 脏读:对于两个事务T1,T2。T1读取了已经被T2更新但还没被提交的字段。之后,若T2回滚,T1读取的内容就是临时且无效的。
- 不可重复读:对于两个事务T1,T2。T1读取了一个字段,然后T2更新了该字段。之后,T1再次读取同一个字段,值就不同了。
- 幻读:对于两个事务T1,T2。T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。之后,如果T1再次读取同一个表,就会多出几行。
每启动一个mysql程序,就会获得一个单独的数据库来连接。每个数据库连接都有一个全局变量@@tx_isolation,表示当前的事务隔离级别。
查看当前的隔离级别:SELECT@@tx_isolation
设置当前mySQL连接的隔离级别(例子)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置数据库系统的全局的隔离级别(例子)
SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
read uncommitted | Y | Y | Y |
read committed | N | Y | Y |
repeatable read | N | N | Y |
serializable | N | N | N |
mysql中默认第三个隔离级别repeatable read
oracle中默认第二个隔离级别read committed
5 视图
含义:虚拟表,和普通表一样使用
mysql15.1版本出现的新特性,是通过表动态生成的数据
5.1 创建视图
CREATE VIEW 视图名
AS
查询语句;
5.2 视图的修改
方式一
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
方式二
ALTER VIEW 视图名
AS
查询语句;
5.3 删除视图
DROP VIEW 视图名,视图名,...;
5.4 查看视图
DESC 视图名;
SHOW CREATE VIEW 视图名;
5.5 视图的更新(更新里面的数据)
插入
修改
视图的可更新性和视图中查询的定义有关系,以下类型的视图是不可更新的:
包含以下关键字的sql语句:分组函数,distinct,group by,having,union或者union all
常量视图
Select中包含子查询
join
from 一个不能更新的视图
where子句的子查询引用了from子句中的表
| 创建语法的关键字 | 是否实际占用物理空间 | 使用 |
视图 | create view | 只是保存了sql逻辑 | 增删改查,一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |
6 变量
系统变量:
全局变量
会话变量
自定义变量:
用户变量
局部变量
6.1 系统变量
说明:变量由系统提供,不是用户定义,属于服务器层面
1.查看所有的系统变量
SHOW GLOBAL [SESSION] VARIABLES;
2.查看满足条件的部分系统变量
SHOW GLOBAL [SESSION] VARIABLES LIKE '%CHAR%';
3.查看指定的某个系统变量的值
SELECT @@GLOBAL|[SESSION].系统变量;
4.为某个系统变量赋值
方式一
SET GLOBAL|[SESSION] 系统变量名=值;
方式二
SET @@GLOBAL|[SESSION].系统变量名=值;
注意:如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认session
6.2 会话变量
作用域:仅作用于当前会话(连接)有效
1.查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
2.查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
3.查看制定的某个会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
4.为某个会话变量复制
方式一
SET @@tx_isolation='read-uncommitted';
方式二
SET SESSION tx_isolation='read-committed';
6.3 自定义变量
说明:变量是系统自定义的,不是由系统的
使用步骤;
声明
赋值
使用(查看,比较,运算等)
1.用户变量
作用域:针对于当前会话(连接)有效,等同于会话变量的作用域
应用在任何地方,也就是begin end 里面或begin end 外面
(1)声明并初始化
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
(2)赋值(更新用户变量的值)
方式一:通过SET或SELECT
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
方式二;通过SELECT INTO
SELECT 字段 INTO 变量名
FROM 表;
(3)使用(查看用户变量名)
SELECT @用户变量名
2.局部变量
作用域:仅仅在定义他的begin end 中有效
应用在begin end 中的第一句话
(1)声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
(2)赋值
方式一:通过SET或SELECT
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二;通过SELECT INTO
SELECT 字段 INTO 局部变量名
FROM 表;
(3)使用
SELECT @局部变量名
| 作用域 | 定义和使用位置 | 语法 |
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号,不用限定类型 |
局部变量 | BEGIN END中 | 只能在BEGIN END 中,且为第一句话 | 一般不用加@符号,需要限定类型 |
7 存储过程函数
7.1 创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
7.2 调用语法
SELECT * from admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,'password')
VALUES ('john1','0000'),('lily','0000');
ENS $
CALL myq1() $
2.创建带in模式参数的存储过程
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
CALL myp2('joib') $
案例
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT '';
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username=username
AND admin.password=PASSWORD;
SELECT result;
END $
CALL myp3("join','0000') $
3.创建带out模式的存储过程
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName
END $
SET @bName$
CALL myp5('ggg',@bName) $
SELECT @bName$
案例:根据女神名,返回对应的男神名和男神魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20),OUTPUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCp
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName
END $
SET @bName,@userCP$
CALL myp5('ggg',@bName,@userCP) $
SELECT @bName,@userCP$
4.创建带inout模式参数的存储过程
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
7.3 删除存储过程
DROP PROCEDURE 存储过程名;
案例
DROP PROCEDURE p1;
7.4 查看存储过程的信息
SHOW CREATE PROCEDURE 存储过程名;
不支持desc
8 函数
8.1 创建语法
CREATE FUNCTION 函数名(参数列表)RETURNS 返回列表
BEGIN
函数体
END
注意:
8.2 调用语法
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c ;
END $
SELECT myf1()$
2.有参有返回
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name=empNmae;
RETURN @sal;
END $
SELECT myf2('k_ing') $
8.3 查看函数
SHOW CREATE FUNCTION 函数名;
8.4 删除函数
DROP FUNCTION 函数名;
9 流程控制结构
9.1 分支结果
case 要判断的字段,变量或表达式
when 常量1 then 要返回的值1或语句1;
when 常量2 then 要返回的值2或语句2;
...
else 要返回的值n或语句n;
end case
使用二:类似于 多重if ,一般用于区间判断
case
when 条件1 then 要返回的值1或语句1
when 条件2 then 要返回的值2或语句2
...
else 要返回的值n或语句n
end case
特点:
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
WHEN THEN SELECT 'D';
END CASE;
END $
CALL test_case(95)$
3.if结构
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;
应用在begin end 中
CREATE PROCEDURE test_case(IN score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
IF score>=80 THEN RETURN 'B';
IF score>=60 THEN RETURN 'C';
IF THEN RETURN 'D';
END IF;
END $
SELECT test_case(95)$
9.2 循环结构
[标签:]while 循环条件 do
循环体;
end while [标签];
2.loop
[标签:]loop
循环体;
end loop [标签];
可以模拟简单的死循环
[标签:]repeat
循环体;
until 结束循环的条件
end repeat [标签];
名称 | 特点 | 位置 |
while | 先判断后执行 | begin end中 |
loop | 没有条件的死循环 | begin end中 |
repeat | 先执行后判断 | begin end中 |