SQL自学，mysql从入门到精通 --- 第 10天，表和库的操作

Mr.L-OAM

已于 2023-09-12 09:58:40 修改

阅读量91

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库

于 2023-09-08 16:22:11 首次发布

本文链接：https://blog.csdn.net/weixin_46153337/article/details/132762924

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

建库

-- 创建一个叫 d2 的数据库
root@mysqldb 15:44:  [(none)]> CREATE DATABASE d2;
Query OK, 1 row affected (0.01 sec)

-- 切换数据库
root@mysqldb 15:45:  [(none)]> USE d2;
Database changed
root@mysqldb 15:45:  [d2]>

建表

root@mysqldb 15:50:  [d2]> CREATE TABLE t1 (  
    -> name VARCHAR(30) NOT NULL,  
    -> amount INT,  
    -> ACCOUNT_ID INT NOT NULL  
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 创建表指定主键
root@mysqldb 15:50:  [d2]> CREATE TABLE t2 (
    -> name VARCHAR(25) NOT NULL,
    -> age VARCHAR(3) NULL DEFAULT NULL,
    -> job VARCHAR(30) NULL,
    -> PRIMARY KEY (name) USING BTREE
    -> )
    -> ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Query OK, 0 rows affected (0.01 sec)

-- 利用已有的表创建新表
root@mysqldb 15:58:  [d2]> CREATE TABLE new_t1 LIKE t1;
Query OK, 0 rows affected (0.11 sec)

ALTER TABLE

-- 查看表结构
root@mysqldb 15:58:  [d2]> DESC t1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(30) | NO   |     | NULL    |       |
| amount     | int(11)     | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)     | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 修改表结构，将name 字段改成VARCHAR(40)
root@mysqldb 16:02:  [d2]> ALTER TABLE t1 CHANGE name name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 16:03:  [d2]> DESC t1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(40) | YES  |     | NULL    |       |
| amount     | int(11)     | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)     | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 写法2
root@mysqldb 16:06:  [d2]> ALTER TABLE t1 MODIFY name varchar(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 添加字段在第一列
root@mysqldb 16:06:  [d2]> ALTER TABLE t1 ADD COLUMN id INT FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 16:08:  [d2]> DESC t1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| amount     | int(11)     | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)     | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 添加字段在指定列
root@mysqldb 16:08:  [d2]> ALTER TABLE t1 ADD COLUMN address VARCHAR(100) AFTER name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


root@mysqldb 16:09:  [d2]> DESC t1;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| address    | varchar(100) | YES  |     | NULL    |       |
| amount     | int(11)      | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)      | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 默然则添加的字段位置是在最后
root@mysqldb 16:10:  [d2]> ALTER TABLE t1 ADD COLUMN work VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 16:11:  [d2]> DESC t1;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| address    | varchar(100) | YES  |     | NULL    |       |
| amount     | int(11)      | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)      | NO   |     | NULL    |       |
| work       | varchar(20)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-- 修改字段名
root@mysqldb 16:11:  [d2]> ALTER TABLE t1 CHANGE work phone varchar(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 16:13:  [d2]> DESC t1;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| address    | varchar(100) | YES  |     | NULL    |       |
| amount     | int(11)      | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)      | NO   |     | NULL    |       |
| phone      | varchar(11)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-- DROP 删除字段
root@mysqldb 09:26:  [d2]> ALTER TABLE d2.t1 DROP amount, DROP phone;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 09:26:  [d2]> DESC t1;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| address    | varchar(100) | YES  |     | NULL    |       |
| ACCOUNT_ID | int(11)      | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

RENAME

root@mysqldb 09:55:  [d2]> ALTER TABLE t1 RENAME TT;
Query OK, 0 rows affected (0.01 sec)

DROP TABLE

root@mysqldb 09:56:  [d2]> SHOW TABLES;
+--------------+
| Tables_in_d2 |
+--------------+
| TT           |
| new_t1       |
| t2           |
+--------------+
3 rows in set (0.00 sec)

-- 删除 t2 表
root@mysqldb 16:14:  [d2]> DROP TABLE t2;
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 09:57:  [d2]> SHOW TABLES;
+--------------+
| Tables_in_d2 |
+--------------+
| TT           |
| new_t1       |
+--------------+
2 rows in set (0.00 sec)

-- 删除表时，最好指定库名和表面，防止删除错误
root@mysqldb 16:14:  [d2]> DROP TABLE d2.new_t1;
Query OK, 0 rows affected (0.01 sec)

DROP DATABASE 语句

-- 查看数据库
root@mysqldb 16:16:  [d2]> SHOW DATABASES;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| d1                       |
| d2                       |
| mysql                    |
| performance_schema       |
| sql_self_learning_skills |
| sys                      |
+--------------------------+
7 rows in set (0.33 sec)

-- 删除数据库
root@mysqldb 16:17:  [d2]> DROP DATABASE d2;
Query OK, 1 row affected (0.17 sec)