-- 约束createtableuser(
id intprimarykeyauto_incrementcomment'主键',
name varchar(10)notnulluniquecomment'姓名',
age intcheck( age >0and age <=120)comment'年龄',statuschar(1)default'1'comment'状态',
gender char(1)comment'性别')comment'用户表';-- 添加数据insertintouser(name, age,status, gender)values('tom1',19,'1','男'),('tom2',19,'1','男');insertintouser(name, age,status, gender)values('tom3',19,'1','男');insertintouser(name, age,status, gender)values(null,19,'1','男');insertintouser(name, age,status, gender)values('tom3',19,'1','男');insertintouser(name, age,status, gender)values('tom4',19,'1','男');insertintouser(name, age,status, gender)values('tom5',-1,'1','男');insertintouser(name, age,status, gender)values('tom6',120,'1','男');insertintouser(name, age, gender)values('tom7',120,'男');insertintouser(name, age, gender)values('tom8',120,'男');insertintouser(name, age, gender)values('tom9',120,'男');insertintouser(name, age, gender)values('tom11',120,'男');insertintouser(name, age, gender)values('tom12',120,'男');insertintouser(name, age, gender)values('tom13',120,'男');-- 外键约束createtable dept(
id intauto_incrementcomment'ID'primarykey,
name varchar(50)notnullcomment'部门名称')comment'部门表';INSERTINTO dept (id, name)VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');createtable emp(
id intauto_incrementcomment'ID'primarykey,
name varchar(50)notnullcomment'姓名',
age intcomment'年龄',
job varchar(20)comment'职位',
salary intcomment'薪资',
entrydate datecomment'入职时间',
managerid intcomment'直属领导ID',
dept_id intcomment'部门ID')comment'员工表';INSERTINTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(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);-- 添加外键altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references dept(id);-- 删除外键altertable emp dropforeignkey fk_emp_dept_id;-- 删除/更新行为altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references dept(id)ondeletecascadeonupdatecascade;altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references dept(id)ondeletesetnullonupdatesetnull;
多表关系
-- 多表查询-- 创建dept表,并插入数据createtable dept(
id intauto_incrementcomment'ID'primarykey,
name varchar(50)notnullcomment'部门名称')comment'部门表';INSERTINTO dept (id, name)VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');-- 创建emp表,并插入数据createtable emp(
id intauto_incrementcomment'ID'primarykey,
name varchar(50)notnullcomment'姓名',
age intcomment'年龄',
job varchar(20)comment'职位',
salary intcomment'薪资',
entrydate datecomment'入职时间',
managerid intcomment'直属领导ID',
dept_id intcomment'部门ID')comment'员工表';-- 添加外键altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references
dept(id);INSERTINTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(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),(7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),(8,'周芷若',19,'会计',48000,'2006-06-02',7,3),(9,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),(10,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2),(11,'鹿杖客',56,'职员',3750,'2006-10-03',10,2),(12,'鹤笔翁',19,'职员',3750,'2007-05-09',10,2),(13,'方东白',19,'职员',5500,'2009-02-12',10,2),(14,'张三丰',88,'销售总监',14000,'2004-10-12',1,4),(15,'俞莲舟',38,'销售',4600,'2004-10-12',14,4),(16,'宋远桥',40,'销售',4600,'2004-10-12',14,4),(17,'陈友谅',42,null,2000,'2011-10-12',1,null);
多表查询
-- 连接查询-- 存在笛卡尔积select*from emp, dept where emp.dept_id = dept.id ;-- 内连接-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)select e.name, d.name from emp e, dept d where e.dept_id = d.id;-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)select e.name, d.name from emp e innerjoin dept d on e.dept_id = d.id;select e.name, d.name from emp e join dept d on e.dept_id = d.id;-- 外连接-- 1. 查询emp表的所有数据, 和对应的部门信息select e.*, d.*from emp e leftouterjoin dept d on e.dept_id = d.id;select e.*, d.*from emp e leftjoin dept d on e.dept_id = d.id;-- 2.查询dept表的所有数据, 和对应的员工信息(右外连接)select e.*, d.*from emp e rightouterjoin dept d on e.dept_id = d.id;select e.*, d.*from dept d leftouterjoin emp e on e.dept_id = d.id;-- 自连接-- 1.查询员工 及其 所属领导的名字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 leftouterjoin emp b on a.managerid = b.id;-- 联合查询-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来select*from emp where salary <5000unionselect*from emp where age >50;-- 子查询-- 标量子查询-- 1. 查询 "销售部" 的所有员工信息-- a.查询销售部的部门idselect id from dept where name ='销售部';-- b. 查询 "销售部" 的所有员工信息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 >(select entrydate from emp where name ='方东白');-- 列子查询-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息-- a.查询"销售部" 和 "市场部"的部门idselect id from dept where name in('销售部','市场部');-- b.查询"销售部" 和 "市场部" 的所有员工信息select*from emp where dept_id in(select id from dept where name in('销售部','市场部'));-- 2. 查询比 财务部 所有人工资都高的员工信息-- a.查询财务部所有人的工资select id from dept where name ='财务部';select salary from emp where dept_id =(select id from dept where name ='财务部');-- b.查询比 财务部 所有人工资都高的员工信息select*from emp where salary >all(select salary from emp where dept_id =(select id from dept where name ='财务部'));-- 3.查询比研发部其中任意一人工资高的员工信息-- a.查询研发部所有人的工资select salary from emp where dept_id =(select id from dept where name ='研发部');-- b.查询比研发部其中任意一人工资高的员工信息select*from emp where salary >any(select salary from emp where dept_id =(select id from dept where name ='研发部'));-- 行子查询-- 1.查询与 "张无忌" 的薪资及直属领导相同的员工信息-- a.查询与"张无忌" 的薪资和直属领导select salary, managerid from emp where name ='张无忌';-- b.查询与 "张无忌" 的薪资及直属领导相同的员工信息select*from emp where(salary, managerid)=(select salary, managerid from emp where name ='张无忌');-- 表子查询-- 1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息-- a.查询"鹿杖客" , "宋远桥" 的职位和薪资select job, salary from emp where name in('鹿杖客','宋远桥');-- b. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息select*from emp where(job, salary)in(select job, salary from emp where name in('鹿杖客','宋远桥'));-- 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 leftjoin dept d on e.dept_id = d.id;
多表查询练习
-- 多表查询案例createtable salgrade(
grade int,
losal int,
hisal int)comment'薪资等级表';insertinto salgrade values(1,0,3000);insertinto salgrade values(2,3001,5000);insertinto salgrade values(3,5001,8000);insertinto salgrade values(4,8001,10000);insertinto salgrade values(5,10001,15000);insertinto salgrade values(6,15001,20000);insertinto salgrade values(7,20001,25000);insertinto salgrade values(8,25001,30000);-- 1). 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)select e.name, e.age, e.job, d.*from emp e, dept d where e.dept_id = d.id;-- 2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)select e.name, e.age, e.job, d.*from emp e join dept d on e.dept_id = d.id where e.age <30;-- 3). 查询拥有员工的部门ID、部门名称selectdistinct d.id, d.name from emp e, dept d where e.dept_id = d.id;-- 4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来select e.*, d.name from emp e leftjoin dept d on e.dept_id = d.id where age >40;-- 5). 查询所有员工的工资等级select e.name, 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). 查询 "研发部" 员工的平均工资selectavg(e.salary)from emp e, dept d where e.dept_id = d.id and d.name ='研发部';-- 8). 查询工资比 "灭绝" 高的员工信息。-- a.查询 "灭绝"的工资select salary from emp where name ='灭绝';-- b.查询工资比 "灭绝" 高的员工信息。select*from emp where salary >(select salary from emp where name ='灭绝');-- 9). 查询比平均薪资高的员工信息-- a. 查询员工的平均薪资selectavg(salary)from emp;-- b. 查询比平均薪资高的员工信息select*from emp where salary >(selectavg(salary)from emp);-- 10). 查询低于本部门平均工资的员工信息-- a. 查询指定部门的平均薪资selectavg(e1.salary)from emp e1 where e1.dept_id =1;-- b. 查询低于本部门平均工资的员工信息select*,(selectavg(e1.salary)from emp e1 where e1.dept_id = e2.dept_id)'平均'from emp e2 where salary <(selectavg(e1.salary)from emp e1 where e1.dept_id = e2.dept_id);-- 11). 查询所有的部门信息, 并统计部门的员工人数select d.*,(selectcount(*)from emp e where e.dept_id = d.id)'人数'from dept d;selectcount(*)from emp where dept_id =1;-- 12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;
事务
-- 事务-- 准备数据droptableifexists account;createtable account(
id intprimarykeyAUTO_INCREMENTcomment'ID',
name varchar(10)comment'姓名',
money double(10,2)comment'余额')comment'账户表';insertinto account(name, money)VALUES('张三',2000),('李四',2000);update account set money =2000;-- 方式1select @@autocommit;set @@autocommit=1;-- 转账操作(张三转给李四1000块)-- 1.查询张三账户余额select*from account where name ='张三';-- 2.张三账户余额-1000update account set money = money -1000where name ='张三';
出错了...-- 3.李四账户余额+1000update account set money = money +1000where name ='李四';-- 提交事务commit;-- 回滚事务rollback;-- 方式2-- 手动的通过begin开启事务-- 开启事务starttransaction;-- 转账操作(张三转给李四1000块)-- 1.查询张三账户余额select*from account where name ='张三';-- 2.张三账户余额-1000update account set money = money -1000where name ='张三';
出错了...-- 3.李四账户余额+1000update account set money = money +1000where name ='李四';-- 执行出错,回滚事务rollback;