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)