圣诞派对

mysql数据库中 : database : 文件夹 table : 数据表(数据文件) 进入mysql mysql -u 用户名 -p 输入密码 显示系统的database（文件夹） 查看存在的数据库 show databases; 创建database create database 名称; 选择database use database名称; 在当前database可以随时选择其他的database 只需要直接use 名称即可 显示数据文件 show tables; 显示数据文件必须要在选择database之后才可以进行 删除database drop database 名称; 查看表的详细数据 desc 表名； 创建table create table 表名(列名 类型,列名1 类型1,...列名N 类型N); 删除表 drop table 名称; 插入数据 1.insert （into ）表名 values(列值,列值1,...列值N); 该种插入方式必须与表中的列顺序一致 2.insert （into） 表名(列名,列名1,...列名N) values(列值,列值1,...列值N); 制定某些列之后插入相对应的值，可以不按照表中列顺序插入, 并可以只插入一部分数据 查询数据 select 列名,列名1,...列名N from 表名; 最简单的查询方式 ：select * from 表名; 一种通配符，用来代表所有列 一般情况下，工作中不使用 因为需要多一次遍历查询表内列名 删除数据 条件删除 delete from 表名 where 列名 = 列值; 删除全部数据 delete from 表名; 更改数据 条件更改 update 表名 set 列名 = 列值 where 列名1 = 列值1; 更改全部数据 update 表名 set 列名 = 列值; 别名机制 在数据库中 一个列名之后直接添加一个未定义的单词，相当于对该列进行别名操作 例:select ename name from emp; name既是ename的别名 中间略写了关键字as 数据表也可以起别名，sql语句的执行顺序为 from起向右执行，执行完毕后再从select开始向右执行 条件查询 查询工资大于5000的员工的编号、姓名和工资 select empno,ename,salary from emp where salary > 5000; 查询工资大于5000且小于10000的员工的编号、姓名和工资 select empno,ename,salary from emp where salary > 5000 and salary < 10000; 查询工资小于5000或大于10000的员工的编号、姓名和工资 select empno,ename,salary from emp where salary < 5000 or salary > 10000; 查询工资在5000到10000之间的员工的编号、姓名和工资 select empno,ename,salary from emp where salary between 5000 and 10000; 比较运算符 > < = <>(!=) >= <= 在mysql中支持!=运算符 但是，部分数据库不支持!=运算符， 一般情况下，<>运算符是数据库的通用"不等于"运算符 逻辑运算符 and(&&) or(||) 在mysql中支持&&和||运算符，但是通用的逻辑运算使用and和or 在做区间判断时where 列名 between (列值begin) and (列值end) between and是一个全闭区间，左右值均包含 条件删除、条件更新、条件查询都可以使用数据库中的通用比较运算符 查询员工的月工资 select empno,ename,salary+ifnull(bonus,0) sal_month from emp; 列之间相加，就是列之间的值一一相加 查询员工的姓名，编号，月工资，一年的基本工资，一年总奖金，一年的总工资 select empno,ename, salary+ifnull(bonus,0) sal_month, salary12 sal_base_year, bonus12 bonus_year, (salary+ifnull(bonus,0)) *12 sal_year from emp; 列还可以直接与一个常量做运算，并且运算结果依然可以做二次运算 查询员工编号为1001,1003,1005的员工编号，姓名 select empno,ename from emp where empno in(1001,1003,1005); in 查询一个集合 但效率比较低 一般情况下不使用 一般情况下用exists替换 查询出名字叫Lacus的员工的编号、姓名、工资 select empno,ename,salary from emp where ename = 'Lacus'; 在mysql中查询字符串信息时不区分大小写，但在其他数据库中区分 需要使用upper和lower做相应的转换 通用函数 ifnull(列名，value) 如果该列的某一个值为NULL，则修改为value 在部分数据库中该函数叫nvl upper(列名) 将该列的值转换为大写（对字符串列操作） lower(列名) 将该列的值转换为小写（对字符串列操作） 求出公司里的人数 select count(empno) emp_count from emp; 使用一个不会是空的列进行查询，一般情况下使用编号列 求出公司员工所有人的总工资 select sum(salary + ifnull(bonus,0)) sum_sal from emp; 求出公司员工的平均工资 select avg(salary+ifnull(bonus,0)) avg_sal from emp; 求出公司员工的编号、姓名、工资并按照工资从小到大的顺序排序 select empno,ename,salary from emp order by salary asc; asc : 正序 默认排序方式 可省略 desc : 逆序 当逆序排序时 在最后添加 当条件查询语句需要排序时，要在语句最后添加排序 组函数 count(列名) 根据该列的值，返回相应的数量 sum(列名) 根据该列的值，返回该列值的总和 avg(列名) 根据该列的值，返回该列的平均数 max(列名) 根据该列的值，返回该列的最大值 min(列名) 根据该列的值，返回该列的最小值 查询出公司每个部门的部门编号、该部门的人数、该部门的工资总和 select deptno,count(empno),sum(salary) from emp group by deptno; 平均工资大于5000元的部门编号和平均工资，没有部门的不算 select avg(salary) avg_sal,deptno from emp group by deptno having avg_sal > 5000; 当分组之后不可以使用where条件查询只可以使用having 哪些职位的人数超过1个人？ select job,count(empno) from emp group by job having count_emp > 1; 查询调用组函数时，如果语句中带有普通列查询，则必须做分组处理 group by 列名 分组处理的列一般情况下，就是所查询的普通列,并且该列的值是有重复的 查询出没有奖金的员工编号和姓名 select empno,ename from emp where bonus is NULL; NULL的特性 1.任何与NULL做运算的结果 都是NULL 2.被组函数忽略 3.在mysql中NULL默认最小，在oracle中NULL默认最大 4.在数据库中判断是否为NULL不可以用比较运算符判断 判断是NULL is NULL 不是NULL is not NULL 子查询 查询出工资比Lacus高的员工的编号、姓名、工资 select empno,ename,salary from emp where salary > (select salary from emp where ename = 'Lacus'); 查询出工资比Lacus低的员工的编号、姓名、工资,按照工资的从小到大的顺序排序 select empno,ename,salary from emp where salary < (select salary from emp where ename = 'Lacus') order by salary; 查询出比部门20人数多的部门编号和部门人数 select deptno,count(empno) count_emp from emp group by deptno having count_emp > (select count(empno) from emp where deptno = 20); 当子查询返回多列时 all 所有的 any 任意一个 谁比所有的Lacus工资高 select empno,ename,salary from emp where salary > all(select salary from emp where ename = 'Lacus'); 谁比任意一个Lacus工资高 select empno,ename,salary from emp where salary > any(select salary from emp where ename = 'Lacus'); 关联子查询 哪些员工的薪水比本部门的平均薪水低？ select empno,ename,salary from emp e1 where salary < (select avg(salary) from emp e2 where e1.deptno = e2.deptno); 求每个部门的最高工资的员工的编号和姓名 select empno,ename,salary from emp e1 where salary = (select max(salary) from emp e2 where e1.deptno = e2.deptno); exists 哪些人是其他人的经理？ select empno,ename from emp where empno in (select mgr from emp where mgr is not null); select empno,ename from emp where empno = any (select mgr from emp where mgr is not null); select empno,ename from emp e1 where exists (select 1 from emp e2 where e1.empno = e2.mgr); exists 判断时 判断的是一个bool类型，不判断列值是否相同 书写时 直接按照where exists的方式使用 子查询中 select不需要返回一个列值，只需要返回一个bool值 所以不需要查询某一列 一般情况下我们用select 1(一个常量)做查询条件 谁和拉克丝同部门？列出除了拉克丝的人 select empno,ename from emp where deptno in (select deptno from emp where ename = 'Lacus') and ename <> 'Lacus'; select empno,ename from emp where deptno = any (select deptno from emp where ename = 'Lacus') and ename <> 'Lacus'; select empno,ename from emp e1 where exists (select 1 from emp e2 where e1.deptno = e2.deptno and e2.ename = 'Lacus') and ename <> 'Lacus'; 谁是拉克丝的下属？ select empno,ename from emp where mgr in (select empno from emp where ename = 'Lacus'); select empno,ename from emp where mgr = any (select empno from emp where ename = 'Lacus'); select empno,ename from emp e1 where exists (select 1 from emp e2 where e1.mgr = e2.empno and e2.ename = 'Lacus' ); in between...and... exists 都可以在使用前面添加not 用来表示不在这些数据中 哪些人不是别人的经理？ select empno,ename from emp where empno not in (select mgr from emp where mgr is not null); in的方式中需要去掉null值 否则判断不正确 select empno,ename from emp where empno <> all (select mgr from emp where mgr is not null); select empno,ename from emp e1 where not exists (select 1 from emp e2 where e1.empno = e2.mgr); 哪些部门没有员工？ select deptno from dept where deptno not in (select deptno from emp); select deptno from dept where deptno <> all (select deptno from emp); select d.deptno from dept d where not exists (select 1 from emp e where d.deptno = e.deptno); 表间关联查询 表A join 表B on 条件 查询出所有员工的编号、姓名、部门名和工作地点 select e.empno,e.ename,d.dname,d.location from emp e,dept d where e.deptno = d.deptno; 部分数据库不支持 select e.empno,e.ename,d.dname,d.location from emp e (inner) join dept d on e.deptno = d.deptno; inner 可略 默认按照inner的方式关联 内联联接 取两个表中的交集 外连接 左外连接 驱动表 left outer join 匹配表 on 条件 显示驱动表中的所有数据，匹配表进行匹配 查询出所有员工的编号、姓名、部门名和工作地点 把没有部门的员工也显示出来 select e.empno,e.ename,d.dname,d.location from emp e left outer join dept d on e.deptno = d.deptno; select e.empno,e.ename,d.dname,d.location from dept d right outer join emp e on e.deptno = d.deptno; 右外连接 驱动表 right outer join 匹配表 on 条件 显示匹配表中的所有数据，驱动表进行匹配 查询出所有员工的编号、姓名、部门名和工作地点 把没有员工的部门也显示出来 select e.empno,e.ename,d.dname,d.location from emp e right outer join dept d on e.deptno = d.deptno; select e.empno,e.ename,d.dname,d.location from dept d left outer join emp e on e.deptno = d.deptno; 全外连接 select e.empno,e.ename,d.dname,d.location from emp e left outer join dept d on e.deptno = d.deptno union select e.empno,e.ename,d.dname,d.location from emp e right outer join dept d on e.deptno = d.deptno; full outer mysql不支持 在其他数据库中均支持 select e.empno,e.ename,d.dname,d.location from emp e full outer join dept d on e.deptno = d.deptno; union 联合 返回两次查询的结果 去掉重复的数据 将不重复的结果都打印 一般情况下 用于全外连接和做合计相关的数据查询 注:联合的后段查询结果 不会显示相应的列名， 而是按照前段的查询结果显示 在程序接收数据时有可能造成接收困难 自链接 当前表与当前表的另外一个别名进行表间关联查询 显示员工的编号、姓名和他的上司姓名 select e1.empno,e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno; 模糊条件查询 like 需要配合通配符使用 % 写在查询的值的内部 例:like '%valueTemp%' 数据中存在valueTemp的值都查询 注:可以操作基本数字类型，只是操作时依然需要添加'' 因为mysql在这种情况， 将数据转换为varchar类型 _ 写在查询的值得内部 例:like 'valueTemp' 一共三个值 第二个值是valueTemp的数据 注:一般情况下是配合%使用的通配符，独立使用的情况比较少 例:like '_valueTemp%' 查询出第二个值是valueTemp的数据 查询出名字里第三个字母是e的员工的编号、姓名、工资、工作部门名称和工作地点 按照工资从大到小的顺序排序 select e.empno,e.ename,e.salary,d.dname,d.location from emp e join dept d on e.deptno = d.deptno where e.ename like '__e%' order by e.salary desc; 去重 distinct 加在列名之前 查询公司的职位都有哪些? select distinct job from emp; 注:一个select对应下 一般情况只有一个distinct， 并且一般情况下不会出现去重列后查询其他列 因为去重列是无法判断行数的，会导致数据混乱 复制表 create table 表名 select 列名 from 表名 复制结果集 复制表内数据 insert into 表名 select 列名 from 表名 在数据库中 对比于c/c++新增了一个新的类型date 专门用于存储日期类型的数据类型 在mysql date的延伸类型 time timestamp datetime year timestamp 默认存储当前时间，当修改同一行数据时会默认修改 在mysql中 时间函数 : now() : yyyy-mm-dd hh:mm:ss 年-月-日 时:分:秒 current_time() : hh:mm:ss 时:分:秒 可略写为curtime(); current_date() : yyyy-mm-dd 年-月-日 可略写为curdate(); 求出所有员工的编号、姓名和工作天数 select empno,ename,to_days(curdate())-to_days(hiredate) work_day from emp; select empno,ename,datediff(curdate(),hiredate) work_day from emp; 索引 index 类似于目录式另外的一个数据文件 当条件查询时可以加快 < >= <= in not in <> between...and... not between...and... 但是提高查询效率的同时，会降低插入、修改和删除的效率，因为索引文件 会与表本身同步更新 索引文件会根据表中数据的增大而增大，占用硬盘空间，容易造成服务器硬盘溢出 创建一个一般索引 create index 名称 on 表名(列名); 删除一个一般索引 drop index 名称 on 表名 索引还区分为 组合索引，主键索引，聚簇索引 事务 begin 在mysql中默认情况下 想要进行事务处理 需要先进行一次begin操作 begin执行后才可以进行一下操作 commit 提交 确定begin之后的操作 使其不可以rollback rollback 撤销 begin之后的操作可以退回到没有操作时的状态 事务操作只可以操作上一次事务，无法连续撤销 set autocommit = 1/0 可以设置开启事务或关闭事务 contraint 约束 not null 不可为空 unique 不可重 primary key 主键(主键索引) default value 默认值 auto_increment 自增长列 foreign key 外键约束(组合索引) 在表的基本列写完之后，按照以下方式: foreign key(本表列名) references 主表名(主表列) 约束可以提高插入数据的准确性，但是因为系统需要匹配约束条件，所以会降低修改数据的效率 修改数据表结构 alter table 表名 action action : add 添加 set 设置 change 改变 modify 修改 CHANGE 对列进行重命名或更改列的类型，需给定旧的列名称和新的列名称、当前的类型 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称） drop 删除 engine 改变类型 rename 改名 什么是触发器？ 什么是事务？什么是锁？ 什么叫视图？游标是什么？ 数据库中常见的几种约束有哪些？ 分别代表什么意思？如何使用? 1， 数据库编程（每小题3分） 有如下表： Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 注：S#为学生编号，C#为课程编号，T#为教师编号 查询“001”课程比“002”课程成绩高的所有学生的学号 select s1.S# from SC s1 join SC s2 on s1.S# = s2.S# and s1.score > s2.score and s1.C# = 001 and s2.C# = 002; 查询平均成绩大于60分的同学的学号和平均成绩 select S#,avg(score) avg_score from SC group by S# having avg_score > 60; 查询所有同学的学号、姓名、选课数、总成绩 select s.S#,s.Sname,count(sc.C#) count_course, sum(sc.score) sum_score from Student s join SC sc on s.S# = sc.S# group by s.s#; 查询没学过“叶平”老师课的同学的学号、姓名 select S#,Sname from Student s1 where not exists select 1 from Student s2 join SC sc on s.S# = sc.S# where s1.S# = s2.S# and sc.C# = (select C# from Course where T# = (select T# from Teacher where Tname = '叶平')); 删除学习“叶平”老师课的SC表记录 delete from SC where C# = (select C# from Course c join Teacher t on c.T# = t.T# and t.Tname = '叶平'); 查询不同老师所教不同课程平均分从高到低显示 select avg(score) avg_score from SC sc join Course c on sc.C# = c.C# group by c.T# order by avg_score desc; 查询两门以上不及格课程的同学的学号及其平均成绩 select S#,avg(score) avg_score from SC sc1 where exists(select 1 from (select S#,score from score where score < 60) sc where sc1.S# = sc.S# group by sc.S# having count(sc.S#) >= 2) group by S#; 查询各科成绩前三名的记录:(不考虑成绩并列情况) select score,C# from SC sc1 where sc1.score = (select score from SC sc2 where sc1.C# = sc2.C# order by score desc limit 1) or sc1.score = (select score from SC sc2 where sc1.C# = sc2.C# order by score desc limit 1,1) or sc1.score = (select score from SC sc2 where sc1.C# = sc2.C# order by score desc limit 2,1) order by C#; 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 select C#,max(score) max_score ,min(score) min_score from SC group by C#; 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; select S#,Sname from Student s where exists( select 1 from SC sc1 where C# = 1 and exists (select 1 from SC sc2 where C# = 2 and sc1.S# = sc2.S#) and s.S# = sc1.S#); 主键：唯一确定一条记录。可以由一个或多个字段产生。 create table student(stuId int not null auto_increment primary key , stuName varchar(32) not null default 'NewStu' , stuSex char(7) not null default 'male' , stuAge int not null default 18 , stuPart varchar(32) not null default 'computer'); create table teacher(tecId int not null auto_increment , tecName varchar(32) not null default 'NewTec' , salary float not null default 3000 , primary key(tecId)); 外键：若表中的某字段的值需要依赖于其他表中的某个字段，则把该字段申明为外键 create table course(couId int not null auto_increment , couName varchar(32) not null default 'NewCou' , tecId int not null , primary key(couId) , foreign key(tecId) references teacher(tecId)); create table score(stuId int not null , couId int not null , mark float not null default 60 , primary key(stuId, couId) , foreign key(stuId) references student(stuId) , foreign key(couId) references course(couId)); 删除 mysql> delete from student where id = 10009; //如果不加条件--delete from student，则会将表中数据全部清除 修改重置 update student set age = 20 where id = 10005; //如果不加条件--update student set age = 20，则会将表中所有age字段的设置为20 mysql> update student set age=age-4,dept='math' where id = 10007; mysql> update student set age=age+3 where age >= 20;