数据库 - 基础

DDL 操作库表 (create alter drop)
DML 表的增删改 (insert update delete)
DQL 表的查询 (select)
DCL 用户和权限等操作

CRUD 增查改删

C - Create
R - Retrieve
U - Update
D - Delete

DDL（数据定义语言），操作数据库

Create

// 如果不存在再创建数据库
CREATE DATABASE IF NOT EXISTS db1;

// 使用指定编码创建数据库
CREATE DATABASE db2 CHARACTER SET gbk;

// +
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET gbk;

Retrieve

// 查询数据库列出名称
SHOW DATABASES;

// 查询创建数据库语句, 展示创建时的语句, 可查看字符集编码
SHOW CREATE DATABASE 数据库名;

Update

// 更新数据库
ALTER DATABASE 数据库名 CHARACTER SET utf8;

Delete

// 删除
DROP DATABASE 数据库名;

// 先判断存在再删
DROP DATABASE IF EXISTS 数据库名;

使用数据库

// 查询正在使用的数据库
SELECT DATABASE();

// 使用数据库
USE 数据库名;

DDL（数据定义语言），操作表

Create

// 创建表(最后参数不加逗号)
CREATE TABLE 表名( 列名类型, 列名2 类型2, 列名3 类型3 );

// copy
CREATE TABLE 新表名 LIKE 被复制表名;

类型

int 整数
double 小数
date 日期只包含年月日, yyyy-MM-dd
datetime 日期时间年月如时分秒, yyyy-MM-dd HH:mm:ss
timestamp 时间戳
varchar 字符串, name varchar(20)
其它不常用

约束

primary key 主键约束, auto_increment 自增约束(只能在主键设置)

// `primary key` 主键约束
// 在创建表时，添加主键约束
create table stu(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);

// 删除主键
ALTER TABLE stu DROP PRIMARY KEY;

// 创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

// `auto_increment` 自增约束
// 在创建表时，添加主键约束，并且完成主键自增长
create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);

// 删除自动增长
ALTER TABLE stu MODIFY id INT;

// 添加自动增长
ALTER TABLE stu MODIFY id INT auto_increment;

not null 非空约束

// 创建表时添加约束
CREATE TABLE stu(
	id INT,
	name VARCHAR(20) NOT NULL -- name为非空
);

// 删除name的非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);

// 创建表完后，添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;

unique 唯一约束

// 在创建表时，添加唯一约束
CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 手机号
);

// 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

// 在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

foreign key 外键约束

// 在创建表时，可以添加外键约束
CREATE TABLE 表名(
	...
	外键字段,
	CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段) REFERENCES 外键关联表名称(外键关联表对应字段)
);

// 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名称;

// 创建表之后，添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段) REFERENCES 外键关联表名称(外键关联表对应字段);

check 检查约束
default 默认值约束

Retrieve

// 查询表列出表名称
SHOW TABLES;

// 查询表结构
DESC 表名;

Update

// 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

// 修改表的字符集
ALTER TABLE 表名 CHARACTER SET utf8;

// 添加列
ALTER TABLE 表名 ADD 列名数据类型;

// 修改列名
ALTER TABLE 表名 CHANGE 旧列表新列名新类型(可选);

// 只改类型
ALTER TABLE 表名 MODIFY 列名类型(可选);

// 删除列
ALTER TABLE 表名 DROP 列名;

Delete

DROP TABLE 表名;

DROP TABLE IF EXISTS 表名;

DML（Data Manipulation Language，数据操作语言），增删改表

增

INSERT INTO 表名(列名, 列名2) VALUES(值, 值2)；

删

// (不加 WHERE 会操作所有记录) 条件(例如id = 2)
DELETE FROM 表名 WHERE 条件;

// 删所有记录 (不推荐, 实际会逐条执行, 效率低)
DELETE FROM 表名;

// 删所有记录 (推荐, 实际会执行删表再创建空表)
TRUNCATE FROM 表名;

改

// (不加 WHERE 会操作所有记录) 条件(例如id = 2)
UPDATE 表名 SET 列名 = 值, 列名2 = 值2 WHERE 条件(例如id = 2)

DQL（Data Query Language），查表

SELECT * FROM 表名 WHERE 条件;

语法

select
  字段列表
from
  表名列表
where
  条件列表
group by
  分组字段
having
  分组之后的条件
order by
  排序
limit
  分页限定

运算符

> < <= >= = <>

between ... and

in (集合)

like 模糊查询

is null

and &&

or ||

not !

基础查询

// 多字段查询 (*可代替所有字段)
select 字段, 字段2 from 表名;

// 去重 distinct
select distinct 列名 from 表名;

// 某列去重
select distinct 列名, 列名2 from 表名;

// 计算
ifnull(被替换表达式, 替换表达式2) null参加运算时计算结果都为null 这时可用 ifnull 替换

// 别名
as 可省略, 空格代替

模糊查询 like

// % 是通配符

// 查询第1个字是马的人 (字+%)
select * from student where name like ‘马%’;

// 第2个字是化 (下划线+字+%)
select * from student where name like ‘_化%’;

// 名字是3个字的 (3个下划线)
select * from student where name like ‘___’;

// 包含 (%德%)
select * from student where name like ‘%德%’;

排序查询

语法：order by 子句
- order by 排序字段1 排序方式1 ，排序字段2 排序方式2…
排序方式
- ASC：升序，默认的。
- DESC：降序。

聚合函数

聚合函数：将一列数据作为一个整体，进行纵向的计算

count：计算个数
- 一般选择非空的列：主键，count(*)
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

注意：IFNULL函数可处理null

分组查询

语法：group by 分组字段

分组查询的 select 字段只能是: 分组字段或聚合函数

select 子句 frome 表 where 条件 group by 分组字段 having 聚合函数条件

having 是为了解决 where 不能带聚合函数，where 是用于筛选分组前的数据, having 是用于筛选分组后的数据

SQL 语法规定，在使用 group by 时，select 子句中出现的字段，要么是 group by 中的列，要么被包含在聚合函数中
因为, 分组后是一个整体, 要么是分组字段(整体), 要么是聚合函数(聚合函数是这个整体的运算后的结果)

分页查询

语法：limit 开始的索引, 每页条数

公式：开始的索引 = （当前的页码 - 1） * 每页条数

DCL 管理用户，授权

一般由 DBA 数据管理员操作, root 用户

// 切换到 mysql 数据库
USE mysql;

// 查询 user 表
SELECTE * FROM user;

// 修改用户密码直接改 user 表 (注意要使用 PASSWORD() 函数加密)
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’

// 修改用户密码使用 MYSQL 语句 (注意要使用 PASSWORD() 函数加密)
SET PASSWORD FOR ‘用户名’@’主机名’ = PASSWORD(‘新密码’);

忘记 root 密码需要无验证启动mysql服务再通过修改表的方式设置密码

数据库 - 基础

DDL（数据定义语言），操作数据库

Create

Retrieve

Update

Delete

使用数据库

DDL（数据定义语言），操作表

Create

类型

约束

Retrieve

Update

Delete

DML（Data Manipulation Language，数据操作语言），增删改 表

增

删

改

DQL（Data Query Language），查表

基础查询

模糊查询 like

排序查询

聚合函数

分组查询

分页查询

DCL 管理用户，授权

DML（Data Manipulation Language，数据操作语言），增删改表