一、MySQL基础查询篇练习
本文接上文:MySQL从入门到入洞房(一)
- 排序查询
limit x,y (x:表示从X条数据开始 y:需要查出多少条),不同于python的切片
SELECT c_no, s_no,sc_degree FROM score ORDER BY sc_degree DESC LIMIT 0,1;
关键字:order by 理解为“按照。。排序”
mysql> SELECT c_no, s_no,sc_degree FROM score ORDER BY sc_degree DESC LIMIT 0,1;
+-------+------+-----------+
| c_no | s_no | sc_degree |
+-------+------+-----------+
| 3-105 | 103 | 92 |
+-------+------+-----------+
1 row in set (0.00 sec)
- 平均运算
关键字:avg()
计算平均值,group by
分组,将课程号分组,然后计算
mysql> SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no;
+-------+----------------+
| c_no | AVG(sc_degree) |
+-------+----------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+----------------+
3 rows in set (0.00 sec)
- 模糊查询
关键字:like + 正则
mysql> select avg(sc_degree) , c_no from score
-> group by c_no
-> having count(c_no)>=2
-> and c_no like"3%";
+----------------+-------+
| avg(sc_degree) | c_no |
+----------------+-------+
| 85.3333 | 3-105 |
| 76.3333 | 3-245 |
+----------------+-------+
2 rows in set (0.00 sec)
上述select的查询过程,SQL语句就像是一个递归的过程。
关键字:not
模糊取反
SELECT * FROM student WHERE s_name NOT LIKE '王%';
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+--------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+------+--------+-------+---------------------+---------+
- 区间查询
关键字:between and
SELECT s_no,sc_degree FROM score WHERE sc_degree BETWEEN 71 AND 89;
+------+-----------+
| s_no | sc_degree |
+------+-----------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+------+-----------+
- 多表查询
select s_name,sc_degree,c_name FROM score,student,course
WHERE score.s_no = student.s_no
AND score.c_no = course.c_no
AND sc_degree
BETWEEN 71 AND 89;
+--------+-----------+------------+
| s_name | sc_degree | c_name |
+--------+-----------+------------+
| 王丽 | 86 | 操作系统 |
| 王丽 | 85 | 数字电路 |
| 王芳 | 88 | 计算机导论 |
| 王芳 | 75 | 操作系统 |
| 王芳 | 79 | 数字电路 |
| 赵铁柱 | 76 | 计算机导论 |
| 赵铁柱 | 81 | 数字电路 |
+--------+-----------+------------+
SELECT s_name, c_no, sc_degree FROM student,score
WHERE student.s_no = score.s_no;
+--------+-------+-----------+
| s_name | c_no | sc_degree |
+--------+-------+-----------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+--------+-------+-----------+
上例理解成,当student.s_no = score.s_no两张表里的所调用字头相等时,从student,score 的s_name, c_no, sc_degree中,取出符合条件的字段。
为什么可以取出,原因就是因为为张表的相同字段可以关联。
- 条件查询
SELECT * FROM student
WHERE YEAR(s_birthday)
IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+--------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
+------+--------+-------+---------------------+---------+
思路仍然是先找到,后找到。
- 多表嵌套查询
select * from student where s_no
IN (SELECT s_no FROM score
WHERE c_no = (SELECT c_no FROM course
WHERE t_no = (SELECT t_no FROM teacher WHERE t_name='张旭')));
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+--------+-------+---------------------+---------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+--------+-------+---------------------+---------+
思路很简单,刚开始可以一个一个表找,找到然后嵌套
- 并集出表
关键字:union
将两个不同的查询结果求并集
SELECT * FROM teacher WHERE t_depart = '计算机系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系')
UNION
SELECT * FROM teacher WHERE t_depart = '电子工程系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系');
+------+--------+-------+---------------------+--------+------------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
+------+--------+-------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+------+--------+-------+---------------------+--------+------------+
- 至少关键字
关键字:any
至少有多少个需要被查询
select * from score where c_no = '3-105' AND sc_degree > ANY(SELECT sc_degree FROM score WHERE c_no = '3-245' ) ORDER BY sc_degree desc ;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+-----------+
练习语句的含义是:从score中查表,这个表满足的条件是他的课程号是3-105,并且,挑出 他的分数至少大于3-245的分数。最后降序排列。
- 所有关键字
关键字:all
表示所有
SELECT * FROM score WHERE sc_degree > ALL (select sc_degree from score WHERE c_no = '3-245') AND c_no = '3-105';
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+------+-------+-----------+
练习语句的含义是:从score中查表,这个表满足的条件是他的课程号是3-105,并且,挑出 他的分数所有大于3-245的分数。最后降序排列。
总结: ANY 和 ALL
ANY:表示任何一个就行了
ALL:表示所有都要比较
- 时间函数运用
SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;
+--------+------+
| s_name | age |
+--------+------+
| 曾华 | 42 |
| 匡明 | 44 |
| 王丽 | 43 |
| 李军 | 43 |
| 王芳 | 44 |
| 陆军 | 45 |
| 王尼玛 | 43 |
| 张全蛋 | 44 |
| 赵铁柱 | 45 |
| 张飞 | 45 |
+--------+------+
- max与min的练习
SELECT MAX(s_birthday) ,MIN(s_birthday) FROM student;
+---------------------+---------------------+
| MAX(s_birthday) | MIN(s_birthday) |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
这里的最大是指年份最大,但现实中应该是最小的,是吧
- between的创意性练习
先建一个等级表
CREATE TABLE grade(
low INT(3),
upp INT(3),
grade CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'c');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');
利用条件where
,between and
关键字,进行查询
SELECT s_no, c_no , grade FROM score, grade WHERE sc_degree BETWEEN low and upp;
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | c |
| 105 | 6-166 | c |
| 109 | 3-105 | c |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+------+-------+-------+
二、MySQL的内、左右、全连接
先建俩表
人员:
mysql> create database bank;
Query OK, 1 row affected (0.00 sec)
mysql> use bank;
Database changed
mysql> create table person(
-> id int ,
-> name varchar(20),
-> cardid int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into person values
-> (1,'a',1),
-> (2,'b',2),
-> (3,'c',3),
-> (4,'d',4);
Query OK, 5 rows affected (0.19 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
+------+------+--------+
5 rows in set (0.00 sec)
mysql> create table banks(
create table banks(
^C
mysql> create table banks(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.13 sec)
mysql> insert into banks values
-> (1,"nong"),
-> (2,"jian"),
-> (3,"gong"),
-> (4,"zhong");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from banks;
+------+-------+
| id | name |
+------+-------+
| 1 | nong |
| 2 | jian |
| 3 | gong |
| 4 | zhong |
+------+-------+
4 rows in set (0.00 sec)
mysql> insert into banks values
-> (5,"gongshang")
-> ,(6,"youzheng");
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into person values
-> (5,'e',4)
-> ,(6,'f',1);
mysql> insert into person values(7,'g',0);
- 内连接
关键字:inner join
mysql> select * from person inner join banks on person.cardid=banks.id;
+------+------+--------+------+-------+
| id | name | cardid | id | name |
+------+------+--------+------+-------+
| 1 | a | 1 | 1 | nong |
| 2 | b | 2 | 2 | jian |
| 3 | c | 3 | 3 | gong |
| 4 | d | 4 | 4 | zhong |
+------+------+--------+------+-------+
4 rows in set (0.00 sec)
可以将inner去掉,结果一样。
其实就是通过两张表中的相同字段,将两张表关联在一起。
- 左外连接
关键字:left join
=left outer join
mysql> select * from person left join banks on person.cardid=banks.id;
+------+------+--------+------+-------+
| id | name | cardid | id | name |
+------+------+--------+------+-------+
| 1 | a | 1 | 1 | nong |
| 6 | f | 1 | 1 | nong |
| 2 | b | 2 | 2 | jian |
| 3 | c | 3 | 3 | gong |
| 4 | d | 4 | 4 | zhong |
| 5 | e | 4 | 4 | zhong |
| 7 | g | 0 | NULL | NULL |
+------+------+--------+------+-------+
7 rows in set (0.00 sec)
表中可以看出左侧所有数据查询,右侧有相等就表述,否则就不查询。
- 右外连接
关键字:right join
=right outer join
mysql> select * from person right outer join banks on person.cardid=banks.id;
+------+------+--------+------+-----------+
| id | name | cardid | id | name |
+------+------+--------+------+-----------+
| 1 | a | 1 | 1 | nong |
| 2 | b | 2 | 2 | jian |
| 3 | c | 3 | 3 | gong |
| 4 | d | 4 | 4 | zhong |
| 5 | e | 4 | 4 | zhong |
| 6 | f | 1 | 1 | nong |
| NULL | NULL | NULL | 5 | gongshang |
| NULL | NULL | NULL | 6 | youzheng |
+------+------+--------+------+-----------+
8 rows in set (0.00 sec)
- 全连接
错误示例:
mysql> select * from person full join banks on person.cardid=banks.id;
ERROR 1054 (42S22): Unknown column 'person.cardid' in 'on clause'
MySQL不支持全连接,那该怎么办呢?
想起之前学过的关键字:union
那原理是什么呢?见下图
那该怎么办呢?union
!
mysql> select * from person left join banks on person.cardid=banks.id
-> union
-> select * from person right outer join banks on person.cardid=banks.id;
+------+------+--------+------+-----------+
| id | name | cardid | id | name |
+------+------+--------+------+-----------+
| 1 | a | 1 | 1 | nong |
| 6 | f | 1 | 1 | nong |
| 2 | b | 2 | 2 | jian |
| 3 | c | 3 | 3 | gong |
| 4 | d | 4 | 4 | zhong |
| 5 | e | 4 | 4 | zhong |
| 7 | g | 0 | NULL | NULL |
| NULL | NULL | NULL | 5 | gongshang |
| NULL | NULL | NULL | 6 | youzheng |
+------+------+--------+------+-----------+
9 rows in set (0.01 sec)
三、MySQL事务
什么是事务?
事务是一个不可分割的最小的工作单元,事务可维护数据库的完整性。它保证MySQL事务要么完全执行,要么完全不执行。
例如:
A:我买了一个LV包,我要付钱(钱包-10000)
B:你扫码(钱包+10000)
> 如果事务未工作,且B操作数据库回退rollback
B:哎?我怎么没收到钱?快转账!!
A:我支付了啊?
对应代码演示,回退rollback:rollback,撤回上一条执行结果
- 事务自动提交时(默认开启)
手动提交:commit
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
+------+------+--------+
7 rows in set (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.09 sec)
mysql> insert into person values(8,'h',1);
Query OK, 1 row affected (0.09 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
+------+------+--------+
8 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
+------+------+--------+
8 rows in set (0.00 sec)
我们发现,回退根本没有用,继续
- 事务回滚时
mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into person values(9,'i',2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
| 9 | i | 2 |
+------+------+--------+
9 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
+------+------+--------+
8 rows in set (0.00 sec)
发现rollback
关键字起作用了,回退成功,说明事务被关闭了。
重新设定:
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into person values(9,'i',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
| 9 | i | 3 |
+------+------+--------+
9 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from person;
+------+------+--------+
| id | name | cardid |
+------+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 4 |
| 6 | f | 1 |
| 7 | g | 0 |
| 8 | h | 1 |
| 9 | i | 3 |
+------+------+--------+
9 rows in set (0.00 sec)
- 手动开启事务
begin; start transaction;
两种方法 - 事务的四大特性
~事务是最小的单位不可分割
~同一事务的SQL语句,必须保证同时成功或同时失败
~事务一与事务二具有隔离性
~事务一旦结束就不可返回
入门到此为止,下次看看MySQL的索引,触发器,还有在服务器部署MySQL远程访问,JDBC操作MySQL。
·
路还很长。