一.检索数据

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

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

-- 检索单个列：SQL语句不区分大小写，多条SQL语句必须以分号;分隔select prod_namefrom products;-- 检索多个列：在选择多个列时，一定要在列名之间加上逗号select prod_id,prod_name,prod_pricefrom products;-- 检索所有列select *from products;-- 检索不同的行select distinct vend_idfrom products;-- 限制结果-- limit 5表示返回不多于5行select prod_namefrom productslimit 5;-- limit 5,5表示返回行5开始的5行，第一个数为开始位置，第二个数为要检索的行数select prod_namefrom productslimit 5,5;-- 使用完全限定的表名select products.prod_namefrom products;

二.排序检索数据

-- 排序数据select prod_namefrom productsorder by prod_name;-- 按多个列排序select prod_id,prod_price,prod_namefrom productsorder by prod_price,prod_name;-- 指定排序方向select prod_id,prod_price,prod_namefrom productsorder by prod_price desc;

三.过滤数据

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

四.数据过滤

-- 组合where子句-- 1.and操作符select prod_id,prod_price,prod_namefrom productswhere vend_id = 1003 and prod_price <=10;-- 2.or操作符select prod_name,prod_pricefrom productswhere vend_id = 1002 or vend_id = 1003;-- 3.计算次序select prod_name,prod_pricefrom productswhere (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;-- in操作符select prod_name,prod_pricefrom productswhere vend_id in (1002,1003)order by prod_name;-- not操作符select prod_name,prod_pricefrom productswhere vend_id not in (1002,1003)order by prod_name;

五.用通配符进行过滤

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

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

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

-- like操作符-- 1. % 表示任何字符出现任意次数select prod_id,prod_namefrom productswhere prod_name like 'jet%';-- 2. _ 的用途与 % 一样，但只匹配单个字符而不是多个字符select prod_id,prod_namefrom productswhere prod_name like '_ ton anvil';

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

/*like与regexp的区别like匹配整个列，如果被匹配的文本在列值中出现，立刻将不会找到它，相应的行也不会被返回(除非使用通配符)，而regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将会被返回。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使regexp和like一样*/-- 基本字符匹配select prod_namefrom productswhere prod_name regexp '1000'order by prod_name;-- 进行or匹配select prod_namefrom productswhere prod_name regexp '1000|2000'order by prod_name;-- 匹配几个字符之一select prod_namefrom productswhere prod_name regexp '[123] tom'order by prod_name;-- 匹配范围select prod_namefrom productswhere prod_name regexp '[1-5] tom'order by prod_name;-- 匹配特殊字符select vend_namefrom vendorswhere vend_name regexp '.'order by vend_name;-- 匹配多个实例select prod_namefrom productswhere prod_name regexp '\\([0-9] sticks?\\)'order by prod_name;-- 定位符select prod_namefrom productswhere prod_name regexp '^[0-9\\.]'order by prod_name;

七.创建计算字段

-- 拼接字段select concat(vend_name,'(',vend_country,')')from vendorsorder by vend_name;-- 执行算数计算select prod_id,quantity,item_price,quantity*item_price as expanded_pricefrom orderitemswhere order_num = 20005;

八.使用数据处理函数

-- 文本处理函数select vend_name,upper(vend_name) as vend_name_upcasefrom vendorsorder by vend_name;-- 日期和时间处理函数select cust_id,order_numfrom orderswhere year(order_date) = and month(order_date) = 9;

九.汇总数据

-- 聚集函数select avg(prod_price) as avg_pricefrom productswhere vend_id = 1003;select count(*) as num_custfrom customers;select max(prod_price) as max_pricefrom products;select sum(quantity) as items_orderedfrom orderitemswhere order_num = 20005;-- 聚集不同值select avg(distinct prod_price) as avg_pricefrom productswhere 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_avgfrom products;

十.分组数据

-- 创建分组select vend_id,count(*) as num_prodsfrom productsgroup 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 ordersfrom ordersgroup by cust_idhaving count(*) >= 2;-- 分组和排序select order_num,sum(quantity*item_price) as ordertotalfrom orderitemsgroup by order_numhaving sum(quantity*item_price) >= 50order by ordertotal;

十一.使用子查询

-- 利用子查询进行过滤select cust_name,cust_contactfrom customerswhere cust_id in(select cust_idfrom orderswhere order_num in (select order_numfrom orderitemswhere prod_id = 'TNT2'));-- 作为计算字段使用子查询select cust_name,cust_state,(select count(*)from orderswhere orders.cust_id = customers.cust_id) as ordersfrom customersorder by cust_name;

十二.联结表

-- 创建联结select vend_name,prod_name,prod_pricefrom vendors,productswhere vendors.vend_id = products.vend_idorder by vend_name,prod_name;-- 使用表别名select cust_name,cust_contactfrom customers as c,orders as o,orderitems as oiwhere c.cust_id = o.cust_idand oi.order_num = o.order_numand prod_id = 'TNT2';-- 自联结select p1.prod_id,p1.prod_namefrom products as p1,products as p2where p1.vend_id = p2.vend_idand p2.prod_id = 'DTNTR'；-- 外部联结select customers.cust_id,orders.order_numfrom customers left outer join orderson customers.cust_id = orders.cust_id;-- 使用带聚集函数的联结select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ordfrom customers inner join orderson customers.cust_id = orders.cust_idgroup by customers.cust_id;/* 使用联结和联结条件1. 注意所使用的联结类型。一般使用内部联结，但使用外部联结也是有效的2. 保证使用正确的联结条件，否则将会返回不正确的数据3. 应该总是提供联结条件，否则会得出笛卡尔积4. 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。*/

十三.组合查询

-- 创建组合查询select vend_id,prod_id,prod_pricefrom productswhere prod_price <= 5unionselect vend_id,prod_id,prod_pricefrom productswhere vend_id in (1001,1002);/* union规则1. 必须由两条以上的select语句组成，语句之间用关键字union分隔2. 每个查询必须包含相同的列，表达式或聚集函数(不过每个列不需要以相同的次序列)3. 列数据类型必须兼容：类型不必完全相同，但必须是dbms可以隐含转换的类型 *//* union 和 whereunion几乎总是完成与多个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 customersset cust_name = 'the fudds',cust_email = 'elmer@'where cust_id = 1005;-- 删除数据delete from customerswhere 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