SQL回顾二

where 条件：
=\<\>\<>\!=\>=
or:或者
and：并且
between A and B：在A和B之间
in (值)：在……内部
is null：为null
not:不
select的标准格式：select *|字段列表|聚合函数 from 表名 [where 条件] [group by 字段] [having 字段][order by 字段 ASC|DESC][limit 起始索引,数量]]


一、模糊查询
like:模糊查询的关键字
_:1个字符
%:任意个字符


1.1以XX开头
格式：select *|字段 from 表名 where 字段 like 'XX%';


查询商品表中名字以华为开头的
select * from goods where name like '华为%';
查询商品表中名字以华为开头的且后面就一个字符的商品
select * from goods where name like '华为_' ;


1.2以XX结尾
格式：select *|字段 from 表名 where 字段 like '%XX';


查询商品表中价格以9结尾的
select * from goods where price like '%9';
查询商品表中价格为三位数且以9结尾
select * from goods where price like '__9';
等价于
select * from goods where price like '%9' and price >99 and price <1000;


1.3包含XX
格式：select *|字段 from 表名 where 字段 like '%XX%';


查询商品表中名字包含o的商品
select * from goods where name like '%o%';




创建一个商品表：
create table goods(id int,name varchar(50),address varchar(50),price int);
新增数据
insert into goods(id,name,address,price) values(1,'华为手机P9',"河南",3999);
insert into goods(id,name,address,price) values(2,'华为手机P10',"河南",2999);
insert into goods(id,name,address,price) values(3,'苹果7',"天津",999);
insert into goods(id,name,address,price) values(4,'苹果7s',"北京",30999);
insert into goods(id,name,address,price) values(5,'小米6',"河南",9999);
insert into goods(id,name,address,price) values(6,'三星note7',"河南",99);
insert into goods(id,name,address,price) values(7,'三星note8',"首尔",999);
insert into goods(id,name,address,price) values(8,'中兴G7',"山东",888);
insert into goods(id,name,address,price) values(9,'oppoR9',"河北",7777);




二、字段控制查询


2.1查询结果去重
distinct:对查询结果去重


//查询goods表中所有数据并去除重复的
select DISTINCT * from goods;


2.2对null值转换
ifnull(字段名称 , 待转换的值)：标记某个字段为null的话转换为指定的数据参与运算
查询goods表中id和price的和
select id+price from goods;
null值和任何类型的数据运算结果都是null


select id+ifnull(price,10000) from goods;


2.3别名
可以为运算的结果起别名： as 别名
也可以为表起别名，as可以省略


查询goods表中id和价格的和，要求价格为null则作为10000处理
select id+ifnull(price,10000) as total from goods;
等价于
select id+ifnull(price,10000) total from goods;


查询goods表名价格小于100的商品
select * from goods where price <100;
等价于
select * from goods where goods.price <100;
等价于
select * from goods g where g.price <100;






三、排序
order by 字段 排序方式,……
排序方式：ASC:升序(默认排序)，DESC:降序


查询goods表中按照价格升序排列
select * from goods order by price;
等价于
select * from goods order by price ASC;


查询goods表中按照价格降序排列
select * from goods order by price DESC;


查询goods表中按照价格降序排列,如果价格相同按照id进行升序排列
select * from goods order by price DESC, id asc;


四、聚合函数
COUNT:计数，计算指定列不为null的数量
MAX:计算指定列的最大值
MIN:计算指定列的最小值
SUM:计算指定列的总和
AVG:计算指定列的平均值






查询goods表中的数据的数量
//查询goods表中price不为null的数据行
select count(price) from goods;
//查询goods表中id不为null的数据行
select count(id) from goods;
//查询goods表中的数据行
select count(1) from goods;


查询goods表中所有price的总和
select sum(price) from goods;
查询goods表中价格最高和和最低的商品
select MAX(price) maxp,min(price) minp from goods;
查询goods表中的评价价格
select avg(price) from goods;




五、分组
group by 字段名称 [having 条件]




create table student (no int ,name varchar(5),sex varchar(2),age int);
insert into student values(17000501,'桑晓东','男',18);
insert into student values(17000502,'张文秀','男',18);
insert into student values(17000503,'曹怡','女',16);
insert into student values(17000504,'杨海婷','女',12);
insert into student values(17000505,'贾金山','女',16);
insert into student values(17000506,'卢林','男',28);
insert into student values(17000506,'卢林','男',28);


查询学生表中男女的数量
select sex,count(no) from student group by sex;
查询学生表中男女的平均年龄
select sex,avg(age) from student group by sex;
查询每个年龄的学生的数量
select age,count(no) from student group by age;
查询学生表中每个年龄的人数并且，人数等于的1


select age,count(no) ct from student group by age having ct=1;


where和having的区别
where:用在表名后，进行条件的筛选，主要用在分组前
having：用在group by之后，主要对分组的数据进行筛选




六、limit分页
格式：limit 行索引,数量：从指定的行索引开始查询，查询指定的数量


查询学生表中前三条数据
select * from student limit 0,3;
查询学生表中第三条到底五条的学生
select * from student order by no limit 2,3;


分页：每页显示2条数据，请查询第三页的数据
select * from student order by no limit 4,2;
//分页算法
int count=2;int page=3;
select * from student order by no limit (page-1)*count,count;


