MySQL数据库学习(三)

一、 select语句

  1.查询所有

select * from  t_user;

  2.查询指定列

select user_name,user_sex from  t_user;

  3. 基本条件查询 【= or and 】

select * from  t_user where user_id=11;
select * from  t_user where user_id=11 or user_sex=false;
select * from  t_user where user_name='lisi' and user_sex=false;

  4.比较运算符查询

select * from  t_user where user_height < 170.5;
select * from  t_user where user_height < 170.5 and user_sex=false;

  5.区间查询 between 最小值 and 最大值

select * from  t_user where user_height  between 160.5 and 170.5;
select * from  t_user where user_height >= 160.5 and user_height <= 170.5;

  6.in(集合)条件查询

select * from  t_user where user_id in(1,3,5,7,9,11,13);

  7.模糊查询

select * from  t_user where user_name like 'zh%';
select * from  t_user where user_name like '%i';
select * from  t_user where user_name like '%s%';

  8.is null

select * from  t_user where user_height is null;

  9 not

select * from  t_user where  not(user_height > 170.5);
select * from  t_user where user_height is not null;

  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(uage) from t_user;
#max(列名) --  求最大值
select max(uage) from t_user;
#min(列名) --  求最小值
select min(uheight) from t_user;

  13.分页查询 limit 参数1 , 参数2

#参数1---当前页的第一条记录的开始位置[(当前页码-1)*每页记录数]
#参数2---每页记录数
#共有8条记录，每页显示3条记录
#第1页
select * from t_user limit 0,3;
#第2页
select * from t_user limit 3,3;
#第3页
select * from t_user limit 6,3;

  14.分组查询 group by 列名[先分组，后查询统计]

select uage,count(uage) from t_user group by uage;
select usex,count(usex) from t_user group by usex;

  15 别名查询

#数据库表可以有别名  【表名称  as  表的别名 / 表名称 表的别名】
#例如: t_user as 用户表 /   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;
#别名通常情况下会在多表查询中区分名称相同的列。

二、多表查询

2.1 外键的定义

  foreign key 外键
  创建表的时候：foreign key 表名(列名) references 表名(列名)
  通过修改表结构的方式添加外键
  alter table 表名 add constraint fk1 foreign key (列名) references 表名(列名);

2.2 表与表之间的关系
  一对一
  一对一主键关联关系【主键关联主键，主键及是主键也是外键】

#创建用户信息表
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,'123456789012345678','陕西.西安',10);
insert  into t_card  values(null,'098765432112345678','陕西.铜川',10);
#向用户信息表中添加测试数据
insert  into t_user values(null,'zhangsan',23,true,'长安县',1);
insert  into t_user values(null,'lisi',24,false,'雁塔区',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,'J20180903','javaEE');
insert  into  t_class values(null,'A20181010','Android');
insert  into  t_class values(null,'I20181111','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);

2.3 子查询

  #子查询----一个查询语句中的查询条件又是一条查询语句
  #查询语句的嵌套
  #查询年龄小于平均年龄的学生信息

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

2.4 联合查询

  内连接查询 【inner join】
  左连接查询 【left join】 左外连接查询 【left outer join】
  右连接查询 【right join】 右外连接查询 【right outer join】

#语法格式
/*
select  [col1,col2...coln来自多张表【最好使用别名】]
from table1 
inner  join/
left  outer join/left   join/
right outer join/right  join
table2 
on table1.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 u.card_id=c.c_id 
       where u.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