爱站网seo培训,印刷电商网站开发,龙华网站建设销售员,移动网站推广文章目录 什么是SQLSQL 分类SQL语言的规则与规范阿里巴巴MySQL命名规范数据导入指令 显示表结构 DESC基本的SELECT语句SELECTSELECT ... FROM列的别名 AS去除重复行 DISTINCT空值参与运算着重号查询常数过滤数据 WHERE练习 运算符算术运算符加减符号乘除符号取模符号 符号比较运… 文章目录 什么是SQLSQL 分类SQL语言的规则与规范阿里巴巴MySQL命名规范数据导入指令 显示表结构 DESC基本的SELECT语句SELECTSELECT ... FROM列的别名 AS去除重复行 DISTINCT空值参与运算着重号查询常数过滤数据 WHERE练习 运算符算术运算符加减符号乘除符号取模符号 符号比较运算符等号运算符不等于运算符 非符号类型运算符为空运算符不为空运算符最小值运算符LEAST最大值运算符GREATEST区间运算符BETWEEN AND模糊查询运算符LIKE正则表达式运算符REGEXP 逻辑运算符逻辑非运算符逻辑与运算符逻辑或运算符逻辑异或运算符XOR 练习 运算符的优先级排序与分页排序数据排序规则单列排序多列排序 分页实现规则拓展 练习 什么是SQL
SQL是一种标准
SQLStructured Query Language结构化查询语言是使用关系模型的数据库应用语言 与数据直接打交道 由 IBM 上世纪70年代开发出来。后由美国国家标准局ANSI开始着手制定SQL标准先后有 SQL-86 SQL-89 SQL-92 SQL-99 等标准。SQL 有两个重要的标准分别是 SQL92 和 SQL99它们分别代表了 92 年和 99 年颁布的 SQL 标准我们今天使用的 SQL 语言依然遵循这些标准。不同的数据库生产厂商都支持SQL语句但都有特有内容。
SQL 分类
SQL语言在功能上主要分为如下3大类 DDLData Definition Languages、数据定义语言
这些语句定义了不同的数据库、表、视图、索引等数据库对象还可以用来创建、删除、修改数据库和数据表的结构。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DMLData Manipulation Language、数据操作语言
用于添加、删除、更新和查询数据库记录并检查数据完整性。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。SELECT是SQL语言的基础最为重要。
DCLData Control Language、数据控制语言
用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。 因为查询语句使用的非常的频繁所以很多人把查询语句单拎出来一类DQL数据查询语言 还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL Transaction Control Language事务控制语言。 SQL语言的规则与规范
SQL 可以写在一行或者多行。为了提高可读性各子句分行写必要时使用缩进每条命令以 ; 或 \g 或 \G 结束关键字不能被缩写也不能分行关于标点符号 必须保证所有的()、单引号、双引号是成对结束的必须使用英文状态下的半角输入方式字符串型和日期时间类型的数据可以使用单引号’ 表示列的别名尽量使用双引号 而且不建议省略as
SQL大小写规范 建议遵守
MySQL 在 Windows 环境下是大小写不敏感的MySQL 在 Linux 环境下是大小写敏感的 数据库名、表名、表的别名、变量名是严格区分大小写的关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。 推荐采用统一的书写规范 数据库名、表名、表别名、字段名、字段别名等都小写SQL 关键字、函数名、绑定变量等都大写
命名规则暂时了解
数据库、表名不得超过30个字符变量名限制为29个 必须只能包含 A–Z, a–z, 0–9, _共63个字符 数据库名、表名、字段名等对象名中间不要包含空格同一个MySQL软件中数据库不能同名同一个库中表不能重名同一个表中字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用请在SQL语句中使用**** 着重号引起来保持字段名和类型的一致性在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数那在另一个表里可就别变成字符型了
阿里巴巴MySQL命名规范
阿里巴巴《Java开发手册》之MySQL字段命名
【 强制 】表名、字段名必须使用小写字母或数字禁止出现数字开头禁止两个下划线中间只出现数字。数据库字段名的修改代价很大因为无法进行预发布所以字段名称需要慎重考虑。 正例aliyun_adminrdc_configlevel3_name反例AliyunAdminrdcConfiglevel_3_name 【 强制 】禁用保留字如 desc、range、match、delayed 等请参考 MySQL 官方保留字。【 强制 】表必备三字段id, gmt_create, gmt_modified。 说明其中 id 必为主键类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create,gmt_modified 的类型均为 DATETIME 类型前者现在时表示主动式创建后者过去分词表示被动式更新 【 推荐 】表的命名最好是遵循 “业务名称_表的作用”。 正例alipay_task 、 force_project、 trade_config 【 推荐 】库名与应用名称尽量一致。【参考】合适的字符存储长度不但节约数据库表空间、节约索引存储更重要的是提升检索速度。 正例无符号值可以避免误存负数且扩大了表示范围。 数据导入指令
mysql source d:\mysqldb.sql或者通过图形化管理工具进行执行SQL脚本
显示表结构 DESC
使用DESCRIBE 或 DESC 命令表示表结构
DESCRIBE employees;
--或
DESC employees;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
--------------------------------------------------------其中各个字段的含义分别解释如下
Field表示字段名称。Type表示字段类型Null表示该列是否可以存储NULL值。Key表示该列是否已编制索引。PRI表示该列是表主键的一部分UNI表示该列是UNIQUE索引的一部分MUL表示在列中某个给定值允许出现多次。Default表示该列是否有默认值如果有那么值是多少。Extra表示可以获取的与给定列有关的附加信息例如AUTO_INCREMENT等。
基本的SELECT语句 SELECT
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句SELECT … FROM 语法
SELECT 标识选择哪些列
FROM 标识从哪个表中选择选择全部列
SELECT * FROM departments;一般情况下除非需要使用表中所有的字段数据最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是当不知道所需要的列的名称时可以通过它获取它们。在生产环境下不推荐你直接使用 SELECT * 进行查询。 选择特定的列
SELECT department_id, location_id FROM departments;MySQL中的SQL语句是不区分大小写的因此SELECT和select的作用是相同的但是许多开发人员习惯将关键字大写、数据列和表名小写读者也应该养成一个良好的编程习惯这样写出来的代码更容易阅读和维护。 列的别名 AS
重命名一个列紧跟列名也可以在列名和别名之间加入关键字AS别名使用双引号以便在别名中包含空格或特殊的字符并区分大小写。
SELECT department_id AS id, department_name NAME FROM departments;AS 可以省略
SELECT department_id AS 部门ID, department_name 部门名称 FROM departments;紧跟列名也可以在列名和别名之间加入关键字AS别名使用双引号以便在别名中包含空格或特殊的字符并区分大小写。
去除重复行 DISTINCT
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;默认情况下查询会返回全部行包括重复行。在SELECT语句中使用关键字DISTINCT去除重复行
SELECT DISTINCT department_id,salary
FROM employees;SELECT DISTINCT 列名称1列名称2…组合属性去重就是这几个列完全一模一样才算重复元素SELECT 列名称1DISTINCT 列名称2则会报错
空值参与运算
所有运算符或列值遇到null值运算的结果都为null
SELECT employee_id,salary,commission_pct,12 * salary * (1 commission_pct) annual_sal
FROM employees;这里你一定要注意在 MySQL 里面 空值不等于空字符串。一个空字符串的长度是 0而一个空值的长度是空。而且在 MySQL 里面空值是占用空间的 着重号
mysql SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ORDER at
line 1SELECT * FROM order;
----------------------
| order_id | order_name |
----------------------
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
----------------------
3 rows in set (0.00 sec)我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同请在SQL语句中使用一对着重号引起来。
查询常数 SELECT 查询还可以对常数进行查询。对的就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的而不是从数据表中动态取出的。 你可能会问为什么我们还要对常数进行查询呢SQL 中的 SELECT 语法的确提供了这个功能一般来说我们只从一个表中查询数据通常不需要增加一个固定的常数列但如果我们想整合不同的数据源用常数列作为这个表的标记就需要查询常数。
比如说我们想对 employees 数据表中的员工姓名进行查询同时增加一列字段 corporation 这个字段固定值为“尚硅谷”可以这样写
SELECT 尚硅谷 as corporation, last_name FROM employees;过滤数据 WHERE
语法
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件使用WHERE 子句将不满足条件的行过滤掉WHERE子句紧随 FROM子句
查询部门ID为90的员工信息员工ID员工姓名部门ID
SELECT employee_id AS 员工ID, last_name员工姓名, job_id AS 工作类型, department_id AS 部门ID
FROM employees
WHERE department_id 90 ;练习
# 1.查询员工12个月的工资总和并起别名为ANNUAL SALARY
SELECT 12*salary*(1IFNULL(commission_pct,0)) AS ANNUAL SALARY FROM employees;
# 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id FROM employees;
# 3.查询工资大于12000的员工姓名和工资
SELECT last_name, salary FROM employees WHERE salary 12000;
# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id FROM employees WHERE employee_id 176;
# 5.显示表 departments 的结构并查询其中的全部数据
DESC departments;
SELECT * FROM departments;运算符
算术运算符
算术运算符主要用于数学运算其可以连接运算符前后的两个数值或表达式对数值或表达式进行加、减-、乘*、除/和取模%运算 加减符号
SELECT 100, 100 0, 100 - 0, 100 50, 100 50 -30, 100 35.5, 100 - 35.5
FROM DUAL;
-----------------------------------------------------------------------
| 100 | 100 0 | 100 - 0 | 100 50 | 100 50 -30 | 100 35.5 | 100 - 35.5 |
-----------------------------------------------------------------------
| 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 |
-----------------------------------------------------------------------
SELECT 1 1, 100 1, 100 A,CONCAT(1,101);1 1 100 1 100 A CONCAT(1,101)
------ --------- --------- ---------------
2 101 100 1101 一个整数类型的值对整数进行加法和减法操作结果还是一个整数一个整数类型的值对浮点数进行加法和减法操作结果是一个浮点数加法和减法的优先级相同进行先加后减操作与进行先减后加操作的结果是一样的在Java中的左右两边如果有字符串那么表示字符串的拼接。但是在MySQL中只表示数值相加。如果遇到非数值类型先尝试转成数值如果转失败就按0计算。补充MySQL中字符串拼接要使用字符串函数CONCAT()实现 1001’为101 发生了隐式转换100 ‘A’ 100 ,因为A转换不了为数值所以为0 乘除符号
SELECT 100 ,100 * 1 , 100 * 1.0 , 100 / 1.0 , 100 / 2 , 100 2 * 5 / 2 ,100 /3 , 100 DIV 0;
100 100 * 1 100 * 1.0 100 / 1.0 100 / 2 100 2 * 5 / 2 100 /3 100 DIV 0
------ ------- --------- --------- ------- --------------- ------- -----------
100 100 100.0 100.0000 50.0000 105.0000 33.3333 (NULL)一个数乘以整数1和除以整数1后仍得原数一个数乘以浮点数1和除以浮点数1后变成浮点数数值与原数相等一个数除以整数后不管是否能除尽结果都为一个浮点数一个数除以另一个数除不尽时结果为一个浮点数并保留到小数点后4位乘法和除法的优先级相同进行先乘后除操作与先除后乘操作得出的结果相同。在数学运算中0不能用作除数在MySQL中一个数除以0为NULL。 取模符号
SELECT 12 % 3, 12 MOD 5, -12%5, -12 MOD -5 FROM DUAL;
12 % 3 12 MOD 5 -12%5 -12 mod -5
------ -------- ------ ------------
0 2 -2 -2符号比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较**比较的结果为真则返回1比较的结果为假则返回0其他情况则返回NULL。**比较运算符经常被用来作为SELECT查询语句的条件来使用返回符合条件的结果记录。 等号运算符
SELECT 1 1, 1 1, 1 0, a a, (5 3) (2 6), NULL , NULL NULL;
1 1 1 1 1 0 a a (5 3) (2 6) NULL NULL NULL
------ ------- ------ --------- ----------------- --------- ------------
1 1 0 1 1 (NULL) (NULL)
SELECT 1 2, 0 abc, 1 abc FROM dual;
1 2 0 abc 1 abc
------ --------- -----------0 1 0
SELECT 1 1, 1 0, a a, (5 3) (2 6), NULL, NULL NULL FROM dual;
1 1 1 0 a a (5 3) (2 6) NULL NULL NULL
--------- ------- ----------- ------------------- ----------- --------------
1 0 1 1 0 1等号运算符判断等号两边的值、字符串或表达式是否相等如果相等则返回1不相等则返回0。在使用等号运算符时遵循如下规则 如果等号两边的值、字符串或表达式都为字符串则MySQL会按照字符串进行比较其比较的是每个字符串中字符的ANSI编码是否相等。如果等号两边的值都是整数则MySQL会按照整数来比较两个值的大小。如果等号两边的值一个是整数另一个是字符串则MySQL会将字符串转化为数字进行比较。 0 ‘abc’ abc转为数值失败变成0 如果等号两边的值、字符串或表达式中有一个为NULL则比较结果为NULL。 对比SQL中赋值符号使用 :
不等于运算符
SELECT 1 1, 1 ! 2, a ! b, (34) (26), a ! NULL, NULL NULL;
1 1 1 ! 2 a ! b (34) (26) a ! NULL NULL NULL
------ ------ ---------- -------------- ----------- --------------
0 1 1 1 (NULL) (NULL)不等于运算符和!用于判断两边的数字、字符串或者表达式的值是否不相等如果不相等则返回1相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL或两边都为NULL则结果为NULL。
非符号类型运算符 为空运算符
SELECT NULL IS NULL, ISNULL(NULL), ISNULL(a), 1 IS NULL;
NULL IS NULL ISNULL(NULL) ISNULL(a) 1 IS NULL
------------ ------------ ----------- -----------
1 1 0 0
#查询commission_pct等于NULL。比较如下的四种写法
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct NULL;空运算符IS NULL或者ISNULL判断一个值是否为NULL如果为NULL则返回1否则返回0。
不为空运算符
SELECT NULL IS NOT NULL, a IS NOT NULL, 1 IS NOT NULL, NULL NULL;
NULL IS NOT NULL a IS NOT NULL 1 IS NOT NULL NULL NULL
---------------- --------------- ------------- --------------
0 1 1 (NULL)
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);非空运算符IS NOT NULL判断一个值是否不为NULL如果不为NULL则返回1否则返回0。
最小值运算符LEAST
SELECT LEAST (1,0,2), LEAST(b,a,c), LEAST(1,NULL,2);
LEAST (1,0,2) LEAST(b,a,c) LEAST(1,NULL,2)
------------- ------------------ -----------------
0 a (NULL)语法格式为LEAST(值1值2…值n)。其中“值n”表示参数列表中有n个值。在有两个或多个参数的情况下返回最小值。由结果可以看到当参数是整数或者浮点数时LEAST将返回其中最小的值当参数为字符串时返回字母表中顺序最靠前的字符当比较值列表中有NULL时不能判断大小返回值为NULL。
最大值运算符GREATEST
SELECT GREATEST(1,0,2), GREATEST(b,a,c), GREATEST(1,NULL,2);
GREATEST(1,0,2) GREATEST(b,a,c) GREATEST(1,NULL,2)
--------------- --------------------- --------------------
2 c (NULL)语法格式为GREATEST(值1值2…值n)。其中n表示参数列表中有n个值。当有两个或多个参数时返回值为最大值。假如任意一个自变量为NULL则GREATEST()的返回值为NULL。
区间运算符BETWEEN AND
SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, b BETWEEN a AND c;
1 BETWEEN 0 AND 1 10 BETWEEN 11 AND 12 b BETWEEN a AND c
----------------- -------------------- -------------------------
1 0 1BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN AAND B此时当C大于或等于A并且C小于或等于B时结果为1否则结果为0。BETWEEN …AND… 是闭合区间是包括两个边界的
SELECT a IN (a,b,c), 1 IN (2,3), NULL IN (a,b), a IN (a, NULL), NULL IN (a, NULL);
a IN (a,b,c) 1 IN (2,3) NULL IN (a,b) a IN (a, NULL) NULL IN (a, NULL)
-------------------- ---------- ----------------- ------------------ ---------------------1 0 (NULL) 1 (NULL)IN运算符用于判断给定的值是否是IN列表中的一个值如果是则返回1否则返回0。如果给定的值为NULL则结果为NULL
SELECT a NOT IN (a,b,c), 1 NOT IN (2,3);
a NOT IN (a,b,c) 1 NOT IN (2,3)
------------------------ ----------------0 1NOT IN运算符用于判断给定的值是否不是IN列表中的一个值如果不是IN列表中的一个值则返回1否则返回0。 0。如果给定的值或者匹配条件为NULL则返回结果为NULL。
模糊查询运算符LIKE
LIKE运算符通常使用如下通配符
“%”匹配0个或多个字符。
“_”只能匹配一个字符。SELECT NULL LIKE abc, abc LIKE NULL;
NULL LIKE abc abc LIKE NULL
--------------- -----------------
(NULL) (NULL)
SELECT first_name
FROM employees
WHERE first_name LIKE S%;SELECT last_name
FROM employees
WHERE last_name LIKE _o%;LIKE运算符主要用来匹配字符串通常用于模糊匹配如果满足条件则返回1否则返回
ESCAPE
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;回避特殊符号的使用转义符。例如将[%]转为[ %]、[]转为[ ]然后再加上[ESCAPE‘$’]即可如果使用\表示转义要省略ESCAPE。如果不是\则要加上ESCAPE。
正则表达式运算符REGEXP
REGEXP运算符用来匹配字符串语法格式为 expr REGEXP 匹配条件 。如果expr满足匹配条件返回 1
SELECT shkstart REGEXP ^s, shkstart REGEXP t$, shkstart REGEXP hk;
shkstart REGEXP ^s shkstart REGEXP t$ shkstart REGEXP hk
---------------------- ---------------------- ------------------------1 1 1SELECT atguigu REGEXP gu.gu, atguigu REGEXP [ab];atguigu REGEXP gu.gu atguigu REGEXP [ab]
------------------------ -------------------------1 11‘^’匹配以该字符后面的字符开头的字符串。 2‘$’匹配以该字符前面的字符结尾的字符串。 3‘.’匹配任何一个单字符。 4“[…]”匹配在方括号内的任何字符。例如“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围使用一个‘-’。“[a-z]”匹配任何字母 而“[0-9]”匹配任何数字。 5‘’匹配零个或多个在它前面的字符。例如“x”匹配任何数量的‘x’字符“[0-9]”匹配任何数量的数字而“”匹配任何数量的任何字符。 逻辑运算符 逻辑非运算符
SELECT NOT 1, NOT 0, NOT(11), NOT !1, NOT NULL;
NOT 1 NOT 0 NOT(11) NOT !1 NOT NULL
------ ------ -------- ------ ----------
0 1 0 1 (NULL)逻辑非NOT或!运算符表示当给定的值为0时返回1当给定的值为非0值时返回0当给定的值为NULL时返回NULL。
逻辑与运算符
SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
1 AND -1 0 AND 1 0 AND NULL 1 AND NULL
-------- ------- ---------- ------------1 0 0 (NULL)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary 10000
AND job_id LIKE %MAN%;逻辑与AND或运算符是当给定的所有值均为非0值并且都不为NULL时返回1当给定的一个值或者多个值为0时则返回0否则返回NULL
逻辑或运算符
SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
1 OR -1 1 OR 0 1 OR NULL 0 || NULL NULL || NULL
------- ------ --------- --------- --------------1 1 1 (NULL) (NULL)#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees WHERE NOT (salary 9000 AND salary 12000);
SELECT employee_id,salary FROM employees WHERE salary 9000 OR salary 12000;
SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND 12000;SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary 10000 OR job_id LIKE %MAN%;逻辑或OR或||运算符是当给定的值都不为NULL并且任何一个值为非0值时则返回1否则返回0当一个值为NULL并且另一个值为非0值时返回1否则返回NULL当两个值都为NULL时返回NULL。 注意OR可以和AND一起使用但是在使用时要注意两者的优先级由于AND的优先级高于OR因此先对AND两边的操作数进行操作再与OR中的操作数结合。 逻辑异或运算符XOR
SELECT 1 XOR -1 , 1 XOR 0 , 0 XOR 0 , 1 XOR NULL , 1 XOR 1 XOR 1 , 0 XOR 0 XOR 0;
1 XOR -1 1 XOR 0 0 XOR 0 1 XOR NULL 1 XOR 1 XOR 1 0 XOR 0 XOR 0
-------- ------- ------- ---------- ------------- ---------------
0 1 0 (NULL) 1 0
#要么部门id是10或者20工资小于8000 要么工资大于8000 部门不是10或者20
select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary 8000;逻辑异或XOR运算符是当给定的值中任意一个值为NULL时则返回NULL如果两个非NULL的值都是0或者都不等于0时则返回0如果一个值为0另一个值不为0时则返回1。
练习
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT t1.last_name,t1.salary
FROM employees AS t1
where t1.salary NOT between 5000 and 12000;SELECT last_name, salary
FROM employees
WHERE salary 5000 OR salary 12000;
# 2.选择在20或50号部门工作的员工姓名和部门号
select t1.last_name,t1.department_id
FROM employees AS t1
where t1.department_id in (20,50);SELECT last_name, department_id
FROM employees
WHERE department_id 20 OR department_id 50;
# 3.选择公司中没有管理者的员工姓名及job_id
select t1.last_name, t1.job_id
FROM employees AS t1
where t1.manager_id is null;# 4.选择公司中有奖金的员工姓名工资和奖金级别
select t1.last_name,t1.salary,t1.commission_pct
FROM employees AS t1
where t1.commission_pct is not null;
# 5.选择员工姓名的第三个字母是a的员工姓名
select t1.last_name
FROM employees AS t1
where t1.last_name like __a%;
# 6.选择姓名中有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE %a%k% OR last_name LIKE %k%a%;SELECT t1.last_name
FROM employees AS t1
WHERE t1.last_name LIKE %a% and t1.last_name LIKE %k%;
# 7.显示出表 employees 表中 first_name 以 e结尾的员工信息
SELECT t1.first_name
FROM employees AS t1
where t1.first_name like %e;SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name REGEXP e$;
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
select t1.last_name,t1.job_id
FROM employees AS t1
where t1.department_id between 80 and 100
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT t1.last_name,t1.salary,t1.manager_id
FROM employees AS t1
where manager_id in (100,101,110);运算符的优先级 数字编号越大优先级越高优先级高的运算符先进行计算。可以看到赋值运算符的优先级最低使用“()”括起来的表达式的优先级最高
排序与分页
排序数据
排序规则
使用 ORDER BY 子句排序
ASCascend: 升序默认是升序DESCdescend:降序ORDER BY 子句在SELECT语句的结尾
单列排序
#按照hire_date升序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
#按照hire_date降序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
#按照employee_id升序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY employee_id ;
#按照别名进行排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;被排序的字段不一定要在SELECT中出现ORDER可以使用别名进行排序 因为先将数据查出来才进行排序所以SELECT中的别名ORDER中可以识别
SELECT employee_id, last_name, salary*12 annsal
WHERE annsal IS NOT NULL
FROM employees
ORDER BY annsal;
SELECT employee_id, last_name, salary*12 annsal WHERE annsal is not null FROM employees ORDER BY annsal LIMIT 0, 1000错误代码 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near FROM employees
ORDER BY annsal LIMIT 0, 1000 at line 3SELECT t1.employee_id, t1.last_name, t1.salary*12 annsal
FROM employees t1
WHERE t1.employee_id IS NOT NULL
ORDER BY t1.employee_idWHERE 中不能使用SELECT中的别名因为先通过WHERE中的条件进行过滤出数据WHERE中可以使用FROM中的别名 先执行FROM从哪来数据再执行WHERE进行过滤其次执行SELECT选出数据最后执行ORDER进行排序 WHERE要在FROM后面ORDER在WHERE后面
多列排序
#先对department_id升序排序然后对 salary 降序排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;可以使用不在SELECT列表中的列排序。在对多列进行排序的时候首先排序的第一列必须有相同的列值才会对第二列进行排序。如果第一列数据中所有值都是唯一的将不再对第二列进行排序
分页
实现规则
分页原理
所谓分页显示就是将数据库中的结果集一段一段显示出来需要的条件。
MySQL中使用 LIMIT 实现分页
格式
LIMIT [位置偏移量,] 行数 --前10条记录
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录
SELECT * FROM 表名 LIMIT 20,10;MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”意思是获取从第5条记录开始后面的3条记录和“LIMIT4,3;”返回的结果相同。 位置偏移量从0开始并且LIMIT NM是包括N的
SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;注意LIMIT 子句必须放在整个SELECT语句的最后使用 LIMIT 的好处 约束返回结果的数量可以 减少数据表的网络传输量 也可以提升查询效率 。如果我们知道返回结果只有1 条就可以使用 LIMIT 1 告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表只需要检索到一条符合条件的记录即可返回
拓展
在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字而且需要放到 SELECT 语句的最后面。 如果是 SQL Server 和 Access需要使用 TOP 关键字比如
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC如果是 DB2使用 FETCH FIRST 5 ROWS ONLY 这样的关键字
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY如果是 Oracle你需要基于 ROWNUM 来统计行数
SELECT rownum,last_name,salary FROM employees WHERE rownum 5 ORDER BY salary DESC;需要说明的是这条语句是先取出来前 5 条数据行然后再按照 hp_max 从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。我会在后面讲到子查询你可以使用
SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum 10;练习
#1. 查询员工的姓名和部门号和年薪按年薪降序,按姓名升序显示
select last_name,salary *12*(1IFNULL(commission_pct,0)) AS annsal
FROM employees
ORDER BY annsal DESC, last_name ASC;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资按工资降序显示第21到40位置的数据
select last_name,salary
FROM employees
where salary not between 8000 and 17000
order by salary desc
limit 20,20;#第21个因为从0开始所以偏移量是20
#3. 查询邮箱中包含 e 的员工信息并先按邮箱的字节数降序再按部门号升序
SELECT last_name,salary
FROM employees
WHERE email like %e%
order by LENGTH(email) desc,department_id asc;
