MySQL基础2

1、SELECT：作为SQL组成部分的保留字，它的用途是从一个或者多个表中检索信息，关键字SELECT不能用作表或者列的名字。使用SELECT语句，必须至少给出两条信息----想选择什么，以及从什么地方选择。

下面实际操作SELECT语句的使用。

（1）SELECT检索单个列

创建mydata数据库，在数据库中创建如下5张表，表的创建在此处获取http://www.forta.com/books/0672336073/

使用 SELECT prod_name FROM Products; 检索单个列，结果如下

（2）SELECT检索多个列

要想从一个表中检索多个列，仍然使用相同的SELECT语句。唯一的不同是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。下面的SELECT语句从Products表中选择3列

SELECT prod_id,prod_name,prod_price FROM Products;

(3)检索所有列：给定一个通配符 × ，则返回表中所有列

SELECT * FROM Products;

 

2、去重语句，SELECT语句返回所有匹配的行，但是如果有重复的语句出现，该如何处理？如上图所示，如果想要筛选出vend_id列中不同的列值，这就需要使用DISTINCT语句。

SELECT DISTINCT vend_id FROM Products;

SELE只返回不同（具有唯一性）的vend_id行。如果使用DISTINCT关键字，它必须放在列名的前面。SELECT关键字作用于所有的列，不仅仅是跟在其后的那一列。

3、返回前N行语句

SELECT语句返回指定表中所有匹配的行，如果只想返回第一行或者一定数量的行，可以使用LIMIT关键字来限制最多返回多少行。注意：不同的DBMS关键字不同。

SELECT prod_name FROM Products LIMIT 5;

上述使用SELECT语句检索单独的一列数据，LIMIT 5表示返回不超过5行的数据。

为了得到后面的5行数据，需要指定从哪开始以及检索的行数，如下图

SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

LIMIT 5 OFFSET 5 返回从第五行起的5行数据，第一个数字是限制检索的行数，第二个数字是限制从哪开始，products表中只有9行数据，所以只返回了4行数据。

4、注释

很多DBMS都支持各种形式的注释语法。

行内注释：注释使用 -- （两个连字符）嵌在行内，-- 之后的文本就是注释。

在一行的开始处使用# ，这一整行都作为注释。也可以使用/**/进行多行注释。

5、数据排序

SQL语句返回某个数据库表的单个列，其输出并没有特定的顺序。为了明确地对SELECT语句检索出来的数据进行排序，可以使用ORDER BY字句取一个或者多个列的名字，对输出进行排序。

SELECT prod_name FROM Products ORDER BY prod_name;

注意：ORDER BY字句的位置，在指定一条ORDER BY字句时，应该保证它是SELECT语句最后一条字句，否则会出现错误。

5.2按多个列排序：要按照多个列排序，简单指定列名，列名之间用逗号隔开。

SELECT prod_id,prod_name,prod_price FROM Products ORDER BY prod_price,prod_name;

 

过滤数据

6、使用WHERE字句

在SELECT语句中，根据WHERE字句中指定的搜索条件进行过滤，WHERE字句在表名（FROM字句）之后给出。

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;

 

7、范围值检查

要检查某个范围的值，可以使用BETWEEN操作符，该操作符需要两个值，即范围的开始值和结束值。如下图

SELECT prod_name,prod_price FROM Products where prod_price BETWEEN 5 AND 10;

从这个例子可以看出，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值，这两个值必须使用AND关键字分隔，BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

8、组合WHERE字句

为了进行更强的过滤控制，SQL允许给出多个WHERE字句，这些字句有两种使用方式，即以AND字句或OR字句的方式使用。

如下语句选择的是vend_id 与DLL01相等 并且 prod_price <= 4 的数据。

SELECT prod_id,prod_name,prod_price FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR操作符与AND操作符正好相指反，它指示DBMS检索匹配任一满足条件的行。

9、求值顺序

