MySQL基础语法笔记
介绍
由于之前都没有接触过数据库的基础知识,特别花了一些时间通过观看视频的方式来进行数据库的入门。曾经也看过一些书籍来尝试入门,但是书上的内容对于入门来说过于繁琐。因此选择了bilibili上的黑马程序员数据库篇作为学习途径,视频说的真的非常的好,老师的语言非常的简洁,而且逻辑很清晰。以下是基于MySQL的SQL语言基础内容。
一、MySQL安装及基础模型
1.打开、连接
- 打开:net start mysql80 关闭 net stop mysql80
- 从MySQL的Command line打开
- 从Cmd输入如下指令
mysql -u root -p
2.数据模型
-
关系型数据库
建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
-
数据模型
客户端->DBMS----SQL语句---->数据库->二维表
二、SQL
1.SQL通用语法
(1)可以以单行或多行书写,以分号结尾
(2)SQL语句可以使用空格/缩进来增强语句的可读性
(3)MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
(4)注释:
单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
多行注释: /* 注释内容 */
2.SQL语句的分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库、表、字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
3.DDL语句
DLL-数据库操作
# 查询
# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
# 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE[IF EXISTS] 数据库名;
# 使用
USE 数据库名;
DDL-表操作-查询
# DDL-表操作-查询
# 查询当前数据库的所有表
SHOW TABLES;
# 查询表结构
DESC 表名;
# 查询指定表的建表语句
SHOW CREATE TABLE 表名;
DDL-表操作-创建
# DDL-表操作-创建
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
...
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
DDL-表操作-数据类型
(1)数值类型
类型 | 大小 | 描述 |
---|---|---|
TINYINT | 1 byte | 小整数值 |
SMALLINT | 2 bytes | 大整数值 |
MEDIUMINT | 3 bytes | 大整数值 |
INT或INTEGER | 4 bytes | 大整数值 |
BIGINT | 8 bytes | 极大整数值 |
FLOAT | 4 bytes | 单精度浮点数值 |
DOUBLE | 8 bytes | 双精度浮点数值 |
DECIMAL | 小数值(精确定点数) |
(2)字符类型
类型 | 大小 | 特点 | 描述 |
---|---|---|---|
CHAR | 0~255 bytes | 性能高 | 定长字符串 |
VARCHAR | 0~65535 bytes | 长度根据数据算,性能较差 | 变长字符串 |
TINYBLOB | 0~255 bytes | 不超过255个字符的二进制数据 | |
TINYTEXT | 0~255 bytes | 短文本字符串 | |
BLOB | 0~65535 bytes | 二进制长文本数据 | |
TEXT | 0~65535 bytes | 长文本数据 | |
MEDIUMBLOB | 0~16777215 bytes | 二进制形式的中等长度文本数据 | |
MEDIUMTEXT | 0~16777215 bytes | 中等长度文本数据 | |
LONGBOLB | 0~4294967295 bytes | 二进制形式的极大文本数据 | |
LONGTEXT | 0~4294967295 bytes | 极大文本数据 |
(3)日期类型
类型 | 大小 | 格式 | 描述 |
---|---|---|---|
DATE | 3 | YYYY-MM-DD | 日期值 |
TIME | 3 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | YYYY | 年份值 |
DATETIME | 8 | YYYY-MM-DD HH:MM:SS | 混合日期和时间 |
TIMESTAMP | 4 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
DDL-表操作-修改
# DDL-表操作-修改
# 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT] [约束];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
DDL-表操作-删除
# DDL-表操作-删除u
# 删除表
DROP TABLE[IF EXISTS] 表名;
# 删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
4.MySQL图形化界面
- Sqlyog
- Navicat
- DataGrip
5.DML语句
添加数据(INSERT)
修改数据(UPDATE)
删除数据(DELETE)
DML-添加数据
# DML-添加数据
# 1.给指定字段添加数据
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...);
# 2.给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2, ...);
# 3.批量添加数据
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...),(值1, 值2, ...),...;
INSERT INTO 表名 VALUES(值1, 值2, ...),(值1, 值2, ...),...;
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应包含在引号中
- 插入的数据大小,应该在字段的规定范围内
DML-修改数据
# DML-修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,...[WHERE 条件];
DML-删除数据
# DML-删除数据
DROP FROM 表名 [WHERE 条件];
6.DQL语句
DQL基本语法
SELECT
字段列表(显示的字段)
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表(ASK默认升序 DESC)
LIMIT
分页参数 (开始索引, 每一页显示多少个)
DQL语句执行顺序:
FROM 表格
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
SELECT 字段列表
ORDER BY 排序字段列表
7.DCL语句
用来管理数据库 用户、控制数据的访问权限
DCL-管理用户
# DCL管理用户
# 1.查询用户
USE mysql;
SELECT * FROM user;
# 2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_passward BY '新密码';
# 4.删除用户
DROP USER '用户名'@'主机名';
DCL-权限控制
# DCL-权限控制
# 1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 2.授予权限
CREATE 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
三、函数
函数:是指一段可以直接被另一端程序调用的程序或代码
1.字符串函数
函数 | 功能 |
---|---|
CONTACT(s1, s2, …, sn) | 字符串拼接 |
LOWER(str) | 转换为小写 |
UPPER(str) | 转换为大写 |
LPAD(str, n, pad) | 左填充字符pad |
RPAD(str, n, pad) | 右填充字符pad |
TRIM(str) | 去除字符串两端的空格 |
SUBSTRING(str, start, len) | 从str的start获取一段长度为len的子字符串 |
2.数值函数
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x, y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x, y) | 求参数x的四舍五入的值,保留y位小数 |
3.日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR() | 获取指定date的年份 |
MONTH() | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date, INTERVAL exper type) | 返回一个日期/时间加上一个时间间隔expr后的时间值 |
DATEDIFF(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
4.流程函数
函数 | 功能 |
---|---|
IF(value, t, f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回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默认值 |
四、约束
概念:约束是作用域表中字段上的规则,用于限制存储在表中的数据
目的:保证数据库中的数据的正确、有效性和完整性
分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 逐渐是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEAFAULT |
检查约束 | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
外键约束
概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
# 外键约束
# 添加外键
CREATE TABLE 表名(
字段名 数据类型
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
# 删除外键
ALTER TABLE 表格 DROP FOREIGN KEY 外键名称;
删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应的记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应的记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应的记录时,首先检查该记录是否有对应的外键,如果有,则也删除/更新外键在字表中的记录。 |
SET NULL | 当在父表中删除对应的记录时,首先检查该记录是否有对应的外键,如果有则设置字表中该外键值为null(这就要求该外键允许取null) |
SET DEFAULT | 父表有变更时,字表将外键设置成一个默认的值(Innodb不支持) |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
五、多表查询
1.多表关系
- 一对多
实现:在多的一方建立外键,指向一的一方的主键。
- 多对多
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两房主键
- 一对一
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
2.多表查询概述
笛卡尔积:两个有关联关系的表的查询会存在 n * m个,需要添加where条件过滤多余的项。
3.内连接
概念:查询A表和B表之间的交集数据。
# 内连接查询语法:
# 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
# 显示内连接
SELECT 字段列表 表1 [INNER] JOIN 表2 ON 连接条件...;
4.外连接
概念:左外:查询左表的所有。右外:查询右表的所有
# 外连接查询语法:
# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
5.自连接
应用:当表格的某一列与另一列存在对应关系时,要查询其绑定的对应关系表可以使用自连接查询
(可理解为将一张表分为两张表来建立查询关系)
# 自连接查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
注:必须给表起别名
# 联合查询-union, union all
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
6.子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
按子查询结果可分类为:
- 标量子查询 :| = | < | > | >= | <=
- 列子查询:IN | NOT IN | ANY | SOME | ALL
- 行子查询:= | < | > | IN | NOT IN
- 表子查询:IN
按子查询位置分类:
- WHERE之后
- FROM之后
- SELECT之后
7.多表查询案例
略
六、事务
1.事务简介
事务:是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求。即这些操作要么同时成功,要么同时失败。
2.事务操作
# 修改事务为手动提交
SELECT @@autocommit;
SET @@autocommit = 0;
# 开启事务
START TRANSACTION 或 BEGIN;
# 提交事务
commit;
# 回滚事务
rollback;
3.事务四大特性
- 原子性(A):不可分割的最小操作单元
- 一致性©:事务完成时,必须使所有数据都保持一致的状态
- 隔离性(I):数据库系统提供的隔离机制,保证事务再不受外部并发操作影响的独立环境下运行
- 持久性(D):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
4.并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行的数据已经存在,好像出现了”幻影“ |
5.事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | ✔ | ✔ | ✔ |
Read committed | x | ✔ | ✔ |
Repeatable Read | x | x | ✔ |
Serializable | x | x | x |
# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
# 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};