举例表结构
![4d5bead13f807449349a04583dd2647d.png](https://img-blog.csdnimg.cn/img_convert/4d5bead13f807449349a04583dd2647d.png)
t_user表
![2009cbbe1fc1fd5441ca940e518b5e6e.png](https://img-blog.csdnimg.cn/img_convert/2009cbbe1fc1fd5441ca940e518b5e6e.png)
t_book表
![5dca742155fdee644ac78f60191eb1df.png](https://img-blog.csdnimg.cn/img_convert/5dca742155fdee644ac78f60191eb1df.png)
t_cartitem表
![b6d521c7127e9b862b5537dce98b2a0f.png](https://img-blog.csdnimg.cn/img_convert/b6d521c7127e9b862b5537dce98b2a0f.png)
t_category表
![bb5c897b2404d84eb377c607a34552b7.png](https://img-blog.csdnimg.cn/img_convert/bb5c897b2404d84eb377c607a34552b7.png)
基础操作
- 增:insert into t_user VALUES(5,"xiaoming",12345,"254@qq.com",0,"xxxx",null,null)
- 删:delete from t_user where uid=5;
- 改:update t_user set loginname="ZS" where uid=6;
- 查:select * from 要查询的表名 where 条件,例如
![06ee070893573bc016fb4d10792921e1.png](https://img-blog.csdnimg.cn/img_convert/06ee070893573bc016fb4d10792921e1.png)
单表条件查询
- 取消重复行DISTINCT:select DISTINCT loginname from loginpass=1234
- 查询满足条件的元组:
- 比较大小:select bname from t_book where price<60
- 确定范围:select bname from t_book where price between 20 and 50
- 确定集合:select bname from t_book where press in ('清华大学出版社','人民邮电出版社')
- 字符匹配:[not] like '匹配串'
- %代表任意长度,a%b代表以a开头以b结尾的任意字符串,如acb、addfgdgb,例如select * from t_user where loginname like 'z%'
- _代表任意单个字符,a_b代表以a开头以b结尾的长度为3的任意字符串,如atb,例如select * from t_user where loginname like 'a_c'
- 涉及空值的查询只能用is不能用“=”:select * from t_user where gender is NULL
- 排序order by默认升序:select bname from t_book where authur='霍斯特曼' order by price
- 聚集函数:select 聚集函数 from 表名 where 条件,聚集函数不能用在条件里
- 统计元组个数:count(*)
- 计算某一列值的总和:sum(列名)
- 计算某一列值的平均值:avg(列名)
- 求某一列最大值:max(列名)
- 求某一列最小值:min(列名)
- 查询结果分组group by:以作者分组并查找该作者书籍均价大于50的作者,select author, avg(price) from t_book group by author having avg(price)>50
多表查询
- 连接查询
- 等值与非等值连接:例如查询在购物车中的书籍信息 select t_book.* from t_book,t_cartitem where t_book.bid=t_cartitem.bid
- 外连接:取left左边的表的元组数,查找购物车中的书籍名和加购数量 select t_book.bname,t_cartitem.quantity from t_cartitem left outer join t_book on (t_book.bid=t_cartitem.bid)
- 多表连接:查询购物车中的用户名和书名 select t_user .loginname,t_book.bname from t_book,t_cartitem,t_user where t_book.bid=t_cartitem.bid and t_user.uid= t_cartitem.uid
- 嵌套查询
- 带有in谓词的子查询:查询类别为程序设计的图书名 select bname from t_book where cid in (select cid from t_category where cname='程序设计')
- 带有比较运算符的子查询:
- 查询类别为程序设计的图书名 select bname from t_book where cid = (select cid from t_category where cname='程序设计');
- 查询大于所有书籍平均价格的书 select bname,price from t_book x where price >= (select avg(price) from t_book y where x.bname=y.bname)
- 带有any或all谓词的子查询
- 查询程序设计类书籍比任意一本数据库书籍价格低的书籍名称:select bname price from t_book where price < any(select price from t_book where cid=1) and cid=5
- 查询程序设计类书籍比所有数据库书籍价格都低的书籍名称:select bname price from t_book where price <all(select price from t_book where cid=1) and cid=5