查询的顺序：from-->where--->group by --->having--->order by --->limit


七、数据完整性
约束条件：
主键、唯一约束、自增、不为null、默认值、外键等


7.1主键：唯一标记，不可为null，不能重复
任何表必须有主键


添加主键的方式：
1、create table (字段 primary key)
2、create table (字段,primary key(字段名称,字段名称))
3、alter table 表名 add primary key(字段名称);


//delete from student where no =(select no from student  limit 5,1);


delete from student where no=17000506;
//添加主键为no
alter table student add primary key(no);


create table grade(id int,name varchar(20),primary key(id));


7.2unique:唯一约束 ，不能重复，可以为null(多个)
create table test1(id int unique );


7.3auto_increment:自动增长，每次递增1,一般主键使用
create table test2(id int primary key auto_increment,msg varchar(20) unique);


7.4not null:不为null


create table test3(id int not null);


7.5default:默认值
create table test4(id int primary key auto_increment,psw varchar(6) not null default '123456');
insert into test4(psw) values(default);
insert into test4(psw) values(null);


7.6FOREIGN KEY ：外键
外键：一个表中的某个字段的值必须为另张表中的主键的值，这时我们可以使用外键约束
作用：当新增数据时，如果当前的外键的值在另张表的主键的值不存在，就报错
CONSTRAINT 外键名称(外键表中的名称) foreign key(当前表中的外键) references 另张表名称(主键名称)
外键的添加方式:
1、create table 表名(字段,外键,CONSTRAINT 外键名称(外键表中的名称) foreign key(当前表中的外键) references 另张表名称(主键名称))；
2、ALTER TABLE 表名ADD CONSTRAINT 外键名称FOREIGN KEY(当前表中的外键) REFERENCES  另张表名称(主键名称))；
create table student (sid int primary key auto_increment,name varchar(20),
fk_cid int,
CONSTRAINT fk_student_cid foreign key(fk_cid) references class (cid));
insert into student(name,fk_cid) values('小贾',1);




八、多表关系：
8.1一对一
2表数据一一对应
8.2一对多
2表数据，存在一对多
8.3多对多
2表数据，存在多对多的关系


九、三大范式
阐述如何创建表并定义之间联系
9.1第一大范式：
确定列的原子性
就是每列的内容各不相同，要划分为最小单元
9.2第二大范式
确保有主键
主要用于区分每条数据，并定位
9.3第三大范式
表与表之间通过主键关联
主键唯一


功能单一原则：表描述的或存储的数据单一
关联通过主键


十、多表查询
主要用来查询多张表
1、合并结果集
UNION ：合并2个查询的结果，要求2个查询的列的个数和列的数据类型必须相同，会消除重复的数据
UNION ALL:合并2个查询的结果，要求2个查询的列的个数和列的数据类型必须相同，不会消除重复的数据


2、内部连接查询
inner join on：内部连接
格式：select * from 表1 inner join 表2 on 表1字段=表2字段
查询2个表的数据，内部连接的形式
select * from mytest1 inner join mytest2 on mytest1.id=mytest2.id2;


3、外部连接查询
左外联：left outer join on
格式：select * from 表1 left join 表2 on 表1字段=表2字段
左联就是表1为主表，表一数据都有，表2有就显示，没有就是显示为null
右外联：right outer join on
格式：select * from 表1 left join 表2 on 表1字段=表2字段
右联就是表2为主表，表二数据都有，表1有就显示，没有就显示为null




左联查询2表
select * from mytest1 t1 left join mytest2 t2 on t1.id=t2.id2;
右联查询2表
select * from mytest1 t1 right join mytest2 t2 on t1.id=t2.id2;
等价于
select * from mytest2 t2 left join mytest1 t1 on t1.id=t2.id2;


4、连接查询
查询2个表中所有数据的组合结果
select * from mytest1,mytest2;
产生的结果集：笛卡尔积
结果的数量：表1的数量*表2的数量




十一、内置函数
abs:取绝对值
bin:十进制转换二进制
ceiling:向上取整
floor:向下取整
rand:随机数：0.0-1.0(不包含)
now:现在日期+时间
current_date:现在的日期
current_time:现在时间
instr(字符串,要查找的内容):查询对应的索引
ucase:转换为大写
lcase:转换为小写
replace(字符串,原来的值,新的值):替换
substring(字符串,开始索引,长度):截取


十二、杂谈
1、索引：
提高查询的效率
通过索引快速进行查询，速度会提高
2、视图：
为比较复杂的结果集创建一个虚拟的表
格式：create view 名称 as select * from 表名；
3、触发器：
一张表操作时，会对另外的表页进行操作
格式：
delimiter $修改当前的结束符为$(默认的结束;)
create trigger 触发器名称 before|after insert|delete|update on 表名主 
for each row  
begin
要执行的sql语句
例如：
insert into 表名被(字段名称) values(new.表名主字段);
end $




有2张表，user和mylog
现在要求：新增用户时，默认会记录一条日志


delimiter $
create trigger t_test after insert on user for each row
begin
insert into myLog(msg,time) values(new.name,current_date());
end $


4、快速备份新表
create table 新表名称 select * from 旧表


其他数据库：
select * into 新表名 from 旧表;但是MySQL不支持