5、DDL-CSDN博客

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> show create database test1;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+

mysql> create database if not exists db1 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

[root@vms10 ~]# cat /data/mysql/db1/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci

1.1、修改数据库

mysql> alter database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

1.2、删除数据库

mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected (0.01 sec)

2、创建表

2.1、直接创建

:41: (root@localhost) [db1]> create table student(
    -> id int unsigned auto_increment primary key,
    -> name varchar(20) not null,
    -> age tinyint unsigned,
    -> gender enum('M','F') default 'M'
    -> )engine=innodb auto_increment=10 default charset=utf8;

:42: (root@localhost) [db1]>  create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

:08: (root@localhost) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type      | Null | Key | Default           | Extra             |
+-------+-----------+------+-----+-------------------+-------------------+
| id    | int       | NO   | PRI | NULL              | auto_increment    |
| date  | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+

:09: (root@localhost) [db1]> insert into testdate (id) values(1);

:09: (root@localhost) [db1]> select  * from testdate;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-04-08 16:09:39 |
+----+---------------------+

2.2、利用现有的表创建（但是会缺失主键等必要的属性）

:09: (root@localhost) [db1]> create table testdate2 select * from testdate;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

:34: (root@localhost) [db1]> select * from testdate2;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-04-08 16:09:39 |
+----+---------------------+

2.3、只复制现存的表结构，不复制数据

:46: (root@localhost) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type      | Null | Key | Default           | Extra             |
+-------+-----------+------+-----+-------------------+-------------------+
| id    | int       | NO   | PRI | NULL              | auto_increment    |
| date  | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
2 rows in set (0.00 sec)

:46: (root@localhost) [db1]> create table testdate3 like testdate;
Query OK, 0 rows affected (0.01 sec)

:46: (root@localhost) [db1]> desc testdate3;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type      | Null | Key | Default           | Extra             |
+-------+-----------+------+-----+-------------------+-------------------+
| id    | int       | NO   | PRI | NULL              | auto_increment    |
| date  | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+

3、查看表

查看支持的存储引擎

mysql> show engines;

查看表

mysql> show tables;

查看表结构

mysql> desc user;

查看表创建命令

mysql> show create table user;

查看表状态

mysql> show table status like 'user'\G;

查看库中所有表状态

mysql> show table status from mysql\G;

4、修改和删除表

删除表

[db1]> drop table if exists testdate2;

修改表

ALTER TABLE 'tbl_name'

# 字段：

# 添加字段： add

ADD col1 data_type [ FIRST | AFTER col_name]

# 删除字段： drop

# 修改字段：

alter （默认值） , change （字段名） , modify （字段属性）

# 重命名
ALTER TABLE students RENAME s1;

# 添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;

# 修改字段类型
ALTER TABLE s1 MODIFY phone int;

# 修改字段名称
ALTER TABLE s1 CHANGE phone mobile char(11);

# 删除字段
ALTER TABLE s1 DROP COLUMN mobile;

# 设置字符集
ALTER TABLE s1 character set utf8;

#新建表无主键，添加和删除主键
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;