1. order by 排序检索

1.按单个列排序

select prod_name from products order by prod_name;

2.按多个列排序，下例仅在多行具有相同的prod_price时才对产品按prod_name进行排序

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

3.指定方向排序，默认升序，降序 desc。若在多个列上进行desc排序，必须对每个列指定desc关键字

select prod_id,prod_pricemprod_name from products order by prod_price desc;

4.order by + limit；order by位于from之后，limit位于oder by之后

select prod_price from products order ny prod_price desc limit 1;

2. where 过滤数据

1.where指定搜索条件，位于from之后,order by 位于where之后

select prod_name,prod_price from products where prod_price = 2.50;

2.where字句操作符<,>,!=,is null,not null,between and
3.组合where字句：or，and；sql处理and的优先级高于or，若先处理or则需要添加圆括号

select prod_name,prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

4.in 指定条件范围
5.not 否定后跟条件关键字

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

3. like 通配符

1.like 操作符与通配符使用，% 表示任何字符出现任意次数，%通配符也不能匹配值null的数据

-- 找出所有以jet起头的产品
select prod_id,prod_name from products where prod_name like 'jet%';
-- 找出任意位置包含jet的产品
select prod_id,prod_name from products where prod_name like '%jet%';

2._下划线通配符，作用同%,但是只匹配单个字符而非多个

4. 正则表达式

1.使用正则表达式

-- 匹配1或2或3 Ton
select prod_name from products where prod_name regexp '1|2|3 Ton' order by prod_name;

2.匹配特殊字符必须用\为前导,匹配\需要使用\\

select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

3.匹配多个实例 *0个或多个匹配、 +1个或多个匹配、 ?0个或1个匹配、{n}指定数目的匹配、{n,}不少于指定数目的匹配、{n,m}匹配数目范围n-m

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

4.定位符 ^文本的开始、$文本的结尾、[[:<:]]词的开始、[[:>:]]词的结尾

-- 0-9数字开头或者.开头
select prod_name from products where prod_name regexp '^[0-9\\.]';

5. 使用创建字段

1.concat()函数拼接字段，需要一个或多个串，通过逗号分隔

select concat(vend_name,'(',vend_country,')') from vendors;

2.trim()函数去掉左右两边空格、rtrim()去掉串右边空格、ltrim()去掉串左边空格

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors;

3.as使用别名

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') as vend_title from vendors;

6. 数据处理函数

1.文本处理函数upper()转换大写、lower()转换小写、locate()找出串的子串、subString()返回子串的字符串
2.日期和时间处理函数、具体使用查找

--比较日期,仅将给出的部分与列中的日期比较
select cust_id,order_num from orders where date(order_date) = '2005-09-01';
--检索2005年9月的数据
select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id,order_num from orders where year(order_date) = 2005 and month(order_date) = 9;

3.数值处理函数

7. 聚集函数

1.avg() 求某列的平均值
2.count() 统计行数，统计总行或某列有值的行数
3.max() 最大值,忽略值为null的行
4.min() 最小值，忽略值为null的行
5.sum() 返回指定列的和

select avg(distinct prod_price) from products where vend_id = 1003;
select count(*) from products;
select max(prod_price) from products;
select min(prod_price) from products;
select sum(prod_price) from products where vend_id = 1003;

8. 分组函数

1.group by 分组

• group by 子句中只能是列、不能是聚集函数
• 除聚集计算语句外，select 语句中的每个列都必须在group by子句中给出
• 若分组中具有null值，则null将作为一个分组返回，多个null值分为一组
• group by位于where之后、order by 之前

select vend_id, count(*) as num_prods from products group by vend_id;

2.having 过滤分组，where过滤行

• where在数据分组前进行过滤；having在数据分组后进行过滤
• having通常对分组的select后的聚集函数做过滤
• select顺序：select、from、where、group by、having、order by、limit

select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;

9. 子查询

1.将一条select语句的返回结果用于另一条select语句的where语句

• 性能问题导致通常采用联结表

select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

10. 联结表

1.=等值联结实际上也就是内连接inner join on

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id;
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

2.表别名、自联结(单条select语句不能同一次引用相同的表)

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';

3.外联结，left outer join on，right outer join on

• 外联结包含了那些在相关表中没有关联的行
• 左外联结，左表选择所有行，右外联结，右表选择所有行

select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;
select customers.cust_id,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id;

4.带聚集函数的联结

select customers.cust_name,customer.cust_id,count(orders.order_num) as num_ord from customers left outer join orders on customers.cust_id = orders.cust_id group by customer.cust_id;

11.组合查询

1.union 将多个查询的结果合并为单个返回

• 必须由2或2条以上的select语句组成，语句之间使用union关联
• union中的每个查询必须包含相同的列或函数表达式
• union并集时会自动去除重复行

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);
-- 等价于
select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in (1001,1002);

2.union all 返回所有匹配行，包含重复行
3.对组合查询结果排序，order by必须出现在最后一条select语句之后，对结果集进行排序

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)
order by vend_id,prod_price;

12. 全文本搜索

• myisam支持全文本搜索，innodb不支持
• create table时，通过指定fulltext(column)去进行全文本搜索
• 不要在导入数据时使用fulltext，有助于更快的导入数据

1.match() 指定被搜索的列,against()指定要使用的搜索表达式

• 传递给match()的值必须与fulltext()定义中相同，若指定多个列，则次序也需完全一致
• 全文搜索会对结果排序，较高等级的行先返回

--此处指定fulltext(note_text)
select note_text from productnotes where match(note_text) against('rabbit');

2.查询扩展

select note_text from productnotes where match(note_text) against('rabbit' with query expansion);

13. 插入数据

1.插入完整行

insert into customers values ('jet','100 street','90046','USA',null);
insert into customers (cust_name,cust_address,cust_zip,cust_country,cust_email) values ('jet','100 street','90046','USA',null);

2.插入多行

insert into customers (cust_name,cust_address,cust_zip,cust_country,cust_email) values 
('jet','100 street','90046','USA',null),
('sss','100 street','90047','USA',null)

3.插入检索出的数据，mysql不关心select返回的列名，仅做对应列填充

insert into customers(name,age) select(name,age) from CustomersOld;

14. 更新和删除数据

1.更新

update customers set cust_name = 'bis',cust_email='ww@fudd.com' where cust_id = 10005;
update customers set cust_email = null where cust_id = 10005;

2.删除

delete from customers where cust_id = 10086;

15. 创建和操纵表

1.create table 建表

• PRIMARY KEY 主键，可以指定多个列为主键，用逗号分隔
• auto_increment 每个表只允许一个，且必须被索引，列数据自动增量生成
• engine = innodb 指定数据处理内部引擎

CREATE TABLE `Student`(
`s_id` VARCHAR(20) not null auto_increment,
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
) engine = innodb;

2.alter table 更新表

--添加列
alter table vendors add vend_phone char(20);
--删除列
alter table vendors drop column vend_phone;
--删除表
drop table customers2;
--重命名表
rename table customers2 to customers;