mysql DDL
在MySQL中,数据库是用于存储和操作诸如表,数据库视图,触发器,存储过程等数据的对象的集合。
创建数据库
mysql> CREATE DATABASE [IF NOT EXISTS] database_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE语句的后面是要创建的数据库名称。建议数据库名称尽可能是有意义和具有一定的描述性。
IF NOT NULL是语句的可选子句。可防止创建数据库服务器中已存在数据库的错误。不能在MySQL数据库服务器中具有相同名称的数据库。
mysql> SHOW DATABASES; 显示MySQL数据库服务器中的所有数据库。
mysql> USE database_name; 切换数据库。
删除数据库
mysql> DROP DATABASE [IF EXISTS] database_name;
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name( column_list )ENGINE=table_type DEFAULT CHARSET=utf8;
存储引擎ENGINE就是指表的类型,数据库存储引擎决定了表在计算机的存储方式。如果不明确声明存储引擎,MySQL将默认使用InnoDB。
InnoDB是MySQL的一种存储引擎,InnoDB给MySQL提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。InnoDB是MySQL上第一个提供外键约束的表引擎,而且对事务处理的能力,也是其他存储引擎不能比拟的。不过这种引擎的缺点就是读写效率稍差,占用的数据空间相对比较大。
要在CREATE TABLE
语句中为表定义列,使用以下语法:
column_name data_type[size] [完整约束条件]
column_name
指定列的名称。每列具有特定数据类型和大小,例如:VARCHAR(255)
。-
NOT NULL
或NULL
表示该列是否接受NULL
值。 -
DEFAULT value
用于指定列的默认值。 -
AUTO_INCREMENT
指示每当将新行插入到表中时,列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT
列。
约束条件 | 说明 |
PRIMARY KEY | 标识该属性为表的主键 |
FOREIGN KEY | 标识该属性为表的外键,是与之关联某表的主键 |
NOT NULL | 标识该属性不能为空 |
UNIQUE | 标识该属性的值是唯一的 |
AUTO_INCREMENT | 标识该属性的值是自动增加 |
DEFAULT | 为该属性设置默认值 |
如果要将表的特定列设置为主键,则使用以下语法:
PRIMARY KEY (col1,col2,...)
设置表的外键:
CONSTRAINT 外键别名 FOREIGN KEY(字段名) REFERENCES 表名(字段名)
举个栗子:
CREATE TABLE teacher( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, teac_id int UNIQUE, stu_id int, name varchar(20) DEFAULT NULL, CONSTRAINT STUID FOREIGN KEY(stu_id) REFERENCES student(id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> SHOW TABLES; 查看某个数据库中的所有表
mysql> DESCRIBE table_name; 查看数据表结构
mysql> SHOW CREATE TABLE table_name; 查看表的详细定义,字段名、字段的数据类型、约束条件、存储引擎和字符编码
修改数据表
1)修改表名
表名可以在一个数据库中唯一的确定一张表。
mysql> ALTER TABLE 旧表名 RENAME 新表名;
2)修改字段名
mysql> ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
3)修改字段的数据类型
mysql> ALTER TABLE 表名 MODIFY 字段名 数据类型;
4)增加字段
mysql> ALTER TABLE 表名 ADD 字段名1 数据类型 [完整性约束条件] [FIRST | AFTER 字段名2];
其中,字段名1是指需要增加的字段的名称;
FIRST是可选参数,其作用是将新增字段设置为表的第一个字段;
AFTER也是可选的参数,其作用是将新增字段添加到表中已有的字段名2后面。
5)删除字段
mysql> ALTER TABLE 表名 DROP 字段名;
6)更改表的存储引擎
mysql> ALTER TABLE 表名 ENGINE = 存储引擎名;
7)删除表的外键约束
mysql> ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
删除数据表
mysql> DROP TABLE table_1;
drop、delete 和 truncate区别:
drop语句删除表的结构;
delete语句每次删除表的一行数据,操作会记录在bin-log日志,可以事务回滚;
truncate语句一次性删除表的所有数据,速度比delete快,但操作不记录日志,不可以回滚。
SELECT CONCAT('DROP TABLE ', table_name, ';' )
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME LIKE '模糊表名%';