数值和字符范围比较： 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.5select *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;  -- 修改字段类型：modifyalter table books modify  column aspect int;-- 修改字段名和字段类型:changealter 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：左表数据完全输出，右表如匹配则输出，无匹配则输出NULLselect *from books left join vendors on books.vendorId=vendors.id;-- 右外连接 right join：右表数据完全输出，左表如果匹配则输出，无匹配则输出nullselect *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=1unionselect *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=1union allselect *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 wherenot 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!')));