MySql学习笔记

Mysql

Mysql语句

库

库管理：创建库

方式一：创建数据库，使用默认的字符集和排序方法
CREAT DATABASE 数据库名；

方式二：判断并创建默认字符集库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名；

方式三：创建指定字符集或者排序方式
CREATE DATABASE 数据库名 CHARACTER SET 字符集；
CREATE DATABASE 数据库名 COLLATE 排序规则；

方式四：创建指定字符集和排序规则库
CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则

库管理：查看和使用库

方式一：展示当前所有库
SHOW DATABASES;

方式二：查看当前使用库
SELECT DATABASE();

方式三：展示指定库下所有表
SHOW TABLES FROM 数据库名；

方式四：展示创建库的信息
SHOW CREATE DATABASE 数据库名；

方式五：切换库/选中库
USE 数据库名；

方式六：查看当前库的字符集和排序规则
show variables like 'character_set_database';查看字符集
show variables like 'collation_database';查看排序规则

库管理：修改库

修改库编码字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集；//修改字符集

ALTER DATABASE 数据库名 COLLATE 排序方式；//修改排序方式

ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序方式；//修改字符集和排序方式

库管理：删除库（提桶跑路）

方式一：直接删除库
DROP DATABASE 数据库名；

方式二：判断并删除库（推荐）
DROP DATABASE IF EXISTS 数据库名；

表

表的创建

create table [if not exists] 表名(
列名 类型 [列可选约束] ，
列名 类型 [列可选约束] [comment'列可选注释']
[列可选约束]

)[表可选约束][comment'表可选注释']；

1. 必写项：表名、列名、列类型,其他选写
2. 推荐使用if not exists 判断创建是否存在
3. 注释不是必须但是是很有必要的
4. 如果写了多个列，列之间用“,”分割

表的数据类型

整数类型

类型	存储(字节)	最小值（有符号）	最小值（无符号）	最大值（有符号）	最大值（无符号）
tinyint	1	-128	0	127	255
smallint	2	-3768	0	32767	65535
mediumint	3	-8388608	0	838867	16777215
int	4	-2147483648	0	2147483647	4294967295
bigint	8	-2^63	0	2^63-1	2^64-1

浮点数类型

类型	字节	M(小数+整数位数)	D(小数位数)
float	4	M最大为24	D最大为8
double	8	M最大为53	D最大为30

定点数

类型	字节	M(小数+整数位数)	D(小数位数)
decimal	动态计算	M最大为65	D最大为30

字符串

字符串	特点	长度	长度范围	存储空间
char（M）	固定长度	M	0<=M<=255	M*4个字节（utf8mb4）
varchar(M)	可变长度	M	Mysql一行最多65535字节	M*4+1个字节（utf8mb4）

时间

类型	名称	字节	日期格式	最小值	最大值
year	年	1	YYYY/YY	1901	2155
time	时间	3	HH:MM:SS	-838:59:59	838:59:59
date	日期	3	YYYY-MM-DD	1000:01:01	9999:12:03
datetime	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00	2038-01-19 03:14:07

插入默认添加时间 datetime | timestamp default current_timestamp
修改默认修改时间（插入的默认时间） datetime | timestamp default current_timestamp on undate current_timestamp

有符号即有负号，默认为有负号，无符号时需要加unsigned

修改表

修改表中列（字段）

1. 添加一列，可指定x字段前或后
   alter table 表名 add 字段名 字段类型 [first|after 字段名];
2. 修改列名
   alter table 表名 change 原字段名 字段名 新字段类型 [first|after 字段名]；
3. 修改列类型
   alter table 表名 modify 字段名 新字段类型 [first|after 字段名];
4. 删除一列
   alter table 表名 drop 字段名;

修改表名
alter table 表名 rename [to] 新表名；

删除表

1. 删除数据表
   drop table if exists 数据表1[数据表2、数据表3。。。。]；
2. 清空表中数据
   truncate table 表名;

数据操作DML

插入表中数据

为表中所有字段（列）插入数据：insert into 表名 values(value1,value2,.....)

为表中每个字段插入数据，值的顺序和和数据库中定义的顺序需一一对应

为表中特定一行字段插入数据:insert into 表名（列名1,列名2.....）values(value1,value2.....)

同时插入多行数据
insert into 表名 values(value1,value2,....),(value1,value2,value3....)
insert into 表名（列名1,列名2.....）values(value1,value2.....),(value1,value2.....)
values也可以写成value,但是values更规范
字符和日期型数据应写在单引号内

修改表中数据

1. 修改表中所有数据（全表修改）
   update 表名
   set column1=value1,column2=value2....
   更新表中所有行的指定列数据

2. 修改表中符合条件行的数据(条件修改)
   update 表名
   set column1=value1,column2=value2....
   [where condition]

删除表中所有数据

1. 删除表中所有数据（全表删除）
   delete from 表名

