目录
一、数据库定义 & 基操
1. 关系型数据库
概念:建立在关系型模型基础上,由多张相互关联的二维表组成的数据库。
2. MySQL启动
net start mysql80
net stop mysql80
3. MySQL客户端连接
mysql -u root -p
4. MySQL语句分类
分类 | 说明 |
DDL | 定义数据库对象,例如数据库、表、字段 |
DML | 操作语言,对数据进行增删改操作 |
DQL | 查询语言,查询记录 |
DCL | 控制语言,创建用户、控制访问权限 |
二、DDL& 数据类型
5. DDL语句
关于数据库 | |
show databases; | 查询所有数据库 |
select database(); | 查询当前数据库 |
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; | 创建 |
drop database [if exists] 数据库名; | 删除 |
use 数据库名; | 使用 |
关于表 | |
show tables; | 查询当前数据库的所有表 |
desc 表名; | 查询表结构 |
show create table 表名; | 查询指定表的建表语句 |
-
表创建
create table 表名(
字段 类型 [comment 字段注释],
字段 类型 [comment 字段注释],
字段 类型 [comment 字段注释]
) [comment 表注释];
create table staff(
id int comment 'id',
name varchar(50) comment 'name',
age int,
gender varchar(1)
) comment 'staff';
-
查询表结构
6. 数据类型及案例
-
数值类型
类型 | 大小 | 有符号(signed)范围 | 无符号(unsigned)范围 |
TINYINT | 1 Byte | (-128,127) | (0,255) |
SMALLINT | 2 Bytes | (-32768,32767) | (0,65535) |
MEDIUMINT | 3 ytes | (-8 388 608,8 388 607) | (0,16 777 215) |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 |
以年龄为例,年龄不可能出现负数,故可用unsigned。除此之外,如果年龄用int类型浪费空间,所以最合适的为 age TINYINT UNSIGNED。
以分数为例解释double的用法,score double(4,1), 4代表总共最多有四个数字组成,1代表小数点后一位。
-
字符串类型
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
定长char(5)与变长varchar(5)的区别:即便只存一个字符,char(5)还是会占用5个字符的内存,性能好,因为不用计算需要占用几个空间。而varchar(5)则仅占用一个字符,性能差。
对于姓名推荐使用varchar(20),对于性别推荐使用char(1)。
-
日期时间类型
类型 | 大小 (bytes) | 范围 | 格式 |
---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS |
YEAR | 1 | 1901/2155 | YYYY |
DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC | YYYY-MM-DD hh:mm:ss |
以生日为例,用date类型就可。
7. 表的修改与删除
修改 | 说明 |
alter table 表名 add 字段名 类型 [comment 注释] [约束]; | 添加字段 |
alter table 表名 change 旧字段名 新字段名 类型 [comment 注释] [约束]; | 修改字段名和字段类型 |
alter table 表名 rename to 新表名; | 修改表名 |
8. 表的删除
删除 | 说明 |
alter table 表名 drop 字段名; | 删除字段 |
alter table 表名 modify 字段名 字段类型 after 字段 | 改变字段顺序 |
drop table [if exists] 表名; | 删除表 |
truncate table 表名; | 删除指定表并重新创建该表 |
三、DML
9.添加数据
insert into 表名(字段名1,字段名2...)values (值1,值2....); | 给指定字段添加数据 |
insert into 表名 values (值1,值2....); | 给全部数据添加数据 |
insert into 表名(字段名1,字段名2...)values (值1,值2....),(值1,值2....) ....; | 批量添加数据 |
insert into 表名 values (值1,值2....),(值1,值2....) ....; |
注意:
1. 字段与插入值的顺序一一对应
2. 字符串和日期数据应该包含在引号之中
3. 插入的数据大小必须在规定范围内
10.修改数据
update 表名 set 字段名=值,字段名=值,... [where 条件]; | 修改数据 |
修改语句的条件可以有也可以没有,如果没条件,会修改整张表的所有数据。
11.删除数据
delete from 表名 [where 条件] | 删除数据 |
注意:
delete语句的条件可有可无,如果没有会删除整张表的所有数据。
delete语句不能删除某一个字段的值(可用update)。
四、DQL
12. DQL语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
select 字段1,字段2,... from 表名; | 查询多个字段 |
select * from 表名; | |
select 字段1 [as 别名],字段2 [as 别名] ... from 表名; | 设置别名 |
select distinct 字段列表 from 表名; | 去除重复记录 |
-
条件查询
select 字段列表 from 表名 where 条件列表;
操作符 | 描述 |
---|---|
= | 等号,检测两个值是否相等,如果相等返回true |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true |
between...and... | 在某个范围之间 |
in(...) | 在in列表中的值;多旋一 |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 是null |
and 或 && | 并且(多个条件同时成立) |
or 或 || | 或者(任意一个成立即可) |
not 或 ! | 非,不是 |
-
聚合函数
count、max、min、avg、sum
注意:使用聚合函数时,所有的null值不参与计算
-
分组查询
select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组过滤后的条件];
where与having的区别:
执行时机:where分组之前进行过滤,不满足where条件,不参与分组;having是分组之后对结果进行过滤。
判断条件:where不能对聚合函数进行判断,而having可以。
注意
执行顺序:where > 聚合函数 > having
-
排序查询
select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式2;
asc:升序 desc:降序
注意:当第一个字段值相同时,才会根据第二个字段排序。
-
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
注意
起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
-
执行顺序
from - > where -> group by -> select -> order by -> limit
五、DCL
13. 用户管理语法
use mysql; select * from user; | 查询用户 |
create user ‘用户名’@‘主机名’ identified by ‘密码’; | 创建用户 |
alter user ‘用户名’@‘主机名’ identified with mysql_native_password by '新密码'; | 修改用户密码 |
drop user ‘用户名’@‘主机名’ ; | 删除用户 |
14. 权限控制语法
show grants for ‘用户名’@‘主机名’ ; | 查询权限 |
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’ ; | 授予权限 |
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’ ; | 撤销权限 |
注意:
多个权限之间,用逗号分隔