SQL必知必会
#数据库
#数据库为一些表的集合，表的每列为某一类数据类型的数据，每行为一记录
#主键
#每个表都有一列唯一标识记录的数据，称为主键
#同一表中任意两行数据都有不相同的主键，主键值不允许为NULL
#主键值不允许修改和重用
#检索
#从表1中检索列1 select 列1 from 表1
#从表1中检索列1，列2 select 列1，列2 from 表1
#sql语句不区分大小写，但表名列名是区分的
#每条sql语句以英文 ; 结尾，sql忽略缩进
#去重
#distinct指示数据库只返回不同的值
E: select distinct vend_id from Products;
#distinct关键字作用于其后所有的列
#限制结果
#limit， select list from table limit m,n;或limit n
#上面m为检索开始的索引，n为需要检索的记录条数，limit后只有一个数默认从索引0开始
#表中第一个索引，即第一行记录为0
#排序结果
#为保证每次检索结果顺序的唯一性，一般需要进行排序，order by
#select ... from 表 order by 列
#可依据多列进行排序，order by列1，列2
#可依据位置排序，select 列1,列2 form 表1 order by 2,1;（先依据列2再依据列1）
#可降序排列，关键词desc
E: select 列1 form 表1 order by 列2desc;
#desc只作用于其前面的那一列
#过滤数据
#where子句位于from子句之后
#where子句的操作符 = 等于、!=不等于、is null 为NULL值、between在两值之间
#！=3不返回含NULL的观测
#between用法为 between m and n
#多条件过滤
#即where子句为一些条件的组合
#进行组合的关键字为 or and not in
#当有多个条件时用括号进行分隔
E: select prod_name,prod_price from Products where vend_id in ("DLL01',"BRS01');
E: select prod_name from Products where not vend_id="DLL01';
#含通配符的过滤
#在搜索子句中使用通配符时，必须使用like操作符
# % 表示任何自出出现任意次数
E: select prod_id,prod_name from Products where prod_name like 'Fish%';
# like '%' 不会匹配为NULL的行
# _ 用法同 % ，但其只匹配单个字符
#计算字段
#即对检索出来的数据进行转换、计算或格式化的过程
#拼接字段
#concat（内容1，内容2，内容3）
E: select Concat(vend_name,'(',vend_country,')') from Vendors order by vend_name;
#在进行拼接操作时，一般需去空格操作
E: select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from Vendors;
#使用别名
#别名用as关键字赋予，新的别名不用加引号
E: select concat(rtrim(vend_name),'(',trim(vend_country),')') as vend_title from Vendors;
常用文本处理函数
left(str,length)
#从字符串左边开始截取一定长度的字符
E: select left('example',3)
#当length超过最大长度也可
right(str,length)
#从字符串右边开始截取一定长度的字符
lenth()
#返回字符串的长度
ltrim()
#去掉字符串左边的空格
rtrim()
#去掉字符串右边的空格
lower()
#将字符串转换为小写
upper()
#将字符串转换为大写
substring(str,length)
#从字符串一定位置开始截取到最后的字符
substring(str,length1,length2)
#从字符串一定位置开始截取一定长度的字符
soundex()
#将文本转换为描述性语音
E: select * from Table1 where soundex(tab1_name) = soundex('Y San')
#其可以将‘Y sam’检索出来
#数据类型的转换
convert(value,type)
#获取value数据后生成type类型的数据
E：select convert('3.35',signed);
#可以转换的数据类型
#二进制，binary
#字符型，char
#日期，date
#时间，time
#日期时间型，datetime
#浮点数，decimal
#整数，signed
#无符号整数，unsigned
#时间日期相关函数
now()
#生成年月日时间
E: select now()
2019-02-18 13:22:21
curdate()
#生成年月日
curtime()
#生成时间
#MySQL的数据类型
#文本
char 固定长度字符串（可包含字母、数字及特殊字符）最多255个字符
varchar 可变长度字符串（可包含字母、数字及特殊字符）最多255个字符
text 存放最大长度65535个字符的字符串
mediumtext 存放最大长度16777215个字符的字符串
#数字
int -2147483648到2147483647
float 带有浮动小数点的小数字
double 带有浮点小数点的大数字
decimal 作为字符串储存的double类型
#日期
date 日期，格式YYYY-MM-DD
datetime 日期和时间的组合,格式YYYY-MM-DD HH:MM:SS
time 时间，格式HH:MM:SS
year 日期，格式YYYY
#常用数值处理函数
abs() 返回一个数的绝对值
cos() 返回一个数的余弦
sin() 返回一个数的正弦
tan() 返回一个数的正切
exp() 返回一个数的指数值
sqrt() 返回一个数的平方根
pi() 返回圆周率
ceil(x) 返回大于x的最大整数值
floor(x) 返回小于x的最大整数值
mod(x,y) 同x%y，返回x/y的模，即作整除然后取余，被除数和除数都可为小数
round(x[,d]) 将数字x四舍五入到指定小数位d
truncate(x,d) 将数字x截断到指定的小数位数d
rand() 返回0到1的随机数，可以设定随机数种子，rand(4)
#常用聚集函数
#对某些行运行的函数
avg() 返回某列的平均值，只用于单列
E: sleect avg(prod_price) as avg_price from Products where vend_id = "DLL01"
count() 返回某列的行数
max() 返回某列的最大值或最大日期，忽略为NULL的行
min() 返回某列的最小值或最小日期，忽略为NULL的行
sum() 返回某列值之和，忽略列值为NULL的行
E:select sum(item_price*quantity) as total_price from OrderItems where order_num = 2005
#聚集参数
一般默认为所有行执行，当需对只包含不同的值执行时用到distinct
E:select avg(distinct prod_price) as avg_price from Products where vend_id = "DLL01"
#分组数据
#使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算，即便于聚集函数的汇总数据
#分组使用select语句的group by子句建立
E: select vend_id,count(*) as num_prods from Products group by vend_id;
即 select 分组依据列1，聚集函数操作 from Table group by 分组依据列1
#group by后可接任意数目的分组列，构成嵌套分组
E: select Dept,Edlevel,max(salary) as Maximum from Staff group by Dept,Edlevel
#分组依据中含NULL时，NULL作为一个分组标准
#select语句中，除去聚集函数操作，不能包含分组依据列名外的列名
#一般不允许分组列包含长度可变的数据类型（如文本或备注型字段）
#group by子句必须出现在where子句之后，order by子句之前
#过滤分组
#即过滤掉一些分组依据得到的分组
#过滤分组用having，其用法同where，但where只是选择符合条件的行，并不能过滤分组
E: select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;
#where和having可在分组中连用，先行过滤where，再分组group by，再分组过滤having
E: select vend_id,count(*) as num_prods from Products where prod_price >=4 group by vend_id
having count(*) >=2
#以上分析：首先，where子句过滤出prod——price至少为4的行，其次，按vend_id分组数据，最后，having
子句过滤出计数至少为2的分组
#分组排序
E: select vend_id,count(*) as num_prods fro Produts group by vend_id order by vend_id;
#注意，依靠group by得到的分组并不总是以分组顺序给出的
#一般在使用group by子句时，为确保排序的结果唯一，同时给出order by子句
#同group by，order by也可分组嵌套,order by 在子句最后
E: select order_num,count(*) as items from OrderItems group by order_num having count(*)
>=3 order by itemsorder_num;
#子查询
#即嵌套在其他查询中的查询，包括where子句中的子查询和计算字段中的子查询
#子查询用于where子句
E: select cust_id from Orders
where order_num in (select order_num from OrderItems where prod_id='RGAN01');
#select语句中子查询总是从内向处理，使用子查询利用好缩进
#子查询可以嵌套
E: select cust_name,cust_contact from Customers
where cust_id in (select cust_id from Orders
where order_num in (select order_num for OrderItems
where prod_id='RGAN01'));
#计算字段使用子查询
#即对检索出的每个记录（行数据）使用一次子查询
E: select cust_name,cust_state,
(select count(*) from Orders where Orders.cust_id = Customers.cust_id) as orders
form customers order by cust_name;
#计算字段中子查询可以使用子查询中from表的列，也可使用最外面form表的列
#计算字段中子查询使用最外面from表的列时要使用完全限定列名，即外表名.外表列名
#完全限定列名，即表名.列名，select操作多个表时，应使用完全限定列名来避免歧义
#关系表与关系数据库的设计
#关系表的设计即把信息分解成多个表，一类数据一个表，各表通过某些共同的值相互关联。
#联结
#当信息储存在多个表，可使用联结在一个select语句中检索出全部所需数据
#联结原理，select ... form表1,表2
where 表名1.列名a = 表名2.列名b；
#当要多联结时，select ... form表1,表2,表3
where 表名1.列名a=表名2.列名b and 表名2.列名c=表名3.列名d
E: select vend_name,prod_name,prod_price from Vendors,Produts
where Vendors.vend_id=Prodects.vend_id;
#上面vend_name,prod_name是Vendors中的列,prod_price为Produts中的列
#当无联结条件即无where子句，称为笛卡尔积，检索出的行数为第一个表的行数乘第二个表的行数
#内联结
#inner join on
E: select vend_name,prod_name,prod_price
from Vendors inner join Products on Vendors.ven_id=Products.vend_id;
#即inner join 两端为需要联结的表，on 后面为具体两表要联结的列，效果与上面相同
#表别名
#使用表别名缩短了sql语句，允许在一个select语句中多次使用相同的表
#表别名可用于where子句、完全限定列名、order by子句等
E: select cust_name,cust_contact from Customers as c,Orders as O,OrderItems as OI
where C.cust_id = O.cust_id and OI.order_num = O.order_num
and prod_id = 'RGAN01';
#自联结
#即联结操作中两个表是同一个表
E: 选出与 jim Jones同一公司的所有顾客
E: select c1.cust_id,c1.cust_name,c1.cust_contact from Customers as c1,Customers as c2
where c1.cust_name =c2.cust_name and c2.cust_contact = 'Jim Jones';
#自然联结
#无论何种联结，应该至少有一列不止出现在一个表中
#自然联结针对某表，使该表所含的需要与其他表联结的相同列，结果中只出现一次
#对某表进行自然联结使用 表.*
E: select c.*,o.order_num,o.order_date, from customers as c, orders as o
where c.cust_id = o.cust_id and prod_id ='GCAN01';
#上面 c.* 作用，当c表含有order_num列和order_date列，限定这两次在结果中只出现一次
#一般所有的所有的内联结都是自然联结，一般用不到不是自然联结的内联结
#外联结
#对外联结，联结包含了那些在相关表中没有关联行的行
#用法，from 表1 left outer join 表2 on 表1.列1 = 表2.列1
#表1.列1与表2.列1联结，只要表1.列1出现的，无论表2.列1有无与其对应，都会检索出来
E: select table1.price,table2.id,table1.state
from table2 left outer join table1
on table2.id = table1.id
#left outer join检索出左表所有，right outer join检索出右表所有
#联结中使用聚合函数
E: select Customers.cust_id,count(Orders.order_num) as num_ord
from Customers inner join Orders
on Customers.cust_id = Orders.cust_id
group by Customers.cust_id;
#组合查询
#一般一条sql语句返回一个结果，组合查询即执行多个查询并返回一个结果
#用union组合多条select语句
E: select cust_name,cust_contact,cust_email from Customers
where cust_state in ('IL','IN','MI')
union
select cust_name,cust_contact,cust_email from Customers
where cust_name = 'Fun4All';
#对于复杂顾虑条件，或从多个表中检索，用union会更简单
#union中的每个查询必须含相同的列、表达式或聚集函数
#union对结果自动去除重复的行，同where or，union all 不去重
#union中只能使用一条order by，放在最后
#插入数据
E: insert into table values('a',11,NULL)
#上面操作并不安全，表结构发生变化时，不再适用
E: insert into Customers(cust_id,cust_name,cust_address)
values('1000006','Toy Land','123 any Street');
#insert中可以省略列，省略的列使用默认值
#插入检索出的数据
#insert select
#insert into Customers(cust_id,cust_contact,cust_email)
select cust_id,cust_contact,cust_email) from CustNew;
#复制到新表
E: create talbe CustoCopy as select * from Customers;
#更新数据
#更新（修改）表中数据，使用update
E: update Customers set cust_email='hello@ss.com'
where cust_id = '1000034';
#更新多个列，update talbe set list1 = ... ，list = ... ,
where ...
#where 条件中 空字符 不等于 NULL
#删除数据
E: delete from Customers where cust_id = '100032';
#主键与外键
#主键是每行数据的唯一标识，外键能保证表与表之间的数据的完整性和准确性
#外键可以防止数据的误删
#创建表
E: create table NowProducts(
prod_id char(10) not null,
vend_id char(10) not null,
prod_num int(4) nul );
#上面括号中为列名，数据类型及解释，是否允许为空
#一般null为默认，即默认允许为空
#默认值
E: create talbe OrderItems(
order_num char(10) not null default 1);
#默认值常用于日期或时间，如使用now() 函数
#更新表
#删除列
#用法， alter table 表名 drop column 列名
#添加列
#用法，alter table 表名 add column 列名 数据类型及解释
E: alter table Person add column birthday char(20);
#删除表
#用法，drop table 表名
#修改表名
#rename table 原表名 to 新表名
#视图
视图是虚拟的表，即是查询的结果
E: select cust_name,cust_contact from ProductCustomers
where prod_id = 'RGAN01';
#上面ProductCustomers为一个视图，其包含一个查询
#视图可以简化sql操作，可以嵌套，但会降低性能
#使用视图时，禁止使用order by
#视图的创建
E: create view ProductCustomers as
select cust_name,cust_contact,prod_id from Customers,Orders,OrderItemss
where Customers.cust_id = Orders.cust_id and
OrderItems.order_num = Orders.order_num;
#存储过程
#一组为了完成特定功能的sql语句集，静态编译后存储在数据库中
#对sql语句执行时需要先编译再执行，存储过程提高了性能，简化了操作
#存储过程的执行
#: call Procedurename(value1,value2,...)
#存储过程不返回值，只是修改值
#存储过程的创建
E:
drop procedure if exists 'myproc';
delimiter ;;
create procedure myproc(in value_a int,in value_b int,out value_sum int)
begin
declare c int;
if value_a is null then set a = 0;
end if;
if value_b is null then set b = 0;
end if;
set sum = a + b;
end
;;
delimiter ;
#存储过程的关键字
#存储过程有关键字 in out inout
#in 表示后面的变量为只读的，即只读入存储过程中
#out 表后后面的变量
浙公网安备 33010602011771号