MySQL必知必会 | 学习笔记 (全)

一.检索数据

每个SQL语句都是由一个或多个关键字构成的

最经常使用的SQL语句就是SELECT语句，它的用途是从一个或多个表中检索信息

-- 检索单个列：SQL语句不区分大小写，多条SQL语句必须以分号;分隔
select prod_name
from products;

-- 检索多个列：在选择多个列时，一定要在列名之间加上逗号
select prod_id,prod_name,prod_price
from products;

-- 检索所有列
select *
from products;

-- 检索不同的行
select distinct vend_id
from products;

-- 限制结果
-- limit 5表示返回不多于5行
select prod_name
from products
limit 5;
-- limit 5,5表示返回行5开始的5行，第一个数为开始位置，第二个数为要检索的行数
select prod_name
from products
limit 5,5;

-- 使用完全限定的表名
select products.prod_name
from products;

二.排序检索数据

-- 排序数据
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 prod_price desc;

三.过滤数据

-- 使用where子句
select prod_name,prod_price
from products
where prod_price = 2.5;

-- where子句操作符
-- 1.检查单个值
select prod_name,prod_price
from products
where prod_name = 'fuses';
-- 2.不匹配检查 可以使用<>，也可以使用！=
select vend_id,prod_name
from products
where vend_id <> 1003;
-- 3.范围值检查
select prod_name,prod_price
from products
where prod_price between 5 and 10;
-- 4.空值检查
select prod_name
from products
where prod_price is null;

四.数据过滤

-- 组合where子句
-- 1.and操作符
select prod_id,prod_price,prod_name
from products
where vend_id = 1003 and prod_price <=10;
-- 2.or操作符
select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003;
-- 3.计算次序
select prod_name,prod_price
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

-- in操作符
select prod_name,prod_price
from products
where vend_id in (1002,1003)
order by prod_name;

-- not操作符
select prod_name,prod_price
from products
where vend_id not in (1002,1003)
order by prod_name;

五.用通配符进行过滤

①不要过度使用通配符，如果其它操作符能达到相同的目的，应该使用其它操作符

②在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处，最慢

③注意通配符的位置，放错地方，可能不会返回想要的数据

-- like操作符
-- 1. % 表示任何字符出现任意次数
select prod_id,prod_name
from products
where prod_name like 'jet%';

-- 2. _ 的用途与 % 一样，但只匹配单个字符而不是多个字符
select prod_id,prod_name
from products
where prod_name like '_ ton anvil';

六.用正则表达式进行搜索

/*like与regexp的区别
like匹配整个列，如果被匹配的文本在列值中出现，立刻将不会找到它，相应的行也不会被返回(除非使用通配符)，而regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将会被返回。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使regexp和like一样*/

-- 基本字符匹配
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;

-- 进行or匹配
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;

-- 匹配几个字符之一
select prod_name
from products
where prod_name regexp '[123] tom'
order by prod_name;

-- 匹配范围
select prod_name
from products
where prod_name regexp '[1-5] tom'
order by prod_name;

-- 匹配特殊字符
select vend_name
from vendors
where vend_name regexp '.'
order by vend_name;

-- 匹配多个实例
select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;

-- 定位符
select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;

七.创建计算字段

-- 拼接字段
select concat(vend_name,'(',vend_country,')')
from vendors
order by vend_name;

-- 执行算数计算
select prod_id,
       quantity,
       item_price,
       quantity*item_price as expanded_price
from orderitems
where order_num = 20005;

八.使用数据处理函数

-- 文本处理函数
select vend_name,upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

-- 日期和时间处理函数
select cust_id,order_num
from orders
where year(order_date) = 2005 and month(order_date) = 9;

九.汇总数据

-- 聚集函数
select avg(prod_price) as avg_price
from products
where vend_id = 1003;

select count(*) as num_cust
from customers;

select max(prod_price) as max_price
from products;

select sum(quantity) as items_ordered
from orderitems
where order_num = 20005;

-- 聚集不同值
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;

-- 组合聚集函数
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;

十.分组数据

-- 创建分组
select vend_id,count(*) as num_prods
from products
group by vend_id;

