跳至主要內容

SQL语句

知识库数据库SQL数据库大约 4 分钟

提示

SQL 是一种声明式语言，声明了一个你想从原始数据中获得什么样的结果的一个范例。

基础

-- 创建数据库
CREATE DATABASE databaseName;
-- 删除数据库
DROP DATABASE databaseName;
-- 进入数据库
use databaseName;
-- 创建新表
CREATE TABLE tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..);
-- 根据已有的表创建新表：
CREATE TABLE tab_new like tab_old (使用旧表创建新表);
CREATE TABLE tab_new as SELECT col1,col2… FROM tab_old definition only;
-- 删除新表
DROP TABLE tabname;
-- 增加一个列
ALTER TABLE tabname ADD COLUMN col type;
-- 添加主键
ALTER TABLE tabname ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE tabname DROP PRIMARY KEY
-- 创建索引
CREATE [unique] index idxname ON tabname(col….);
-- 删除索引，索引是不可更改的，想更改必须删除重新建。
DROP INDEX idxname;
-- 创建视图
CREATE VIEW viewname AS SELECT statement;
-- 删除视图
DROP VIEW viewname;
-- 选择
SELECT * FROM table1 WHERE 1 = 1;
-- 插入
INSERT INTO table1 (field1, field2) VALUES (value1, value2);
-- 删除
DELETE FROM table1 WHERE 1 = 1;
-- 查找
SELECT * FROM table1 WHERE field1 LIKE '%value1%';
-- 排序
SELECT * FROM table1 ORDER BY field1, field2[desc|ASC];
-- 总数
SELECT COUNT(1) 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;
高级查询运算词：UNION（并集） EXCEPT（差集） INTERSECT（交集）
使用外连接：LEFT JOIN RIGHT JOIN FULL JOIN
分组：GROUP BY ，统计信息 CONT,SUM,MAX,MIN,AVG
排序：ORDER BY，DESC|ASC

提升

-- 显示文章、提交人和最后回复时间
SELECT a.title, a.username, b.adddate FROM table a, ( SELECT MAX(adddate) AS adddate FROM table WHERE table.title = a.title ) b;
-- between的用法,between限制查询数据范围时包括了边界值,not between不包括
SELECT * FROM table1 WHERE create_time BETWEEN time1 AND time2;
SELECT * FROM table1 WHERE create_time NOT BETWEEN time1 AND time2;
-- 两张关联表，删除主表中已经在副表中没有的信息
DELETE FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE able1.field1 = table2.field1 );
-- 产生0到100间的随机数
SELECT FLOOR(RAND() * 100);
-- md5()产生32位随机字符串
SELECT MD5(RAND() * 10000);
-- 当前时间戳
SELECT UNIX_TIMESTAMP() * 1000;
-- 当前时间
SELECT NOW();
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
select CONCAT(CURDATE(),' ',CURTIME());
-- 相差2天
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');
-- 5天(5小时/5分钟/5秒)之内创建的数据
SELECT * FROM tableName WHERE TIMESTAMPDIFF(DAY,created_at,now()) < 5;
SELECT * FROM tableName WHERE TIMESTAMPDIFF(HOUR,created_at,now()) < 5;
SELECT * FROM tableName WHERE TIMESTAMPDIFF(MINUTE,created_at,now()) < 5;
SELECT * FROM tableName WHERE TIMESTAMPDIFF(SECOND,created_at,now()) < 5;

技巧

  • 1=1 和 1=2
  • 导出 database
mysqldump -uroot -p --databases databasename > sql.sql
  • 导入 sql 文件
mysql -uroot -p -D databasename < sql.sql
  • 替换字段中的空格
UPDATE users SET full_name = replace(full_name, ' ', '');

数据开发-经典

-- 按姓氏笔画排序,从少到多
SELECT * FROM TableName ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_ci_as;

性能优化

SQL 执行顺序
stage1=>operation: FROM
stage2=>operation: ON
stage3=>operation: JOIN
stage4=>operation: WHERE
stage5=>operation: GROUP BY
stage6=>operation: HAVING
stage7=>operation: SELECT
stage8=>operation: DISTINCT
stage9=>operation: ORDER BY
stage10=>operation: LIMIT

stage1(right)->stage2(right)->stage3(right)->stage4(right)->stage5(right)->stage6(right)->stage7(right)->stage8(right)->stage9(right)->stage10(right)
SQL 原则

  • FROM 后面的表关联，是自右向左解析的.尽量把数据量大的表放在最右边来进行关联。

  • WHERE 条件的解析顺序是自下而上的,能筛选出大量数据的条件放在 WHERE 语句的最下面。

  • 避免全表扫描:

    1. WHERE order by 字段创建索引
    2. null 用 0 替代
    3. 避免使用!= <>
    4. union all 替换 or
    5. 避免使用 in 和 not in 尽量使用 between and
    6. 避免模糊查询
    7. 使用 exists 替代 in exists 查询效率更好
    8. 子查询中不要包含 ORDER BY 子句

  • 尽量使用数字类型

  • 优化 distinct 使用 group by

  • 尽量避免 WHERE =左边的函数处理

  • F5 看执行计划 修改 cost 为个位数或十位数

  • 子查询要放在比较操作符的右边

  • 不要对索引字段进行格式转换

  • 不要对索引字段使用函数

  • 在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

  • 需创建索引

    1. 列中数据值分布范围很广
    2. 列中包含大量空值
    3. 列经常在 WHERE 子句或连接条件中出现
    4. 表经常被访问而且数据量很大，访问的数据大概占数据总量的 2%到 4%

  • 不要创建索引

    1. 表很小
    2. 列不经常作为连接条件或出现在 WHERE 子句中
    3. 查询的数据大于 2%到 4%
    4. 表经常更新
    5. 加索引的列包含在表达式中