学习mysql数据库的基本操作

原创 368阅读 · 时间2019年4月11日 10:33

三个表的结构介绍.png安装啥的,百度就可以了,很简单的。

下面是数据库命令及DEPT,EMP两个表数据查询。

1、连接数据库1、

sudo mysql -uroot -p123456   连接本机

 mysql数据库设置密码:grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;


mysql -h 192.168.12.202 -p 3306 -u root -p

sodo mysql -h 127.0.0.112.171 -p 3306 -u root -p


mysql -h ip地址 -P 端口(3306)号 -u 用户名 -p 密码

mysql -h ip地址 -P 端口号 -u 用户名 -p 密码

mysql -h 127.0.0.1 -u root -p

mysql -h 192.168.12.202 -uroot -p


2、显示所有数据库

show databases;


3、创建数据库

create database 数据库名字 charset=utf8;


4、切换使用数据库

use 数据库名字;

5、删除数据库

drop 数据库名字;



6、查询数据库下所有的表

use 数据库名;

show tables;


7、创建表

create table 表名(

列名 类型,

列名 类型,

列名 类型

);


8、常用的类型

数字int,float,decimal

字符串char,varchar,longtext

日期date,datetime


bit 默认是一个


9、删除表

drop table 表名



10、约束

1、主键约束   primary key,默认是唯一,标识这一行数据与其它不同。非空并唯一

2、非空约束   not null,默认是NULL

3、唯一约束   unique

4、默认约束   default

5、外键约束   foreign key



/*外键表*/

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、基本的增删改查


1.增:

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');


2.查:

select * from student;

select name,phone from student; 

select name 姓名,phone 电话 from student;


3.删

delete from student where id = 3;


4.改: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;

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、查询平均薪水等级最低的部门的部门名称

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


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;



评论

踏踏实实,做好自己!

相关文章推荐 ?
近七日热文推荐 !
回到顶部