2. 删除表中符合条件的数据（条件删除）
   delete from 表名[where condition]

数据查询DQL

基础select语法

1. 非表查询
   select n;
   输出n；n可以是数、计算式、函数；

2. 指定表
   select 列名1,列名2,列名3 from 表名;
   获取到表中的某些列
   select 表名.列名（多表查询时使用，与上述作用相同） 表名.*（该表中的所有列）from 表名；

3. 查询列，起别名
   select 列名 as 别名,列名，列名 as 别名 from 表名；
   其中as可以省略

4. 对查询结果去重
   select distinct 列名 [列名，列名]from 表名；

5. 给查询结果加一个常数
   select 列名 '常数' 列名（三者位置不固定） from 表名；

如果有使用*等通配符需要放在第一位
ifnull(列名，若列为空给予的值)

显示表结构

describe 表名；或 desc 表名；

过滤数据

select 字段一、字段2 from 表 where 后接过滤条件

运算符号

逻辑运算符

逻辑且 :&& AND
否定：! NO
逻辑或：OR ||
逻辑异或：XOR
Mysql将所有非零非NULL的值计算为TRUE

查找函数
find_in_set(a,b);查找b中的内容，若包含a则返回找到的第一个的下角标，反之返回0；

单行和多行函数

---

单行函数：对一行中某一列操作函数，其返回值是单一值
单行函数包括：数字、日期、字符、流程、信息、时间函数
多行函数：对多行中某列操作函数，其返回值是单一值
多行函数包括：聚合函数

---

数字

时间

流程控制函数
case
格式一

点击查看代码

case
	when condition1 then result1
	when condition2 then result2
	.
	.
	.
	else default result
end [as 别名] //将结果单拿出来作为一列

格式二

点击查看代码

