MySQL

Mysql

①.数据库概述及数据准备

1.MYSQL概述

MySQL最初是由“MySQL AB”公司开发的一套关系型数据库管理系统（RDBMS-Relational Database Mangerment System）。
MySQL不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过7万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。
MySQL AB是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson和Michael “Monty” Widenius在瑞典创办的。
在2008年初，Sun Microsystems收购了MySQL AB公司。在2009年，Oracle收购了Sun公司，使MySQL并入Oracle的数据库产品线。

1.2 sql、DB、DBMS分别是什么

1.DB:
DataBase (数据库，数据库实际上在硬盘上以文件的形式存在)
数据库，通常是一个或一组文件，保存了一些符合特定规格的数据,数据库对应的英语单词是DataBase,简称:DB,数据库软件称为数据库管理系统（DBMS），全称为DataBase Management System，如：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql

2.DBMS:
DataBase Management System (数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer..)

3.SQL
结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。
SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行SQL。(sql语句的编译由DBMS完成)

DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
DBMS-（执行）->SQL->（操作）->DB

2.表(table)

表(table)是一种结构化的文件，可以用来存储特定类型的数据。
表是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。
一个表包括行和列：
行：被称为数据/记录（data）
列：被称为字段（column）

每一个字段应该包括哪些属性？字段名、数据类型、相关的约束。

3.sql语句的分类

DQL:查询语言

DML:对表当中的数据进行增删改

DDL:对表结构的增删改

TCL:事务控制语言

DCL:数据控制语言

DQL 数据查询语言(DQL-Data Query Language)
代表关键字:select
DML 数据操纵语言(DML-Data Manipulation Language)
代表关键字:insert,delete,update
DDL 数据定义语言(DDL-Data Definition Language)
代表关键字:create ,drop,alter
TCL 事务控制语言(TCL-Transactional Control Language)
代表关键字:commit (提交事务) ,rollback (回滚事务);
DCL 数据控制语言(DCL-Data Control Language)
代表关键字:grant(授权),revoke (撤销).权限

4.导入数据 489

导入数据（后期大家练习的时候使用这个演示的数据）
第一步：登录mysql数据库管理系统
dos命令窗口：
mysql -uroot -p333
第二步：查看有哪些数据库
show databases; (这个不是SQL语句，属于MySQL的命令。)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
第三步：创建属于我们自己的数据库
create database bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
第四步：使用bjpowernode数据
use bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
第五步：查看当前使用的数据库中有哪些表？
show tables; (这个不是SQL语句，属于MySQL的命令。)
第六步：初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql

注意：数据初始化完成之后，有三张表：
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+

5.脚本

bjpowernode.sql 这个文件以 .sql 结尾，这样的文件被称为"sql脚本"。
sql脚本：当一个文件的扩展名是.sql，并且改文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。
source：后面的文件可以直接拖进dos窗口中。

6.删除数据库：

drop database bjpowernode;

7.查看表结构：

8.表中的数据

9.常用命令

mysql> select database(); 查看当前使用的是哪个数据库

mysql> select version(); 查看mysql的版本号。

\c   命令，结束一条语句。

10、查看创建表的语句：

	show create table emp;

②.常用命令

2.1、查看msyql版本

• MySQL程序选项具有以下两种通用形式：
– 长选项，由单词之前加两个减号组成
– 短选项，由单个字母之前加一个减号组成
C:\Users\Administrator>mysql --version
mysql Ver 14.14 Distrib 5.5.36, for Win32 (x86)
C:\Users\Administrator>mysql -V
mysql Ver 14.14 Distrib 5.5.36, for Win32 (x86)

2.2、创建数据库

1.create database 数据库名称;

create database  bjpowernode;

2.use 数据库名称

use bjpowernode;

在数据库中建立表，因此创建表的时候必须要先选择数据库。

2.3、查询当前使用的数据库

select  database();

查询数据库版本也可以使用

select  version();

2.4、终止一条语句

