一、数据库基础
数据库(database):保存有组织的数据的容器
表(table):某种特定类型数据的结构化清单。
- 储存在表中的数据是同一种类型的数据或清单。
- 表的名字是唯一的。
列(column):表中的一个字段。所有表都是由一个或多个列组成的。
- 数据类型:所允许的数据的类型。每个表列都有相应的数据类型,它限制(或允许)该列中存储的数据。
- 数据类型及其名称是SQL不兼容的一个主要原因。
行(row):表中的一个记录,也可称为数据库记录。
主键(primary key):一列(或一组列),其值能够唯一标识表中每一行。
没有主键,更新或删除表中特定行就极为困难。
作为主键的列需要满足以下条件:
- 任意两行都不具有相同的主键;
- 每一行都必须具有一个主键值(主键列不允许NULL值);
- 主键列中的值不允许更新或修改;
- 主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行)。
1.什么是SQL
SQL是Structured Query Language(结构化查询语言)的缩写。SQL是一种专门用来与数据库沟通的语言。
2.使用注释
(1).--
-- 之后的文本就是注释,注意是(--)两个连字符
-- -------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table population scripts for MySQL.
-- -------------------------------------------
(2).#
SELECT prod_name
FROM products
LIMIT 5,3; -- 从第5行开始检索,检索5行
(3).多行注释/* */
/*SELECT prod_name
FROM products
LIMIT 5,3; %/
关键字:是SQL的保留字,关键字不能用作表或列的名字
SQL语句不区分大小写,但是为了方便阅读,通常关键字使用大写,表明、列名使用小写
在处理SQL语句时,所有的空格都被忽略,通常将SQL语句分成多行更容易阅读和调试
USE tysql; -- 使用数据库tysql
-- sql语句结束以后要以分号(;)结尾
二、检索数据
1.select 语句
功能:从一个或多个表中检索信息
2.检索单个列
-- 从表Products中筛选 prod_name
SELECT prod_name
FROM Products;
3.检索多个列
-- 在select后面给出多个列名,不同列名之间用逗号(,)分隔
SELECT prod_name,prod_id,prod_price
FROM Products;
4.检索所有列(*)
-- 使用*能检索出所有列,包括未知列
SELECT *
FROM Products;
5.检索不同的值(DISTINCT)
SELECT vend_id -- 检索vend_id
FROM Products;
SELECT DISTINCT vend_id -- 检索vend_id里不同的值
FROM Products;
-- DISTINCT关键字作用于所有列,不仅仅是跟在其后的那一列
SELECT DISTINCT vend_id,prod_price
FROM Products;
6.限制结果(LIMIT)
SELECT prod_name
FROM products
LIMIT 5; --检索前5行
SELECT prod_name
FROM products
LIMIT 5 OFFSET 5; --从第5行开始检索,检索5行
SELECT prod_name
FROM products
LIMIT 5,3; --也可以使用逗号隔开,与offset作用一样
--从第5行开始检索,检索3行
三、排序检索数据
1.排序数据(ORDER BY)
SELECT prod_name -- 显示prod_name
FROM products
ORDER BY prod_price; -- 按prod_price排序
在指定一条ORDER BY子句时,应该保证它是select语句中的最后一条子句。如果它不是最后的子句,将会出现错误消息。
2.按多个列排序
SELECT prod_name,prod_id,prod_price
FROM products
ORDER BY prod_price,prod_name;
-- 先按prod_price排序,再按prod_name排序,如果prod_price列中所有的值都是唯一的,则不会根据prod_name排序
3.按列位置排序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY 2,3;-- 按第2,3列排序
可以混合使用实际列名和相对列位置
4.指定排序方向(DESC,降序)
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
DESC关键字只应用到直接位于其前面的列名
如果想在多个列上进行降序排序,必须对每一列指定DESC关键字
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
--prod_price是降序,prod_name还是默认升序
四、使用where子句
在select语句中,数据根据where子句中指定的搜索条件进行过滤,where子句在表名之后给出
SELECT prod_name,prod_price
FROM products
WHERE prod_price>5;
在同时使用where和order by子句时,应该让order by位于where之后
where子句操作符:
- <> 不等于
- !< 不小于
- !> 不大于
- between 在指定的两个值之间
- is null 为null值
1.范围值检查(between)
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
两个值用AND关键字分隔
2.空值检查(IS NULL)
SELECT prod_name,prod_price
FROM products
WHERE prod_price IS NULL;
五、高级数据过滤
SQL允许给出多个where子句,可以以AND子句或OR子句的方式使用
1.AND和OR操作符
and操作符,过滤出满足所有给定条件的数据
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price < 4;
or操作符,过滤出满足任一条件的数据
mysqlSELECT prod_name,prod_price
FROM products
WHERE vend_id = 'DLL01' OR prod_price > 9;
AND的优先级高于OR
2.IN操作符
where子句中用来指定要匹配值的清单的关键字,功能与OR相当
优点:
- IN操作符的语法更清楚,更直观;
- 求值顺序更容易管理;
- 比OR操作符执行得更快;
- 最大的优点是可以包含其他select语句,能够更动态的建立where子句。
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN ('DLL01','BRS01');
3.NOT操作符
not操作符有且只有一个功能,就是否定其后所跟的任何条件。
SELECT prod_name,prod_price
FROM products
WHERE NOT vend_id ='DLL01';
六、用通配符进行过滤
通配符:用来匹配值的一部分的特殊字符
LIKE操作符:在搜索子句中使用通配符,必须使用LIKE操作符
1.百分号(%)
%表示任意字符出现任意次数
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE 'fish%';--检索任意以fish起头的词
- 通配符可以在搜索模式中的任意位置使用
- %不会匹配NULL
2.下划线(_)
用途与%一样,但是只匹配单个字符
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';--两个_通配符
3.方括号([])
只有Access和SQL Server支持
用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符
七、拼接字段
1.concat
SELECT CONCAT (cust_name ,'(' , cust_contact, ')')
FROM customers
ORDER BY cust_id;
2.使用别名AS
别名有时也称为导出列
SELECT CONCAT (cust_name ,'(' , cust_contact, ')')
AS cust_title -- as赋予列别名
FROM customers
ORDER BY cust_id;
八、执行算数计算
SELECT item_price,quantity,
item_price*quantity AS expanded_price
FROM orderitems
WHERE order_num = 20007
ORDER BY expanded_price;
可以根据需要使用select语句进行检验。
九、使用函数处理数据
常见的MySQL函数
函数 | 语法 |
---|---|
提取字符串的组成部分 | substring() |
数据类型转换 | convert() |
取当前日期 | curdate() |
1.常见的文本处理函数
函数 | 说明 |
---|---|
left() | 返回字符串左边的字符 |
length() | 返回字符串的长度 |
lower() | 将字符串转换为小写 |
ltrim() | 去掉字符串左边的空格 |
right() | 返回字符串右边的字符 |
rtrim() | 去掉字符串右边的空格 |
soundex() | 返回字符串的soundex值 |
upper() | 将字符串转换为大写 |
2.日期和时间处理函数
应用程序一般一般不使用日期和时间的存储格式,因此时间和日期函数总是用来读取、统计和处理这些值。
SELECT order_num
FROM orders
WHERE YEAR(order_date) = 2012 -- 使用year()函数从日期中提取年份
3.常用的数值处理函数
函数 | 说明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |
十、汇总数据
1.聚集函数
聚集函数:对某些行运行的函数,计算并返回一个值。
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
(1) AVG()函数
SELECT AVG(prod_price) AS avg_price
FROM products;
avg()函数只能用于单个列,要获得多个列的平均值,必须使用多个AVG()函数
AVG()函数忽略列值为NULL的行
(2)COUNT()函数
COUNT(*)对表中行的数目进行计数
COUNT(column)对特定列中具有值的行进行计数,忽略NULL值
SELECT COUNT(*) AS num_cust
FROM customers;
SELECT COUNT(cust_email) AS num_email
FROM customers;
(3)MAX()、MIN()函数
SELECT MAX(prod_price) AS min_price,
MIN(prod_price) AS max_price
FROM products;
MAX()、MIN()函数要求指定列名
(5)SUM()函数
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
2.聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
3.组合聚集函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products;
十一、分组数据
1.创建分组GROUP BY
SELECT order_num,COUNT(*) AS order_quan
FROM orderitems
GROUP BY order_num;
2.过滤分组HAVING
SELECT prod_id,COUNT(*) AS order_idnum
FROM orderitems
GROUP BY 1
HAVING COUNT(*) >= 2;
使用having是应该结合group by 子句
3.分组和排序
ORDER BY 与 GROUP BY
ORDER BY | GROUP BY |
---|---|
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
4.select子句顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
select | 要返回的列表或表达式 | 是 |
from | 从中检索数据的表 | 仅在从表选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
十二、使用子查询
子查询,即嵌套在其他查询中的查询。
1.利用子查询进行过滤
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01');
子查询总是从内向外处理
2.作为计算字段使用子查询
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;