mysql 07.11

简单的增删改查语句

insert into table_name values(v1,v2,...);
insert into table_name(column1,column2)values(v1,v2);
insert into table_name values(v1,...),(v1,...)

delete from table_name where 条件：

update table_name set column = new_valuem,...where 条件

select * from table_name;
select column,...from table_name where 条件:

数据类型

1.整型：
tyniint smallint mediumint int bigint
2.浮点
float double decimal
3.字符串
char varchar tiny|long [text blob] binary varbinary
枚举 enum
集合 set
4.日期和时间
date time datetime year timestamp

1.约束

约束：一种限制；

数据库的约束，是对数据的安全性，完整性的保证；

mysql中的约束：
unique ：唯一性约束 表示这个字段不能出现重复的值，
not null ：非空约束 表示这个字段的值不能为空
null : 
default ：默认值
primary key:主键约束 非空+唯一
foreign key: 外键约束，用于指向另一个表的主键字段

#完整的建表语句
create table table_name(字段名称 字段类型[(宽度) 约束]) charset utf8;

create table blog(
    name char(20) not null,
    gender enum("g","b") default "b",
    id int  unique）；
    
insert into student values("jack",null,null);
#默认值可以被覆盖，   
insert into student(name,id) values("jack",null);

alter table student modify id int unique not null;

primary key

主键与普通约束的区别：
create table person(
    id char(19) primary key,
	name char(20)
);

mysql> insert into person values('1','rose');
Query OK, 1 row affected (0.08 sec)

mysql> insert into person values('1','jack');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into person values('2','jack');
Query OK, 1 row affected (0.07 sec)

mysql> insert into person values(null,'tom');
ERROR 1048 (23000): Column 'id' cannot be null

#从约束角度来看就等同于,非空+唯一

#在innodb 存储引擎中，主键用于组织数据，主键是必须存在的，且非空和唯一
相当于一种索引，加速查找。

设置自动增长

create table teacher(
	id int primary key auto_increment,
	name char(10));
	
insert into teacher values(null,'jack');
insert into teacher(name) values('jack');

#主要用在具备索引且是数字类型的字段上，通常与主键一起使用；

foreign key:外键约束，用于指向另一个表的主键字段

create table teacher(
	id int primary key auto_increment,
	name char(20),
	gender char(1),
	dept_id int,
	foreign key(dept_id) reference dept(id));
	
dept_id 表示要关联那个表
dept 表示要关联那个表
dept(id) id表示关联的dept表的id字段

现有主表 后有从表 

foreign key 带来的约束作用；
1.在从表中插入一条记录，关联了一个主表中不存在的id，导致插入失败，
必须保证外键的值在主表中是存在的
2 插入数据的顺序，
先插入主表记录，在插入从表记录
3.从表更新外键时也必须保证外键的值在主表中是存在的
4.删除主表记录时 要保证从表中没有外键关联被删除的id
否则先删除从表，在删除主表
5.更新主表记录的主键时 要保证从表中没有外键关联被删除的id
6。必须先创建主表
7.删除表先删除从表

mysql> create table class(
    -> id int primary key auto_increment,
    -> name char(10));
Query OK, 0 rows affected (0.25 sec)

mysql> create table student(
    -> id int primary key auto_increment,
    -> name char(10),
    -> gender enum('boy','girl') default 'boy',
    -> c_id int,
    -> foreign key(c_id) references class(id));
Query OK, 0 rows affected (0.33 sec)

级联操作 cascade

1.级联的删除
当删除主表时自动删除从表中相关数据
2.级联更新
当主表的主键跟新时自动跟新关联的从表数据

drop table if exists class;
# 如果这表存在 才执行删除  可以避免报错  if exists
# if not exists  如果不存在才执行
create table class(
        id int primary key auto_increment,
        name char(20)
	);
insert into class values(null,"py9");
insert into class values(null,"py10");

#创建表的时候指定级联操作 
drop table if exists student;
create table student(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        c_id int,
    	foreign key(c_id) references class(id)
         on update cascade
   	 	on delete cascade
	);
# 级联操作可以单独使用 也可以一起使用   空格隔开即可 
insert into student values(null,"jack","m",1);
insert into student values(null,"rose","m",1);
insert into student values(null,"tom","m",2);

外键的使用

#多对一
#部门:
create table dept(
	id int primary key auto_increment,
	name char(20),
	job char(50),
	manager char(10)
);
#老师表:
create table teacher(
	id int primary key auto_increment,
    name char(20),
    gender char(1),
    dept_id int,
    foreign key(dept_id) references dept(id),
);

#多对多
建立一个中间表,用于存储关系,至少具备两个字段分别指向老师和学生的主键,两个字段都是外键  如下:
create table student(id int primary key auto_increment,name char(10));
create table teacher(id int primary key auto_increment,name char(10)); 
create table t_s_r(
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
    );
 # 先插入 学生或是老师都可以 但是关系表一定是最后添加的 
 insert into teacher values(null,"bgon"),(null,"nike");
 
 insert into student values(null,"老王"),(null,"老李");
  
  # 老王被bgon教过 
  insert into t_s_r values(1,1);
   # nike教过老李 
  insert into t_s_r values(2,2);
   # nike教过老王 
  insert into t_s_r values(2,1);

 已知老师名称为bgon 请找出他教过那些学生			
1.通过名字获取 bgon的id 			
2.拿着id取关系表中拿到一堆学生的id			
3.通过学生的id取出学生的信息			
 select id from teacher where name = "bgon";
 select s_id from t_s_r where t_id = 1;
 select  * from student where id = 1;
 
 # 子查询方式   把一条语句的结果作为另一条语句的条件!
 select  * from student where id = (select s_id from t_s_r where t_id = (select id from teacher where name = "bgon"));
    
 已知学生名为老李 请查询出 哪些老师教过他  				
1.通过名字获取老李的id
select id from student where name = "老李";
2.拿着id去关系表 找出老师的id				
select t_id from t_s_r where s_id = 2;
3.通过老师的id取出老师的信息 			
select name from teacher where id = x;

# 子查询方式:
select name from teacher where id = (
    select t_id from t_s_r where s_id = (
        select id from student where name = "老李"
    )
);

#### 处理方式, 

​确定先后顺序
​将先存在的数据作为主表 
​后存在的作为从表
​使两个表的id保持一一对应
​方法1: 从表的id 即是主键又是外键 
​方法2:从表的id设置为外键 并保证唯一

# 人员表
create table person(
	id int primary key auto_increment,
    name char(10),
    age int
);
# 详情表 
create table person_info(
	id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);
#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据  

#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!