如果想要终止一条正在编写的语句，可键入
\c。

2.5、退出mysql

可使用\q、QUIT或EXIT：
如：
mysql> \q 
mysql> exit 
mysql> quit

③.查看建表语句

3.1、查看和指定现有的数据库

show databases;//该databases是数据库的统称

3.2、指定当前缺省数据库

use databases;//该databases是数据库的统称

3.3、查看当前使用的库

select  database();

3.4、查看当前库中的表

show tables;

3.5、查看其他库中的表

3.6、查看表的结构

desc <table name>;

3.7、查看表的创建语句

show create table <table name>;

④简单的查询

1.查询一个字段

select 字段名 from emp;

Select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开，from表示将要查询的表，它和字段之间采用空格隔开.

2.查询多个字段

语法格式：

select 字段名1，字段名2，字段名3，.... from 表名;

注意:

1.任何一条sql语句都以 ";" 结尾。
2.sql语句不区分大小写。
3.字段可以参与数学运算。
4.查询多个字段，select中的字段采用逗号间隔即可，最后一个字段，也就是在from前面的字段>不能使用逗号了。

若查询员工的年薪

select ename,sal *12 from emp;

3.查询全部字段

可以将所有的字段放到select语句的后面，这种方案不方便，但是比较清楚，我们可以采用如下便捷的方式查询全部字段。

select * from emp;

采用select * from emp，虽然简单，但是*号不是很明确，建议查询全部字段将相关字段写到select语句的后面,在以后java连接数据库的时候,是需要在java程序中编写SQL语句的,这个时候编写的SQL语句不建议使用select * 这种形式,建议写明字段,这样可读性强.

4.计算员工的年薪

1.列出员工的编号，姓名和年薪

select empno,ename,sal*12 from emp;

5.将查询出来的字段显示为中文

select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;
     //注意:字符串必须添加单引号 | 双引号

可以采用as关键字重命名表字段，其实as也可以省略，如：

select empno  "员工编号", ename  "员工姓名", sal*12  "年薪" from emp;

注意：
1.标准sql语句中要求字符串使用单引号' 字符 ' 括起来。虽然mysql支持双引号，但是其他数据库系统不通用。
2.as关键字可以省略不写。

⑤条件查询

条件查询需要用到where语句，where必须放到from语句表的后面支持如下运算符

运算符	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between ... and ...	两个值之间，等同于>= and <=
is null	为null(is not null 不为空)
and	并且
or	或者
in	包含，相当于多个of(not in 不在这个范围中)
not	not 可以取非，主要用在is 或 in中
like	like称为模糊查询，支持%或下划线匹配 %匹配任意个字符下划线，一个下划线只匹配一个字符

1.等号操作符

select 字段1，字段2 from 表名 where 条件;

执行顺序：先from，然后where,最后select

查询工资为5000的员工

select empno, ename, sal from emp where sal=5000;

查询job为MANAGER的员工

select empno, ename from emp where job='manager';

2.不等于操作符 <>

查询工资不等于5000的员工

select empno,ename,sal from emp where sal<>5000;

查询工作岗位不等于MANAGER的员工

select empno,ename from emp where job<>'MANAGER';

注意：字符串用单引号括起来。

3、between … and …操作符

查询薪水在1600-3000的员工(第一种方法用>=和<=)

select empno,ename,sal from emp where sal>=1600 and sal<=3000;

and是连接符，意为"并且"的意思

查询薪水为1600到3000的员工(第二种方式，采用between … and …)

select empno,ename,sal from emp where sal between 1600 and 3000;

between...and...是闭区间，包括两个区间值 [1600,3000]。即between … and …，它是包含最大值和最小值的

用在字符 [A , C) 表示区间左闭右开。

4、is null 和is not null

Null为空，但不是空串，为null可以设置这个字段不填值，如果查询为null的字段，采用is null.
查询津贴为空的员工

select * from emp where comm is null;

查询津贴不为空的员工

