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
常用SQL语句
最新推荐文章于 2024-03-19 21:56:17 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)