MySQL学习笔记7(查询数据)

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,homeaddrFROM employeeWHERE age<26ORDER 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 employeeGROUP 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 employeeGROUP 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,functionFROM employee,departmentWHERE 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,functionFROM employee LEFT JOIN departmentON 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,functionFROM employee,departmentWHERE employee.d_id=department.d_idAND 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,functionFROM employee,departmentWHERE employee.d_id=department.d_idORDER 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 employeeWHERE d_id IN(SELECT d_id FROM department);

5.2带比较运算符的子查询

子查询可以使用比较运算符。其在子查询中使用的非常广泛。如查询分数、年龄、价格、收入等。
找出获得二等奖学金的学生的 学号、姓名、分数

SELECT id,name,score FROM computer_stuWHERE 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 employeeWHERE EXISTS(SELECT d_name FROM departmentWHERE d_id=1003);

5.4带ANY关键字的子查询

ANY关键字表示满足其中任一条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。
找出所有可以获得奖学金同学的信息

SELECT * FROM computer_stuWHERE score>=ANY(SELECT score FROM scholarship);

5.5带ALL关键字的子查询

ALL关键字表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
获得一等奖学金的同学信息

SELECT * FROM computer_stuWHERE 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 employeeUNIONSELECT d_id FROM department;

7：为表和字段取别名

当表的名称特别长时，在查询中直接使用表名很不方便。这时可以为表取一个别名。
MySQL中为表取别名的基本形式如下：

表名 表的别名

SELECT * FROM department dWHERE d.d_id=1001;

查询结果：

d_id d_name function address 1001 销售部 负责产品销售 1号楼销售大厅

当查询数据时，MySQL会显示每个输出列的名称。默认的情况下，显示的列名是创建表时定义的列名。有时为了显示结果更加直观，需要一个更加直观的名字来表示这一列。为字段取别名的基本形式如下：

属性名 [ AS ] 别名

SELECT d_id department_id,d_name AS department_nameFROM 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