复习第三天： 数据库

1、连接数据库
mysql -h ip地址 -P 端口号 -u 用户名 -p 密码

2、显示所有数据库
show databases;

3、创建数据库
create database 数据库名字 default charset=utf8;

4、使用数据库
use 数据库名字;

5、删除数据库
drop 数据库名字;

6、查询数据库下所有的表
use 数据库名;
show tables;

7、创建表
create table 表名(
列名 类型,
列名 类型,
列名 类型
);

8、常用的类型
数字 int,float,decimal
字符串 char,varchar,longtext
日期 date,datetime

9、删除表
drop table 表名

10、约束
1、主键约束
2、非空约束
3、唯一约束
4、默认约束
5、外键约束

/*外键表*/
create table student(
    id int primary key,         
    name varchar(100) not null,
    idcard char(18) unique,
    addres varchar(100) default '郑州',
    gid int not null,
    foreign key(gid) references grade(id)
);


/*主键表*/
create table grade(
    id int primary key,
    name varchar(100) not null
);

11、描述表的信息
desc 表名

12、显示表的创建sql语句
show create table 表名

13、主键的生成策略
1、int 自动增长 auto_increment
2、字符串 uuid 了解

create table grade(  
    id int auto_increment primary key,
    name varchar(100) not null
);
create table grade2(  
    id char(36) primary key,
    name varchar(100) not null
);
insert into grade(name) values('a');
insert into grade(name) values('b');

insert into grade2(id,name) values(uuid(),'a');
insert into grade2(id,name) values(uuid(),'b');

14、基本的增删改查

create table student(
    id int auto_increment primary key,
    name varchar(100) not null,
    sex char(1) not null,
    address varchar(100) default '郑州',
    phone varchar(11),
    birthday date
);

insert into student(name,sex,address,phone,birthday) values('老王','男','开封','11111111111','1998-2-2');

select * from student;
select name,phone from student;
select name 姓名,phone 电话 from student;

delete from student where id = 3;

update student set address='开封' where id = 5;
update student set sex='女',address='曼谷' where id = 5;

UPDATE 
    student 
SET 
    sex='女',address='曼谷' 
WHERE 
    id = 5;

15、单表查询

/*1、查询所有EMP信息*/
select * from EMP;

/*2、查询所有job*/
select job from EMP;

/*3、去重：查询所有job*/
select distinct job from EMP;


/*4、去重：查询所有deptno,job的组合*/
select distinct deptno,job from EMP;

/*5、条件：查询工资大于2000的*/
select * from EMP where sal > 2000;


/*6、条件：查询工资大于2000的并且部门编号是10的*/
select * from EMP where sal > 2000 and deptno = 10;

/*7、条件：查询工资2000-3000之间的*/
select * from EMP where sal >= 2000 and sal <= 3000;
select * from EMP where sal between 2000 and 3000;

/*8、模糊：查询以S开头的员工信息*/
select * from EMP where ename like 'S%';

/*9、模糊：查询含有S的员工信息*/
select * from EMP where ename like '%S%';

/*10、模糊：查询含第三个字符是R的员工信息*/
select * from EMP where ename like '__R%';

/*11、范围：查询部门编号是10，20的员工信息*/
select * from EMP where (deptno = 10) or (deptno=20);
select * from EMP where deptno in (10,20);

/*12、空：查询没有有奖金的员工信息*/
select * from EMP where comm is null;

/*13、空：查询奖金大于400的员工信息*/
select * from EMP where comm > 400;

/*14、空：查询员工的编号，年薪  null参与的运算，结果还是null*/
select empno 编号,(sal+ifnull(comm,0))*12 年薪 from EMP;


/*15、聚合：统计员工的数量*/
select count(*) from EMP;

/*16、聚合：统计有奖金员工的数量*/
select count(*) from EMP where comm is not null;
select count(comm) from EMP;

/*17、聚合：最高的工资，最低的工资，平均工资,工资的总和*/
select max(sal),min(sal),avg(sal),sum(sal) from EMP;


/*
    分组需要注意：

        1、分组之后只能查询两种 
                1、被分组的列  
                2、聚合函数

        2、数据过滤
                1、过滤的数据是分组之前，where
                2、过滤的数据是分组之后，having

        3、关键词的顺序
                select
                from 
                where    分组之前的过滤
                group by
                having   分组之后的过滤
                limit

*/

/*18、分组：每个部门的平均工资~~~*/

select deptno,avg(sal)
from EMP
group by deptno;


