2018.10.23mysql数据库操作二、查询操作

创建库：create database python_test charset=utf8;

创建表：

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
    );
    create table classes (
        id int unsigned auto_increment primary key not null,
        name varchar(30) not null
    );

 

 

一、基本查询

查询所有字段

select * from students;

查询指定字段

select id ,name from students;

select students.id,students.name from students;

使用as给字段起别名：

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

可以通过as给表起别名：

select students.id,students.name from students;

select s.id,name from students as s;

消除重复行

select distinct gender from students;

二、where使用

比较

select * from students where age>5;

逻辑运算符

select * from students where age>5 and age<10;

模糊查询

like

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

rlike(正则)

select name from students where name rlike '^周.*轮$';

范围查询

in

select name,age from students where age in(12,18,34);

not in

between and

select name,age from students where age between 18 and 34;

not between and

空判断

is null

select * from students where height is null;

is not null

select * from students where height is not null;

排序查询

order by

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

select * fromstudents where is_delete=0 order by name desc;

 

三、聚合函数查询

计算总数：

select count(*) from students;

计算最大值：

select max(age) from students;

计算最小值

select min(age) from students;

计算总和

select sum(age) from students;

计算平均值

select avg(age) from students;

四、分组查询

select gender from students group by gender having id=5

select gender,sum(age) from students group by gender

select gender ,group_concat(name) from students group by gender;

having:对结果进行过滤；where对原表数据进行判断

 

五、分页查询

select * from students limit count;

select * from students limit 0,5;

六、连接查询

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

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

 

七、子查询：

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