目录
(一)数据模型
1.关系型数据库(RDBMS)
概念:多张相互连接的二维表组成的数据库
特点:便于维护、使用方便
注释:
单行注释:--注释内容或#注释内容
多行注释:/* 注释内容 */
(二)SQL分类
DDL:数据定义语言,用来定义数据库对象(数据库、表、字段)
DML:数据操作语言,用来对数据库表中的数据进行增删改查
DQL:数据查询语言,用来查询数据库中表的记录
DCL:数据控制语言,用来创建数据库用户、控制数据库访问权限
一、DDL数据库操作
查询:
查询所有数据库: SHOW DATABASES;
查询当前数据库: SELECT DATABASE();
查询表结构: DESC 表名;
查询指定表的建表语句:SHOW CREATE TABLE 表名;
创建:
数据库创建:CREATE DATABASE [IF NOT EXISTS] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则];
表创建: CREATE TABLE 表名(字段1 字段1类型[COMMENT 字段1注释]....字段n 字段n类型[COMMENT 字段n注释])[COMMENT 表注释];
删除:
DROP DATABASE [IF EXISTS] 数据库名;
使用:
USE 数据库名;
1.DDL-表操作-数据类型
MySQL中数据类型:数值类型、字符串类型、日期时间类型
数值类型:
有符号:SIGNED 无符号:UNSIGNED (有无负号)
TINYINT: 小整数值 (-128,127)
SMALLINT:大整数值 (-32768,32768)
MEDIUMINT:大整数值(-8388608,8388607)
INT或INTEGER:大整数值 (-2147483648,2147483647)
BIGINT:极大整数值 (-2^63,-2^63-1)
FLOAT:单精度浮点数值 (-3.402823466 E+38.402823466351 E+38)
DOUBLT:双精度浮点数值 (-1.7976931348623157 E+308,1.7976931348623157 E+308)
DECIMAL:小数值 指定精度和标度(精度是小数点前的位数 标度是小数点后的位数)
字符串类型:
CHAR: 定长字符串 (0-255)
VARCHAR:变长字符串 (0-65535)
TINYBLOB:二进制数据 (0-255)
TINYTEXT:短文本字符串 (0-255)
BLOB:二进制形式的长文本数据 (0-65535)
TEXT:长文本数据 (0-65535)
MEDIUMBLOB:二进制形式中的中等长度文本数据 (0-16777)
MBDIUMTEXT:中等长度文本数据 (0-16777)
LOWNGBLOB:二进制形式中的极大文本数据 (0-4294967295)
LONGTEXT:极大文本数据 (0-4294967295)
日期时间类型:
DATE: 日期值 格式:YYYY-MM-DD
TIME:时间值或持续时间值 格式:HH:MM:ss
YEAR:年份值 格式:YYYY
DATETIME:混合日期和时间 格式:YYYY-MM-DD HH:MM:ss
TIMESTAMP:混合日期和时间值、时间擢 格式:YYYY-MM-DD HH:MM:ss
2.DDL-表操作-修改
添加字段:ALTER TABLE 表名 ADD字段名 类型(长度) [COMMENT 注释] [约束];
修改字段:ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释] [约束];
修改表名:ALTER TABLE 表名 RENAME TO 新表名;
3.DDL-表操作-删除
删除表:DROP TABLE [IF EXISTS] 表名;
删除指定表,并重新创建该表:TRUNCATE TABLE 表名;
二、DML数据库操作
添加数据(INSERT) 字符串和日期数据要放在引号中
给指定字段添加数据:INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...);
给全部字段添加数据:INSERT INTO 表名(值1,值2,...);
批量添加数据:INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2...),(值1,值2,...);
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...);
修改数据(UPDATE):
UPDATE 表名 SET 字段1=值1,字段2=值2 ... [WHERE 条件];
删除数据(DELETE) 如果没有条件,则会删除整张表:
DELETE FROM 表名 [WHERE 条件];
三、DQL数据库操作
关键字:SELECT
SELECT +字段列表
FROM +表名列表
WHERE + 条件列表
GROUP BY +分组字段列表
HAVING +分组后条件列表
ORDER BY +排序字段列表
LIMT +分页参数
基本查询 :
查询多个字段:
SELECT 字段1,字段2,... FROM 表名
SELECT * FROM 表名;
设置别名:
SELECT 学段1[AS 别名1],字段2[AS 别名2]...FROM 表名;
去除重复记录:
SELECT DISTINCT 字段列表 表名;
条件查询(WHERE)
语法:SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:>、>=、<、<=、=、!=、
BETWEEN...AND... (在某个范围内最大或最小)、
IN(...): 在in之后的列表中的值,多选一、
IS NULL : NULL、
LIKE 占位符: 模糊查询(_匹配单个字符、%匹配任意字符)
AND或者&& : 并且
OR或||: 或者
NOT或!: 不是
聚合函数:(COUNT、MAX、MIN、AVG、SUM)
count 统计数量、max 最大值、min最小值、avg平均值、sum求和
语法:SELECT 聚合函数(字段列表) FROM 表名;
分组查询:(GROUP BY)
语法:SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];
where和having的区别:
执行时机不同:where是在分组之前进行过滤,having是在分组之后进行过滤;
判断条件不同:where不能对聚合函数进行过滤,having可以过滤;
排序查询:(ORDER BY)
语法:SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
ASC: 升序(默认升序)
DESC:降序
分页查询:(LIMIT)
语法:SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:起始索引从0开始、如果查询的是第一页,起始索引可以省略。
四、DCL数据库操作
登录用户:mysql -u 用户名 -p
管理数据库、控制数据库的访问权限
1.查询用户:
USE MySQL;
SELECT*FROM user;
2.创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.修改用户密码:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码';
4.删除用户
DROP USER '用户名'@'主机名' ;
5. 权限控制:
ALL、ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库、表、视图
CREATE 创建数据库、表
查询权限:SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限:GRANTS 权限列表 ON数据库.表名 TO '用户名'@'主机名' ;
撤销权限:REVOKE 权限列表 ON数据库.表名 FROM '用户名'@'主机名' ;
(三)函数
1.字符串函数
格式:SELECT 函数参数
CONCAT(S1,S2,...Sn) 拼接成字符串,将s1,s2...sn拼接成一个字符串
LOWR(str) 把字符串str全部转为小写
UPPER(str) 把字符串str全部转为大写
LPAD(str,n,pad) 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
RPAD(str,n,pad) 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
2.数值函数
CELL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数X的四舍五入的值,保留y位小数
3.日前函数
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数
4.流程函数
IF(value ,t,f) 如果value为true,则返回t,否则返回f
IFNULL(value1,value2) 如果value不为空,返回value1,否则返回value2
CASE WHEN [val1] THEN [res1] ...ELSE[default] END 如果val1为true,返回res1,...否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] ...ELSE[default] END 如果expr的值等于val1,返回res1,...否则返回default默认值
(四)约束
约束:约束是作用于表中字段上的规则,用于限制存储在表中的数据
目的:保证数据库中数据的正确、有效性和完整性
分类:
AUTO_INCREMENT : 自动增长
非空约束 (NOT NULL) : 限制该字段不能为空
唯一约束 (UNIQUE) :保证该字段所有数据都是唯一不重复的
主键约束 (PRIMARY KEY ): 主键是一行数据唯一的标识,要求非空且唯一
默认约束 (DEFAULT) :保存数据时,如果未指定该字段的值,则采用默认值
检查约束 (CHECK) :保证字段值满足某一个条件
外键约束 (FOREIGN KEY): 让两张表的数据之间建立连接,保证数据的一致性和完整性
添加外键: CREATE TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
删除外键:ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为:
NO ACTION: 当父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新。
RESTRICT: 当父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。
CASCADE :当父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键子表中的记录。
SET NULL :当父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null;
SET DEFAULT :父表有变更时,子表将外键列设置成一个默认的值
(五)多表查询
多表关系:
一对多:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键
一对一:多用于单表的拆分,将一张表的基础字段放在一张表中,其他详细字段放在另一张表中,以提升操作效率。
实现:
多对多:一个课程可以选择多个课程,一个课程可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联双方主键
多表查询:从多张表中查询数据
笛卡尔积是两个集合A集合和B集合的所有组合情况。(消除掉无效的笛卡尔积)
1.连接查询
内连接:查询A、B交集部分的数据
隐式内连接:SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
显式内连接:SELECT 字段列表 FROM 表1[INNER] JOIN,表2 ON 连接条件...;
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
语法:SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
右外连接:查询右表所有数据,以及两张表交集部分数据
语法:SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
自连接:当前表与自身的连接查询,自连接必须使用表别名
语法:SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
联合查询(union、union-all):把多次查询的结果联合起来,形成一个新的查询结果集。
语法:SELECT 字段列表 FROM 表A... UNION [ALL] SELECT 字段列表 FROM 表B...;
子查询:嵌套查询,SQL中嵌套SELECT语句。
语法:SELECT *FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
1.标量子查询:子查询的结果为单个值
常用的操作符:= <> > >= < <=
2.列子查询:子查询结果为一列(多行)
常用的操作符:
IN:在指定的集合范围内;
NOT IN:不在指定的集合范围内;
ANY、SOME:子查询返回列表中,有任意一个满足即可;
ALL:子查询返回的所有制都必须满足;
3.行子查询:子查询结果为一行(多列)
常用的操作符:= <> > >= < <=
4.表子查询:子查询结果为多行多列
常用的操作符:IN
(六)事务
事务简介:
事务:是一组操作的集合,十五会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务操作:
查看/设置事务提交方式:
SELECT @@autocommit
SET @@autocommit= 0;
开启事务:START TRANSACTION 或 BEGIN
提交事务: COMMIT;
回滚事务:ROLLBACK;
事务的四大特性(ACID)
原子性:事务是不可分割的最小单元,要么全部成功,要么全部失败
一致性:事务完成时,必须使所有的数据都保持一致状态
隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
脏读:一个事务读到另一个事务还没有提交的数据
不可重复读:一个事物先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读:一个事物按照条件查询数据时,没有对应的数据行,但在插入数据时,有发现这行数据已经存在,好像出现了"幻影"
事务的隔离级别:
隔离级别 脏读 不可重复读 幻读
Read uncommitted(读未提交) √ √ √
Read committed(读已提交) × √ √
Repeatable Read(可重复读)默认 × × √
Serializable(串行化) × × ×
查看事务隔离级别:SELECT @@TRANSACTION_ISOLATION
设置事务隔离级别:SET [SESSION | GLOBAL] TRANSACTION ISOLATION [READ UNCOMMITTED|Read committed|Repeatable Read|Serializable]