MYSQL-基本查询语句、运算符、排序与分页

SQL的分类

DDL：数据定义语言

CREATE ALTER DROP RENAME TRUNCATE

DML：数据操作语言

SELECT UPDATE INSERT DELETE

DCL：数据控制语言

COMMIT ROLLBACK SAVEPOINT GRANT REVOKE

基本sql语句

DUAL表

SELECT 1+2 sum FROM DUAL;

WHERE：查询条件

SELECT employee_id,last_name,salary FROM employees WHERE salary>12000;

NULL：查询员工的id，名字，薪水，年薪（绩效系数为NULL时当做0处理）

SELECT employee_id,last_name,salary,salary*(1+IFNULL(commission_pct,0))*12 年薪, commission_pct FROM employees ;

DESCRIBE、DESC：显示表结构

DESC employees;
DESCRIBE employees;

DISTINCT：查询去重后的数据

SELECT DISTINCT department_id 部门id FROM employees;

查询常量

SELECT employee_id,123 FROM employees;

AS：别名，可以省略

SELECT employee_id,last_name,salary*12 AS "ANNUAL SALARY" FROM employees;

算术运算符

加减乘除 + - * /

SELECT 34+'45' FROM DUAL;
SELECT 34-12 FROM DUAL;
SELECT 34*NULL FROM DUAL;#null值参与运算，结果为null
SELECT 34/12 FROM DUAL;

div ：整除，该运算符取商的整数部分，不四舍五入

SELECT 100 DIV 0 FROM DUAL;#分母如果为0，则结果为null
SELECT 100 DIV 23 FROM DUAL

% mod：取模，一个数字除以另一个数字的余数

SELECT 100%23,100 MOD 0 FROM DUAL;#分母如果为0，则结果为null

比较运算符

= <=> <> != < <= > >=

SELECT 1 = NULL,NULL = NULL FROM DUAL;# 只要有null参与判断，结果就为null
SELECT * FROM employees where employee_id <> 100;

# <=> ：安全等于，为NULL而生。
SELECT 1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a' FROM DUAL;
SELECT 1 <=> NULL, NULL <=> NULL FROM DUAL; # 0,1
SELECT * FROM employees where commission_pct <=> NULL;

IS NULL,IS NOT NULL,ISNULL

# 以下三种写法查询结果一致
SELECT * FROM employees where commission_pct <=> NULL;
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE ISNULL(commission_pct);

BETWEEN AND,NOT BETWEEN AND

SELECT * FROM employees WHERE salary BETWEEN 12000 AND 24000;
SELECT * FROM employees WHERE salary >= 12000 AND salary<=24000;
SELECT * FROM employees WHERE salary NOT BETWEEN 12000 AND 24000;

in,not in

SELECT * FROM departments WHERE department_id in(10,20,30);
SELECT * FROM departments WHERE department_id not in(10,20,30);

LIKE

SELECT * FROM departments WHERE department_name LIKE '%e%';
SELECT * FROM departments WHERE department_name LIKE '%e%a%';
SELECT * FROM departments WHERE department_name LIKE '_a%';
# _：通配符，\_：字符_
SELECT * FROM departments WHERE department_name LIKE '_\_a%';
#  escape '/' 表示 / 之后的 _ 不作为通配符了  *_:字符_
SELECT * FROM departments WHERE department_name LIKE '_*_a%'  ESCAPE '*';

REGEXP：正则表达式

操作符	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。
[…]	字符集合。匹配所包含的任意一个字符。
[^…]	负值字符集合。匹配未包含的任意字符
p1	p2
*	匹配前面的子表达式零次或多次
+	匹配前面的子表达式一次或多次
{n}	n 是一个非负整数。匹配确定的 n 次
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次

# 正则表达式
SELECT * FROM departments WHERE department_name REGEXP '^S';
SELECT * FROM departments WHERE department_name REGEXP 'e$';
SELECT * FROM departments WHERE department_name REGEXP '[a]';

逻辑运算符： OR || AND && NOT ! XOR

# 查询出两个查询条件所得交集之外的结果集
SELECT * FROM departments WHERE department_id<50 XOR department_id >10;
SELECT * FROM departments WHERE department_id<50 && department_id >10;
SELECT * FROM departments WHERE department_id<50 and department_id >10;
SELECT * FROM departments WHERE department_id<50 || department_id >200;
SELECT * FROM departments WHERE department_id<50 OR department_id >200;

位运算符： & | ^ ~ >> <<

SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
SELECT 10 & ~1 FROM DUAL;
#在一定范围内满足：每向左移动1位，相当于乘以2；每向右移动一位，相当于除以2。
SELECT 4 << 1 , 8 >> 1 FROM DUAL;

排序

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

ORDER BY：升序ASC，降序DESC

SELECT first_name,department_id,salary FROM employees WHERE department_id in (10,20,30,40,50,60) ORDER BY department_id DESC ,salary ASC;

分页

LIMIT

• 格式：WHERE … ORDER BY …LIMIT

# 查询第一页，每页10条数据
SELECT employee_id,department_id,salary FROM employees ORDER BY salary DESC LIMIT 0,10 ;
SELECT employee_id,department_id,salary FROM employees ORDER BY salary DESC LIMIT 10 ;
# 查询第三页
SELECT employee_id,department_id,salary FROM employees ORDER BY salary DESC LIMIT 30,10;
# 查询第45,46两条数据
SELECT employee_id,department_id,salary FROM employees ORDER BY salary DESC LIMIT 45,2;
SELECT employee_id,department_id,salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 45 ;