MySQL表管理精要-优快云博客

MySQL创建表和管理表

章节概述

创建数据库
创建表
修改表的定义
删除,重命名和清空表

创建数据库

创建一个保存员工信息的数据库

– create database employees;
相关其他命令

– show databases;查看当前所有数据库
– use employees;“使用”一个数据库，使其作为当前数据库

命名规则

• 数据库名不得超过30个字符，变量名限制为29个

• 必须只能包含 A–Z, a–z, 0–9, _共63个字符

• 不能在对象名的字符间留空格

• 必须不能和用户定义的其他对象重名

• 必须保证你的字段没有和保留字、数据库系统或常用

方法冲突

• 保持字段名和类型的一致性,在命名字段并为其指定数

据类型的时候一定要保证一致性。假如数据类型在一

个表里是整数,那在另一个表里可就别变成字符型了

create table语句

必须具备:

caeate table权限

存储空间

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

必须指定:
- 表名
- 列名,数据类型,尺寸

语法

MariaDB [myemployees]> create table dept (
    -> depyno int(2),
    -> dname varchar(14),
    -> loc varchar(13));
Query OK, 0 rows affected (0.05 sec)

查看表结构

MariaDB [myemployees]> desc dept
    -> ;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| depyno | int(2)      | YES  |     | NULL    |       |
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

常用的数据类型

数据类型	作用
int	使用4个字节保存整数数据
char(size)	定长字符数据.若未指定,默认1个字节,最大长度255
varchar(size)	可变长字符数据,根据字符实际长度保存,必须制定长度
float(M,D)	单精度,M=整数位+小数位,D=小数位.D<= M<=255,0<=D<=30,默认M+D<=6.
Double(M,D)	双精度.D<=M<=255,0<=D<=30,默认M+D<=15
Date	日期型数据,格式‘YYYY-MM-DD’
Blob	二进制形式长文本数据,最大可达4G
text	长文本数据,最大可达4G


MariaDB [myemployees]> create table emp (
    -> emp_id int auto_increment,
    -> emp_name char(20),
    -> salary double,
    -> birthday date,
    -> primary key(emp_id));
Query OK, 0 rows affected (0.01 sec)


创建表
CREATE TABLE emp
(#int类型，自增
emp_id INT AUTO_INCREMEN
#最多保存20个中英文字符
emp_name CHAR (20),
#总位数不超过15位
salary DOUBLE,
#日期类型birthday DATE,
#主键PRIMARY KEY (emp_id));


查看表结构
MariaDB [myemployees]> desc emp;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| emp_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| emp_name | char(20) | YES  |     | NULL    |                |
| salary   | double   | YES  |     | NULL    |                |
| birthday | date     | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

使用子查询创建表

使用 AS subquery 选项，将创建表和插入
数据结合起来
```
CREATE TABLE table
[(column, column...)]
AS subquery;
```
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
实例一:赋值表Employees

MariaDB [myemployees]> create table emp1 as select * from employees;
Query OK, 107 rows affected (0.00 sec)
Records: 107  Duplicates: 0  Warnings: 0

示例二:创建的表emp2是空表

MariaDB [myemployees]> create table emp2 as select * from employees where 1  = 2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

示例三:创建一个80好部门的表

MariaDB [myemployees]> create table dept80 as select employee_id,last_name,salary*12 annsal ,hiredate from employees where department_id = 80;
Query OK, 34 rows affected (0.06 sec)
Records: 34  Duplicates: 0  Warnings: 0

MariaDB [myemployees]> desc dept80;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| employee_id | int(6)       | NO   |     | 0       |       |
| last_name   | varchar(25)  | YES  |     | NULL    |       |
| annsal      | double(19,2) | YES  |     | NULL    |       |
| hiredate    | datetime     | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Alter table语句

向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

追加一个新列alter table add

MariaDB [myemployees]> alter table dept80 add job_id varchar(15);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [myemployees]> desc dept80;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| employee_id | int(6)       | NO   |     | 0       |       |
| last_name   | varchar(25)  | YES  |     | NULL    |       |
| annsal      | double(19,2) | YES  |     | NULL    |       |
| hiredate    | datetime     | YES  |     | NULL    |       |
| job_id      | varchar(15)  | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改一个列Alter table tablename modify

修改last_name varchar(30)

MariaDB [myemployees]> alter table dept80 modify last_name varchar(30);
Query OK, 34 rows affected (0.02 sec)              
Records: 34  Duplicates: 0  Warnings: 0

修改annsal的尺寸和默认值

MariaDB [myemployees]> alter table dept80 modify annsal double(9,2) default 1000;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除一个列drop column

删除jop_id这一列

MariaDB [myemployees]> alter table dept80 drop column job_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

重命名一个列

使用 CHANGE old_column new_column dataType子句重命列

MariaDB [myemployees]> alter table dept80 change hiredate hire_date varchar(15);
Query OK, 34 rows affected, 34 warnings (0.02 sec) 
Records: 34  Duplicates: 0  Warnings: 34

删除表

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• DROP TABLE 语句不能回滚

DROP TABLE dept80;Table dropped

清空表

truncate table 语句:

删除表中的所有数据
释放表的存储空间

语法

TRUNCATE TABLE detail_dept;
Table truncated.

TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据,可以回滚
对比：
delete from emp2;
select * from emp2;
rollback;
select * from emp

改变对象的名称(改变表的名字)

执行rename语句改变表,视图的名称

MariaDB [myemployees]> alter table emp2 rename to emp3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [myemployees]> show tables;
+-----------------------+
| Tables_in_myemployees |
+-----------------------+
| departments           |
| dept80                |
| detail_dept           |
| emp                   |
| emp1                  |
| emp3                  |
| employees             |
| jobs                  |
| locations             |
+-----------------------+
9 rows in set (0.00 sec)

MariaDB [myemployees]>