select * from emp where comm is not null;

null 和 0不是一个意思。null指数据为空，0指这个数据的值为0。

5.and

and表示并且的含义，表示所有的条件必须满足

工作岗位为MANAGER,薪水大于2500的员工

select * from emp where job='MANAGER' and sal > 2500;

6.or

or，只要满足条件即可,相当于包含

查询出job为manager或者job为salesman的员工

select * from emp where job='manager' or job='salesman';

7 表达式的优先级

查询薪水大于1800，并且部门代码为20或30的员工（错误的写法）

select * from emp where sal > 1800 and deptno = 20 or deptno = 30;

以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的.

查询薪水大于1800，并且部门代码为20或30的（正确的写法）

select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

关于运算符的问题：不用记，没有把握尽量采用括号.

8.in

in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些

查询出job为manager 或者 job为salesman的员工

select * from emp where job in ('manager','salesman');

查询出薪水包含1000和薪水包含3000的员工

select * from emp where sal in(1000,3000);

in (1000,3000) 是指1000 或 3000 千万不要记成区间。

9.not

查询出薪水不包含1600和薪水不包含3000的员工（第一种写法）

select * from emp where sal <> 1600 and sal <> 3000;

查询出薪水不包含1600和薪水不包含3000的员工（第二种写法)

select * from emp where not (sal = 1600 or sal = 3000);

查询出薪水不包含1600和薪水不包含3000的员工（第三种写法） not in

select * from emp where sal not in (1600, 3000);

10.like(模糊查询)

在模糊查询中必须掌握两个特殊符号(% _ )

%代表任意多个字符

_代表任意一个字符

如果 "_" 需要在查询中表示普通字符，则需要用\ ”\ _ ”

查询姓名以M开头所有的员工

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

查询姓名以N结尾的所有的员工

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

查询姓名中包含O的所有的员工

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

查询姓名中第二个字符为A的所有员工

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

Like 中的表达式必须放到单引号中|双引号中

⑥排序数据

注意：默认是升序，asc表示升序，desc表示降序。

select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc; //升序
select ename , sal from emp order by sal desc; //降序

1.单一字段排序

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。

按照薪水由小到大排序(系统默认由小到大)

select * from emp order by sal;

取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)

select * from emp where job='MANAGER' order by sal;

如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面

按照多个字段排序，如：首先按照job排序，再按照sal排序

select * from emp order by job,sal;

注意:这个排序的意思是先按照job排序，然后在job排好序的基础上对job排好顺序的表，根据sal进一步排序。同一个首字母开头按照sal排序。

2.手动指定排序顺序

手动指定按照薪水从小到大排序

select * from emp order by sal asc;

手动指定按照薪水由大到小排序

select * from emp order by sal desc;

3.多个字段排序

按照job和sal倒叙。

select * from emp order by job,sal;

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

4.使用字段的位置排序

按照薪水排序

select * from emp order by 6;

不建议使用此种方式，采用数字含义不明确，程序不健壮

⑦分组函数/聚合函数/多行处理函数

count	取得记录数
sum	求和
avg	取平均数
max	取最大的数
min	取最小的数

注意：分组函数自动忽略NULL，不需要手动的加where 条件排除NULL值。

count(*)和count(具体的某个字段)，他们有什么区别？

select count(*) from emp where xxx;      符合条件的所有记录总数。(和某个字段无关)

select count(comm) from emp;            统计comm字段中不为NULL的数据总数。

注意：分组函数不能直接使用在where 关键字后面。
因为group by是在where执行之后才会执行的。
分组函数要在分组之后才能使用。

函数优先级
select      5
..
from        1
..
where      2
..
group by 3
..
having      4
..
order by    6
...

mysql>select ename,sal from emp where sal>avg(sal);

ERROR 1111 (HY000): Invalid use of group function

所有分组函数都是对"某一组"数据进行操作。

1.有NULL参与的运算结果一定是NULL。

