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` = '雞雞雞雞';