sql server 外键 语法 sql外键语句

MySQL数据库(SQL语句2)

一、多表查询

1.外键的定义

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

2.表与表之间的关系
一对一
一对一主键关联关系【主键关联主键，主键及是主键也是外键】
创建用户信息表

create table t_users(
 u_id int primary key auto_increment,
 u_name varchar(20),
 u_age int,
 u_sex bit,
 u_address varchar(30),
 foreign key t_users(u_id) references t_card(c_id)
 );

创建用户身份证信息表

create table t_card(
 c_id int primary key auto_increment,
 c_number varchar(20),
 c_arg varchar(18),
 c_year int
 );

一对一外键关联关系【专门创建外键列，外键列关联到主键列】

创建用户信息表2

create table t_users2(
 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_user2(card_id) references t_card(c_id)
 );

创建用户身份证信息表

create table t_card2(
 c_id int primary key auto_increment,
 c_number varchar(18),
 c_arg varchar(10),
 c_year int
 );

向用户身份证信息表中添加数据

insert into t_card values(null,‘123456789’,‘西安’,10);
 insert into t_card values(null,‘456789123’,‘咸阳’,10);
 insert into t_card values(null,‘987654321’,‘渭南’,10);

向用户信息表中添加测试数据

insert into t_users values(null,‘张三’,23,true,‘长安县’);
 insert into t_users values(null,‘李四’,24,false,‘雁塔区’);
 insert into t_users values(null,‘王五’,25,true,‘碑林区’);

一对多 【外键的建立要放在多方表中维护】
创建一个班级表

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

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

4.多表查询——连接查询
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 table1.col = table2.col
 where 查询条件

内连接查询 inner join–根据用户姓名，得到用户的所有信息【用户基本信息和身份证信息】

select u_id,u_name,u_age,u_sex,u_address,
 c_id,c_number,c_arg,c_year
 from t_users
 inner join
 t_card
 on u_id=c_id
 where u_name=‘李四’;

别名区分

select u.u_id,u.u_name,u.u_age,u.u_sex,u.u_address,
 c.c_id,c.c_number,c.c_arg,c.c_year
 from t_users as u
 inner join
 t_card as c
 on u.u_id=c.c_id
 where u.u_name=‘李四’;

左外连接查询 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