所有数据库都是这样规定的。

select ename,(sal+null)*12 as yearsal from emp;

解决方法：ifnull(可能为NULL的数据，被当做什么处理)：属于单行处理函数

select ename,ifnull(comm,0) as comm from emp;

// ifnull(comm,0)的意思   就是指如果comm的值为null,把这个值当做0处理。

2. count

取得所有的员工数

select count(*) from emp;

count(*)表示取得所有记录，忽略null，为null的值也会取得

取得津贴(comm)不为null员工数

select count(comm) from emp;

采用count(字段名称)，不会取得为null的记录

取得工作岗位的个数

select count(distinct job ) from emp;

3.sum

Sum可以取得某一个列的和，null会被忽略.

取得薪水的合计

select sum(sal) from emp;

取得总薪水的合计(总薪水=sal+comm)

select sum(sal+ifnull(comm,0)) from emp;

注意 comm 里面有的数据为NULL，所以需要用单行函数ifnull()来解决问题。

4.avg

5.max

6.min

7.concat

含义：将多个字符串连接成一个字符串。

语法：

concat(str1, str2,...) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

8.concat_ws

和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
1

语法：

concat_ws(separator, str1, str2, ...)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

8.分组查询

1.group by

group by : 按照某个字段或者某些字段进行分组。

having : having是对分组之后的数据进行在此过滤。

例：找出每个工作岗位的最高薪资。

select max(sal) from emp group by job;

注意： 分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。

并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话，整张表的数据会自成一组。

select ename,max(sal),job from emp group by job;

上语句在mysql当中，查询结果存在，但是结果没有意义，在oracle数据库当中会报错。语法错误。

规定：当一条语句中有group by的话，select 后面只能跟分组函数和参与分组的字段。

例：找出每个部门不同工作岗位的最高工资。

select deptno,job,max(sal) from emp group by job;

2.having

如果想对分组数据再进行过滤需要使用having子句.

要求显示各部门平均薪资大于2000的数据。

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

此时不适用where条件语句是因为，where条件语句不能在后面使用分组函数。
下面是错误写法。

select avg(sal),deptno from emp where avg(sal)>2000 group by deptno;  // 这种写法是错误的

3.select语句总结

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

首先执行where语句过滤原始数据
执行group by进行分组
执行having对分组数据进行操作
执行select选出数据
执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

9.查询结果集的去重

正确写法

select distinct job from emp; //distinct关键字去重

select ename,distinct job from emp;

以上的sql语句是错误的。

distinct只能出现在所有字段的最前面。

10.连接查询

1.连接的分类

内连接：

等值连接
非等值连接
自连接

外连接

左外连接（左连接）
右外连接（右连接）

全连接

关于表的别名

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

表的别名的好处：1.执行效率高。2.可读性好。

emp 的别名为e , dept的别名为d.

2.内连接之等值连接：

最大特点是：条件是等量关系。

案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92:(太老，不用了)

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

SQL99: (常用的)

 select
     e.ename,d.dname
 from
     emp e
 join
     dept d
 on
     e.deptno =d.deptno;

99语法结构

...
    A
 (inner) join //inner 可以省略，带着Inner目的是可读性好。
    B
  on
    连接条件
  where
    ...

SQL99语法结构更清晰一些，表的连接条件和后来的where条件分离了。

3.内连接——非等值连接：

最大的特点是：连接条件中的关系是非等量关系。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select 
  e.ename,e.sal,s.grade
from 
  emp e
join
  salgrade s
on
  e.sal between s.losal and s.hisal;

4.自连接

自连接最大的特点是：一张表看作两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

mysql> select empnp,ename,mgr from emp;

emp a 员工表

![](images\emp a 员工表.png)

emp b 领导表

![](images\emp b 领导表.png)

select 
a.ename as '员工名',b.ename as'领导名'
from
emp a
inner join
emp b
on
a.mgr = b.empno;

5.外连接

1.外连接和内连接的区别

内连接：

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。

