数据增删改查

1、外键

1. 主键:PRIMARY KEY (列名) 唯⼀性,不能重复。
2. 外键的作⽤是要和另⼀个表的主键保持数据⼀致性,完整性。

3. -- 创建一个班级表
   create table if not exists grade(
   id int primary key,
   gradeName varchar(20)
   );
   
   -- 创建一个学生表
   create table if not exists student(
   id int primary key,
   name varchar(20),
   gradeId int 
   );
   
   -- 创建外键约束
   -- 1、创建表时指定外键约束
   create table if not exists student2(
   id int primary key,
   name varchar(20),
   gradeId int,
   constraint grade_id foreign key (gradeId) references `grade`(id)
   );
   -- 2、创建表后再指定外键约束
   alter table student
   add constraint grade1_id foreign key (gradeId) references `grade`(id);

    

2、插⼊数据

-- 插入数据
insert into student values (4,"李四",2);
insert into student values (5,"王五",2);
insert into student values (6,"五六",2);
insert into student values (7,"王五六",1);
insert into student(id,gradeId) values (8,3),
(9,1),
(10,1);
insert into grade values (4,"大数据123");
insert into student values (11,"未文选",4),
(12,"邱江涛",4);

3、修改数据

-- 修改数据或更新数据
update student set name="王五三" where id = 8;
-- 修改王七的班级id为2
update student set gradeId=2 where name="王七";
-- 把班级id为2的王五姓名修改为王老五
update student set name ="王老五"where gradeId=2 and name ="王五";

4、删除数据

1. 

2.  

   -- 删除数据
   delete from student where name is null;
   delete from student where name="";
   delete from student;
   -- truncate table 表名
   -- 删除班级表中的所有数据
   truncate table grade; -- 有引用的外键不能删除
   delete from grade; -- 有引用的外键不能删除
   -- 删除学生表中的所有数据
   truncate table student;
   delete from  student;
   truncate table student2;
   insert into student2(name,gradeId) values
   (8,2),
   (9,1),
   (7,4);

5、查询

-- 查询
-- 查询学生表中的所有记录
select * from student;
select id,name,gradeId from student;
-- 查询班级表中的所有记录
select * from grade;
-- 查询指定的字段
-- 查询所有的学生姓名
select name from student;
select name as 姓名 from student; -- as 给列起别名 as可以省略
select name as "姓名" from student;
select name 姓名 from student;
select id 学生编号,name 姓名,gradeId 班级编号 from student 学生表;
-- distinct 去除重复的记录
select distinct gradeId from student;
-- all 默认的，显示所有记录
select gradeId from student;
select all gradeId from student;