一、DML语言
1、表添加数据
语法:INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …)
insert into `animal` (sID,sname,birthday,price) values (100,'阿福','2023-7-25',300);
insert into `animal` values (102,'Mytime','2022-4-20',306.6);
insert into `animal` (`sname`,`birthday`) values ('Https','2022-11-24');
多项同时添加
insert into `animal` (sname,birthday,price)values
('FTP','2001-1-1',200.1),
('DNF','2004-4-4',404),
('BUFF','2019-9-1',606.6);
2、表修改数据
语法:update 表名 SET column_name = value [ , column_name2 = value2, …. ] [ WHERE condition ];
update animal set price=1000 where sname='Mytime';
update animal set price=price+100 where sID=100;
UPDATE animal set birthday='2022-2-2' where sID between 102 and 105;
UPDATE animal set birthday='2019-1-12' where sID>100 and sID<=102;
update animal set sname='梦想',price=666 where sID=102;
3、表删除数据
语法:DELETE FROM 表名 [ WHERE condition ];
delete from animal where price>=600;
#语法:TRUNCATE [TABLE] table_name
truncate animal;
truncate 删除整个表内容,且无法找回,一般不使用
二、DQL语句
1、select查询表中数据
zero:语法结构
select 字段名1[,字段名2,...,字段名n]|* from 表名 [where ... group by... having...order by... limit...];
group by为查询时查询表中不重复的列
1>查询表中所有数据,使用*
SELECT * FROM `stu`;
2>查询表中的列(字段)
SELECT `sid`,`sname` FROM `stu`;
2、select条件查询
条件查询:
使用where对数据进行过滤,要结合运算符或者关键字一起使用
逻辑操作符:
比较操作符:
1> 例
#查询性别为女,并且年龄小于50的记录
SELECT * FROM `stu` WHERE age<50;
#查询学号为S_1001,或者姓名为liSi的记录
SELECT * FROM `stu` WHERE `sid`='S_1001' OR `sname`='lisi';
#查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM `stu` WHERE `sid`='S_1001' OR `sid`='S_1002' OR `sid`='S_1003';
#使用IN关键来来进行数据过滤
SELECT * FROM `stu` WHERE `sid` IN('S_1001','S_1002','S_1003');
#上面两条SQL语句写法不同,但是执行结果是一样的
2> 例
#查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM `stu` WHERE `sid` NOT IN('S_1001','S_1002','S_1003');
#另外一种方式实现
SELECT * FROM `stu` WHERE `sid`!='S_1001' AND `sid`!='S_1002' AND `sid`!='S_1003';
3> 查询空值
SELECT * FROM `stu` WHERE `age` IS NULL;
4> and和between
#查询年龄在20到40之间的学生记录
SELECT * FROM `stu` WHERE `age`>=20 AND `age`<=40;
#使用第二种方式实现
#BETWEEN num1 AND num2:获得num1到num2之间的数据,包含num1和num2本身
SELECT * FROM `stu` WHERE `age` BETWEEN 20 AND 40;
5> != 和not
#查询性别非男的学生记录
#方式一
SELECT * FROM `stu` WHERE `gender`='female';
#方式二
SELECT * FROM `stu` WHERE `gender`!='male';
#方式三
SELECT * FROM `stu` WHERE `gender`<>'male';
#方式四:注意:NOT关键字需要放在列名前面,否则报错
SELECT * FROM `stu` WHERE NOT `gender`='male';
#查询姓名为null的学生记录
SELECT * FROM `stu` WHERE `sname` IS NULL;
#查询姓名不为null的学生记录
SELECT * FROM `stu` WHERE NOT `sname` IS NULL;
SELECT * FROM `stu` WHERE `sname` IS NOT NULL;
#SELECT * FROM `stu` WHERE `sname` NOT IS NULL; -- 这是错误写法