99. python高级------MySQl数据库的条件查询(1)

99. python高级------MySQl数据库的条件查询(1)

python修炼第二十四天

2019年 4月 23日 晴

• 数据库操作前的准备

• 创建数据库

• create database python_test_1 charset=utf8;

• 使用数据库

• use python_test_1;

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
– );

1.查询练习

1.查询所有字段
–select * from 表名;

select * from students;

2.查询指定字段
– select 列1,列2,… from 表名;

select name,age from students;

3.使用 as 给字段起别名
– select 字段 as 名字… from 表名;

select name as name2 from students;

– select 表名.字段 … from 表名;

select students.name from students;

4.可以通过 as 给表起别名
– select 别名.字段 … from 表名 as 别名;

select s.name from students as s;

5.消除重复行(查性别)

– distinct 字段
– group by 分组

select distinct gender from students;

6. 条件查询
   – 比较运算符
   > >= < <= != = <>
   – select … from 表名 where …
   (1) >
   – 查询年纪大于18岁的信息

    select * from students where age > 18;
   

(2) <
– 查询年纪小于18岁的信息

	select * from students where age < 18;

(3) >=
(4)<=
– 查询小于或者等于18岁的信息

	select * from students where age <= 18;

(5) =
– 查询年龄为18岁的所有学生的名字

select * from students where age = 18;

(6) != 或者 <>
– 查询年龄不为18岁的所有学生的名字

	select * from students where age != 18;
	select * from students where age <> 18;

7. 逻辑运算符
   (1)and
   – 18和28之间的所有学生信息

    select * from students where age > 18 and age < 28;
   

– 18岁以上的女性

select * from students where age > 18 and gender = '女';

(2) or
– 18以上或者身高高过180(包含)以上

	select * from students where age > 18 or height >= 180;

(3) not
– 不在 18岁以上的女性 这个范围内的信息

	select * from students  where age > 18 and gender = '女';
	select * from students  where not age > 18 and gender = '女';
	select * from students  where not (age > 18 and gender = '女');

8. 模糊查询(where name like 要查询的数据)
   – like
   – % 替换任意个
   – _ 替换1个
   – 查询姓名中 以 “小” 开始的名字

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

– 查询姓名中 有 “小” 所有的名字

	select * from students where name like "%小%";

– 查询有2个字的名字

	select * from students where name like '__';

– 查询有3个字的名字

	select * from students where name like '___';

– 查询至少有2个字的名字

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

9. 范围查询
   (1)in (1, 3, 8)表示在一个非连续的范围内
   – 查询 年龄为18或34的姓名

    select * from students where age = 18 or age = 34;
    
    select * from students where age in (18,34);
   

(2) not in 不非连续的范围之内
– 年龄不是 18或34岁的信息

	select * from students where age not in (18,34)

(3)between ... and ...表示在一个连续的范围内
– 查询 年龄在18到34之间的的信息

– 包含两端从小到大

	select * from students where age between 18 and 34;

(4) not between ... and ...表示不在一个连续的范围内
– 查询 年龄不在18到34之间的的信息

	select * from students where age not between 34 and 18;

10. 空判断
    (1) 判空is null
    – 查询身高为空的信息

    select * from students where height is null;
    

(2) 判非空is not null

	select * from students where height is not null;