MySQL 详细单表增删改查crud语句

CREATE TABLE `user`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)',
	`username` VARCHAR(50) COMMENT '用户姓名', 
	`age` CHAR(3) COMMENT '用户年龄'
);

INSERT INTO USER VALUES(2,'小二',12);
INSERT INTO USER VALUES(3,'张三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'赵六',36);
INSERT INTO USER VALUES(7,'七七',18);

CREATE TABLE `students`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生id(主键)',
	`name` VARCHAR(10) COMMENT '学生姓名', 
	`grade` VARCHAR(10) COMMENT '学生年级',
	`chinese` INT COMMENT '语文成绩',
	`math` INT COMMENT '数学成绩',
	`english` INT COMMENT '英语成绩'
);

INSERT INTO students VALUES(1,'迪丽热巴','二年级',100,100,100);
INSERT INTO students VALUES(2,'古力娜扎','一年级',99,88,98);
INSERT INTO students VALUES(3,'马尔扎哈','三年级',46,20,99);
INSERT INTO students VALUES(4,'阿里巴巴','一年级',78,81,100);
INSERT INTO students VALUES(5,'哈哈哈哈','六年级',20,10,5);
INSERT INTO students VALUES(6,'作者本人','二年级',100,100,100);
INSERT INTO students VALUES(7,'嘻嘻哈哈','五年级',70,99,60);
INSERT INTO students VALUES(8,'哭哭啼啼','四年级',33,15,54);

SELECT * FROM USER;

SELECT * FROM USER WHERE age >= 18;

SELECT username FROM USER WHERE age  18;

SELECT u.* FROM USER AS u;

SELECT u.`username`,u.`age` FROM USER u WHERE u.`username`='小二'  u.`age`=12;

SELECT * FROM USER WHERE username='张三'  age  18;

SELECT s.chinese + s.math + s.english FROM students s WHERE s.name = '哈哈哈哈';

SELECT NAME FROM students WHERE chinese BETWEEN 60 AND 80;

SELECT NAME FROM students WHERE math NOT BETWEEN 60 AND 100;

SELECT s.`id`,s.`name` FROM students s WHERE !(s.`english` BETWEEN 0 AND 90);

SELECT * FROM students WHERE grade IN ('一年级','二年级');

SELECT * FROM students WHERE grade NOT IN ('一年级','二年级','三年级');

SELECT * FROM students WHERE `name` IS NULL;

where 列名 like '%巴'; 		-- 表示以 巴 结尾的;
where 列名 like '巴%'; 		-- 表示以 巴 开头的;
where 列名 like '%巴巴%' 	-- 表示数据里面包含 巴巴 的;

SELECT * FROM students WHERE `name` LIKE '%巴';

SELECT * FROM students WHERE `name` LIKE '作者%';

SELECT * FROM students WHERE `name` LIKE '%嘻哈%';

SELECT * FROM students WHERE `name` LIKE '%哈_';

SELECT * FROM students WHERE grade IN ('一年级','二年级','三年级') ORDER BY chinese DESC;

SELECT * FROM students LIMIT 0,3;

SELECT * FROM students LIMIT 3,3;

SELECT * FROM students LIMIT 6,3;

CREATE TABLE `city` (
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
	`city_name` VARCHAR(100) COMMENT '城市名',
	`city_year` VARCHAR(4) COMMENT '年份，yyyy',
	`city_gdp` DOUBLE COMMENT '当前年份，城市一整年的GDP，单位亿元',
	`city_population` DOUBLE COMMENT '当前年丰，城市的总人口，单位万人'
);

INSERT INTO city VALUES(1,'上海',2018,32679,2418);
INSERT INTO city VALUES(2,'北京',2018,30320,2171);
INSERT INTO city VALUES(3,'深圳',2018,24691,1253);
INSERT INTO city VALUES(4,'广州',2018,23000,1450);
INSERT INTO city VALUES(5,'重庆',2018,20363,3372);
INSERT INTO city VALUES(6,'上海',2019,38155,2424);
INSERT INTO city VALUES(7,'北京',2019,35371,2171);
INSERT INTO city VALUES(8,'深圳',2019,26927,1302);
INSERT INTO city VALUES(9,'广州',2019,23628,1491);
INSERT INTO city VALUES(10,'重庆',2019,23605,3372);

SELECT COUNT(*) FROM city WHERE city_year = '2019';

SELECT MAX(city_gdp) FROM city WHERE city_year = '2018';

SELECT MIN(city_gdp) FROM city WHERE city_year = '2018';

SELECT SUM(city_gdp) FROM city WHERE city_year = '2019';

SELECT AVG(city_gdp) FROM city WHERE city_year = '2019';

INSERT INTO students(id,NAME,grade,chinese,math,english) 
VALUES( 9,'鸡鸡鸡鸡','七年级',77,77,77);

INSERT INTO 表名(字段1,字段2,字段3) VALUES (值1,值2,值3),(值1,值2,值3); -- 插入多条数据
INSERT INTO 表名 VALUES(值1,值2); -- 针对表全字段进行插入操作
INSERT INTO 表名(字段) SELECT 字段 FROM 表2; -- 查询结果插入
INSERT INTO 表名 SELECT 字段 FROM 表2; -- 查询结果，全表插入

update 表名 set 字段=值 where 条件; -- 带条件的去修改指定数据，否则修改全表；

UPDATE students SET english = 10 WHERE `name` = '哈哈哈哈';

delete from 表名 where 条件; -- 删除数据带条件删除，否则删除全表的数据

DELETE FROM students WHERE `name` = '鸡鸡鸡鸡';