MySQL—SQL基础(DDL、DCL、约束)

$参考资料$
思维导图：https://processon.com/mindmap/6004e42fe401fd261bc70815
SQL基础：https://www.w3school.com.cn/sql/index.asp

DDL

数据库

CREATE DATABASE

CREATE DATABASE 用于创建数据库。

# create database 数据库名
create database mydb 

ALTER DATABASE

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

参考文章：http://blog.itpub.net/26006967/viewspace-1749746/

DROP DATABASE

DROP DATABASE 语句用于删除数据库：

# drop database 数据库名
drop database mydb

表

CREATE TABLE

CREATE TABLE 语句用于创建数据库中的表。
语法

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

示例

CREATE TABLE student(
	name varchar(255),
	age int(100),
	gender char(2),
	city varchar(155)	
)

ALTER TABLE

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
添加列

# alter table 表名 add 列名 datatype(数据类型)
alter table student add phone char(11)

修改列的数据类型

# alter table 表名 alter column 列名 datetype(数据类型)
alter table student alter column age char(10) 

删除列

# alter table 表名 drop column 列名
alter table student drop column city

某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。

DROP TABLE

DROP TABLE用来删除表

# drop table 表名
drop table student

注意：
delete是删除数据库中某一条或者若干条记录，不会改变表结构及索引
truncate 是删除表内全部数据，相当于清空表，但并不删除表本身
drop是将表从数据库中删除

索引

您可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

CREATE INDEX

CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

# CREATE INDEX index_name ON table_name (column_name) 在某张表的指定列创建索引
create index nameIndex on student (name)

ALTER INDEX

# 查看索引
show index from student
# 可以先删除后添加
# 删除
drop index nameIndex on student
# 添加
alter table student add index phoneIndex(phone)

普通索引、UNIQUE索引和PRIMARY KEY索引这三种创建索引的格式

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

DROP INDEX

我们可以使用 DROP INDEX 命令删除表格中的索引。

# DROP INDEX index_name ON table_name
drop index nameIndex on student
alter table student drop index nameIndex

约束

约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

我们将主要探讨以下几种约束：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

not null
NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值：

CREATE TABLE Persons
(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
)

unique
UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE Persons
(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	UNIQUE (Id_P)
)

primary key
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE Persons
(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (Id_P)
)

foreign key
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。让我们通过一个例子来解释外键。请看下面两个表：

请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。 "Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。 "Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。 FOREIGN KEY 约束用于预防破坏表之间连接的动作。 FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CREATE TABLE Orders
(
	Id_O int NOT NULL,
	OrderNo int NOT NULL,
	Id_P int,
	PRIMARY KEY (Id_O),
	FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

check
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制

CREATE TABLE Persons
(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	CHECK (Id_P>0)
)

# 如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束
alter table student add check (id_p > 0)
# 撤销check
alter table student drop check id_p

default
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束

ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束

ALTER TABLE Persons ALTER City DROP DEFAULT

auto increment
Auto-increment 会在新记录插入表中时生成一个唯一的数字。

CREATE TABLE Persons
(
	P_Id int NOT NULL AUTO_INCREMENT,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (P_Id)
)

DCL

推荐两篇文章
https://www.cnblogs.com/yuehouse/p/11184399.html
https://blog.csdn.net/u011526599/article/details/51175883

管理用户

1. 添加用户：
    * 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    * 主机名可以用%表示该用户可以从任意地址访问数据库
2. 删除用户：
    * 语法：DROP USER '用户名'@'主机名';
3. 修改用户密码：
    UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

    SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

    * mysql中忘记了root用户的密码？
        1. cmd -- > net stop mysql 停止mysql服务
            * 需要管理员运行该cmd
        2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
        3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
        4. use mysql;
        5. update user set password = password('你的新密码') where user = 'root';
        6. 关闭两个窗口
        7. 打开任务管理器，手动结束mysqld.exe 的进程
        8. 启动mysql服务
        9. 使用新密码登录。
4. 查询用户：
    -- 1. 切换到mysql数据库
    USE myql;
    -- 2. 查询user表
    SELECT * FROM USER;

注：通配符： % 表示可以在任意主机使用用户登录数据库

权限管理

1. 查询权限：
    -- 查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
    SHOW GRANTS FOR 'lisi'@'%';

2. 授予权限：
    -- 授予权限
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    -- 给张三用户授予所有权限，在任意数据库任意表上
    GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. 撤销权限：
    -- 撤销权限：
    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';