在进行查询之前，我们要先建好关系表，并往数据表中插入些数据。为查询操作做好准备。
五张关系表的创建：
1#创建并进入数据库： 2mysql> CREATE DATABASE `info`; 3Query OK, 1 row affected (0.00 sec) 4mysql> USE `info`; 5Database changed 6 7#创建学院表： 8CREATE TABLE `department`( 9`id` INT PRIMARY KEY AUTO_INCREMENT, 10`name` VARCHAR(20) NOT NULL 11); 12 13 #创建学生表： 14CREATE TABLE `student`( 15`s_id` INT PRIMARY KEY AUTO_INCREMENT, 16`name` VARCHAR(20) NOT NULL, 17`dept_id` INT, 18FOREIGN KEY(`dept_id`) REFERENCES `department` (`id`) 19); 20 21 22`age` INT, 23`sex` CHAR(1), 24FOREIGN KEY(`s_id`) REFERENCES `student` (`s_id`) 25); 26 27#创建课程表： 28CREATE TABLE `course`( 29`id` INT PRIMARY KEY AUTO_INCREMENT, 30`name` VARCHAR(20) NOT NULL 31); 32 33#创csou建中间表： 34CREATE TABLE `select`( 35`s_id` INT, 36`c_id` INT, 37PRIMARY KEY (`s_id`,`c_id`), 38FOREIGN KEY (`s_id`) REFERENCES `student`(`s_id`), 39FOREIGN KEY (`c_id`) REFERENCES ins`course`(`id`) 40);showcreate; 41 42 43#查看当前存在的表： 44mysql> SHOW TABLES;
往数据表中添加数据
1#往学院表中添加数据： 2mysql> INSERT INTO `department`(`name`) 3 -> VALUES('外国语'),('艺术'),('计算机'),('化工'); 4 5#往学生表中添加数据： 6mysql> INSERT INTO `student`(`name`,`dept_id`) 7 -> VALUES('小明',1),('小红',3),('小花',3),('小新',4),('张三',2),('刘三',3); 8 9#往学生详细信息表中添加数据： 10mysql> INSERT INTO stu_details 11 -> VALUES(1,18,'男'),(4,20,'女'),(3,16,'女'),(2,19,'男'); 12 13#往课程表中添加数据： 14mysql> INSERT INTO `course`(`name`) 15 -> VALUES ('心理学'),('佛学'),('近代史'), ('音乐鉴赏'); 16 17#查看中间表的结构： (select是关键字，要加反引号) 18mysql> DESC `select`; 19+-------+---------+------+-----+---------+-------+ 20| Field | Type | Null | Key | Default | Extra | 21+-------+---------+------+-----+---------+-------+ 22| s_id | int(11) | NO | PRI | NULL | | 23| c_id | int(11) | NO | PRI | NULL | | 24+-------+---------+------+-----+---------+-------+ 252 rows in set (0.03 sec) 26#往中间表中添加数据 27mysql> INSERT INTO `select` 28 -> VALUES(1,2),(1,4),(2,1),(2,4),(4,1),(4,2),(4,4);
查询
查询所有记录
1SELECT * FROM tb_name; 2 3 SELECT * FROM `department`; 4 SELECT * FROM `student`; 5 SELECT * FROM `stu_details`; 6 SELECT * FROM `course`; 7 SELECT * FROM `select`; 8 9查询选中列记录 10 11SELECT col_name1,col_name2 FROM tb_name; 12 13查询指定条件下的记录 14 15SELECT col_name FROM tb_name WHERE 条件 16 17查询后为列取别名 18 19SELECT col_name AS new_name FROM tab_name 20 21模糊查询 22 23SELECT 字段 FROM 表 WHERE 某字段 Like 条件 24 25#查询所有记录： 26mysql> SELECT * FROM `student`; 27 28#查询选中列记录 29mysql> SELECT name,dept_id FROM student; 30 31#查询指定条件下的记录 32mysql> SELECT * FROM student WHERE `name`='小红'; 33 34#查询后为列取别名 35SELECT name AS `姓名` ,dept_id AS 学院id FROM student WHERE s_id>=2; 36 37SELECT name ,dept_id FROM student WHERE s_id>=2; 38 39SELECT name `姓名` ,dept_id 学院id FROM student WHERE s_id>=2; 40 41#模糊查询 * 42mysql> select * from student where name like '小%'; 43mysql> select * from student where name like '小_'; 44mysql> select * from student where name like '%小%'; 45 46% 多个字（任意个）* 47_ 一个字 48 49 50#逻辑运算符 or and 51# or 52select * from student where name like '小%' or name like '张%'; 53 54# and 55select * from student where name like '小%' and s_id >2; 56 57# 判断为null，不用 = ，只能用 is is not 58select * from student where dept_id is null; 59 60select * from student where dept_id is not null;
排序ORDER BY selectsuss
ASC升序(默认) DESC降序
1#查询学生的选修表（中间表） 2mysql> SELECT * FROM `select`; 3 4#按学生学号升序输出 5mysql> SELECT * FROM `select` ORDER BY `s_id`; 6select s_id 学生,c_id 课程 from `select` order by s_id asc; 7 8#按课程id降序输出： 9mysql> SELECT * FROM `select` ORDER BY `c_id` DESC;
限制显示数据的数量LIMIT
1# 分页。 2 3#按学生学号升序输出的前4条数据 4mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4; # （0，4） 从 索引为0， 往后面拿4条 5 6 7 8#指定的返回的数据的位置和数量 9mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4,4; # （4，4） 从 索引为4， 往后面拿4条
分组查询GROUP BY
1## 一般跟聚合函数 2 3 4#对学生表中学院栏进行分组，并统计学院的学生人数： 5mysql> SELECT dept_id AS 学院id,count(dept_id) AS 学生个数 FROM student GROUP BY `dept_id` ; 6+----------+--------------+ 7| 学院id | 学生个数 | 8+----------+--------------+ 9| 1 | 1 | 10| 3 | 2 | 11| 4 | 1 | 12+----------+--------------+ 133 rows in set (0.00 sec) 14 15select * from student order by dept_id; 16see 17 18## 不能这么写 19# select s_id , age ,sex from stu_details group by sex 20 21# group by 只能跟聚合函数一起用， 一般用 做统计 22select sex , MAX(age) from stu_details group by sex 23 24 25 26# 装B 27select dept_id,group_concat(s_id,name separator ',') from student group by `dept_id`; 28 3 | 2小红,3小花,6刘三 29 30select dept_id,group_concat(s_id,' ',name separator ',') from student group by `dept_id`; 31 32 33 34HAVING分组条件 35HAVING 后的字段必须是SELECT后出现过的 36mysql> SELECT dept_id AS 学院id,count(dept_id) AS 学生个数 FROM student GROUP BY `dept_id` HAVING 学生个数=1; 37+----------+--------------+ 38| 学院id | 学生个数 | 39+----------+--------------+ 40| 1 | 1 | 41| 4 | 1 | 42+----------+--------------+ 432 rows in set (0.01 sec) 44 45区别： 46where 带着条件 去表里面，删选内容， （table） 47 48having ：select 出来了结果，在进行一次删选。
子查询
出现在其他SQL语句内的SELECT字句。
1)嵌套在查询内部2)必须始终出现在圆括号内3)可以包含多个关键字或条件
1### 把别人的结果，当成数据，直接拿过来用。 2 3 4# 求出学生的平均年龄 5SELECT AVG(`age`) FROM `stu_details`; 6 7#查找出大于平均年龄的数据 8mysql> SELECT * FROM `stu_details` WHERE `age`>18.25; 9 10#将平均数的SQL语句作为子查询放入上一条语句中 11mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`); 12 13 14 15##需求： 要查找，计算机和外国语 的学生 16 17select id from department where name in ('计算机','外国语'); 18 19 20 21select * from student where dept_id in (select id from department where name in ('计算机','外国语') ); 22 23 24# in ， not in 25select * from student where dept_id not in (1,3); 26select * from student where dept_id in (1,3);
联表查询
内连接[INNER| CROSS] JOIN
无条件内连接：无条件内连接，又名交叉连接/笛卡尔连接第一张表种的每一项会和另一张表的每一项依次组合
有条件内连接：在无条件的内连接基础上，加上一个ON子句当连接的时候，筛选出那些有实际意义的记录行来进行拼接
在写条件时注意两张表的列名是否一样，如果时一样的则要在前面加上表名，tb_name.colname这种形式存在
1#无条件内连接： 2mysql> SELECT * FROM `student` INNER JOIN `department`; 3+------+--------+------+----+-----------+ 4| s_id | name | dept_id | id | name | 5+------+--------+------+----+-----------+ 6| 1 | 小明 | 1 | 1 | 外国语 | 7| 2 | 小红 | 3 | 1 | 外国语 | 8| 3 | 小花 | 3 | 1 | 外国语 | 9| 4 | 小新 | 4 | 1 | 外国语 | 10| 1 | 小明 | 1 | 2 | 艺术 | 11| 2 | 小红 | 3 | 2 | 艺术 | 12| 3 | 小花 | 3 | 2 | 艺术 | 13| 4 | 小新 | 4 | 2 | 艺术 | 14| 1 | 小明 | 1 | 3 | 计算机 | 15| 2 | 小红 | 3 | 3 | 计算机 | 16| 3 | 小花 | 3 | 3 | 计算机 | 17| 4 | 小新 | 4 | 3 | 计算机 | 18| 1 | 小明 | 1 | 4 | 化工 | 19| 2 | 小红 | 3 | 4 | 化工 | 20| 3 | 小花 | 3 | 4 | 化工 | 21| 4 | 小新 | 4 | 4 | 化工 | 22+------+--------+------+----+-----------+ 2316 rows in set (0.04 sec) 24#有条件内连接： 25mysql> SELECT * FROM `student` INNER JOIN `department` 26 -> ON dept_id=id; 27+------+--------+------+----+-----------+ 28| s_id | name | dept_id | id | name | 29+------+--------+------+----+-----------+ 30| 1 | 小明 | 1 | 1 | 外国语 | 31| 2 | 小红 | 3 | 3 | 计算机 | 32| 3 | 小花 | 3 | 3 | 计算机 | 33| 4 | 小新 | 4 | 4 | 化工 | 34+------+--------+------+----+-----------+ 354 rows in set (0.03 sec) 36 37#需求： 查询出 学生姓名，和对应学院名 38mysql> SELECT s.name 姓名, d.name 学院 FROM `student` s 39 -> INNER JOIN `department` d 40 -> ON dept_id=id;
外连接{ LEFT| RIGHT } [OUTER] JOIN
{ LEFT| RIGHT } [OUTER] JOIN
左外连接： （以左表为基准） in两张表做连接的时候，在连接条件不匹配的时候留下左表中的数据，而右表中的数据以NULL填充
右外连接： （以右表为基准）对两张表做连接的时候，在连接条件不匹配的时候留下右表中的数据，而左表中的数据以NULL填充
1#往学生表中添加数据，只添加名字 2mysql> INSERT INTO student(name) 3 -> VALUES('xixi'); 4Query OK, 1 row affected (0.11 sec) 5#查看所有学生表数据 6mysql> SELECT * FROM student; 7+------+--------+------+ 8| s_id | name | dept_id | 9+------+--------+------+ 10| 1 | 小明 | 1 | 11| 2 | 小红 | 3 | 12| 3 | 小花 | 3 | 13| 4 | 小新 | 4 | 14| 5 | xixi | NULL | 15+------+--------+------+ 165 rows in set (0.00 sec) 17#使用内连接加条件只能看到有分配好学院的学生的信息； 18mysql> SELECT * FROM student INNER JOIN departmentjoin 19 -> ON dept_id=id; 20+------+--------+------+----+-----------+ 21| s_id | name | dept_id | id | name | 22+------+--------+------+----+-----------+ 23| 1 | 小明 | 1 | 1 | 外国语 | 24| 2 | 小红 | 3 | 3 | 计算机 | 25| 3 | 小花 | 3 | 3 | 计算机 | 26| 4 | 小新 | 4 | 4 | 化工 | 27+------+--------+------+----+-----------+ 284 rows in set (0.02 sec) 29 30#使用左连接把学生的数据全取出来，该学生没有学院信息的用NULL填充 31mysql> SELECT * FROM student LEFT JOIN department 32 -> ON dept_id=id; 33+------+--------+------+------+-----------+ 34| s_id | name | dept_id | id | name | 35+------+--------+------+------+-----------+ 36| 1 | 小明 | 1 | 1 | 外国语 | 37| 2 | 小红 | 3 | 3 | 计算机 | 38| 3 | 小花 | 3 | 3 | 计算机 | 39| 4 | 小新 | 4 | 4 | 化工 | 40| 5 | xixi | NULL | NULL | NULL | 41+------+--------+------+------+-----------+ 425 rows in set (0.00 sec) 43 44 45 46#使用右外连接把目前还没有学生的学院的数据也显示出来 47mysql> SELECT * FROM student RIGHT JOIN department 48 -> ON dept_id=id; 49+------+--------+------+----+-----------+ 50| s_id | name | dept_id | id | name | 51+------+--------+------+----+-----------+ 52| 1 | 小明 | 1 | 1 | 外国语 | 53| 2 | 小红 | 3 | 3 | 计算机 | 54| 3 | 小花 | 3 | 3 | 计算机 | 55| 4 | 小新 | 4 | 4 | 化工 | 56| NULL | NULL | NULL | 2 | 艺术 | 57+------+--------+------+----+-----------+ 585 rows in set (0.00 sec) 59 60mysql>s 61 62# 需求： 作为班主任，我想看到，学生的 ( 姓名，选的课程名，所属学院 ) 63select s.name, c.name,d.name from `student` s 64left join `select` se on se.s_id = s.s_id 65left join course c on se.c_id = c.id 66left join department d on s.dept_id = d.id; 67 68##1. 69select s.name,d.name from student s left join department d on s.dept_id=d.id; 70 71##2.学生选的课（名称） 72select se.s_id ,c.name from `select` se left join course c on se.c_id = c.id; 73 74 75select s.name, d.name, c.name from student s 76left join department d on s.dept_id = d.id 77left join `select` se on s.s_id = se.s_id 78left join course c on se.c_id = c.id; 79 80 81 82select s.name ,GROUP_CONCAT(c.name SEPARATOR ',') 课程,d.name from `student` s 83left join `select` se on s.s_id = se.s_id 84left join `course` c on se.c_id = c.id 85left join `department` d on s.dept_id = d.id 86group by s.name,d.name ; 87+--------+-------------------------------+-----------+ 88| name | 课程 | name | 89+--------+-------------------------------+-----------+ 90| xixi | NULL | NULL | 91| 小新 | 心理学,佛学,音乐鉴赏 | 化工 | 92| 小明 | 佛学,音乐鉴赏 | 外国语 | 93| 小红 | 音乐鉴赏,心理学 | 计算机 | 94| 小花 | NULL | 计算机 | 95+--------+-------------------------------+-----------+ 96 97 98#需求： 作为宿管， 学生的 ( 姓名， 年龄，性别，所属学院) 99select s.name, stu.age,stu.sex,d.name from student s 100left join `stu_details` stu on s.s_id = stu.s_id 101left join `department` d on s.dept_id = d.id; 102+--------+------+------+-----------+ 103| name | age | sex | name | 104+--------+------+------+-----------+ 105| 小明 | 18 | 男 | 外国语 | 106| 小红 | 19 | 男 | 计算机 | 107| 小花 | 16 | 女 | 计算机 | 108| 小新 | 20 | 女 | 化工 | 109| xixi | NULL | NULL | NULL | 110+--------+------+------+-----------+
事务
事务： 是数据库运行中的一个逻辑工作单位。#原子性事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。#一致性事务在完成时，必须使所有的数据都保持一致状态。#隔离性由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。
为了保证数据库记录的更新从一个一致性状态变更为另一个一致性状态
1例： 2创建一张银行账户的表 3mysql> CREATE TABLE `account`( 4 -> `id` INT PRIMARY KEY AUTO_INCREMENT, 5 -> `name` VARCHAR(20) NOT NULL, 6 -> `balance` INT 7 -> ); 8Query OK, 0 rows affected (0.52 sec) 9添加两个用户及用户的存款的信息 10mysql> INSERT INTO `account`(`name`,`balance`) 11 -> VALUES('张三',10000), 12 -> ('小明',2000) 13 -> ; 14Query OK, 2 rows affected (0.09 sec) 15Records: 2 Duplicates: 0 Warnings: 0 16 17假设现在用户小明在商店买了500元东西，现在要转账给商店，那么就需要从小明的账户上减去500，然后在商店的用户上加上500，但是如果在减500的过程中出现了系统故障，再重新启动后发现小明的钱扣了，但商店却没有收到，这时候就会出现数据变动不一致。对于这种数据的修改我们需要的就是要么同时修改成功，要么同时修改失败，所以这就需要用事务来进行出来。 18 19START TRANSACTION：开始一个新的事务 20COMMIT：提交当前事务，做出永久改变 21ROLLBACK：回滚当前事务，放弃修改 22 23mysql> START TRANSACTION; 24Query OK, 0 rows affected (0.00 sec) 25 26mysql> UPDATE `account` 27 -> SET `balance`= `balance`-500 28 -> WHERE `name` ='小明' 29 -> ; 30Query OK, 1 row affected (0.04 sec) 31Rows matched: 1 Changed: 1 Warnings: 0 32 33使用ROLLBACK;使数据的修改不生效，回到事务前的状态： 34mysql> ROLLBACK; 35Query OK, 0 rows affected (0.06 sec) 36 37 38做一次正确的操作： 39mysql> START TRANSACTION; 40Query OK, 0 rows affected (0.00 sec) 41 42mysql> UPDATE `account` 43 -> SET `balance`=`balance`-500 44 -> WHERE `name`='小明' 45 -> ; 46Query OK, 1 row affected (0.03 sec) 47Rows matched: 1 Changed: 1 Warnings: 0 48 49mysql> UPDATE `account` 50 -> SET `balance`=`balance`+500 51 -> WHERE `name`='张三' 52 -> ; 53Query OK, 1 row affected (0.00 sec) 54Rows matched: 1 Changed: 1 Warnings: 0 55 56mysql> SELECT * FROM `account`; 57 58mysql> COMMIT; 59Query OK, 0 rows affected (0.07 sec) 60 61当COMMIT后，数据修改成功，ROLLBACK也没法回到之前了。 62 63mysql> ROLLBACK; 64Query OK, 0 rows affected (0.00 sec)
使用事务来处理是非常必要。
子查询
出现在其他SQL语句内的SELECT字句。
1)嵌套在查询内部2)必须始终出现在圆括号内3)可以包含多个关键字或条件
### 把别人的结果，当成数据，直接拿过来用。# 求出学生的平均年龄SELECT AVG(`age`) FROM `stu_details`;#查找出大于平均年龄的数据mysql> SELECT * FROM `stu_details` WHERE `age`>18.25;#将平均数的SQL语句作为子查询放入上一条语句中mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);##需求： 要查找，计算机和外国语 的学生select id from department where name in ('计算机','外国语');select * from student where dept_id in (select id from department where name in ('计算机','外国语') );# in ， not inselect * from student where dept_id not in (1,3);select * from student where dept_id in (1,3);