SQL核心知识总结备忘(实例基于MySQL5.7.21)

最近要准备面试了，在这里总结一下SQL核心知识。

SQL简介

---

SQL 是用于访问和处理数据库的标准的计算机语言。
SQL可大体分为3类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

 

SQL速查

---

SQL 通配符

SQL 通配符必须与 LIKE 运算符一起使用。

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	非字符列中的任一字符

 

SQL语法案例

---

DDL常用实例

实际应用采用数据库管理工具如Aqua Data Studio即可

1.建库、删库

CREATE DATABASE dbName

DROP   DATABASE dbname

2.建表、复制表、删表

CREATE TABLE `new_tab` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 建表

-- * 通过复制旧表建立新表 *
CREATE TABLE new_tab SELECT * FROM old_tab -- 复制旧表结构及数据
CREATE TABLE new_tab SELECT * FROM old_tab WHERE 1=2 -- 只复制旧表结构

DROP TABLE 'new_tab' -- 删表

3.修改表

（1）增删字段

ALTER TABLE `new_tab` DROP `value`; -- 删除字段 value

ALTER TABLE `new_tab` ADD COLUMN `valid` INT NULL DEFAULT NULL; -- 新增valid,column可省略

tip: ADD时mysql可以用after 字段名指定位置

ALTER TABLE `new_tab` ADD `valid` INT NULL DEFAULT NULL AFTER `name`

(2) 修改字段

ALTER TABLE `new_tab` CHANGE `valid` `valid` VARCHAR(32) -- 修改字段类型、长度

4.设置主键

ALTER TABLE `new_tab` add primary key(id) -- 将字段id设为主键，id必须存在

5.增删索引

--新建索引语法 create [unique] index idxname on tabname(col….) 
CRETE  INDEX `index_valid` ON `new_tab`(`valid`) 

ALTER TABLE `new_tab` DROP INDEX `index_valid` -- 删除索引

SHOW INDEX FROM `new_tab` -- 查询所有索引

6.增删视图

-- 新建视图:   create view viewname as select statement 
CREATE VIEW new_view AS SELECT id,name,valid from new_tab -- 采用select字段名称作为视图字段名称
CREATE VIEW new_view1(id1,name1,valid1) AS SELECT id,name,valid from new_tab -- 自定义新字段名称

-- 删除视图：drop view viewname
DROP VIEW new_view1

DML常用实例

选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’
排序：select * from table1 order by field1,field2 [desc]
总数：select count(*) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1[separator]
外连接：

• left outer join on 连接条件：
  左外连接（左连接）：结果集包括左连接表的所有行和连接表的匹配行
  SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
• right outer join on 连接条件:
  右外连接(右连接)：结果集包括右连接表的所有行连接表的匹配行
• full outer join on 连接条件：
  全外连接：包括两个连接表中的所有记录。
  联合：
• UNION 联合时消除重复列
• UNION ALL 联合时不消除重复列
  分页：select * from old_tab limit 0,10
  其他：between,not, in , exists

典型场景

top n

说明：先排序然后取前n个，limit n = limit 0,n

select * from tablename order by orderfield desc/asc limit n；

选择排名

说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息（排行榜）

select a,b,c from TA ta where a=(select max(a) from TB tb where tb.b=ta.b)

随机查询

SELECT * FROM `old_tab` ORDER BY RAND() LIMIT 1 -- 简单较慢  15万数据 8s左右

SELECT * FROM `old_tab` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `old_tab`)-(SELECT MIN(id) FROM `old_tab`))+(SELECT MIN(id) FROM `old_tab`)) AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id LIMIT 1; -- 推荐，原理 id >= Round((MaxID-MinID)*Rand()) + MinID 

SELECT * FROM `old_tab` AS t1 
JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `old_tab`)-(SELECT MIN(id) FROM `old_tab`))+(SELECT MIN(id) FROM `old_tab`)) AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id LIMIT 1; -- 推荐，较快（待验证）

处理重复

利用分组统计列colA重复数据

SELECT COUNT(*) as reNum, colA   FROM old_tab GROUP BY colA  HAVING reNum > 1;

过滤重复数据

 SELECT DISTINCT colA  FROM old_tab -- DISTINCT （推荐）
 或者
  SELECT  colA  FROM old_tab GROUP BY colA -- 通过GROUP BY

删除重复数据
方法一：根据表结构特点找到要删除的id然后删除之
例如：下面先通过子查询找到重复记录中重复的name和最小的id ,然后删除原表中name = tname 且 id > minId的记录

delete from old_tab 
where id >
(select minId from (select min(id) ,name as minId from old_tab  group by name having count(name)>1) t where t.name = name) 

方法二：新建表导入过滤重复数据后的记录，重命名为原表，原表删除。

	--- ----未完待续-----

SQL零散知识备忘

---

1.sql不区分大小写
2.between包含开始及结束值
3.SQL通配符：%，_，[]
4.sql获取当前日期:mysql[curdate()] ,orcale[sysdate], sql server[GETDATE()]
5.Oracle中无as
6.truncate table清空表（速度比delete快，因为不记录。该方法不常用。）
7.mysql所有变量必须以@开头(这里指全局变量)
8.mysql游标只能用于存储过程和函数
9.MyISAM引擎不支持事务，InnoDB支持事务。

参考文献

http://www.w3school.com.cn/sql/index.asp
http://www.cnblogs.com/Mrwxs/articles/8613470.html
https://www.cnblogs.com/dongdongleday/p/6659087.html
https://www.jb51.net/article/42229.htm