WHERE字句可以包含任意数目的AND 和 OR 操作符，允许两者结合以进行复杂、高级的过滤，允许使用括号对操作符进行明确分组。但是需要注意，AND在求值过程中优先级更高。

例：现在需要列出价格为10美元及以上，且由DLL01或者BRS01制造的所有产品。

SELECT prod_name,prod_price FROM Products where prod_price >= 10 AND (vend_id = 'DLL01' OR vend_id = 'BRS01');

10、IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，IN取一组由逗号分割、括在圆括号中的合法值。

SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

 

通配符

---

11、通配符（wildcard）用来匹配值的一部分的特殊字符。

11.1百分号（%）通配符

在搜索串中，%表示任何字符出现任意次数，例如为了找出所有以词Fish起头的产品，可以使用以下语句。

SELECT prod_id ,prod_name FROM Products WHERE prod_name LIKE 'Fish%';

 

11.2 下划线（ _ ）通配符

下划线的用途与%一样，但是它只匹配单个字符，而不是单个字符。

11.3 方括号（[ ]）通配符：用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

使用函数处理数据

12、文本处理函数

UPPER（）将文本转换为大写。使用示例如下：

SELECT vend_name ,UPPER(vend_name) AS vend_name_space FROM Vendors ORDER BY vend_name;

13、日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式，日期和时间以特殊的格式存储，以便于能够快速和有效的排序或者过滤，并且节省物理存储空间。举例说明日期处理函数的用法。

SELECT order_num FROM Orders WHERE YEAR(order_date)=2012;

汇总数据

14、聚集函数（aggregate function）:对某些行运行的函数，计算并返回一个值

我们经常需要汇总数据，但是却并不需要吧他们实际检索出来，为此SQL专门设计了此类函数。

14.1 AVG() 函数：返回某列的平均值

SELECT AVG(prod_price) AS avg_price FROM Products;

14.2COUNT()函数：返回指定列的行数

SELECT COUNT(*) AS num_cust FROM Customers;

将圆括号中的星号换成指定列的名称，即可求得那列的行数

14.3 MAX()函数：返回指定列中的最大值。

SELECT MAX(prod_price) AS max_price FROM Products;

14.3 MIN()函数：与MAX()函数相反，返回指定列的最小值。

SELECT MIN(prod_price) AS min_price FROM Products;

14.4 SUN()函数：返回指定列的和

SELECT SUM(quantity) AS items_orderd FROM OrderItems WHERE order_num = 20005;

15、聚集不同值

以上5个函数，对所有执行的计算，指定ALL参数或不指定参数（ALL是默认）。对于只需要不同的值用作计算处理，指定DISTINCT参数即可。

分组数据

16、GROUPBY 字句

分组是使用SELECT语句的GROUPBY字句建立的。看下面的例子。

SELECT vend_id ,COUNT(×) AS num_prods FROM Products GROUP BY vend_id;

SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段。GROUP BY()字句指示DBMS按照vend_id排序并且分组数据。

 

附在实际面试中遇到的一道面试题：

MySQL的优化方式有哪些？

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

1.索引的优化
    只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
    尽量使用短索引，如果可以，应该制定一个前缀长度
    对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
    对于有多个列where或者order by子句的，应该建立复合索引
    对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
    尽量不要在列上进行运算（函数操作和表达式操作）
    尽量不要使用not in和<>操作
 

2.sql语句的优化
    查询时，能不要*就不用*，尽量写全字段名
    大部分情况连接效率远大于子查询
    多使用explain和profile分析查询语句
    查看慢查询日志，找出执行时间长的sql语句优化
    多表连接时，尽量小表驱动大表，即小表 join 大表
    在千万级分页时使用limit
    对于经常使用的查询，可以开启缓存
 

3.表的优化

• 表的字段尽可能用NOT NULL
• 字段长度固定的表查询会更快
• 把数据库的大表按时间或一些标志分成小表
• 将表分区