mysql简单的单表查询详解
MySQL的查询操作：
单表查询：简单查询
多表查询：连续查询
联合查询：
选择和投影：
投影：挑选要显示的字段
选择：挑选符合条件的行
投影：SELECT 字段1, 字段2, ... FROM tb_name;
SELECT * FROM tb_name;
选择：SELECT 字段1, ... FROM tb_name WHERE 子句;
布尔条件表达式
mysql> CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY ,Name CHAR (30) NOT NULL , Age TINYINT UNSIGNED NOT NULL , Gender ENUM('F','M') NOT NULL,Tutor CHAR(30));
Query OK, 0 rows affected (0.01 sec)
mysql> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(30) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| Tutor | char(30) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+【插入数据】
mysql> insert into students values (1,'Guo Jing',27,'M','Song Jiang'),(2,'Yang Guo',28,'M','Hu Sanniang'),(3,'Guo polu',21,'M','Jii Baoyu');
mysql> INSERT INTO students VALUES (4,'Xue Baochai',19,'F','Rong Momo'),(5,'Xia Yuhe',37,'F','Shi Qian'),(6,'Wu Yong',51,'M','Lin Daiyu');
【显示数据】
mysql> select * from students; +-----+-------------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+-------------+-----+--------+-------------+ | 1 | Guo Jing | 27 | M | Song Jiang | | 2 | Yang Guo | 28 | M | Hu Sanniang | | 3 | Guo polu | 21 | M | Jii Baoyu | | 4 | Xue Baochai | 19 | F | Rong Momo | | 5 | Xia Yuhe | 37 | F | Shi Qian | | 6 | Wu Yong | 51 | M | Lin Daiyu | +-----+-------------+-----+--------+-------------+
【只显示25到40岁的】
mysql> select * from students where Age between 25 and 40; +-----+----------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+----------+-----+--------+-------------+ | 1 | Guo Jing | 27 | M | Song Jiang | | 2 | Yang Guo | 28 | M | Hu Sanniang | | 5 | Xia Yuhe | 37 | F | Shi Qian | +-----+----------+-----+--------+-------------+
【名字，年龄 只显示25到40岁的】 between and
mysql> select Name,Age from students where Age between 25 and 40; +----------+-----+ | Name | Age | +----------+-----+ | Guo Jing | 27 | | Yang Guo | 28 | | Xia Yuhe | 37 | +----------+-----+
【更新表的数据】
mysql> update students set Gender='M'where Name='Wu Yong';
【显示 以 X开头的名字】like ‘X%’
mysql> select * from students where Name like 'X%'; +-----+-------------+-----+--------+-----------+ | SID | Name | Age | Gender | Tutor | +-----+-------------+-----+--------+-----------+ | 4 | Xue Baochai | 19 | F | Rong Momo | | 5 | Xia Yuhe | 37 | F | Shi Qian | +-----+-------------+-----+--------+-----------+
【显示 以 X开头的名字】增则表达式 RLIKE
mysql> select * from students where Name Rlike '^X.*'; +-----+-------------+-----+--------+-----------+ | SID | Name | Age | Gender | Tutor | +-----+-------------+-----+--------+-----------+ | 4 | Xue Baochai | 19 | F | Rong Momo | | 5 | Xia Yuhe | 37 | F | Shi Qian | +-----+-------------+-----+--------+-----------+
【添加null值】
mysql> insert into students values(7,'tom',11,'M','jerry'),(8,'tomy',13,'M',null)； mysql> select * from students； +-----+-------------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+-------------+-----+--------+-------------+ | 1 | Guo Jing | 27 | M | Song Jiang | | 2 | Yang Guo | 28 | M | Hu Sanniang | | 3 | Guo polu | 21 | M | Jii Baoyu | | 4 | Xue Baochai | 19 | F | Rong Momo | | 5 | Xia Yuhe | 37 | F | Shi Qian | | 6 | Wu Yong | 51 | M | Lin Daiyu | | 7 | tom | 11 | M | jerry | | 8 | tomy | 13 | M | NULL | +-----+-------------+-----+--------+-------------+
【查找显示空值 】is null
mysql> select * from students where Tutor is null; +-----+------+-----+--------+-------+ | SID | Name | Age | Gender | Tutor | +-----+------+-----+--------+-------+ | 8 | tomy | 13 | M | NULL | +-----+------+-----+--------+-------+
【查找显示非空值 】is not null
mysql> select * from students where Tutor is not null; +-----+-------------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+-------------+-----+--------+-------------+ | 1 | Guo Jing | 27 | M | Song Jiang | | 2 | Yang Guo | 28 | M | Hu Sanniang | | 3 | Guo polu | 21 | M | Jii Baoyu | | 4 | Xue Baochai | 19 | F | Rong Momo | | 5 | Xia Yuhe | 37 | F | Shi Qian | | 6 | Wu Yong | 51 | M | Lin Daiyu | | 7 | tom | 11 | M | jerry | +-----+-------------+-----+--------+-------------+
【布尔条件表达式操作符】：
=
<=>
<>
<
<=
>
>=
IS NULL
IS NOT NULL
LIKE: 支持的通配符: %(任意长度的任意字符)， _（任意单个字符）
RLIKE, REGEXP: 支持使用正则表达式
IN: 判断指定字段的值是否在给定在列表中；
【组合条件测试】：
NOT, !
AND, &&
OR, ||
【多条件】BETWEEN ... AND ...: 位于指定的范围之间 where Age>25 and Gender='M'
mysql> select * from students where Age>25 and Gender='M'; mysql> select * from students where Age>25 && Gender='M'; mysql> select * from students where Age > 25 and Gender = 'M'; +-----+----------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+----------+-----+--------+-------------+ | 1 | Guo Jing | 27 | M | Song Jiang | | 2 | Yang Guo | 28 | M | Hu Sanniang | | 6 | Wu Yong | 51 | M | Lin Daiyu | +-----+----------+-----+--------+-------------+
【显示 顺序】 order by Name ；asc升序；desc降序；
mysql> select * from students where Age>25 and Gender='M' order by Name desc; +-----+----------+-----+--------+-------------+ | SID | Name | Age | Gender | Tutor | +-----+----------+-----+--------+-------------+ | 2 | Yang Guo | 28 | M | Hu Sanniang | | 6 | Wu Yong | 51 | M | Lin Daiyu | | 1 | Guo Jing | 27 | M | Song Jiang | +-----+----------+-----+--------+-------------+
【聚合函数】：
SUM(), AVG(), MAX(), MIN(), COUNT()个数
【计算年龄之和】
mysql> select sum(Age) from students; +----------+ | sum(Age) | +----------+ | 207 | +----------+
【支持where】
mysql> select sum(Age) from students where Age>25 && Gender='M' ; +----------+ | sum(Age) | +----------+ | 106 | +----------+
【 GROUP BY 分组：统计F、M 各个年龄之和】
mysql> select Gender,sum(Age) from students group by Gender; +--------+----------+ | Gender | sum(Age) | +--------+----------+ | F | 56 | | M | 151 | +--------+----------+
【插入一个字段】
mysql> alter table students add ClassID TINYINT UNSIGNED; mysql> select * from students; +-----+-------------+-----+--------+-------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+-------------+-----+--------+-------------+---------+ | 1 | Guo Jing | 27 | M | Song Jiang | NULL | | 2 | Yang Guo | 28 | M | Hu Sanniang | NULL | | 3 | Guo polu | 21 | M | Jii Baoyu | NULL | | 4 | Xue Baochai | 19 | F | Rong Momo | NULL | | 5 | Xia Yuhe | 37 | F | Shi Qian | NULL | | 6 | Wu Yong | 51 | M | Lin Daiyu | NULL | | 7 | tom | 11 | M | jerry | NULL | | 8 | tomy | 13 | M | NULL | NULL | +-----+-------------+-----+--------+-------------+---------+
【update 数值 】
mysql> update students set ClassID=2 where SID=1; mysql> select * from students; +-----+-------------+-----+--------+-------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+-------------+-----+--------+-------------+---------+ | 1 | Guo Jing | 27 | M | Song Jiang | 2 | | 2 | Yang Guo | 28 | M | Hu Sanniang | 3 | | 3 | Guo polu | 21 | M | Jii Baoyu | 3 | | 4 | Xue Baochai | 19 | F | Rong Momo | 1 | | 5 | Xia Yuhe | 37 | F | Shi Qian | 2 | | 6 | Wu Yong | 51 | M | Lin Daiyu | 1 | | 7 | tom | 11 | M | jerry | 1 | | 8 | tomy | 13 | M | NULL | 4 | +-----+-------------+-----+--------+-------------+---------+
【统计每个ClassID 对应的人数 】
mysql> select ClassID,count(*) from students group by ClassID; +---------+----------+ | ClassID | count(*) | +---------+----------+ | 1 | 3 | | 2 | 2 | | 3 | 2 | | 4 | 1 | +---------+----------+
【统计每个ClassID 对应的人数，并显示年龄之和 】
mysql> select ClassID,count(*) ,sum(Age) from students group by ClassID; +---------+----------+----------+ | ClassID | count(*) | sum(Age) | +---------+----------+----------+ | 1 | 3 | 81 | | 2 | 2 | 64 | | 3 | 2 | 49 | | 4 | 1 | 13 | +---------+----------+----------+
【having 统计显示，每个ClassID人数大于等于2的】
mysql> select ClassID from students group by ClassID having count(Name) >=2; +---------+ | ClassID | +---------+ | 1 | | 2 | | 3 | +---------+
【统计显示，每个ClassID，年龄之和大于等于2的】
mysql> select ClassID from students group by ClassID having sum(Age) <=50; +---------+ | ClassID | +---------+ | 3 | | 4 | +---------+
【limit】
limit 一个参数就显示几行
mysql> select *from students limit 3; +-----+----------+-----+--------+-------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+----------+-----+--------+-------------+---------+ | 1 | Guo Jing | 27 | M | Song Jiang | 2 | | 2 | Yang Guo | 28 | M | Hu Sanniang | 3 | | 3 | Guo polu | 21 | M | Jii Baoyu | 3 | +-----+----------+-----+--------+-------------+---------+
limit 两个参数用，隔开，第一个为跳过，第二个显示行数
mysql> select *from students limit 2,4; +-----+-------------+-----+--------+-----------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+-------------+-----+--------+-----------+---------+ | 3 | Guo polu | 21 | M | Jii Baoyu | 3 | | 4 | Xue Baochai | 19 | F | Rong Momo | 1 | | 5 | Xia Yuhe | 37 | F | Shi Qian | 2 | | 6 | Wu Yong | 51 | M | Lin Daiyu | 1 | +-----+-------------+-----+--------+-----------+---------+
练习：导入hellodb.sql，以下操作在students表上执行
1、以ClassID分组，显示每班的同学的人数；
2、以Gender分组，显示其年龄之和；
3、以ClassID分组，显示其平均年龄大于25的班级；
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；
1，mysql> select ClassID ,count(Name) from students group by ClassID; +---------+-------------+ | ClassID | count(Name) | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 2 | | 4 | 1 | +---------+-------------+ 2，mysql> select Gender,sum(Age) from students group by Gender; +--------+----------+ | Gender | sum(Age) | +--------+----------+ | F | 56 | | M | 151 | +--------+----------+ 3，mysql> select ClassID,avg(Age) from students group by ClassID having avg(Age) >=25 ; +---------+----------+ | ClassID | avg(Age) | +---------+----------+ | 1 | 27.0000 | | 2 | 32.0000 | +---------+----------+ 4，mysql> select Gender,sum(Age) from students where Age >=25 group by Gender ; +--------+----------+ | Gender | sum(Age) | +--------+----------+ | F | 37 | | M | 106 | +--------+----------+
【重复数据只显示一次】distinct
mysql> select ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 3 | | 3 | | 1 | | 2 | | 1 | | 1 | | 4 | +---------+ mysql> select distinct ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 3 | | 1 | | 4 | +---------+
【SELECT语句的执行流程：】
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
【SELECT语句:】mysql> help select;
DISTINCT：指定的结果相同的只显示一次；mysql> help distinct;
SQL_CACHE：缓存于查询缓存中；
SQL_NO_CACHE：不缓存查询结果；
