SQL DDL 操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_51022853/article/details/127751566

SQL

DDL——数据库操作

1、查询

查询所有数据库

SHOW DATABASES;(空格可一个可多个)

--查询所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.05 sec)

查询当前数据库

SELECT DATABASE();

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

2、创建

CREATE DATABASE [IF NOT EXISTS] [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

[IF NOT EXISTS]：如果数据库不存在则创建，如果存在则不执行任何操作。

[DEFAULT CHARSET 字符集]：如：[DEFAULT CHARSET UTF8]

UTF8字符集存储长度8个字节，不建议使用，UTF8MB44个字节。

mysql> create database itcast;
Query OK, 1 row affected (0.04 sec)

再次查询数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

再新建itcast数据库

mysql> create database itcast;
ERROR 1007 (HY000): Can't create database 'itcast';

database exists已经存在itcast数据库，所以报错

mysql> create database if not exists itcast;
Query OK, 1 row affected, 1 warning (0.03 sec)

加 if not exists，如果不存在则创建，如果存在则不执行任何操作

mysql> create database itheima default charset utf8mb4;
Query OK, 1 row affected (0.03 sec)

创建 itheima数据库，UTF8字符集。

3、删除

DROP DATABASE [IF EXISTS] 数据库名；

mysql> drop database test;
Query OK, 0 rows affected (0.06 sec)

4、使用

USE 数据库名;

mysql> use itcast;
Database changed

DDL——表操作——查询

1、查询当前数据库所有表

SHOW TABLES;

mysql> show tables;
Empty set (0.05 sec)

2、查询表结构

DESE 表名;

3、查询指定表的建表语句

SHOW CRATE TABLE 表名;

DDL——表操作——创建

CREATE TABLE 表名(

字段1 字段1类型 [COMMENT 字段1注释],

字段2 字段2类型 [COMMENT 字段2注释],

字段3 字段3类型 [COMMENT 字段3注释],

……

字段n 字段n类型 [COMMENT 字段n注释]

)[COMMENT 表注释]；

注意：最后一个字段结尾时无逗号。

--查询当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

--查询当前数据库所有表
mysql> show tables;
Empty set (0.00 sec)

--创建表
mysql> create table tb_user(
    -> id int comment '编号',
    -> name varchar(50) comment '姓名',
    -> age int comment'年龄',
    -> gender varchar(1) comment '性别'
    -> ) comment'用户表';
Query OK, 0 rows affected (0.07 sec)

--查询当前数据库所有表
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_user          |
+------------------+
1 row in set (0.00 sec)

--查询表结构
mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

--查询指定表的建表语句
mysql> show create table tb_user;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'            |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
--ENGINE=InnoDB是存储引擎            
--DEFAULT CHARSET=utf8mb4默认字符集      
--COLLATE=utf8mb4_0900_ai_ci默认排序规则

DDL——表操作——数据类型

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

mysql> create table emp(
    -> id int comment '编号',
    -> workno varchar(10) comment '工号',
    -> name varchar(10) comment '姓名',
    -> gender char(1) comment '性别',
    -> age tinyint unsigned comment '年龄',
    -> idcard char(18) comment '身份证号',
    -> entrydate date comment '入职时间'
    -> ) comment '员工表';
Query OK, 0 rows affected (0.02 sec)

查询表结构

mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

DDL——表操作——修改

1、添加字段

ALTER TABLE 表名 ADD 字段名类型（长度）[COMMENT 注释][约束];

案例：为emp表增加一个新的字段“昵称”为nickname,类型为varchar(20)

mysql> alter table emp add nickname varchar(20) comment'昵称';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
| nickname  | varchar(20)      | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

2、修改

修改数据类型

ALTER TABLE 表名 MODIFY 字段名新数据类型（长度）；

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名新字段名类型（长度） [COMMENT 注释] [约束]；

案例：将emp表的nickname字段修改为username,类型为varchar(30)

mysql> alter table emp change nickname username varchar(30) comment'用户名';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
| username  | varchar(30)      | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3、删除字段

ALTER TABLE 表名 DROP 字段名；

案例：将emp表