MySQL关于库和表的增删查改命令

DDL 库的增删查改

-- 创建自己的数据库
create database 库名;
create database ee_2110;
+--------------------+
| Database           |
+--------------------+
| ee_2110            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 查询当前所有的数据库名称;
show databases;
+--------------------+
| Database           |
+--------------------+
| ee_2110            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 删除库
drop database 库名;
drop database ee_2100;
+--------------------+
| Database           |
+--------------------+
| ee_2110            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

 -- 查询创建数据库的默认的字符集格式
 show create database ee_2110;

DDL之表的增删查改

-- 创建表的前提,先选择库,进入到库中
mysql> use ee_2110;
Database changed
mysql>

-- 查询这个库中的所有的表
mysql> show  tables;
Empty set (0.00 sec) -- 为空
mysql>

-- 创建了一个表
	创建表的语法
		create table 表名(
			字段名称1 字段类型1,
			字段名称2 字段类型2,
			字段名称3 字段类型3
			... 
		) ;
-- 创建了一个学生表
mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> gender varchar(8),
    -> address varchar(50),
    -> email varchar(50)
    -> );
Query OK, 0 rows affected (0.04 sec)


-- 查询表的结构
desc 表名;
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| gender  | varchar(8)  | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| email   | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

-- 修改表的字段名称
alter table 表名  change 旧字段名称 新字段名称 数据类型;
mysql> alter table student change  gender sex varchar(8);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | varchar(8)  | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| email   | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 修改字段类型
alter table 表名 modify 字段名称 新的字段类型;
mysql> alter table student modify address varchar(100);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| sex     | varchar(8)   | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| email   | varchar(50)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)


-- 添加一个新的列(新的字段)
alter table 表名 add 新的字段名称 类型;
mysql> alter table student add socre double(3,1);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| sex     | varchar(8)   | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| email   | varchar(50)  | YES  |     | NULL    |       |
| socre   | double(3,1)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)


-- 修改表:将某一个字段删除
alter table 表名 drop 字段名称;
mysql> alter table student drop email;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| sex     | varchar(8)   | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| socre   | double(3,1)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)


-- 复制一张新的表跟以前的表结构相同
create table 新的表名 like 以前的表名;
mysql> create table teacher like student;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> desc teacher;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| sex     | varchar(8)   | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| socre   | double(3,1)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)


-- 修改的表的名称
alter table 以前的表名 rename to 新的表名;
mysql> alter table teacher rename to tea;
Query OK, 0 rows affected (0.03 sec)


-- 删除表
drop table 表名 ;
drop table if exists 表名;
mysql> drop table tea;
Query OK, 0 rows affected (0.04 sec)