DDL 数据定义语言

2021-07-22 00:28:59

创建数据库（CREATE）

# 语法

mysql> help create database

Name: 'CREATE DATABASE'

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:

    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
# 创建数据库

mysql> create database mydb01;

Query OK, 1 row affected (0.00 sec)

mysql> create SCHEMA mydb02;

Query OK, 1 row affected (0.00 sec)
# 建库时提示已存在，可以使用 IF NOT EXISTS 避免报错

mysql> create database mydb;

ERROR 1007 (HY000): Can't create database 'mydb'; database exists

mysql> create database IF NOT EXISTS mydb;

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> create database IF NOT EXISTS mydb;

Query OK, 1 row affected, 1 warning (0.00 sec)
# 查看建库语句

mysql> show create database mydb;

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

| Database | Create Database                                                 |

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

| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |

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

1 row in set (0.00 sec)
# 创建数据库并指定字符集

mysql> create database db3 charset utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> show create database db3;

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

| Database | Create Database                                              |

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

| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

# 不指定校验规则默认就是 utf8_general_ci

mysql> create database db4 charset utf8;

Query OK, 1 row affected (0.00 sec)

删除数据库（DROP）

mysql> drop database db1;

Query OK, 0 rows affected (0.00 sec)

修改数据库（ALTER）

mysql> show create database db2;

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

| Database | Create Database                                                |

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

| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |

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

1 row in set (0.00 sec)

mysql> alter database db2 charset utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> show create database db2;

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

| Database | Create Database                                              |

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

| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

创建数据表（CREATE）

# 语法

mysql> help create table;

Name: 'CREATE TABLE'

Description:

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    (create_definition,...)

    [table_options]

    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options]

    [partition_options]

    [IGNORE | REPLACE]

    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    { LIKE old_tbl_name | (LIKE old_tbl_name) }
# 建表

# 1.进入一个库

mysql> use db2

Database changed

# 2.查看当前所在库

mysql> select database();

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

| database() |

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

| db2        |

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

1 row in set (0.00 sec)

# 3.1 建表，建表最少有一列

mysql> create table tb1;

ERROR 1113 (42000): A table must have at least 1 column

mysql> create table tb1(id int);

Query OK, 0 rows affected (0.04 sec)

# 3.2 建表，建表有多个列

mysql> create table student(

    -> id int,

    -> name varchar(12),

    -> age tinyint,

    -> gender enum('M','F'),

    -> cometime datetime);

Query OK, 0 rows affected (0.01 sec)

# 3.3 插入数据

mysql> insert into student values(1,'wqh',18,'male',now());

...

......

mysql> select * from student;

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

| id   | name | age  | gender | time                |

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

|    1 | wqh  |   18 | male   | 2020-07-14 20:58:52 |

|    2 | wzh  |   18 | male   | 2020-07-14 20:58:58 |

|    3 | djd  |   18 | male   | 2020-07-14 21:00:24 |

|    4 | tcy  |   18 | female | 2020-07-14 21:00:33 |

|    5 | gsp  |   18 | male   | 2020-07-14 21:00:43 |

|    6 | cjq  |   18 | male   | 2020-07-14 21:00:53 |

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

6 rows in set (0.00 sec)

# 4.查看表

mysql> show tables;

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

| Tables_in_db2 |

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

| tb1           |

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

1 row in set (0.00 sec)

mysql> desc tb1;

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

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

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

| id    | int(11) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

数据表的数据属性

# 查看创建数据表的语句

mysql> show create table students;

