MYSQL 学习笔记（在终端里的命令操作）

mysql数据库
库-> 表
显示所有表
show databases;

创建新的数据库
create database 数据库名称；//创建数据库
数据库命令之一：show databases;// 一定要以分号结尾
cmd  mysql -u rost -p   // 启动服务
show create database 数据库名称； //搜索数据库的信息
create  database   db2 character set gbk(字符集);
drop database 库名； // 删除数据库

使用库shoe
use 库名； //使用库
show  tables;   //显示当前库中的所有表
//创建一个员工表（emp）姓名（ename）工资（sal）
//创建一个学生表 （student ） 有学号（id） 姓名（name)  性别（sex）
 

查看表字段信息‘
desc  emp;

查看表信息
show  create  table  emp;
修改表名称 
rename  table    原名 to 新名；
rename   table  emp  to emp1;
修改表属性  字符集
enamede table   empl charset=gbk;

添加表字段
alter table 表名 add 字段名  类型；  //添加到最后
alter table 表名 add 字段名  类型   first;   //添加到最前面;
alter table 表名 add 字段名  类型   after  字段名2； // 在字段名2后面插入字段
 删除表字段
alter table 表名  drop 字段名；

修改表字段和位置
alter table  表名 change 原字段名  新字段名  类型 first;
alter table  表名 change 原字段名  新字段名  类型 after  字段名2；
删除表
drop  table 表名；

练习：
创建一个员工表
 create  table  emp(id char(20),name char(20));
修改表的字符集为gbk;
alter table emp  charset=gbk;
修改表明为temp
rename  table emp to temp;
给表名添加age字段在id后面；
alter table  temp add age int after id;
给表名添加sal字段在最后；
alter table temp add sal  double;
修改sal字段名为salary;
alter table temp change sal  salary double;
修改age 字段到salary字段的垢面
alter table temp modify age   int after salary;
删除salary字段
alter table temp  drop salary;
删除表
drop table temp;

数据库的查询 ，插入，
//创建一个学生表 （student ） 有学号（id） 姓名（name)  性别（sex）
create table  student(
id varchar(20),
name varchar(20),
sex  int,
math int,
English int);
)
//插入所有字段信息
insert  into student values()；
insert  into student values('001','张三',0,80,90);
//插入指定字段信息
insert  into student (id,name) values('001','张三');
//批量 插入
insert  into student (id,name) values('001','张三'),('002','李华');

//查询所有信息
select* from  student;
//查询表中某些字段的信息
select   id,name from student;
//条件查询
select * from student where  id='003';
//修改数据
updata  student  set  id='004';
update  student  set  id='004' where  name='aa';

//删除数据
delete  from student  where id='002';
//告诉数据库传过的值要以gbk形式
set name gbk;   

//练习
删除数学成绩低于80的
delete from student where  math<80;

什么是事务？
就是数据库中执行SQL语句的工作单位，此工作单元不可拆分，能够保证全部成功或全部失败。
//查看数据库自动提交的状态
show  variables like  '%autocommit%';
设置事务自动提交的开关   0   关闭  1    开启
set  autocommit=0/1;

提交事务commit;
回滚事务rollback;
保存回滚点savepoint    s1;
回滚到某个回滚点rollback   to   s1;

//事务操作步骤
1、查看数据库自动提交的状态
show  variables like  '%autocommit%';
2、设置事务自动提交的开关为关；
set  autocommit=0；
再查看一次
show  variables like  '%autocommit%';
3、关闭事务成功后，向表里插入数据
insert  into student values('001','张三',0,80,90);
4、打开另外一个终端打开数据库，查看表中是否出现插入的记录；（并没有）
select * from temp；

5、返回之前的终端提交事务
输入commit;
6、在返回新打开的终端查询记录；（存在）
select * from temp；

//MYSQ5.0版本
//事务开始的步骤
1、事务开始begin；
2、查看已有表里面的数据；
3、插入一条数据；
4、查询表里的数据看一下是否遍变；（会变）
5、打开另外一个终端打开数据库，查看表中是否出现插入的记录；（无，原因：因为之前终端的事务并没有提交。）
6、返回之前的终端提交事务commit;
查看数据是否更新，两个终端都看一下；（有）

   
不适用begin会出现错误；
 //去除重复的数据
select distinct(sex)  from temp; 

//比较运算符
>,<,=,!=,<=,>=,is null,is not null;
练习
1、查 工资小于等于1600的员工姓名和工资
select  ENAME,SAL from emp where SAL<=1600;
2、查询部门编号是20的员工姓名，职位和编号
 select  ENAME,DEPTNO,JOB   from emp where DEPTNO=20;
3、职位是manager的员工姓名和职位
 select  ENAME,JOB from emp where  JOB='manager';
4、查询不是10号部门的所有员工姓名和部门编号
 select    ENAME,DEPTNO from  emp where  JOB!=10;

AND和OR（AND 表示两者并列前后两者不同，or表示前后两者相同）
1、查询10号部门工资大于 1000小于2500的姓名，职位，工资，部门编号。
 select  ENAME,DEPTNO,JOB,SAL   from emp where deptno=10 AND SAL<2500;
