SQL系列之DDL/DCL语言

前言：前面王豆豆已经写了增删改查这四种软件测试人员必须要掌握的语句，以下是链接：

增删改查的增删改

增删改查的查之简单查询

增删改查的查之高级查询

今天王豆豆主要讲一下DDL和DCL语言，这些语句只需要了解即可，当然能够记住是最好的。

SQL语言主要分为以下四类：

数据定义语言（DDL）：

用于下定义和管理数据对象（库，表，索引，视图），包括数据库，数据表等，例如：CREATE DROP ALTER等语句。

数据操作语言（DML）：

用于操作数据库对象中包含的数据，例如：INSERT UPDATE DELETE语句。

数据查询语言（DQL）：

用于查询数据库对象中包含的数据，能够进行单表查询，连接查询，嵌套查询，以及集合查询等各种复杂不同的数据库查询，并将数据返回到客户机中显示，例如：SELECT语句。

数据控制语言（DCL）：

用于管理数据库的语言，包含管理权限及数据更改，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

SQL语言通用规则：

1. 数据库对关键字的大小写不敏感，不包括数据，一般是关键字大写

2.SQL语言以分号结尾，只认分号（;），没有分号（;）数据库就会认为语句未结束

3.SQL语言可以分行写，以分号结尾

4.对空格敏感，每一个命令都空格分开

5.对数据可以使用单引号，也可以使用双引号，但必须配对使用

1.使用命令提示符登录mysql

(1)一般使用的是,默认就是3306和本机

mysql -u用户名 -p密码

例如：mysql -uroot -p123456

(2)如果端口号不是默认的，主机不是本机

格式：mysql -u用户名 -p密码 -h 主机地址 -P 端口号

例如：mysql -uroot -p123456 -h localhost -P 3307

例如：mysql -uroot -p123456 -h 192.168.2.83 -P 3307

2.查看数据库中的所有库名称

show databases;

3.选择库

use 库名称

例如：use mysql;

4.查看库的所有表

show TABLES;

5.mysql库中user表为用户表，包含mysql中所有的用户

user表字段解析：

Host 字段 -----主机地址，表示该用户能在什么地方进行登录

localhost ---本机

127.0.0.1 ---本机，指IPv4----TCP/IP

::1 -----本机，指IPv6

表示root只能本机登录，不能远程登录

192.168.2.92----指仅能通过此IP登录，任意IP

%-----指所有机器都能登录

User ----用户名

一个用户可以创建多个host

Password---密码

加密后的密文

为空的表示没密码

CREATE---创建库/用户

CREATE可以创建库，创建表，创建用户

(1)创建库

格式: CREATE DATABASE 库名;

mysql> CREATE DATABASE test_k;

(2)创建用户

1.创建默认用户

格式: CREATE USER username;

创建用户最简单的命令

mysql> CREATE user test_k;

表数据显示为：

host user password

% test_k

创建出来的用户，host为%，没有密码

2.创建用户，并指定密码

格式： CREATE USER username IDENTIFIED BY "PASSWORD";

mysql> CREATE user test_c identified by "123456";

表数据显示为：

host user password

% test_c *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

3.创建用户的同时指定host和密码

mysql> CREATE user 'test_t'’@'192.168.2.91‘' identified by '123456'‘;

4.创建一个用户test1,密码为123456，该用户可以在任意机器上登录

CREATE USER 'test1'@'%' identified by '123456';

==CREATE USER test1 identified by '123456';

CREATE TABLE----创建表

(1)创建表

公式： CREATE TABLE 表名( 字段名称1 数据类型 [属性][约束],

字段名称2 数据类型 [属性][约束],

字段名称3 数据类型 [属性][约束],

......

字段名称n 数据类型 [属性][约束],

);

(表名和字段名称是标识符，自己命名的)

mysql> CREATE TABLE student(id int PRIMARY KEY,

-> name varchar(10),

-> sex varchar(5),

-> age int(10),

-> addr varchar(30));

(2)查看表结构

DESC 表名;

(3)数据类型

原文链接：https://www.w3cschool.cn/mysql/mysql-data-types.html

数值类型：

文本类型：

日期类型：

（4）属性

(1).unsigned---- 无符号型

tinyint---值的范围（-128-127） 有符号位

