Mysql第一、二天

2020-08-01 本文已影响0人 __method__

关系数据库

是指一些相关的表和其他数据库对象的集合。对于关系数据库来说，关系就是表的同义词。表是由行和列组成（类似二维数组的结构）

结构化查询语言（SQL)

对数据库操作的普通话，不区分大小写
结构化查询语言(Structured Query Language)简称SQL, 是操作和检索关系型数据库的标准语言，目前应用于各种关系型数据库。

结构化查询语言分类

数据查询语言（DQL:Data Query Language）：语句主要包括SELECT，用于从表中检索数据。
数据操作语言（DML：Data Manipulation Language）：语句主要包括INSERT，UPDATE和DELETE，用于添加，修改和删除表中的行数据。
数据定义语言（DDL:Data Definition Language）：语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象
事务处理语言（TPL:Transaction Process Language）：语句主要包括COMMIT和ROLLBACK，用于提交和回滚。
数据控制语言（DCL:Data Control Language）：语句主要包括GRANT和REVOKE，用于进行授权和收回权限。

Dos命令

dir  列出所有文件
cls 清屏
cd  C:\phpstudy_pro\Extensions\MySQL5.7.26\bin 修改文件夹
切换盘符  D:

链接数据库库命令：

mysql -hlocalhost -uroot  -p

mysql 常用命令

show databases;  显示所有数据库
use 数据库名   使用该数据库
quit; 退出数据库

基本查询语句

SELECT     [DISTINCT]{*|column|expression [alias],...}
FROM    table;

SELECT     列名    from  表名；

选择所有列

select deptno, dname, loc  from dept;
select *  from dept;

算术运算符

可以在SELECT语句中使用算术运算符，改变输出结果。 |运算符|描述| |---|---| |+|加| |-|减| |*|乘 | |/| 除|
让emp表中所员工的工资 + 200，同时显示之前的工资和加薪之后的工资

SELECT sal ， sal+200 from emp；

算术运算符优先级

1、乘除优先于加减
2、相同优先权的表达式按照从左至右的顺序依次计算
3、括弧可以提高优先权，并使表达式的描述更为清晰
显示员工姓名和年薪

select ename, (sal+100)*12 from emp;

练习：
员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)

select sal*6+sal*(1+0.2)*6  from emp;

空值NULL
任何包含空值的算术表达式运算后的结果都为空值NULL。

select ename, sal*12+comm from emp;

列别名

使用列别名的方法

方式1：列名列别名(空格)
方式2：列名 AS 列别名（as）
以下三种情况列别名两侧需要添加双引号
列别名中包含有空格
列别名中要求区分大小写(ORACLE中)
列别名中包含有特殊字符

select ename 员工名, sal 工资 from emp;

select ename as "员工 名", sal as 工资 from emp;

知识小课堂：

nvl(列， 值)  如果列为空，那么给与相应的默认值（oracle）
ifnull(列， 值)  如果列为空，那么给与相应的默认值（oracle）

练习：员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，要求显示列标题为员工姓名，工资收入，奖金收入，总收入

select ename 员工姓名, sal 工资收入, ifnull(comm, 0) 奖金, sal*6+sal*1.2*6+ifnull(comm,0) 总收入 from emp;

消除重复行

SELECT distinct deptno 
FROM   emp;

练习：查询员工表中一共有哪几种岗位类型

select distinct job from emp;

显示表结构

desc emp;

where 语句

where 语句可以作为查询的限定条件
格式：在from之后

SELECT [DISTINCT] {*|  column |  expression [alias], ...}
FROM   table
[WHERE condition(s)];

栗子

数值类型

select ename, hiredate, deptno from emp where deptno=30;

varchar

select ename, job from emp where job = 'MANAGER ';

date

SELECT * from emp where hiredate > '1982-01-01';

查询职位为SALESMAN的员工编号、职位、入职日期。

select empno,job, hiredate from emp where job="SALESMAN";

查询1985年12月31日之前入职的员工姓名及入职日期。

select ename,hiredate from emp  where hiredate<"1985-12-31";

查询部门编号不在10部门的员工姓名、部门编号

select ename, deptno from emp where deptno <> 10;

特殊比较运算符
BETWEEN..AND 使用BETWEEN .. AND.. 运算符来判断要比较的值是否在某个范围内。

select * from emp where sal between 1000 and 2000;

select * from emp where mgr in (7698, 7839, 7788);

练习：
查询入职日期在82年至85年的员工姓名，入职日期。

select ename,hiredate from emp where hiredate between '1982-01-01' and '1985-12-31';

查询月薪在3000到5000的员工姓名，月薪。

select ename,sal from emp where hiredate between 3000 and 5000;

查询部门编号为10或者20的员工姓名，部门编号。

select ename,deptno from emp where deptno in(10,20);

查询经理编号为7902, 7566, 7788的员工姓名，经理编号