case expr(列名或表达式）
	when value1 then result1
	when value2 then result2
	.
	.
	.
	else default result
end [as 别名] //

多行函数/聚合函数

函数	用法
AVG(列名)	计算某一列的平均值（数值型）
SUM(列名)	计算某一列的和（数值型）
MIN	计算某一列的最小值（任意类型）
MAX	计算某一列的最大值（任意类型）
COUNT(列名/*/1)	计算某一列或行的记录数（任意类型）

分组查询

排序查询

数据切割（分页查询）

---

---

约束

概念：表级别的规定,数据的限制语法
作用：确保数据的准确性、可靠性、正确性
添加时机：1.创建表时直接添加 2.创建表之后通过alter table 语句添加

约束分类

三个重点：作用、添加、删除

域（列）级约束(此类约束只对当前列值有效果)

• 非空约束：NOT NULL ，列非空约束
• 默认值约束：default ，某列默认值
• 检查约束：check ，检查约束

实体（行）级约束（此类约束需要对比同一表中其他行数据才有效果）

• 主键约束：primary key ，主键唯一且不为空约束
• 唯一约束：unique，限制某一列值表中唯一
• 自增长约束：auto_increment，数字类型字段插入数据自增长约束

引用（多表）级约束（此类约束，需要对比其他表的列才有效果）

• 参照（外键约束）：foreign key，限定表中某一列，正确引用其他表的数据值

域级约束

非空约束

作用：限定某个字段/列的值不允许为空
关键字：NOT NULL
特点

默认：所有类型列默认都可以为NULL，包括数字类型
列上添加：非空约束只能添加到列上！
多次使用：一个表中可以有很多列进行非空限定！
空值判定：空字符不是null，0也不是null！

添加：
建表时添加

	create table 表名称(
	字段名 数据类型,
	字段名 数据类型 NOT NULL,
	字段名 数据类型 NOT NULL,
)

建表后修改

alter table 表名称 modify 字段名 数据类型 not null；

删除

alter table 表名称 modify 字段名 数据类型 NULL；
OR
alter table 表名称 modify 字段名 数据类型；
#不加默认允许为NULL

默认值约束

作用：限定某个字段/列的添加默认值
关键字：default
特点

位置约定：默认值约束不能添加到唯一(不能重复)或者主键（不能重复）上，其他列都可以
生效时机：当插入数据时，没有显示赋值，赋予默认值！
细节特点：添加约束时，default默认值，默认值对应正确数据类型！

添加:

建表时添加

create table 表名称(
	字段名 数据类型 default 默认值，
	字段名 数据类型 not null default 默认值);

建表后修改

alter table 表名称 modify 字段名 数据类型 default 默认值；
#如果这个字段原来有非空约束，需要保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null；

删除：

alter table 表名称 modify 字段名 数据类型;
#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null
#删除默认值约束，保留非空约束

检查约束

作用：检查某个字段的值是否符合XX要求，一般指的是值得范围
关键字：check（限制表达式）
特点：

新特性：5.7版本不支持check约束，8+版本才支持check约束
万能约束：check（表达式）， 可以自定义表达式，变成任何约束！
不推荐：不推荐使用check约束，进行数据检查，建议程序级限制！

添加：
建表时添加

create table 表名称(
	字段名 数据类型
	check(表达式),#check约束属于表级别，不用添加到列后
	字段名 数据类型 not null default 默认值);

建表后修改

alter table 表名 add constraint 约束名 check (表达式);
#约束名不能重复

删除：

alter table 表名 drop constraint 约束名;

拓展:查看约束

select * 
from information_schema.table_constraints
where table_schema = '库名
and table_name = 'emp3';

实体（行）级约束

唯一约束

作用：限定某个字段或者组合字段，在表中的数据是唯一
关键字：unique
特点：

约束数量：同一个表可以有多个唯一约束
空值处理：唯一性约束允许列值为空
约束名称：在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同

添加：
建表时添加

create table 表名称(
	字段名 数据类型 unique,
	字段名 数据类型 unique key);
create table 表名称(
	字段名 数据类型，
	[constraint约束名]unique key(字段名));

建表后修改

alter table table_name
add constraint constraint_name(约束名) unique(列名,列名)；

删除

alter table 表名
drop constraint 约束名；

主键

概念：区分一行不重复的列，主键列永不为空
关键字：
特点：

主键数量：每个表只能有一个主键
单一和复合：主键可以有单列和多列组成（复合主键）
主键列类型：可以是任意类型，只要唯一且不重复、不为null就行
主键命名：一般采用identify（标识单词）缩写xxid或xx_id等，但没有明确要求
主键索引：创建主键时，系统会默认在所在的列或列组合上建立对应的主键索引（能够根据主键自主查询的，就根据主键自主查询，效率更高）。如果删除主键约束了，主键约束对应的索引就会自动删除，主键索引固定命令：primary

主键约束

概念：针对主键列的数据约束和限制，确保主键列不会出现错误数据（保证唯一且不为空）
建表时添加：
create table 表名称（ 字段名 数据类型 primary key，#列级模式 ）； create table 表名称（ 字段名 数据类型， [constraint 约束名] primary key （字段名）#表级模式 ）；
建表后修改
alter table 表名称 add primary key(字段列表)；#字段列表可以是一个字段，也可以是多个字段
删除
删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在，唯一消失
删除主键约束和索引 alter table 表名称 drop primary key；

自增长约束

作用
限定某个整数类型字段，插入数据不显示维护，值自动增长
关键字
auto_increment
特点
添加位置：只能添加到键列（主键，唯一），普通列不可以 约束数量：每一张表只能有一个自增长约束 数据类型：增加自增长的列必须是整数类型 特殊情况：如果给自增长字段设置0或者null，列数据会自增长赋值，如果是非零和非空数据，那么将设置真实值
建表时添加
create table 表名称（ 字段名 数据类型 primary key auto_increment ）； create table 表名称（ 字段名 数据类型 unique key auto_increment ）
建表后修改
alter table 表名称 modify 字段名 数据类型 auto_increment
删除
alter table 表名称 modify 字段名 数据类型

外键

定义：引用或参照其他主键列值的列，我们称为外键，外键的值的范围应当对应主键的值的范围
特点
外键数量：每个表中可以包含多个外键 外键跨表：外键时跨表引用其他表的主键，被引用为主表，外键表为子表 外键类型：外键类型不能时任意类型，应该和主键类型对应，尽量命名相同 主外键关系：关系型数据库，关系指的就是主外键关系，有主外键的两张表能水平联查 其他影响：存在主外键关系（外键约束），删除主表数据时，可能会因为子表引用而删除失败。可以先删除子表的所有引用数据再删除

外键约束

作用：外键应该引用主键的值，但是如果不添加约束，可能会出现错误数据

建表时添加
create table 主表名称( 字段1 数据类型 primary key )；#子表中添加主外键约束 create table 子表名称（ 字段1 数据类型 primary key； [CONSTRAINT <外键约束名称>] FOREIGN KEY（外键） references 主表名（主键）[on update xx][on delete xx] ）；
建表后修改
alter table 子表名 add [constraint 约束名] foreign key（子表的字段） references 主表名（被引用字段）[on uodate xx][on delete xx];
删除
`
第一步，先查看约束名和删除外键约束
select * from information_scherma.table_constraints where table_name = '表名称'；#查看某个表的约束名
alter table 子表明 drop foreign 外键约束名；

第二步，查看索引名和删除索引
show index from 表名称；#查看某个表的索引名
alter table 子表名 drop index 索引名；
`

级联（约束等级设计）

Cascade	在父表上undate/delete记录时，同步update/delete子表的匹配记录
Set null	在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action	如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict	同no action，都是立即检查外键约束
Set default	父表有变更时，子表将外键列设置成一个默认的值，但lnnodb不能识别

样例
alter table 子表名 add constraint 外键约束名 foreign key （子表外键列） references 主表名 （主表主键列） on delete cascade on update cascade