2、查询10号部门和20号 部门的员工所有信息
 select  *   from emp where DEPTNO=20 OR DEPTNO=10;

IN 和 NOT IN
是否与给定集合中的某个值匹配
1、查询10号部门和20号 部门的员工所有信息
  select  *   from emp  where  deptno in(10,20);
2、查询不属于10号部门和20号 部门的员工所有信息
select  *   from emp  where  deptno not  in(10,20);

Like 
_ :表示一个字符
%：表示0个或多个
1、查询名字里有'A'的人的所有信息
 select * from emp where  ename like '%A%';
2、查询名字里第二个字母是'A'的人的信息
 select * from emp where  ename like '_A%';

排序
order  by
1、查询所有员工信息，按部门编号排序。
 select * from emp order by deptno;

分页
limit;
1、查询按部门编号排序以后第4到6条记录。
 select * from emp order by deptno limit  3,4;//从下标0开始，3表示从第4条开始，4表示查询4条；

聚合函数
sum() 总和
1、查询所有员工的工资总和
select sum(sal) from emp;
avg()平均值
2、查询所有员工的平均工资
select avg(sal) from emp;
max()最大值
3、查询工资最高的员工信息(子查询)
 select * from emp where sal=(select max(sal)from emp);
min()最小值
4、查询工资最低的员工信息（子查询）
 select * from emp where sal=(select min(sal)from emp);
count 计数
5、查询有多少位员工
select count(*) from emp;

分组
group by

1、查询各个部门的平均薪资。
select avg(sal) ,deptno from  emp group by deptno;
2、查询平均薪资大于2000的部门，显示部门编号和平均薪资
（提示：having）
 select avg(sal) ,deptno from  emp group by deptno  having  avg(sal) > 2000;

子查询
1、查询工资最高的员工信息(子查询)
 select * from emp where sal=(select max(sal)from emp);

ifnull（字段，值）判断字段是否为Null  如果为null那么换成值显示。 

练习：
1、查询没有上级领导的员工的编号，姓名，工资
 select empno,ename,sal,job  from emp where  MGR is null;
2、查询emp表中没有绩效的员工的姓名，职位，工资，及绩效
 select ename,job,sal,comm from emp where  comm is null;
3、查询emp表中含有奖金的员工编号，姓名，职位，及奖金。
<1>select ename,job,sal,comm from emp where   sal is not null;
注意：另一种当位为null时将null替换为0
ifnull(comm,0) c
<2>select ename,job,sal, ifnull(comm,0) c from emp ;
<3>select t.* from (select empno,ename,job, ifnull(comm,0) c from  emp) t where
<4>select  empno,ename,job,comm from  emp where comm <> 0;
4、查询含有上级领导的员工的姓名，工资，以及上级领导编号；
select ename,sal,mgr from emp where mgr is not  null;
5、查询emp表中名字以‘S’开头的所有员工的姓名；
select ename from emp where ename like 'S%';
6、查询emp表中名字最后一个字符是's'的员工的姓名；
 select ename from emp where  ename like '%S';
7、查询倒数第二个字符是E的员工姓名；
select ename from emp where  ename like '%E_';
8、查询emp表中员工的倒数第三个字符是N的员工姓名；
 select ename from emp where  ename  like '%N__';
9、查询EMP表中员工的名字中包含A的员工姓名；
select ename from emp where  ename like '%A%';
10、查询emp表中名字不是以K开头的员工姓名；
 select ename from emp where  ename not like 'K%';

11、查询名字中含有A的员工姓名；
select ename from emp where  ename not like '%A%';
12、查询职位是CLERK的员工人数；

 select count(job) from emp where  job ='clerk';
13、查询职位是 salesman的员工的最高薪水；
SELECT max(sal) from emp where job='salesman';
14、查询emp表中所有员工的奖金之和和平均值；
select  sum(comm),avg(ifnull(comm,0)) from emp;

15、 查询工资在1000-2000之间的员工姓名，编号，职位，工资。
select empno,ename,job,sal from emp where  sal>1000 and sal<2000;

16、查询于WARD同职位的员工的信息；
select * from  emp where deptno=(select deptno from emp where ename='WARD');

17、最早和最晚入职日期；
 select max(HIREdate),min(HIREdate) from emp;
 

关联查询
一定写条件；

笛卡儿积

1、查询每个员工的所在的城市；
select  loc,ename from emp,dept where emp.deptno = dept.deptno;
2、查询每个员工的员工编号 ，姓名，上级编号和姓名；
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
3、查询10号部门所在位置，部门名字，以及10号部门的平均薪资。
select loc,dname,avg(sal) from emp,dept where  emp.deptno = dept.deptno and emp.deptno=10;
4、查询部门在 NEW YORK 的员工；
select * from emp,dept where   emp.deptno = dept.deptno  and  dept.loc='NEW YORK';
5、查询每个部门的总薪资和部门的部门编号，部门名称，部门位置
select  sum(sal),dept.deptno,dname,loc  from emp,dept where  emp.deptno = dept.deptno group by dname；
6、查询SALES部门的平均薪资；
 select avg(sal) from emp,dept where  emp.deptno = dept.deptno and dept.dname='SALES';
7、有一张竞赛队伍表，有A,B,C,D 查询出所有的组合方式；