mysql学习笔记

登入

mysql -uroot -p
password

– 数据的准备

 create database python_test charset=utf8

– 使用一个数据库
use python_test
– 显示使用的当前数据是哪个
select database()

– student表
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)
cls_id int unsigned
gender enum(‘男’,‘女’,‘中性’,‘保密’)
is_delete bit default 0
);

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

– 向表内增加数据

 insert into students values
 (0,'小明',18,180.00,2,1,0)
 (0,'小蓝',18,180.00,2,1,0)

– 查询

– 查询所有字段
select * from students
select * from classes
select name, age from students

  select name as 姓名, age as 年龄 from students  

– select 别名， 字段 。。。 from 表名 as 别名
select s.name, s.age from student as s;

select distinct gender from students;  -- dsitinct 去重

– 条件查询

	select * from students where age>18;
	select id,name,gender from students where age<18;
	select * from students where age=18 and age<28;
	select * from students where not age>18;
	select * from students where not (age>18 and gender=2);
	select * from students where (not age>18) and gender=2;

– 模糊查询
–like %替换1个或者多个
– _ 替换1个
– 查询姓名以“小”开始的名字
select name from students where name like “小%”;
– 查询姓名中有“小”的所有名字
select name from students where name like “%小%”;
– 查询有两个字的名字
select name from students where name like “";
– 至少有两个字
select name from students where name like "%”;

– rlike 正则
– 查询以周开始的姓名
select name from students where name rlike “^周.*伦￥”

– 范围查询
select name,age from student where age in (12, 18, 34); – 12,18,34
select name,age from student where age not in (12, 18, 34);
select name,age from student where age between 18 and 34; 18-34

– 空判断
– 判空 is null

--查询身高为空
    select * from student where height is null;
    select * from student where height is not null;

– 排序

-- order by 字段

–asc 从小到大排序
– desc 从大到小排序

– 查询年龄在18到34岁之间的男性，按年龄从小到大排序
select * from students where (age between 18 and 34 ) and gender=1 order by age – 默认从小到大
select * from students where (age between 18 and 34 ) and gender=1 order by age asc
– 有相同的的可以多个字段 order by 多个字段 （默认id）
select * from students where (age between 18 and 34 ) and gender=1 order by age asc，id des

– 聚合函数

– 总数
– count
– 查询男性有多少人，女性多少人
select count() from students where gender=1
select count() as 男性人数 from students where gender=1
select count(*) as 女性人数 from students where gender=2

– 最大值
– max()
–查询最大年龄
select max(age) from students;

– 查询女性最高身高
select max(height) from students where gender=2;
– 最小值 min()
– 求和 sum()

–平均值 avg()

– select sum(age)/count(*) from students

– 分页

– limit start, count
– 限制查询出来的数据个数limit
select * from student limit 5

– 查询前5个数据
select * from student limit 0,5
– 查询下5个数据
select * from student limit 5,5

– 每页显示2个，第一个页面
select * from student limit 0,2
– 每页显示2个，第二个页面
select * from student limit 2,2
– 每页显示2个，第三个页面
select * from student limit 4,2
– 每页显示2个，第四个页面
select * from student limit 6,2 ---->limit(第N页-1)*每页个数，每页个数

有很多条件时 limit 要放在最后

– 连接查询（多个表的关联查询）

– inner join … on
select … from 表一 inner join 表二 两张表合起来
– 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on student.cls_id=classes.id;

– 按照要求显示姓名班级
select student.*, classes,name from students inner join classes on student.cls_id=classes.id

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

– 给表起名

select s.name, c.name from students as sinner join classes as c on student.cls_id=classes.id

– left join 结果包括没有班级的学生
select … from students as left join classes as c on s.cls_id=c.id

  select ... from students as left join classes as c on s.cls_id=c.id having c.id is null  从结果找出结果

  select ... from students as left join classes as c on s.cls_id=c.id where c.id is null  -- 从原表判断

– 自关联 一个表的字段关联这个表另外一列字段

source xxx.sql – 导入数据

– 子查询

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