SQL(structure query language): 结构化查询语句数据库(database): 保存有组织的数据的容器（通常是一个文件或一组文件）MySQL、Oracle等数据库软件，称为数据库管理系统(DBMS)

目录

数据库、表的相关操作容量查询数据库相关操作表的相关操作 数据的查询、插入、删除等操作数据类型

数据库、表的相关操作

容量查询

# 引擎的容量 SELECT ENGINE, SUM(TABLE_ROWS) AS '记录数', SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)', SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)' FROM information_schema.TABLES GROUP BY ENGINE; # schema的容量 SELECT TABLE_SCHEMA AS '数据库', SUM(TABLE_ROWS) AS '记录数', SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)', SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)' FROM information_schema.TABLES GROUP BY TABLE_SCHEMA ORDER BY SUM(DATA_LENGTH) desc, sum(INDEX_LENGTH) desc; # 每张表的容量 SELECT TABLE_SCHEMA AS '数据库', TABLE_NAME AS '表名', SUM(TABLE_ROWS) AS '记录数', SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)', SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)' FROM information_schema.TABLES GROUP BY TABLE_SCHEMA, TABLE_NAME ORDER BY SUM(DATA_LENGTH) desc, sum(INDEX_LENGTH) desc;

数据库相关操作

创建、查看、使用、删除数据库：

-- 1.创建数据库 CREATE DATABASE school; -- 2.查询数据库 SHOW DATABASES; -- 3.使用数据库 USE school; -- 4.删除数据库 DROP DATABASE school;

表的相关操作

创建、查看、删除表 -- 1.创建表 CREATE TABLE t_class ( classno INT, cname VARCHAR(20), loc VARCHAR(40), stucount INT ); -- 2.查看表结构 DESC t_class; -- 3. 查看表的详细定义: SHOW CREATE TABLE t_class; -- 4.删除表 DROP TABLE t_class; 复制表 -- 1.创建和t_class具有相同表结构的表t_class_copy CREATE TABLE t_class_copy LIKE t_class; -- 2.将t_class中的数据全部插入t_class_copy INSERT INTO t_class_copy SELECT * FROM t_class; 修改表 -- 改变表名 ALTER TABLE t_class1 RENAME t_class; -- 在结尾加入字段 ALTER TABLE t_class ADD head_teacher_id INT; -- 在开头加入字段 ALTER TABLE t_class ADD advisor VARCHAR(20) FIRST; -- 在指定位置加入字段 ALTER TABLE t_class ADD advisor_2 VARCHAR(20) AFTER cname; -- 删除字段 ALTER TABLE t_class DROP advisor_2; -- 字段改数据类型 ALTER TABLE t_class MODIFY loc VARCHAR(50); -- 一次性修改字段名字，类型，位置(不想修改的字段设为与原字段相同的参数就可以) ALTER TABLE t_class CHANGE advisor advisor_modify VARCHAR(100) AFTER loc; 为表添加约束 -- 给字段添加NN(非空)约束 ALTER TABLE t_class MODIFY stucount INT NOT NULL DEFAULT 0; -- 给字段添加UK（唯一）约束 ALTER TABLE t_class MODIFY cname VARCHAR(20) UNIQUE; -- 添加pk(主键)约束 ALTER TABLE t_student_pk ADD CONSTRAINT pk_stuno PRIMARY KEY (stuno); -- 删除PK约束 ALTER TABLE t_student_pk DROP PRIMARY KEY; -- 添加联合主键（如果表已经存在主键，则需要删除原来的主键） ALTER TABLE t_student_pk ADD CONSTRAINT pk_stuno_sname PRIMARY KEY(stuno, sname); -- 设置主键约束，并且设置为自增字段 CREATE TABLE t_class ( class_id INT(11) PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20), loc VARCHAR(40), stucount INT(11) ); -- 为t_student添加class_id字段，并设置为FK约束 ALTER TABLE t_student ADD class_id INT, ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES t_class(class_id); 添加索引 -- 为已有的表添加索引（方法1） ALTER TABLE t_class ADD INDEX index_classno (classno); -- 为已有的表添加索引（方法2） CREATE INDEX index_classno ON t_class(classno); -- 删除索引 ALTER TABLE t_class DROP INDEX index_classno; -- 添加唯一索引 Unique index ALTER TABLE t_class ADD UNIQUE INDEX index_classno (classno); -- 添加全文索引 Fulltext index ALTER TABLE t_class ADD FULLTEXT INDEX index_loc (loc); -- 添加复合索引 ALTER TABLE t_class ADD INDEX index_cname_loc (cname, loc); -- explain 查询效率（由于存在符合索引index_cname_loc，所以第一段的查询效率高于第二段） EXPLAIN SELECT * FROM t_class WHERE cname = 'beijing' AND loc='hah'; EXPLAIN SELECT * FROM t_class WHERE advisor = 'adv' AND loc = 'haha';