select ename,mgr from emp where mgr in(7902,7566,7788);

模糊查询LIKE运算符
使用LIKE运算符判断要比较的值是否满足部分匹配，也叫模糊查询。模糊查询中两个通配符：

% 代表零或任意更多的字符
_ 代表一个字符

select * from emp where ename like 'M%';

select * from emp where ename like '_A%';

LIKE通配符转义

您可以使用ESCAPE标识符实现对“%”和 “_”的查找。
例：查询 JOB 以“MAN_”开头的雇员信息

select * from emp where JOB like 'MAN@_%'escape'@';
select * from emp where JOB like 'MAN/_%'escape'/';

IS NULL 运算符

使用 IS NULL 运算符来判断要比较的值是否为空值NULL

SELECT *FROM    emp  WHERE   mgr IS NULL;

查询员工姓名以W开头的员工姓名。

select ename from emp where ename like 'W%';

查询员工姓名倒数第2个字符为T的员工姓名。

select ename from emp where ename like "%T_";

查询奖金为空的员工姓名，奖金

select ename , comm from emp where comm is null;

逻辑运算符

SELECT empno, ename, job, sal FROM   emp  WHERE  sal>=1100  AND    job='CLERK';

SELECT empno, ename, job, sal FROM   emp  WHERE  sal>=1100  or job='CLERK';

 SELECT * from emp where job not in('SALESMAN','CLERK');

NOT运算符还可以和BETWEEN…AND、LIKE、IS NULL一起使用

... WHERE deptno NOT IN (10, 20)
... WHERE sal NOT BETWEEN 3000 AND 5000
... WHERE ename NOT LIKE 'D%’
... WHERE mgr IS NOT NULL

运算符的优先级

SELECT ename, job, sal  FROM   emp  WHERE  job='SALESMAN'  OR  job='PRESIDENT'  AND  sal>1500;

SELECT   ename, job, sal FROM     emp  WHERE    (job='SALESMAN'  OR job='PRESIDENT')  AND    sal>1500;

查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。

select ename,job,sal from emp where sal > 2000 and job in ('MANAGER','SALESMAN');

查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。

select ename,deptno,sal from emp where deptno in (10,20) and sal between 3000 and 5000;

查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。

select ename,hiredate,job from emp where hiredate between '1981-01-01' and '1982-01-01' and job not like 'SALES%';

查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

select ename,job,deptno from emp where job in ('SALESMAN','MANAGER') and deptno in(10,20) and ename like '%A%';

排序

使用ORDER BY子句能对查询结果集进行排序,语法结构如下：

SELECT  [DISTINCT] { *| 列名 | 表达式 [别名][,...] }
FROM    表名
[WHERE  条件]
[ORDER BY  {列名| 表达式| 列别名| 列序号} [ASC| DESC],…];

select * from emp order by hiredate;

可以按照列名、表达式、列别名、结果集的列序号排序
ASC: 升序(默认值), DESC: 降序
ORDER BY 子句必须写在SELECT语句的最后
规则

1、数字升序排列小值在前，大值在后。即按照数字大小顺序由小到大排列。
2、日期升序排列相对较早的日期在前，较晚的日期在后。
3、字符升序排列按照字母由小到大的顺序排列。即由A-Z排列；中文升序按照字典顺序排列。
4、空值在升序排列中排在最前面，在降序排列中排在最后。

按照别名进行排序

select ename, sal*12 annsal from emp order by annsal desc;

多列排序

select * from emp order by deptno desc, sal desc;

参与排序的多列都可以指定升序或者降序
ORDER BY子句中可以写没在SELECT列表中出现的列

select ename,deptno, sal from emp order by 2 desc, 3 desc;

查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。

select ename, deptno,sal  from emp where deptno in(20,30) order by sal;

查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。

select ename, deptno,sal from emp where sal between 2000 and 3000 and deptno not in(10) order by deptno,sal desc;

查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

select ename, hiredate, job from emp where (hiredate between '1982-01-01' and '1983-12-31') and (job like 'SALES%' or job like 'MAN%') order by hiredate desc;

LIMIT 语句(Mysql)

使用select语句时，经常要返回前几条或者中间某几行记录，可以使用关键字limit。语法格式如下：

SELECT 字段列表
FROM 数据源
LIMIT [start,]length;

说明： 1．limit接受一个或两个整数参数。start表示从第几行记录开始输出，length表示输出的记录行数。 2．表中第一行记录的start值为0(不是 1)。

select * from emp limit 5,2;

练习

查询入职日期最早的前5名员工姓名，入职日期。

select ename,hiredate from emp order by hiredate limit 5;

查询20号部门下入职日期最早的前2名员工姓名，入职日期。

select ename,hiredate from emp where deptno =20  order by hiredate limit 2;

按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门编号。

select ename,hiredate,deptno from emp limit 0,5;
select ename,hiredate,deptno from emp limit 5,5;
select ename,hiredate,deptno from emp limit 10,5;

