MySql数据库基本操作

• 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. 创建、查看、删除表

-- 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;

2. 复制表

-- 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;

3. 修改表

-- 改变表名
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;

4. 为表添加约束

-- 给字段添加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);

5. 添加索引

-- 为已有的表添加索引（方法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，添加外键约束，就等于认了个“爸爸”，“爸爸”中的字段不能随意删除了。

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

数据类型

1. 整数类型

类型	字节数
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
INTEGER	4
BIGINT	8

2. 浮点数类型和定点数类型

类型	字节数
FLOAT	4
DOUBLE	8
DECIMAL(M, D) 或 DEC(M, D)	M+2

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

3. 日期和时间类型

类型	字节数
YEAR	1
TIME	3
DATE	4
DATETIME	8
TIMESTAMP	4

注意：

• 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;

4. 字符串类型

类型	字节数
CHAR	比如CHAR(20)就是指定CAHR类型的长度为20
VARCHAR	0 ~ 65535的任意值，比如VARCHAR(100)的具体分配的空间在0 ~ 100之间变化
TINYTEXT	0 ~ 255
TEXT	0 ~ 65535
MEDIUMTEXT	0 ~ 16772150
LONGTEXT	0 ~ 4294967295
ENUM
SET

5. 二进制数据类型

类型	取值范围
BINARY(M)	字节数为M，允许长度为0 ~ M的定长二进制字符串
VARBINARY(M)	3
DATE	4
DATETIME	8
TIMESTAMP	4

6. 复合数据类型
   enum 和 set

create table test (name varchar(40), sex enum('male', 'female') );
insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');

create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));
insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');
# 通过 find_in_set 函数来查找 set 类型满足条件的数据
select * from test2 where find_in_set('red', color);