Mysql基本查询

Mysql基本查询

• 创建表

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),   -- 最大5位数字，其中两位小数
gender enum('男','女','人妖','保密'),
cls_id int unsigned default 0,   
isdelete bit default 0
);

• 为表插入数据：

insert into students values
(0,'韦少',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'芙蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'周杰',34,176.00,2,5,0);

查询表中的所有的字段信息：

select * from students;

查询表中部分字段的信息：

select name,age from students;
# 或者
select students.name,students.age from students;

给表起个别名

• 注意：前面的也需要修改，不然会报错

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

给字段起别名

select s.name as 名字,s.age as 年龄 from students as s;

去重

select distinct id,gender from students;

mysql条件查询

查看mysql的版本

select version();

mysql可以进行基本的运算

select 2 + 3;

where进行条件查询

• where后面支持多种运算符，进行条件的处理
  • 比较运算符、逻辑运算符、模糊查询、范围查询、空判断
• 比较运算符
  • 等于: =
  • 大于: >
  • 大于等于: >=
  • 小于: <
  • 小于等于: <=
  • 不等于: != 或 <>
• 比如：

select * from students where id = 10;

• 逻辑运算符
• and
  • 这个符号两边都为真的时候，结果才为真，也就是才满足条件。
• or
  • 这个符号两边只要有一个为真的时候，结果就为真，也就是才满足条件。
• not
• 比如：

select * from students where id > 3 and gender = 2;
select * from students where id < 4 or isdelete = 0;
select * from students where not id = 10;

模糊查询like

• %表示任意多个任意字符
  • a%b ==> aab, axxb, auuub, ab,
• _表示一个任意字符
  • a_b ==> acb, aub, aib,
• 比如：

select * from students where name like '周%';
select * from students where name like '周_';

范围查询：

• in
  • 表示在一个非连续的集合内：
• between … and …
  • 表示在一个连续的范围内

select * from students where id in(1,3,8);
select * from students where id between 1 and 5;

空判断

• 注意：null与’'是不同的
• 判空is null
  • 不能通过比较运算符= 与null进行比较，需要使用 is null
• 判非空is not null
• 比如

# 查询没有填写身高的学生
select * from students where height is null;
# 查询填写了身高的学生
select * from students where height is not null;

优先级

• 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序

• asc
  • 从小到大排序，即升序；
• desc
  • 从大到小排序，即降序；
• 默认是升序；

select * from students order by height desc;
# 先通过身高降序，在通过年龄降序；
select * from students order by height desc,age desc;
# 对id是1到5的数据进行身高的排序
select * from students where id > 1 and id < 5 order by height desc;

聚合函数

count(*)

• 表示计算总行数：

select count(*) from students;
select count(height) from students;

max(列)

• 表示求此列的最大值

select max(height) from students;

min(列)

• 表示求此列的最小值

select min(height) from students;

sum(列)

• 表示求此列的和

select sum(height) from students;
select sum(height) / count(id) from students;

avg(列)

• 求此列的平均值

mysql> select avg(height) from students;

分组

• 按照字段分组：此字段相同的数据会被放到一个组中，分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中，可以对分组后的数据进行统计，做聚合运算。
• 分组的目的还是为了做统计。
• 默认情况下：分组之后会对分组的数据执行一次升序排序的操作

select gender from students group by gender;
# 也可以用distinct去重,效果是一样的
select distinct gender from students;
# 对分组的数据进行排序的时候不需要用order by；
select gender from students group by gender desc;
# 查看按着性别分组后，组里人的姓名：
# 无论有多少姓名，都只能看到第一个姓名
select gender,any_value(name) from students group by gender;
# 与group_concat()函数一起使用，可以看到所有的姓名
select gender,group_concat(name) from students group by gender;
# 可以同时看姓名和年龄
select gender,group_concat(name,age) from students group by gender;
# 查看各种年龄的人数
select age,count(*) from students group by age;

分组后的数据筛选

• having后面的条件运算符与where相同

select count(*) from students where gender = 1;
# 也可以写成
select count(*) from students group by gender having gender = 1;
select gender,count(*) from students group by gender having gender = 2;
select gender,count(*) from students where gender = 2 group by gender;

• 对比where与having
  • where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
  • having是对group by的结果进行筛选
• 总结：有group by语句的出现不一定有having语句的出现，有having语句的出现就一定会有group by语句的出现。

用limit限制查询结果的数量

• 查询数据时，可能会查询出很多的记录。而用户需要的记录可能只是很少的一部分。这样就需要来限制查询结果的数量。Limit是mysql中的一个特殊的关键字。其可以用来指定查询结果从哪条记录开始显示，还可以指定一共显示多少条记录。有两种用法：
• 不指定初始位置
  • 不指定初始位置时，记录从第一条记录开始显示。显示记录的条数有limit关键字指定。
  • 如果你指定的数目比实际表中存在的记录数大，那么查询结果就会把表的全部记录显示出来

select * from students order by height desc limit 3;

• 指定初始位置
  • 指定从哪条记录开始显示，并且可以指定显示多少条记录

# 查询时，从记录的下标为3的这个位置开始查起(下标是从0开始进行记数的)，一共查询3条记录。前面的3代表，从哪条记录开始查起，后面的3代表，一共查询几条记录，它们之间用逗号分开。
select * from students order by height desc limit 3,3;