外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中

的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

2.外连接的分类

左外连接（左连接）：表示左边的这张表是主表。

右外连接（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接也会有对应的左连接的写法。

案例：找出每个员工的上级领导？（所有员工必须全部查询出来。包括KING）

emp a 员工表

emp b 领导表

外连接：（左外连接/左连接）

select 
	a.ename '员工', b.ename '领导'
from
	emp a
left outer join    //outer 可以省略不写。
	emp b
on
	a.mgr = b.empno;

外连接：（右外连接/右连接）

select 
	a.ename '员工', b.ename '领导'
from
	emp b
right outer join  //outer 可以省略不写.
	emp a
on
	a.mgr = b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。

案例：找出哪个部门没有员工？

select 
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

案例：找出每一个员工的部门名称以及工资等级。

select
  e.ename,d.dname,s.grade
from
   emp e
left join
   dept d
on
  e.deptno = d.deptno
left join
 salgrade s
on
 sal between losal and hisal;

案例：找出每一个员工的部门名称、工资等级、以及上级领导。

select 
		e.ename '员工',d.dname,s.grade,e1.ename '领导'
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join
		emp e1
	on
		e.mgr = e1.empno;

11.子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里。

select
  ..(select)..
from
  ..(select)..
where
  ..(select)..

1.where后面嵌套子查询

案例：找出高于平均薪资的员工信息。

select * from emp where sal > avg(sal); //错误的写法，where后面不能直接使用分组函数。

第一步：找出平均薪资

select avg(sal) from emp;

第二步：where过滤

	select * from emp where sal > 2073.214286;

第一步和第二步合并：

select * from emp where sal > (select avg(sal) from emp);

2.from后面嵌套子查询

案例：找出每个部门平均薪水的薪资等级

第一步：找出每个部门平均薪水。（求sal的平均值）

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

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，连接条件是：t.avgsal between s.losal and s.hisal

select t.*,s.grade
from
 (select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal ;

3.select后面嵌套子查询

12.union用法

作用：将查询结果集相加

案例：找出工作岗位是SALESMAN和MANAGER的员工

方法一 or

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

方法二 in

select ename,job from emp where job in ('MANAGER','SALESMAN');

方法三 union(可以不同表的结果拼接到一起)

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

13.limit以及通用分页SQL

重点！！，以后分页查询全靠它。

1.limit是Mysql特有的，其他数据库中没有，不通用。

2.limit取结果集中的部分数据，这是它的作用。

3.语法机制

limit startIndex, length

startIndex表示起始位置，从0开始，0表示第一条数据。

length表示取几个。

案例：取出工资前五名的员工（思路，降序取前五个）

select ename,sal from emp order by sal desc limit 0,5;

如果limit后面把起始位置省略掉了，mysql自动默认从第0个开始。

select ename,sal from emp order by sal desc limit 5;

结果同上

4.limit是sql语句中最后执行的环节。

5.案例：找出工资排名在第四到第6名的员工

select ename,sal from emp order by sal desc limit 3,6;

6.通用的标准分页SQL

如果每页显示3条记录则

第1页 ：0,3;
第2页 ：3,3;
第3页 ：6,3;
第4页 ：9,3;
第5页 ：12,3;
每页显示pageSize条记录
第pageNo页：(pageNo - 1) * pageSize, pageSize

14.建表语句

1.建表语句的语法格式：

create table 表名(
  字段名1 数据类型,
  字段名2 数据类型,
  字段名3 数据类型,
  ...
);

表名在数据库中一般建议以：t_或者tbl_开始。

MYSQL支持的数据类型：数值类型，日期和时间类型，字符串类型

2.MYSQL常见的数据类型

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度)	变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
Float(有效数字位数，小数位)	数值型
Int( 长度)	整型
bigint(长度)	长整型
Date	日期型年月日
DateTime	日期型年月日时分秒毫秒
time	日期型时分秒
BLOB	Binary Large OBject（二进制大对象）（存储图片、视频等流媒体信息）
CLOB	Character Large OBject（字符大对象）（存储较大文本，比如，可以存储4G的字符串）
其它…………………

