MySQL增删改查基础

表格

create table exam(
   id int primary key auto_increment,
   name varchar(20) not null,
   chinese double,
   math double,
   english double
); 
insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);
insert into exam values(null,'刘备',null,55,38);

对表格增删改查

-- 对数据的操作
-- 新增
INSERT INTO exam(name,chinese,math,english) VALUES('曹操',85,80,60)

-- 如果为表中的每一列都插入值，列名可以省略不写
INSERT INTO exam VALUES(null,'张角',90,90,90)

-- 删除
-- WHERE 条件语句，后面跟具体的条件，使用and连接
-- DELETE FROM exam WHERE id = 7	删除id为7的数据
DELETE FROM exam WHERE id = 2
DELETE FROM exam WHERE name = '张角' AND chinese = 90
-- 删除全部数据，不可恢复
TRUNCATE exam;

-- 修改密码
-- UPDATE exam SET chinese = 90	将所有的语文成绩都改成90
UPDATE exam SET chinese = 90
-- 修改关羽的语文成绩为20
UPDATE exam SET chinese = 20 WHERE name = '关羽'
-- 修改赵云的语文成绩为100，数学成绩为100
UPDATE exam SET chinese = 100,math = 100
WHERE name = '赵云'

-- 查询
-- SELECT 列
-- FROM 表格
-- WHERE 条件，不写where表示查询所有
-- 查询exam表格中所有的姓名和中文成绩
SELECT name,chinese
FROM exam

-- 查询exam表格中所有语文成绩和数学成绩大于60的人
SELECT name,chinese,math
FROM exam
WHERE chinese>60 and math>60