一、mysql基础
(一) 基础概念和配置
1.基础概念
- DB 数据库
- DBMS 数据库管理系统
- SQL 结构化查询语言
2.服务的启动
-
方式一: 通过命令行启动 net start mysql
-
方拾贰: 通过计算机->属性->服务->找到mysql服务
2.mysql的连接
mysql -h[主机名] -u[账号] -p[密码]
二、DQL语言的学习
(一) 基础命令
1.执行顺序
mysql执行sql的顺序从 From 开始,以下是执行的顺序流程
1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积,生成Temp1
2、JOIN table2 所以先是确定表,再确定关联条件
3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2
4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3
5、GROUP BY 对中间表Temp3进行分组,产生中间表Temp4
6、HAVING 对分组后的记录进行聚合 产生中间表Temp5
7、SELECT 对中间表Temp5进行列筛选,产生中间表 Temp6
8、DISTINCT 对中间表 Temp6进行去重,产生中间表 Temp7
9、ORDER BY 对Temp7中的数据进行排序,产生中间表Temp8
10、LIMIT 对中间表Temp8进行分页,产生中间表Temp9
2.常见关键字和特殊用法
DISTINCT去重
注:当出现双去重时,默认取去重次数少的列进行连接,当一般达不到效果
SELECT DISTINCT EMA,DISTINCT EMB, FROM a
<=>安全等于
即可判断具体值也可判断空值
#<=>
SELECT * FROM employees WHERE commission_pct <=> NULL
SELECT * FROM employees WHERE employee_id <=> 100
DESC
查询表的结构
DESC employees
SHOW INDEX
查看表的索引
SHOW INDEX FROM 表名
+
mysql的+号只是运算符,不能对字符串进行连接
# null + 123 结果为null
# 'abc' + 123 结果为123
# '123' + 123 结果为 256
3. 模糊查询
like:
- 通常与统配符(%,_)一起使用 %:匹配所有字符 _:匹配单个字符
- 自定义转义符 ESCAPE
between and :
- 使代码更加简洁,增加可读性
in:
- 相当于or,增加可读性
- 只要满足其中一个条件成立,则结果为真
is null : 判断字段的值是否为空
is not null: : 判断字段的值是否不为空
#LIKE
SELECT * FROM employees WHERE job_id LIKE '%&_%' ESCAPE '&'
# BETWEEN AND
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 10000
# IN
SELECT * FROM employees WHERE job_id in ('IT_PROG','FI_ACCOUNT')
# IS NULL | IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NULL
SELECT * FROM employees WHERE commission_pct IS NOT NULL
4.排序查询
ORDER BY 字段 | 多个字段 | 别名 | 表达式 | 函数 ASC | DESC
ASC :升序 DESC :降序
- 当不指名升序降序时,默认升序
- 当有多个排序时,优先主排序,后面以此类推
5.分页查询
limit offIndex, size
起始下标为0
page:第几页
size:显示的数据条数
offIndex = (page-1) * size
(二)常见函数
1.单行函数
(1)字符串函数
# CONCAT 用于字段名或字符串进行连接
SELECT CONCAT(str1,str2,str3,...)
# LENGTH 返回字段值的字节数
SELECT LENGTH(字段名)
# upper:将字符串小写变大写 lower:将字符串大写变小写
SELECT UPPER(str) LOWER(str)
# SUBSTR(str, index, len) 截取从index为始长度为len的字符串
SELECT SUBSTR('zhangHong' , 1 , 3)
# INSTR(str,substr)str:字符串 substr:子串 返回子串第一次出现的索引 找不到返回 0
SELECT INSTR(str,substr)
# TRIM(substr FROM str) 去掉str前后substr的字符
SELECT TRIM('a' FROM 'aaAAAaa')
# LPAD 用指定strsub子串实现左填充指定字符长度 RPAD
SELECT LPAD('晓光',10,'strsub')
# REPLACE 将String中所有出现的from_str替换为to_str。
REPLACE(String,from_str,to_str)
# IFNULL 如果该字段的值为空,就将该值替换为val
SELECT IFNULL(name字段名,val替换值)
# ISNULL(字段名) 如果name为空的话返回1,反之返回0
SELECT ISNULL(字段名)
(2)数学函数
#ROUND 四舍五入
#CEIL 向上取整 >=的最小整数
#FLOOR 向下取整 <=的最小整数
#TRUNCATE(X,LEN) 截断X小数LEN长度位
SELECT TRUNCATE(1.223333,1)
#MOD取余 a-a/b*b a是负数 结果就 为负数,反之为整数
(3)日期函数
NOW 返回当前系统日期+时间
CURDATE 返回当前系统日期,不包含时间
CURTIME 返回当前系统时间,不包含日期
YEAR() MONTH() MONTHNAME()返回英文单词月份 DAY() HOUR() MINUTE() SECOND()
#STR_TO_DATE 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') AS result;
#DATE_FORMAT 将日期转换为指定的字符串
SELECT DATE_FORMAT(date,format)
#DATEDIFE(data1,data2)求两个日期相差的天数 data1-data2
SELECT DATEDIFF('2020-4-12','2020-4-15')
(4)流程控制函数
IF函数
- SELECT IF(expr1,expr2,expr3)
- 相当于三目运算符
CASE函数
- 方式一
- 相当于JAVA的switch语句
- 方式二
- 相当于JAVA的if else语句
IF函数
# SELECT IF(expr1,expr2,expr3) 1:表示式 2:成真输出 3:成假输出
SELECT IF(10>3,1,0)
----------------------------
CASE函数
# 方式一
SELECT salary, department_id,
CASE department_id
WHEN 30 THEN salary*1.2
WHEN 40 THEN salary*1.3
WHEN 50 THEN salary*1.4
ELSE salary
END AS Fsalary
FROM employees
# 方式二
SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary BETWEEN 15000 AND 20000 THEN 'B'
WHEN salary BETWEEN 10000 AND 15000 THEN 'C'
ELSE 'D'
END result
FROM employees
-------------------------
(5)其它
# VERSION 查看mysql版本
SELECT VERSION()
# DATABASE 查看当前数据库
SELECT DATABASE()
# USER 查看当前用户
SELECT USER()
2.聚合函数
特征
- 都忽略了空值
- 使用count(*)时效率较高
- 一般和group by使用
#sum
#avg
#max
#min
#count
#round
(三)分组查询
格式
SELECT
FROM
WHERE
GROUP BY
ORDER BY
HAVING
查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id > 105
GROUP BY manager_id # 单字段 | 多字段 | 函数 | 表达式 | 别名
HAVING MIN(salary) > 5000
(四)连接查询
- 内连接:FROM 表1 AS 别名 INNER JOIN 表2 AS 别名 ON 连接条件
- 左外连接:FROM 表1 AS 别名 LEFT JOIN 表2 AS 别名 ON 连接条件
- 以表一为主表
- 右外连接:FROM 表1 AS 别名 RIGHT JOIN 表2 AS 别名 ON 连接条件
- 以表二为主表
- 全外连接:FROM 表1 AS 别名 FULL JOIN 表2 AS 别名 ON 连接条件
- 以表一表二为主表
1.内连接
(1)等值连接
特征
- 连接的结果为交集部分
- 当有n个表时,至少有n-1个连接条件
查询全部成员所对应的部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d ON e.employee_id = d.department_id
(2)非等值连接
查询员工的工资和工资级别
SELECT salary 工资,grade_level 工资级别
FROM employees e
INNER JOIN job_grades ON e.salary BETWEEN lowest_sal AND highest_sal
(4)自连接
查询员工名和上级的名称
SELECT e.last_name 员工名,m.last_name 上级名
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
2.外连接
(1)左外连接
包含左主表中的所有内容,当子表与主表连接条件不等时,子表数据显示为空
(2)右外连接
包含右主表中的所有内容,当子表与主表连接条件不等时,子表数据显示为空
(3)全外连接
3.交叉连接
生成的表即为笛卡尔积表
(五)子查询
- 按子查询出现的位置
- select后面
- 支持标量子查询
- from后面
- 支持表子查询
- where或having后面
- 支持标量子查询
- 支持列子查询
- 支持行子查询
- exists后面
- 支持标量子查询
一行一列
- 支持列子查询
一列多行
- 支持行子查询
多行多列
- 支持表子查询
多行多列表
- 支持标量子查询
- select后面
(六)union联合查询
将多条查询结果合并为一条
- 要求多条查询结果的列数要一致
- 要求多条查询结果的列的属性和类型要一致
- union会去重 union all不去重
SELECT * FROM 表1
UNION
SELECT * FROM 表2
三、DML语言的学习
DML:数据操作语言
(一)插入
- 方式一:支持子查询 和 多条插入
- 语法1:
INSERT INTO 表名(列1,列2,...) VALUES(值1,值2,...)
- 语法2:
INSERT INTO 表名(列1,列2,...)子查询
- 特征
- 列名和值必须对应
- 可以不写列名,但值的插入默认为表的全部字段
- 方式二:
- 语法:
INSERT INTO 表名 SET 列1=值1,列2=值2
(二)修改
1.修改单表的数据
语法:
UPDATE 表名 SET 列1=值1,列2=值2,... WHERE 筛选条件
2.修改多表的数据
语法:
UPDATE 表1 别名 INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件
(三)删除
1.单表数据的删除
语法:
DELETE FROM 表名 WHERE 筛选条件
2.多表数据的删除
语法:
DELETE 要删除数据表的别名
FROM 表1 别名 INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
WHERE 筛选条件
3.delete和truncate的区别
区别:
- delete能删除指定表中数据,truncate只能删除全部数据
- 当要删除全部数据时,truncate效率比delete高
- 当主键有自增条件时,delete删除全部数据后,自增数不重新计数,truncate则从1开始计数
- delete有返回值,而truncate无返回值
- delete有回滚,而truncate无回滚
四、DDL语言的学习
DDL:数据定义语言
(一)库的管理
1.创建
语法:
CREATE DATABASE IF NOT EXISTS 库名
2.修改
语法:
ALTER DATABASE 库名 CHARACTER SET 新的字符集
修改字符集
3.删除
语法:
DROP DATABASE IF EXISTS 库名
(二)表的管理
展示表的结构
- 语法:
SHOW TABLES
1.创建
语法
CREATE TABLE IF NOT EXISTS 表名(
字段名 字段类型【长度】 【约束】
id int(4)
)
2.修改
添加列 | 修改列名 | 修改列的属性和约束 | 删除列
- 语法:
ALTER TABLE 表名 add | change | modify | drop COLUMN 列名 【类型 | 约束】
修改表名
- 语法:
ALTER TABLE 旧表名 RENAME TO 新表名
3.删除
语法:
DROP TABLE IF EXISTS 表名
4.复制
(1)仅仅复制表的全部字段
语法:
CREATE TABLE copy(复制的表) LIKE author(被复制的表)
(2)复制表的字段和数据
语法:
CREATE TABLE copy(复制的表) 子查询
(3)仅仅复制表的部分字段
语法:
CREATE TABLE copy(复制的表) SELETE 指定字段 FROM 被复制的表 WHERE 1=2
5.自增长列
AUTO_INCREMENT
- 列的键值唯一(即该列含有主键约束或唯一约束)
- 一个表中至多只有一个自增长列
- 列的类型只能是数值型
# 创建自增长列
CREATE TABLE 表名(
id INT PRIMARY KEY AUTO_INCREMENT
)
# 设置自增长的起始下标(插入一条)
INSERT INTO 表名(id) VALUE(起始下标)
# 设置自增长的步长
set AUTO_INCREMENT_INCREMENT=步长
五、常见的数据类型
(一)数值型
- Tinyint 字节数:1
- Smallint 字节数:2
- Mediumint 字节数:3
- Int 字节数:4
- Bigint 字节数:5
特征:
- 默认有符号 (有负数) unsigned
- 默认长度为 11 ZEROFILL 零填充(当值不满足指定长度时,则用0填充)
(二)小数型
- 浮点型
- float(M,D) 字节:4
- double (M,D) 字节:8
- 定数型
- DECIMAL(M,D) 字节:M+2
- 特征
- M:整数位数+小数位数
- D:小数位数
- DECIMAL精度比DOUBLE高
(三)字符串型
- 较短的文本
- char(M)
M可以省略
M:最大字符数
固定的字符长度
空间耗费较大
效率高
- varchar(M)
M不能省略
M:最大字符数
可变的字符长度
空间耗费较小
效率低
- set(字符串1,字符串2,…)
只能插入指定的字符串集合
- 较长的文本
- text
(四)日期型
类型 数据格式 所占字节数 数据范围 特点
- date
只保存日期
- datetime
保存日期和时间
8 1000-9999- timestamp
保存日期+时间
4 1970-2038 时区影响- time
只保存时间
- year
只保存年份
六、常见约束
六大约束
NOT NULL
字段值不能为空
UNIQUE
字段值唯一,但可以为空一个表中可以有多个
DEFAULT
当用户不输入用户值时,该字段有默认值
CHECEK
检查当前字段是否符合条件
PRIMARY KEY
字段值唯一,且不能为空一个表中至多有一个
FOREIGN KEY
用于维系两个有关系的表,在从表中增加外键约束,用于引用主表中某列的值主键 外键 唯一 通过DROP删除
#列级约束
id INT PRIMARY KEY
#表级约束条件(除非空,默认)
CONSTRAINT pk PRIMARY KEY(id)
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)
#修改列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 约束
#修改表级约束
ALTER TABLE 表名 ADD CONSTRAINT 昵称 PRIMARY KEY(列名)
级联删除与级联置空
#设置级联删除
ALTER TABLE 表名 MODIFY CONSTRAINT 昵称 FOREIGN KEY(从表字段) REFERENCES 主表(id)ON DELETE CASCADE;
DELETE FROM major WHERE id=3;
#设置级联置空
ALTER TABLE 表名 MODIFY CONSTRAINT 昵称 FOREIGN KEY(从表字段) REFERENCES 主表(id)ON DELETE SET NULL;
DELETE FROM major WHERE id=2;
七、TCL语言的学习
(一)事务
1.概念
事务(transaction):一个不可再分的工作单元,通常包含一组SQL语句
2.事务的四大特性(ACID)
原子性(Atomicity):一个事务是一个不可再分的单元,具有原子性
一致性(Consistency):一个事务执行之前和执行之后都必须处于一致性状态
隔离性(Isolate):一个事务的执行不受其它并发事务的影响,即事务与事务之间相互隔离
持久性(Durability):一个事务一旦提交,对数据的改变将是持久性的
3.关于事务的一些术语
- 关闭自动提交事务:Set autocommit=0
- 开启事务:Start Transaction
- 提交事务:Commit
- 回滚事务:Rollback
==事务前后数据存储位置:==`事务未提交前,只会将操作的历史记录保存在内存中,底层数据不会发生变化,只有事务提交后才会将操作后的数据同步到底层数据中`
4.事务特性隔离性
`隔离性产生的三大问题`
- 脏数据:指一个事务读取了另外一个事务未提交的数据。
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
- 幻像读:是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
隔离性的四个等级
脏数据
不可重复读
幻像读
- 读未提交:read uncommitted × × ×
- 读已提交:read committed √ × ×
- 可重复读:repeatable read √ √ ×
- 串行化: serializable √ √ √
(二)事务处理
1.事务的操作流程
一、关闭事务自动提交
SET autocommit=0;
二、开启事务
START TRANSACTION;
三、一句或一组SQL语句
....
四、事务提交或回滚
COMMIT; | ROLLBACK;
2.事务隔离性操作流程
SET GLOBAL | SESSION TRANSACTION ISOLATION LEVEL 隔离等级
八、视图的讲解
概念:视图是由数据库中的一个表或多个表导出的虚拟表,是一种虚拟存在的表,方便用户对数据的操作
一、视图的创建
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS
SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
二、视图查询
SELECT * FROM 视图名
三、修改视图
ALTER VIEW view_user
AS
SELECT id,name FROM tb_user where id in (select id from tb_user);
四、删除视图
DROP VIEW IF EXISTS view_user;
九、变量
(一)系统变量
查看所有的系统变量
SHOW GLOBAL | 【SESSION】 VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL | 【SESSION】 VARIABLES like ‘%char%’;
查看指定的某个系统变量的值
select @@global | [session].系统变量名
为某个系统变量赋值
set global | 【session】 系统变量名=值
set global | 【session】.系统变量名=值
(二)自定义变量
1.用户变量
用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接,但是当当前连接断开后,其所定义的用户变量都会消失。
一、声明并赋值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
二、更新值
select 值 into @变量名 from 表;
三、使用
select @变量名
#样例
SET @a=1,@b=2
SET @sum=@a+@b
SELECT @sum
2.局部变量
MySQL局部变量常用在存储过程的语句块(BEGIN / END)中,其作用域仅限于当前语句块内,当语句块执行完毕后,局部变量就消失了。
一、声明并赋值
declare 变量名 类型
declare 变量名 类型 default 值
二、更新值
set 局部变量名 = 值
set 局部变量名 := 值
select @局部变量名 := 值
select 值 into 局部变量名
三、使用
select 局部变量名
#样例
DELIMITER $$
DROP PROCEDURE IF EXISTS `tmpVar_test`$$
CREATE PROCEDURE `test`.`tmpVar_test`(OUT aa VARCHAR(5),OUT bb INT)
BEGIN
DECLARE a VARCHAR(5);
DECLARE b INT DEFAULT 22;
SET aa = a;
SET bb = b;
END$$
DELIMITER ;
如果没有给变量指定默认值,则它的系统默认值为 NULL。
也可以使用 SET 关键字给变量赋值。
十、存储过程与函数
(一)储存过程
对数据库 SQL 语言层面的代码封装与重用。
1.创建
存储过程的参数
IN
输入参数:表示调用者向过程传入值(传入值可以是字面量或变量);OUT
输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量);INOUT
输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)。
delimiter $; 结束标志
-- 创建存储过程
create procedure 存储过程名(参数列名)
begin
定义局部变量;
SQL语句;
end;
2.调用
call 存储过程名(参数列表);
(二)函数
存储函数与存储过程唯一区别就是存储函数具有返回值,而存储过程没有返回值。但是,因为存储过程传递的参数有输出参数类型,也用来可以代替返回值,因此,存储过程完全可以完成存储函数的操作。
1.创建
set global log_bin_trust_function_creators=1; # 设置允许创建函数
delimiter $ --将sql语句结束符号修改为$,这样只有sql遇到$时才开始执行
create function 存储函数名([参数名 类型,...]) returns type --返回值类型
begin
...
return 变量名;
end$
delimiter ; --将结束符修改为默认的分号
2.调用
select 存储函数名(参数列表);
3.案例
set global log_bin_trust_function_creators=1;
CREATE FUNCTION c2() RETURNS INT
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 0;
DECLARE c INT DEFAULT 2;
SET a = 1;
SET b = 2;
SET c = a + b;
RETURN c;
END;
SELECT c2();
十一、流程控制结构
(一)分支结构
1.IF语句
#格式
IF condition THEN
...
ELSE condition THEN
...
ELSE
...
END IF;
#案例
-- 创建存储过程
CREATE PROCEDURE example_if (IN x INT)
BEGIN
IF x = 1 THEN
SELECT 1;
ELSEIF x = 2 THEN
SELECT 2;
ELSE
SELECT 3;
END IF;
END;
-- 调用存储过程
CALL example_if(2);
2.CASE语句
#格式
CASE value 条件 | 表达式
WHEN value THEN ...
WHEN value THEN ...
ELSE ...
END CASE
#案例
-- 创建存储过程
CREATE PROCEDURE example_case(IN x INT)
BEGIN
CASE x
WHEN 1 THEN SELECT 1;
WHEN 2 THEN SELECT 2;
ELSE SELECT 3;
END CASE;
END;
-- 调用存储过程
CALL example_case(5);
(二)循环结构
1.while循环
#格式
WHILE condition DO
...
END WHILE;
#案例
-- 创建存储过程
CREATE PROCEDURE example_while(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
WHILE i <= 100 DO
SET s = s+i;
SET i = i+1;
END WHILE;
SET sum = s;
END;
-- 调用存储过程
CALL example_while(@sum);
SELECT @sum;
2.leap循环
#格式
[LEAVE标签]:LOOP
...
END LOOP
#案例
-- 创建存储过程
CREATE PROCEDURE example_loop(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
loop_label:LOOP
SET s = s+i;
SET i = i+1;
IF i>100 THEN
-- 退出LOOP循环
LEAVE loop_label;
END IF;
END LOOP;
SET sum = s;
END;
-- 调用存储过程
CALL example_loop(@sum);
SELECT @sum;
3.repeat循环
#格式
REPEAT
...
UNTIL condition
END REPEAT
#案例
-- 创建存储过程
CREATE PROCEDURE example_repeat(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
REPEAT
SET s = s+i;
SET i = i+1;
UNTIL i > 100
END REPEAT;
SET sum = s;
END;
-- 调用存储过程
CALL example_repeat(@sum);
SELECT @sum;
4.ITERATE语句
ITERATE语句可以出现在LOOP、REPEAT和WHILE语句内,其意为“再次循环”。
该语句的格式与LEAVE大同小异,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。
-- 创建存储过程
CREATE PROCEDURE example_iterate(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE s INT DEFAULT 0;
loop_label:LOOP
SET i = i+1;
IF i > 10 THEN
-- 退出整个循环
LEAVE loop_label;
END IF;
IF (i mod 2) THEN
SET s = s+i;
ELSE
-- 退出本次循环,继续下一个循环
ITERATE loop_label;
END IF;
END LOOP;
SET sum = s;
END;
-- 调用存储过程
CALL example_iterate(@sum);
SELECT @sum;