SQL学习

SQL基础

数据模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：
层次模型：树状
网状模型：网状
关系模型：表格，行列

DDL：Data Definition Language数据定义语言
DDL允许用户定义数据，也就是创建表create、删除表drop、修改表alter结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language数据操纵语言
DML为用户提供添加inset、删除delete、更新update数据的能力，这些是应用程序对数据库的日常操作。

DCL：Data Control Language数据控制语言
DCL是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。commit、rollback

创建表空间
create tablespace AA add datafile 保存路径；

查看所有数据库
show databases;
创建数据库
create database sas；
删除
drop tablespace tablespace_name；
创建表
create

create table student(
    StudentID int primaty key,
    StudentName varchar(10),
    Studentclass varchar(8),
    Studentgrade grade,
);

DML增删改查

查

insert into student values('01','李四','一班',200);
delete from student where StudentID='01';
update student set StudentName = '张三' where StudentName = '李四';
select * from student where Studentclass like '[一]%';

单表查询
查询结果展示：

select 列名1，列名2 as 别名 from 表名 as 别名；

常数添加

select 列名1，列名2，0.9 as discount from 表名；

四则运算

select 列名1，列名2，列名2-列名1 as profit from 表名；

字符拼接(为一个)

select concat(列名1，列名2，...) as 产品信息 from 表名；
select concat(name，'是'，列名2) as 产品信息 from 表名；

指定拼接符concat_ws

select m.*,concat_ws(‘是’,name，列名2) as 产品信息 from 表名；

去重复

select distinct m.valid_month from 表名as 别名；

结果排序order by

select m.* from 表名 as 别名 order by m.valid_month ASC[DESC];
select m.* from 表名 as 别名 order by convert(m.name) ASC[DESC];--中文转为gdk格式排序
select m.* from 表名 as 别名 order by convert(m.name) ,m.valid_month DESC ;--只会后者降序

数据过滤
根据指定条件，过滤结果数据。不能单独使用，非必需子句

select m.* from milk_tea as m where m.prod_name = '奶糖' ;

对于null数据

select m.* from milk_tea as m where ifnull(sale_price,0) <> 15;

数值过滤

select m.* from milk_tea as m where m.in_price between 5 and 15;

空值过滤

select m.* from milk_tea as m where m.sale_price is null;

非空值过滤

select m.* from milk_tea as m where m.sale_price is not null;

模糊查询
like '…_…'单个，或者‘…%…’任何数目

select m.* from milk_tea as m where m.prod_name like '奶%' ;
select m.* from milk_tea as m where m.prod_name like '_糖' ;--奶糖
select m.* from milk_tea as m where m.prod_name like '__糖' ;--棒棒糖

多个过滤条件
and \or \and…or…

select m.* from milk_tea as m where m.in_price >= 5 and m.in_price <=15;
select m.* from milk_tea as m where m.in_price >= 5 or m.in_price <=15;
select m.* from milk_tea as m where (m.in_price >= 5 and m.in_price <=15) or m.in_price between 1 and 5;

取值限制 in

select m.* from milk_tea as m where m.prod_name in ('奶茶','薯片','棒棒糖');

否定条件 not(只否定紧跟着的条件)

select m.* from milk_tea as m where not m.prod_name in ('奶茶','薯片','棒棒糖');

数据类型
数值型

--数值型
integer,decimal（固定小数位）,float\double\real
--文本型
char（定长）,varchar（可变长）
--日期型
date\time\datetime

函数使用
绝对值函数 ABS(-3)=3
平方根函数SQRT(4)=2
指数函数EXP(4)
四舍五入函数round(1.234)=1.23
圆周率函数 PI()
文本处理函数
字符长度 char_length(‘数据’)=2
字节数 length(‘数据’)=6
去除右边空值 Ltrim
去除左边空值 Ltrim
大写字母转换 Upper
小写字母转换Lower

select upper(t.id_char) from test_sjlx as t;
select length(t.id_char) from test_sjlx as t;

聚合函数

函数表达式 = 聚合函数名 + 参数
计数函数
count(*)/(1)
count(col)–只作用非NULL行
count(distinct col)–对行去重
sum(col)求和，null作为0
avg(col)求平均，null作为0
max(col)
min(col)

select count(1) from milk_tea;
select avg(m.sale_price) from milk_tea as m;
select sum(m.sale_price) from milk_tea as m where m.net_w='100g';

分组数据

group by不用别名

select m.net_w,sum(m.sale_price) from milk_tea as m where m.net_w in('100g','150g') group by m.net_w;--数据求和，挑选100g和150g分别进行聚合

