SQL系列之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命令

相关文章 我们该如何设计数据库 数据库设计经验谈 数据库设计过程 数据库编程总结

相关文档 数据库性能调优技巧 数据库性能调整 数据库性能优化讲座 数据库系统性能调优系列

相关课程 高性能数据库设计与优化 高级数据库架构师 数据仓库和数据挖掘技术 Hadoop原理、部署与性能调优