本章练习
查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

 select ename,hiredate,job from emp where hiredate > '1982-07-09' and job <> 'SALESMAN';

查询员工姓名的第三个字母是a的员工姓名。

select ename from emp where ename like '__a%';

查询除了10、20号部门以外的员工姓名、部门编号。

select ename,deptno from emp where deptno not in (10,20);

查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。

select * from emp where deptno = 30 order by sal desc,ename;

查询没有上级的员工(经理号为空)的员工姓名。

select ename from emp where mgr is null;

查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。

select ename,sal,deptno from emp where sal >= 4500 and deptno in (10,20);

多表链接

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
语法为

SELECT    table1.column, table2.column
FROM    table1, table2
WHERE    table1.column1 = table2.column2;

笛卡尔积: 第一个表中的所有行和第二个表中的所有行都发生连接。
为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件
接下来我们详细学习这些连接条件：

等值连接

select * from emp,dept where  emp.deptno=dept.deptno;

select empno, ename , emp.deptno, loc from emp,  dept where emp.deptno=dept.deptno and loc = 'NEW YORK';

限制歧义列名

在用到多个表时可以使用表名作前缀来限定列；
通过使用表前缀可以提高性能；

select emp.empno, emp.ename , emp.deptno, dept.loc from emp,  dept where emp.deptno=dept.deptno and loc = 'NEW YORK';

通过使用列的别名可以区分来自不同表但是名字相同的列

select e.empno as 员工编号, e.ename 姓名,d.dname 部门名称 
 from emp e, dept d where e.deptno=d.deptno;

练习：

写一个查询，显示所有员工姓名，部门编号，部门名称。

select e.ename,e.empno,e.job from emp e,dept d where e.deptno = d.deptno;

写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select e.ename,d.loc,e.comm from emp e,dept d where e.deptno = d.deptno and d.loc = 'CHICAGO' and comm is not null;

写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno and e.ename like '%A%';

非等值连接

select e.e
name 员工姓名, e.sal 工资 , s.grade 工资等级  from emp e, salgrade s where  e.sal between s.losal and s.hisal;

两个表以上的链接

多表连接的写法分析

分析要查询的列都来自于哪些表，构成FROM子句;
分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;
接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;
分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;
根据用户想要显示的信息，补充SELECT子句。
分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

select e.empno 编号,e.ename 姓名,e.sal 员工薪水,s.grade 工资等级,d.loc 所在城市 from emp e,salgrade s,dept d where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal) order by s.grade;

自连接

查询每个员工的姓名和直接上级姓名

select w.ename 员工姓名, m.ename 领导姓名 from emp w, emp m where w.mgr = m.empno;

select w.ename 员工姓名 ,w.empno 员工编号,m.ename 领导姓名,m.empno 领导编号 from emp w,emp m,dept d where w.mgr = m.empno and d.deptno = w.deptno and d.loc in('NEW YORK','CHICAGO');

交叉连接

创建一个员工表和部门表的交叉连接。

select e.empno, e.ename, d.dname from emp e cross join dept d;

自然连接

select e.empno, e.ename, d.dname from emp e natural join dept d;

相比于等值连接，等值连接直接指明连接条件，自然连接是自己找（两个表字段名相同，自动添加），自然连接无法控制用哪些字段连接

外连接

左外链接
左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。
查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

select e.ename,e.deptno, d.dname from emp e left outer join dept d on(e.deptno = d.deptno);

右外链接
查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来

select e.ename, e.deptno, d.dname from emp e right outer join dept d on (e.deptno = d.deptno);

创建一个员工表和部门表的交叉连接。

select e.ename,d.dname from emp e cross join dept d;

使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

select e.ename,d.dname,e.hiredate from emp e natural join dept d where e.hiredate>'1980-05-01';

使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename 员工,m.ename 经理 from emp w left outer join emp m on (w.mgr = m.empno);

使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename 员工,m.ename 经理 from emp m right outer join emp w on (w.mgr = m.empno);

显示员工SMITH的姓名，部门名称，直接上级名称

select e.ename 员工姓名, d.dname 部门名称, h.ename 上级名称 from emp e, emp h,dept d where e.deptno=d.deptno and e.mgr=h.empno and e.ename='SMITH';

显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select e.ename 员工姓名, d.dname 部门名称, e.sal 工资,s.grade 工资等级  from emp e, salgrade s,dept d where e.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade>4;

显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT w.ename 员工姓名,m.ename 领导姓名 FROM emp w,emp m WHERE w.mgr = m.empno AND m.ename in ('KING','FORD');

显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

SELECT w.ename 员工姓名,w.hiredate 参加工作时间,m.ename 经理名,m.hiredate 参加工作时间 FROM emp w,emp m WHERE w.mgr = m.empno AND w.hiredate < m.hiredate;