mySQL数据库查询数据

单表查询

首先创建数据库内容：

create table student(
	id char(36) primary key,
	name varchar(8) not null,
	age int(3) default 0,
	mobile char(11),
	address varchar(150)
)
insert into student 
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student 
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student 
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student 
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student 
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');

单表查询语法：
select * | 字段列表 | 表达式 from table_name [where 条件] [order by 字段列表]
说明：

1. *：相当于按照表中字段顺序罗列表中的所有字段

select * from student

2. 字段列表：当查询结果只是表中部分字段时，可将要显示的字段罗列出来，字段之间以逗号间隔

select name,address from student

3. 表达式：可以是算术运算符也可以是数据库中的函数

select age+1 from student;
select length(name) from student;

4. table_name：表名
5. where：指定查询过滤条件
6. order by：对查询结果进行排序

字段别名：

1. select所选字段后面可以指定别名以使查出来的结果所显示的字段更好理解，字段名与别名之间使用空格或as关键字间隔；为了阅读方便，推荐使用as关键字这种方式定义字段别名。

select age+1 stu_age from student;
select length(name) as name_length from student;

2. 在默认情况下，查询结果中显示的字段名为大写字母，如果别名中包含空格、特殊字符（例如%）或者对大小写敏感，则别名需要使用双引号引起来。

select age+1 "stu age" from student;
select length(name) as "name%length" from student;
#不加双引号，上面SQL语句均无法执行

表别名：
表名与别名之间使用空格间隔（MySQL数据库中表的别名可以使用as关键字间隔，但Oracle数据库不允许）。

select student.name,student.address from student
select s.name,s.address from student s
#表别名简化了SQL语句

注意：
表的别名最多可以有30个字符；
表别名的作用方法在整个select语句中都有效，离开select语句即无效。

关系表达式：
is、null、关系运算符、and、or、between and、in

#null 查询的为空字段时用is，非空字段时用is not
select * from student where address is null
select * from student where address is not null

#关系运算
select * from student where age>11

#and or
select * from student where age<28 and address is not null
select * from student where age=11 or age=28

#between 小数 and 大数  （包括边界值）
select * from student where age between 11 and 28
select * from student where age between 28 and 11

#in 相当于or
select * from student where age in (11,24)
select * from student where age=11 or age=24

模糊查询：

#模糊查询
#% 匹配0次或多次
#查询以“张三开头的名字”
select * from student where name like '张三%'

#_只能匹配一次
#查找以“张三”开头并且后面有且仅有一个字符跟着的名字
select * from student where name like '张三_'

#escape 将其后紧跟的字符转为普通字符
select * from student where name like '%%%'
select * from student where name like '%A%%' escape 'A'
select * from student where name like '%A_%' escape 'A'

注意：
escape后面单引号中只能是单个字符；
escape后面可以是字母、#、$、,、\等字符；

order by：

#order by 排序，必须置于SQL语句的最后

#升序用asc，降序用desc
select * from student order by age asc 
select * from student order by age desc

#默认为asc，即可以将asc省略不写
select * from student order by age

#当按多个字段排序时，优先排序前面的，当前面的相同时，再将相同的数据按后面的字段排序
select * from student order by age,mobile desc
select * from student order by age desc,mobile

不同数据类型，升序的含义如下：

1. 数字类型：小值在前面显示；
2. 日期类型：早的日期在前面显示；
3. 字符类型：依据字母顺序显示，a在前，z最后；

dual伪表：

#dual伪表 此表不存在，只是为了让sql语句完整
#取余 mod
select mod(1,3) from dual
select mod(1,3) from student
#默认使用伪表dual
select mod(1,3)

distinct：去重

#distinct 去重（所有的字段都必须放在distinct后面）
select age from student
select distinct age from student
#只有当age和name都重复时才会去掉
select distinct age,name from student
#若只对age去重，不对name去重（如下），则最终显示时有4个age和5个name，从而无法显示，所以所有的字段都必须放在distinct后面
select name,distinct age from student