无符号的范围（0-255）

CREATE TABLE t1(id tinyint(10));

CREATE TABLE t2(id tinyint(10) unsigned);

mysql> DESC t1;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | tinyint(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

mysql> DESC t2;

+-------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+-------+

| id | tinyint(10) unsigned | YES | | NULL | |

+-------+----------------------+------+-----+---------+-------+

1.向t1表中插入数据129

insert into t1(id) values(129);

ERROR 1264 (22003): Out of range value for column 'id' at row 1

2.向t2表中插入数据-11

insert into t2(id) values(-11);

ERROR 1264 (22003): Out of range value for column 'id' at row 1

(2) 自动补全--zerofill

字段设为zerofill时，自动为unsigned，且只能是数值类型

zerofill，unsigned只对数值类型的字段有效，且为整型

mysql> CREATE TABLE t1(id int zerofill);

mysql> DESC t1;

+-------+---------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------------+------+-----+---------+-------+

| id | int(10) unsigned zerofill | YES | | NULL | |

+-------+---------------------------+------+-----+---------+-------+T

mysql> insert into t1(id) values(12);

mysql> select * from t1;

+------------+

| id |

+------------+

| 0000000012 |

+------------+

（3）自动递增---auto increment

字段为auto_increment时，字段必须为主键

mysql> CREATE TABLE t1(id int auto_increment PRIMARY KEY,name varchar(10));

mysql> DESC t1;

+-------+---------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------+---------+------+-----+---------+----------------+

1.插入数据时，可以不插入相应数据（空值 ），但是该字段的值为自动递增，即是在最大值 上加1

INSERT INTO t1 (name) values("test");

2.数据如果是delete了，自动递增的计数器并不会清零，而会根据以前的最大值 再加1

3.插入数据时，id插入为null,0时，该字段还是自动递增，在最大值上加1

用法：流水号，学号等

1.不能空，唯一

auto_increment 必须为PRIMARY KEY(唯一+不能空）

2.计数器，值会自动递增

(4) null and not null--空和不为空

创建表时，null是默认为YES，表示该字段在插入数据时，可以为null(空)

eg:

INSERT INTO t3 VALUES (1,"TEST");

INSERT INTO t3(NUM) VALUES (2);

mysql> DESC t3;

+-------+------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------------------+------+-----+---------+-------+

| num | tinyint(3) unsigned zerofill | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

+-------+------------------------------+------+-----+---------+-------

B.NOT NULL--不为空

CREATE TABLE t4 (num tinyint not null,

name varchar(10) not null,

age int(3));

mysql> DESC t4;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| num | tinyint(4) | NO | | NULL | |

| name | varchar(10) | NO | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

例如：不给num字段插入数据

INSERT INTO T4(name,age) values("test",24);

1364 - Field 'num' doesn't' have a default value

提示"num"没有默认值 ，在插入数据时必须输入数据

（5）DEFAULT-----默认值

CREATE TABLE t5(id int unsigned auto_increment PRIMARY KEY,

name varchar(10) not null default " ",

age int(3) not null default 0,

address varchar(30) );

（6）PRIMARY KEY----主键 :

1,唯一

2.不能为空

一个表中只有一个主键

（7）UNIQUE--- 唯一

数据不能重复

NOT NULL + UNIQUE = PRIMARY KEY

一个表中可以有多个UNIQUE

CREATE TABLE t6(id int PRIMARY KEY,

name varchar(10) unique,

age int(2) not null,

phone int(10) not null unique);

例如：向name字段插入重复的值

INSERT INTO t6(id,name,age,phone) values (2,"tester",24,"13410000000");

1062 - Duplicate entry 'tester' for key 'name'

以上只是一部分经常使用到的属性和约束，并不完整。

（7）IF NOT EXIST

CREATE TABLE [IF NOT EXISTS] 表名(字段名1 数据类型 [属性] [约束],

字段名2 数据类型 [属性] [约束])[表类型][字符集];

例如：库中已经存在t1表，再次插入t1表

mysql> CREATE TABLE t1(id int);

1050 - TABLE 't1' already exists

mysql> CREATE TABLE if not exists t1(id int);

Query OK, 0 rows affected

if not exists 在创建表时判断表是否存在，如果存在，执行通过不报错，但也不创建表，

如果表不存在，执行通过不报错，同时创建表

推荐：在创建时，最好加上判断

GRANT-权限

1.查看权限

(1)查看权限：

SHOW GRANTS;

(2)查看当前用户的权限

SELECT USER(); ---查看当前所在的用户

(3)查看其它用户的权限

SHOW GRANTS FOR 用户名;

例如：SHOW GRANTS FOR test; ---查看test用户的权限

(4)展示权限解析：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'

IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

WITH GRANT OPTION

a.ALL PRIVILEGES----表示所有权限

b.ON*.* ---表示所有库.所有表 *表示所有

c. TO 'root'@'localhost'---表示哪个用户，这里指是root

d. IDENTIFIED BY PASSWORD

'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ---表示该用户访问的密码

e.WITH GRANT OPTION ---可以赋权

(5)USAGE---无权限

2.赋权

格式：

1.只赋权

GRANT 权限1,权限2,权限3,.....权限n ON *.* TO "username"@"host";

2.赋权并修改密码

GRANT 权限1,权限2,权限3,.....权限n ON *.* TO "username"@"host" IDENTIFIED BY "password";

1.给test_k赋select,insert,UPDATE权限

GRANT select,insert,UPDATE on *.* to 'test_k'@'%';

2.给test_k这个用户针对test库赋所有权限

GRANT ALL PRIVILEGES on test.* to 'test_k'@'%';

REVOKE-回收权限

公式: REVOKE 权限1,权限2,权限3,.....权限n ON *.* FROM "username"@"host";

REVOKE select on *.* from 'test_k'@'%';

revoke ---回收权限

on---表示范围

from ---表示从谁那里收权限

修改密码

(1)在命令提示窗口输入

在修改密码时，必须知道旧密码

mysqladmin -u用户名 -p旧密码 password 新密码 -P 端口号

（端口号不是3306,要加-P参数）

(2)修改user表中的用户密码（在mysql中修改的，所以必须事先进行登录）

UPDATE mysql.user SET password=password("123456")

WHERE user="test_t";

(3)SET password

1.修改当前用户的密码，要先登录，也要知道原密码

mysql> SET password = password("123456");

2.修改其它用户的密码

一般只有root用户才有权限，必须要知道root用户的密码

mysql> SET password for test_k = password("1234567");

mysql> SET password for test@192.168.2.92= password("987456");

修改密码，要么要知道该用户的密码，要么要知道root用户的密码

如果root用户的密码忘记了，只能改配置文件

DROP-删库，删表，删用户

DROP 命令慎用

(1)删除库

DROP DATABASE 库名;

(2)删除表

DROP TABLE [IF EXISTS] 表名;

删除多张表：

DROP TABLES 表1,表2…；

(3)删除用户

DROP USER 用户名;

1.host为%

mysql> DROP USER test_c;

2.host不为%，为IP，域名

DROP USER "username"@"host";

DROP USER 'test_t'@'192.168.2.83';

3.直接删除mysql.user表中的数据，也是删除用户

DELETE FROM mysql.user WHERE user="test";

ALTER -修改表结构

(1)修改表的名称

mysql> ALTER TABLE t6 RENAME AS student;

(2)修改表字段的数据类型

ALTER TABLE 表名 MODIFY 列名称 新的数据类型;

mysql> ALTER TABLE student modify phone varchar(11);

(3)修改表字段名称

ALTER TABLE 表名 CHANGE 旧列名称 新列名称 数据类型

mysql> ALTER TABLE student CHANGE name username varchar(10);

新列名称的数据类型，可以同时修改

(4)增加字段

ALTER TABLE 表名 ADD 列名称 数据类型 [完整属性] [约束] [first/after 列名称2];

1.如果不指定顺序，默认新增到最后

ALTER TABLE student ADD address varchar(30) not null default " chengdu";

2.将新增字段添加到最前面

ALTER TABLE student ADD s_id int auto_increment PRIMARY KEY FIRST;

3.将新增字段添加到username后面

ALTER TABLE student ADD sex varchar(5) not null after username;

（5）删除字段

ALTER TABLE 表名 DROP 列名称;

在实际工作中一般表中有大量的数据，在修改表结构会导致数据的丢失或者串行，所以一般不建议使用ALTER命令