数据库基础知识三

创建表外键
--切记：作为外键一定要和关联主键的数据类型保持一致
create table student(
id int primary key auto_increment,
name varchar(20),
charger_id tinyint,
foreign key (charger_id) references table_name(id)
)engine=innodb

add constraint

增加/删除外键
alter table table_name2 add constraint abc ---'abc'为外键的别名
foreign key (field) references table_name(field1);

alter table table_name2 drop foreign key abc; --'abc'为外键的别名

innodb支持的四种方式

cascade方式 父表上update/delete,同步update/delete掉子表相匹配的记录
外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动删除
foreign key (field1) references table_name(field) on delete cascade

set null方式，在父表上update/ delete 记录时，将子表上相匹配记录的列设为null
注意子表的外键列不能为not null

foreign key(field) references table_name (field)
on delete set null

多表查询

内连接: inner join
select * from table_name1,table_name2;---笛卡尔查询 n*m行

select * from table_name1 inner join table_name2 where table_name1.field = table_name2.field1;

外连接--左连接 left join、右连接 right join

select * from table_name1 left join table_name2 where table_name1.field = table_name2.field1;

全连接 full join

多表查询--复合查询
子查询--嵌套查询 是将一个查询语句嵌套在另一个查询语句中
内层查询语句的查询结果，可以为外层的查询语句提供查询条件
子查询中可以包含IN ,NOT IN,ANY ALL ,EXISTS 和NOT EXISTS等关键字
比较运算符：< > = !=

查询员工表中 部门id在部门表中出现过的

select * from employee where dept_id in (select dept_id from department)

将表2复制到表1--创建表 嵌套
复制表--create table table_name1 (select * from table_name2)

带exists关键字的子查询
exists关键字表示存在，在使用exists关键字时，内层查询语句不返回查询的记录
而是返回一个真假值，true,false
当返回为true时，外层查询语句将进行查询;
当返回为false时，外层查询语句将不进行查询;

select * from employee where exists(select dept_id from department where dept_id=203);

索引index---在mysql中也叫键，是存储引擎中用于快速找到记录的一种数据结构。
索引对于良好的性能非常关键，创建与维护索引会很多时间和空间，但查询速度大大提高
索引语法：
创建普通索引--可以用index或key

name 加上唯一键--加上索引
alter table test1 modify name varchar(20) unique;

create table table_name(
字段名1，数据类型 [完整型约束条件]，
字段名1，数据类型 [完整型约束条件]，
[unique|fulltext(全局索引)|spatial（空间索引）] index|key
[索引名]（字段名（长度））[asc|desc]);

create table test1(
id int primary key auto_increment,
name varchar(20),
resume text，
INDEX （or key） index__name(name));

create table test1(
id int ,
name varchar(20),
resume text，
key index__name(name));
创建唯一索引

create table test1(
id int ,
name varchar(20) unique,
resume text，
unique index index__name(name));
创建全文索引

create table test1(
id int,
name varchar(20),
resume text，
fulltext index index__name(name));
创建多列索引示例
create table test1(
id int ,
name varchar(20),
resume text，
index index__resume_name(resume,name));

创建索引
在已存在表上创建索引
create |unique|fulltext|spatial|index 索引名 on 表名 （字段名（长度）[ASC|DESC]);

create index index__emp_name on emp(name);

alter table 在已存在表中修改索引
alter table table_name add |unique|fulltext|spatial|index 索引名 on 表名 （字段名（长度）[ASC|DESC]);

alter table emp add unique index index__emp_name (name);

删除索引
语法： DROP INDEX 索引名 on 表名;
drop index index__emp_name on emp;

存储过程
delimiter $$ --修改分隔符,以$$结束
delimiter ; --以;结束
示例：

delimiter $$
create procedure autoinsert() --autoinsert为函数名
BEGIN
declare i int default 1;
while (i<500000)do
insert into table_name (field) values(i,value);
set i=i+1;
end while;
END$$
delimiter ;

调用函数 --autoinsert为函数名
call autoinsert();

示例：

delimiter $$
create procedure autoinsert1()
BEGIN
declare i int default 1;
while (i<50000)do
INSERT INTO test.testa(name,product,number)VALUES("allo","haooa","12356");
set i=i+1;
end while;
END$$
delimiter ;
call autoinsert1();