mysql之select-Chocolee
测试案例数据:1.employee表
CREATE TABLE employee(
num INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_id INT NOT NULL,
name VARCHAR(20),
age INT,
sex VARCHAR(4),
homeaddr VARCHAR(50)
);
INSERT INTO employee VALUES(NULL,1001,'张三',26,'男','北京市海淀区');
INSERT INTO employee VALUES(NULL,1001,'李四',24,'女','北京市昌平区');
INSERT INTO employee VALUES(NULL,1002,'王五',25,'男','湖南长沙市');
INSERT INTO employee VALUES(NULL,1004,'Aric',15,'男','England');
2.work表
CREATE TABLE work(
id INT,
name VARCHAR(20),
sex VARCHAR(4),
info VARCHAR(50)
);
INSERT INTO work VALUES(1001,'hjh',NULL,NULL);
INSERT INTO work VALUES(1002,'cch',NULL,NULL);
INSERT INTO work VALUES(1003,'zk',NULL,'student');
3.grade表
CREATE TABLE grade(
num INT(10) NOT NULL,
course VARCHAR(10) NOT NULL,
score FLOAT
);
INSERT INTO grade VALUES(1001,'数学',80);
INSERT INTO grade VALUES(1001,'语文',90);
INSERT INTO grade VALUES(1001,'英语',85);
INSERT INTO grade VALUES(1001,'计算机',95);
INSERT INTO grade VALUES(1002,'数学',88);
INSERT INTO grade VALUES(1002,'语文',90);
INSERT INTO grade VALUES(1002,'英语',89);
INSERT INTO grade VALUES(1002,'计算机',90);
INSERT INTO grade VALUES(1003,'数学',80);
INSERT INTO grade VALUES(1003,'语文',98);
INSERT INTO grade VALUES(1003,'英语',85);
INSERT INTO grade VALUES(1003,'计算机',95);
4.department表
CREATE TABLE department(
d_id INT NOT NULL PRIMARY KEY ,
d_name VARCHAR(20),
function VARCHAR(20),
address VARCHAR(50)
);
INSERT INTO department VALUES(1001, '科研部', '研发产品', '3号楼5层') ;
INSERT INTO department VALUES(1002, '生产部', '生产产品', '5号楼1层') ;
INSERT INTO department VALUES(1003, '销售部', '策划销售', '1号楼销售大厅') ;
5.computer_stu表
CREATE TABLE computer_stu(
id INT PRIMARY KEY,
name VARCHAR(20),
score FLOAT
);
INSERT INTO computer_stu VALUES(1001, 'Lily',85);
INSERT INTO computer_stu VALUES(1002, 'Tom',91);
INSERT INTO computer_stu VALUES(1003, 'Jim',87);
INSERT INTO computer_stu VALUES(1004, 'Aric',77);
INSERT INTO computer_stu VALUES(1005, 'Lucy',65);
INSERT INTO computer_stu VALUES(1006, 'Andy',99);
INSERT INTO computer_stu VALUES(1007, 'Ada',85);
INSERT INTO computer_stu VALUES(1008, 'Jeck',70);
6.scholarship表
CREATE TABLE scholarship(
level INT PRIMARY KEY,
score INT
);
INSERT INTO scholarship VALUES(1,90);
INSERT INTO scholarship VALUES(2,80);
INSERT INTO scholarship VALUES(3,70);
7.info表
CREATE TABLE info(
id INT,
name VARCHAR(20)
);
INSERT INTO info VALUES(1, 'Aric');
INSERT INTO info VALUES(2, 'Eric');
INSERT INTO info VALUES(4, 'Jack');
INSERT INTO info VALUES(5, 'Lucy');
INSERT INTO info VALUES(6, 'Lily');
INSERT INTO info VALUES(7, 'Tom');
INSERT INTO info VALUES(8, 'aaa');
INSERT INTO info VALUES(9, 'dadaaa');
INSERT INTO info VALUES(10, 'aaabd');
INSERT INTO info VALUES(11, 'abc12');
INSERT INTO info VALUES(12, 'ad321');
INSERT INTO info VALUES(17, 'ababab');
1.查询语句的基本语法
SELECT 属性列表
FROM 表名和视图列表
[ WHERE 条件表达式1 ]
[ GROUP BY 属性名1 [ HAVING 条件表达式2 ] ]
[ ORDER BY 属性名2 [ ASC|DESC ] ]
其中，“属性列表”参数表示需要查询的字段名；“表名和视图列表”参数表示从此处指定的表或视图中查询数据，表和视图可以有多个；“条件表达式1”参数指定查询条件；“属性名1”参数指按该字段中的数据进行分组；“条件表达式2”参数表示满足该表达式的数据才能输出；“属性名2”参数指该字段中的数据进行排序，排序方式由“ASC”和“DESC”两个参数指出；“ASC”参数表示按升序的顺序进行排序，这是默认参数；“DESC”参数表示按降序的顺序进行排序。
1.1简单SELECT语句来查询employee表
SELECT num,name,age,sex,homeaddr FROM employee;
1.2 包含WHERE子句和ORDER BY子句的SELECT语句
SELECTnum,d_id,name,age,sex,homeaddr
FROM employee
WHERE age= 、!= 、 、!> 、!<
指定范围
BETWEEN AND、NOT BETWEEN AND
指定集合
IN、NOT IN
匹配字符
LIKE、NOT LIKE
是否为空值
IS NULL、IS NOT NULL
多个查询条件
AND、OR
2.单表查询
单表查询时指从一张表中查询所需要的数据。查询数据时，可以从一张表中查询数据，也可以从多张表中同时查询数据。两者的查询方式上有一定的区别。因为单表查询只在一张表上进行操作，所以查询比较简单。
2.1 查询指定字段为name,age的数据
SELECT name,age FROM employee;
2.2 查询指定记录，按条件进行查询，查询d_id为1001的数据
SELECT * FROM employee WHERE d_id='1001';
2.3 带IN关键字的查询
语法：
[ NOT ] IN (元素1，元素2，…….元素n)
下面使用IN关键字进行查询，集合的元素为整数型数据
SELECT * FROM employee WHERE d_id IN (1001,1002);
集合的元素为字符型数据
SELECT * FROM employee WHERE name NOT IN ('张三','李四');
2.4 带BETWEEN AND的范围查询
语法：
[ NOT ] BETWEEN 取值1 AND 取值2
查询age字段取值从15到25.
SELECT * FROM employee WHERE age BETWEEN 15 AND 25;
查询age字段取值不在15到25之间的
SELECT * FROM employee WHERE age NOT BETWEEN 15 AND 25;
2.5 带LIKE的字符匹配查询
语法：
[ NOT ] LIKE ‘字符串’
使用LIKE关键字来匹配一个完整的字符串“Aric”
SELECT * FROM employee WHERE name LIKE 'aric';
使用LIKE关键字来匹配带有通配符“%”的字符串“北京%”
SELECT * FROM employee WHERE homeaddr LIKE '北京%';
使用NOT LIKE 关键字来查询不是姓李的所有人的记录
SELECT * FROM employee WHERE name NOT LIKE '李%';
2.6 查询空值
IS NULL 关键字可以用来判断字段的值是否为空值（NULL）。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。
语法：
IS [ NOT ] NULL
使用IS NULL 关键字来查询work表中info字段为空值的记录
SELECT * FROM work WHERE info IS NULL;
使用IS NOT NULL 关键字来查询work 表中info字段不为空值的记录
SELECT * FROM work WHERE info IS NOT NULL;
2.7 带AND的多条件查询
语法：
条件表达式1AND条件表达式2[ ….AND 条件表达式n ]
使用AND关键字来查询employee中d_id为1001，而且sex为男的记录
SELECT * FROM employee WHERE d_id=1001 AND sex LIKE '男';
在employee表中查询d_id小于1004，age小于26，而且sex为“男”的记录
SELECT * FROM employee WHERE d_id=3;
+------+------------+
| sex| COUNT(sex) |
+------+------------+
| 男 | 3 |
+------+------------+
以d_id字段和sex字段进行分组
SELECT * FROM employee GROUP BY d_id,sex;
以sex字段进行分组查询，使用COUNT()函数来计算每组的记录数，并且加上WITHROLLUP记录求和
SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;
+------+------------+
| sex| COUNT(sex) |
+------+------------+
| 女 | 1 |
| 男 | 3 |
| NULL | 4 |
+------+------------+
2.12 用LIMIT限制查询结果的数量
查询employee表的所有记录，但只显示前2条
SELECT * FROM employee LIMIT 2;
SELECT * FROM employee LIMIT 0,2 ;
+-----+------+--------+------+------+--------------------+
| num | d_id | name | age| sex| homeaddr |
+-----+------+--------+------+------+--------------------+
| 1 | 1001 | 张三 | 26 | 男 | 北京市海淀区 |
| 2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
+-----+------+--------+------+------+--------------------+
从第二条记录开始显示，并显示两条记录
SELECT * FROM employee LIMIT 1,2 ;
+-----+------+--------+------+------+--------------------+
| num | d_id | name | age| sex| homeaddr |
+-----+------+--------+------+------+--------------------+
| 2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
| 3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
+-----+------+--------+------+------+--------------------+
3.使用集合函数查询
3.1 COUNT()统计记录的条数函数
使用COUNT()函数统计employee表的记录数
SELECT COUNT(*) FROM employee;
统计不同d_id值得记录数，COUNT()函数和GROUP BY关键字一起使用
SELECT d_id,COUNT(*) FROM employee GROUP BY d_id;
3.2 SUM()求和函数
使用SUM()函数统计grade表中学号为1001的同学的成绩
SELECT num,SUM(score) FROM grade WHERE num='1001';
将grade表按照num字段进行分组，然后使用SUM()函数统计各分组的总成绩
SELECT num,SUM(score) FROM gradeGROUP BY num;
3.3 AVG()求平均函数
使用AVG()函数计算employee表中的平均年龄（age）
SELECT AVG(age) FROM employee;
使用AVG()函数计算grade表中不同科目的平均成绩
SELECT course,AVG(score) FROM grade GROUP BY course;
3.4 MAX()求最大值函数
使用MAX()函数查询employee表中的最大年龄（age）
SELECT MAX(age) FROM employee;
使用MAX()函数查询grade表中不同科目的最高成绩
SELECT course,MAX(score) FROM grade GROUP BY course;
使用MAX()函数查询work表中name字段的最大值
SELECT MAX(name) FROM work;
3.5 MIN()求最小值函数
使用MIN()函数查询employee表中的最小年龄
SELECT MIN(age) FROM employee;
使用MIN()函数查询grade表中不同科目的最低成绩
SELECT course,MIN(score) FROM grade GROUP BY course;
4.连接查询
连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。
4.1 内连接查询
使用内连接查询的方式查询employee表和department表。在执行内连接查询之前，先分别查查employee表和department表中的记录，以便比较
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee,department
WHERE employee.d_id=department.d_id;
+-----+--------+------+------+------+-----------+--------------+
| num | name | d_id | age| sex| d_name | function |
+-----+--------+------+------+------+-----------+--------------+
| 1 | 张三 | 1001 | 26 | 男 | 科研部 | 研发产品 |
| 2 | 李四 | 1001 | 24 | 女 | 科研部 | 研发产品 |
| 3 | 王五 | 1002 | 25 | 男 | 生产部 | 生产产品 |
+-----+--------+------+------+------+-----------+--------------+
4.2 外连接查询
语法：
SELECT 属性名列表
FROM 表名1LEFT|RIGHT JOIN 表名2
ON 表名1.属性名1=表名2.属性名2；
左连接查询：
使用左连接查询的方式查询employee表和department，两表通过d_id字段进行连接
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee LEFT JOIN department
ON employee.d_id=department.d_id;
+-----+--------+------+------+------+-----------+--------------+
| num | name | d_id | age| sex| d_name | function |
+-----+--------+------+------+------+-----------+--------------+
| 1 | 张三 | 1001 | 26 | 男 | 科研部 | 研发产品 |
| 2 | 李四 | 1001 | 24 | 女 | 科研部 | 研发产品 |
| 3 | 王五 | 1002 | 25 | 男 | 生产部 | 生产产品 |
| 4 | Aric | 1004 | 15 | 男 | NULL | NULL |
+-----+--------+------+------+------+-----------+--------------+
右连接查询：
使用右连接查询的方式查询employee表和department，两表通过d_id字段进行连接
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee RIGHT JOIN department
ON employee.d_id=department.d_id;
+------+--------+------+------+------+-----------+--------------+
| num| name | d_id | age| sex| d_name | function |
+------+--------+------+------+------+-----------+--------------+
| 1 | 张三 | 1001 | 26 | 男 | 科研部 | 研发产品 |
| 2 | 李四 | 1001 | 24 | 女 | 科研部 | 研发产品 |
| 3 | 王五 | 1002 | 25 | 男 | 生产部 | 生产产品 |
| NULL | NULL | NULL | NULL | NULL | 销售部 | 策划销售 |
+------+--------+------+------+------+-----------+--------------+
4.3 复合条件连接查询
在连接查询时，也可以增加其他的限制条件。通过多个条件的复合查询，可以使查询的结果更加准确
使用内连接查询的方式查询employee表和department表，并且employee表中的age字段的值必须大于24
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee,department
WHERE employee.d_id=department.d_id AND age>24;
使用内连接查询的方式查询employee表和department表，并且以employee表中的age字段的升序方式显示结果
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee,department
WHERE employee.d_id=department.d_id ORDER BY age ASC;
5.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。
5.1 带IN关键字的子查询
查询employee表中的记录，这些记录的d_id字段的值必须在department表中出现过
SELECT * FROM employee
WHERE d_id IN
(SELECT d_id FROM department);
#not in
SELECT * FROM employee
WHERE d_id NOT IN
(SELECT d_id FROM department);
5.2 带比较运算符的子查询
从computer_stu表中查询获得一等奖学金的学生的学号、姓名、分数。各个等级的奖学金的最低分存储在scholarship表中
SELECT id,name,score FROM computer_stu
WHERE score>=
(SELECT score FROM scholarship
WHERE level=1);
在department表中查询那些部门没有年龄24岁的员工，员工的年龄存储在employee表中，先查询一下employee表和department表，以便进行对比
SELECT d_id,d_name FROM department
WHERE d_id !=
(SELECT d_id FROM employee
WHERE age=24);
5.3 带EXISTS关键字的子查询
EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。
如果department表中存在d_id取值为1003的记录，则查询employee表的记录
SELECT * FROM employee
WHERE EXISTS
(SELECT d_name FROM department
WHERE d_id=1003);
SELECT * FROM employee
WHERE NOT EXISTS
(SELECT d_name FROM department
WHERE d_id=1003);
SELECT * FROM employee
WHERE EXISTS
(SELECT d_name FROM department
WHERE d_id=1004);
如果department表中存在d_id取值为1003的记录，则查询employee表中age大于24的记录
SELECT * FROM employee
WHERE age>24 AND EXISTS
(SELECT d_name FROM department
WHERE d_id=1003);
5.4 带ANY关键字的子查询
ANY关键字表示满足其中任一条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。
从computer_stu表中查询出哪些同学可以获得奖学金，奖学金的的信息存储在scholarship表中
SELECT * FROM computer_stu
WHERE score>=ANY
(SELECT score FROM scholarship);
5.5 带ALL关键字的子查询
ALL关键字表示满足所有条件，使用ALL关键字时，只有满足内层查询语句返回所有的结果，才可以执行外层查询语句。
从computer_stu表中查询出哪些同学可以获得一等奖学金，奖学金的信息存储在scholarship表中，先需要从scholarship表中查询出各种奖学金的最低分。然后，从computer_stu表中查询哪些人的分数高于所有奖学金的最低分
SELECT * FROM computer_stu
WHERE score>=ALL
(SELECT score FROM scholarship);
6.合并查询结果
合并查询结果是将多个SELECT语句的查询结果合并到一起，因为某种情况下，需要将几个SELECT语句查询出来的结果合并起来。
语法：
SELECT 语句1
UNION|UNION ALL
SELECT 语句2
UNION|UNION ALL…..
SELECT 语句n:
从department表和employee表中查询d_id字段的取值。然后通过UNION关键字将结果合并到一起
SELECT d_id FROM department
UNION
SELECT d_id FROM employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
从department表和employee表中查询d_id字段的取值，然后通过UNION ALL关键字将结果合并到一起
SELECT d_id FROM department
UNION ALL
SELECT d_id FROM employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1001 |
| 1001 |
| 1002 |
| 1004 |
+------+
7.为表和字段取别名
在查询时，可以为表和字段取一个别名。这个别名可以代替其指定的表和字段。
7.1 为表取别名
表名 表的别名
为department表取个别名d,然后查询表中d_id字段取值为1001的记录
SELECT * FROM department d where d.d_id=1001;
7.2 为字段取别名
属性名 [ AS ] 别名
为department表中d_id字段取名为department_id, d_name字段取名为department_name,在改名前先查看两个字段的数据
SELECT d_id AS department_id, d_name AS department_name FROM department;
为department表中d_id字段取名为department_id,d_name字段取名为department_name,而且department表取名为d,然后查询表中d_id字段取值为1001的记录
SELECT d.d_id AS department_id,d.d_name AS department_name,d.function,d.address
FROM department d
WHERE d.d_id=1001;
8.使用正则表达式查询
正则表达式是用某种模式去匹配一类字符串的一个方式。
语法：
属性名REGEXP ‘匹配方式’
正则表达式的模式字符表格
正则表达式的模式字符
含义
^
匹配字符串开始的部分
$
匹配字符串结束的部分
.
代表字符串中的任意一个字符，包括回车和换行
[字符集合]
匹配“字符集合”中的任何一个字符
[^字符集合]
匹配“字符集合”以外的任何一个字符
S1|S2|S3
匹配S1,S2,S3中的任意一个字符串
*
代表多个该符号之前的字符，包括0个和1个
+
代表多个该符号之前的字符，包括1个
字符串
字符串出现N次
字符串
字符串出现至少M次，最多N次
8.1 查询以特定字符或字符串开头的记录
从info表name字段中查询以字符“L”开头的记录
SELECT * FROM info WHERE name REGEXP '^L';
从info表name字段中查询以字符串“aaa”开头的记录
SELECT * FROM info WHERE name REGEXP '^aaa';
8.2 查询以特定字符或字符串结尾的记录
从info表name字段中查询以“c”结尾的记录
SELECT * FROM info WHERE name REGEXP 'c$';
从info表name字段中查询以“aaa”结尾的记录
SELECT * FROM info WHERE name REGEXP 'aaa$';
8.3 用符号“.”来代替字符串中的任意一个字符
从info表name字段中查询以字符“L”开头，以字符“y”结尾，中间有两个任意字符的记录
SELECT * FROM info WHERE name REGEXP '^L..y$';
8.4 匹配指定字符中的任意一个
从info表name字段中查询包含“c”、“e”、“o”这三个字符中任意一个的记录
SELECT * FROM info WHERE name REGEXP '';
从info表name字段中查询包含数字的记录
SELECT * FROM info WHERE name REGEXP '';
从info表name字段中查询包含数字或者字母a、b、c的记录
SELECT * FROM info WHERE name REGEXP '';
8.5 匹配指定字符以外的字符
从info表name字段中查询包含a到w字母和数字以外的字符的记录
SELECT * FROM info WHERE name REGEXP '[^0-9a-w]';
8.6 匹配指定字符串
从info表name字段中查询包含“ic”、“uc”、“ab”这三个字符串中任意一个的记录
SELECT * FROM info WHERE name REGEXP 'ic|uc|ab';
8.7 使用“*”和“+”来匹配多个字符
从info表name字段中查询字符“c”之前出现过“a”的记录
SELECT * FROM info WHERE name REGEXP 'a*c';
+------+-------+
| id | name|
+------+-------+
| 1 | Aric|
| 2 | Eric|
| 4 | Jack|
| 5 | Lucy|
| 11 | abc12 |
+------+-------+
SELECT * FROM info WHERE name REGEXP 'a+c';
+------+------+
| id | name |
+------+------+
| 4 | Jack |
+------+------+
8.8 使用{M}或者{M,N}来指定字符串连续出现的次数
从info表name字段中查询出现过“a”三次的记录
SELECT * FROM info WHERE name REGEXP 'a{3}';
从info表name字段中查询出现过“ab”最少一次，最多三次的记录
SELECT * FROM info WHERE name REGEXP 'ab{1,3}';
页:
[1]