create table user ( id int auto_increment, name varchar(20) not null default '', age tinyint unsigned not null default 0, index id (id) )engine=innodb charset=utf8;
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like '诺基亚%';
mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;
+------+------+---------+ | name | gk | pj | +------+------+---------+ | 张三 | 2 | 60.0000 | | 李四 | 2 | 50.0000 | +------+------+---------+ 2 rows in set (0.00 sec)
3.4、order by 与 limit查询
3.4.1 按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;
3.4.2 按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;
3.4.3 接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc;
3.4.4 取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;
3.4.5 取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;
3.5 连接查询
3.5.1 取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id;
3.5.2 取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id where ecs_goods.cat_id = 4;
3.5.3 取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id left join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_id where ecs_goods.cat_id = 4;
+------+----------+ | tid | tname | +------+----------+ | 1 | 国安 | | 2 | 申花 | | 3 | 公益联队 | +------+----------+ 3 rows in set (0.00 sec)
mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime -> from -> m left join t as t1 -> on m.hid = t1.tid -> left join t as t2 -> on m.gid = t2.tid;
A表: +------+------+ | id | num | +------+------+ | a | 5 | | b | 10 | | c | 15 | | d | 10 | +------+------+
B表: +------+------+ | id | num | +------+------+ | b | 5 | | c | 15 | | d | 20 | | e | 99 | +------+------+
要求查询出以下效果:
+------+----------+ | id | num | +------+----------+ | a | 5 | | b | 15 | | c | 30 | | d | 30 | | e | 99 | +------+----------+
create table a ( id char(1), num int ) engine myisam charset utf8;
insert into a values ('a',5),('b',10),('c',15),('d',10);
create table b ( id char(1), num int ) engine myisam charset utf8;
insert into b values ('b',5),('c',15),('d',20),('e',99);
mysql> # 合并 ,注意all的作用 mysql> select * from ta -> union all -> select * from tb;
+------+------+ | id | num | +------+------+ | a | 5 | | b | 10 | | c | 15 | | d | 10 | | b | 5 | | c | 15 | | d | 20 | | e | 99 | +------+------+
参考答案:
mysql> # sum,group求和 mysql> selectid,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
+------+----------+ | id | sum(num) | +------+----------+ | a | 5 | | b | 15 | | c | 30 | | d | 30 | | e | 99 | +------+----------+ 5 rows in set (0.00 sec)
3.7、子查询:
查询出最新一行商品(以商品编号最大为最新,用子查询实现)
select goods_id,goods_name from ecs_goods where goods_id =(select max(goods_id) from ecs_goods);
查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);
用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;
用exists型子查询,查出所有有商品的栏目
select * from category where exists (select * from goods where goods.cat_id=category.cat_id);
创建触发器:
CREATE trigger tg2 after insert on ord for each row update goods set goods_number=goods_number-new.num whereid=new.gid
CREATE trigger tg3 after delete on ord for each row update goods set goods_number=good_number+old.num whereid=old.gid
CREATE trigger tg4 after update on ord for each row update goods set goods_number=goods_number+old.num-new.num whereid=old.gid
四、常用表管理语句
设置字符编码 set names gbk;
查看所有数据库：show databases;
查看所有表：show tables
查看表结构：desc 表名/视图名
选择表 use 表名;
查看建表过程：show create table 表名
查看建视图过程：show create view 视图
查看所有详细表信息：show table status\G(让结果显示好看一些)
查看某张表详细信息：show table status where name='goods（表名）'\G
删除表：drop table 表名
删除视图：drop view 视图名；
删除列：alter table drop column 指定列
改表名：rename table oldName to newName
更新表：update 表名 set 字段
插入数据：insert into 表名 value()
清空数据：truncate 表名;(相当于删除表在重建)
写错语句退出:\c
让结果显示好看一些:\G
五、查询总结
5.1 insert
insert into 表名 插入列与值要严格对应
数字不必加单引号 字符串必须加单引号
例子：insert into test(age,name)values(10,'小明');
5.2 update操作
// 例子： update user set age=8 where name=lianying; //（注意where条件不加会影响所有行，需要小心）
# 例子:语句有严格的顺序 mysql> selectid,sum(num) -> from -> (select * from a union select * from b) as temp -> group by id -> having sum(num)>10 -> order by sum(num) desc -> limit 0,1;
5.8 子查询
where字查询：（内层的查询结果作为外层的比较条件）
静态的：select goods_id,goods_name from goods where goods_id=32;
动态的：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);
#取出每个栏目下最新的商品： select goods_id,cat_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);
5.9 from子查询
#每个栏目下最新的商品： mysql> select goods_id,goods_name from (select * from goods where 1 order by cat_id desc) as tmp -> group by cat_id;
5.10 exists子查询：
#查询栏目下是否有商品 mysql> select * from category -> where exists(select * from goods where goods.cat_id=category.cat_id)
5.11 内连接查询（重要）
内连接是左右连接结果的交集
select xxx from table1 inner jion table2 on table1.xx=table2.xx mysql> select boy.hid,boy.bname,girl.hid,girl.gname -> from -> boy inner join girl on boy.hid=girl.hid;
5.12 左连接特点
以左表的数据为标准，去找右表的数据，查不到的为NULL
#左连接 mysql> select boy.hid,boy.bname,girl.hid,girl.gname -> from -> boy left join girl on boy.hid=girl.hid;
#右连接 mysql> select boy.hid,boy.bname,girl.hid,girl.gname -> from -> boy right join girl on boy.hid=girl.hid;
mysql> select goods_id,cat_name,goods_name,shop_price -> from -> goods left join category on goods.cat_id= category.cat_id -> where goods.cat_id=4;
5.13 union查询
把2条或多条的额查询结果，合并成1个结果集
sql1 N行
sql2 M行
sql1 union sql2，N+M行
union语句必须满足一个条件：各语句取出的列数要相同
union语句中不用写order by 因为sql合并后得到总的结果集可以order by 字句order by失去意义
场景：2条语句，各自的where非常复杂，可以简化成简单的条件在union
注意：使用union时，完全相等的行将会被合并。合并是比较耗时的操作，一般不让union合并，使用union all 可以避免合并 对速度有提升
mysql> select * from a -> union all #union all 可以避免重复语句合并 -> select * from b;
mysql> select goods_id,cat_id,goods_name,shop_price from goods where cat_id=2 -> union -> select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4; ```
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
这样，如果brithday是未来的年月日的话，计算结果为0。
下面的sql语句计算员工的绝对年龄，即当birthday是未来的日期时，将得到负值
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') <date_format(birthday, '00-%m-%d')) as age from employee
