基础操作DML

 

 

 

 

 

 

 

 

-- 数据插入
-- 格式1
insert into student(sid,name,gender,age,birth,address,score)
values(1001,'张三','男',18,'2001-12-23','北京',85.5),
(1002,'张三san','男',18,'2001-12-23','北京',85.5);

-- 格式2
insert into student values(1008,'张华si','woman',21,'1999-01-08','广州',79),
(1007,'张华hua','woman',21,'1999-01-08','广州',79);

 

 

 

-- 数据修改
-- 格式1
UPDATE student set address='重庆';#把所有学生地址修改为重庆

-- 格式2
UPDATE student set address='北京' where sid = 1004;#id=1005学生地址修改为北京
UPDATE student set address='北京' where sid > 1004;#id>1005学生地址修改为北京

UPDATE student set address='东京',score = 99 where sid = 1004;#id=1004学生地址修改为东京，成绩改为99

 

 

-- 删除数据
DELETE from student where sid=1004;#删除sid=1004的学生数据
delete from student;#删除表所有数据
TRUNCATE table student;#将整个表删除在创建一个名字一样的表

练习

CREATE table if not EXISTS mydb1.employee(
id int,
name VARCHAR(20),
gender VARCHAR(10),
salary double
);#可以以数据库.表方式代替use
insert into employee(id,name,gender,salary) VALUES(1,'张三','男',2000);
insert into employee VALUES(2,'张三er','男',2000),(3,'张s','男',2200); ;
UPDATE employee set salary=5000;
UPDATE employee set salary=3000 where name='张三';
UPDATE employee set salary=4000,gender='女' where name='张三er';
UPDATE employee set salary=salary+1000 where name='张三s';#将张三s薪水在原有基础+1000