sql 查询

查询原则：列看成变量，where后面看成表达式

1、常用函数：

max	求最大，select max(shop_price) from goods;
min	求最小
sum	求总和，select sum(goods_number) from goods;
avg	求平均
count	求行总数，select count(*) from goods;如果*用某一个字段，当字段为null时不计入总数，*数绝对行数

2、查询5种字句

where/group by/having/order by/limit	顺序不能乱

where    对表中的数据起作用
group by 以某种分类分组显示
having   对查询结果进行筛选，此时where已经发挥完作用得到了结果，对结果筛选只能用having
order by 排序，desc 倒序 asc 正序--既小的在前 一个字段没法排的，按第二个字段排，逗号隔开
limit    两个参数，偏移量，条数    limit 5 10	从第6条开始取10条

select a, (b-c) as d from goods where 1 having d > 200;    
select cat_id,sum(goods_number) from goods group by cat_id;    //分别查询所有栏目下的商品总数, 表根据不同的cat_id分别求和并列出来，cat_id相同的相加
select shop_price from goods where cat_id = 4 order by cat_id asc, goods_time desc limit 5 10;

3、子查询

from型子查询：内层sql的查询结果，当成一张临时表，供外层sql再次查询
select * from (select goods_id from goods order by cat_id asc,goods_id desc)
as tmp 
group by cat_id

where型子查询
where 列 = (内层sql),则内层sql返回的必是一个值，既只有一列的一行
where 列 in (内层sql)，则内层sql只返回单列，可以多行

select max(goods_id) from goods;
select goods_id,goods_name from goods where goods_id = 33;
合并
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

select goods_name from goods 
where goods_id in (select max(goods_id) from goods group by cat_id)


existe 型子查询，效率较高
是指把外层sql的结果，拿到内层sql去测试
如果内层sql成立，则该行取出，一行一行判断

如取出有商品的栏目
select cat_id,cat_name from category
where exists (select * from goods where goods.cat_id = category.cat_id);

4、多表联合查询

表与集合的关系：
每张表是一个集合，每一行是一个元素
表A —> 集合A 	3行
表B —> 集合B	2行
A*B 3*2=6行
select * from A,B;

两表相乘，where筛选，占内存很多，效率很低
select * from A,B where A.id = B.id;


左连接,表A取全部值，B中没有满足A.id = B.id条件的行则补空值，有取相应值，有多条满足则也取出多条
select * from 
A left join B on A.id = B.id 

右连接，左右连接是可以互换的，尽量用左连接
A left join B 等价于 B right join A

内连接，如果从集合的角度，内连接是左右连接的交集
即表A，B的行必须满足A.id=B.id才取出，不会补空值

union:合并2条或多条语句的结果，默认去重复，不去重复使用union all
语法：
sql union sql2
sql1和sql2查出来的内容可以源自两个表，列明不一致以sql1为准，只要结果集的列数一致就可以
union后的结果集也可以排序
sql1 union sql2 order by 字段

5、视图

视图：视图就是表的影子，会随着表的改变而改变，用于查询，改变一般没有意义(语法一般也通不过，不能更改)，如果想修改，视图数据和表的数据要一一对应，能够相互推出，最好不用
使用视图作用：
1、可以简化查询 
2、更精细的权限控制，比如两个网站合作，A网站不想给B看密码字段，其它字段开放，就可以创建视图，然后开放视图的权限给对方。再比如小说站，article表，1亿条数据，于是拆分成article1、article2、article3表，查询小说时不知道在哪张表，用视图合并查询即可

创建视图
creat view 视图名
as
select 语句 
例如：creat view starts as select * from A
创建视图完成后数据库会多出一张starts表，跟正常表一样查询

正常表存储都是三个文件 frm 表结构/MYD 数据/MYI 索引  视图只有frm结构文件，表示了映射关系，没有其它两个文件
删除 drop view A

下面是不建立临时表的视图，拿不准时用不用临时表可使用algorithn = undefind,效果上与建立临时表的普通视图一模一样
algorithn = marge 合并
temptable 临时表
undefind 未定义

creat algorithn = merge view v2
as 
select * from goods whew shop_price >300;

6、事务

有的引擎支持(InnoDB)，有的不支持(myisam)，支持不支持存储数据上没什么区别
转账：张三给李四转500块钱
张三	-500
李四	+500
在逻辑上，这个操作要么都成功，要么都不成功


开启事务 start transaction
update a2 set money = money + 1000 where name = ‘zhangsan’;
update a2 set money = money - 1000 where name = ‘lisi’;
commit 提交事务

开启事务，体现事务原子特性
start transaction；
update a2 set money = money + 500 where name = ‘zhangsan’;
#故意打错表名，模拟网络故障失败
update a2 set moneyyyy = money - 500 where name = ‘lisi’;
#扣李四500失败
#部分失败，之前成功步骤的回滚，在全部成功之前，之前成功的影响也被隐藏
rollback;

事务一旦完成，无法撤销。

7、触发器

待续

8、小技巧

select * from A where 1=1;    //用1=1主要是多条件查询时，拼接SQL查询字符串的时候较为方便
select (b-c) as d from goods where 1;    //查询b字段与c字段的差，并把差命名为d字段(又叫广义投影)

insert into result values (‘hello’,’world‘);   //一般插入语法
insert into a.goods select id from b.goods;    //把数据库b的goods表的id复制到a数据库里的goods表中

update main set num=floor(num/10)*10 where unm>=20 and num <=39;    
//把main表中在20-29之间的数改成20，30-39之间的数改成30，num/10取整，再乘以10

null最好不用用’’或0代替，比较时只能用 is null或者is not null,不易处理，效率较低

sql 查询

标签：