博客

#3、了解数据库和表

show databases;#显示数据库
show tables;#显示当前数据库中的表
show columns from customers;#显示可用表的列表
describe customers;#显示可以表的列表另一种方式
show status;#显示服务器状态信息
show grants;#显示安全权限
show errors;
show warnings;

#4、检索数据

select prod_name from products;#检索单个列
select prod_name,prod_price,prod_id from products;#检索多列
select * from products;#检索所有列
select distinct vend_id from products;#去重
select prod_name from products limit 5;#限制展示结果
select prod_name from products limit 5,5;#显示第几行开始的几行
select products.prod_name from products;#使用完全限定表名

#5、排序检索数据

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;#以降序排列
select prod_id,prod_price,prod_name from products
order by prod_price desc,prod_name;#仅对特定列进行降序
select prod_id,prod_price,prod_name from products
order by prod_price desc,prod_name desc;#多列进行降序
select prod_price from products
order by prod_price desc limit 1;#order by与limit组合找出特定值，limit在order by之后

#6、过滤数据

select prod_name,prod_price from products
where prod_price = 2.5;#where设定过滤条件，在from之后，order by之前
select prod_name,prod_price from products
where prod_price < 10;#范围筛查
select vend_id,prod_name from products
where vend_id<>1003;#不匹配筛查
select prod_name,prod_price from products
where prod_price between 5 and 10;#between检索范围
select cust_id from customers
where customers.cust_email is null;#空值检查

#7、数据过滤 and or in not

select prod_id,prod_price,prod_name from products
where vend_id = 1003 and prod_price <= 10;#and操作符,可以添加多个and
select prod_name,prod_price from products
where vend_id = 1002 or vend_id = 1003;#or检索匹配任意给定条件的行
select prod_name,prod_price from products
where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;#and计算次序中优先
select prod_name,prod_price from products
where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;#正确写法
select prod_name,prod_price from products
where vend_id in (1002,1003)
order by prod_name;#in用来指定要匹配值的清单的关键字，功能与or相同
select prod_name,prod_price from products
where vend_id not in (1002,1003)
order by prod_name;#not取反

#8、通配符过滤 % _

select prod_id,prod_name from products
where prod_name like 'jet%';#%通配符,匹配剩余内容
select prod_id,prod_name from products
where prod_name like '%anvil%';
select prod_id,prod_name from products
where prod_name like '_ ton%'#_通配符，匹配一个字符

#9、正则表达式

select prod_name from products
where prod_name regexp '1000'
order by prod_name;#基本字符匹配
select prod_name from products
where prod_name regexp '.000'
order by prod_name;#.表示匹配任意一个字符
select prod_name from products
where prod_name regexp '1000|2000'
order by prod_name;#|表示or，匹配上就返回
select prod_name from products
where prod_name regexp '[123] Ton'
order by prod_name;#[]or缩写
select prod_name from products
where prod_name regexp '[1-5] Ton'
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?\\)'
#\\(匹配(,?0个或1个匹配
select prod_name from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;
#[:digit:]匹配任意数字，{4}出现四次
select prod_name from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;#定位符^表示匹配串的开始

#10、创建计算字段

select concat(vend_name,'(',vend_country,')')from vendors
order by vend_name;#concat拼接字段
select concat(rtrim(vend_name),'(',rtrim(vend_country),')')from vendors
order by vend_name;#rtrim去除串右边空格
select concat(rtrim(vend_name),'(',rtrim(vend_country),')')
as vend_title from vendors
order by vend_name;#as设定别名
select prod_id,quantity,item_price,quantity*item_price as expanded_price
from orderitems where order_num = 20005;#执行算数计算

#11、使用数据处理函数

select vend_name,upper(vend_name) as vend_name_upcase
from vendors order by vend_name;#upper() 文本转换为大写
select cust_name,cust_contact from customers
where soundex(cust_contact) = soundex('Y lie');#soundex()发音比较
select cust_id,order_num from orders
where date(order_date) = '2005-09-01';#date()日期函数
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;#另一种查询日期范围方法

#12、汇总数据

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 min(prod_price) as min_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;#通过distinct只计算不同价格的平均值
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;#组合聚集函数

#13、分组数据

select vend_id,count(*) as num_prods
from products group by vend_id;#group by创建分组
select cust_id,count(*) as orders from orders
group by cust_id having count(*)>=2;#having 过滤分组
select vend_id,count(*) as num_prods from products
where prod_price >= 10
group by vend_id having count(*) >= 2;#where在数据分组前过滤，having在数据分组后进行过滤
select order_num,sum(quantity*orderitems.item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50
order by ordertotal;#分组与排序

必须遵守顺序
(select) (distinct) (from) (where) (group by) (having) (order by) (limit)