SQL-DDL操作

数据库操作

登录MySQL 

PS D:\WorkSpace\MachineLearning\DL_learning> mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

显示所有数据库 

mysql> show databases;
+--------------------+  
| Database           |  
+--------------------+  
| atguigudb          |  
| dbtest14           |  
| information_schema |  
| mysql              |  
| performance_schema |  
| sys                |  
+--------------------+  
6 rows in set (0.00 sec)

创建数据库 

mysql> create database itcast;
Query OK, 1 row affected (0.11 sec)mysql> create database if not exists test1;
Query OK, 1 row affected (0.10 sec)mysql> create database test2 default charset utf8mb4;
Query OK, 1 row affected (0.08 sec)

删除数据库 

mysql> drop database itcast;
Query OK, 0 rows affected (0.15 sec)mysql> drop database if exists  test2;
Query OK, 0 rows affected (0.08 sec)

进入某个数据库进行查询 操作

mysql> use test1;
Database changed

查看当前处于哪个数据库 

mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

表结构

 查询所有表

mysql>  use atguigudb;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries           |
| departments         |
| emp_details_view    |
| employees           |
| job_grades          |
| job_history         |
| jobs                |
| locations           |
| order               |
| regions             |
+---------------------+
10 rows in set (0.00 sec)

查询表的结构

创建一张表，；分号表示语句结构，enter才可以执行

mysql> create table tb_user(-> id int,-> name varchar(50),-> age int,-> gender varchar(1)-> );
Query OK, 0 rows affected (0.95 sec)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| 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.00 sec)

 查询表的结构（SQL语言）

mysql> show create table tb_user;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table|
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (`id` int DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`age` int DEFAULT NULL,`gender` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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.39 sec)

添加字段

mysql> alter table emp add nickname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> describe 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)

修改某列的列名和列的数据类型

把nickname改为username

mysql> alter table emp change nickname username varchar(30) comment '用户名';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> describe 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)

修改表名 

把表名：emp改为employee

mysql> alter table emp rename to employee;
Query OK, 0 rows affected (0.50 sec)mysql> describe emp;  
ERROR 1146 (42S02): Table 'test1.emp' doesn't exist
mysql> describe employee;
+-----------+------------------+------+-----+---------+-------+
| 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)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| employee        |
| tb_user         |
+-----------------+
2 rows in set (0.00 sec)

 修改表的字段类型

把username 的类型从varchar(30) 改为 char(28)

mysql> alter table employee modify column username char(28);
Query OK, 0 rows affected (2.45 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> describe employee;
+-----------+------------------+------+-----+---------+-------+
| 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  | char(28)         | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

删除某一列

删除username那一列

mysql> alter table employee  drop username;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> describe employee;
+-----------+------------------+------+-----+---------+-------+
| 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.00 sec)

删除表

删除表

mysql> drop tables if exists tb_user;
Query OK, 0 rows affected (0.22 sec)mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| employee        |
+-----------------+
1 row in set (0.00 sec)

 删除表，重新创建表

 表的数据没了。新表和旧的表的结构不变。（数据没了，结构还在）

mysql> truncate table employee;
Query OK, 0 rows affected (0.93 sec)mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| employee        |
+-----------------+
1 row in set (0.00 sec)mysql> describe employee;
+-----------+------------------+------+-----+---------+-------+
| 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)