/*19、分组：每个部门员工工资高于1000的平均工资*/

select deptno,avg(sal)
from EMP
where sal > 1000
group by deptno;

/*20、分组：每个部门员工工资高于1000的平均工资,平均工资高于2000*/
select deptno,avg(sal)
from EMP
where sal > 1000
group by deptno
having avg(sal)>2000;


select deptno,avg(sal) avg_sal
from EMP
where sal > 1000
group by deptno
having avg_sal>2000;

/*21、分组：每个部门每个工种的最高工资*/

select deptno,job,max(sal)
from EMP
group by deptno,job;
/*22、排序：查询所有员工信息，按照工资排序*/

select * from EMP
order by sal asc;

select * from EMP
order by sal desc;

/*23、排序：查询所有员工信息，按照按照部门正序，按照工资倒序*/
select * from EMP
order by deptno,sal desc;


/*24、分页*/
select * from EMP
order by empno;

select * from EMP
order by empno
limit 3;

select * from EMP
order by empno
limit 2,3;


/*25、分页：按照编号排序，每页显示2(page_size)条，查第5(page_now)页信息*/

/*
0 1
2 3
4 5
...
limit (page_now-1)*page_size,page_size
*/
select * from EMP
order by empno
limit 8,2;

16、三种映射关系

/* 
1:1 
任选一个表当作主键表，另一个表当作外键表 
并且外键列必须唯一 
*/

drop table if exists husband;
drop table if exists wife;

create table wife(
    id int auto_increment primary key,
    name varchar(100)
);

create table husband(
    id int auto_increment primary key,
    name varchar(100),
    wid int unique,
    foreign key(wid) references wife(id) 
);


/*
1:M
    设置外键
*/
create table dept(
    id int auto_increment primary key,
    name varchar(100)
);

create table emp(
    id int auto_increment primary key,
    name varchar(100),
    did int,
    foreign key(did) references dept(id) on delete cascade
);
/*
M:N
    创建中间表
    一般中间表也有用
*/

create table student(
    id int auto_increment primary key,
    name varchar(100)
);

create table subject(
    id int auto_increment primary key,
    name varchar(100)
);

create table student_subject(
    id int auto_increment primary key,
    stuid int,
    subid int,
    foreign key(stuid) references student(id), 
  foreign key(subid) references subject(id)
);

17、关联查询

/*1、内连接*/
select * from DEPT,EMP
where DEPT.DEPTNO = EMP.DEPTNO;

select  
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT
inner join 
    EMP
on 
    DEPT.DEPTNO = EMP.DEPTNO;




/*查询员工的编号，姓名，所在部门的名字*/
select 
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT,EMP
where 
    DEPT.DEPTNO = EMP.DEPTNO;




select 
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT,EMP
where 
    DEPT.DEPTNO = EMP.DEPTNO
and
    DEPT.DEPTNO = 10;


/*外连接*/
select  
    t2.EMPNO,t2.ENAME,t1.DEPTNO,t1.DNAME
from 
    DEPT t1
left join 
    EMP t2
on 
    t1.DEPTNO = t2.DEPTNO;


/*创建自关联的表*/

create table EMP2(
    id int auto_increment primary key,
    name varchar(100),
    mgr int foreign key(mgr) references EMP2(id)
);




/*查询员工的编号，姓名，上级名字*/
select t1.empno,t1.ename,t2.ename 
from EMP t1,EMP t2
where t1.mgr = t2.empno

select t1.empno 员工的编号 ,t1.ename 员工的姓名,t2.ename 上级的姓名
from EMP t1 left join EMP t2
on t1.mgr = t2.empno;

小练习

/*1、一个表能完成不？*/

1、查询emp中最高薪水人的名字

    1、查询最高的薪水
    select max(sal) from EMP;

    2、将1作为条件
    select ename from EMP
    where sal = (select max(sal) from EMP);


2、查询每个部门中的最高薪水人的名字和所在的部门编号

    1、各个部门的最高薪水
    select max(sal),deptno from EMP
    group by deptno;

    2、将1的结果作为一个新的表，联表查询
    select t1.ename,t1.deptno
    from 
        EMP t1
    inner join 
        (select max(sal) max_sal,deptno from EMP group by deptno) t2
    on t1.deptno = t2.deptno and sal = t2.max_sal;



3、查询薪水在平均薪水之上的雇员的名字
    select ename from EMP where sal >(select avg(sal) from EMP);