char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar.

例：建立一个student表

create table t_student (
  no bigint,
  name varchar(255),
  sex char(1),
  classno varchar(255),
  birth char(10)
);

15.插入语句

语法格式：

insert into 表名（字段名1，字段名2，字段名3,....） values(值1,值2,值3,....)；

要求：字段的数量和值的数量相同，并且数据类型要对应相同。

例：

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1950-10-12');

若只输入一个值，则其他值为NULL;

insert into t_student(name) values('wangming');

注意：当一条insert语句执行成功之后，表格当中必然会多一行记录。

即使多的这一行记录中某些字段为NULL,后期也没有办法再执行Insert语句插入数据了，只能使用update进行更新。

insert语句的另一种写法

insert into 表名 values(值1,值2,值3,...)

注意：省略写法，后面括号的值必须和表中的数据一一对应，且不能够缺少值，否则会报错。

insert语句插入多行数据

insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...),(值1,值2，值3,...);

将查询结果插入到一张表中

insert into dept1 select * from dept;
select * from dept1;

表的复制

语法：

create table 表名 as select语句；

16.删除数据

语法格式：

delete from 表名 where 条件;

注意：没有条件全部删除

例1：删除10部门数据

delete from dept1 where deptno = 10;

例2：删除所有记录

delete from dept1;

16.1删除大表数据

truncate table 表名;//表被截断，不可回滚。永久丢失。

删除的是大表的数据，

17.修改数据：update

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

注意：没有条件，整张表数据全部更新。

18.删表语句

drop table if exists 表名;

把表直接删掉。不可恢复。删除的是表本身

19.约束作用及常见约束

定义：创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

19.1、常见的约束：

非空约束（not null）：约束的字段不能为NULL

唯一约束（unique）：约束的字段不能重复

主键约束（primary key）：约束的字段既不能为NULL，也不能重复

外键约束（foreign key）

检查约束（check）:注意oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

19.2、非空约束 (not null)

drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255) not null,
  password varchar(255)
);
insert into t_user(id,password) values (1,'123');

结果

19.3、唯一性约束(unique)

唯一性约束修饰的字段具有唯一性，不能重复。但可以为NULL;

drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255) unique
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');

19.4、主键约束

drop table if exists t_user;
create table t_user(
  id int primary key,
  username varchar(255),
  email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;

insert into t_user(id,username,email) values(1,'jack','jack@123.com');

id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
主键的特点：不能为NULL，也不能重复。

19.5、主键的术语

主键约束：primary key

主键字段：id字段添加primary key之后，id叫做主键字段。

主键值：id字段中的每一个值都是主键值

19.6、主键的作用

1.表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键

2.主键的作用：主键值是这行记录在这张表当中的唯一标识。

19.7、主键的分类

根据主键字段的字段数量来划分：

单一主键(常用的，推荐的)
复合主键(多个字段联合起来添加一个主键约束) （复合主键不建议用，违背了三范式）

根据主键性质来划分：

自然主键：主键最好就是和业务没有任何关系的自然数。（推荐）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

最好不要拿着和业务挂钩的字段作为主键。

因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

注意：一张表的主键约束只能有1个。

19.8、MySQL提供主键值自增

格式

primary key auto_increment,  //主键值后加上 auto_increment

drop table if exists t_user;
create table t_user(
  id int primary key auto_increment, 
  username varchar(255));
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
select * from t_user;

19.9、外键约束

格式

foreign key(classno) references t_class(cno)

外键约束的术语：

外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值

业务背景：
请设计数据库表，用来维护学生和班级的信息？

		第一种方案：一张表存储所有数据
		no(pk)			name			classno			classname
		-------------------------------------------------------------------------------------------
		1					zs1				101				北京大兴区经济技术开发区亦庄二中高三1班
		2					zs2				101				北京大兴区经济技术开发区亦庄二中高三1班
		3					zs3				102				北京大兴区经济技术开发区亦庄二中高三2班
		4					zs4				102				北京大兴区经济技术开发区亦庄二中高三2班
		5					zs5				102				北京大兴区经济技术开发区亦庄二中高三2班
		缺点：冗余。【不推荐】

  	第二种方案：两张表（班级表和学生表）
  	t_class 班级表
  	cno(pk)		cname
  	--------------------------------------------------------
  	101		北京大兴区经济技术开发区亦庄二中高三1班
  	102		北京大兴区经济技术开发区亦庄二中高三2班
  
  	t_student 学生表
  	sno(pk)		sname				classno(该字段添加外键约束fk)
  	------------------------------------------------------------
  	1				zs1				101
  	2				zs2				101
  	3				zs3				102
  	4				zs4				102
  	5				zs5				102

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
  cno int,
  cname varchar(255),
  primary key(cno)
);

