mysql——2-18数据库

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#为教师编号

1. 查询“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;

2. 查询平均成绩大于60分的同学的学号和平均成绩
   select S#,avg(score) avg_score from SC group by S#
   having avg_score > 60;

3. 查询所有同学的学号、姓名、选课数、总成绩
   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#;

4. 查询没学过“叶平”老师课的同学的学号、姓名
   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 = '叶平'));

5. 删除学习“叶平”老师课的SC表记录
   delete from SC where C# =
   (select C# from Course c
   join Teacher t on c.T# = t.T# and
   t.Tname = '叶平');

6. 查询不同老师所教不同课程平均分从高到低显示
   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;

7. 查询两门以上不及格课程的同学的学号及其平均成绩
   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#;

8. 查询各科成绩前三名的记录:(不考虑成绩并列情况)
   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#;

9. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
   select C#,max(score) max_score ,min(score) min_score from SC group by C#;

10. 查询学过“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;