内置函数

单行函数： 单行函数仅对单条数据中的列进行操作并且返回一个结果；
多行函数： 多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数；

单行函数：

#单行函数
#length() 返回字符串存储长度
select name,length(name) length from student

#char_length() 返回字符串中字符个数
select name,char_length(name) length from student

#concat() 将多个字符串首尾相连后返回,遇到null则全为null
select concat(id,',',name,',',mobile,',',address) info from student

#concat_ws() 将多个字符串按照第一个字符进行首尾相连
select concat_ws(',',id,name,mobile,address) info from student

#trim() 返回去掉str源字符串两端、前缀或后缀字符串
select trim('  Tom  ') from dual;
select trim(both 'a' from 'aaaTomaaa');
select trim(leading 'a' from 'aaaTom');
select trim(trailing 'abc' from 'Tomabc');

#substr() substring() 二者完全一样
select substr('abcdef',2);
select substr('abcdef',2,1);
select substr('abcdef',2,3);

#replace() 替换
select replace('110#119#120','#','$')

#reverse() 反转
select reverse('abc')

#now() 当前时间
select now()
#date_format 将时间格式化
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')

#round() 进行四舍五入后返回
select round(3.1415926)
select round(3.1415926,3)
#truncate() 直接截取后返回
select truncate(3.1415926,3)

#strcmp() 比较字符串，相同则返回0；第一个小于第二个返回-1，否则返回1
select strcmp('abc','abc')
select strcmp('abc','a')

#convert() 将前一类型转换为后一类型,后一类型可以为可以是char（字符型）、date（日期型）、
#time（时间型）、datetime（日期时间型）、 signed（整型）和decimal（浮点型）类型中的一个
select convert(now(),date)

#if(a,b,c) 类似于三目运算符，若a为真则返回b，否则返回c
select if(address is null,'未知',address) from student
#ifnull(a,b) 如果a为null则返回b，否则返回a
select ifnull(address,'未知') from student

注： 比较重要的有四个date_format、convert、if、ifnull

多行函数：

1. 多行函数忽略空值——可以通过ifnull函数或if(expr1, expr1, expr1)用一个值代替空值；
2. 多行函数默认考虑重复值——可以通过distinct关键字使组函数不考虑重复值；

#聚合函数
select avg(age) from student
select sum(age) from student
select max(age) from student
select min(age) from student
select count(age) from student
#求标准差
select stddev(input) from student
#求方差
select variance(input) from student

#1、聚合函数统计时忽略null值
select count(address) from student
#但可以通过如下方法使其不忽略null值
select count(ifnull(address,'')) from student
#2、默认情况下，字段不能和聚合函数一起使用
select name,avg(age) from student

group by： group by用于将表中数据划分为若干个组，group by后面用于指定分组的依据。

1. 只有对应列相同的多行数据才会归为一组。

#将数据按name分组，在查询每个组的name
select name from student group by name
#将数据按name分组，再查询每个组的id分别有多少个
select count(id) from student group by name

2. 如果select语句中使用group by进行了分组，则select子句中只可以有组函数和分组字段，不能含有其他字段，否则查询结果没有意义。

#如下情况聚合函数就可以和字段同时使用，但必须是分组的字
#将数据按name分组，分别查询每个组的名字都是什么、每个组的id都有多少个
select name, count(id) from student group by name

3. 可以用having进行数据过滤

#将最后的结果中id个数>1的组查出来（查询哪个姓名重名）
select name, count(id) from student group by name having count(id)>1
#where 也是过滤功能，但是它不能跟聚合函数

4. 如果group by子句后面跟着order by子句，则order by子句用于排序的字段必须是多行函数或分组字段
5. 关键字顺序：

#where -> group -> by having -> order by 
#先用where对数据进行过滤，再用过滤后的数据进行分组，
#再将分组后的数据用having再进行过滤，最后将过滤后的结果再通过order by排序