MySql练习记录01

最新推荐文章于 2024-06-02 14:54:06 发布

hjlplus

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量40

点赞数

分类专栏： mysql 文章标签： mysql 数据库

原文链接：https://blog.csdn.net/lv_hulk/article/details/82936738?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522170614685716777224420539%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=170614685716777224420539&biz_id=0&utm_medium=

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

创建表带字段备注的模板

-- CREATE TABLE `biz_chache_jiancha_jilu` (
--   `bus_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
--   `dept_id` bigint(20) NOT NULL COMMENT '部门id',
--   `user_id` bigint(20) NOT NULL COMMENT '用户id',
--   `deployment_id` varchar(64) DEFAULT '' COMMENT '部署主键',
--   `instance_id` varchar(64) DEFAULT '' COMMENT '流程实例主键',
--   `dateStr` varchar(64) DEFAULT '' COMMENT '日期',
--   `jiancha_xiangmu` varchar(64) DEFAULT '' COMMENT '检查项目',
--   `jiancha_neirong` varchar(64) DEFAULT '' COMMENT '检查内容',
--   `jiancha_jieguo` varchar(64) DEFAULT '' COMMENT '检查结果',
--   `chuli_jieguo` varchar(64) DEFAULT '' COMMENT '处理结果',
--   `bei_zhu` varchar(64) DEFAULT '' COMMENT '备注',
--   `bus_status` char(1) NOT NULL COMMENT ' 状态（1正常 2停用）',
--   `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
--   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
--   `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
--   `update_time` datetime DEFAULT NULL COMMENT '更新时间',
--   PRIMARY KEY (`bus_id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='叉车记录';

练习1

1)建emp表：

CREATE TABLE IF NOT EXISTS emp (
empno int PRIMARY KEY COMMENT '雇员编号',
ename varchar(20) NOT NULL COMMENT '雇员名称',
job varchar(20) NOT NULL COMMENT '职位',
mgr int DEFAULT NULL COMMENT '领导编号',
hiredate date COMMENT '入职时间',
sal decimal(20, 2) COMMENT '工资',
comm decimal(20, 2) COMMENT '奖金',
deptno int COMMENT '部门编号'
);

添加外键

-- 格式：alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>);
alter table emp add CONSTRAINT FK_CID foreign key(deptno) REFERENCES dept(deptno);

插入数据

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00,NULL, 20);

INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00',1600.00, 300.00, 30);

INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00',1250.00, 500.00, 30);

INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00',2975.00, NULL, 20);

INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00',1250.00, 1400.00, 30);

INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00',2850.00, NULL, 30);

INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00',2450.00, NULL, 10);

INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00',3000.00, NULL, 20);

INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00',5000.00, NULL, 10);

INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00',1500.00, 0.00, 30);

INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00',1100.00, NULL, 20);

INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00,NULL, 30);

INSERT INTO emp VALUES (7934, 'MILLER','CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);

-- 查询所有数据
SELECT * FROM emp;

2)创建dept表

CREATE TABLE IF NOT EXISTS dept(
    deptno int PRIMARY KEY COMMENT '部门编号',
    dname varchar(20) not null COMMENT '部门名称',
    loc varchar(20) not null COMMENT '部门位置'
);

插入数据

INSERT INTO dept VALUES (10,'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS','BOSTON');

-- 查询数据
SELECT * From dept;

3)创建salgrade表（工资等级表）

create table salgrade(
grade int primary key COMMENT '等级',
losal int COMMENT '最低工资',
hisal int COMMENT '最高工资'
);

插入数据

insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);

完成以上数据表的相关查询

-- 1.查询部门名称是‘ACCOUNTING’的员工姓名及薪水等级
SELECT emp.ename,salgrade.grade
FROM emp,dept,salgrade
WHERE 
    dept.dname = 'ACCOUNTING' 
    AND
    emp.deptno = dept.deptno
    AND
    emp.sal BETWEEN salgrade.losal and salgrade.hisal;
-- 2.查询平均薪水最低的部门名称，只统计领导薪资
SELECT dname FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE empno in (SELECT mgr FROM emp) 
                                GROUP BY deptno
                                ORDER BY avg(sal) LIMIT 0,1);
