Mysql基本练习

SELECT DATABASE();

use mybase;

//创建表
create TABLE exam (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
English INT,
Chinese INT,
Math INT
);

SHOW TABLES;

//查看表结构
DESC exam;

//向表中添加一列
ALTER TABLE exam ADD History INT NOT NULL;

//修改列的数据类型
ALTER TABLE exam MODIFY History DOUBLE(7,2);

//修改表的列名
ALTER TABLE exam CHANGE History Physics INT NOT NULL;

//修改表名
RENAME TABLE exam TO score;
desc score;

//修改表的字符集
ALTER TABLE score CHARACTER SET GBK;

//删除列
ALTER TABLE score DROP Physics;

//插入部分列
INSERT into score(id,NAME,English,Chinese,Math) VALUE(1,‘hudie’,90,90,90);
INSERT INTO score(id,NAME,English,Chinese) value(null,‘diedie’,91,91);

– 插入所有列
INSERT into score VALUES(3,‘huhu’,80,80,80);

SELECT * from score;

– 修改记录
UPDATE score set Chinese = 99;
UPDATE score set Math = 100 WHERE id = ‘1’;

– 删除记录
DELETE from score where id = ‘2’;
DELETE FROM score;

– delete与truncate的区别：
– truncate table删除表的记录：将整个表都删除掉，重新创建一个空表；
– delete from 删除表的记录：一条一条进行删除；
INSERT INTO score VALUES(3,‘shu’,80,80,80);
DELETE FROM score;
INSERT INTO score VALUES(null,‘liba’,90,90,90);

TRUNCATE table score;

INSERT INTO score VALUES(null,‘小花’,99,99,99);
INSERT INTO score VALUES(null,‘小蓝’,89,89,89);
INSERT INTO score VALUES(null,‘小zi’,89,89,89);

SELECT * FROM score;

– 查询部分字段
SELECT math from score;

– 过滤重复字段行
SELECT DISTINCT math from score;

– 查询字段起别名
SELECT NAME,English AS English_socre FROM score;

– 使用表达式
SELECT NAME,English-20 AS _Enlish FROM score;
SELECT NAME,English+math+Chinese FROM score;

– 模糊查询
SELECT * FROM score WHERE NAME LIKE ‘小_’;
SELECT * FROM score WHERE NAME LIKE ‘%%’;

– 排序查询
SELECT * FROM score WHERE NAME LIKE ‘小%’ ORDER BY English ASC;

– 聚合函数
SELECT SUM(English+Chinese+math) from score;
SELECT COUNT(id) FROM score WHERE NAME is NOT NULL;
SELECT MAX(English) from score;
SELECT Min(English) FROM score;
SELECT AVG(English) FROM score;

– 分组查询与分页查询
CREATE TABLE emp (
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal DOUBLE(7,2),
commit DOUBLE(5,2),
dept INT NOT null
);

INSERT INTO emp VALUES
(1002,‘白展堂’,‘clerk’,1001,‘1983-05-09’,7000.00,200.00,10),
(1003,‘李大嘴’,‘clerk’,1002,‘1980-07-08’,8000.00,100.00,10),
(1004,‘吕秀才’,‘clerk’,1002,‘1985-11-12’,4000.00,null,10),
(1005,‘郭芙蓉’,‘clerk’,1002,‘1985-03-04’,4000.00,null,10),
(2001,‘胡一菲’,‘leader’,null,‘1994-03-04’,15000.00,NULL,20),
(2002,‘陈美嘉’,‘manger’,2001,‘1993-05-24’,10000.00,300.00,20),
(2003,‘吕子乔’,‘clerk’,2002,‘1995-05-19’,7300.00,100.00,20),
(2004,‘张伟’,‘clerk’,2002,‘1994-10-12’,8000.00,500.00,20),
(2005,‘曾小贤’,‘clerk’,2002,‘1993-05-10’,9000.00,700.00,20),
(3001,‘刘梅’,‘leader’,null,‘1968-08-08’,13000.00,NULL,30),
(3002,‘夏冬梅’,‘manger’,3001,‘1968-09-21’,10000.00,600.00,30),
(3003,‘夏雪’,‘clerk’,3002,‘1989-09-21’,8000.00,300.00,30),
(3004,‘张一山’,‘clerk’,3002,‘1991-06-16’,88000.00,200.00,30);

SELECT * FROM emp;

– 查询每个部门的平均工资
SELECT dept,MAX(sal) from emp GROUP BY dept;

– 查询每个职位的最高工资和最低工资
SELECT job,MAX(sal),MIN(sal) from emp GROUP BY job;

– 查询每个部门的最高薪水，只有最高薪水大于15000的记录才显示
SELECT dept,max(sal) as max_sal from emp GROUP BY dept HAVING max_sal >= 15000;

– 查询每个部门的平均工资
SELECT dept,MIN(sal) from emp GROUP BY dept;

– 6.Havaing子句与where子句的区别
(1)where是用来过滤记录的,HAVING是用来过滤分组的
(2)过滤的时机不相同,先过滤Where后过滤Having.
(3)WHERE是在查询表时逐行过滤以选取满足条件的记录
(4)having是在数据查询后并且分完组后对分组进行过滤的
(5)HAVING必须跟在group BY
(6)查询语句执行顺序:5select 1from 2where 3group by 4having 6order by

– 分页查询
– 查询前三行1，2，3
SELECT * FROM emp LIMIT 0,3;

– 查询11，12，13，14，15
SELECT * FROM emp LIMIT 10,5;

SELECT * FROM emp;

– 查询工资最高的前十个员工的信息
SELECT * from emp ORDER BY sal DESC limit 0,10;

– 完整性约束
desc emp;

– 添加唯一约束和非空约束
ALTER TABLE emp MODIFY NAME VARCHAR(21) UNIQUE not null;

– 创建主表
create TABLE dept (
dept INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);

INSERT INTO dept VALUES
(10,‘餐饮部’,‘上海’),
(20,‘销售部’,‘浙江’),
(30,‘财务部’,‘北京’),
(40,‘技术部’,‘深圳’);

– 为表emp添加外键
ALTER TABLE emp Add FOREIGN key (dept) REFERENCES dept(dept);

SELECT * from emp,dept;

– 等值链接 （A.主键=B.外键）
SELECT * FROM emp,dept WHERE dept.dept = emp.dept;

各种连接查询的比较：
https://blog.csdn.net/zjt980452483/article/details/82945663

– 内链接
SELECT * FROM emp INNER JOIN dept on dept.dept = emp.dept;

– 左外链接
SELECT * from emp left outer JOIN dept on dept.dept = emp.dept;

– 右外链接
SELECT * from emp right OUTER JOIN dept on dept.dept = emp.dept;

– 多表查询练习
– 查看每个员工的名字和其所在部门的名字
desc emp;
desc dept;

SELECT emp.ename,dept.dname FROM emp,dept where emp.dept=dept.dept;

– 查询工作地点在北京的员工有哪些
SELECT * FROM emp INNER JOIN dept on emp.dept=dept.dept WHERE dept.loc = ‘北京’;

– 查看每个城市的员工的平均薪资
SELECT dept.loc,IFNULL(AVG(sal),0) FROM emp RIGHT JOIN dept on emp.dept=dept.dept GROUP BY dept.loc;

– 查看和曾小贤同职位的员工
SELECT * FROM emp where job=(SELECT job FROM emp WHERE ename = ‘曾小贤’);

参考链接：
https://blog.csdn.net/weixin_43691058/article/details/103175678