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

1、常用函数：

max求最大，select max(shop_price) from goods;

min求最小

sum求总和，select sum(goods_number) from goods;

avg求平均

count求行总数，select count(*) from goods;如果*用某一个字段，当字段为null时不计入总数，*数绝对行数

substr  截取字符串，注意从1开始而不是0 select * from goods where SUBSTR(`goods_number`,5) = 9;

SUBSTRING(id,1,6) 截取字符串，返回从第1个字符开始，取六个字符

SUBSTRING_INDEX(id,'-',2) 截取字符串，返回从字符串中第二个‘-’前面的所有字符

//给所有用户加余额的10分之1作为积分

UPDATE `users` SET jifen= jifen + CAST(money/10 AS DEC(14,2)) WHERE 1

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 —> 集合B2行

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,不易处理，效率较低

9、例子

1、查询根据天分组，每天的条数

SELECT COUNT(*) count_num,a.*//查询每天的条数

FROM

(

SELECT *

FROM dl_match

WHERE 1=1

ORDER BY begin_time ASC

) a

GROUP BY DATE(begin_time)//DATE(begin_time)按天分组

2、根据人分组，查询出每个人的总花费，不存在花费的用0代替null

SELECT a.*,c.divide,IF(sumcoin IS NULL, '0', sumcoin) AS sumcoin

FROM `dl_users` a

LEFT JOIN

(

SELECT SUM(coin) AS sumcoin,touid FROM dl_coindetail GROUP BY touid

) AS b ON a.id = b.touid

INNER JOIN `dl_emceelevel` c ON a.levelnumber = c.levelid

WHERE is_host = 1 AND user_status<>0

ORDER BY sumcoin DESC;

3、查询一分钟内的记录

SELECT `detail` FROM `a` WHERE `type` = 2 AND `add_time` > DATE_SUB(NOW(),INTERVAL 1 MINUTE) ORDER BY id DESC LIMIT 1

3、查询6秒内的记录

SELECT `detail` FROM `a` WHERE `type` = 2 AND `add_time` > date_sub(now(),interval 6 second) ORDER BY id

10、修改表名，字段名

修改表名

RENAME TABLE `dl_video_tag_relationships` TO `dl_tag_term_relationships`;

RENAME TABLE `dl_video_tags` TO `dl_tag_terms`;

修改字段名

ALTER TABLE `dl_tag_terms` CHANGE `tag_id` `term_id` BIGINT;

ALTER TABLE `dl_tag_term_relationships` CHANGE `tag_id` `term_id` BIGINT;

11、替换表中某个字段的部分字符串

UPDATE `dl_posts` SET post_title = REPLACE(post_title,'酵母','天空');

12、把某一行记录内容与id结合更新到该记录

UPDATE `dl_users` SET user_nicename = CONCAT(user_nicename,id)

13、查询昨天的记录

SELECT * FROM `record` WHERE DATEDIFF(CURDATE(),DATE(a.add_time))=1