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

最新推荐文章于 2024-11-01 12:20:35 发布

liuwangleoooO

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量185

点赞数

分类专栏： MySQL 文章标签：数据库 DDL DCL 约束

本文链接：https://blog.csdn.net/qq_36581961/article/details/112795414

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章目录

DDL
约束
DCL
- 管理用户
- 权限管理

$\color{blue}{参考资料}$
思维导图：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

管理用户

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'@'%';