sql语句，从查询开始

sql语句，从查询开始-基本查询

基本的SELECT语句

基本语法

SELECT 字段1，字段2，字段3…FROM 表名

# 通过desc查看都有那些字段
DESC employees;
# *代表所有的列
SELECT * 
FROM employees;
#
SELECT employee_id,last_name,email 
FROM employees;

列的别名

三种表示别名的方法：

字段名+空格+别名（这种情况下，如果别名自带空格是不能使用得）

字段名+AS(全称：alias)+别名

字段名+空格+双引号别名

SELECT employee_id emp_id,last_name AS lname,department_id "部门ID"
FROM employees;

去除重复行

在查询某一个字段的时候，因为表的设计，该字段的会有许多重复的值

比如查询员工表中的部门ID，有很多员工会属于同一个部门，部门ID也就重复了

通过在字段前面加一个 DISTINCT关键字进行去重查询

SELECT DISTINCT department_id
FROM employees;

空值参与运算

空值：null

null不等同于0，‘’，‘null’

空值参与运算，结果一定也为空。

#通过以下方式查询出来的结果，因为大部分人奖金率是null的，计算出来的结果就为null
SELECT employee_id,salary "月工资",salary*(1+commission_pct)*12 "年工资"
FROM employees;
# 根据上面需求，需要查询出年工资则可以通过把奖金率中的null让系统替换为0计算，可以通过IFNULL函数进行
# IFNULL(判断对象如果为NULL,替换成该值)
SELECT employee_id,salary "月工资",salary*(1+ IFNULL( commission_pct,0))*12 "年工资"
FROM employees;

着重号

如果表名和关键字重复了，则可以通过着重号进行解决

# order是mysql的一个关键字，然后我也有一个表叫order，可以通过着重号进行查询
SELECT * 
FROM `order`;

查询常数

如果在查询的时候，表中的数据没有存在的字段，而该字段又可以作为每条记录都能用的数据，可以通过查询常数插入

比如员工表中没有公司名称，而该表中所有员工都属于一个公司的情况，可以通过查询常数插入

在查询字段前面，通过单引号引用插入

SELECT 'A公司',employee_id,last_name
FROM employees;

显示表结构

DESCRIBE employees; # 现实了表中字段的详细信息
DESC employees;

过滤数据

例如只查询90号部门的数据，其他的不需要

通过WHERE进行过滤查询，WHERE需要声明在FROM的后面

SELECT * 
FROM employees
WHERE department_id=90;
#
SELECT *
FROM employees
WHERE last_name='King';
#

运算符

基本算数运算符

运算符	名称	作用	示例
+	加法运算符	计算两个值或表达式的和	SELECT A + B
-	减法运算符	计算两个值或表达式的差	SELECT A-B
*	乘法运算符	计算两个值或表达式的乘积	SELECT A* B
/或DIV	除法运算符	计算两个值或表达式的商	SELECT A/B 或者 SELECT A DIV B
%或MOD	求模（求余）运算符	计算两个值或表达式的余数	SELECT A % B 或者 SELECT A MOD B

加减乘除取模运算符

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM dual;
#
SELECT 100,100 * 1,100 * 1.0,100 / 1.0,100 / 2,100 + 2 * 5 / 2 ,100 /3 ,100
DIV 0
FROM DUAL;
SELECT 12 % 3, 12 MOD 5 FROM dual;

比较运算符

SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL;
#
SELECT 1 = 2, 0 = 'abc', 1 = 'abc' 
FROM DUAL;
# 安全等于可以对NULL进行判断
SELECT 1 <=> NULL,NULL <=> NULL
FROM DUAL;

非符号运算符

# IS NULL/IS NOT NULL/ISNULL
# IS NULL是指等于NULL的值
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#ISNULL是一个函数
SELECT last_name,salary,commission_pct
FROM employees
WHERE  ISNULL(commission_pct);

# IS NOT NULL不等于NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# LEAST/GREATEST
# LEAST最小
# GREATEST最大
SELECT LEAST('G','b','m','a'),GREATEST('G','b','m','a')
FROM DUAL;
#
SELECT LEAST(first_name,last_name) "最小",GREATEST(first_name,last_name) "最大"
FROM employees;

# BETWEEN 条件1 AND 条件2 ，查询条件1和条件2之间的数据，包含边界
# 查询6000-8000之间月工资的员工
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >= 6000 && salary <= 8000;
# in (set) / not in (set) 
# 通过使用IN来筛选出department_id=10的或者20的或者30的
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN (10,20,30);

SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);
# 模糊查询 LIKE
# 比如查询last_name中包含字符'a'的员工信息,%表示不确定字符
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
# 查询以a开头的字符
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
# 查询第二个字符是a的，_；代表一个不确定的字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

# REGEXP：正则表达式。以a开头的匹配
SELECT last_name
FROM employees
WHERE last_name REGEXP '^a';

逻辑运算符

运算符	作用	示例
NOT或!	逻辑非	SELECT NOT A
AND或&&	逻辑与	SELECT A AND B SELECT A&&B
OR或||	逻辑或	SELECT A OR B
SELECT A
XOR	逻辑异或	SELECT A XOR B

1. 逻辑非运算符 逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

2. 逻辑与运算符 逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

3. 逻辑或运算符 逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为 NULL时，返回NULL。

4. 逻辑异或运算符 逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

排序和分页

排序

如果没有使用排序，默认查询返回数据按照插入数据顺序进行排序的

可以使用 ORDER BY 对查询的数据进行排序操作

升序操作使用 ASC（ascend）

降序操作使用 DESC（descend）

如果不加升序或者降序，默认使用升序

# 例如对工资从高到底排序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
# 例如使用别名进行排序，列的别名不能在WHERE中使用
SELECT employee_id,last_name,salary,salary*12 annual_sal
FROM employees
ORDER BY annual_sal;
# 例如同时使用SHERE过滤和排序，还可以过滤字段和排序字段不是查询的字段
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;
# 二级排序，先对department_id进行降序排序，然后在对salary进行升序排序。
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC,salary;

分页

Mysql 使用limit实现数据分页显示

# 例如每页20条数据，LIMIT 0,20; 0是指从第一条数据开始，往后的20条数据
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

# 例如每页显示20条记录，此时显示第2页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;
# 例如每页显示pageSize条记录，此时显示第pageNo页；
# 公式：LIMIT(pageNo-1)*pageSize,pageSize;
# 显示第一页,每页20条 pageNo=1，pageSize=20
# LIMIT(1-1)*20，20;
# LIMIT 0,20;
# 显示第二页，每页20条，pageNo=2，pageSize=20
# LIMIT(2-1)*20，20;
# LIMIT 20,20;

# WHERE...ORDER BY LIMIT 声明顺序
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0,10;

# 不规则显示，比如只想显示特定行数，表里有107行，只显示 32，33行，在要查询的行-1=偏移量，加上查询的条目数
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

# MySQL8.0新特性：LIMIT ... OFFSET，LIMIT [条目数] OFFSET [偏移量]
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;