1.创建一个表如图
create table xxx(
id int not null primary key,
name varchar(20) not null
)
2.多表条件查询
select * from clothes,user where clothes.id=user.id;
3.嵌套查询
select * from clothes where color=(select color from clothes where size=44 limit 1);
4.并查询
select id from clothes union select id from user;
5.交查询
select id from clothes where id in (select id from user);
6.求和(sum(字段) 对字符串和时间无效)
select sum(price) from clothes;
7.求平均值(avg(字段) 对字符串和时间无效)
select avg(price) from clothes;
8.计数(注:count(字段名)不包含NULL)
select count(*) from clothes;
9. 求最大值
select max(price) from clothes;
10.求最大值的所在行(降序排列并分页查询第一个元素)
select * from clothes order by price desc limit 1;
11.求最小值
select min(price) from clothes;
12.求最小值的所在行(升序排列并分页查询第一个元素)
select * from clothes order by price asc limit 1;
select * from clothes where price = (select max(price) from clothes );
13. distinct 字段中值唯一
select distinct brand from clothes;
14.默认升序排列(asc可去掉)
select * from clothes order by price asc;
15.降序排列
select * from clothes order by price desc;
16.group by 分组
group by 可以达到去重的作用,相同的数据只会显示一份
**(1)通过商标进行分组查询商标、同一商标的商品数量和种类行数**
select count(style),brand,sum(num) from clothes group by brand;
**(2)通过商标进行分组查询商标和行数大于1的种类行**
Select brand,count(style) from clothes group by brand having count(style)>1;
17.子查询
select * from users where name in (select name from users where name='帅哥成');
select * from users where age = (select max(age) from users );
对表进行操作
1.从指定数据库中模糊查询表名
select table_name from information_schema.tables where table_schema='数据库名'and table_name like "%"#{表名}"%"
2.删除表(必须用${})
drop table ${表名}
多表联合查询
例子:
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
1) 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
#隐式内连接
select a.*,b.* from a,b where a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2)左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3) 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4) 全连接
select a.*,b.* from a left join b on a.id=b.parent_id
union
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
两个搜索条件或查询
<if test="search!=null and search!=''">
and (res_name like "%"#{search}"%" or res_code like "%"#{search}"%")
</if>
三表查询
①
select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark
from table1 a,table2 b,table3 c where a.sems_role_rid=b.rid and a.udeptid=c.deptid
②
select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark
from table1 a left join table2 b on a.sems_role_rid=b.rid left join table3 c on a.udeptid=c.deptid
Union查询
使用条件:两个结果集的列数相同就可以使用,即使字段类型不相同
当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all ,mysql 会把所有的记录返回,且效率高于union
SELECT id,name,age,phone FROM users union all SELECT id,name,old,nunmber FROM cc;
视图
(某些联合表构成的视图是不可更新的,更新视图会导致原来的数据也同样更新,视图是虚拟的定义,视图数据来自表中,表中数据更新,视图中的数据也会随着更新)
创建视图(两个表的列名不能相同注意起别名)
create view view_users_cc as
select a.id,a.name as username,age,salary,phone,b.name,old,nunmber from users a left join cc b on a.id=b.id;
查看视图
select * from view_users_cc;
删除视图
drop view view_users_cc;