【MySQL】高级用法

MYSQL 高级用法

创建表结构

• 直接创建

	
	DROP TABLE IF EXISTS movies;
	create table movies(
		id int primary key not null,
		director varchar,
		actors varchar,
		movie varchar,
		prices double not null,
		created_date date not null,
		updated_date date
	)ENGINE=INNODB DEFAULT CHARSET=utf8;--数据库引擎和字符编码;
	

• 如果表不存在，则创建

	DROP TABLE IF EXISTS movies;
	create table if not exists movies(
		id int primary key not null,
		director varchar,
		actors varchar,
		movie varchar,
		prices double not null,
		created_date date not null,
		updated_date date
	)ENGINE=INNODB DEFAULT CHARSET=utf8;--数据库引擎和字符编码;

CHARSET=utf8 不设置的话，会是mysql默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1，不支持中文

表数据插入

• 直接插入

		
	INSERT INTO movies VALUES (1,'甄子丹','叶问1','甄子丹',40.5,NOW(),NOW());
	INSERT INTO movies VALUES (2,'甄子丹','叶问2','甄子丹',40.5,NOW(),NOW());
	INSERT INTO movies VALUES (3,'甄子丹','叶问3','甄子丹',40.5,NOW(),NOW());
	INSERT INTO movies VALUES (4,null,'宇宙通缉令','李连杰',65.5,NOW(),NOW());
	INSERT INTO movies VALUES (5,'卡梅隆','泰坦尼克号','Json',45.5,NOW(),NOW());
	INSERT INTO movies VALUES (6,'成龙','警察故事','成龙',50.5,NOW(),NOW());

• 主键冲突时忽略INSERT IGNORE INTO

	INSERT IGNORE INTO movies VALUES (1,'甄子丹','叶问1','甄子丹',41.5,now(),now());

• 主键冲突时 先删除再插入 REPLACE INTO

	REPLACE INTO movies VALUES (1,'甄子丹','叶问1','甄子丹',42.5,now(),now());

• 主键重复时，会执行更新操作 ON DUPLICATE KEY UPDATE

	INSERT INTO movies VALUES (6,'成龙','警察故事','成龙',50.5,NOW(),NOW()) 
	ON DUPLICATE KEY UPDATE prices =  prices  + 10;

排序 ORDER BY

• 自定义排序 field

 	select * from movies order by movie;
 	select * from movies order by field(movie , '叶问1','叶问1','叶问2','叶问3','警察故事','泰坦尼克号','宇宙通缉令');

• 空值排序 if( isnull(A)，0,1)

   select * from movies order by if(isnull(director , 2,1)) , director  desc;

分组 GROUP BY

• 分组链接函数group_contact

根据director 进行分组，然后拼接movie在一起,这样就可以知道一个导演拍了多少电影

SELECT director ,group_contact(movie) as movies FROM movies m group by director ;
SELECT director ,group_contact(movie order by prices SEPARATOR ',') as movies FROM movies m group by director ;

• 分组统计后在进行统计汇总WITH ROLLUP

	SELECT actors,sum(prices) FROM movies m GROUP BY actors;
	SELECT actors,sum(prices) FROM movies m GROUP BY actors WITH ROLLUP;

CASE WHEN 表达式CASE WHEN

	select *,case when prices > 60  then '太贵'
				 when prices > 50 and prices < 40 then '一般'
				 when prices < 40 then '便宜'
				 else '白菜价' from movies ;

公共子查询提取 WITH AS

生成一个表名为 movies_level 的临时表，以供后面的SQL使用

WITH 
movies_1 AS (
	SELECT movie,prices,actors,CASE WHEN prices > 60  THEN'太贵'
				 WHEN prices > 50 and prices < 40 THEN'一般'
				 WHEN prices < 40 THEN '便宜'
				 ELSE '白菜价' END tags FROM movies where m.tags = '便宜' ;
),
movies_2 as (
	SELECT movie,prices,actors,CASE WHEN prices > 60  THEN'太贵'
				 WHEN prices > 50 and prices < 40 THEN'一般'
				 WHEN prices < 40 THEN '便宜'
				 ELSE '白菜价' END tags FROM movies where m.tags = '太贵' ;
)
SELECT * FROM movies_1 union SELECT * FROM movies_2;

动态SQL的执行

DECLARE

BEGIN 
-- 动态查询数据
SET @id := 1;
SET @sql := CONCAT('SELECT actors,sum(age) FROM movies m GROUP BY actors id = ', @id ,' AND moive = 警察故事');

-- sql 预编译
PREPARE stmt FROM @sql;
-- 执行查询
EXECUTE stmt;
-- 释放语句
DEALLOCATE PREPARE stmt;

END;

表数据清空

• DROP TABLE删除表后重新创建
• DELETE FROM 删除全部数据
• TRUNCATE TABLE 删除表所在整页数据

truncate和delete都能删除表中的数据，他们的区别：

• 1. TRUNCATE 是 DDL 命令，命令执行完就提交，删除的数据不能恢复； DELETE 命令是 DML 命令，命令执行完需提交后才能生效，删除后的数据可以通过日志文件恢复。
• 2. 如果表中的数据量较大，TRUNCATE的速度比DELETE速度快很多。
• 3. truncate删除将重新设置表索引的初始大小，而delete不能。
• 4. delete能够触发表上相关的delete触发器，而truncate则不会触发。
• 5. delete删除的原理是一次一条从表中删除数据，并将删除操作当做事物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进行数据页的删除，因此执行速度快，但是不能回滚
• 6. 速度上，一般来说drop>truncate >delete。