1 定义
- DDL: 数据库定义语言,定义数据库语言,操作库表的语言
- DML:数据库操作语言,增删改
- DQL:数据库查询语言
- DCL:数据库控制语言,用来创建数据库用户,控制数据库用户的权限
2. 常用数据类型
数字 字符串 日期
3.1 数字
TINYINT:1字节;有符号范围-128127;无符号范围0255;小整数值
SMALLINT:2字节;有符号范围-3276832767;无符号范围065535;大整数值
MEDIUMINT:3字节;大整数值
INT:4字节;大整数值
BIGINT:8字节,对应long类型,极大整数值
FLOAT:4字节;单精度浮点数
DOUBLE:8字节;双精度浮点数
DECIMAL:小数值(精确定点数)
注意:小数类型使用举例:
- 成绩,保留一位小数 60.5分~100.0分, FLOAT(4, 1) 4表示总长最大4位,1表示1位小数
3.2 字符串
- CHAR:0-255字节;定长字符串
- VARCHAR:0-65535字节;可变长度字符串
- TINYTEXT:短文本字符串
- TEXT:长文本字符串
- LONGTEXT:极大文本数据
3.3 日期
- DATE:3字节;日期,1000-01-01到9999-12-31
- TIME:3字节;时间,-838:59:59到838:59:59
- YEAR:1字节;年份,1901到2155
- DATETIME:8字节;1000-01-01 00:00:00到9999-12-31 23:59:59
- TIMESTAMP:4字节;时间戳;1970-01-01 00:00:00到2038-01-19 03:14:07
3. DDL Definetion 数据库定义语言
1. 数据库操作
# 创建数据库
CREATE DATABASE [IF NOT EXIST] TABLENAME [DEFAULT CHARSET UTF8MB4] [COLLATE utf8mb4_general_ci];
# 展示所有的数据库
SHOW DATABASES;
# 使用某个数据库
USE TABLENAME;
# 查询当前使用的是哪个数据库
SELECT DATABASE();
# 删除数据库
DROP DATABASE [IF EXIST] DBNAME;
2. 表操作
# 展示库中的所有表
SHOW TABLES;
# 展示表的结构
DESC TABLENAME;
# 展示表的建表语句
SHOW CREATE TABLE TABLENAME;
# 修改表名
ALTER TABLE OLDTABLENAME RENAME TO NEWTABLENAME
# 删除表
DROP TABLE [IF EXIST] TABLENAME;
# 删除表后重新建表,不改变数据结构;相当于清楚表里面的数据
TRUNCATE TABLE [IF EXIST] TABLENAME;
# 增加字段
ALTER TABLE TABLENAME ADD COLUMNNAME 类型长度 [COMMENT 注释] [约束];
# 修改字段数据类型
ALTER TABLE TABLENAME MODIFY COLUMNNAME 类型长度 [COMMENT 注释] [约束];
# 修改字段名和数据类型
ALTER TABLE TABLENAME CHANGE OLDCOLUMNNAME NEWCOLUMNNAME 类型长度 [COMMENT 注释] [约束];
# 删除字段
ALTER TABLE TABLENAME DROP COLUMNNAME;
4. DML Manipulation 数据库操作语言
insert into tablename () value (); -- 插入
update tablename set column1=value1; -- 更新
delete tablename where ...; -- 删除
5. DQL Query 数据库查询语言
执行顺序
- from子句组装数据源
- where子句进行条件过滤
- group by 子句对数据进行分组
- 使用聚合函数进行计算
- 使用having子句筛选分组
- 计算所有表达式(select中的)
- select的字段
- order by 和 limit
SELECT
*
FROM
TABLENAME
WHERE
...
GROUP BY
...
HAVING
...
ORDER BY
...
LIMIT
1. 逻辑运算符
>
>=
<
<=
<>或者!=
between and
in (...)
like
is null
and或者&&
or或者||
not !
2. 聚合函数
count(...) -- 求数量
max() -- 求最大值
min() -- 求最小值
avg() -- 求平均值
sum() -- 求和
注意:count(*) count(id) count(1)的区别
其实根本点在于null值不参与聚合函数的运算,那么由此我们可以得出:count(*)得出来的值为所有记录数量;count(id)的时候,如果id为主键列,那么得出来的也是所有记录数,但是如果count非主键列的时候,如果某条记录的该列为null,那么就会过滤掉该条记录;count(1)计算出来的也是所有记录数
若列名为主键,count(列名)会比count(1)快;
若列名不为主键,count(1)会比count(列名)快;
若表多个列并且没有主键,则 count(1) 的执行效率优于 count(*);
若表有主键,则 select count(主键)的执行效率是最优的;
若表只有一个字段,则 select count(*)最优;
3. 分组查询
group by
having
having和where的区别:
- 执行时机:where是组装完数据源就会执行的;having是在group by后执行的,要更晚一些
- where不能对聚合函数进行判断;having可以(因为在sql的执行顺序中是先执行where然后group by然后才是聚合函数和having)
6. DCL Control 数据库控制语言
DCL主要用来管理数据库用户和控制用户权限的
1. 管理数据库用户
# 使用'mysql'这个库
use mysql;
# 从user表中获取数据,查询所有用户
select * from user;
# 创建用户,用户身份通过用户名+主机名进行标识,用户名+主机名才能表现用户的身份
create user '用户名'@'主机名' identifyed by '密码';
# 修改用户密码
alter user '用户民'@'主机名' identified with mysql_native_password by '密码';
# 删除用户
drop user '用户名'@'主机名';
2. 权限控制
权限可分为以下几种:
ALL 或者 ALL PRIVILEGES:所有权限
SELECT:查看权限
INSERT:插入
UPDATE:修改
DELETE:删除
ALTER:修改表结构
DROP:删除库、表、视图
CREATE:创建库、表
# 展示用户的所有权限
show grants for '用户名'@'主机名';
# 赋予用户权限;多个权限可用','分隔;库名和表名可以使用*进行匹配表示所有
grant 权限列表 on 库名.表名 to '用户名'@'主机名';
# 移除用户的权限
revoke 权限列表 on 库名.表名 from '用户名'@'主机名';