一、库的基本操作
-- 库的创建 CREATE DATABASE [IF NOT EXISTS] 库名; -- 库的修改 RENAME DATABASE 旧库名 TO 新库名; -- 更改库的字符集 ALTER DATABASE 库名 CHARACTER SET GBK; -- 库的删除 DROP DATABASE IF EXISTS 库名;
二、表的基本操作
2.1、
CREATE TABLE 表名（ 字段1 数据类型 [列级别的约束条件] [默认值] COMMENT ‘别名’, 字段2 数据类型 [列级别的约束条件] [默认值] COMMENT ‘别名’, 字段3 数据类型 [列级别的约束条件] [默认值] COMMENT ‘别名’, . [表级别约束条件] ）ENGINE='engine_name',CHARSET=’字符集’;
2.2、使用主键
-- 方法一 CREATE TABLE person( P_ID INT PRIMARY KEY )ENGINE='InnerDB',CHARSET='utf8'; -- 方法二 CREATE TABLE person( P_ID INT , NAME VARCHAR(20), PRIMARY KEY(P_ID) )ENGINE='InnoDB',CHARSET='utf8'; -- 方法三 多字段联合主键 CREATE TABLE person( P_ID INT , P_NAME VARCHAR(20), PRIMARY KEY(P_ID,P_NAME) )ENGINE='InnerDB',CHARSET='utf8';
2.3、使用外键
外键是用来在两个表之间创建关联的，可以是一列，或者多列，一个表可以有一个或者多个外键。一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除另一个表中具有关联关系的行。
-- 部门表 CREATE TABLE dept( D_ID BIGINT PRIMARY KEY AUTO_INCREMENT, D_NAME VARCHAR(20) NOT NULL )ENGINE='InnerDB',CHARSET='utf8' -- 员工表 CREATE TABLE emp( E_ID INT(11) PRIMARY KEY, E_NAME VARCHAR(25), deptid BIGINT, salary FLOAT, CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES dept(D_ID) );
-- 语法 CONSTRAINT 外键名 FOREIGN KEY 外键字段名1 [,字段名2，。。。。] REFERENCES 主表名 主键列1 [主键列2,.....]
2.4、使用非空约束
-- NOT NULL CREATE TABLE IF NOT EXISTS f(F_ID INT(11),F_NAME VARCHAR(20) NOT NULL,PRIMARY KEY(F_ID))ENGINE=INNODB CHARSET=utf8;
2.5、使用唯一性约束
-- UNIQUE CREATE TABLE IF NOT EXISTS e(E_ID INT(11),E_NAME VARCHAR(20) UNIQUE,PRIMARY KEY(e_id))ENGINE=INNODB CHARSET=utf8;
2.6、使用默认约束
如果插入一条新纪录时没有为这个字段赋值，那么系统就会自动为这个字段赋值
CREATE TABLE IF NOT EXISTS e( E_ID INT(11), E_NAME VARCHAR(20) DEFAULT 'jdy' ) ENGINE=INNODB CHARSET=utf8;
-- AUTO_INCREMENT,注意使用自增时，该列的数据必须具有唯一性 CREATE TABLE IF NOT EXISTS e( E_ID INT(11) UNIQUE AUTO_INCREMENT, E_NAME VARCHAR(20) DEFAULT 'jdy' ) ENGINE=INNODB CHARSET=utf8;
--ALTER TABLE <旧表名> RENAME [TO] 新表明 -- 500万条数据，修改表名0.03秒 ALTER TABLE e1 RENAME TO e2;
-- ALTER TABLE <表名> MODIFY <字段名> <数据类型> ALTER TABLE e MODIFY e_name VARCHAR (30) ;
-
-
如果将VARCHAR（20）改成VARCHAR(25)范围扩大，500万条数据0.3秒
-
Int(10)-->int(20) 0.01秒
-
Int(20)-->int(10) 0.3秒
-
VARCHAR-->INT 26秒
-
3.3、修改字段名
-- ALTER TABLE <表名> CHANGE <旧字段名><新字段名><新数据类型> ALTER TABLE e CHANGE e_name e_name1 VARCHAR(22);
如果只修字段名称不修改字段数据类型，0.05秒就可以（500万条数据）。
3.4、添加字段
# ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名] ALTER TABLE e ADD e_address VARCHAR(12) NOT NULL AFTER e_name;
500万条数据 11秒
3.5、删除字段
-- ALTER TABLE <表名> DORP <字段名> ALTER TABLE e DROP e_address;
3.6、修改字段的排列位置
ALTER TABLE <表名> MODIFY <字段1><数据类型> FIRST | AFTER <字段2>；
500万条数据 10秒
3.7、更改表的存储引擎
-- 查看当前表的存储引擎： SHOW CREATE TABLE table_name; -- 更改引擎 ALTER TABLE <表名> ENGINE = <更改后的存储引擎>
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
-- 若本身存在默认值，则先删除 ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT; -- 若本身不存在则可以直接设定 ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT 默认值;
四、删除表
删除没有被关联的表
DROP TABLE [IF EXISTS] 表1，表2，表3.....
删除被其他表关联的主表。关联表的情况下，如果直接删除父表，会失败，是因为直接删除，将会破坏表的参照完整性。 如果必须删除，可以删除出字表，再删除父表。如果想单独删除父表。只需要将关联表的外键约束取消，就可删除父表
1.删除字表中的外键列
2.删除父表
五、表的复制
-- 仅仅复制表的结构 -- create table 新表明 like 旧表明 CREATE TABLE new_e LIKE e -- 复制表的结构+数据 -- create table 新表明 like 旧表明 select语句 CREATE TABLE new_e LIKE e SELECT * FROM
六、表的事物操作
-
插入数据
INSERT INTO table_name (column_list) VALUES (values_list);
-
同时插入语多条记录
INSERT INTO table_name(column_list) VALUES (values_list1),(values_list2),(values_list3)....;
-
将查询结果插入列表中
INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHERE (CONDITION)
-
更新数据
UPDATE table_name SET column_name1=value1,column_name2=value2.....where (CONDITION);
-
删除数据
DELETE FROM table_name [WHERE <CONDITION>];
删除表中的所有记录还可以使用TRUNCATE TABLE语句，truncate将直接删除原来的表，并重新创建一个表，其语法是truncate table table_name.truncate直接删除的是表，而不是记录，因此执行速度比delete快。