sql高级语法入门

– 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]:排除单个字符

select *from books where price like ‘2%’; – 价格以2开头的记录
select *from books where price like ‘%5’; – 价格以5结尾的记录
select *from books where price like ‘%2%’; – 价格包含2的记录
select *from books where price like ‘23.5’; – 价格为23.5的记录，完全等价于–>

– 注意：

like使用模糊查询时如果开头出现%的形式，部分数据库可能不会走索引查询，降低查询效率，因此尽量开头使用确定的部分数值
select *from books where price=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]%’;

– 修改数据表的字段

alter table books add aspect varchar(10); – 添加aspect字段：格式：alter table 表名add 字段名 字段类型
alter table books drop column aspect; – 删除aspect字段：格式：alter table 表名 drop 字段名
alter table books modify column aspect int; – 修改字段类型：modify
alter table books change column aspect newAspect int; – 修改字段名和字段类型:change

– 查询完全不同的数据记录

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

– 约束处理

alter table books add unique key(id); – 添加unique约束
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; – 删除外键：指明外键约束名：可能存在多个外键
alter table book rename books; – 修改表名

– 多表查询

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：将查询的结果聚合起来，必须确保聚合之后的新表，字段和类型一致，否则报错

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;

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

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

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

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

– 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;

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

create or replace view booksView as select *from books where vendorId is not null;
drop view if exists booksView; – 如果存在则删除视图，避免删除不存在的试图导致异常

– 注意：

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

补充：BLOB类型的数据插入语句：

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