持续更新
启用/停止/登录
# 启用
net start mysql
# 停止
net stop mysql
# 登录本机mysql
mysql -uroot -p密码
# 登录远程mysql服务
mysql -h主机地址 -P端口 -u用户名 -p密码
基本操作
规范:
- 以
;
或\g
结尾;- 关键字大写;
- 一条sql语句可以换行。
#查看当前用户
SELECT USER();
#显示数据库
SHOW DATABASES;
#打开数据库
USE 数据库名;
#查看当前库
SELECT DATABASE();
#显示当前数据库的表
SHOW TABLES;
#打开一个数据库的同时查看其他数据库的表
SHOW TABLES FROM 数据库名;
#创建表
CREATE TABLE 自定义表名(
字段1 类型1(长度),
字段2 类型2,
字段3 类型3,
……
);
#eg.
CREATE TABLE people(
username varchar(20),
id int,
gender char,
birthday datetime
);
常用函数 - 单行函数
字符函数
#CONCAT(str1, str2, ...)
#拼接字符串
SELECT CONCAT('HELLO', ' WORLD'); #HELLO WORLD
#LENGTH(str)
#查询字节长度,一个汉字的字节长度是3,英文字母的字节长度是1
SELECT LENGTH('HELLOWORLD'); #10
SELECT LENGTH('哈哈'); #6
#CHAR_LENGTH(str)
#查询字符长度
SELECT CHAR_LENGTH('HELLOWORLD'); #10
SELECT CHAR_LENGTH('哈哈'); #2
#SUBSTR(str, startIndex, length)
#mysql中字符串索引从1开始;省略最后一个参数即从startIndex截取到最后
SELECT SUBSTR('HELLOWORLD', 2, 3); #ELL
#INSTR(str, sub)
#查询sub在str中的索引,类似JS中的indexOf
SELECT INSTR('HELLOWORLD', 'ELL'); #2
#TRIM
#去除字符串前后空格或指定字符
SELECT TRIM(' HAHA HAHA '); #HAHA HAHA
SELECT TRIM('h' FROM 'hhhHAHA HAHAhhhh'); #HAHA HAHA
#STRCMP(str1, str2)
#比较两个字符的大小
SELECT STRCMP('abc','aaa'); #1
SELECT STRCMP('abc','abc'); #0
SELECT STRCMP('abc','ccc'); #-1
#LEFT / RIGHT(str, counts)
#从左边或右边起始位置开始截取字符串
SELECT LEFT('ABC', 1); #A
SELECT RIGHT('ABC', 2); #BC
#LPAD / RPAD(str, length, padstr)
#在字符串的左边或右边填充padstr,填充后的字符串长度为length
SELECT LPAD('HAHA', 1, 'C'); #H
SELECT LPAD('HAHA', 4, 'C'); #HAHA
SELECT LPAD('HAHA', 8, 'C'); #CCCCHAHA
SELECT RPAD('哈哈',4,'aaa'); #哈哈aa
SELECT RPAD('哈哈',7,'aaa'); #哈哈aaaaa
数字函数
#ABS
#绝对值
SELECT ABS(-100); #100
SELECT ABS(100); #100
#CEIL
#向上取整
SELECT CEIL(10.001); #11
SELECT CEIL(10.999); #11
#FLOOR
#向下取整
SELECT FLOOR(10.001); #10
SELECT FLOOR(10.999); #10
#ROUND
#四舍五入
SELECT ROUND(10.001); #10
SELECT ROUND(10.999); #11
SELECT ROUND(10.45, 1); #10.5
SELECT ROUND(15.45, -1); #20
#TRUNCATE
#强制截取,不做四舍五入、向上或向下取整
SELECT TRUNCATE(10.001, 0); #10
SELECT TRUNCATE(10.99, 0); #10
SELECT TRUNCATE(10.99, 1); #10.9
#MOD
#取余
#结果的正负由第一个参数的正负决定
SELECT MOD(10, 3); #1
SELECT MOD(-10, 3); #-1
SELECT MOD(10, -3); #1
SELECT MOD(-10, -3); #-1
日期函数
#NOW
#获取当前日期和时间
SELECT NOW(); #2020-12-24 10:08:11
#CURDATE
#当前日期
SELECT CURDATE(); #2020-12-24
#CURTIME
获取当前时间
SELECT CURTIME(); #10:09:02
#DATEDIFF(date1, date2)
#求两个时间之间的差值
SELECT DATEDIFF('2020-12-24', '1994-1-9'); #9846
#DATE_FORMAT(date, format)
#格式化日期
SELECT DATE_FORMAT('2020-12-24', '%Y年%m月%d日'); #2020年12月24日
SELECT DATE_FORMAT('2020-12-24', '%D %M %Y'); #24th December 2020
#STR_TO_DATE(str, format)
#将字符串转为日期
SELECT STR_TO_DATE('12/25 2020', '%m/%d %Y'); #2020-12-25
SELECT STR_TO_DATE('24th December 2020', '%D %M %Y'); #2020-12-24
流程控制函数
#IF(expr1, expr_true, expr_false)
SELECT IF(100>99, 'YES', 'NO'); #YES
SELECT IF(commission_pct IS NULL, 0, salary*12*commission_pct) AS bonus
FROM test;
#CASE
/*
1.
CASE expr
WHEN value1 THEN result_1
WHEN value2 THEN result_2
ELSE result_n
END
2.
CASE
WHEN condition1 THEN result_1
WHEN condition2 THEN result_2
ELSE result_n
END
*/
SELECT department_id, salary,
CASE department_id
WHEN '101' THEN salary*2
WHEN '102' THEN salary*3
WHEN '99' THEN salary*0
END AS newsalary
FROM test;
基础查询
有一张名为test的表如下:
查询表结构
# Syntax: DESC table_reference;
DESC test;
查询字段
# Syntax: SELECT select_expr FROM table_references;
SELECT * FROM test; # *代表所有字段
别名
利用
AS
关键字或(空格)
给查询字段定义别名
SELECT `first_name` AS employee_name FROM test;
SELECT `first_name` employee_name FROM test; #与AS等效
SELECT CONCAT(UPPER(first_name),' ', UPPER(SUBSTR(last_name, 1, 1)), SUBSTR(last_name, 2)) AS full_name
FROM test;
去重
利用DISTINCT关键字去重
SELECT DISTINCT department_id FROM test;
替换NULL
IFNULL(para1, para2), 参数一位字段,参数二为字段值为空时的替换值
SELECT IFNULL(manager_id, '空') FROM test;
条件查询
关系表达式
mysql中的关系表达式包括:
>
大于<
小于>=
大于等于<=
小于等于=
等于<>
不等于
逻辑表达式
- AND 与
- OR 或
- NOT 非
*虽然也支持&&
、||
、!
,但不推荐。
模糊查询
- LIKE,一般与通配符搭配使用,对字符型数据进行部分匹配
- IN,查询某字段的值是否在列表之内
- BETWEEN AND,查询某字段的值是否在一个范围内
- IS NULL,判断某字段的值是否为空
SELECT first_name FROM test WHERE first_name LIKE '%A%';
SELECT first_name FROM test WHERE first_name LIKE '_l%';
通配符:
_
任意一个字符;%
任意个字符,包括0;\
默认转义字符。
ESCAPE
自定义转移字符:SELECT first_name FROM test WHERE first_name LIKE '_$_%' ESCAPE '$'; #与下面语句效果一致 #SELECT first_name FROM test WHERE first_name LIKE '_\_%';
SELECT first_name, department_id FROM test WHERE department_id IN (99, 102);
SELECT first_name, department_id FROM test WHERE department_id NOT IN (99, 102);
SELECT first_name, salary FROM test WHERE salary BETWEEN 20000 AND 100000;
SELECT first_name, salary FROM test WHERE salary NOT BETWEEN 20000 AND 100000;
SELECT first_name, manager_id FROM test WHERE manager_id IS NULL;
SELECT first_name, manager_id FROM test WHERE manager_id IS NOT NULL;
常见函数 - 分组函数
分组函数,也叫聚合函数
#SUM
#求和
SELECT SUM(salary)
FROM test; #275000.00
#MIN
#最小值
SELECT MIN(salary)
FROM test; #7000.00
#MAX
#最大值
SELECT MAX(salary)
FROM test; #130000.00
#AVG
#平均值
SELECT AVG(salary)
FROM test; #55000.000000
#COUNT
#字段值的个数
#COUNT(*) / COUNT(常量) 都能够检索出整张表非空行的个数
SELECT COUNT(*) FROM test; #5
SELECT COUNT(1) FROM test; #5
SELECT COUNT(employee_id) FROM test; #5
SELECT COUNT(manager_id) FROM test; #3
分组查询
分组查询关键字:
GROUP BY
#搭配分组函数
SELECT COUNT(*), department_id FROM test GROUP BY department_id;
SELECT AVG(salary), department_id FROM test GROUP BY department_id;
#条件筛选
#WHEN
SELECT MAX(salary), manager_id FROM test
WHERE manager_id IS NOT NULL
GROUP BY manager_id;
#HAVING
SELECT MAX(salary), department_id FROM test
GROUP BY department_id
HAVING MAX(salary) > 10000;
#分组查询后排序 ORDER BY
SELECT MIN(salary), department_id FROM test
GROUP BY department_id
ORDER BY MIN(salary) DESC;