创建

CREATE TABLE student0( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT; NAME VARCHAR(20), age TINYINT UNSIGNED DEFAULT 18, height DECIMAL(5,2), gender ENUM("男","女","中"),DEFAULT "中", cls_id INT );

crud

ALTER TABLE mytable ADD birthday DATE;-- 添加字段 DESC mytable; ALTER TABLE mytable MODIFY birthday DATE DEFAULT "1990-01-01";-- 修改表字段 ALTER TABLE mytable CHANGE birthday birth DATE DEFAULT "1990-02-02"; ALTER TABLE mytable DROP hight;-- 删除表字段 INSERT INTO mytable VALUES(6,"贝贝",1.80,"M","2000-01-01"); -- 自增字段 INSERT INTO mytable (NAME,height) VALUES("小白",1.60); -- 指定字段 插入语句 用() 保护 INSERT INTO mytable (NAME,gender) VALUES("小a",1),("小b",1); -- 插入多行数据 UPDATE mytable SET gender="F" WHERE NAME="小白"; UPDATE mytable SET height="1.60",birth="1998-11-28" WHERE id=7; DELETE FROM mytable WHERE id=8;-- 物理删除 真正意义上的删除数据 -- 逻辑删除 is_del 0 未删除 1 已删除 -- 添加字段 ALTER TABLE mytable ADD `is_del` INT DEFAULT 0;-- 添加字段 UPDATE mytable SET `is_del` =1 WHERE id=10; -- 逻辑上的删除 SELECT * FROM mytable; -- 查询整张表的数据 *代表所有的字段 SELECT NAME,gender FROM mytable;-- 根据字段显示 SELECT (NAME) FROM mytable; -- 可以 SELECT NAME AS "姓名", gender AS "性别" FROM mytable; -- as 重命名 SELECT s.`gender` FROM mytable AS s; -- as 重命名 SELECT DISTINCT NAME FROM mytable; -- 去重 SELECT DISTINCT NAME,gender FROM mytable; -- 多个字段 一行一行比较 去重 SELECT *FROM mytable WHERE id>3; SELECT *FROM mytable WHERE NAME="小白"; ALTER TABLE mytable ADD age INT; SELECT *FROM mytable WHERE age >=30 OR age<=20; SELECT *FROM mytable WHERE id>3 AND gender=2; SELECT *FROM mytable WHERE NOT (age=20 AND gender=2); SELECT *FROM mytable WHERE NAME LIKE "小%"; -- 模糊查询 SELECT *FROM mytable WHERE NAME LIKE "%白%"; -- 含有 白的逻辑信息。 SELECT *FROM mytable WHERE NAME LIKE "__"; -- 任意俩个字符 SELECT * FROM mytable WHERE NAME LIKE "__%"; -- 至少含有2个字符的信息 SELECT * FROM mytable WHERE id IN (1,4,6); -- id 是1，4，6或者的意思 SELECT * FROM mytable WHERE age NOT IN (20,25); -- age 不是20 和25 的信息 SELECT * FROM mytable WHERE id BETWEEN 3 AND 6; -- id 在3-6之间 SELECT * FROM mytable WHERE (id BETWEEN 3 AND 6) AND gender="M"; -- 增加可读性 SELECT * FROM mytable WHERE (id NOT BETWEEN 6 AND 9); -- id 不在6-9的学生信息 SELECT * FROM mytable WHERE birth IS NOT NULL; -- 判断空 数据 SELECT COUNT(*) FROM mytable; 求 总人数 SELECT COUNT(*) FROM mytable WHERE gender="F"; -- 男性的人数 SELECT MAX(age) FROM mytable; -- 查询最大的年龄 SELECT NAME,MAX(age) AS "最大的年龄" FROM mytable; SELECT MIN(age) FROM mytable WHERE is_del=0; -- 未删除学生 年龄的最小值 SELECT SUM(age) FROM mytable WHERE gender="f"; -- 求和男性年龄 SELECT AVG(age) FROM mytable WHERE is_del=0 AND gender="m"; SELECT ROUND(AVG(age),2) FROM mytable WHERE is_del=0 AND gender="f"; ALTER TABLE mytable CHANGE id sid INT NOT NULL AUTO_INCREMENT;-- 修改此阶段 不需要修改主键 primary key DESC mytable;

查询练习

