写在前面
本文用于分享本人学习MySQL的笔记,禁止任何形式的商业传播。对本文内容如有疑问,请于评论区留言或私信。
本篇适合已经初步接触过MySQL学习的人群用于笔记参考。
版本:MySQL8.0
推荐使用DataGrip
参考:
目录
基础篇
你应该知道的
关于大小写
MySQL中默认的一种方式是将关键字大写,字段名和表名小写进行区分,其他方式使用方式均可。
启用MySQL服务
- 在cmd中net start/stop mysql80
- win+r输入services.msc找到mysql80,手动开关
打开MySQL
- 在开始菜单中找到mysql命令行
- 在cmd中输入以下代码(用户名默认root)
mysql -uUSER -pPASSWORD
注意
启用服务指的是在内存中运行MySQL,打开MySQL是进入DBMS
推荐使用MySQL的命令行,不要过分依赖DG,后期在linux系统中的操作都是在终端命令行下进行的。
注释
-- 单行注释
/* 多行注释 */
# MySQL支持
数据类型
常用的数据类型有数值类型,字符串类型和日期类型等。
下表为几种常见的数据类型的描述,其中字符串类型的内存大小是根据分配大小浮动变化的。其他的数据类型不属于本文撰写目的,感兴趣可以参见其它文章。
数值类型 | |
类型 | 内存大小(byte) |
TINYINT | 1 |
SMALLINT | 2 |
MEDIUMINT | 3 |
INT/INTEGER | 4 |
BIGINT | 8 |
FLOAT | 4 |
DOUBLE | 8 |
DECIMAL() | // |
字符串类型 | |
类型 | 描述 |
CHAR() | 定长字符串 |
VARCHAR() | 变长字符串 |
TINYBLOB | 短二进制数据 |
TINYTEXT | 短文本数据 |
BLOB | 二进制数据 |
TEXT | 文本数据 |
MEDIUMBLOB | 中等长度二进制数据 |
MEDIUMTEXT | 中等长度文本数据 |
LONGBLOB | 长二进制数据 |
LONGTEXT | 长文本数据 |
日期类型 | |
类型 | 范围 |
DATE | 1000-01-01~9999-12-31 |
TIME | -838:59:59~838:59:59 |
YEAR | 1901~2155 |
DATETIME | 1000-01-01 00:00:00~9999-12-31 23:59:59 |
TIMESTAMP | 1970-01-01 00:00:01~2038-01-19 03:14:07 |
注意
DECIMAL(m,n)指的是最多有m位数,n位小数,小数点不计入。m默认10,n默认0。
不能存入“-”,“0”开头的数字,“+”默认存入正数。
CHAR()比VARCHAR()的性能好一些,两者选择时具体问题具体分析。
条件语法
SQL语言 | 描述 |
> >= < <= | 对应C语言语法 |
= <> != !> !< | (不)等于 |
BETWEEN <min> AND <max> | 在……之间 |
IN() | 在……之中 |
LIKE | 模糊匹配 |
IS (NOT) NULL | (非)空 |
&& || !AND OR NOT | 对应C语言 |
注意
LIKE _ 匹配单个字符
LIKE % 匹配任意字符
BETWEEN AND 数据的大小顺序不能改变
AND的优先级高于OR
IN一定程度上1可以代替OR
在某些DBMS中,如果char(X)没有完全填充,多余的空间会使用空格来填充,这里查询字段中含有"Love"的字段时要使用:LIKE '%Love%'
LIKE 不会匹配NULL
LIKE [] 匹配指定位置的单个字符(MySQL Server支持,仅作了解)
查找以M或H开头的某字段:LIKE '[MH]%'
其否定:[^MH]%
SQL语言的顺序
编写顺序:select>from>where>group by>having>order by>limit
执行顺序:from>where>group by>select>order by>limit
注意
一定以及必须牢牢记住上面的两个顺序
SQL分类
DDL:数据定义语言,定义数据库对象 definition
DML:数据操作语言,对数据增删改操作 manipulation
DQL:数据查询语言,查询表的记录 query
DCL:数据控制语言,创建用户,控制权限 control
DDL--数据定义语言
-- 显示所有数据库
SHOW DATABASES;
-- 使用数据库
USE <NAME>;
-- 查询当前数据库
SELECT DATABASE();
-- 创建数据库
CREATE DATABASE <NAME>;
CREATE DATABASE IF NOT EXISTS <NAME>;
CREATE DATABASE <NAME> DEFAULT CHARSET UTF8MB4;
-- 删除数据库
DROP DATABASE <NAME>;
DROP DATABASE IF EXISTS <NAME>;
-----------------分割线-------------------
-- 显示所有表
SHOW TABLES;
-- 显示自建表
SHOW CREATE TABLE <NAME>;
-- 创建表
CREATE TABLE <NAME>(
<name1> <type> COMMENT'注释',
<name2> <type> COMMENT'注释',
<name3> <type> COMMENT'注释'
)COMMENT'注释';
-- 查询表结构
DESC <NAME>;
-- 添加字段
ALTER TABLE <NAME> ADD <name4> <type> COMMENT'注释';
-- 修改字段属性
ALTER TABLE <NAME> MODIFY <name1> <new_type>;
ALTER TABLE <NAME> CHANGE <past_name1> <new_name1> <new_type> COMMENT'注释';
-- 删除字段
ALTER TABLE <NAME> DROP <name1>;
--更改表名
ALTER TABLE <PAST_NAME> RENAME TO <NEW_NAME>;
-- 清空表
TURNCATE TABLE <NAME>;
-- 删除表
DROP TABLE <NAME>;
注意
删除数据库和表的操作是慎之又慎,删库跑路一时爽,缝纫机踩到你悲伤。
DML--数据操作语言
-- 添加一项数据
INSERT INTO <NAME>(<name1>,<name2>……) VALUES(<value1>,<value2>……);
INSERT INTO <NAME> VALUES(<value1>,<value2>……);
-- 添加多项数据
INSERT INTO <NAME>(<name1>,<name2>……) VALUES(<value1>,<value2>……),(<value1>,<value2>……),(<value1>,<value2>……);
INSERT INTO <NAME> VALUES(<value1>,<value2>……),(<value1>,<value2>……),(<value1>,<value2>……);
-- 修改数据
UPDATE <NAME> SET <name1>=value1,<name2>=value2……[WHERE condition];
-- 删除数据
DELETE FROM <NAME>[WHERE condition];
注意
不指定字段名就默认是全部
字段顺序要和对应的值一一对应
字符串和日期类型的数据要包含在引号内
插入的数据必须合法
不加WHERE条件则是对整个表进行操作
DQL--数据查询语言
-- 基本语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
-- 设置别名
SELECT <name1> AS <name11>,<name2> AS <name22> FROM <NAME>;
-- 去重操作
SELECT DISTINCT <name1> FROM <NAME>;
-- 条件查询
SELECT <name1> FROM <NAME> WHERE condition;
-- 查询所有字段
SELECT * FROM <NAME>;
-- 聚合函数查询
SELECT 聚合函数<name1> FROM <NAME>;
-- 查询数据个数
SELECT COUNT(*) FROM <NAME>;
-- 分组查询
SELCET <name3> FROM <NAME> [WHERE condition] GRUOUP BY <name2> [HAVING condition];
-- 排序查询
SELECT <name1> FROM <NAME> ORDER BY <name2> <sequence>,<name3> <sequence>;
-- 分页查询
SELECT <name1> FROM <NAME> LIMIT m,n;
注意
AS 可以省略
查询所有字段尽量不要使用 *
聚合函数不对null进行运算
常见的聚合函数:count,max,min,avg,sum
分组查询顺序:WHERE->聚合函数->HAVING
关于分组查询:where是对分组前的数据进行过滤,having是对分组后的数据进行过滤,where不能判断聚合函数,having可以
关于分组查询:asc(默认)升序,desc 降序;多字段排序,当第一个字段顺序相同时,才会按照第二个字段排序
关于分页查询:起始索引从0开始,值为(页码-1)*每页记录数,查询的是第一页数据,页码可以省略
关于分页查询:m,n指的是从(m+1)行开始查询n行数据;页码公式为:LIMIT (page-1)*n,n
LIMIT n,OFFSET (page-1)*n也可以达到上面的效果
关于排序:ORDER BY 可以根据相对位置进行排序,ORDER BY 2 指的是按照SELECT的第二个字段升序排序
ascending descending
DCL--数据控制语言
-- 查询用户
USE MYSQL;
SELECT*FROM USER;
-- 创建用户
CREATE USER 'user'@'hostname' IDENTIFIED BY 'password';
-- 修改密码
ALTER USER 'user'@'hostname' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'new_password';
-- 删除用户
DROP USER 'user'@'hostname';
注意
当主机名是%时,代表可以使用任何主机来控制数据库
使用频率较小,初学者短期可以不用考虑。
函数
在MySQL中内置的可以被SELECT调用的程序
常见函数
函数 | 描述 |
字符串函数 | |
CONCAT(a,b,…) | 连接字符串 |
LOWER(str) | 转小写 |
UPPER(str) | 转大写 |
LPAD(str,n,pad) | 左填充 |
RPAD(str,n,pad) | 右填充 |
TRIM(str) | 去掉头尾的空格 |
SUBSTRING(str,start,len) | 字符串切片 |
数值函数 | |
CEIL() | 向上取整 |
FLOOR() | 向下取整 |
ROUND(x,y) | 对x四舍五入,保留y位小数 |
MOD(x,y) | 返回x%y |
RAND() | 返回0~1之间的随机值 |
日期函数 | |
CURDATE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
NOW() | 获取当前日期和时间 |
YEAR(date) | 获取date年份 |
MONTH(date) | 获取date月份 |
DAY(date) | 获取date日期 |
DATE_ADD(date,interval,expr type) | 返回推迟时间 |
DATEDIFF(date1,date2) | 返回date1-date2的天数 |
流程控制函数 | |
IF(VAL,T,F) | VAL真,返回T,反之F |
IFNULL(VAL1,VAL2) | VAL1非空,返回VAL1,反之VAL2 |
CASE WHEN <VAL1> THEN <RES1> ELSE <DEFAULT> END | VAL1非空,返回RES1,反之DEFAULT |
CASE <EXPR> WHEN <VAL1> THEN <RES1> ELSE <DEFAULT> END | EXPR=VAL1,返回RES1,反之DEFAULT |
注意
SUBSTRING()的索引是从1开始的
DATEDIFF()的返回值可以是负数
END 必须加上
约束
分类
分类 | 描述 | 语法 |
非空约束 | 字段不能为Null | NOT NULL |
唯一约束 | 数据唯一不重复 | UNIQUE |
主键约束 | 一行数据的唯一标识,非空且唯一 | PRIMARY KEY |
默认约束 | 未指定值时,采用默认值 | DEFAULT |
检查约束 | 证字段在取值范围内 | CHECK |
外键约束 | 将两个表之间的数据建立连接 | FOREING KEY |
注意
具有外键的表叫子表,属性做主键的表叫父表。
一个字段可以有多个约束
自动增长:AUTO_INCREMENT ,只能搭配主键使用,但主键不一定需要该约束
主键特点:非空,唯一,不可更新,不能复用
主键是可以由多个字段共同构成的
提示:在DataGrip中使用 ctrl+shift+alt+u 有惊喜
外键约束
-- 添加外键
CREATE TABLE <NAME> (
[CONSTRAINT] [FOREING_KEY NAME] FOREING KEY(name1) REFERRENCES NAME(name2)
);
ALTER TABLE <NAME> ADD CONSTRAINT <FOREING_KEY NAME> FOREING KEY(name1) REFERRENCES NAME(name2) ON UPDATE A ON DELETE B;
-- 删除外键
ALTER TABLE <NAME> DROP FOREING KEY <FOREING_KEY NAME>;
更新外键行为
语法 | 描述 |
NO ACTION | 父表更新记录时,首先检查是否有外键,有则不允许更新 |
RESTRICT | 父表更新记录时,首先检查是否有外键,有则不允许更新 |
CASCADE | 父表更新记录时,首先检查是否有外键,有也允许更新 |
SET NULL | 父表更新记录时,首先检查是否有外键,有则设置子表外键值为null |
SET DEFAULT | 父表更新时,子表将外键列设置为一个默认的值 |
多表查询
同时对多张表的数据进行处理,分为一对多,多对多和一对一的方式。
一对多:在多的一方建立外键,指向少的一方
多对多:建立中间表,中间表至少建立两个外键,关联两方主键
一对一:在任意一方加入外键,关联另一方的逐渐,并设立外键为unique,区别一对多
常见的多表查询连接方式
-- 笛卡尔积(错误代码)
SELECT * FROM <NAME1>,<NAME2>;
-- 隐式内连接
SELECT <name1> FROM <NAME1>,<NAME2> WHERE [condition];
SELECT <name1> FROM <NAME1> a, <NAME2> b WHERE [condition];
-- 显式内连接
SELECT <name1> FROM <NAME1> [AS] [INNER] JOIN <NAME2> ON [condition];
-- 左外连接
SELECT <name1> FROM <NAME1> LEFT [OUTER] JOIN <NAME2> ON [condition];
-- 右外连接
SELECT <name1> FROM <NAME1> RIGHT [OUTER] JOIN <NAME2> ON [condition];
-- 自连接
SELECT <name1> FROM <NAME1> A JOIN <NAME1> B ON [condition];
-- 联合查询
SELECT <name1> FROM <NAME1>,<NAME2>…… UNION [ALL];
-- 子查询
SELECT * FROM <NAME1> WHERE <name1> = (SELECT <name1> FROM <NAME2> WHERE [condition]);
注意
起了别名之后就不能再用原来的名字
关于自连接:自连接必须要起别名,自连接可以是自连接或者外连接
关于联合查询:UNION ALL不去重 UNION去重
关于子查询:分为标量子查询(返回一个值),行子查询,列子查询,表子查询;外部语句可以是INSERT,UPDATE,DELETE,SELECT任何一个
子查询方式 | 支持的操作符 |
标量子查询 | = > >= < <= |
行子查询 | = <> [NOT] IN |
列子查询 | [NOT] IN ANY SOME ALL |
事务
将若干个操作定义为一组操作,同时成功或失败
事务操作
-- 查看/设置事务提交方式
SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 0;
-- 开启事务
START TRANSACTON/BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
注意
默认情况下,事务提交方式是自动提交(AUTOCOMMIT=1)
四大特性
原子性:全部成功或全部失败
一致性:事务完成后,所有数据保持一致状况
隔离性:独立环境进行
持久性:回滚或者提交后,数据是永久的
并发事务问题
脏读:一个事务读到另一个事务未提交的事务
不可重复读:先后读取同一条记录,但两次读取的数据不同
幻读:按照条件查询数据时,没有对应的数据行,但在插入数据时,发现数据已经存在
注意
先记住有这个概念,错过几次就明白原理了
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
READ UNCOMMITTED | T | T | T |
READ COMMITTED | F | T | T |
REPEATABLE READ | F | F | T |
SERIALIZABLE | F | F | F |
-- 查看事务隔离级别
SELECT @@ TRANSACTION ISOLATION;
-- 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMIMITTED | REPEATABLE READ | SERIALIZABLE}
注意
并不是事务隔离级别越高越好
SESSION 是在当前窗口生效,是一个临时设置
GLOBLE 是永久设置
MySQL默认是 REPEATABLE READ
Oracle默认是 READ COMMITTED
写在后面
本文完笔于23-09-29中秋节,祝愿大家身体健康。
若有谬误,望君海涵。
《MySQL数据库笔记(进阶篇)》正在路上。