Mysql学习笔记 -- SQL语句

持续更新

启用/停止/登录

# 启用
net start mysql

# 停止
net stop mysql

# 登录本机mysql
mysql -uroot -p密码

# 登录远程mysql服务
mysql -h主机地址 -P端口 -u用户名 -p密码

基本操作

规范：

1. 以; 或 \g 结尾；
2. 关键字大写；
3. 一条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中的关系表达式包括：

1. > 大于
2. < 小于
3. >= 大于等于
4. <= 小于等于
5. = 等于
6. <> 不等于

逻辑表达式

1. AND 与
2. OR 或
3. NOT 非
   *虽然也支持&&、||、!，但不推荐。

模糊查询

1. LIKE，一般与通配符搭配使用，对字符型数据进行部分匹配
2. IN，查询某字段的值是否在列表之内
3. BETWEEN AND，查询某字段的值是否在一个范围内
4. 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;