【SQL】- 高级用法

2019独角兽企业重金招聘Python工程师标准>>>

数值和字符范围比较： between

select *from books where price>=23.5 and price<=35; 

-- 等价于
select *from books where price between 23.5 and 35; 
-- between包含边界的比较>= / <=:注意小的值在前面

-- 字符比较
select *from books where bookName between '水浒传' and '西游记'; 

模糊查询：% 通配符

• _ 下划线：单个字符 [charlist]
• 排除单个字符： [^charlist] / [!charlist]

注意： like 模糊查询时如开头出现 % 形式，部分数据库可能不会走索引查询，降低查询效率，因此尽量开头使用确定的部分数值

-- 价格以2开头的记录
select *from books where price like '2%';

-- 价格以5结尾的记录
select *from books where price like '%5';

-- 价格包含2的记录
select *from books where price like '%2%'; 

--  价格为23.5记录，等价于 price = 23.5
select *from books where price like '23.5';

-- sql的通配符的使用
>select *from books where price like '2_.5';
select *from books where price like '2%5';
select *from books where price like '[23]%';
select *from books where price like '[^23]%';

修改数据表字段

-- 添加aspect字段：格式：alter table 表名add 字段名 字段类型
alter table books add aspect varchar(10); 

-- aspect 字段在prize字段后(调整表字段顺序，默认加在最后)
alter table books add aspect varchar(10) after prize; 

-- 删除aspect字段：格式：alter table 表名 drop 字段名
alter table books drop column aspect; 

 -- 修改字段类型：modify
alter table books modify  column aspect int;

-- 修改字段名和字段类型:change
alter table books change column aspect newAspect int; 

-- 修改表名
alter table book rename books;  

查询完全不同的数据记录

-- bookName唯一记录:会降低查询速度：不走索引
select distinct(bookName) from books;

约束处理

-- 添加unique约束
alter table books add unique key(id);

-- 添加主键约束
alter table books add constraint PK_books primary key books(id); 

-- 添加外键：级联更新、级联删除
alter table books add constraint FR_books_vendors foreign key (vendorId) references vendors(id) on delete cascade on update cascade;

-- 删除主键约束
alter table books drop primary key;

-- 删除外键：指明外键约束名：可能存在多个外键
alter table books drop foreign key FR_books_vendors; 

多表查询

-- 查询书籍信息及出版商信息
select *from books,vendors where books.vendorId=vendors.id;  

-- 等价于内连接：inner join:两个表都匹配才返回：join(缺省写法)
select *from books inner join vendors on books.vendorId=vendors.id; 

-- 左外连接left join：左表数据完全输出，右表如匹配则输出，无匹配则输出NULL
select *from books left join vendors on books.vendorId=vendors.id;

-- 右外连接 right join：右表数据完全输出，左表如果匹配则输出，无匹配则输出null
select *from books right join vendors on books.vendorId=vendors.id;

-- mysql不支持全链接：full join:只要两个表存在数据就显示输出，没有匹配就使用null值填充输出：可通过左连接和右链接，用union聚合实现效果
select *from books full join vendors on books.vendorId=vendors.id;

结果聚合union：需确保聚合的子结果集结构和类型(或可转换)一致

-- union: 聚合结构并去重
select *from books join vendors on books.vendorId=vendors.id where books.id=1
union
select *from books join vendors on books.vendorId=vendors.id where books.id=2;

-- union: 聚合结构不去重，效率更高
select *from books join vendors on books.vendorId=vendors.id where books.id=1
union all
select *from books join vendors on books.vendorId=vendors.id where books.id=2;

表复制

• select into : 从其他表查询数据插入到另外1张表里面：一般用来生成测试数据(指数增长)或者备份数据

-- 格式：slect 字段名 into 新表名 from 原始数据表: mysql不支持
select id,name,tele,manager into vendorBak from vendors; 

-- mysql替代做法:表内自复制
insert into vendors(name,tele,manager) select name,tele,manager from vendors; 

-- mysql新表备份原始表数据
create table vendorsBak(select *from vendors); 

exist 用法

-- 查找公司工资高于1500的同事
select * from employee where deptid not in(select deptid from employee where salary < 1500)

-- sql优化：not in不经过索引， not exists 使用索引，提高效率, 相当于拿主表数据一条一条匹配exist条件
select * from employee as emp where not exists(select 1 from employee where salary < 1500 and emp.deptid = deptid);
select * from employee as emp where exists(select 1 from employee where salary > 1500 and emp.deptid = deptid);

IFNULL函数: 如果字段为null值则返回设定的默认值

select bookName,author,price,IFNULL(vendorId,0) from books;
select *from books where vendorId is not null;
select *from books where vendorId is null;

行传列：group_concat

-- 分别统计性别男、女的学生名
select stuGender, group_concat(stuName,",") from student group by stuGender

创建视图：从sql语句语义可看出来视图本质就是特定sql语句查询结果的映射(别名)，方便直接使用过滤之后的数据

-- 创建视图
create or replace view booksView as select *from books where vendorId is not null;

 -- 如果存在则删除视图，避免删除不存在的试图导致异常
drop view if exists booksView; 

-- 注意：
视图中关联的数据表如果数据发生改变，视图的筛选出来的数据也会在满足条件的基础上同步更新数据

数据去重

-- 注意去重的表必须存在唯一约束的字段，否则无法去重
delete from employee where id not in(select *from (select min(id) from employee group by name) emp)

-- sql优化
delete from employee where
not exists(select * from (select min(id) as id from employee group by name) em where em.id=employee.id);

BLOB类型数据插入

insert into test values (UNHEX(HEX('Hello Python World!')));

转载于:https://my.oschina.net/yangzhiwei256/blog/3013896