【MySQL】数据库基础知识

一、数据库基础

数据库（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;