mysql 02（查询）

1、准备

# 创建一个数据库:python-db
create database 'python-db' charset=utf8; 

# 使用一个数据库
use 'python-db';

# 显示使用的数据库是哪个
select database();

# 创建一个数据表
# unsigned将数字类型无符号化、primary key主键、auto_increment 自增、not null不能为空
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 primary key auto_increment not null,
	name varchar(30) not null
	);

# 插入数据
insert into students values
(0,'小明',18,160.00,1,1,0),
(0,'小红',17,130.10,2,1,0),
(0,'小和和',14,150.55,2,2,0),
(0,'张小晓',19,170.00,1,1,0),
(0,'王李小强',20,175.00,1,2,0),
(0,'周小强',25,null,1,2,0);
insert into classes values
(0,"python1班"),
(0,"python2班");

2、简单查询
from、as、distinct

# 查
select * from students,classes;
select id,name,gender from students;
select students.id,classes.name,students.name from students,classes;

# 使用 as 给 字段\表 起别名
select name as 姓名,age as 年龄 from students;
# 表起了别名后就不能用原来的名字
select s.name,s.age from students as s;

# 去重复数据
select distinct gender from students;

3、条件查询
where、not

# 比较运算符> 、< 、=、!= 、>=、<=
select ... from 表名 where ...

# 查询年龄(and\or\not)
select * from students where age>18;
select id,name from students where age>18 and age<28;
select * from students where age>18 or height<=170.00;
select * from students where not age>18 and height<=170.00;
select * from students where not (age>18 and height<=170.00);

注意区分

4、模糊查询
like

# % 可以替换0个或多个、_ 替换1个
select * from students where  name like '小%';
select * from students where  name like '小_';

# 查询姓名中有 ”小“ 的
select * from students where  name like '%小%';

# 查姓名只有两个字的
select * from students where  name like '__';
# 查姓名至少两个字的
select * from students where  name like '__%';

rlike（正则）

# 查以 “王” 开头的姓名
select * from students where  name rlike '^王.*';

# 查以 “王” 开头  ‘强’结束 的姓名
select * from students where  name rlike '^王.*强$';

5、范围查询
in、not in、between…and…、not between…and…

select * from students where age in(18,20,21);
select * from students where age not between 18  and 21;
#错误select * from students where age not (between 18  and 21);

6、空判断
null（可以写成Null、NULL）

# a=None 表示a没有指向
# a=''   表示a指向空的数据，有地址

# 查询身高为空的数据
select * from students height is null;
# 查询身高不为空的数据
select * from students height is not Null;