-
查询年龄为20,21,22,23岁的女性员工信息
-
查询性别为男，并且年龄在20到40岁（含）以内的姓名为三个字的员工
-
统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
-
查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
-
查询性别为男，且年龄在20~40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
1.select * from emp where gender='女' and age in(20,21,22,23);
2.select * from emp where gender='男' and age between 20 and 40 and name like'___';
3.select gender,count(*) from emp where age<60 group by gender;
4.select name,age from emp where age<=35 order by age ASC,entrydate DESC;
5.select * from emp where gender='男' and age between 20 and 40 order by age ASC,entrydate ASC limit 5;mysql中limit与offset用法 eg:select * from article limit 1,3;//跳过一条数据，从第2条数据开始取，取3条数据 select * from article limit 3 offset 1;//跳过一条数据，从第2条数据开始取，取3条数据
有一个全部员工的薪水表salaries简况如下:
| emp_no | salary | from_date | to_date |
|---|---|---|---|
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 2001-08-02 | 9999-01-01 |
| 10003 | 43311 | 2001-12-01 | 9999-01-01 |
有一个各个部门的领导表dept_manager简况如下:
| dept_no | emp_no | to_date |
|---|---|---|
| d001 | 10001 | 9999-01-01 |
| d002 | 10003 | 9999-01-01 |
请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:
| emp_no | salary | from_date | to_date | dept_no |
|---|---|---|---|---|
| 10001 | 88958 | 2002-06-22 | 9999-01-01 | d001 |
| 10003 | 43311 | 2001-12-01 | 9999-01-01 | d002 |
CREATE TABLE salaries(
emp_no INT(10) NOT NULL,
salary INT(10) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY(emp_no)
)COMMENT '薪水表';
CREATE TABLE dept_manager(
dept_no CHAR(4) NOT NULL,
emp_no INT(10) NOT NULL,
to_date date NOT NULL,
PRIMARY KEY(dept_no)
)COMMENT '领导表';
INSERT INTO salaries (emp_no,salary,from_date,to_date) VALUES (10001,88958,'2002-06-22','9999-01-01'),(10002,72527,'2001-08-02','9999-01-01'),(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO dept_manager (dept_no,emp_no,to_date) VALUES ('d001',10001,'9999-01-01'),('d002',10003,'9999-01-01');
SELECT s.*,d.dept_no FROM salaries AS s,dept_manager AS d WHERE s.emp_no = d.emp_no ORDER BY s.emp_no ASC;显示内连接：[INNER] JOIN ON inner可以省略
//itheima库emp、dept
查询每一个员工的姓名，及关联的部门的名称
SELECT e.name,d.`name` FROM emp e INNER JOIN dept d ON e.dept_id = d.id;左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件……
//查询表1左表的所有数据 包含 表1和表2交集部分的数据
查询emp表的所有数据,和相应的部门信息（左外连接）
SELECT e.*,d.name FROM emp e LEFT [OUTER] JOIN dept d ON e.dept_id=d.id;
**********************************************************
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件……
//查询表2右表的所有数据 包含 表1和表2交集部分的数据
查询dept表的所有数据，和相应的员工信息（右外连接）
SELECT d.*,e.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id=d.id;自连接(注意一定要给表起别名)
1.查询员工及其所属领导的名字 表emp（自连接中内连接）
SELECT a.`name`,b.`name` FROM emp a,emp b WHERE a.managerid = b.id;
2.查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来
SELECT a.`name` '员工',b.`name` '领导' FROM emp a LEFT JOIN emp b ON a.managerid = b.id;标量子查询
1.查询“销售部”的所有员工信息
--a.查询“销售部”部门ID
SELECT id FROM dept WHERE name = '销售部';
--b.根据销售部门ID，查询员工信息
SELECT * FROM emp WHERE dept_id=4;
合并之后：SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
2.查询在“方东白”入职之后的员工信息
--a.查询方东白的入职日期
SELECT entrydate FROM emp WHERE `name`='方东白';
--b.查询指定入职日期之后的员工信息
SELECT * FROM emp WHERE entrydate> '2009-02-12';
合并之后：SELECT * FROM emp WHERE entrydate> (SELECT entrydate FROM emp WHERE `name`='方东白');列子查询
1.查询“销售部”和“市场部”的所有员工信息
--a.查询“销售部”和“市场部”的部门ID
SELECT id FROM dept WHERE `name`='销售部' OR `name`='市场部';
--b.根据部门ID，查询员工信息
SELECT * FROM emp WHERE dept_id IN (2,4);
合并之后：SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name`='销售部' OR `name`='市场部');
2.查询比财务部所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE `name`= '财务部'));
3.查询比研发部其中任意一人工资高的员工信息
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE `name` = '研发部'));行子查询
查询与“张无忌”的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary, managerid FROM emp WHERE `name`='张无忌');表子查询（多行查询）
1.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE `name`= '鹿杖客' OR `name` = '宋远桥');
2。查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
--a.入职日期是“2006-01-01”之后的员工信息
SELECT * FROM emp WHERE entrydate> '2006-01-01';
--b.查询这部分员工，对应的部门信息
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate> '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id;多表查询总结练习题
1.查询员工的姓名、年龄、职位、部门信息。
SELECT e.`name`,e.age,e.job,d.`name` FROM emp e,dept d WHERE e.dept_id=d.id;
2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息
SELECT e.`name`,e.age,e.job,d.`name` FROM emp e INNER JOIN dept d ON e.dept_id=d.id WHERE e.age<30;
3.查询拥有部门员工的部门ID、部门名称
SELECT DISTINCT d.id,d.`name` FROM emp e,dept d WHERE e.dept_id=d.id;
4.查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示出来
SELECT e.*,d.`name` FROM emp e LEFT JOIN dept d ON e.dept_id=d.id WHERE e.age>40;
5.查询所有员工的工资等级
SELECT e.*,s.grade FROM emp e,salgrade s WHERE e.salary>=s.losal AND e.salary<=s.hisal;
或者：SELECT e.*,s.grade FROM emp e,salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;
6.查询“研发部”所有员工的信息以及工资等级
SELECT e.*, s.grade
FROM emp e,
dept d,
salgrade s
WHERE
e.dept_id = d.id
AND ( e.salary BETWEEN s.losal AND s.hisal )
AND d.`name` = '研发部';
7.查询“研发部”员工的平均工资
SELECT AVG(e.salary) FROM emp e,dept d WHERE e.dept_id=d.id AND d.`name`='研发部';
8.查询工资比“灭绝”高的员工信息
SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE `name`='灭绝');
9.查询比平均薪资高的员工信息
SELECT * FROM emp WHERE salary>(SELECT AVG(salary) FROM emp);
☆10.查询低于本部门平均工资的员工信息
SELECT *,(SELECT AVG(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id) FROM emp e2 WHERE e2.salary<(SELECT AVG(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id);
☆11，查询所有的部门信息，并统计部门的员工人数
SELECT d.*,(SELECT COUNT(*) FROM emp e WHERE e.dept_id =d.id) '人数' FROM dept d;
●12.查询所有学生的选课情况，展示出学生名称，学号，课程名称
SELECT s.`name`,s.id,c.`name` FROM student s,course c,student_course sc WHERE s.id=sc.studentid AND sc.courseid = c.id;事务处理
转账操作
-
张三给李四转账1000块（itheima库中account表）
方式一： SELECT @@autocommit; SET @@autocommit=0;//设置手动提交 SELECT * FROM account WHERE `name`='张三';//查询账户余额 UPDATE account SET money=money-1000 WHERE `name`='张三';//张三账户-1000 UPDATE account SET money=money+1000 WHERE `name`='李四';//李四账户+1000 COMMIT;//提交事务 ROLLBACK;//回滚事务 方式二： START TRANSACTION;//开启事务 SELECT * FROM account WHERE `name`='张三'; UPDATE account SET money=money-1000 WHERE `name`='张三'; 程序异常... UPDATE account SET money=money+1000 WHERE `name`='李四'; COMMIT; ROLLBACK;面试
mysql事务隔离级别
-
事务隔离级别是为了解决各个并行事务竞争导致的数据安全问题的一种规范
-
具体来说，多个事务竞争可能会产生三种不同的一个现象
-
第一个，假设有两个事务T1/T2同时在执行，那么T1事务有可能会读取到T2事务未提交的数据，但是未提交的事务T2有可能会出现回滚，也就导致T1事务读取到一个最终不一定存在的数据，从而产生一个脏读的现象
-
第二个，假设有两个事务T1/T2同时在执行，那么事务T1在不同的时刻读取的同一行数据的时候，有可能结果会不一样，从而导致一个不可重复读的一个问题
-
第三个，假设有两个事务T1/T2同时在执行，那么事务T1在执行范围查询或者范围修改的一个时候，事务T2插入1条属于事务T1范围内的数据，并且提交了，那么这个时候事务T1查询的时候发现多出了1条数据或者说T1事务发现这条数据并没有被修改那么看起来像是产生了一个幻觉，这种现象我们称为幻读
-
而这三种现象在实际应用中，可能有些场景不能接受某些现象的存在，所以在sql里定义了四种隔离级别
-
读未提交，在这种隔离级别下，可能会产生脏读，不可重复读，幻读
-
读已提交，在这种隔离级别下，可能会产生不可重复读和幻读
-
可重复读，在这种隔离级别下，可能会产生幻读
-
串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题
-
-
-
这4种隔离级别里面，只有串行化解决了全部问题，但也意味着，这种隔离级别的性能是最低的，在mysql里面默认存储引擎InnoDB默认隔离级别是RR可重复读，因为它需要保证事务ACID特性中的隔离性
相关函数练习
-
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0
-
通过数据库的函数，生成一个六位数的随机验证码
-
查询所有员工的入职天数，并根据入职天数倒序排序
-
查询emp表的员工姓名和工作地址（北京/上海--->一线城市，其他--->二线城市）
-
统计班级各个学员的成绩，规则如下：（1）>=85，展示优秀（2）>=60，展示及格 （3）否则，展示不及格
1.UPDATE emp SET workno =LPAD(workno,5,'0');
2.select lpad(round(rand()*1000000,0),6,'0');
3.select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
4.SELECT name,(CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) AS '工作地址' FROM emp;
5.SELECT id,name,
(case WHEN math>=85 THEN '优秀' WHEN math>=60 THEN '及格' ELSE '不及格' END) '数学',
(case WHEN english>=85 THEN '优秀' WHEN english>=60 THEN '及格' ELSE '不及格' END) '英语',
(case WHEN chinese>=85 THEN '优秀' WHEN chinese>=60 THEN '及格' ELSE '不及格' END) '语文'
FROM score;用户管理和权限控制
-
创建用户itcast，只能够在当前主机localhost访问，密码123456；
-
创建用户heima，可以在任意主机访问该数据库，密码123456
-
修改用户heima的访问密码为1234
-
删除itcast@localhost用户
1.CREATE user 'itcast'@'localhost' IDENTIFIED by '123456';
2.CREATE user 'heima'@'%' IDENTIFIED by '123456';
3.ALTER user 'heima'@'%' IDENTIFIED WITH mysql_native_password by '1234';
4.DROP user 'itcast'@'localhost';-
查询权限
-
授予权限
-
撤销权限
1.SHOW GRANTS FOR 'heima'@'%';
2.GRANT ALL ON test.* to 'heima'@'%';
3.REVOKE ALL ON test.* FROM 'heima'@'%';约束练习
| 字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
|---|---|---|---|---|
| id | ID唯一标识 | int | 主键，并且自动增长 | PRIMARY KEY,AUTO_INCREMENT |
| name | 姓名 | varchar(10) | 不为空，并且唯一 | NOT NULL,UNIQUE |
| age | 年龄 | int | 大于0，并且小于等于120 | CHECK |
| status | 状态 | char(1) | 如果没有指定该值，默认为1 | DEFAULT |
| gender | 性别 | char(1) | 无 |
CREATE TABLE user(
id INT PRIMARY KEY auto_increment COMMENT '主键',
name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
age INT CHECK (age > 0 && age <=120 ) COMMENT '年龄',
status CHAR(1) DEFAULT('1') COMMENT '状态',
gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';
//插入数据
INSERT INTO user (name,age,status,gender) VALUES ('Tom1',19,'1','男'),('Tom2',25,'0','男');
//录入重复数据时，虽然无法执行，但是该空间已经被占用了，即id编号5
//建子表emp
CREATE TABLE emp(
id INT auto_increment COMMENT 'ID' PRIMARY KEY,
name VARCHAR(50) NOT NULL COMMENT '姓名',
age INT COMMENT '年龄',
job VARCHAR(20) COMMENT '职位',
salary INT COMMENT '薪资',
entrydate date COMMENT '入职时间',
managerid INT COMMENT '直属领导ID',
dept_id INT COMMENT '部门ID'
) COMMENT '员工表';
//插入数据
INSERT INTO emp (id,name,age,job,salary,entrydate,managerid,dept_id) VALUES (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);
//添加外键
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id); //关联主表dept//添加外键
1.CREATE TABLE表名(
字段名 数据类型,
...
CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
2.ALTER TABLE emp ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
//删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;//外键约束删除/更新行为
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
ON UPDATE CASCADE ON DELETE CASCADE;
---------------------------------------------------------------
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null
ON UPDATE SET NULL ON DELETE SET NULL;
1.ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;
2.ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
