mysql查询数据库名6_MySQL数据库操作（6）查询

准备工作

创建并进入数据库：

CREATE DATABASE`info`;USE `info`;

#创建学院表：

CREATE TABLE`department`(

`id`INT PRIMARY KEYAUTO_INCREMENT,

`name`VARCHAR(20) NOT NULL);

#创建学生表：

CREATE TABLE`student`(

`s_id`INT PRIMARY KEYAUTO_INCREMENT,

`name`VARCHAR(20) NOT NULL,

`dept_id`INT,FOREIGN KEY(`dept_id`) REFERENCES`department` (`id`)

);

#创建学生的详细信息表：

CREATE TABLE`stu_details`(

`s_id`INT PRIMARY KEY,

`age`INT,

`sex`CHAR(1),FOREIGN KEY(`s_id`) REFERENCES`student` (`s_id`)

);

#创建课程表：

CREATE TABLE`course`(

`id`INT PRIMARY KEYAUTO_INCREMENT,

`name`VARCHAR(20) NOT NULL);

#创建中间表：

CREATE TABLE `select`(

`s_id`INT,

`c_id`INT,PRIMARY KEY(`s_id`,`c_id`),FOREIGN KEY (`s_id`) REFERENCES`student`(`s_id`),FOREIGN KEY (`c_id`) REFERENCES`course`(`id`)

);

#查看当前存在的表：

SHOW TABLES;

#往学院表中添加数据：

INSERT INTO`department`(`name`)VALUES('English'),('Art'),('Computer'),('Alchemy');

#往学生表中添加数据：

INSERT INTO`student`(`name`,`dept_id`)VALUES('David',1),('Lucy',3),('Lily',3),('John',4),('Jack',2),('Alfred',3);

#往学生详细信息表中添加数据：

INSERT INTOstu_detailsVALUES(1,18,'M'),(4,20,'M'),(3,16,'F'),(2,19,'F');

#往课程表中添加数据：

INSERT INTO`course`(`name`)VALUES ('Math'),('Python'),('Test'), ('Music');

#往中间表中添加数据：

INSERT INTO `select`VALUES(1,2),(1,4),(2,1),(2,4),(4,1),(4,2),(4,4);

查询

查询所有记录：

SELECT * FROM 表名;

例：

SELECT * FROM`department`;SELECT * FROM`student`;SELECT * FROM`stu_details`;SELECT * FROM`course`;SELECT * FROM `select`;

查询指定列记录：

SELECT 列名1,列名2 FROM 表名;

例：

SELECT s_id,sex FROM `stu_details`;

查询指定条件下的记录：

SELECT 列名 FROM 表名 WHERE 条件;

例：

SELECT s_id,sex FROM `stu_details` WHERE age<19;

查询后为列取别名：

SELECT 列名 [AS] 别名 FROM 表名;

别名仅仅是显示在表头上

例：

SELECT name AS `nn` ,dept_id AS dd FROM student WHERE s_id>=2;SELECT name nn ,dept_id dd FROM student WHERE s_id>=2;

模糊查询：

SELECT 列名 FROM 表名 WHERE 条件列 LIKE 匹配字符串;

% 匹配0~n个字符

_ 匹配一个字符

例：

SELECT * FROM student WHERE name LIKE 'L%';SELECT * FROM student WHERE name LIKE '__fr%';

逻辑运算符 OR AND ：

例：

SELECT s_id,sex FROM `stu_details` WHERE age<20 AND sex='M';SELECT s_id,sex FROM `stu_details` WHERE age>=20 OR sex='F';

判断为null，不用 = ，只能用 IS， IS NOT

例：

SELECT * FROM student WHERE dept_id IS NULL;SELECT * FROM student WHERE dept_id IS NOT NULL;

IN ， NOT IN

SELECT * FROM student WHERE dept_id NOT IN (1,3);SELECT * FROM student WHERE dept_id IN (1,3);

排序`ORDER BY`：

`ASC`升序(默认) `DESC`降序

例：

按学生学号升序输出

SELECT * FROM `select` ORDER BY `s_id`;

按课程id降序输出

SELECT * FROM `select` ORDER BY `c_id` DESC;

限制显示数据的数量`LIMIT`：

LIMIT n 从第0条开始，往后面取n条

LIMIT m,n 从第m条开始，往后面取n条

例：

按学生学号升序输出的前4条数据

SELECT * FROM `select` ORDER BY `s_id` LIMIT 4;

按学生学号升序输出的从第2条开始的4条数据(数据索引从0开始)

SELECT * FROM `select` ORDER BY `s_id` LIMIT 2,4;

分组查询`GROUP BY` ：

GROUP BY 只能跟聚合函数一起用， 一般用 做统计

一个分组内值可能会不同的列，不用用作select，例如：SELECT s_id , age ,sex FROM stu_details GROUP BY sex;是非法的，因为每个sex分组中的元素，s_id和age都是不同的。

例：

SELECT sex , MAX(age) FROM stu_details GROUP BY sex;

将组内每个元素拼成字符串，并且用指定的分隔符来分隔

SELECT dept_id,GROUT_CONCAT(s_id,name SEPARATOR ',') FROM student GROUP BY `dept_id`;

聚合函数：

----------------------------------------------------

| AVG(column)| 返回某列的平均值 |

| COUNT(column)| 返回某列的行数(不包括 NULL 值) |

| COUNT(*)| 返回被选行数 |

| FIRST(column)| 返回在指定的域中第一个记录的值 |

| LAST(column)| 返回在指定的域中最后一个记录的值 |

| MAX(column)| 返回某列的最高值 |

| MIN(column)| 返回某列的最低值 |

| SUM(column)| 返回某列的总和 |

----------------------------------------------------

HAVING分组条件：

HAVING 后的字段必须是SELECT后出现过的，并且可以使用别名

WHERE：带着条件去表里面，删选内容。

HAVING：SELECT出来了结果，再进行一次删选。

例：

SELECT dept_id ,count(dept_id) AS n FROM student GROUP BY `dept_id` HAVING n=1;

子查询

出现在其他SQL语句内的SELECT字句。

1)嵌套在查询内部

2)必须始终出现在圆括号内

3)可以包含多个关键字或条件

把另一个SQL的结果，当成数据，直接拿过来用。

例：

# 求出学生的平均年龄

SELECT AVG(`age`) FROM `stu_details`;

#查找出大于平均年龄的数据

SELECT * FROM `stu_details` WHERE `age`>18.25;

#将平均数的SQL语句作为子查询放入上一条语句中

SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);

#选出学院为Computer和English的学生，先选出学院ID，再找出符合的学生ID

SELECT * FROM student WHERE dept_id IN (SELECT id FROM department WHERE name IN ('Computer','English') );