Mysql命令

最近又学习了一遍Mysql数据库，之前学的有点不求甚解，总是感觉一头雾水。现在感觉好多了，分享一下这些命令，顺便巩固一下。

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   分组之后的过滤
                    order by
                    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、查询emp中最高薪水人的名字

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

3、查询薪水在平均薪水之上的雇员的名字


4、查询雇员的名字和所在部门的名字


5、查询薪水在在本部门平均薪水之上的雇员的名字


6、查询每个员工的薪水的等级,员工的姓名


7、查询每个部门的平均薪水的等级,部门的编号


8、查询雇员的名字，所在部门的名字，工资的等级


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


10、查询雇员中是经理人的名字


11、查询平均薪水最高的部门的编号和名称



12、查询部门经理人中平均薪水最低的部门名称  


13、查询薪水最高的前5名雇员编号，名称，薪水


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


15、查询部门的名字和部门的人数(如果部门里没有人数，显示0个)


16、查询员工的编号，工资和所在部门的平均工资

---

下面是数据库的结构图：

---

下面是可以使用的数据库小例子：

/********************************部门表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;

下面是答案：

1.
select ename from EMP
where sal = (select max(sal) from EMP)

2.
select t1.ename,t2.deptno 
from EMP t1
inner join (select deptno,max(sal) max_sal from EMP
group by deptno) t2
on t1.deptno = t2.deptno and sal = t2.max_sal 

3
select ename ,sal from EMP
where sal >(select avg(sal) from EMP)

4.
select ename,dname from EMP,DEPT
where DEPT.deptno = EMP.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 sal > t2.avg_sal AND t1.deptno = t2.deptno

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

7.
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
    t2.ename, t1.ename
from
    EMP t1,EMP t2
where
    t1.mgr = t2.empno;

10.
1.经理的编号
select distinct mgr from EMP;
2.把1作为条件
select 
    ename
from 
    EMP
where 
    empno in (select distinct mgr from EMP)

11.
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.
create view xx as
select * from EMP
where empno in (select distinct mgr from EMP)

select t.deptno,t.dname
from DEPT t
where deptno in (
        select t1.deptno
        from (select avg(sal) avg_sal,deptno from xx group by deptno) t1
        where t1.avg_sal =(select min(t2.avg_sal) from (select avg(sal) avg_sal,deptno from xx group by deptno) t2)
)

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

14.
select ename,empno,sal
from EMP
order by sal desc
limit 6,5

15.
select t1.dname ,t2.num,t1.deptno
from 
    DEPT t1
INNER JOIN 
    (select count(*) num,deptno from EMP GROUP BY deptno )t2
on t1.deptno = t2.deptno    

16.
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
innor join 
    (select avg(sal) avg_sal,deptno from EMP group by deptno) t2
on 
    t1.deptno = t2.deptno;

---