SELECT COUNT(*) FROM mytable GROUP BY gender; -- 计算男生与女生，保密的人数 SELECT gender "性别",COUNT(*) FROM mytable GROUP BY gender; SELECT gender "性别",COUNT(*),GROUP_CONCAT(NAME) FROM mytable GROUP BY gender; -- 详细显示不同性别的名字 -- 分组后查看总人数 还想产看总人数 with rollup SELECT gender ,COUNT(*) FROM mytable GROUP BY gender WITH ROLLUP; -- 查询组内年龄 姓名 SELECT gender AS "性别",GROUP_CONCAT(NAME,age) FROM mytable GROUP BY gender;-- 姓名和年龄连在一起了 SELECT gender AS "性别",GROUP_CONCAT(NAME,"-",age) FROM mytable GROUP BY gender;-- f返回结果用-来拼接 -- 取数总数大于2的 SELECT gender,COUNT(*) FROM mytable GROUP BY gender WHERE COUNT(*)>2; -- 会报错。分组之后的结果需要使用having进行过滤 SELECT gender,COUNT(*) FROM mytable GROUP BY gender HAVING COUNT(*)>2; -- 查询男生、女生 平均年龄超过18岁的性别和名字 SELECT gender ,AVG(age),GROUP_CONCAT(NAME) FROM mytable GROUP BY gender HAVING AVG(age)>18; -- 排序 order by -- 查询年龄在18-30之间的男同学，按照年龄从小到大排序 升序 SELECT * FROM mytable WHERE (age BETWEEN 18 AND 30) AND gender="f" ORDER BY age;-- 默认升序 SELECT * FROM mytable WHERE (age BETWEEN 18 AND 30) AND gender="f" ORDER BY age ASC; -- 升序 SELECT * FROM mytable WHERE (age BETWEEN 18 AND 30) AND gender="f" ORDER BY age DESC;-- 降序 -- 查询年龄在18-30之间的女同学，id从高到低排序 SELECT *FROM mytable WHERE (age BETWEEN 18 AND 30) AND gender="f" ORDER BY sid DESC; ALTER TABLE mytable ADD weight FLOAT; DESC mytable; -- 年龄降序 SELECT gender,NAME,weight,age FROM mytable WHERE (age BETWEEN 18 AND 30) AND gender="f" GROUP BY age DESC,weight ASC; -- 限制 SELECT * FROM mytable; SELECT *FROM mytable LIMIT 2;-- 显示前两条 SELECT *FROM mytable LIMIT 6;-- 显示前6条 SELECT *FROM mytable LIMIT 3,3; -- 显示sid为4-6的数据 偏移量 -- 制作分页 SELECT * FROM mytable LIMIT 0,3; -- 1 SELECT *FROM mytable LIMIT 3,3; -- 2 SELECT *FROM mytable LIMIT 6,3;-- 3 -- 连接 SELECT *FROM mytable INNER JOIN student; -- 直接内连接的数据集 是笛卡尔积 SELECT *FROM mytable s INNER JOIN student c ON s.sid=c.id; SELECT s.name,c.name FROM mytable s INNER JOIN student c ON s.`sid`=c.`id`; -- 显示学生的所有信息，但只显示班级名称 SELECT s.*,c.name FROM mytable s INNER JOIN student c ON s.`sid`=c.`id`; -- 查询 有能够对应班级的学生以及班级信息，按照班级进行排序 SELECT *FROM mytable s INNER JOIN student c ON s.`sid`=c.`id` ORDER BY s.`sid` DESC; SELECT *FROM mytable s INNER JOIN student c ON s.`sid`=c.`id` ORDER BY s.`sid` ASC,s.`sid` ASC; -- 左连接 SELECT * FROM mytable s LEFT JOIN student c ON s.`sid`=c.`id`; -- 查询最高的男生信息 SELECT * FROM mytable WHERE height=1.82;-- 确定知道的身高 SELECT MAX(height) FROM mytable WHERE gender="f"; -- 男性的最高的身高 SELECT NAME "名字",MAX(height) FROM mytable WHERE gender="f"; -- 不行 -- 子查询 SELECT NAME,height FROM mytable s WHERE s.`height`= (SELECT MAX(height) FROM mytable WHERE gender="f"); SELECT *FROM mytable WHERE height >(SELECT AVG(height) FROM mytable); -- 查询高于平均身高的学生信息 SELECT * FROM mytable WHERE gender="m" AND (SELECT MAX(age) FROM mytable); SELECT * FROM mytable WHERE gender="m" AND age=(SELECT MAX(age) FROM mytable WHERE gender="m");