create table t_student(
  sno int,
  sname varchar(255),
  classno int,
  primary key(sno),
  foreign key(classno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
		insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

		insert into t_student values(1,'zs1',101);
		insert into t_student values(2,'zs2',101);
		insert into t_student values(3,'zs3',102);
		insert into t_student values(4,'zs4',102);
		insert into t_student values(5,'zs5',102);
		insert into t_student values(6,'zs6',102);
		select * from t_class;
		select * from t_student;

20.存储引擎

、存储引擎（整个内容属于了解内容）

1、完整的建表语句

CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

	建表的时候可以指定存储引擎，也可以指定字符集。

	mysql默认使用的存储引擎是InnoDB方式。
	默认采用的字符集是UTF8

2、什么是存储引擎呢？

存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。

每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3、查看当前mysql支持的存储引擎？

show engines \G

	mysql 5.5.36版本支持的存储引擎有9个：
		*************************** 1. row ***************************
				Engine: FEDERATED
			  Support: NO
			  Comment: Federated MySQL storage engine
		Transactions: NULL
					 XA: NULL
		  Savepoints: NULL
		*************************** 2. row ***************************
				Engine: MRG_MYISAM
			  Support: YES
			  Comment: Collection of identical MyISAM tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 3. row ***************************
				Engine: MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 4. row ***************************
				Engine: BLACKHOLE
			  Support: YES
			  Comment: /dev/null storage engine (anything you write to it disappears)
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 5. row ***************************
				Engine: CSV
			  Support: YES
			  Comment: CSV storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 6. row ***************************
				Engine: MEMORY
			  Support: YES
			  Comment: Hash based, stored in memory, useful for temporary tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 7. row ***************************
				Engine: ARCHIVE
			  Support: YES
			  Comment: Archive storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 8. row ***************************
				Engine: InnoDB
			  Support: DEFAULT
			  Comment: Supports transactions, row-level locking, and foreign keys
		Transactions: YES
					 XA: YES
		  Savepoints: YES
		*************************** 9. row ***************************
				Engine: PERFORMANCE_SCHEMA
			  Support: YES
			  Comment: Performance Schema
		Transactions: NO
					 XA: NO
		  Savepoints: NO

4.常用的存储引擎

1、MyISAM存储引擎

• MyISAM存储引擎是MySQL最常用的引擎。
• 它管理的表具有以下特征：
– 使用三个文件表示每个表：

• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）

优点：
– 灵活的AUTO_INCREMENT字段处理
– 可被转换为压缩、只读表来节省空间
缺点：
不支持事务。

2、InnoDB存储引擎

• InnoDB存储引擎是MySQL的缺省引擎。
• 它管理的表具有下列主要特征：
– 每个InnoDB表在数据库目录中以.frm格式文件表示
– InnoDB表空间tablespace被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
– 提供全ACID兼容
– 在MySQL服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

优点：支持事务、行级锁，外键等。这种存储引擎数据的安全得到保障。

表的结构存储在 xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制。

3、MEMORY存储引擎

• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
• MEMORY存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm格式的文件表示。
– 表数据及索引被存储在内存中。
– 表级锁机制。
– 不能包含TEXT或BLOB字段。

MEMORY存储引擎以前被称为HEAP引擎。
缺点：不支持事务，数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。

21.事务

1.概述：

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。

2.事务具有四个特征ACID

a) 原子性（Atomicity）
l 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
b) 一致性（Consistency）
l 在事务开始之前与结束之后，数据库都保持一致状态。
c) 隔离性(Isolation)
l 一个事务不会影响其他事务的运行。
d) 持久性(Durability)
l 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念：