/* 在具体使用group by子句前，需要知道一些重要的规定
1. group by子句可以包含任意数目的列
2. 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总
3. group by子句中列出的每个列都必须是建所列或有效的表达式(但不能是聚集函数)，如果在select中使用表达式，则必须在group by子句中指定相同的表达式，不能用别名
4. 除聚集计算语句外，select语句中的每个列都必须在group by子句中给出
5. 如果分组列中具有null值，则null将作为一个分组返回，如果列中有多行null值，它们将分为一组
6. group by子句必须出现在where子句之后，order by子句之前 */

-- 过滤分组
-- having支持所有where操作符，所学过的有关where的所有技术和选项都适用于having，句法相同
-- where在数据分组前进行过滤，having在数据分组后进行过滤，where排除的行不包括在分组中
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

-- 分组和排序
select order_num,sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50
order by ordertotal;

十一.使用子查询

-- 利用子查询进行过滤
select cust_name,cust_contact
from customers
where cust_id in(select cust_id
                 from orders
                 where order_num in (select order_num
                                     from orderitems
                                     where prod_id = 'TNT2'));

-- 作为计算字段使用子查询
select cust_name,
       cust_state,
       (select count(*)
        from orders
        where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

十二.联结表

-- 创建联结
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;

-- 使用表别名
select cust_name,cust_contact
from customers as c,orders as o,orderitems as oi
where c.cust_id = o.cust_id
  and oi.order_num = o.order_num
  and prod_id = 'TNT2';

-- 自联结
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id
  and p2.prod_id = 'DTNTR'；

-- 外部联结
select customers.cust_id,orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;

-- 使用带聚集函数的联结
select customers.cust_name,
       customers.cust_id,
       count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

/* 使用联结和联结条件
1. 注意所使用的联结类型。一般使用内部联结，但使用外部联结也是有效的
2. 保证使用正确的联结条件，否则将会返回不正确的数据
3. 应该总是提供联结条件，否则会得出笛卡尔积
4. 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。*/

十三.组合查询

-- 创建组合查询
select vend_id,prod_id,prod_price
from products
where prod_price <= 5
union
select vend_id,prod_id,prod_price
from products
where vend_id in (1001,1002);

/* union规则
1. 必须由两条以上的select语句组成，语句之间用关键字union分隔
2. 每个查询必须包含相同的列，表达式或聚集函数(不过每个列不需要以相同的次序列)
3. 列数据类型必须兼容：类型不必完全相同，但必须是dbms可以隐含转换的类型 */

/* union 和 where
union几乎总是完成与多个where条件相同的工作
union all为union的一种形式，它完成where子句完成不了的工作
如果确实需要每个条件的匹配行全部出现(包括重复行)，则必须使用union all而不是where */

十四.插入，更新与删除

-- 插入数据
insert into customers values(null,'pep','los','usa','9006',null,null);
insert into customers(cust_name,cust_contact,cust_email)values('pep',null,null);
insert into customers(cust_name,cust_contact)values('pep','100 main'),('martin','42 way');

-- 更新数据
update customers
set cust_name = 'the fudds',
    cust_email = 'elmer@fudd.com'
where cust_id = 1005;

-- 删除数据
delete from customers
where cust_id = 1006;

/* 更新和删除的指导原则
1. 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句
2. 保证每个表都有主键，尽可能像where子句那样使用它
3. 在对update或delete语句使用where子句前，应该先用select进行测试，保证它过滤的是正确的记录，以防编写的where子句不正确
4. 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行 */

十五.使用视图

1.为什么使用视图？

重用SQL语句

简化复杂的SQL操作：在编写查询后，可以方便地重用它而不必知道它的基本查询细节

使用表的组成部分而不是整个表

保护数据：给用户授予表的特定部分的访问权限

更改数据格式和表示

2.规则和限制

必须唯一命名：不能给视图取与别的视图或表相同的名字

对于可以创建的视图数目没有限制

为了创建视图，必须具有足够的访问权限

视图可以嵌套：可以利用从其他视图中检索数据的查询来构造一个视图

视图不能索引，也不能有关联的触发器或默认值

视图可以和表一起使用

order by可以用在视图中：如果从该视图检索数据select中也含有order by，该视图中的order by将被覆盖

3.创建

视图用create view语句来创建

使用show create view viewname；来查看创建视图的语句

用drop删除视图，语法为：drop view viewname；

更新视图时，可以先用drop再用create，也可以直接用create or replace view