MYSQL

最新推荐文章于 2021-02-05 20:35:17 发布

ranqing007

最新推荐文章于 2021-02-05 20:35:17 发布

阅读量301

点赞数

分类专栏： mysql 文章标签： MySQL

本文链接：https://blog.csdn.net/rolljay/article/details/99983020

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

创建用户授权

系统权限分类：
DBA：全部特权
resource
connect

Mysql和Oracle有点不同

Oracle中的字符字段类型为VARCHAR2，而MySQL中为VARCHAR
Oracle中如果字段有小数用NUMBER(x,x)，而MySQL中用DECIMAL

常见的语句

这里列出一些常用的语法：

Oracle中varchar2()长度

varchar2(byte)：就是默认的表示方式，比如我们写成：varchar2(100)，就相当于varchar2(100 byte)，表示最大字节数是100，该字段最多能容纳100个字节，强调空间大小。由于我们描述的是字节，因此，保存汉字等字符时，就要小心了。如果你的数据库用的是GBK编码，那么一个汉字将占用2个字节，最多能存50个汉字，如果你的数据库用的是UTF8编码，那么一个汉字将占用3个字节，最多能存33个汉字。

建立表:

CREATE TABLE Course
(
      -- 编号；
      ID CHAR(3) NOT NULL,
   		 -- 名称；
   		 NAME VARCHAR2(50) NOT NULL,
   		 -- 学分；
   		 Credit NUMBER(2,1) NOT NULL,
   		 -- 先修课程编号；
   		 PreviousCourseId CHAR(3) NULL
);

插入数据：

INSERT INTO Course(ID,Name, Credit, Previouscourseid)
VALUES('001', '计算机导论', 2, NULL);
INSERT INTO Course(ID,Name, Credit, Previouscourseid)
VALUES('002', 'C语言基础', 3, '001');
INSERT INTO Course(ID,Name, Credit, Previouscourseid)
VALUES('003', '数据结构', 3, '002');
INSERT INTO Course(ID,Name, Credit, Previouscourseid)
VALUES('004', 'Java面向对象程序设计', 5, '003');

修改语句

update course set credit=2.5

case…when…语句

update score set value=(case
							when value<95 then value+5
							else 100
						end);

空值处理

当空值参与运算时，如果空值出现在算术表达式中，其运算结果也为空；如果空值出现在字符串连接表达式中，Oracle将其视为空串
1）函数NVL()
语法格式：NVL(expr1,expr2)
说明：如果参数表达式expr1的值为null，则NVL()函数返回参数表达式expr2的值；如果expr1的值不为null，则返回expr1的值。
示例：计算所有教师的月总收入，并利用函数NVL()处理bonus出现null的情况
select name as "姓名" ,nvl(bonus,0)+wage as "月总收入" from teachers;
2）函数NVL2()
语法格式：NVL2(expr1,expr2,expr3)
说明：如果expr1为null，则返回expr3的值；如果expr1不为null，则返回expr2的值；
示例：计算所有教师的月总收入，并利用函数NVL2()处理bonus出现null的情况
select name as "姓名" ,nvl(bonus,bonus+wage,wage)+wage as "月总收入" from teachers;
3）函数coalesce()
语法：coalesce(expr1 [,expr2]...)
说明：返回参数列表第一个非空值，如果所有参数都为空，则返回空值
示例：计算所有教师的月总收入，并利用函数coalesce()处理bonus出现null的情况
select name as "姓名" ,coalesce(bonus+wage,wage)+wage as "月总收入" from teachers;

条件查询

包含测试：
in           在指定集合中   department_id in(101,103)
not in     不在指定集合中   department_id not in(101,103)
范围测试：
between and   在指定范围内   wage between 1000 and 2000
not between and  不在指定范围内  bonus not between 600 and 800
匹配测试：
like   与指定模式匹配   name like '王%'
not like
null测试：
is null   是null值   hire_date is null
is not null

记录排序

一、按照单一列排序：
   1）升序排列，查询结果默认是升序排列，asc可以省略
    select name,title,bonus,wage from teachers order by wage asc;
   2）降序排列
   select name,title,bonus,wage from teachers order by wage desc;
二、按照多列排序
   按多列排序，是指order by 子句的expr指定一个以上的列或表达式。查询结果中的数据行首先按照expr指定的第一个列进行排序，然后根据expr指定的第二个列进行排序
   select student_id,name,specialty,dbo from students order by specialty,name;
三、分组查询
   列函数：MAX(column)、MIN(column)、COUNT(*) 表中行的总数、COUNT(column)列不为null的行数、SUM(column)列中所有值的总和、AVG(column)列中所有值的平均数
