中小企业建网站哪个好,长春怎么做网站,台州seo优化公司,广告设计公司公司vi设计-- 注意事项
-- 1.给数据库和表起名字时尽量选择全小写
-- 2.作为筛选条件的字符串是否区分大小写看设置的校对规则utf8_bin 区分
drop database if exists hrs;
create database hrs default charset utf8 collate utf8_general_ci;use hrs;
drop table if exists tb_emp;
dro…-- 注意事项
-- 1.给数据库和表起名字时尽量选择全小写
-- 2.作为筛选条件的字符串是否区分大小写看设置的校对规则utf8_bin 区分
drop database if exists hrs;
create database hrs default charset utf8 collate utf8_general_ci;use hrs;
drop table if exists tb_emp;
drop table if exists tb_dept;create table tb_dept(
dpno int not null comment 部门编号,
dpname varchar(20) not null comment 部门名称,
dploc varchar(20) not null comment 地址,
primary key (dpno)
);
insert into tb_dept values
(1001,会计部,北京),
(1002,研发部,成都),
(1003,销售部,重庆),
(1004,运维部,深圳);create table tb_emp(
empno int not null comment 工号,
empname varchar(20) not null comment 姓名,
job varchar(20) not null comment 员工职位,
empmgr int null comment 主管姓名,
sal int not null comment 薪资,
comm int comment 每月补贴,
empsex bool default 1 comment 性别,
dpno int not null comment 所在部门编号,
primary key(empno)
);
-- 自参照完整性
-- 级联更新delete cascade on update cascadeforeign key dpno 删除和更新都会在tb_emp中删除更新
-- alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete cascade on update cascade;
alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete restrict on update restrict;-- 默认不让删除
-- alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete set null on update restrict;-- 删除后dpno变成null
alter table tb_emp drop foreign key fk_emp_dp;
alter table tb_emp add constraint fk_em_mgrreign foreign key (empmgr) references tb_emp(empno);
alter table tb_emp drop foreign key fk_em_mgrreign;-- 删除自参照约束insert into tb_emp values
(7800,张三丰,总裁,null,90000,800,1,1001),
(2056,乔峰,分析师,7800,90000,700,1,1004),
(2567,杨逍,架构师,7800,80000,800,0,1002),
(2008,杨不悔,大会计,7800,90000,500,0,1001),
(2643,周芷若,销售经理,7800,8000,7800,0,1003),
(1352,杨过,程序员,2567,9000,1000,1,1002),
(1341,赵敏,会计1,2008,9000,500,0,1001),
(1654,小昭,会计2,2008,8000,800,0,1001),
(1987,张翠山,程序员2,2567,8000,600,0,1002),
(1563,殷素素,运维员1,2056,8000,800,0,1004),
(1744,叶青梅,销售员1,2643,8000,800,0,1003);
use hrs; -- alter table tb_emp add column hiredate date ;after mgr/first -- select * from tb_emp; -- 查询薪资最高的员工 -- select empname from tb_emp where sal(select max(sal) from tb_emp); -- 查询员工的姓名和年薪月薪补贴*12 -- select empname, (salcomm)*12 from tb_emp; -- 查询有员工的部门编号和人数 -- select dpno, count(dpno) from tb_emp group by dpno; -- 查询所有部门的名称和人数 -- select dpname,total from tb_dept t1 inner join (select dpno, count(dpno) as total -- from tb_emp group by dpno) t2 on t1.dpnot2.dpno ; -- select dpname,count(empno) from tb_emp t1 inner join tb_dept t2 on t1.dpnot2.dpno group by t1.dpno; -- 查询薪资最高的员工除Boss的姓名和工资 -- select empname,(salcomm) from tb_emp where (salcomm)(select max(salcomm) from tb_emp where empmgr is not null) ; -- 查询薪水超过平均薪水的员工姓名和工资 -- elect empname,(salcomm)from tb_emp where (salcomm)(select avg(salcomm) from tb_emp); -- 查询薪水超过所在部门平均薪水的员工姓名、部门编号和工资 -- select empname,t1.dpno,sal,round(avgsal, 2) from tb_emp t1 inner join -- (select dpno,avg(sal) as avgsal from tb_emp group by dpno) t2 on t1.dpnot2.dpno where sal avgsal; -- select dpno, avg(salcomm) from tb_emp group by dpno -- select empname,dpno,(salcomm) from tb_emp t1 where t1.dpno (select dpno,avg(salcomm) from tb_emp group by dpno) t2; -- 查询部门中薪水最高的人的姓名、工资和所在部门名称 -- select dpno, max(sal) as msal from tb_emp group by dpno; -- select empname,sal from tb_emp inner join (select * from tb_dept t1 inner join (select dpno, max(sal) as msal from tb_emp group by dpno) t2 on t1.dpnot2.dpno) t3 where tb_emp.dpnot3.dpno and tb_emp.salt3.msal;
-- SELECT e.empno,e.empname,d.dpno,d.dpname FROM tb_emp e inner JOIN tb_dept d ON e.dpno d.dpno -- WHERE (e.dpno, e.sal) IN (SELECT dpno, MAX(sal) FROM tb_emp GROUP BY dpno); -- SELECT e.empno,e.empname,d.dpno,d.dpname FROM tb_emp e inner JOIN tb_dept d ON e.dpno d.dpno -- WHERE exists (select dpno, MAX(sal) FROM tb_emp GROUP BY dpno); -- select t1.empname,t1.sal,t2.dpname from tb_emp t1 inner join tb_dpt t2 on t1.dpnot2.dpno where (t1.dpno,t1.sal) in (select dpno,max(sal) from tb_emp group by dpno); -- select empname,sal, dpname from tb_emp t1 inner join (select dpno, max(sal) as msal from tb_emp group by dpno) t2 on t1.salt2.msal and t1.dpnot2.dpno inner join tb_dpt t3 on t1.dpnot3.dpno; -- 查询主管的姓名和职称 -- select empname,job from tb_emp where empno in (select distinct empmgr from tb_emp where empmgr is not null); -- select empname,job from tb_emp t1 where exists(select x from tb_emp t2 where t1.empnot2.empmgr);
-- select -- 查月薪排名4~6的员工姓名和工资 -- select empname,sal from tb_emp order by sal desc limit 3,3; -- select empname,sal from tb_emp order by sal desc limit 3 offset 3; -- select x from tb_temp t2 where t1.empnot2.empmgr -- select x; -- dual 伪表不存在的表 -- select x from dual; -- select x from tb_emp; -- select x from tb_emp where empno7800; -- select x from tb_emp where empno7900; -- select x from dual where exists(select empno from tb_emp where empname like 张%) -- select x from dual where exists(select empno from tb_emp where empname like 赵%) -- explain 生成执行计划 -- select empno,empname from tb_emp where empno7800; -- explain select empno,empname from tb_emp where empname张三丰; -- explain select empno,empname from tb_emp where empname like 张%; -- 索引(index) -- 索引可以加速查询所以应该在用于查询筛选条件的列上建立索引. -- 索引会使用额外的存储空间而且会让增删改查变得更慢因为要更新索引 -- 所以不能滥用索引 -- create index idx_emp_empname on tb_emp(empname); -- explain select empno,empname from tb_emp where empname张三丰; -- drop index idx_emp_emname on tb_emp;
-- 视图:查询的快照简化查询操作 -- 通过视图可以将用户访问权限限制到某些指定的列上 -- 查询所有部门的名称和人数 -- create view vw_dept_emp_count as -- select dpname ,total from tb_dept t1 left join (select dpno,count(dpno) as total from tb_emp group by dpno) t2 on t1.dpnot2.dpno; -- select * from vw_dept_emp_count; -- drop view vm_dept_emp_count; -- select * from vw_dept_emp_count -- 重新定义界定符为$$ -- delimiter $$ -- 创建存储过程 -- deptno 输入参数 out avgsal 输出参数 -- create procedure sp_dept_avg_sal(deptno int,out avgsal float) -- begin -- select avg(sal) into avgsal from tb_emp where dpnodeptno; -- end$$ -- 将界定符还原回 -- delimiter ; -- call sp_dept_avg_sal(1002,a); -- select a from dual; -- select avg(sal) from tb_emp where dpno1002 -- 触发器在执行增删改操作时可以触发其它的级联操作但是有可能导致“锁表”现象实际开发中尽量避免使用触发器 /*update tb_dept set dpno1005 where dpno1002; delimiter $$ create trigger tr_dept_update after update on tb_dept for each row begin update tb_emp set dpnonew.dpno where dpnoold.dpno; end $$ delimiter ;*/
-- update tb_dept set dpno1005 where dpno1001
-- DCL:授予权限grant to 和召回权限revoke from -- HelloKitty可以从xx主机登录%所有主机localhost本机IP:IP 地址的主机 -- create user Hellokitty120.10.20.30/%/localhost -- drop user HelloKitty%; -- create user helloKitty % identified by 123123; -- 将数据库中hrs的所有权限给helloKitty -- grant all privileges on hrs.* to helloKitty%; -- revoke insert,delete,update on hrs.* from helloKitty%; -- 事务transaction --把多个增删改做成一个不可分割的原子性操作 -- 要么全部都做要么全都不做 -- begin/start transaction delete from tb_emp; begin; delete from tb_emp; -- commit提交事务中的所有操作都生效 commit; -- 回滚事务中的所有操作都撤销 rollback;
在MySQL会话中执行SET SQL_SAFE_UPDATES 0;命令来关闭安全更新模式。注意这仅影响当前会话并且在会话结束时将恢复为之前的设置 SET SQL_SAFE_UPDATES 0;
