格式1. 删除表格中所有记录.
delete from 表名;
格式2. 删除表格中满足where条件的记录
delete from 表名 where 条件;
案例:
1. 删除t_student表格中的所有数据
DELETE FROM t_student;
2. 按条件删除,
先给数据库加入一些模拟的数据
INSERT INTO t_student VALUES(1,'sg沛麟4',10,"2000-12-12","2006-09-01 08:30");
INSERT INTO t_student VALUES(2,'sg沛麟5',11,"2000-12-12","2016-09-01 08:30");
INSERT INTO t_student VALUES(3,'sg沛麟6',12,"2000-12-12","2018-09-01 08:30");
INSERT INTO t_student VALUES(4,'sg沛麟4',13,"2000-12-12","2006-09-01 08:30");
INSERT INTO t_student VALUES(5,'sg沛麟5',14,"2000-12-12","2016-09-01 08:30");
INSERT INTO t_student VALUES(6,'sg沛麟6',15,"2000-12-12","2018-09-01 08:30");
INSERT INTO t_student VALUES(7,'sg沛麟7',16,"2000-12-12","2011-09-01 08:30");
INSERT INTO t_student VALUES(8,'sg沛麟8',17,"2000-12-12","2009-09-01 08:30");
INSERT INTO t_student VALUES(9,'sg沛麟9',19,"2000-12-12","2008-08-08 08:30");
INSERT INTO t_student VALUES(10,'sg沛麟10',19,"2000-12-12","2025-09-01 08:30");
INSERT INTO t_student VALUES(11,'sg沛麟11',19,"2000-12-12","2015-09-01 08:30");
INSERT INTO t_student VALUES(12,'sg沛麟12',19,"2000-12-12","2014-09-01 08:30");
INSERT INTO t_student VALUES(13,'sg沛麟13',19,"2000-12-12","2013-09-01 08:30");
INSERT INTO t_student VALUES(14,'sg沛麟14',20,"2000-12-12","2012-09-01 08:30");
- 删除年龄等于19的学生;
delete from t_student where age=19;
- 删除年龄大于19岁的学生
delete from t_student where age>19;
- 删除年龄在15-19岁之间的 (不包含15和19)
delete from t_student where age>15 and age<19;
delete from t_student where age>=16 and age<=18;
delete from t_student where age between 16 and 18;
- 删除年龄为11,12,13的学生
delete from t_student where age=11 or age=12 or age=13;
delete from t_student where age in(11,12,13);
- 删除年龄不是11,12,13的学生
delete from t_student where age!=11 and age!=12 and age!=13;
delete from t_student where age not in(11,12,13);
- 删除所有入学学员 (intime 不是null)
delete from t_student where intime is not null;
- 删除所有未入学学员 (intime 是 null)
delete from t_student where intime is null;
练习: 删除年龄在10-15岁之间 , 且 id 大于10 或 小于5的学员
delete from t_student where age>10 and age<15 and (id>10 or id<5);
delete from t_student where age>10 and age<15 and id>10 or id<5;
where 条件 *
运算符:
1. > *
2. < *
3. >= *
4. <= *
5. = *
6. != *
7. in(元素1,元素2...元素n)
8. between...and
9. and *
10. or *
11. not
12. is null *: 在SQL语句中, null值的判断, 不能使用 xx=null 或 xx!=null .
如果需要判断为null的 , 使用 xx is null;
如果需要判断不为null的 , 使用 xx is not null
13. like 用于字符串的模糊查询 ,
包含一些占位符的语法:
1. _: 单个任意字符
2. %: n个任意字符
例如: 姓名第一个字为 "小"
where name like "小%";
姓名第二个字为 "麟"
where name like "_麟%";
姓名中包含 "嘿嘿"
where name like "%嘿嘿%";
可以使用小括号, 提高某部分运算的优先级.
修改数据 ***
语法1. 修改 表格中所有记录
update 表名 set 字段名1=字段新值,字段名2=字段新值...字段名n=字段新值;
语法2. 修改 满足where条件的记录
update 表名 set 字段名1=字段新值,字段名2=字段新值...字段名n=字段新值 where 条件;
练习:
1. 修改所有学生的入学日期为: 2008-10-1 08:00
update t_student set intime="2008-10-1 08:00";
2. 将id大于10的学生, 年龄统一修改为20
update t_student set age=20 where id>10;
3. 将生日在2008年以后的学生的 入学日期修改为 2019-10-1
update t_student set intime="2019-10-1" where birthday >= "2008-1-1 00:00";
4. 将姓名中包含6的学生 , 姓名更改为 dsb
update t_student set name="dsb" where name like "%6%";
5. 将所有姓s的学生 , 姓名更改为 哈哈哈
update t_student set name="哈哈哈" where name like "s%";