必须子句（select…from…）过滤行（where）分组子句（group by）分组筛选子句（having）最后排序（order by);
where是行条件，having是组条件
分组条件与聚合函数同时存在，筛选组必有分组条件

--将数据分组，各组进行聚合，对聚合结果再次筛选，得到所需分组
select m.net_w,sum(m.sale_price) from milk_tea as m where m.net_w  group by m.net_w having sum(m.sale_price)>20;

select p.class,count(1) from prid_info as p where p.sale_price>10 group by p.class;

多表查询

--只是把大于15，换成了查到的15
select * 
from milk_tea as m1
where m1.sale_price >(
                      select m.sale_price
                      from milk_tea as m
                      where m.prod_name='奶茶');

select m1.*,(select m.sale_price
            from milk_tea as m
            where m.prod_name = '奶茶')
from milk_tea as m1;

select * 
from prod_info as p2 
where p2.class='日用品'
  and p2.sale_price>(
                      select avg(sale_price) 
                      from prod_info as p 
                      where p.class='日用品' 
                      Group by p.class)

表联结（关联多个表）

select 列名 from 表1，表2 where 表1.列a = 表2.列b

内部联结
inner join … on …

select * from pro_info as p where p.prod_id in ('20003','20004');

select p.*,l.*
from prod_info as p,order_list as l
where p.prod_id = l.prod_id
  and l.order_id ='20190403001';

select p.*,l.*
from prod_info as p join order_list as l
on p.prod_id = l.prod_id
  and l.order_id ='20190403001';

外部联结
outer join … on …
左外部-包含所有左表行
右外部-包含所有右表行
全外部-包含两个表所有行

select c.*,l.*
from cust_info as c left join order_list as l
on c.cust_id = l.cut_id
and l,order_id like '201904%';

select c2.cust_id,count(c2.prod_id)
from(select c.cust_id,c.cust_name,l.prod_id,l.prodname,l.oder_id
     from cust_info as c left outer join order_list as l
     on c.cust_id = l.cust_id
     and l.order_id like '20190401%') c2
group by c2.cust_id;
--联结
select c.cust_id,count(l.prod_id)
from cust_info as c left outer join order_list as l
on c.cust_id = l.cust_id
and l.order_id like '20190401%'
group by c.cust_id;

select * from order_list1 as 1 where 1.order_id like '20190407%'
union
select * from order_list2 as 1 where 1.order_id like '20190407%'

数据插入

insert into prod_info2 values ('T0001','测试产品','test','test',16,20,'NJ0001');

insert into prod_info2(prod_id,prod_name) values ('T0001','测试产品')

查到的数据插入表

values替换为查找的表select

insert into prod_info2
select concat('T',p.prod_id), p.prod_name, p.brand, p.type, p.class, p.cost, p.sale_price, p.supplier_id
from prod_info as p
where p.prod_id = '10001'; 

数据更新

update prod_info2
set sale_price = sale_price / 0.9
where prod_name = '抽纸' or class = '饮料';

update prod_info2 p2
inner join prod_info p
set p2.cost = p.sale_price
where prod_name = p.prod_name
  and p2.brand = p.brand
  and p2.type = p.type;

删除

删除表中

delete from prod_info2 where class = '饮料'；

DDL

创建表

create table pet2
(name  varchar(255) not null,
ower varchar(255) not null,
sex char(1) default 'm',
birth date
);

insert into pet2 values('B','cdsad','w','2020-08-30');

复制表

create table pet3
as
select p2.name,p2.owner from pet2 as p2;

更新表结构

--增加
alter table pet3 add sex char(1);
alter table pet3 add birth date not null;
--删除
alter table pet3 drop birth,drop sex;
--修改类型
alter table pet3 add sex char(1);
alter table pet3 modify column sex char(1) not null;

删除表和重命名

drop table pet3;

rename table pet3 to pet4;

视图 - 虚拟表

--创建视图
create view pet_show
as 
select name, species from pet;
--删除视图
drop view pet_show;

Navicat中操作：

--mysql
create procedure pro_test()
create table pet2 as select * from pet;
call pro_test();
--查到数据

DCL-数据库管理

insert into pet2 values('test1','test','test','f','20130901',null);

回退
rollback;

--自动提交机制关掉
set autocommit =0;
insert into pet2 values('test2','test','test','f','20130901',null);

rollback;

--提交
commit;

begin;
delect from pet2 where name='test2';
savepiont sp1;
delect from pet2 where name='test1';
select * from pet2;

savepiont sp2;
insert into pet2 values('test3','test','test','f','20130901',null);

rollback to sp1;

--释放sp1
release savepiont sp1;

alter table 表名
add constraint primary key 列名;

数据库拓展