4、查询雇员的名字和所在部门的名字
    select t1.ename,t2.dname
    from EMP t1,DEPT t2
    where t1.deptno = t2.deptno;

5、查询薪水在在本部门平均薪水之上的雇员的名字
        select t1.ename,t1.deptno
        from 
            EMP t1
        inner join 
            (select avg(sal) avg_sal,deptno from EMP group by deptno) t2
        on t1.deptno = t2.deptno and sal > t2.avg_sal;

6、查询每个员工的薪水的等级,员工的姓名
        select * from EMP;
        select * from SALGRADE;

        select t1.ename,t2.grade
        from EMP t1,SALGRADE t2
        where t1.sal between t2.losal and t2.hisal;



7、查询每个部门的平均薪水的等级,部门的编号
    1、各个部门的平均薪水
        select avg(sal),deptno from EMP group by deptno;
    2、将1的结果当作一个表
        select
                t1.grade,t2.deptno
        from 
            SALGRADE t1,(select avg(sal) avg_sal,deptno from EMP group by deptno) t2
        where 
            t2.avg_sal between t1.losal and t1.hisal



8、查询雇员的名字，所在部门的名字，工资的等级
        select t1.ename,t2.dname,t3.grade
        from 
            EMP t1,DEPT t2,SALGRADE t3
        where 
            t1.deptno = t2.deptno
        and
            t1.sal between t3.losal and t3.hisal;

        select t1.ename,t2.dname,t3.grade
        from 
            EMP t1
        inner join
            DEPT t2 
        on 
            t1.deptno = t2.deptno
        inner join
            SALGRADE t3
        on
            t1.sal between t3.losal and t3.hisal;



9、查询雇员的名字和其经理的名字

        select employee.ename,employer.ename
        from EMP employer,EMP employee
        where employee.mgr = employer.empno;


10、查询雇员中是经理人的名字
        select * from EMP;

        1、经理上的编号
        select distinct mgr from EMP;
        2、将1当作条件
        select 
                ename
        from 
                EMP
        where 
                empno in(select distinct mgr from EMP);