小结：

PRI：是primary的缩写，标记这一列为主键，用来唯一表中每一行数据的索引。数据库层面的唯一性（通常每个表都有id字段，用来标识一行数据）。UNI：是unique的缩写，顾名思义就是唯一的意思。业务层面的唯一性（比如支付记录的ID，不可能有相同的两个吧！）。MUL：是multiple的缩写，表示这一列是被设置为一个普通索引。之所以叫做multiple，是因为此时可能这一列单独作为索引，也可能这一列和其他标记为MUL的列共同构成了一个索引(这种由多列共同构成的索引被叫作复合索引)。索引用来排序数据以加快搜索和排序数据的速度，比如书的目录就是书的索引。外键约束显示的也是MUL，添加外键约束，就等于认了个“爸爸”，“爸爸”中的字段不能随意删除了。

数据的查询、插入、删除等操作

数据类型

整数类型 类型字节数TINYINT1SMALLINT2MEDIUMINT3INT4INTEGER4BIGINT8 浮点数类型和定点数类型 类型字节数FLOAT4DOUBLE8DECIMAL(M, D) 或 DEC(M, D)M+2

注意：DECIMAL存储的是字符串，精度高，在金融系统中表示货币金额的时候会优先考虑DECIMAL类型，在一般的价格体系中，比如购物平台中货品的标价，选择FLOAT类型就可以。

日期和时间类型 类型字节数YEAR1TIME3DATE4DATETIME8TIMESTAMP4

注意：

YEAR类型只表示年份，如果字需要记录年份，选择YEAR类型可以节约空间。TIME类型只表示时间，如果只需要记录时间，选择TIME类型最合适DATE类型只表示日期DATETIME和TIMESTAMP都可以记录日期和时间，DATETIME类型表示的范围比TIMESTAMP大。TIMESTAMP类型的时间是根据时区来显示的，如果需要显示的时间与时区对应，就选择TIMESTAMP类型。

示例：

-- 数据类型-日期与时间 CREATE TABLE dt_example ( e_date DATE, -- 4 Byte e_time TIME, -- 3 Byte e_datetime DATETIME, -- 8 Byte e_timestamp TIMESTAMP, -- 4 Byte e_year YEAR -- 1 Byte ); INSERT INTO dt_example VALUES( current_date(), current_time(), NOW(), current_timestamp(), year(NOW()) ); SELECT * FROM dt_example; 字符串类型 类型字节数CHAR比如CHAR(20)就是指定CAHR类型的长度为20VARCHAR0 ~ 65535的任意值，比如VARCHAR(100)的具体分配的空间在0 ~ 100之间变化TINYTEXT0 ~ 255TEXT0 ~ 65535MEDIUMTEXT0 ~ 16772150LONGTEXT0 ~ 4294967295ENUMSET 二进制数据类型 类型取值范围BINARY(M)字节数为M，允许长度为0 ~ M的定长二进制字符串VARBINARY(M)3DATE4DATETIME8TIMESTAMP4