函数
文本处理函数
select name,Upper(name) AS name_case
FROM xxx
order by xxx;
left():返回左边的字符
length():返回长度
Locate():找出一个字串
Lower():将串转化为小写
LTrim():去除串左边的空格
Soundex():返回串的SOUNDEX值
where Soundex(cust_contact) = Soundex('Y Lie');
Substring():返回子串的字符
时间和日期处理函数
日期表示格式：yyyy-mm-dd
1.查找特定的日期：
where Data(Order_data) = '2005-09-01'
2.查找特定月份
- where Year(order_data) = 2005 AND Month(order_data) = 9;
- where Data(order_data) Between '2005-09-01' and '2005-09-30
汇总数据
常用聚集函数
AVG() 返回某列平均值
COUNT() 返回某列行数
MAX()
MIN()
SUM()
DISTINCT() 不选取指定列中的重复值，只选取一次重复值
- AVG()
select AVG(xxx) as avg_price
from products
where id =1001
- COUNT()
1.COUNT(*)
对表中所有行进行计数，无论其是否含有空值
2.COUNT(column)
对特定列中具有值得行进行计数
- DISTINCT()
select AVG(DISTINCT grod_privce) AS avg_price
分组数据
数据分组
Group By
select id,count(*) As num
from products
Group By id
->按照每个独立的id进分组排序，并计算相应的num数值
->使用with rollup 可以得到每个分组以分组汇总级别的数值
- group by语句必须在where语句之后，在order by语句之前
- 一般在使用groupby语句时，也应该给出order by语句，保证排序的准确性
过滤分组
HAVING
select id, COUNT(*) as num
form product
where price>=10
Group by id
HAVING COUNT(*) >=2;
- 能用where表示的都可以使用HAVING表示
- where在分组前进行过滤，HAVING在分组后进行过滤
select子句顺序
select->from->where->group by->HAVING->order by->LIMIT
使用子查询(subquery)
利用子查询进行过滤
SELECT cust_id
FROM orders
where order_num in(select order_num
from orderintems
where prod_id = 'TNT2'
);
作为计算字段使用子查询
select cust_name,
cust_state,
(select COUNT(*)
from orders
where order.cust_id = custmer.cust_id) as orders
from custmers
order by cust_name;
创建高级联结
使用别表名
select name,contact
from customers as c, order as o
自联结
select p1.name, p1.id
from products as p1, products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'rrr'
自然联结
- 自然联结排除多次出现，使得每个列只返回一次
外部联结
- 联结在那些相关表中没有关联的行
left outer join 选择左边表格全部列
right outer join 选择右边表格的全部列
select customer.id, order.num
from custmers Left outer join orders
on customers.id = orders.id
使用带聚合函数的联结
select customer.id, customer.name,
count(order.num) as num_ord
from custmers inner join orders
on customers.id = orders.id
group by custmers.id
组合查询
- 多个查询语句，并作为单个查询结果作为返回
创建组合查询
Union
select xx
from xx
where xx
UNION
select xx
from xx
where xx
UNION使用规则
- union必须由两个或者两个以上的select语句组成
- union中的每个查询必须包含相同的列，表达式或者聚合函数
- 使用union会自动去除重复的行，可使用union all解决
- 使用order by进行排序的过程中，须放在组后一个select语句之后
全文本搜索
使用全文本搜索
1.启用全文本搜索支持
create tabel xxx{
FULLTEXT(note_text) //指定一个note_text列
}
2.进行全文本搜索
-where语句中进行排序
where Match(note_text) Against('rabbit');
match与fulltext需要匹配
相比较普通排序，会给出它认为的优先排序等级
-select语句中进行排序
select note_text,
Match(note_text) Against('rabbit') As ranks
3.扩展排序
where Match(note_text) Against('rabbit', with ouery expansion);
4.布尔文本搜索
- 即使没有fulltext索引也可以使用
- where Match(note_text) Against('rabbit'，in boolean mode);
5.常用的布尔操作符与注意事项
插入数据
数据插入
insert
插入完整的行
1.直接插入value值(不安全)
insert into cust
values(null,
xx
);
2.先列出key值再插入(安全)
insert into cust(cust_name,
cust_address,
xx,
xx
)
values(
'zxz',
'xx'
);
cust_id的NULL值是不需要的
- 第一列的id列一般可以自动补全
插入多个行
insert into cust(cust_name,
cust_address,
xx,
xx
)
values(
'zxz',
'xx'
)
values(
'zxz',
'xx'
)；
- 提升整体性能：
insert low priority into
插入检索出的数据
insert into cust(cust_name,
cust_address,
xx,
xx
)
select cust_id
xxx,
xxx,
from cust2;
- 列名不需要匹配
更新与删除数据
- 更新与删除数据的过程均不要忘记使用where语句
更新数据
删除指定列的某一行
update table
set email = ''
name = 'null' 若设置为null可视为删除
where id = ''
删除数据
- 删除指定的行
- 仅删除表的内容，而不会删除表
- 若想要删除整个表格，可以使用
truncate table
delete from cust
where id = ''
