MySQL知识点
1、SQL
1.1、DDL(数据定义语言)
用来定义数据库对象:创建库,表,列等。
- 创建数据库
- 修改数据库
- 添加一列
- 查看表的字段信息
- 修改一个表得字段类型
- 删除一列
- 修改表名
- 查看表的创建细节
- 修改表的列名
- 删除表
- 修改表的字符集
1.2、DML(数据操作语言)
用来操作数据库表中的记录,是对表中的数据进行增,删,改的操作。
- 插入操作
- 更新操作
- 删除操作
- DELETE
- TRUNCATE
1.3、DQL(数据查询语言)
用来查询数据
条件查询
where
运行符及关键字
- = ,!= , < , > , >= , <=
- between … and 值在什么范围
- IN 固定值得范围
- IS NULL IS NOT NULL
- AND
- OR
- NOT
模糊查询
使用 like关键字后跟通配符
通配符:
- _ 任意一个字符
- % 任意0~n个字符
字段控制查询
- 去掉重复记录 distinct
- 对查询结果起别名 as
排序
ORDER BY
排序类型:
- 升序 ASC 从小到大
- 降序 DESC 从大到小
聚合函数
sum,avg,max,min
分组查询group by
在使用分组时,select后面直接跟的字段一般都出现在group by 后
将查询结果按照1个或多个字段进行分组,字段值相同的为一组。当group by单独使用时,只显示出每组的第一条记录,所以group by单独使用时的实际意义不大。
- group by + group_concat()
SELECT gender,GROUP_CONCAT(name) from employee GROUP BY gender;
- group by + 聚合函数
- group by + having (having 与 where的区别)
LIMIT
分页查询
1.4、DCL(数据控制语言)
用来定义访问权限和安全级别
2、SQL数据类型
2.1、数值类型
- int
- integer
- float
- double
- bigint
- tinyint(1表示true,0表示false)
2.2、字符串类型
- char
- varchar
2.3、时间与日期类型
- date
- timestamp
- DateTime
- time
2.4、枚举
- enum
3、数据的完整性
3.1、实体完整性
表中的一行(一条记录)代表一个实体(entity),标识每一行数据不重复。行级约束。
约束类型:
- 主键约束
- 唯一约束
- 自增长约束
3.2、域完整性
限制此单元格的数据正确,不对照此列的其它单元格比较,域代表当前单元格。
- 非空约束(not null)
- 默认值约束(default)
3.3、参照完整性
是指表与表之间的一种对应关系,主外键关系。
4、多表关系
4.1、表之间的关系
- 一对一
- 一对多
- 多对多
4.2、多表查询
4.2.1、连接查询
关联多个表进行查询。
什么是笛卡尔集:同时查询两个表,出现的就是笛卡尔集结果。
连接方式:
- 内连接
- 外连接
- 左连接
左边表当中的数据全部查出,右边表当中,只查出满足条件的内容。 - 右连接
右连接会把右当中的数据全部查出,左表当中只查出满足条件的数据
- 自然连接
连接查询会产生无用笛卡尔集,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式
4.2.2、子查询
一个select语句中包含另一个完整的select语句,或两个以上SELECT,那么就是子查询语句了。
子查询出现的位置:
- where后,把select查询出的结果当作另一个select的条件值
- from后,把查询出的结果当作一个新表;
4.2.3、自连接
自己连接自己,起别名。树节点
5、函数
5.1、字符串函数
- concat(s1,s2,…) 将传入的字符串连接成一个字符串,如果参数中有NULL,则结果返回NULL
SELECT CONCAT("youyuan","12")
- concat_ws(separator,str1,str2) 类似于concat,第一个参数为分隔符,如果分隔符为NULL,则整个结果都为NULL,除了分隔符外的任何参数为NULL,NULL值会被忽略
SELECT CONCAT_WS(",","youyuan","123");
- space(N) 返回空格组成的字符串
SELECT SPACE(4)
- length(str) ;返回值为字符串str的字节长度
SELECT LENGTH("qwer")
- reverse(str)返回字符串的顺序相反
SELECT REVERSE("1234")
- instr(str,x,y,instr) 将字符串str从x位置开始,y个字符串的子串替换为指定的字符串。
SELECT INSTR("youyuan",1,2"ou")
- locate(Substr,str,[pos]) 返回substr在字符串str第一个出现的位置,如果没有则返回0
SELECT LOCATE("u","youyuan")
- lower(str) /upper(str) 将字符串转换小写或大写
SELECT UPPER("qwe");
SELECT LOWER("JJOI");
- left(str,x)和right(str,x) 分别返回字符串最左边的x个字符和最右边的x个字符
SELECT LEFT("qwer",1)
SELECT RIGHT("youyuan",2)
- find_in_set(str,strList) 查询str在strList的位置,strList要以英文逗号分隔
SELECT FIND_IN_SET('u','y2,o,u,y,a,n')
- LTRIM(str)和RTRIM(str) 去掉字符串当中最左侧和最右侧的空格
- TRIM(str) 去掉字符串左右的空格
SELECT TRIM(" s wsdfdff ")
-
REPEAT(str,x) 返回str重复x次的结果
-
REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a。
SELECT REPLACE("youyuan","you",'111')
-
SUBSTRING(str,x,y) 返回字符串str中第x位置起y个字符长度的字符。
-
coalesce(value,…) 返回值为列表当中的第一个非NULL值,如果值全为NULL则返回NULL
SELECT COALESCE(NULL,1,2,3)
5.2、记录函数
- row_count() 返回上一句sql 增删改查的记录。
SELECT ROW_COUNT()
如果上一条SQL是插入语句,则返回插入数据的个数。
如果上一条SQL是查询语句(SELECT/SHOW等),则返回-1
如果上一条SQL是删除表(DROP语句),则返回0
如果上一条SQL是建表语句,且创建的表是空表,则返回0;
- found_rows() 获取上一句SELECT语句(SHOW语句)查询结果集的记录数
1、
SELECT * FROM employee
SELECT FOUND_ROWS()
2、
SELECT SQL_CALC_FOUND_ROWS * FROM employee
SELECT FOUND_ROWS()
根据查询结果,2的查询速度比更快。
注意
- 这两个SQL一定要放在一个SESSION中,不然结果数据是错误的
- 如果你要使用两句SQL组合就不能用可视化软件Navicat、SQLyon之类的执行这两句SQL。
- last_insert_id()返回最后一条插入语句的id
select last_insert_id()
5.3、数值函数
- least(value1,value2,…)返回值为值最小的参数
SELECT LEAST(12,1,-12)
- greatest(value1,value2,…) 返回值最大的参数
- round(x,[D]) 返回参数X最近的整数
- ABS(x) 取x的绝对值
- MOD(X,Y) 返回X/Y的摸
5.4、日期和时间函数
- to_days(data)给定一个日期date,返回天数(从年份0开始的天数,就是从0年开始 到1997年10月1号之间的天数
SELECT TO_DAYS('1997-10-01')
- datediff(expr1,expr2) ; 返回expr1时间和expr2时间之间的间隔天数
SELECT DATEDIFF(CURDATE(),'2020-3-12')
- last_day(date) 返回该月最后一天对应的值。若参数date无效,则返回NULL
SELECT LAST_DAY(CURDATE())
- date_sub(date,INTERVAL expr1 type) 从日期里面减去指定的时间间隔。date参数是日期表达式,expr1参数是减去的天数
SELECT DATE_SUB(CURDATE(),INTERVAL 2 DAY)
-
date_add(date,INTERVAL expr1 type) 加几天
-
format(X,D)对字段的显示进行格式化
SELECT FORMAT(123455.243012,2)
- date_format(date,format)用于以不同的格式显示日期数据。format参数是输出的日期时间格式。data参数是日期类型
SELECT DATE_FORMAT(CURDATE(),'%m-%d-%y') #精确到天
SELECT DATE_FORMAT(CURDATE(),'%b-%d-%y%h:%i%p') #查看今天,精确到分和时段
- timestampdiff (interval,datetime_expr1,datetime_expr2)返回表达式datetime_expr1日期时间datetime_expr2日期时间之间的整数差
SELECT TIMESTAMPDIFF(DAY,'2020-7-20',NOW())
-
CURDATE() 返回当前日期,只包含年月日
-
unix_timestamp(date) 将时间转化为时间戳
SELECT UNIX_TIMESTAMP(CURDATE())
5.5、流程函数
- IF(value,t,f) 如果value是真,返回t,否则返回f
SELECT IF(1>2,0,1)
- IFNULL(value1,value2) 如果value1不为空,返回value1否者返回value2
SELECT IFNULL(1,2)
6、事务
每条sql语句都是一个事务,事务只对DML语句有效,对于DQL无效。
6.1、事务的ACID(考点)
- 原子性(Atomicity):原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚。
- 一致性(Consistency):一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。
- 隔离性(Isolation):隔离性是当多个用户并发访问数据库时, 不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
- 持久性(Durability):持久性是指一个事务一旦被提交了,就不能再回滚了,已经把数据保存到数据库当中了。
斯蒂芬电
6.2、事务的并发问题(考点)
- 脏读
- 不可重复读
- 重复度
- 幻读
事务隔离级别 | 解释 | 幻读 | 脏读 | 不可重复读 |
---|---|---|---|---|
读未提交(Read uncommitted) | 就是一个事务可以读取另一个未提交事务的数据 | 是 | 是 | 是 |
读已提交(Read committed) | 一个事务要等另一个事务提交后才能读取数据 | 否 | 是 | 是 |
可重复读(Repeatable read) | 就是在开始读取数据(事务开启)时,不再允许修改操作 | 否 | 否 | 是 |
串行化(Serializable) | 在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读 | 否 | 否 | 否 |
7、存储过程与自定义函数
7.1、存储过程的编写
7.1.1、变量的声明与赋值
declare i int default 0;
set a = a + 1;
7.1.2、流程控制
declare a int default 0
if a > 1 then
set a = a + 1;
end if
7.1.3、循环控制
declare i int default 0;
declare sum int default 0;
while i < 100 do
set sum = sum +i;
set i = i + 1;
end while
7.1.4、实例
求n个数之和
BEGIN
DECLARE sum int DEFAULT 0;
DECLARE i int default 0;
while i<=n DO
set sum=sum+i;
set i=i+1;
end while;
SELECT sum;
END
调用存储过程
运行结果:
代码调用:
CALL addsum(4,@sum1)
SELECT @sum1
MySQL默认以";“为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER //”声明当前段分隔符,让编译器把两个”//"之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。
7.2存储过程与函数的区别
本质上没区别。函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return,是通过out参数返回多个值。 函数是可以嵌入在sql中使用的,可以在select中调用,而存储过程不行。函数限制比较多,如不能用临时表,只能用表变量等,而存储过程的限制相对就比较少。
- 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。
- 当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以从自己的存储过程内引用其它存储过程,这可以简化一系列复杂语句.
- 存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。
- 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
- 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值,可以向程序返回错误原因。但函数只能返回一个特定类型的值或者表对象。
- 存储过程中的CRUD的操作会影响数据库状态,但函数却不能。
- 函数只能是in类型,存储过程可以使用In\out\inout类型。