| students | CREATE TABLE `students` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',

  `name` varchar(12) NOT NULL COMMENT '学生姓名',

  `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',

  `gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别',

  `cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8                   |

1 row in set (0.00 sec)

# 查看数据表的详细描述

mysql> desc students;

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

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

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

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

| name     | varchar(12)         | NO   |     | NULL              |                |

| age      | tinyint(3) unsigned | NO   |     | NULL              |                |

| gender   | enum('M','F')       | YES  |     | M                 |                |

| cometime | datetime            | YES  |     | CURRENT_TIMESTAMP |                |

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

5 rows in set (0.00 sec)

数据类型属性（Type）

# 字段数据类型（Type）

int			整数，范围 -2^31 - 2^31-1           # 即 -2147483648 - 2147483647

tinyint			最小整数   -128 - 127		   # 如年龄，如果设置字段值非负数，范围变化为 0 - 255

varchar			字符类型（变长）	                   # 如身份证号

char			字符类型（定长）	                   # 如名字

enum			枚举类型		                   # 如性别，给其固定选项，只能选则固定选项中的值

datetime		时间类型		                   # 年月日时分秒

date			时间类型		                   # 年月日

# int 类型

mysql> create table tb1(id int);

Query OK, 0 rows affected (0.04 sec)

mysql> insert tb1 values(1);

Query OK, 1 row affected (0.01 sec)

# 下面这条虽然报错，但其实会显示 11111111111 ，因为 my.cnf 的一条默认配置（最底行）

mysql> insert tb1 values(11111111111);

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

# 删除默认配置，找到 /etc/my.cnf 删除下面的行

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 删除默认配置后，不论插入多大的值，最大只能是 2147483647，最小只能是 -2147483648

mysql> insert tb1 values(2147483647);

Query OK, 1 row affected (0.00 sec)

# 报错，但插入成功，数据表中显示数据为 2147483647

mysql> insert tb1 values(2147483648);

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

mysql> insert tb1 values(-2147483648);

Query OK, 1 row affected (0.00 sec)

# 报错，但插入成功，数据表中显示数据为 -2147483648

mysql> insert tb1 values(-2147483649);

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

# enum 类型

mysql> create table tb2(id int,sex enum('male','female'));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb2 values(1,'male');

Query OK, 1 row affected (0.00 sec)

# 如果插入的数据不符合 enum 类型设置的固定选项，会报错、或者值为空

mysql> insert into tb2 values(1,'other');

ERROR 1265 (01000): Data truncated for column 'sex' at row 1

其他属性（Null，Key，Default，Extra）

NOT NULL： 			# 非空

PRIMARY KEY： 		        # 主键（唯一且非空的）

AUTO_INCREMENT： 	        # 自增（此列必须是：PRIMARY KEY 或者 UNIQUE KEY）

UNIQUE KEY： 		        # 唯一键，单独的唯一的

DEAFULT： 			# 默认值

UNSIGNED： 			# 非负数

COMMENT： 			# 注释

创建数据表（设置更多属性，CREATE）

# 1.建表语句

mysql> create table students

    -> (id int primary key auto_increment comment "学生ID",

    -> name varchar(12) not null comment "学生名字",

    -> age tinyint unsigned not null comment "年龄",

    -> gender enum('M','F') default 'M' comment "性别",

    -> register_time datetime default now() comment "注册时间");

# 2.查看建表语句

mysql> show create table students;

| students | CREATE TABLE `students` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID',

  `name` varchar(12) NOT NULL COMMENT '学生名字',

  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',

  `gender` enum('M','F') DEFAULT 'M' COMMENT '性别',

  `register_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8               |

1 row in set (0.00 sec)

# 3.插入数据

mysql> insert into students values(1,'zzzwqh',18,'M',now());

Query OK, 1 row affected (0.00 sec)

# 因为主键相同无法插入数据

mysql> insert into students values(1,'zzzwqh',18,'M',now());

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

# 正确插入数据方法

mysql> insert into students values('2','zzzwqh',18,'M',now());

Query OK, 1 row affected (0.00 sec)

# 主键已经设置自增没必要自己插入

# 插入数据的正规书写格式

mysql> insert into students(name,age) values('wqh',18);

Query OK, 1 row affected (0.00 sec)

mysql> insert into students(name,age) values('wzh',18);

Query OK, 1 row affected (0.00 sec)

mysql> insert into students(name,age) values('djd',18);

Query OK, 1 row affected (0.00 sec)

mysql> insert into students(name,age,gender) values('tcy',18,'F');

Query OK, 1 row affected (0.00 sec)

mysql> insert into students(name,age) values('gsp',18);

Query OK, 1 row affected (0.00 sec)