-- 3.查询‘SALES’部门平均薪水的等级
SELECT grade FROM salgrade
WHERE            
    (SELECT avg(sal) FROM emp,dept WHERE dname = 'SALES' AND emp.deptno = dept.deptno) 
    BETWEEN losal AND hisal;
-- 4.查询不是领导的员工信息
-- not in(1,2,3,null) 如果没有1，2，3都没有，判断null的时候 返回结果就是null，如果select 1 not  in（1，null）返回0
-- SELECT * FROM emp
-- WHERE empno NOT IN (SELECT mgr FROM emp);

select * from emp e1 where not exists (select 1 from emp e2
where e1.empno = e2.mgr);
-- 5.求薪水最高的员工姓名 （并列时都查询出来）
SELECT ename FROM emp
WHERE sal =
(SELECT max(sal) FROM emp);
-- SELECT ename FROM emp
-- WHERE sal =
-- (SELECT sal FROM emp
-- GROUP BY empno
-- ORDER BY sal desc limit 1)
-- 6.查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列
SELECT empno,ename,sal,sal*12 salyear FROM emp
WHERE sal*12 >10000
ORDER BY sal*12 DESC;
-- 7.查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周
-- DATEDIFF函数来计算当前日期（CURDATE()）与指定日期（'2023-07-19'）之间的天数差
SELECT DATEDIFF(CURDATE(),hiredate)/7 FROM emp
WHERE ename = 'SMITH';
-- TO_DAYS 是一个常见的 SQL 函数，用于将日期转换为天数。这个函数通常用于计算两个日期之间的天数差。
select (TO_DAYS(now()) - TO_DAYS(hiredate))/7 from emp where ename = 'smith';
-- 8.查询第2到第7名的员工，按薪水降序排列
SELECT * FROM emp
ORDER BY sal DESC LIMIT 1,6
-- 9.查询最后入职的5名员工
SELECT * FROM emp
ORDER BY hiredate DESC
LIMIT 0,5
-- select * from emp order by TO_DAYS(hiredate) desc limit 5;
-- 10.查询薪水大于所在部门平均工资的员工信息。
SELECT * FROM emp e1
WHERE e1.sal>(SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno = e2.deptno)

遇见的小bug总结贴个图：

补充：

添加数据(insert)
Insert into tname[(字段名称…)] values(值…);
#添加数据classroom
#给所有字段添加值,和表中字段顺序一致
insert into classroom values(3,‘bd1809’);

#给cname字段添加值,和指定字段的顺序必须一致
insert into classroom(des,cname) values(‘des’,‘bd1810’);

#添加三条记录(批量插入)
insert into classroom(cname,des)
values(‘bd1811’,‘des’)，(‘bd1812’,‘des’)， (‘bd1901’,‘des’);

#将classroom的值整体复制到classroom1
insert into classroom1 select * from classroom;

删除数据 delete
delete from tname [where 条件]
truncate:清空文件级别清空
自增重置
delete:删除只修改数据逐行删除自增不会重置

修改数据 update
Update tname set 字段=新值,… [where 条件]
update classroom set des = ‘des’ where cid = 2;

查询数据 select
Select 字段|表达式
from 表|视图|结果集
Where 条件
Group by 分组
Having 分组之后进行检索
Order by 排序
Limit 限制结果

[as] 别名
#字段表达式结果集表都可以起别名
select ename,sal*1.05 nsal from emp；

#查询所有职位信息去重
select distinct job from emp;

order by 排序字段默认升序排序 asc | desc
#根据员工工资降序排序,工资一致的按照员工编号的降序排序。
select * from emp order by sal desc,empno desc;

#限制结果查询 limit 下标,长度 (仅适用于mysql,分页查询）
select * from emp limit 0,5;

#查询所有有奖金的员工信息（不为空）
select * from emp where comm is not null;
#模糊查询 like
%:0到多个任意字符 _:1个字符
#查询名字以s打头的员工信息
select * from emp where ename like ‘s%’;
#包含
select * from emp where ename like ‘%s%’;
# 第二个字符为L的所有员工信息
select * from emp where ename like ‘_l%’;

说明：
删除和修改用的相对较少，这里没有给出具体事例，只给出删除和修改的方法，数据库重点是查询，需重点掌握！！！

注：原作者是MySQL案例（增删改查）_增删改差 mysql 例子-CSDN博客

我只用于练习记录