四、group by子句
说明：通过使用group by子句，可以在表中达到数据分组的目的。将表的行为分为若干组，这些组中的行并不互相重复。返回通过列函数分别统计每个组，这样每个组都有一个统计值
示例：
--对dept进行分组
select deptno from emp group by deptno;
--按照job和deptno对emp进行分组
select job,deptno from emp group by job,deptno;
--查询每一个deptno工资中的最大值和最小值
select deptno,max(mgr),min(mgr) from emp group by deptno;
--查询每个部门的人数
select deptno,count(*) from emp group by deptno;
五、having子句
group by子句用于指定分组的依据，而having子句则指定条件，用于限制分组显示结果
having子句中的condition用于指定限制分组结果的条件，having子句必须与group by子句一起使用。
--检索平均sal高于2000元的deptno，显示deptno的编号，平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

子查询与集合操作

单行子查询

1、在where子句中使用子查询
--利用子查询，在emp表中检索sal低于平均sal的所有emp
select * from emp where sal<(select avg(sal) from emp);
--利用子查询，在emp表中检索于Smith相同job的所有emp(注意引号里面的内容是区分大小写的）
select * from emp where job=(select job from emp where ename='SMITH');
2、在having子句中使用子查询
--利用子查询，在emp表中检索deptno平均sal高于最低deptno平均sal的部门和平均sal
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select min(avg(sal)) from emp group by deptno);
3、在from子句中使用子查询

多行子查询

返回多行单列数据的子查询成为多行子查询。当在where子句中使用多行子查询时，必须要使用多行比较符in、any、或all
in:匹配于子查询结果的任意一个值，结果为真，否则为假
any:只要符合子查询结果的任意一个值，结果为真，否则为假
all:必须要符合子查询结果的所有值，结果才为真；否则为假
示例：
--利用子查询，在emp中检索s姓员工的empno和ename
select empno,ename from emp where empno in (select empno from emp where ename like 'S%');

合并（merge）

将日期格式以“YYYY-MM-DD”输出

select to_char(hiredate,"YYYY-MM-DD") from emp;

使用字符串连接

select ename || '离职日期是：' || to_char(hiredate,'YYYY-MM-DD') from emp;

多表查询

1、笛卡尔积

首先准备两张表：
dept:
在这里插入图片描述
emp:

select * from emp,dept;

查询出的结果集就叫做笛卡尔积。
在这里插入图片描述

有两个集合A，B，取这两个集合的所有组合情况（比如A有3条数据，B有2条数据，组成结果就是3x2=6条结果）它把所有可能的组合都列出来了
笛卡尔积里面的数据并不是真实的数据，要排除不正确的数据。

2、多表查询的分类（消除无用的数据）

内连接查询：
1、隐式内连接：使用where条件消除无用数据

查询所有员工的信息和对应的部门信息
select * from emp,dept where emp.'dept_id'=dept.'id'

在这里插入图片描述
2、显式内连接

语法：
select 字段列表 from 表名 [inner] join 表明2 on 条件

用显示内连接查询员工信息和对应的部门信息

select * from emp inner join dept on emp.'dept_id'=dept.'id';

在这里插入图片描述
隐式内连接和显式内连接查询的结果是一样的（inner还可以省略）

外连接查询

左外连接查询语法：
select 字段列表 from 表1 left [outer] join 表2 on 条件

1、左外连接：查询左表所有数据以及交集部分
示例：上面内连接查询中emp表都有dept_id，但是如果emp表的dept_id为空的话怎么处理呢？

select * from emp

结果：
在这里插入图片描述
查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称。
如果采用内连接

select t1.*,t2.'name' from emp t1,dept t2 where t1.'dept_id'=t2.'id';

结果：
在这里插入图片描述
用内连接漏掉了新入职没有部门的员工，小白龙记录都没有了，内连接取的是交集。
因此，要用到外连接：

select t1.*,t2.name from emp t1 left outer join dept t2 on t1.'dept_id'=t2.'id';

在这里插入图片描述
因此，就查询成功了。

2、右外连接：查询右表的所有记录以及交集部分

语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；

索引的作用和弊端

优点：提高数据检索效率，降低数据库的io成本和排序成本，查找速度快
缺点：索引占用存储空间，随着table数据的增长，索引数据量也会增加，会带来存储空间的消耗

索引：

创建索引
create [unique] index name_idx on student(name);
删除索引
drop index index_name;

当数据库查询速度很慢的时候，如何优化

1、建立索引
2、减少表之间的关联
3、优化sql，不要让sql做全表查询
4、简化字段

解决一些困惑的问题

1、truncate和delete的区别：

相同点：truncate和delete只删除数据
不同点：1）当表被truncate后，这个表和索引空间会恢复到初始大小
        2）truncate只能对table，delete可以是table和view

2、唯一约束和主键约束的区别：

1)创建语法的不同：
唯一约束：
alter table department add constraint UQ_Department_ID unique(id)
主键约束：
alter table department add constraint PK_Department_ID primary key(id)
2)同一个表只能有一个主键约束，但能有多个唯一约束
3)主键字段值不能为null，但唯一字段值可以为null
4)主键可以做其他表的外键，唯一约束字段不能做其他表的外键

3、where1=1：永真；where 1=0永假