mysql> insert into students(name,age) values('cjq',18);

Query OK, 1 row affected (0.00 sec)

mysql> select * from students;

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

| id | name | age | gender | register_time       |

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

|  1 | wqh  |  18 | M      | 2020-07-14 22:01:58 |

|  2 | wzh  |  18 | M      | 2020-07-14 22:02:04 |

|  3 | djd  |  18 | M      | 2020-07-14 22:02:11 |

|  4 | tcy  |  18 | F      | 2020-07-14 22:02:27 |

|  5 | gsp  |  18 | M      | 2020-07-14 22:02:36 |

|  6 | cjq  |  18 | M      | 2020-07-14 22:02:40 |

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

6 rows in set (0.00 sec)

删除数据表（DROP）

# 删除数据表 student

mysql> drop table student;

修改数据表（ALTER）

# 1.新建表

mysql> create table tabletest(daijiadong tinyint);

Query OK, 0 rows affected (0.04 sec

# 2.修改表名

mysql> alter table tabletest rename stu;

Query OK, 0 rows affected (0.01 sec)

# 3.插入新字段

mysql> alter table stu add rengyufeng int;

Query OK, 0 rows affected (0.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| daijiadong | tinyint(4) | YES  |     | NULL    |       |

| rengyufeng | int(11)    | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

# 4.插入多个新字段

mysql> alter table stu add liukong int,add wangzhangxing int;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| daijiadong    | tinyint(4) | YES  |     | NULL    |       |

| rengyufeng    | int(11)    | YES  |     | NULL    |       |

| liukong       | int(11)    | YES  |     | NULL    |       |

| wangzhangxing | int(11)    | YES  |     | NULL    |       |

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

4 rows in set (0.00 sec)

# 5.插入字段到最前面

mysql> alter table stu add kangpeiwen varchar(100) first;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |

| daijiadong    | tinyint(4)   | YES  |     | NULL    |       |

| rengyufeng    | int(11)      | YES  |     | NULL    |       |

| liukong       | int(11)      | YES  |     | NULL    |       |

| wangzhangxing | int(11)      | YES  |     | NULL    |       |

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

5 rows in set (0.01 sec)

# 6.插入字段到指定字段后面

mysql> alter table stu add chenjianqing varchar(100) after daijiadong;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |

| daijiadong    | tinyint(4)   | YES  |     | NULL    |       |

| chenjianqing  | varchar(100) | YES  |     | NULL    |       |

| rengyufeng    | int(11)      | YES  |     | NULL    |       |

| liukong       | int(11)      | YES  |     | NULL    |       |

| wangzhangxing | int(11)      | YES  |     | NULL    |       |

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

6 rows in set (0.00 sec)

# 7.删除指定列

mysql> alter table stu drop daijiadong;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |

| chenjianqing  | varchar(100) | YES  |     | NULL    |       |

| rengyufeng    | int(11)      | YES  |     | NULL    |       |

| liukong       | int(11)      | YES  |     | NULL    |       |

| wangzhangxing | int(11)      | YES  |     | NULL    |       |

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

5 rows in set (0.00 sec)

# 8.修改字段

mysql> alter table stu change rengyufeng congtianqi int;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |

| chenjianqing  | varchar(100) | YES  |     | NULL    |       |

| congtianqi    | int(11)      | YES  |     | NULL    |       |

| liukong       | int(11)      | YES  |     | NULL    |       |

| wangzhangxing | int(11)      | YES  |     | NULL    |       |

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

5 rows in set (0.00 sec)

# 9.修改字段属性

mysql> alter table stu modify congtianqi tinyint;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;

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

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

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

| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |

| chenjianqing  | varchar(100) | YES  |     | NULL    |       |

| congtianqi    | tinyint(4)   | YES  |     | NULL    |       |

| liukong       | int(11)      | YES  |     | NULL    |       |

| wangzhangxing | int(11)      | YES  |     | NULL    |       |

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

5 rows in set (0.01 sec)
上一篇：Java中如何封装自己的类，建立并使用自己的类库？


下一篇：一次delete基表回收DBA权限的危险性操作