mysql笔记
MySQL是一种DBMS数据库管理系统
其中DBMS分两种：
1.共享文件系统；（通常用于桌面）-Microsoft access
2.客户机-服务器（高级应用）-MySQL
show columns from customers;等同于describe customers;
image.png
返回字段名；数据类型；是否有NULL；键信息；默认值和其他信息
show语句的更多用法，可以参考helpshow来查看。
tips：Mac 终端清屏：command +k
对SQL关键字用大写，列和表名用小写，将更便于阅读和调试；分成多行也便于阅读和调试。
1.检索数据
`检索单列或多列`
SELECT column_name1,column_name2....
FROM table_name;
`单列去重`
SELECT DISTINCT column_name
FROM table_name;
LIMIT 5,5;
`限制结果,从行5开始取5行；存在行0`
LIMIT 3,4 ==LIMIT 4 OFFSET 3
`均表示从行3开始取4行`
`完全限定`
SELECT table.column_name
FROM table;
2.排序检索
`排序,可以用检索列或者非检索列进行排序`
SELECT column_name1,column_name2....
FROM table_name
ORDER BY column_name;
`多列排序,将col1结果先按col2排序，再按col3排序,默认是升序排列`
SELECT column_name1,column_name2,column_name3
FROM table_name
ORDER BY column_name2,column_name3;
`降序,降序需要制定每一列`
SELECT column_name1,column_name2,column_name3
FROM table_name
ORDER BY column_name2 DESC,column_name3;
`order by 要在from之后`
子句顺序怎么办？where，limit，order by？？
order by 在where之后
3.过滤数据
BETWEEN AND
IS NULL
<>和!=都是不等于
4.数据过滤
and,or，其中and优先级更高，有多个命令时需要加()
SELECT productcode,buyprice,productname
FROM products
WHERE (productcode ='S50_1341' OR productcode ='s32_4289') AND buyprice >30;
SELECT prod_name,prod_price
FROM products
WHERE vend_id in (1002,1003)
ORDER BY prod_name;
此外还可以用in来匹配,检索vend_id在1002和1003中的name和price，并按name排列。可以发现in和or功能相似。
5.通配符进行过滤
like,%
%，匹配之后任意字符。可以区分大小写。
s%e,%anvil%，通配符可以出现在两端或者中间。但无法匹配NULL
_只能匹配单个字符；%可以匹配多个或者0，1个。
缺点：搜索时间更长。
like\regexp区别，前者匹配整列，要完全匹配或者用%才能匹配；后者是匹配列值，只要值对上就能返回结果。
6.正则表达式
1.regexp+'字符串'，来匹配值
2.or匹配,regexp+'字符串1|字符串2....'
3.匹配单一字符,[]+'字符串'
4..匹配任意一个字符,-用于表示范围。
5.\\.来匹配特殊字符（转义）
6.定位符^,$,文本开始，结尾
7.匹配重复元字符*,+,?,{n},0或多个，1或多个，0或1个
7.创建计算字段
1.拼接（concatenate）
用concat(obj1,obj2)函数拼接两个列,RTrim()删除右边空格，还可以有左边和两边
2.用as来赋予拼接后的列名
3.计算
8.使用函数
1.文本处理函数
Upper文本全部大写
...
2.日期和时间处理
Date()函数，提取日期部分用于比较,类似还有year(),month()
3.数值处理
一些常用的函数abs()等
9.汇总数据
对于NULL态度
avg()/max()/min()/sum()-忽略列值NULL的行
count()-指定列则忽略NULL；不指定则不忽略
1.聚集函数
avg(),count(),max(),min(),sum()
2.聚集不同的值
all/distinct
3.组合聚集函数
select count(..) as..,max() as ..,
10.分组数据
1.group by +with rollup可以总汇总
在where 之后，order by之前
2.过滤分组
having子句,将分组后的结果再过滤
与where区别，一个是分组前过滤，一个是分组后过滤
3.分组和排序
group by后面跟order by
4.select子句顺序
select,from,where(行过滤),group by(分组说明),having(组过滤),order by(结果排序),limit(检索行数)
11.子查询
类似于嵌套，将内部查询结果作为外部查询的输入参数,与in连用
相关子查询，customers.cust_id(完全限定列名),类似于这种
12.联结表
主键(primary key)--唯一
外键(foreign key)-是另一个表的一列(主键)
分解数据到多个表，能更有效的存储
1.创建联结
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
就是将两个表共有的部分取出，按情况合并并排序
2.笛卡尔积
没有用where,返回所有结果，包括错误结果
3.内部联结
INNER JOIN,需要用ON
select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;
4.多个表联结
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;
将三个表公共部分相互合并，products.vend_id和vendors.vend_id;products.prod_id和orderitems.prod_id合并，order_num =20005是过滤条件。
example2:
子查询：
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'));
思路：
1.从orderitems中选出prod_id = "TNT2"的order_num;
2.从orders中选order_num满足1得到的对应的cust_id;
3.选cust_name和cust_contact，他们满足cust_id来自2.
同样的结果
select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and prod_id = 'TNT2';
利用多表联结，
从customers,orders,orderitems选满足条件prod_id = 'TNT2'的cust_name和cust_contact列
13.高级联结
1.表别名-缩短sql语句
customers as c,orders as o, orderitems as oi
2.不同类型的联结
1).自联结
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';
2).自然联结
3).外部联结
23.视图
视图是虚拟的表，包含实时动态检索查询
1.创建create view
2.查看show create view viewname
3.更新drop/create
#创建view
create view productcustomers as
select cust_name,cust_contact,prod_id
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;
#查询
select cust_name,cust_contact
from productcustomers
where prod_id = 'TNT2';
存储过程
delimiter //
create procedure productpricing()
BEGIN
select avg(prod_price) as priceaverage
from products;
END //
delimiter //
#重新定义结束
delimiter ;
