常用SQL语句

ALTER TABLE students ADD birthday DATE;	-- 添加表字段
DESC students;

ALTER TABLE students MODIFY birthday DATE DEFAULT "1990-01-01";	-- 修改表字段 指定默认值 此时 不修改字段名称

ALTER TABLE students CHANGE birthday birth DATE DEFAULT "1990-01-01";	-- 修改表字段 修改表字段名称

ALTER TABLE students DROP hight;	-- 删除表hight字段

INSERT INTO students VALUES(1,"ryan",20,"男",1,"2000-01-01");	-- 插入整行数据 不管是默认值 还是 可以为空 都要写

INSERT INTO students (NAME,cls_id) VALUES ("amy",2);	-- 指定字段 插入值

INSERT INTO students (NAME) VALUES ("morisllk");	-- 注意：插入语句字段需要用()进行包裹

INSERT INTO students (NAME,gender) VALUES ("amy",1);	-- 注意：枚举类型 可以通过下标进行取值 男：1 女：2 其次：数值不存在则会报错

INSERT INTO students (NAME,gender) VALUES ("maning",2),("sige",2);	-- 插入多行语句

UPDATE students SET gender="男" WHERE NAME="sige";	-- 将姓名为sige的性别变为男性

UPDATE students SET cls_id=2,gender="男" WHERE id=5;	-- 将id为5这行 的班级 设置为2 性别为男

DELETE FROM students WHERE id=1;  -- 物理删除 真正意义上的删除数据

-- 逻辑删除 is_del 0 未删除  1 已删除

ALTER TABLE students ADD `is_del` INT DEFAULT 0; -- 1.添加字段

UPDATE students SET `is_del`=1 WHERE id=4;	-- 2.逻辑删除 

SELECT * FROM students;		-- 查询整张表的数据 *代表所有字段 *

SELECT NAME,gender FROM students;	-- 根据字段显示

SELECT (NAME,gender) FROM students;	-- 语法错误

SELECT (NAME) FROM students;	-- 注意：可以

SELECT NAME AS "姓名", gender AS "性别" FROM students;	-- as 重命名

SELECT s.gender FROM students AS s;	-- as 重命名

SELECT DISTINCT NAME FROM students;	-- 去重

SELECT DISTINCT NAME,age FROM students; -- 多个字段时  一行一行 比较 去重

SELECT * FROM students WHERE id>3; 	-- 查询id>3的数据\

SELECT * FROM students WHERE age>18;	-- 查询年龄 > 18

SELECT NAME,age,gender FROM students WHERE NAME!="amy";		-- 姓名不为amy的

SELECT * FROM students WHERE age>=18 AND <=22;		-- 报错
 
SELECT * FROM students WHERE age>=18 AND age<=22;	-- 查询18-22之间的数据

SELECT * FROM students WHERE 18=<age<=22;	-- 报错

SELECT * FROM students WHERE id>3 AND gender="女";	-- id<3并且为女的
	
SELECT * FROM students WHERE id<3 OR id>4;		-- id<3 或者 id>4的

SELECT * FROM students WHERE NOT(age=18 AND gender="男"); -- 年龄非(18的男同学)

SELECT * FROM students WHERE NOT age=18 AND gender="男";  -- 年龄不是18的男同学

SELECT * FROM students WHERE NAME LIKE "a%"; 	-- 查询名字以a开头的学生信息

SELECT * FROM students WHERE NAME LIKE "%a%";	-- 含有a的学生信息

SELECT * FROM students WHE