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;