SQL,即结构化查询语言,用于操作数据库,这里是一些初步的增删查改,还在更新中。。

开始

显示并切换到指定数据库和表:

SHOW DATABASES;
USE database_name;
DESC departments;
  • 列是字段,行是记录
  • my.ini 可以管理配置
  • 通过 net stopnet start 在 windows 停止/启动服务
  • SQL 语法不区分大小写,但建议关键字大写

简单查询

查询列表其实可以是表中的任意元素,查询的结果是一张虚拟的表。

SELECT DISTINCT 查询列表 AS 结果名 FROM 表名 WHERE a>0 AND/OR b<0 ORDER BY 名称 ASC/DESC;

查询列表可以是:

  • 常量:SELECT 100;,SQL 不区分字符和字符串的查询,SELECT 'john';
  • 表达式:SELECT 100%98;
  • 函数:SELECT xxx();

AS 起别名,可省略:

SELECT salary AS "out put" FROM employees;
  • 消歧义,可以单引号,建议双引号

DISTINCT 去重:

SELECT DISTINCT department_id FROM employees;

WHERE 条件查询的条件、逻辑运算符:

LIKE '%a%'
BETWEEN 0 AND 10
IN 1,2,3
IS NULL
NOT
> < = <> >= <= <=>
&& || !

ORDER BY 排序查询:

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

可以有多个排序,从前往后:ORDER BY 字段1 DESC, 字段2 ASC;

常用字符函数

+号只有运算的作用,连接用 CONCAT 函数:

SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

IFNULL 函数,把 null 值转化为另外的结果:

SELECT CONCAT(last_name,first_name,IFNULL(commission_pct,0)) AS out_put FROM employees;

判断字节长度的 LENGTH()(mySQL 的编码是 UTF-8,所以一个中文3字节):

SELECT LENGTH(last_name) 名字长度, last_name, salary
FROM employees
ORDER BY 名字长度 DESC;

大小写格式化:

UPPER()
LOWER()

SUBSTR() 摘抄文本(编号从1开始,字符而不是字节长度):

SELECT SUBSTR('这是一段测试文本这',6) out_put;
SELECT SUBSTR('这是一段测试文本这',1,3) out_put;

INSTR() 逆向摘抄(返回起始位置):

SELECT INSTR('这是一段测试文本这','文本这') out_put;

TRIM() 去除前后重复字符(默认是空格):

SELECT TRIM('     文本这   ') out_put;
SELECT TRIM('a' FROM 'aaaaa文aa本这aaa') out_put;

LPAD() / RPAD() 用制定的字符左右填充到指定长度:

SELECT LPAD('文本这',10,'*') out_put;
SELECT RPAD('文本这',10,'*') out_put;

REPLACE() 全部替换:

SELECT REPLACE('文本文本这','文本','稳住') out_put;

常用数学函数

ROUND() 四舍五入:

SELECT ROUND(1.65);
SELECT ROUND(1.657,2); -- 保留两位小数

CEIL()FLOOR() 向上、向下取整:

SELECT CEIL(1.65); -- 2
SELECT CEIL(1.00); -- 1
SELECT FLOOR(1.65); -- 1

TRUNCATE() 截断:

SELECT TRUNCATE(1.657,2); -- 保留两位小数

MOD() 取余,实际上的算法 a-a/b*b,因为除号取整,所以结果会有出入:

SELECT MOD(10,3); -- SELECT 10%3;
  • 获取一个0-1之间的随机小数:RAND()

常用日期函数

  • NOW() 返回时间日期:SELECT NOW();
  • CURDATE() 返回日期:SELECT CURDATE();
  • CURTIME() 返回时间:SELECT CURTIME();

获取日期时间中指定的部分:

SELECT YEAR(NOW());
SELECT YEAR(hiredate) 年 FROM employees;

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
  • STR_TO_DATE() 格式化日期:SELECT STR_TO_DATE('09-13-1999','%m-%d-%Y');
  • DATE_FORMAT() 个性化日期:SELECT DATE_FORMAT('1999-09-13','%Y年%m月%d日');

返回日期间相差的天数:

SELECT DATEDIFF('2017-10-01','2017-09-29');

流程控制函数

IF()

SELECT IF(10>5, '大', '小');
SELECT last_name, commission_pct, IF(commission_pct IS NULL, '没奖金', '有奖金') 备注 FROM employees;

CASE()

SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

分组函数

也是统计函数。

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
  • SUM、MIN 仅数值,其他可以任意类型
  • NULL+任何值都为 NULL,所以是没参与运算
  • 去重:SELECT SUM(DISTINCT salary) FROM employees;

关于 COUNT 和其他注意点:

  • MYISAM 储存引擎下 COUNT(*) 效率高
  • INNODB 下,COUNT(*)COUNT(1) 差不多,但比 COUNT(字段) 高些
  • 和分组函数一通查询的字段要求是 GROUP BY 后的字段
SELECT COUNT(*) FROM employees; -- 统计行数

其他函数

当前数据库服务器的版本:

SELECT VERSION();

打开当前的数据库:

SELECT DATABASE();

当前用户:

SELECT USER();

返回字符的密码形式(已过时):

PASSWORD('xxx')

返回字符的 MD5 加密形式:

MD5('xxx')

GROUP BY 分组查询

SELECT MAX(salary), job_id FROM employees GROUP BY job_id;

加个 WHERE 分组前筛选,以及用 HAVING 分组后筛选,性能原因,优先分组前筛选。

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
  • 函数分组:GROUP BY LENGTH(last_name)
  • 多字段分组:GROUP BY department_id,job_id
  • 添加排序:最后加 ORDER BY a DESC

连接查询

使用连接查询避免笛卡尔乘积:

SELECT Name, boyName
FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;

SELECT 里的也不是和 WHERE 一一对应,所以出现歧义的时候也要加限定。

SELECT last_name, employees.job_id, job_title
FROM employees, jobs
WHERE employees.`job_id` = jobs.`job_id`;
  • 如果用了别名,就不能用原名
  • 连接查询分为等值、非等值和自连接

SQL92 语法:

SELECT ... FROM ... WHERE 表1.key=表2.key;

增加记录

INSERT INTO table_name
VALUES (value1,value2,value3,...);

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

删除记录

DELETE FROM table_name
WHERE some_column=some_value;

更新记录

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

快速参考

SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更(改变)数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引(搜索键)
DROP INDEX - 删除索引