数据库MySQL最近练习过得语句（有常用的crud）

创建

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");