-- 数据操纵语言DML（update, insert ,delete）-- 1.插入部分列
INSERT INTO exam(id,NAME,English,chinese,math) VALUE( 1,'xucheng',90 ,90,90);
INSERT INTO exam(id,NAME,English,chinese) VALUE(NULL,'zhangshan',91,91);INSERT INTO score(id,NAME,English,chinese) VALUE(NULL,'李帅',45,60);
-- 2插入所有列
INSERT INTO exam VALUES(2,'小弟',90,100,100);
-- 3、修改记录
UPDATE exam SET chinese = 99; -- 全表修改
UPDATE exam SET math = 100 WHERE id ='1';
-- 4.删除记录
DELETE FROM exam WHERE id = '2';
DELETE FROM exam; -- 全表删除-- delete 与 truncate的区别
-- truncate table 删除表的记录：将整个表删除掉。重新创建一个新的表输入DDL 
-- delete form删除表的记录：一条一条进行删除，delete.CREATE  TABLE score(id INT(11) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),English INT,chinese INT,math INT
);INSERT INTO exame VALUES(3,'成',90,90,90);
INSERT INTO exame VALUES(4,'武',90,90,35);
DELETE FROM exame;
INSERT INTO exame VALUES( NULL ,'李太白',10,10,10);
TRUNCATE table exame;-- 事务管理：只能作用在DML语句上，如果在一个事务中用delete删除所有记录，可以找回
-- 使用delete 删除后可以用commit 和 rollback 找回数据，使用truncate后就找不回来了。-- delete 、 truncate、 drop 的区别
DELETE 、truncate \DROP 的区别
DELETE 、truncate \只是删除表的记录。而drop会直接删除表、USE mybase;
CREATE TABLE (id INT(11) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),english int,chinese  INT,math int
);
RENAME TABLE exam TO exam1;INSERT INTO exam1 values(NULL,'小白',80, 80, 80);
INSERT INTO exam1 VALUES(NULL,'小蓝',89,89,19);
INSERT INTO exam1 VALUES(NULL,'小绿',90,89,91);-- 1.全表查询 select * from 表；
SELECT * FROM exam1;
-- 2. 查询部分字段：select 字段，字段，字段.... from 表；
SELECT NAME , english, math FROM exam1; 
-- 3.过滤重复字段行。select [distinct] * |列名 from 表；
SELECT DISTINCT math FROM exam1;
SELECT DISTINCT NAME,math,form exam1;
-- 4、查询字段起别名。select 字段，as 新字段名，字段 新字段名 from 表；
SELECT NAME,english AS my_english FROM exam1;
-- 5,查询指定字段
SELECT NAME,english,chinese FROM exam1 WHERE NAME='李白';
-- 6.使用表达式+-*/
SELECT id,NAME,english-20 AS _english FROM exam1;
SELECT NAME,english+chinese+math FROM exam1;
-- 7.模糊查询
SELECT * FROM exam1 WHERE NAME LIKE '小_';
SELECT * FROM exam1 WHERE NAME LIKE '%%';
-- 8.使用and. or
SELECT * FROM exam1 WHERE english > 90 AND chinese > 90;
SELECT * FROM exam1 WHERE engilsh > 90 OR math >90;
-- 9.使用in. not in
SELECT * FROM exam1 WHERE id = 2 OR id = 3 OR id = 4;
SELECT * FROM exam1 WHERE id IN(2,3,4);
SELECT * FROM exam1 WHERE id NOT in (2,3,4);
-- 10使用between ...and []
SELECT * FROM exam WHERE english BETWEEN 90 AND 100;
-- 11，is null,is not null
INSERT INTO exam1(id, NAME ) VALUES(NULL,NULL); 
SELECT * FROM exam1 WHERE NAME IS NULL;
SELECT * FROM exam1 WHERE NAME IS NOT NULL;
-- 12, 排序查询
SELECT * FROM exam1 ORDER BY chinese ASC;
SELECT * FROM exam1 ORDER BY chinese DESC;
SELECT * FROM exam1 ORDER BY english DESC,chinese DESC;-- 如果英语成绩相同，按照汉语成绩降序排列SELECT * FROM exam1 WHERE NAME LIKE '小%' ORDER BY english ASC ;
-- 12。聚合函数
SELECT SUM(english+math+chinese) FROM exam;
SELECT COUNT(id) FROM exam1 WHERE NAME IS NOT NULL;
SELECT MAX(english) FROM exam1;
SELECT MIN(english) FROM exam1;
SELECT AVG(english) FROM exam1;