数据库之MySQL查询

-- 创建数据库
create database python_test1 charset=utf8;

-- 使用数据库
use python_test1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

-- 向classes表中插入数据
insert into classes values (0, "python1班"), (0, "python2班");

select * from 表名;
例：
select * from students;

select 列1,列2,... from 表名;
例:
select name from students;

select id as 序号, name as 名字, gender as 性别 from students;

-- 如果是单表查询 可以省略表明
select id, name, gender from students;

-- 表名.字段名
select students.id,students.name,students.gender from students;

-- 可以通过 as 给表起别名 
select s.id,s.name,s.gender from students as s;

select distinct 列1,... from 表名;
例：
select distinct gender from students;

select * from 表名 where 条件;
例：
select * from students where id=1;

select * from students where id > 3;

select * from students where id <= 4;

select * from students where name != '黄蓉';

select * from students where is_delete=0;

select * from students where id > 3 and gender=0;

select * from students where id < 4 or is_delete=0;

select * from students where name like '黄%';

select * from students where name like '黄_';

select * from students where name like '黄%' or name like '%靖';

select * from students where id in(1,3,8);

select * from students where id between 3 and 8;

select * from students where (id between 3 and 8) and gender=1;

select * from students where height is null;

select * from students where height is not null;

select * from students where height is not null and gender=1;

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

select * from students where gender=1 and is_delete=0 order by id desc;

select * from students where is_delete=0 order by name;

select * from students order by age desc,height desc;

select count(*) from students;

select max(id) from students where gender=2;

select min(id) from students where is_delete=0;

select sum(age) from students where gender=1;

-- 平均年龄
select sum(age)/count(*) from students where gender=1;

select avg(id) from students where is_delete=0 and gender=2;

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

select * from 表名 limit start,count

select * from students where gender=1 limit 0,3;

select * from students where is_delete=0 limit (n-1)*m,m

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

select * from students inner join classes on students.cls_id = classes.id;

--此处使用了as为表起别名，目的是编写简单
select * from students as s left join classes as c on s.cls_id = c.id;

select * from students as s right join classes as c on s.cls_id = c.id;

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

能不能将两个表合成一张表呢？

观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的

存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

定义表areas，结构如下

id
atitle
pid

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

source areas.sql;

select count(*) from areas where pid is null;

select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='山西省';

select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.atitle='广州市';

select * from students where age > (select avg(age) from students);

select name from classes where id in (select cls_id from students);

select * from students where (height,age) = (select max(height),max(age) from students);

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

-- 查询id为1的学生的所有信息;
select s.*, c.name as classname from students as s left join classes as c on s.cls_id=c.id where s.id=1;

select c.name, group_concat(s.name) from classes as c left join students as s on s.cls_id=c.id group by c.name;

select c.name, count(c.id) from students as s right join classes as c on s.cls_id=c.id group by c.name;