数值和字符范围比较: 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!')));