SQL语句
# 导入数据库表 查看表结构
mysql> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId | int(4) | NO | PRI | NULL | auto_increment |
| bName | varchar(255) | YES | | NULL | |
| bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | |
| publishing | varchar(255) | YES | | NULL | |
| price | int(4) | YES | | NULL | |
| pubDate | date | YES | | NULL | |
| author | varchar(30) | YES | | NULL | |
| ISBN | varchar(255) | YES | | NULL | |
+------------+------------------------------------------------+------+-----+---------+----------------+
逻辑运算符
or
# 选择出书籍价格为（ 30,40,50,60）的记录,只显示书籍名称，出版社，价格
mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;
+--------------------------------------+--------------------------+-------+
| bName | publishing | price |
+--------------------------------------+--------------------------+-------+
| Illustrator 10完全手册 | 科学出版社 | 50 |
| FreeHand 10基础教程 | 北京希望电子出版 | 50 |
| 网站设计全程教程 | 科学出版社 | 50 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| Delphi 5程序设计与控件参考 | 电子工业出版社 | 60 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
+--------------------------------------+--------------------------+-------+
mysql> select bName,publishing,price from books where price in (30,40,50,60);
+--------------------------------------+--------------------------+-------+
| bName | publishing | price |
+--------------------------------------+--------------------------+-------+
| Illustrator 10完全手册 | 科学出版社 | 50 |
| FreeHand 10基础教程 | 北京希望电子出版 | 50 |
| 网站设计全程教程 | 科学出版社 | 50 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| Delphi 5程序设计与控件参考 | 电子工业出版社 | 60 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
+--------------------------------------+--------------------------+-------+
and
# 选出价格在（30-60）的记录，显示书籍名称以及价格
mysql> select bName,price from books where price >30 and price < 60 limit 6; # 数据太多只显示6行数据
+---------------------------------+-------+
| bName | price |
+---------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
+---------------------------------+-------+
# between and 数据会包含首和尾
mysql> select bName,price from books where price between 30 and 60 limit 6;
+---------------------------------+-------+
| bName | price |
+---------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
+---------------------------------+-------+
not
# 取出价格不大于40书籍的名称以及价格
mysql> select bName,price from books where price < 40;
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 34 |
+-----------------------+-------+
mysql> select bName,price from books where not price > 40;
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 34 |
+-----------------------+-------+
算术运算符
=
# 寻找id=10的书籍信息
mysql> select bName,price from books where bid=10;
+-------------------------------+-------+
| bName | price |
+-------------------------------+-------+
| 3D MAX 3.0 创作效果百例 | 45 |
+-------------------------------+-------+
!=
# 寻找价格不等于40的书籍的名称以及价格 显示3条数据
mysql> select bName,price from books where price != 40 limit 3;
+-----------------------------+-------+
| bName | price |
+-----------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
+-----------------------------+-------+
mysql> select bName,price from books where not price = 40 limit 3;
+-----------------------------+-------+
| bName | price |
+-----------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
+-----------------------------+-------+
<= >=
# 选出价格大于等于30小于等于40的书籍
mysql> select bName,price from books where price >=30 and price <= 40;
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 34 |
+-----------------------+-------+
模糊查询
%
匹配0个或多个字符
mysql> show variables like "%query_cache"; # 包含query_cache无论前面以什么开头
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
mysql> show variables like "query_cache%"; # 包含query_cache无论以什么结尾
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
_
之匹配一个字符
mysql> show variables like "_query_cache"; # 查询为空 在上述可以看到没有以一个字符开头的_
Empty set (0.01 sec)
mysql> show variables like "_____query_cache%"; # 匹配五个找到query_cache
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
子查询
- 在 select 的 where 条件中又出现了 select， 查询中嵌套着查询
- 本质来说一个SQL语句的查询结果作为另外一个语句的查询条件
mysql> select bTypeId from category where bTypename = "网络技术"; # 通过名称查询图书名称为网络技术的ID号
+---------+
| bTypeId |
+---------+
| 7 |
+---------+
mysql> select bName from books where bTypeId=7; # 通过ID好在book找出id为7的图书名称
+----------------------+
| bName |
+----------------------+
| Internet操作技术 |
+----------------------+
mysql> select bName from books where bTypeID=(select bTypeId from category where bTypeName="网络技术");
+----------------------+
| bName |
+----------------------+
| Internet操作技术 |
+----------------------+
limit
作用
- 用来限制显示的条目熟练
- LIMIT 接受一个或两个数字参数。参数必须是一个整数常量
- 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目
- 初始记录行的偏移量是 0（ 而不是 1）
'''
1:表示从第一条偏移一行 取五行数据
2:1偏移一行 相当于 1+1=2 即从第二行开始
'''
mysql> select * from category limit 1 ,5;
+---------+--------------+
| bTypeId | bTypeName |
+---------+--------------+
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
+---------+--------------+
'''
1:我们对所有记录排序以升序排列，取出前面 3 个来
2:0可以省略不写
'''
mysql> select bName,price from books order by price asc limit 0,3;
+-----------------------------+-------+
| bName | price |
+-----------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
+-----------------------------+-------+
order by
作用
- 升序： order by “排序的字段” asc 默认
- 降序： oredr by “排序的字段” desc
mysql> select bName,price from books where price in (50,60,70) order by price asc limit 4;
+--------------------------------------+-------+
| bName | price |
+--------------------------------------+-------+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
+--------------------------------------+-------+
mysql> select bName,price from books where price in (50,60,70) order by price desc limit 4;
+--------------------------------------+-------+
| bName | price |
+--------------------------------------+-------+
| ASP数据库系统开发实例导航 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| Illustrator 10完全手册 | 50 |
+--------------------------------------+-------+
聚合函数
Sum
# 显示所有图书单价的总合
mysql> select sum(price) from books;
+------------+
| sum(price) |
+------------+
| 10048 |
+------------+
avg
# 求书籍 Id 小于 3 的所有书籍的平均价格
mysql> select avg(price) from books where bId<=3;
+------------+
| avg(price) |
+------------+
| 39.3333 |
+------------+
max
# 求所有图书中价格最贵的书籍
mysql> Select bName,price from books where price=(select max(price) from books);
+----------------------------------------+-------+
| bName | price |
+----------------------------------------+-------+
| Javascript与Jscript从入门到精通 | 7500 |
+----------------------------------------+-------+
min
# 求所有图书中价格便宜的书籍
mysql> select bName,price from books where price=(select min(price) from books);
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 34 |
+-----------------------+-------+
count
# 统计价格大于 40 的书籍数量
mysql> select count(*) from books where price>40;
+----------+
| count(*) |
+----------+
| 43 |
+----------+
group by
以某个字段为单位进行数据查询
# 分组只能获取分组字段的数据 不能获取其余数据
mysql> select author from books group by author limit 3;
+--------+
| author |
+--------+
| 丁佳 |
| 于佳 |
| 付强 |
+--------+
# 使用函数获取分组之后其余的数据
mysql> select author,group_concat(bName) from books group by author limit 3;
+--------+-------------------------------------------+
| author | group_concat(bName) |
+--------+-------------------------------------------+
| 丁佳 | 3D Studio Max 3综合使用 |
| 于佳 | Access 2000应用及实例基集锦 |
| 付强 | 活学活用Delphi5,3DS MAX 4横空出世 |
+--------+-------------------------------------------+
# 和聚合函数使用
mysql> select price, max(price) from books group by price limit 3 ;
+-------+------------+
| price | max(price) |
+-------+------------+
| 34 | 34 |
| 41 | 41 |
| 43 | 43 |
+-------+------------+
having
作用
- 进行数据过滤
与where区别
- 执行优先级 where > group by >having
- where执行优先级大于group by因此可以使用任意字段 但是不能使用聚合函数
- having 发生在分组之后 因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
select price from books group by price having bId>3; # 报错
ERROR 1054 (42S22): Unknown column 'bId' in 'having clause'
mysql> select price,group_concat(bName) from books group by price having avg(price)>30 limit 3;
+-------+-------------------------------------------------------------+
| price | group_concat(bName) |
+-------+-------------------------------------------------------------+
| 34 | 网站制作直通车 |
| 41 | 黑客与网络安全 |
| 43 | pagemaker 7.0短期培训教程,网络程序与设计－asp |
+-------+-------------------------------------------------------------+
连接查询
内连接
根据表中的共同字段进行匹配 将匹配出来的数据显示出来
# 将book表中id与category表中id相等的数据显示出来
mysql> select bName,price,bTypeName from books inner join category where books.bTypeId=category.bTypeID limit 3;
+-----------------------------+-------+-----------+
| bName | price | bTypeName |
+-----------------------------+-------+-----------+
| 网站制作直通车 | 34 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | 网站 |
+-----------------------------+-------+-----------+
左连接
- 以左表为主表将共同字段的数据匹配出来
- 左表数据全部显示右表将以null显示
# 以左表为基础 不符合条件的左表显示出来 右表以null显示
mysql> select a_name,a_dept,b_name,b_dept from a_table left join b_table on a_table.a_name=b_table.b_name;
+--------+-----------+--------+-----------+
| a_name | a_dept | b_name | b_dept |
+--------+-----------+--------+-----------+
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老六 | 总裁部 | NULL | NULL |
| 老李 | 运营部 | NULL | NULL |
| 老六 | 总裁部 | NULL | NULL |
| 老李 | 运营部 | NULL | NULL |
+--------+-----------+--------+-----------+
右连接
- 以右表为主表将共同字段的数据匹配出来
- 右表数据全部显示左表将以null显示
mysql> select a_name,a_dept,b_name,b_dept from a_table right join b_table on a_table.a_name=b_table.b_name;
+--------+-----------+--------+-----------+
| a_name | a_dept | b_name | b_dept |
+--------+-----------+--------+-----------+
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老王 | 秘书部 | 老王 | 秘书部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| 老刘 | 设计部 | 老刘 | 设计部 |
| NULL | NULL | 老懒 | 人事部 |
| NULL | NULL | 老黄 | 生产部 |
| NULL | NULL | 老懒 | 人事部 |
| NULL | NULL | 老黄 | 生产部 |
+--------+-----------+--------+-----------+
全连接
- 返回左右表中所有的记录
- 返回左右表中连接字段相等的记录
mysql> select * from a_table full join b_table where a_dept=b_dept;
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_dept | b_id | b_name | b_dept |
+------+--------+-----------+------+--------+-----------+
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老刘 | 设计部 | 3 | 老刘 | 设计部 |
| 3 | 老刘 | 设计部 | 3 | 老刘 | 设计部 |
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老刘 | 设计部 | 3 | 老刘 | 设计部 |
| 3 | 老刘 | 设计部 | 3 | 老刘 | 设计部 |
+------+--------+-----------+------+--------+-----------+