mysql外键约束

１　外键约束
１定义
让当前表字段的值　在另一个表的范围内选择

删  同步  ，改增 互不关联。
２　语法
foreign key(参考　字段名）references  被参考表名（被参考字段名）
on delete 级联动作（cascade）
on update　级联动作（cascade)
3使用规则
１　两张表　被参考字段和参考字段数据类型　要一直
２　被参考字段必须是key 的一种，通常primary　key
示例
表１　缴费信息表（财务）
学号    　 姓名　　    班级　           缴费金额
１　   唐伯虎　　　　　 aid171128000
2 秋香aid1711 20000
表２　学生信息表（班主任）
学号    　 姓名　　    　缴费金额
１　    唐伯虎　　 28000
2 秋香20000
创建表１　主表
１缴费信息表
create table jiaofei(id int primary key,name char(20),class char(25),
money int)default charset= utf8;
insert into jiaofei values(1,'唐伯虎','aid1711',20000),(2,'秋香','aid1711',28000),(3,'祝枝山','aid1711',25000);
创建表２　从表
create table jiaofeif(stu_id int,name char(20),money int,foreign key(stu_id)references jiaofei(id)on delete cascade on update cascade)default charset=utf8;
insert into jiaofeif values(1,'唐伯虎',20000),(2,'秋香',28000);
delete from jiaofei where name='唐伯虎';
select* from jiaofei;
select* from jiaofeif;


#级联操作
update jiaofei set money=30000 where name='秋香';
select*from jiaofei;

update jiaofei set id=4 where name ='秋香'; 　　　# 关联ｉｄ　就改了
注意：
１删除同步
删除主表中记录时，从表中记录会同步删除
２修改　同步（update）
insert into jiaofeif values(6,'文征明',18000); #报错
只针对　与　修改主表　被参照字段时　，从　表参照字段才会级联更新
３　在从表中　插入　记录时，如果参照字段的值　没有在主表被参照字段中，则不允许　插入
２　删除外键约束
语法：
alter table 表名 drop foreign key 外键名;
注意：
1 外键名　的查看方式：
show create table 表名;
show create table jiaofeif;


alter table jiaofeif drop foreign key jiaofeif_ibfk_1;


insert into jiaofeif values(100,'张飞',50000);




３　在已有表中添加外键；
alter table 表名 add foreign key(参照字段名)references 表名(被参照字段名)on delete cascade on update cascade;
alter table jiaofeif add foreign key(stu_id) references jiaofei(id)on delete cascade on update cascade;


aleter table jiaofeif drop where name='张飞';
delete from jiaofeif  where name='张飞';


注意：　在已有表中添加外键时，会受到　原有数据的限制




３　数据导入
１作用
把文件　系统的内容　导入到数据库中
２语法
load data infile '文件名'
into table 表名
fieids terminated by '分隔符'

lines terminated by '\n'
示例：
把　/etc/passwd 文件中的内容导入数据库db3　下的userinfo 表中
用户名　密码　ｕｉｄ　ｇｉｄ
tarena:x:1000:1000:
用户描述　主目录　　　　登录权限


tarena,,,:/home/tarena:/bin/bash

操作步骤：
１　在数据中创建对应的表
create table userinfo(username char(20),passwd char(1),uid int,gid int,comment varchar(50),homedir varchar(50),shell varchar(50));
２　将要导入的文件拷贝到数据库的默认搜索路径
1 show variables like 'secure_file_priv';# 查看数据库默认搜索路径
/var/lib/mysql-files/ 
show variales like  'sec%iv';
2   cp 文件 路径
sudo cp /etc/passwd /var/lib/mysql-files/ #复制
load data infile'/var/lib/mysql-files/passwd'
into table userinfo
fields terminated by ':'
lines terminated by '\n';
３　将系统文件导入到创建的表中




vi
a 光标　的左边开始
ｏ


ｙｙ复制　ｐ粘贴
ｄｄ删除
ｗ保存

４数据导出
１作用
将数据库中表的记录保存到系统文件里
２语法
select ...from 表名
into out file '文件名'
fields terminated by'分隔符'
lines terminated by'分隔符';
select username,passwd,uid from userinfo
into outfile'/var/lib/mysql-files/userinfo.txt'
fields terminated by' '
lines terminated by'\n';
sudo -i 
cd /var/lib/mysql-files
ls
cat userinfo.txt
exit
注意：
１导出的内容由　sql查询语句决定
２　执行导出命令时路径必须指定在对应的数据库目录下
练习:
将　ｍｙｓｑｌ　库下的user　表中的　user,host 两个字段的值导出到
user2.txt,将其存放在　数据库目录下
select user,host from mysql.user
into outfile'/var/lib/mysql-files/user2.txt'
fields terminated by' '
lines terminated by'\n';






