MySQL数据库3

8.1基本select语句
1.查询所有
select from aa_user;
2.查询指定列
select user_name,user_sex from aa_user;
3.基本条件查询【=or and】
select * from aa_user where user_id=6;
select * from aa_user where user_id=11 or user_sex=false;
select * from aa_user where user_name=‘lisi’ and user_sex=false;

4.比较运算符查询
select * from aa_user where user_id<8;
select * from aa_user where user_id<10 and user_sex=true;
5.区间查询 between 最小值 and 最大值
select * from aa_user where user_id between 6 and 10;
select * from aa_user where user_id<=10 and user_id>=8;
6.in(集合)条件查询
select * from aa_user where user_id in(7,8,9);
7模糊查询
select * from aa_user where user_name like’w%’;
select * from aa_user where user_name like’%i’;
select * from aa_user where user_name like’%s%’;
8.is null
select * from aa_user where user_height is null;
9 not
select * from aa_user where not(user_height is null);
select * from aa_user where not user_id<12;
10.排序查询 order by 列名 asc[升序]|desc[降序]
select * from t_user order by uage asc;
select * from t_user order by uheight desc;
11.SQL聚合函数查询
count(列名/) – 行的总数【总记录数】
select count(*)from t_user;
#sum(列名)—求和
select sum(uheight)from t_user;
avg(列名)—求平均数
select avg(uheight)from t_user;
max(列名)—求最大值
select max(uage)from t_user;
#min(列名)—求最小值
select min(uage)from t_user;
12分页查询 limit 参数1，参数2
*参数1–当前页的第一条记录的开始位置[(当前页码-1）每页记录数】
参数2—每页记录数
共有8条记录 每页显示3条记录
第一页
select * from t_user limit 0,3;
第二页
select * from t_user limit 3,3;
第三页
select * from t_user limit 6,3;
13分组查询 group by 列名[先分组，后查询统计]
select uage,count(uage)from t_user group by uage;
select usex,count(usex)from t_user group by usex;
14别名查询
数据库表可以有别名【表名称 as 表的别名/表名称 表的别名】
例如：t_user ad 用户表 /t_user 用户表
select usex,count(usex)from t_user as 用户表 group by usex;
#表中的列[包含统计函数]可以有别名
#[列名称 as 列的别名/列名称 列的别名]
select usex as 性别,count(usex)总数 from t_user as 用户表 group by usex;
select usex as 性别,count(usex)总数 from t_user as 用户表 group by 用户表.usex;
别名通常情况下会在多表查询中区分名称相同的列。
表与表之间的关系
下面这三种关系都需要外键来建立关系
foreign key 外键
创建表的时候:foreign key 表名(列名)references 表名(列名）
通过修改表结构的方式添加外键
alter table 表名 add constraint fk1 foreign key (列名) references 表名(列名);
一对一
一对一主件关联关系【主键关联主键，主键及既主键也是外键】
创建用户信息表
create table t_user(
u_id int primary key auto_increment,
u_name varchar(20),
u_age int,
u_sex bit,
u_address varchar(30),
foreign key t_user(u_id)references t_card(c_id)
)
创建用户身份证信息表
create table t_card(
c_id int primary key auto_increment,
c_number varchar(18),
c_arg varchar(10),
c_year int
);
一对一外键关联关系【专门创建外键列，外键列关联到主键列】
创建用户信息表
create table t_user(
u_id int primary key auto_increment,
u_name varchar(20),
u_age int,
u_sex bit,
u_address varchar(30),
card_id int unique,
foreign key t_user(card_id)references t_card(c_id)
);
#创建用户身份证信息表
create table t_card(
c_id int primary key auto_increment,
c_number varchar(18),
c_arg varchar(10),
c_year int
);
向用户身份证信息表中添加测试数据
insert into t_card values(null,‘521131541121115266’,‘陕西.西安’,10);
insert into t_card values(null,‘123456789123456789’,‘陕西.铜川’,55);
向用户信息表添加测试数据
insert into t_user values(null,‘zhangsan’,88,true,‘长安县’,1);
insert into t_user values(null,‘lisi’,99,true,‘雁塔区’,2);
一对多【外键的建立要放在多方表中维护】
创建一个班级表
create table t_class(
c_id int primary key auto_increment,
c_number varchar(10),
c_name varchar(10)
);
创建学生表
create table t_student(
s_id int primary key auto_increment,
s_name varchar(20),
s_age int,
s_sex bit,
s_address varchar(30),
class_id int,
foreign key t_student(class_id)references t_class(c_id)
);
向班级表添加测试数据
insert into t_class values(null,‘J2020000’,‘javaEE’);
insert into t_class values(null,‘A20201999’,‘Android’);
insert into t_class values(null,‘C20180808’,‘IOS’);
向学生表添加测试数据
insert into t_student values(null,‘zhangsan’,23,true,‘西安’,1);
insert into t_student values(null,‘lisi’,24,false,‘北京’,2);
insert into t_student values(null,‘wangwu’,25,true,‘上海’,3);
insert into t_student values(null,‘zhangsansan’,26,true,‘西安南’,1);
insert into t_student values(null,‘lisisi’,27,false,‘北京北’,2);
insert into t_student values(null,‘wangwuwu’,28,true,‘上海东’,3);
多对多【需要单独创建一张表类维护关系】
创建角色表
create table t_role(
r_id int primary key auto_increment,
r_name varchar(10),
r_desc varchar(30)
);
create table t_group(
g_id int primary key auto_increment,
g_name varchar(20),
g_desc varchar(30)
);
#创建中间表维护关联关系
create table t_rolegroup(
z_id int primary key auto_increment,
role_id int,
group_id int
);
通过修改表结构的方式添加外键
alter table t_rolegroup add constraint fk1 foreign key(role_id)references t_role(r_id);
alter table t_rolegroup add constraint fk2 foreign key(group_id)references t_group(g_id);
向角色表中添加测试数据
insert into t_role values(null,‘java程序员’,‘负责开发java程序’);
insert into t_role values(null,‘测试员’,‘负责测试程序’);
向项目组表中添加测试数据
insert into t_group values(null,‘CRM组’,‘负责开发CRM系统’);
insert into t_group values(null,‘ERP组’,‘负责开发ERP系统’);
向中间表中添加测试数据
insert into t_rolegroup values(null,1,1);
insert into t_rolegroup values(null,1,2);
insert into t_rolegroup values(null,2,1);
insert into t_rolegroup values(null,2,2);
子查询----一个查询语句中的查询条件又是一条查询语句
查询语句的嵌套
查询年龄小于平均年龄的学生信息
select avg(s_age)from t_student;
select * from t_student where s_age<25.5;
select * from t_student where s_age<(select avg(s_age)from t_student);
多表查询——连接查询
1.内连接查询 【inner join】
2.左外连接查询 【left outer join/left join】
3.右外连接查询 【right outer join/right join】
4.全连接查询【不解释】

语法格式
/*
select [col1,col2…coln来自多张表【最好使用别名】]
from table1
inner join/
left outer join/left join/
right outer join/right join
table2
on table.col=table2.col
where 查询条件
*/
inner join–根据用户姓名，得到用户的所有信息【用户基本信息和身份证信息】
select u_id,u_name,u_age,u_sex,u_address,card_id,
c_id,c_number,c_arg,c_year
from t_user
inner join
t_card
on card_id=c_id
where u_name=‘lisi’;
select u.u_id,u.u_name,u.u_age,u.u_sex,u.u_address,u.card_id,
c.c_id,c.c_number,c.c_arg,c.c_year
from t_user as u
inner join
t_card as c
on card_id=c_id
where u_name=‘lisi’;
#left outer join
查询班级名称是javaEE班的所有学生信息[班级信息]
select s_id,s_name,s_age,s_sex,s_address,
c_number,c_name
from t_student
left outer join
t_class
on class_id=c_id
where c_name=‘javaEE’;
#right outer join
查询学生姓名是lisi的所有信息[班级信息]
select s_id,s_name,s_age,s_sex,s_address,
c_number,c_name
from t_student
right outer join
t_class
on class_id=c_id
where s_name=‘lisi’;
内连接与左连接和右连接查询的区别
–内连接：显示左边右边共有的
–左连接：左边有的显示，右边没有的为null
–右连接：右边有的显示，左边没有的为null