a) 事务（Transaction）：一批操作（一组DML）

b) 开启事务（Start Transaction）

c) 回滚事务（rollback）

d) 提交事务（commit）

e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行DML语句是其实就是开启一个事务
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.
事务只对DML有效果。

3.事物之间的隔离性

事务隔离性存在隔离级别，理论上分为四个级别

第一级别: 读未提交(read uncommitted)

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

读未提交存在脏读(Dirty Read)现象：表示堵到了脏的数据。

第二级别：读已提交(read committed)

对方事务提交之后的数据我方可以读取到。

对于这种隔离级别解决了：脏读现象没有了。.

读已提交存在的问题是：不可重复读。

第三级别：可重复读(repeatable read)

这种隔离级别解决了：不可重复读问题。

第四级别：序列化读/串行化读

解决了所有问题

效率低，需要事务排队

Oracle数据库默认的隔离级别是：读已提交

Mysql数据库默认的隔离级别是：可重复读

4.事务操作

commit 提交 , rollback 回滚。

mysql事务默认情况下是自动提交的。（只要执行任意一条DML语句则提交一次）

1.解除默认提交的方法

start transaction; //关闭自动提交机制

posted @ 2023-04-02 07:10 Gonfei 阅读(19) 评论(0) 收藏举报

刷新页面返回顶部

今天是周几

MySQL

Mysql

①.数据库概述及数据准备

1.MYSQL概述

1.2 sql、DB、DBMS分别是什么

2.表(table)

3.sql语句的分类

4.导入数据 489

5.脚本

6.删除数据库：

7.查看表结构：

8.表中的数据

9.常用命令

10、查看创建表的语句：

②.常用命令

2.1、查看msyql版本

2.2、创建数据库

③.查看建表语句

3.1、查看和指定现有的数据库

3.2、指定当前缺省数据库

3.3、查看当前使用的库

3.4、查看当前库中的表

3.5、查看其他库中的表

3.6、查看表的结构

3.7、查看表的创建语句

④简单的查询

1.查询一个字段

2.查询多个字段

3.查询全部字段

4.计算员工的年薪

5.将查询出来的字段显示为中文

⑤条件查询

1.等号操作符

2.不等于操作符 <>

3、between … and …操作符

4、is null 和is not null

5.and

6.or

7 表达式的优先级

8.in

9.not

10.like(模糊查询)

⑥排序数据

1.单一字段排序

2.手动指定排序顺序

3.多个字段排序

4.使用字段的位置排序

⑦分组函数/聚合函数/多行处理函数

1.有NULL参与的运算结果一定是NULL。

2. count

3.sum

4.avg

5.max

6.min

7.concat

8.concat_ws

8.分组查询

1.group by

2.having

3.select语句总结

9.查询结果集的去重

10.连接查询

1.连接的分类

2.内连接之等值连接：

3.内连接——非等值连接：

4.自连接

5.外连接

1.外连接和内连接的区别

2.外连接的分类

外连接：（左外连接/左连接）

外连接：（右外连接/右连接）

11.子查询

1.where后面嵌套子查询

2.from后面嵌套子查询

3.select后面嵌套子查询

12.union用法

13.limit以及通用分页SQL

14.建表语句

1.建表语句的语法格式：