5　嵌套查询
１定义
把内层的查询结果作为外层查询的条件
２语法
sql　查询语句　where 条件　(sql查询语句);
示例（利用 userinfo表操作）
１　把　uid 的值　小于　这个字段的平均值的用户名和ｕｉｄ　显示出来


select uid ,username from userinfo where uid < (select avg(uid)from userinfo);  


2　查找　userinfo 表中　username 的值在mysql　库下的user　表中host 字段值为localhostd user 字段的值　的用户名　
select username from userinfo where username in (select user from mysql.user where host='localhost' and user='root');
select username from userinfo where username in (select user form mysql.user where host='localhost' and user='root');


6　表的复制
１表的复制
１语法
create table 表名　select 查询命令；
示例：
１　复制userinfo　表的全部记录和字段
create table userinfo2 select *from userinfo;　
select *from userinfo;
注意：
复制表的时候不会把原有表的键属性复制过来
2 复制userinfo表的　前１０条记录，userinfo3
create table userinfo3 select *from userinfo limit10;


3 复制userinfo 表的username,password，uid三个字段的第２－１０条记录
create table useinfo4 select username,passwd,uid from userinfo limit 1,9;


２　复制表结构
１　语法
create table 表名 select 查询命令 where false;
示例：

１复制一下　userinfo 表的表结构，userinfo5
create table userinfo5 select *from userinfo where false;

练习　＋　环境准备：
１　创建ｔ１表，包含userinfo表中的　username,uid,shell三个字段的前２条记录create table t1 select username,uid,shell from userinfo limit 2;


２　创建ｔ２表,包含userinfo表中的username，uid,gid,homedir四个字段的前３条记录　
create table t2 select username,uid,gid,homedir from userinfo limit 3;

７多表查询
１两种方式
１语法
select 字段名列表　from　表名列表；＃ 笛卡尔集
1 select * from t3,t4;
2 select t4.username,t4.uid,t3.username from t4,t3;

select username ,uid from t3;
select username from t4;
２select 字段名列表　from 表名列表　where 条件;
示例：
１　找到t3 表　和　t4　表中　相同的用户名，全部显示信息
select *  from t3,t4 where t3.username=t4.username;


2 找到　ｔ３表　和t 4 表　相同的uid 号　把两张表 uid　和　username　都显示出来
select t3.uid,t3.username,t4.uid,t4.username from t3,t4 where t3.uid=t4.uid; 

７　连接查询
１创建两张表
１create table t7 select username,uid from userinfo limit 2;
2 create table t8 select username,uid from userinfo limit 3;
２　左连接
１定义　
以左边的　表为主显示查询结果
２语法
select 字段名 from 表１　left join 表２ on 条件；
示例：
ｔ7 t8 表中username　相同的记录显示出来,以　t3为主
select * from t7 left join t8 on t7.username=t8.username; 
insert into t7 values('mysql',888),('python',666); # 显示结果　依左
select * from t7 left join t8 on t7.username=t8.username; 
select *from t7 right join t8 on t7.username=t8.username;
select *from t8 left join t7 on t8.username=t7.username;
3右连接


ｃｈａｒ　定长　浪费存储空间，性能强　快
ｖａｒｃｈａｒ　变长　，相对于ｃｈａｒ来说节省存储空间，但是性能低　慢




char(n)是定长格式，格式为char(n)的字段固定占用n个字符宽度，如果实际存放的数据长度超过n将被截取多出部分，如果长度小于n就用空字符填充。


varchar(n)是变长格式，这种格式的字段根据实际数据长度分配空间，不浪费对于的空间，但是搜索数据的速度会麻烦一点。


一般地说，只要一个表有一个字段定义为varchar(n)类型，那么其余用char(n)定义的字段实际上也是varchar(n)类型。


如果你的长度本身不长，比如就3～10个字符，那么使用char(n)格式效率比较高，搜索速度快。但是如果有的数据很长，有的数据有比较短，比如注册用户的简介这样的字段，实在没有办法，而且很在乎浪费的空间，那么就用varchar(n)格式。