mysql查询

数据的准备

显示所有数据库

show databases;

创建一个数据库

create database 数据库名 charset=utf8;

使用一个数据库

use 数据库名;

显示当前使用的是哪个数据库

select database();

创建表(字段 类型 约束)

create table student(
	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
);

显示数据表的创建，也就是打印出上面定义的那些字段和约束

show create table 表名;

向表中插入元素

insert into student values
(0, '小明', 18, 180, 00, 2, 1, 0),
(0, '小月月', 18, 160, 00, 2, 2, 1);

查询

查询所有字段

select * from 表名;

查询指定字段

select 列1, 列2, ... from 表名;

使用as给字段取别名

select name as 姓名, age as 年龄 from 表名;

当有多个表，可以使用表名.字段名

select student.name as 姓名, student.age as 年龄 from 表名;

使用as给表起别名

select s.name, s.age from students as s;

清除重复行（distinct）

select distinct gender from student;

条件查询

比较运算符

select ... from 表名 where ...;

大于运算符>，查询大于18岁的信息

select * from student where age>18;

小于运算符<，查询小于18岁的信息

select * from student where age<18;

还有>=, <=, !=, =, <>不等于

逻辑运算符

and运算符，查询年龄在18到28之间所有学生信息

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

or运算符，查询年龄大于或者身高超过180

select * from student where age>18 or height>180;

not运算符，查询不在18岁以上的女性

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

模糊查询

like

• %替换一个或多个
• _替换一个

查询姓名中以‘小’开始的名字

select name from student where name like "小%";

查询有2个字的名字

select * from student where name like "__";

rlike 正则
查询以周开始的姓名

select * from student where name rlike "^周.*";

范围查询

in(1, 3, 8)表示一个非连续的范围
查询年龄为18，34的姓名

select name, age from student where age=18 or age=34;
select name, age from student where age in (12, 18, 34);

not in ()不非连续的范围之内
查询年龄不是12， 18， 34岁的信息

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

between…and…表示在一个连续的范围内
查询年龄在18到34岁之间的信息

select * from stduent where age between 18 and 34;

not between… and…表在不在这个连续范围内
查询年龄不在18到34岁之间的信息

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

is null空判断
查询身高为空的信息

select * from student where height is null;

is not null非空判断

select * from student where height is not null;

排序

order by 字段
asc从小到大排序，即升序
desc从大到小排序，即降序
查询年龄在18到34岁之间的男性，按照年龄从小到大排序

select * from student where (age between 18 and 34) and gender='男' order by age;

查询年龄在18到34之间的女性，按照身高从高到矮排序

select * from student where (age between 18 and 34) and gender='女' order by height desc;

order by 多个字段
查询年龄在18到34岁之间，身高从高到矮，身高相同按照id从大到小

select * from student where (age between 18 and 34) and gender='女' order by height desc, id desc;

聚合函数

总数
count
查询男性有多少人，女性有多少人

select count(*) as 男性人数 from student where gender='男';
select count(*) as 女性人数 from student where gender='女';

最大值 最小值
max min
查询最大的年龄

select max(age) as 最大年龄 from student;

求和
sum
计算所有人的年龄综合

select sum(age) from student;

平均值
avg
计算平均年龄

select avg(age) from student;
select sum(age)/count(*) from student;

round(123.23, 1) 保留一位小数同时四舍五入
计算所有人的平均年龄，保留2位小数

select round(avg(age), 2) from student;
select round(sum(age)/count(*), 2) from student;

分组

group by
按照性别分组，查询所有的性别

select gender from student gruop by  gender;

计算每种性别的人数

select gender,count(*) from student group by gender;

计算男性的人数

select gender,count(*) from student where gender='男' group by gender;

查看所有男性的名字

select gender,group_concat(name) from student where gender='男' group by gender;

having对分组进行筛选
查询平均年龄超过30岁的性别，以及姓名 having avg(age)>30

select gender,group_concat(name),avg(age) from student group by gender having avg(age)>30;

查询每种性别中人数多于两人的信息

select gender,group_concat(name) from student group by gender having count(*)>2;

分页

limit, start, count限制显示的个数和开始的位置
查询前5个数据

select * from student limit 5;
select * from student limit 0, 5;

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
内连接：交集
查询有能够对应班级的学生以及班级信息

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

只显示姓名和班级

select s.name, c.name from student as s inner join classes as c on s.cls_id=c.id;

左连接：按照左边表的所有内容，若在右边表中找不到，就用null填充

select * from student as s left join classes as c on s.cls_id=c.id;

查询没有班级对应的学生

select * from student as s left join classes as c on s.cls_id=c.id having c.id=null;

右连接和左连接相反

自关联

几张表合成一张表
比如省市县三张表合成一张表也可以

子查询

查询最高的男生信息
将子查询的结果当作条件去查询

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

数据库设计

三范式：数据库设计的一些规范

• 第一范式：强调列的原子性，即列不能再分成其他几列
• 第二范式：在第一范式的基础上，另外包含两部分内容，一是表必须有一个主键，二是没有包含在主键中的列必须完全依赖于主键