Python社区  »  DATABASE

MySQL基础02-数据库定义(DDL语句)

四月不见 • 3 月前 • 134 次点击  

一、简介

DDL(Data Definition Language)数据定义语言。
用来定义数据库对象: 数据库 等。关键词: create drop alter

二、DDL操作数据库

1、创建数据库

创建语法: create database [if not exists] 数据库名;
创建数据库并指定字符集(字符编码): create database 数据库名 character set 字符集;

创建一个数据库,如:

mysql> create database db1 character set utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

2、查看某个数据库的定义信息(创建语句):

语法: show create database 数据库名;

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

mysql> show create database db1 \G
*************************** 1. row ***************************
       Database: db1
Create Database: CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

3、修改数据库

修改数据库字符集格式: alter database 数据库名 default character set 字符集;

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

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

4、删除数据库

语法: drop database 数据库名;

mysql> drop database db3;
Query OK, 0 rows affected (0.02 sec)

要删除的数据库不存在时:

mysql> drop database db3;
ERROR 1008 (HY000): Can't drop database 'db3'; database doesn't exist
mysql> drop database if exists db3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

5、使用/切换数据库

查看正在使用的数据库: select database();
切换/使用数据库: use 数据库名;

mysql> use db1;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

三、DDL操作数据表

1、创建表

语法: create table 表名 ([字段名 [属性]...,]...);

mysql> create table user (
    -> id int,
    -> name char
    -> );
Query OK, 0 rows affected (0.04 sec)

创建一个结构相同的表: create table 新表名 like 旧表名;

mysql> create table user1 like user;
Query OK, 0 rows affected (0.02 sec)

2、查看表

查看某个数据库中的所有表: show tables;

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user          |
| user1         |
+---------------+
2 rows in set (0.00 sec)

查看表结构: desc 表名;

mysql> desc user;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | YES  |     | NULL    |       |
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

查看表的创建语句: show create table 表名;

mysql> show create table user1 \G
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3、修改表

修改表名: rename table 表名 to 新表名;

mysql> rename table user to user1;
Query OK, 0 rows affected (0.02 sec)

修改表的字符集: alter table 表名 character set 字符集;

mysql> alter table user1 character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user1 \G
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int DEFAULT NULL,
  `name` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

4、删除表

语法: drop table [if exists] 表名;

mysql> drop table user1;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user          |
+---------------+
1 row in set (0.01 sec)

四、DDL操作数据列(字段)

1、添加表列

语法: alter table 表名 add 列名 [属性]... [after 插入位置];

mysql> alter table user1 add age int;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc user1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | YES  |     | NULL    |       |
| name  | char(1) | YES  |     | NULL    |       |
| age   | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、修改表列

修改列类型: alter table 表名 modify 列名 [新属性];




    
mysql> alter table user1 modify name varchar(8);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int        | YES  |     | NULL    |       |
| name  | varchar(8) | YES  |     | NULL    |       |
| age   | int        | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

修改列名: alter table 表名 change 旧列名 新列名 [新属性];

mysql> alter table user1 change age phone varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(8)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

3、删除列

语法: alter table 表名 drop 列名;

mysql> alter table user1 drop phone;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int        | YES  |     | NULL    |       |
| name  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

五、参考

8.0官方文档: https://dev.mysql.com/doc/refman/8.0/en/

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/126158
 
134 次点击  
分享到微博