11、查询平均薪水最高的部门的编号和名称
        1、每个部门的平均薪水
        select avg(sal) avg_sal,deptno from EMP group by deptno;
        2、求1表中的最高平均薪水
        select max(t.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t
        3、将2的结果当作条件
        select t1.deptno
        from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
        where t1.avg_sal = (select max(t2.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t2)
        4、将3作为条件
        select t.deptno,t.dname
        from DEPT t
        where deptno in (
                select t1.deptno
                from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
                where t1.avg_sal = (select max(t2.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t2)
        );


12、查询平均薪水等级最低的部门的部门名称
  select t3.dname
      from DEPT t3
      where deptno in (
            select t1.deptno
            from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
            where t1.avg_sal =(select min(t.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t)
      );

13、查询部门经理人中平均薪水最低的部门名称  
             (1)查询部门经理
              select* from EMP where empno in(select distinct mgr from EMP);

             (2)讲(1)的结果作为条件
              select t3.dname,t3.deptno
              from DEPT t3
              where deptno in (
                   select t1.deptno
                   from (select avg(sal) avg_sal,deptno from ((select* from EMP where empno in(select distinct mgr from EMP))as a) group by deptno) t1
                   where t1.avg_sal =(select min(t.avg_sal) from (select avg(sal) avg_sal,deptno from ( (select* from EMP where empno in(select distinct mgr from EMP))as a) group by deptno) t)
              );



14、查询薪水最高的前5名雇员编号，名称，薪水
        select empno,ename,sal
        from EMP 
        order by sal desc
        limit 5;


15、查询薪水最高的第6名到第10名雇员编号，名称，薪水

        select empno,ename,sal
        from EMP 
        order by sal desc
        limit 5,5;


16、查询部门的名字和部门的人数(如果部门里没有人数，显示0个)
        select t1.dname,ifnull(t2.num,0)
        from 
            DEPT t1
        left join
                (select count(*) num,deptno from EMP group by deptno) t2
        on t1.deptno = t2.deptno;


17、查询员工的编号，工资和所在部门的平均工资
          select 
                    empno,ename,sal,t2.deptno,(select avg(sal) from EMP t1 where t1.deptno = t2.deptno)
                from 
                    EMP t2;

                select 
                    empno,ename,sal,t2.deptno,t2.avg_sal 
                from 
                    EMP t1
                inner join 
                    (select avg(sal) avg_sal,deptno from EMP group by deptno) t2
                on 
                    t1.deptno = t2.deptno;

18、函数

参考网站
1、字符串

1、length

        select LENGTH("abc中国");
        select ename,LENGTH(ename) from EMP;
2、concat
        select CONCAT("a","bc","xx");

3、str_to_date
        select STR_TO_DATE("2018年02月03日","%Y年%m月%d日");

        create table tt(
                id int auto_increment primary key,
                birthday date
        );
        insert into tt(birthday) values("2018-2-4");
        insert into tt(birthday) values(STR_TO_DATE("2018年02月03日","%Y年%m月%d日"));
        select * from tt;

2、数字

1、floor，ceil
        select FLOOR(1.56),CEIL(1.16);
2、rand  
        select RAND(10);

3、日期

1、now
        select NOW();
        select DAYOFWEEK('2018-08-26');

2、date_format  日期转字符串

        select DATE_FORMAT(NOW(),"%Y年%m月%d日 %H时%i分%s秒");
        select DATE_FORMAT("2018-2-3","%Y年%m月%d日");

19、视图

创建视图
create view myview 
as
select avg(sal) avg_sal,deptno from EMP group by deptno;


使用视图
select * from myview;

20、事务

(1)所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
(2)事务四大特性(简称ACID)：
  1.原子性：一组事务，要么成功；要么撤回。
  2.稳定性 ：有非法数据（外键约束之类），事务撤回。
  3.隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
  4.可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， 
           innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。     


(3)事务语句：
   开启begin;  或者 start transaction;
   提交commit;
   回滚rollback;

(4)例子：

drop table bank;

create table bank(
    id int primary key,
    money int
);

insert into bank values(1,10);
insert into bank values(2,1);


select * from bank;

begin;

update bank 
set money = money-5
where id = 1;




update bank 
set money = money+5
where id = 2;

rollback;

commit;


select * from bank;

begin;

savepoint p1;

update bank 
set money = money-1
where id = 1;

savepoint p2;

update bank 
set money = money-1
where id = 1;

savepoint p3;

update bank 
set money = money-1
where id = 1;

savepoint p4;


rollback to p4;
commit;

21，pymysql基本操作

(1)增删改格式
import pymysql

#连接对象
my_conn = pymysql.connect(host="localhost", user="root", password="root", database="laowang", port=3306, charset="utf8")

#执行工具
my_cursor = my_conn.cursor()

#执行sql
my_cursor.execute("delete from student where id = 3");

#提交，增删改
my_conn.commit();

#关闭
my_conn.close()




(2)查询格式
import pymysql

#连接对象
my_conn = pymysql.connect(host="localhost", user="root", password="root", database="laowang", port=3306, charset="utf8")

#执行工具
my_cursor = my_conn.cursor()

#执行sql
my_cursor.execute("select * from student");

#查询，元组
content = my_cursor.fetchall()
print(content)


#关闭
my_conn.close()

附录一 数据库表

/********************************部门表dept********************************/
/*创建表*/
DROP TABLE  IF EXISTS DEPT;  
CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,  
    DNAME VARCHAR(14) ,  
    LOC VARCHAR(13) 
);
/*插入数据*/
INSERT INTO DEPT VALUES  
(10,'ACCOUNTING','NEW YORK'),  
(20,'RESEARCH','DALLAS'), 
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');  
/*查询数据*/
SELECT * FROM DEPT;


/********************************员工表emp********************************/
/*创建表*/
DROP TABLE  IF EXISTS EMP;  
CREATE TABLE EMP(
    EMPNO INT PRIMARY KEY,  
    ENAME VARCHAR(14) ,  
    JOB VARCHAR(9),  
  MGR INT,  
  HIREDATE DATE,  
  SAL DECIMAL(7,2),  
  COMM DECIMAL(7,2),  
  DEPTNO int REFERENCES DEPT 
);

/*插入数据*/
INSERT INTO EMP VALUES  
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),  
(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30), 
(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-7-13',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);  
/*查询数据*/
SELECT * FROM EMP;



/********************************工资等级表salgrade********************************/
/*创建表*/
DROP TABLE  IF EXISTS SALGRADE;  
CREATE TABLE SALGRADE(
    GRADE INT PRIMARY KEY,  
  LOSAL INT,  
  HISAL INT 
);  
/*插入数据*/
INSERT INTO SALGRADE VALUES 
(1,700,1200), 
(2,1201,1400), 
(3,1401,2000),  
(4,2001,3000),  
(5,3001,9999);  
 /*查询数据*/
SELECT * FROM SALGRADE;