MySQL 表增删改

MySQL 表的增删改

一、增加数据

INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  ( '值1', '值2', '值3', … )

① 字段或值之间用英文逗号隔开。

② “字段1, 字段2…”该部分可省略，但添加的值务必与表结构数据列顺序相对应,且数量一致。

③ 可同时插入多条数据，values 后用英文逗号隔开。

二、修改数据

UPDATE 表名 SET column_name = value [ , column_name2 = value2, … ] [ WHERE condition ];

① column_name 为要更改的数据列。

② value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果。

③ condition为筛选条件，如不指定则修改该表的所有列数据。

三、删除数据

DELETE FROM 表名 [ WHERE  condition ]; 

condition为筛选条件，如不指定则删除该表的所有列数据。

四、实战案例

create database if not exists `homework`;

use `homework`;
-- 创建学生信息表
create table if not exists `student`
(
    `sno` varchar(20) not null primary key comment '学号',
    `sname` varchar(20) not null comment '姓名',
    `ssex` varchar(20) not null comment '性别',
    `sbirthday` datetime comment '出生日期',
    `class` varchar(20) comment '班级'
);
-- 创建教师信息表
create table if not exists `teacher` (
    `tno` varchar(20) not null primary key comment '教师编号',
    `tname` varchar(20) not null comment '姓名',
    `tsex` varchar(20) not null comment '性别',
    `tbirthday` datetime comment '出生日期',
    `prof` varchar(20) comment '职称',
    `depart` varchar(20) not null comment '科系'
);
-- 创建课程表
create table if not exists `course`
(
    `cno` varchar(20) not null primary key comment '课程编号',
    `cname` varchar(20) not null comment '课程名称',
    `tno` varchar(20) not null comment '授课教师编号',
    constraint fk_course_tno foreign key (`tno`) references `teacher` (`tno`)
);
-- 创建成绩表score
create table if not exists `score` (
    `sno` varchar(20) not null comment '学生学号',
    `cno` varchar(20) not null comment '课程编号',
    `degree` numeric(4,1) comment '成绩',
    constraint fk_score_sno foreign key (`sno`) references `student` (`sno`),
    constraint fk_score_cno foreign key (`cno`) references `course` (`cno`)
);

-- 向学生student表添加数据
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 108, '曾华'
       , '男', '1977-07-01', '95033');
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 105, '匡明'
       , '男', '1975-10-02', '95031');
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 107, '王丽'
       , '女', '1976-01-23', '95033');
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 101, '李军'
       , '男', '1976-02-20', '95033');
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 109, '王芳'
       , '女', '1975-02-10', '95031');
INSERT INTO student (sno, sname, ssex, sbirthday, class)
VALUES ( 103, '陆君'
       , '男', '1974-06-03', '95031');

-- 向教师信息teacher表添加数据
insert into teacher(tno, tname, tsex, tbirthday, prof, depart)
values (804, '李成', '男', '1958-12-02', '副教授', '计算机系');
insert into teacher(tno, tname, tsex, tbirthday, prof, depart)
values (856, '张旭', '男', '1969-03-12', '讲师', '电子工程系');
insert into teacher(tno, tname, tsex, tbirthday, prof, depart)
values (825, '王萍', '女', '1972-05-05', '助教', '计算机系');
insert into teacher(tno, tname, tsex, tbirthday, prof, depart)
values (831, '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 向课程course表添加数据
insert into course(cno, cname, tno)
values ('3-105', '计算机导论', 825);
insert into course(cno, cname, tno)
values ('3-245', '操作系统', 804);
insert into course(cno, cname, tno)
values ('6-166', '数字电路', 856);
insert into course(cno, cname, tno)
values ('9-888', '高等数学', 831);

-- 向成绩score表添加数据
insert into score(sno, cno, degree)
values (103, '3-245', 86);
insert into score(sno, cno, degree)
values (105, '3-245', 75);
insert into score(sno, cno, degree)
values (109, '3-245', 68);
insert into score(sno, cno, degree)
values (103, '3-105', 92);
insert into score(sno, cno, degree)
values (105, '3-105', 88);
insert into score(sno, cno, degree)
values (109, '3-105', 76);
insert into score(sno, cno, degree)
values (101, '3-105', 64);
insert into score(sno, cno, degree)
values (107, '3-105', 91);
insert into score(sno, cno, degree)
values (108, '3-105', 78);
insert into score(sno, cno, degree)
values (101, '6-166', 85);
insert into score(sno, cno, degree)
values (107, '6-166', 79);
insert into score(sno, cno, degree)
values (108, '6-166', 81);

#----------------------------------------------------
-- 创建员工表
create table if not exists `staff`
(
    `id` int,
    `name` varchar(20),
    `gender` char(2),
    `birthday` date,
    `email` varchar(10),
    `remark` varchar(50)
);

-- 在员工表基础上增加age列
alter table `staff` add `age` int;
-- 修改email列长度为50
alter table `staff` modify `email` varchar(50);
-- 删除remark列
alter table `staff` drop `remark`;
-- 列名name修改为username
alter table `staff` change `name` `username` varchar(20);

insert into `staff`
values (1, '张三丰', '男', '1367-10-21', '12321222@qq.com', 102);
insert into `staff`
values (2, '达摩', '男', '1227-04-15', '1121213242@126.com', 54);
insert into `staff`
values (3, '梅超风', '女', '1547-06-01', '232442@163.com', 44);
insert into `staff`
values (4, '三体星人', '男', '3012-08-15', '12345678@fly.com', 2000);
insert into `staff`
values (5, '超级赛亚人', '男', '1985-02-03', 'whosyourdaday@sina.com', 25);

-- 将id为4的那一行的username改为“弗利萨”
update `staff` set `username` = '弗利萨' where id = 4;
-- 删除age为2000的那一行
delete from staff where `age` = 2000;
-- 将id为4的那一行的age改为250
update `staff` set `age` = 250 where id = 4;
-- 将id为5的那一行的username改为“孙悟空”，年龄改为26
update `staff` set `username` = '孙悟空', `age` = 250 where id = 5;