SQL必知必会+MySQL实践学习记录（上）

１．创建数据库

• 登录MySQL

$ mysql -u root -p

• 生成数据库

CREATE database crashcourse;
USE crashcourse;

• 导入.sql源文件

SOURCE ~/create.sql;
SOURCE ~/populate.sql;

2．查看数据库状态

• 数据库列表信息

SHOW DATALABLES;

• 显示指定的数据库中的所有表

SHOW TABLES;

• 显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

SHOW COLUMNS FROM　Products;

• 显示数据表的详细索引信息，包括PRIMARY KEY（主键）

SHOW INDEX FROM Products;

• 输出MySQL数据库管理系统的性能及统计信息

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:

3．检索数据

• 使用SELECT语句检索

SELECT prod_id, prod_name，prod_price　
FROM Products;

• 检索所有列

SELECT *　FROM Products;

• 检索不同的值

SELECT DISTINCT vend_id FROM Products;

• 限制结果

SELECT vend_id FROM Products LIMIT 5 OFFSET 5;　－－LIMIT 5, 5

• 注释

SELECT vend_id FROM Products LIMIT 5 OFFSET 5;　--LIMIT 5,5
SELECT vend_id FROM Products LIMIT 5 OFFSET 5;　/* LIMIT 5,5 */
SELECT vend_id FROM Products LIMIT 5 OFFSET 5;　#LIMIT 5,5

4．排序检查数据

• 排序数据（ORDER　BY 子句放在最后）

SELECT prod_name FROM Products ORDER BY prod_name;

• 按多列排序

select prod_id, prod_price, prod_name from Products order by prod_price, prod_name;

• 按列位置进行排序

 select prod_id, prod_price, prod_name from Products order by 2,3;

• 指定排序方向（默认升序ＡSC，降序需用关键字DESC）

 select prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name;

5．过滤数据

• 使用WHERE子句（关键词WHERE需位于ORDER BY之前）

select prod_price, prod_name from Products where prod_price=3.49 order by prod_name;

• WHERE子句操作符

select prod_price, prod_name from Products where prod_price<10;

select vend_id, prod_name from Products where vend_id <> "DLL01";

select prod_price, prod_name from Products where prod_price between 5 and 10;

select cust_name from Customers where cust_email is null;

６．高级数据过滤

• AND操作符

select prod_id, prod_price, prod_name from Products where vend_id="DLL01" and prod_price <= 4;

• OR操作符

select prod_price, prod_name from Products where vend_id = "DLL01" or vend_id = "BRS01";

• 求值顺序

select prod_name, prod_price from Products where (vend_id = "DLL01" or vend_id = "BRS01") and prod_price >=10;

• IN操作符

select prod_name, prod_price from Products where vend_id in ("DLL01", "BRS01") order by prod_name;

• NOT操作符

select prod_name from Products where not vend_id = "DLL01";

７．用通配符进行过滤

• 通配符只适用于文本字段（字符串），费文本数据类型字段不能使用通配符进行搜索
• 百分号%通配符（搜索区分大小写）

select prod_id, prod_name from Products where prod_name like "Fish%";

• ％常用于搜索邮件相关命令

where email like "y%@qq.com"

• 下划线_通配符（只匹配单个字符，注意NULL值）

select prod_id, prod_name from Products where prod_name like "__ inch teddy bear";

select prod_id, prod_name from Products where prod_name like "%inch teddy bear";

• 方括号[]通配符（指定字符集，只匹配某一个）
  注意[]在MySQL中不用做通配符
  [^]用于否定子句中

８．创建计算字段

• 拼接字段（有些DBMS是用+或者｜｜实现拼接）

show columns from Vendors;
select concat(vend_name, " (", vend_country, ")") from Vendors order by vend_name;

• 使用RTRIM()、LTRIM()、TRIM()去除空格，得到格式化数据
• 使用别名（关键词AS）

select concat(vend_name, " (", vend_country, ")") as vend_title from Vendors order by vend_name;插入代码片

• 执行算术计算

select prod_id, quantity, item_price from OrderItems where order_num = 20008;

select prod_id, quantity, item_price, quantity*item_price as expended_price from OrderItems where order_num = 20008;

• 测试计算

select now();
select Trim(" abc ");

9．使用函数处理数据

• 函数差异（只适用于MySQL）

substring(); #提取字符串的子部分
convert();   #数据类型转换
curdate();   #获取当前日期

• 文本处理函数

select vend_name, upper(vend_name) as vend_name_upcase from Vendors order by vend_name;

left()   　　　　　　　　　　　　　#返回字符串左边的字符
length()/len()/datalength()   #返回字符串的长度
lower()   #将字符串转换为小写
ltrim()/rtrim()/trim()   　　　#去掉字符串中的空格
right()   　　　　　　　　　　　　#返回字符串右边的字符
soundex()   　　　　　　　　　　　#返回字符串的soundex值
upper()    　　　　　　　　　　　　#将字符串转换为大写

select cust_name, cust_contact from Customers where soundex(cust_contact) = soundex("Michael Green");

• 日期和时间处理函数（ year() / month() / day() ）

select order_num, order_date from Orders where day(order_date)=03;

• 数值处理函数

abs()  
cos() 
exp()   #返回指数值
pi()    #返回圆周率
sin()
sqrt()  #返回平方根
tan()  

10．汇总数据

• 聚集函数
• 求平均avg()

select avg(prod_price) as avg_price from Products;

select avg(prod_price) as avg_price from Products where vend_id = "DLL01";

• 计数count()

select count(*) as num_cust from Customers;  #对所有行计数，包含NULL

select count(cust_email) as num_cust from Customers;　#对cust_email列计数，不包含NULL

• 最大值max()
  用于文本数据时，max()返回按该列排序后的最后一行
  max()忽略列值为NULL的行

select max(prod_price) as max_price from Products;

• 最小值min()

select min(prod_price) as min_price from Products;

• 总计sum()

select sum(item_price * quantity) as total_price from OrderItems where order_num = 20005;

• 聚集不同值distinct
  distinct用于count()时，必须使用列名，不能用于计算或表达式

select avg(distinct prod_price) as avg_price from Products where vend_id = "DLL01";

select count(distinct prod_price) as count_price from Products where vend_id = "DLL01";

• 使用聚集函数

select count(*) as num_items,
    　　min(prod_price) as min_price,
    　　max(prod_price) as max_price,
    　　avg(prod_price) as avg_price
from Products;