mysql表单内容的查询

查询所有字段:select * from student;

查询指定字段:select name,class_no from student;

查询符合条件字段的列:select name from student where id=3;

select name from student where id between 1 and 4;

select name from student where name like '耗%';

通配符:  _ 代替一个字符  %替代多个字符

一般使用!=,用<>代替

= , <>, !=, >,<,>=,<=,between ... and ...

 

列名 between ... and ...

列名 like ...

条件 and 条件2

or 和and一样使用

and 可以用 && 代替

in操作符用来满足条件用的,不过效率低

 

select * from student where id in(1,2,3,4);

select * from student where id not in(1,2,3,4);

 

找到空数据:select * from student where id is null;

去重查询:select distinct class_no from student;

 

第二条开始的两条结果:select * from student limit 2,2;

 

 

有右表为基础,显示左表所有列,不管是否与关联条件匹配,左表中只显示匹配的数据,不匹配的用NULL填充

select e.name,d.department_name from employee as e

right join department as d on e.department_id=d.department_id;

 

全连接(全外连接)因为MySQL不支持full join  用left 和right连接union一下,代替全连接

左右表都不做限制,所有记录都显示,两表不匹配的地方均为NULL

select e.name,d.department_name from employee as e

left join department as d on e.department_id=d.department_id

union

select e.name,d.department_name from employee as e

right join department as d on e.department_id=d.department_id;

 

查询结果排序

order by

升序

select * from salary order by grade;

降序

select * from salary order by grade desc;

 

分组查询

SQL函数

聚合函数:对一组值执行计算,返回单个值

分析函数:基于一组行来计算聚合值

排名函数:分区中的每一行返回一个排名值

行集函数:在SQL像表引用一样使用的对象

标量函数:对单一值进行运算,返回单一值

 

聚合函数

count(*) count(列名)结果集的记录数  空值不计算在内

select count(*) from salary;

select count(number) from salary;

 

AVG(列名) 不算空值 返回平均值

select AVG(number) from salary;

 

MAX(列名)指定列最大值

select MAX(number) from salary;

 

MIN(列名)指定列最小值

select MIN(number) from salary;

 

 

SUM(列名)指定列的和

select SUM(number) from salary;

 

select number*12 from salary;  年薪

select number/21.75/7.5 from salary; 日薪

 

 

 

create table orders(

id int primary key,

price int,

Orderdate date,

customer varchar(22)

);

 

 

insert into orders

select 1,1000,'2018-8-20','sirius' union all

select 2,600,'2018-9-21','robert' union all

select 3,800,'2018-9-21','baby'union all

select 4,1000,'2018-8-20','sirius' union all

select 5,600,'2018-9-21','robert' union all

select 6,800,'2018-9-21','baby'union all

select 7,800,'2018-9-21','baby'union all

select 8,1000,'2018-8-20','sirius' union all

select 9,600,'2018-9-21','robert';

 

分组求和

select sum(price),customer from orders group by customer;客户分组

select sum(price),Orderdate from orders group by Orderdate;日期分组

select customer,Orderdate,sum(price) from orders group by customer,Orderdate;客户+日期分组

在分组中不能使用where作为条件语句,要用having

select customer,Orderdate,sum(price) from orders group by customer,Orderdate having customer='baby';

查询订单总额小于3000的客户,按用户分组 计算单笔消费小于800不计算在内

select sum(price),customer from orders where price>800 group by customer having sum(price)<3000;

 

 

 

select now();获得当前时间 date+time 结构

select current_timestamp();获取当前时间的时间戳

date_format(date,format)时间转换字符

time_format(time,format)

select date_format(now(),'%Y%m%d%H%i%s);

upper(列名)/ucase(列名)小写变大写

lower(列名)/lcase(列名)大写变小写

substring(列名,start,length)/mid(列名,start,length)截取字符串

round(列名,decimals)截取小数,保存小数几位数