MySQL学习笔记7
查询数据
查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用,也是最重要的操作。用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。
1:查询语句的基本语法
2:在单表上查询数据
3:使用集合函数查询数据
4:多表上联合查询
5:子查询
6:合并查询结果
7:为表和字段取别名
8:使用正则表达式查询
创建名为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,'Anc',15,'男','England');
插入后employee表的内容如下:
num | d_id | name | age | sex | homeaddr |
---|---|---|---|---|---|
1 | 1001 | 张三 | 26 | 男 | 北京市海定区 |
2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
4 | 1004 | Anc | 15 | 男 | England |
创建work表:
CREATE TABLE work(
id INT,
name VARCHAR(20),
sex VARCHAR(4),
info VARCHAR(50)
);
插入三条记录:
INSERT INTO work VALUES(1001,'THX',NULL,NULL);
INSERT INTO work VALUES(1002,'CCH',NULL,NULL);
INSERT INTO work VALUES(1003,'ZKJ',NULL,'student');
创建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);
创建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,'Lda',85);
INSERT INTO computer_stu VALUES(1008,'Jeck',70);
创建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);
创建info表:
CREATE TABLE info(
id INT,
name VARCHAR(20)
);
插入记录:
INSERT INTO info VALUES(1,'Arsc');
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:基本查询语句
MySQL中可以使用SELECT语句来来查询数据。根据查询的条件不同,数据库系统会找到不同的数据。通过SELECT语句可以很方便的获取所需的信息。
MySQL中,SELECT的基本语法形式如下:
SELECT 属性列表
FROM 表名和视图列表
[ WHERE 条件表达式1 ]
[ GROUP BY 属性名1 [ HAVING 条件表达式2 ] ]
[ ORDER BY 属性名2 [ ASC | DESC ]]
基本的条件查询语句:
SELECT num,d_id,name,age,sex,homeaddr
FROM employee
WHERE age<26
ORDER BY d_id DESC;
查询结果:
num | d_id | name | age | sex | homeaddr |
---|---|---|---|---|---|
4 | 1004 | Anc | 15 | 男 | England |
3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
2:在单表上查询数据
单表查询是指从一张表中查询所需要的数据
查询条件表
查询条件 | 符号或关键字 |
---|---|
指定范围 | BETWEEN AND 、NOT BETWEEN AND |
指定集合 | IN 、NOT IN |
匹配字符 | LIKE 、NOT LIKE |
是否为空值 | IS NULL 、IS NOT NULL |
多个查询条件 | AND 、OR |
2.1查询指定记录
SELECT语句中可以设置查询条件。用户可以根据自己的需要来设置查询条件,按条件进行查询。WHERE语句可以用来指定查询条件。
SELECT * FROM employee WHERE d_id=1001;
2.2带IN关键字的查询
IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中,则满足查询条件,该记录将被查询出来。如果不在集合中,则不被查询出来。其语法规则为:
[ NOT ] IN (元素1,元素2,、、、,元素n )
SELECT * FROM employee WHERE d_id IN (1001,1004);
2.3带BETWEEN AND的范围查询
其语法规则为:
[ NOT ] BETWEEN 取值1 AND 取值2
SELECT * FROM employee WHERE age BETWEEN 15 AND 25;
2.4带LIKE的字符匹配查询
LIKE关键字可以匹配字符串是否相等。其语法规则为:
[ NOT ] LIKE ‘字符串’
SELECT * FROM employee WHERE name LIKE 'Anc';
SELECT * FROM employee WHERE homeaddr LIKE '北京%';
SELECT * FROM employee WHERE name LIKE 'A_c';
%代表任意字符
_代表一个字符
2.5查询空值
IS NULL关键字可以判断字段的值是否为控制(NULL)。其语法规则如下:
IS [ NOT ] NULL
SELECT * FROM work WHERE info IS NULL;
2.6带AND的多条件查询
2.7带OR的多条件查询
2.8查询结果不重复
如果在表中的某些字段上没有唯一性约束,这些字段可能存在着重复的值。例如,employee表中的d_id字段就存在重复的情况。
SELECT DISTINCT d_id FROM employee;
2.9对查询结果排序
从表中查询出来的数据可能是无序的,或者其排列顺序不是用户所期望的顺序。可以使用ORDER BY 关键字对记录进行排序。其语法规则为:
ORDER BY 属性名 [ ASC | DESC ]
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY d_id ASC, age DESC;
查询结果为:
num | d_id | name | age | sex | homeaddr |
---|---|---|---|---|---|
1 | 1001 | 张三 | 26 | 男 | 北京市海定区 |
2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
4 | 1004 | Anc | 15 | 男 | England |
2.10分组查询
GROUP BY关键字可以将查询结果按某个字段或多个字段进行分组。字段中值相等的为一组。其语法规则为:
GROUP BY 属性名 [ HAVING 条件表达式 ][ WITH ROLLUP ]
SELECT * FROM employee GROUP BY sex;
查询结果:
num | d_id | name | age | sex | homeaddr |
---|---|---|---|---|---|
2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
1 | 1001 | 张三 | 26 | 男 | 北京市海定区 |
SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;
查询结果:
sex | GROUP_CONCAT(name) |
---|---|
女 | 李四 |
男 | 张三,王五,Anc |
SELECT sex,COUNT(sex) FROM employee GROUP BY sex;
查询结果:
sex | COUNT(sex) |
---|---|
女 | 1 |
男 | 3 |
SELECT sex,COUNT(sex) FROM employee
GROUP BY sex HAVING COUNT(sex)>=3;
查询结果:
sex | COUNT(sex) |
---|---|
男 | 3 |
SELECT * FROM employee GROUP BY d_id,sex;
num | d_id | name | age | sex | homeaddr |
---|---|---|---|---|---|
2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
1 | 1001 | 张三 | 26 | 男 | 北京市海定区 |
3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
4 | 1004 | Anc | 15 | 男 | England |
SELECT sex,COUNT(sex) FROM employee
GROUP BY sex WITH ROLLUP;
WITH ROLLUP起到汇总的作用,查询结果:
sex | COUNT(sex) |
---|---|
女 | 1 |
男 | 3 |
NULL | 4 |
2.11用LIMIT限制查询结果的数量
LIMIT是MySQL中的一个特殊关键字。其可以用来指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。
SELECT *FROM employee LIMIT 2;
只显示两条记录。如果不指定位置,显示前2条。
SELECT *FROM employee LIMIT 1,2;
从第2条记录开始,显示2条记录。
3:使用集合函数查询
集合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()。GROUP BY关键字通常需要与集合函数一起使用。
SELECT d_id ,COUNT(*) FROM employee GROUP BY d_id;
查询结果:
d_id | COUNT(*) |
---|---|
1001 | 2 |
1002 | 1 |
1004 | 1 |
SELECT num,SUM(score) FROM grade GROUP BY num;
显示结果:
num | SUM(score) |
---|---|
1001 | 350 |
1002 | 357 |
1003 | 358 |
4:连接查询
连接查询是将两个或者两个以上的表按某个条件连接起来,从中选取需要的数据。当不同的表中存在表示相同意义的字段时,可以通过该字段来连接这几个表。连接查询包括内连接查询和外连接查询。
4.1内连接查询
内连接查询是一种最常用的连接查询。内连接查询可以查询两个或两个以上的表。当两个表中存在表示相同意义的字段时,可以通过该字段来连接这几个表。当该字段的值相等时,就查询出该记录。
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 表名1 LEFT | RIGHT JOIN 表名2
ON 表名1.属性名1=表名2.属性名2;
LEFT JOIN以表名1为准,RIGHT JOIN以表名2为准。
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 | Anc | 1004 | 15 | 男 | NULL | NULL |
4.3复合条件连接查询
SELECT num,name,employee.d_id,age,sex,d_name,function
FROM employee,department
WHERE employee.d_id=department.d_id
AND age>24;
查询结果:
num | name | d_id | age | sex | d_name | function |
---|---|---|---|---|---|---|
1 | 张三 | 1001 | 26 | 男 | 销售部 | 负责产品销售 |
3 | 王五 | 1002 | 25 | 男 | 科研部 | 研发新产品 |
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;
查询结果:
num | name | d_id | age | sex | d_name | function |
---|---|---|---|---|---|---|
2 | 李四 | 1001 | 24 | 女 | 销售部 | 负责产品销售 |
3 | 王五 | 1002 | 25 | 男 | 科研部 | 研发新产品 |
1 | 张三 | 1001 | 26 | 男 | 销售部 | 负责产品销售 |
5:子查询
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以为外层查询语句提供查询条件。因为在特定情况下,一个查询语句的条件需要另一个查询语句来获取。通过子查询,可以实现多表之间的查询。子查询中可能包括IN、NOT IN、ALL、EXISTS、NOT EXISTS等关键字。子查询中还可能包含比较运算符,如“=”、“!=”、“>”、“<”等。
5.1带IN关键字的子查询
SELECT * FROM employee
WHERE d_id IN
(SELECT d_id FROM department);
5.2带比较运算符的子查询
子查询可以使用比较运算符。其在子查询中使用的非常广泛。如查询分数、年龄、价格、收入等。
找出获得二等奖学金的学生的 学号、姓名、分数
SELECT id,name,score FROM computer_stu
WHERE score<
(SELECT score FROM scholarship WHERE level=1)
AND score>=
(SELECT score FROM scholarship WHERE level=2);
查询结果:
id | name | score |
---|---|---|
1001 | Lily | 85 |
1003 | Jim | 87 |
1007 | Lda | 85 |
5.3带EXISTS关键字的子查询
EXISTS关键字表示存在。使用EXISTS关键字时,内层查询语句不返回查询的记录。二十返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值(True)。否则,将返回一个假值(Flase)。返回真值时,外层查询语句将进行查询。返回假值时,外层查询语句不进行查询。
SELECT * FROM employee
WHERE EXISTS
(SELECT d_name FROM department
WHERE d_id=1003);
5.4带ANY关键字的子查询
ANY关键字表示满足其中任一条件。使用ANY关键字时,只要满足内层查询语句返回的结果中的任何一个,就可以通过该条件来执行外层查询语句。
找出所有可以获得奖学金同学的信息
SELECT * FROM computer_stu
WHERE score>=ANY
(SELECT score FROM scholarship);
5.5带ALL关键字的子查询
ALL关键字表示满足所有条件。使用ALL关键字时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。
获得一等奖学金的同学信息
SELECT * FROM computer_stu
WHERE score>=ALL
(SELECT score FROM scholarship);
6:合并查询
合并查询结果是将多个SELECT语句的查询结果合并到一起。因为某种情况下,需要将几个SELECT语句查询出来的结果合并起来显示。
进行合并操作使用UNION和UNION ALL关键字。
使用UNION关键字时,数据库系统会将所有的查询结果合并到一起,然后去除掉相同的记录。而UNION ALL关键字则只是简单的合并到一起。其语法规则如下:
SELECT 语句1
UNION | UNION ALL
SELECT 语句2
UNION | UNION ALL、、、
SELECT 语句n;
SELECT d_id FROM employee
UNION
SELECT d_id FROM department;
7:为表和字段取别名
当表的名称特别长时,在查询中直接使用表名很不方便。这时可以为表取一个别名。
MySQL中为表取别名的基本形式如下:
表名 表的别名
SELECT * FROM department d
WHERE d.d_id=1001;
查询结果:
d_id | d_name | function | address |
---|---|---|---|
1001 | 销售部 | 负责产品销售 | 1号楼销售大厅 |
当查询数据时,MySQL会显示每个输出列的名称。默认的情况下,显示的列名是创建表时定义的列名。有时为了显示结果更加直观,需要一个更加直观的名字来表示这一列。为字段取别名的基本形式如下:
属性名 [ AS ] 别名
SELECT d_id department_id,d_name AS department_name
FROM department;
查询结果:
department_id | department_name |
---|---|
1003 | 生产部 |
1002 | 科研部 |
1001 | 销售部 |
8:使用正则表达式查询
正则表达式是用某种模式去匹配一类字符串的一个方式。例如,使用正则表达式可以查询出包含A、B、C其中任一字母的字符串。正则表达式的查询能力比通配字符串的查询能力更强大,而且更加的灵活。
MySQL中,使用REGEXP关键字来匹配查询正则表达式。其基本形式如下:
属性名 REGEXP ‘匹配方式’
有关正则表达式的模式字符说明参考另一篇博文
R中的grep、grepl、sub、gsub、regexpr、gregexpr等函数
8.1查询以特定字符或字符串开头的记录
使用字符“^”可以匹配以特定字符或字符串开头的记录。
SELECT * FROM info WHERE name REGEXP '^l';
查询结果:
id | name |
---|---|
5 | Lucy |
6 | Lily |
8.2查询以特定字符或字符串结尾的记录
使用字符“$”可以匹配以特定字符或字符串结尾的记录。
SELECT * FROM info WHERE name REGEXP 'c$';
查询结果:
id | name |
---|---|
1 | Arsc |
2 | Eric |
8.3用符号“.”来代替字符串中的任意一个字符
SELECT * FROM info WHERE name REGEXP '^L..y$';
查询结果:
id | name |
---|---|
5 | Lucy |
6 | Lily |
8.4匹配指定字符中的任意一个和指定字符以外的字符
使用方括号([])可以将需要查询字符组成一个字符集。只要记录中包含方括号中的任意字符,该记录将会被查询出来。例如,通过“[abc]”可以查询包含a、b、c这三个字母中任何一个的记录。
SELECT * FROM info WHERE name REGEXP '[ceo]';
查询结果:
id | name |
---|---|
1 | Arsc |
2 | Eric |
4 | Jack |
5 | Lucy |
7 | Tom |
11 | abc12 |
SELECT * FROM info WHERE name REGEXP '[^a-zA-Z]';
查询结果:
id | name |
---|---|
11 | abc12 |
12 | ad321 |
8.5匹配指定字符串
正则表达式可以匹配字符串。当表中的记录包含这个字符串时,就可以将该记录查询出来。如果指定多个字符串时,需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可。
SELECT * FROM info WHERE name REGEXP 'ic|uc|ab';
查询结果:
id | name |
---|---|
2 | Eric |
5 | Lucy |
10 | aaabd |
11 | abc12 |
17 | ababab |
8.6使用“*”和“+”来匹配多个字符
正则表达式中,“”和“+”都可以匹配多个该字符之前的字符。但是,“+”至少表示一个字符,而“”可以表示零个字符。
SELECT * FROM info WHERE name REGEXP 'a*c';
“*”表示将其前的字符进行0个或多个的匹配
查询结果:
id | name |
---|---|
1 | Arsc |
2 | Eric |
4 | Jack |
5 | Lucy |
11 | abc12 |
SELECT * FROM info WHERE name REGEXP 'a+c';
“+”匹配1或多个正好在它之前的那个字符
查询结果:
id | name |
---|---|
4 | Jack |
8.7使用{M}或者{M,N}来指定字符串连续出现的次数
正则表达式中,“字符串{M}”表示字符串连续出现M次;“字符串{M,N}”表示字符串连续出现至少M次,最多N次。
SELECT * FROM info WHERE name REGEXP 'ab{1,3}';
查询结果:
id | name |
---|---|
10 | aaabd |
11 | abc12 |
17 | ababab |