MySql数据库介绍及常用SQL语句（整理）

【数据库介绍】

数据库（database）是一个用来存储数据的关系型数据库的系统。相比文件存储更加快捷、方便。

1、关系型数据库

在数据库存储过程中，所有的数据列都会相互有关联，每条数据都能够知道自己到底有哪些列是自己的内容。（列之间存在关系）
例如：MySQL、 Oracle、 DB2、 SQLServer
作用：用来存储基本数据

2、非关系型数据库

和map集合一样，存储的数据是key - value形式，及时存储一类的信息，相互也不能知道是哪些。（数据与数据之间没有任何关系）
例如：redis
作用：用来做数据缓存
关系型数据库的简介（常见的库）
MySQL oracle 是一个开源的免费的数据库适用于中小型项目
Oracle oracle 是一个收费的大型的非常好的数据库（大型项目）
DB2 IBM 大小项目都可以，免费-收费数据库
SQLServer微软 大小项目都可以，免费-收费数据库

【MySQL数据库基本应用】

mysql -hlocalhost -uroot -proot

Mysql：声明当前访问的是MySQL数据库
-h h为host意思，这里输入当前访问的地址
-u u为user意思，输入登录的用户名
-p p为password 输入当前用户密码
不访问外网，本地访问可以省略-h

【SQL】语言介绍

MySQL是数据库、SQL是数据库操作语言、DBA数据库管理人员
DDL数据定义语言、DML数据操作语言、DQL数据查询语言
DCL数据控制语言、CCL指针操作语言、TPL事务处理语言

【数据库定义语言】

【库操作】

1、创建数据库

create database 库名;

2、查看数据库

show databases;

3、删除数据库

drop database 库名;

4、查看数据库详细信息

show create database 库名;

5、修改数据库字符集

alter database 库名 character set gbk；

6、创建库时指定字符集

create database 库名 character set 字符集;

【表操作】

1、选择数据库

use 库名

2、创建表

create table 表名 （列名1 数据类型（长度),列名2 数据类型（长度)）;

3、查看当前创建的表

desc 表名;

4、查询所有表

show tables；

5、删除表

drop table 表名；

6、修改表——修改表名

alter table 表名 rename 新表名；

7、修改表——在原表增加一列

alter table 表名 add 列名 类型（长度）；

8、修改表——在原表删除一列

alter table 表名 drop 列名；

9、修改表——在原表修改一列

alter table 表名 change 旧列名 新列名 类型（长度）；

DML【数据库操作语言】

特点：不针对数据库或表。针对表中的数据

1、增加一条数据
insert into 表名（列名，列名…） value （值，值…）；
例：

1 insert into student（name, sex, hobby） value ('林黛玉','女','哭');
2 insert into student (name, hobby) value ('林黛玉','哭');

【简写方式】不推荐 值的数量必须是列的数量

insert into student value ('林黛玉','女','哭');

一次性增加多条数据

1 insert into student (name, sex, hobby) values 
2 ('贾宝玉','男','玩'),
3 ('薛宝钗','女','笑'),
4 ('王熙凤','女','吃');

2、修改数据

update 表名 set 列=值，列=值 ... where 列 = 值；

3、删除数据
delete from 表名 where 列= 值；
例：

1 delete from student where name='张三';
2 delete from student;
3 truncate student;

清空数据的两种方式：
（1）delete from 表名 ，一条条删除数据，如果数据量大，很慢。
（2）truncate 表名，直接删除表，然后创建一个同名的新表，很快。

DQL【数据库查询语言】

【查询数据】

select * from 表名；

1 select name as 姓名， sex 性别 from student；
2 select sex，hobby from student where name = '张三'；

【MySQL数据库数据类型】

【字符串类型】

   char 字符串，长度一旦指定，直接占用最大长度的空间。
   varchar 字符串，长度指定后，会根据填充的数据长度进行空间分配，不超过最大长度。

【数值类型】

int 一般设计表时，固定值都是用数字表示，而这个数字其实是int类型的一种。
tinyint（微小的）占一个字节
mediumint（中型的）占两个字节
int（正常）占四个字节
bigint（大的）占八个字节
float、 double、 long

【布尔类型】

数据库本身是没有boolean类型的，但是一些工具会默认提供布尔类型（SQLyog）即使选了boolean类型，表创建之后会自动转表为tinyint 0 false 1 true

【日期类型】

date 年月日
time 时分秒
datetime 年月日 时分秒
timestamp（时间戳） 年月日 时分秒

datetime 和 timestamp 的区别

datetime
默认最大时间9999-12-31 23：59：59 如果当前时间不填充数据，默认是null
timestamp
默认最大时间2037-12-31 23：59：59 如果当前时间不填充数据，默认是系统时间

说明：timestamp
1970-01-01 00:00:00 计算机元年 纪念一个计算机诞生（unix）
为什么时间戳最大时间是2037年，原因是早期系统只有32位的，能够执行最大的数字就是2037年转化的毫秒数。

【约束】

指在出创建数据表时，会设置一些规则，这些规则就是约束。
主键约束、外键约束、唯一约束、默认约束、非空约束

【主键约束】

关键字：primary key

1 #创建一个带主键的表
2 #primary key （pk）
3 create table person(
4 id int(10) primary key,
5 name varchar(20),
6 sex tinyint(1)
7 )；

主键规则：1、主键不能为空 2、主键不能重复

主键自增长功能
关键字：auto_increment
要求：必须是int类型

create table person(
id int(10) primary key auto_increment,
name varchar(20),
sex tinyint(1)
)；

【唯一约束】

当前列不能有重复数据，约束能力没有主键强
关键字：unique

create table person(
id int(10) primary key auto_increment, 
name varchar(50), 
phone varchar(20) unique, 
sex tinyint(1)
);

特点：
1、数据类型不做要求
2、值可以为空
3、不能填写当前的值

【默认约束】

如果当前数据没有填写，可以通过默认约束指定一个默认值。
关键字：default

1 create table person(
2 id int(10) primary key auto_increment,
3 name varchar(20),
4 phone varchar(20) unique,
5 description varchar(200) default ‘默认值’
6 );

特点：约束力极弱，唯一做的功能就是添加默认值。

【非空约束】

当前数据列必须填写值，可以是空字符串，不能是null
关键词：not null

Create table person(
Id int(30) primary key auto_increment,
Name varchar(30) not null,
Phone varchar(30) unique,
Description varchar(200) default ‘默认值’
);

特点：值可以是空字符串但不能是null值

【表关系】

表与表之间的关系称为【外键关系】
一对一、一对多、多对多
外键：foreign key （fk）

【一对一】

思想：一般两张表在一对一情况下，我们会设定主表（必须先存在）和从表（数据可以没有，如果有那么也必须只能对应一条主表主表数据）

#主表
create table car(
cid int(10) primary key auto_increment,
name varchar(50),
color varchar(20)
);

#从表
create table car_number(
nid int(10) primary key auto_increment,
number varchar(50),
color varcahr(20)
);

这样设计两张表没有任何关联 【在从表中指定外键引用主表的主键】
【一对一】 主表的主键在从表中做外键，并且指定到从表的主键上。要求从表主键类型和主表一样并且不能自增长。

#设计主表
create table car(
cid int(10) primary key auto_increment,
name varchar(20),
color  varchar(20)
);

#从表
create table car_number(
nid int(10) primary key,
number varchar(30),
color varchar(20),
foreign key (nid) references car(cid)
);

1、测试:从表如果不填写主键违反从表主键策略
2、测试:从表主键填写一个主表没有的主键，违反了外键约束
3、测试：从表中添加一个相同的值，违反从表的主键策略

【一对多 多对一】

思想：【一】的主键在【多】的一方做外键，并且在【多】新建一个字段作为外键使用。
外键指定到多是一方。

1  #一对多、多对一设计
2  create table locks(
3  lid int(10) primary key auto_increment,
4  name varchar(20),
5  color varchar(20)
6  );
7  create table keyes(
8  kid int(10) primary key auto_increment,
9  number varchar(30),
10 lid int(10),
11 foreign key (lid) references locks(lid)
12 );

【多对多】

思想：没有主从表，外键无法单独在两张表中指定，需要建第三张表支持。

1  #多对多表设计
2  create table games(
3  gid int(10) primary key auto_increment,
4  name varchar(20),
5  type varchar(30)
6  );
7
8  create table player(
9  pid int(10) primary key auto_increment,
10  name varchar(50),
11 age int(40)
12 );
13
14 create table games_player(
15 pid int(10),
16 gid int(10),
17 foreign key(pid) references player (pid),
18 foreign key(gid) references games (gid)
19 );

【连接（关联）查询】

select * from car, car_number;

笛卡儿积：在多表查询的过程中，如果不给where条件，那么查询的结果会所有的数据会一一匹配。

【内连接查询】

隐式内连接
查询时关键字使用 where

#查询当前车名奔驰S200的车牌号（一对一）
select c.`name`, cn.number from car c , car_number cn where c.`name` = ‘奔驰S200’ and c.cid = cn.nid;

#查询当前三环锁有哪些钥匙，返回锁名称和钥匙编号（一对多）
select ls.`name` , ks.number from locks ls, keyes ks where ls.`name` = ‘三环’ and ls.lid = ks.lid;

#查询当前002编号的钥匙是哪个锁，返回锁名称和钥匙编号
select ls.`name`, ks.number from locks ls, keyes ks where ks.number = ‘002’ and ls.lid = ks.lid;

#查询LOL游戏有哪些玩家（多对多）
select gs.name, pr.name from games gs, games_player gp, player pr where gs.name = ‘lol’ and gs.gid = gp.gid and gp.pid = pr.pid;

显式内连接
查询时关键字使用 inner join on

#查询当前车名奔驰s200的车牌号
select c.`name` , cn.number from car c inner join car_number cn  on c.`name` = ‘奔驰s200’ and c.cid = cn.nid;

#查询lol游戏有哪些玩家
select gs.name, pr.name from games gs inner join games_player gp inner join player pr on gs.name = ‘lol’ and gs.gid = gp.gid and gp.pid = pr.pid;

【外连接查询】

左外连接查询
关键字： left join in
多表查询，条件匹配后以左表数据为主，右表如果没有匹配到数据或者没有左表的多，那么右表用null表示【以左为尊】

#左外连接
select * from car c left join car_number cn on c.cid = cn.nid;

右外连接查询
关键字：right join on
多表查询，条件匹配后以右表数据为主

#右外连接
select * from car c right join on car_number cn on c.cid = cn.nid;

【综合总结】

（一）创建库：

create database offcnoa;

（二）创建表

create table dept(
deptno int (2) not null,
dname varchar(14),
loc varchar(13),
primary key (deptno)
);
create table emp(
empno int(4) not null,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date default null,
sal double(7,2),
comm double(7,2),
primary key (empno),
deptno int(2)
);
create table salgrade(grade int,losal int,hisal int);

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 

 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, NULL, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); 

（三）基本表的介绍

dept (部门表)
DEPTNO (部门编号)
NAME(部门名称)
LOC(部门的地址)

emp(员工表)
EMPNO(员工编号)
ENAME(员工的名称)
JOB(工作岗位)
MGR（直接上级领导编号）
HIREDATE（入职日期）
SAL（薪资，工资）
COMM(补助)
DEPTNO（部门编号）

salgrade（薪资等级表）
GRADE(薪资编号)
LOSAL(最低薪资)
HISAL(最高薪资)

（四）基本的查询操作

（1）查询员工的姓名

select ename from emp;

（2）查询员工的薪水以及姓名

select ename,sal from emp;

（3）查询员工表所有的数据

Select * from emp;

（4）查询每一个员工的年薪

select ename,sal*12 from emp;

（五）条件查询

支持的运算符

运算符	说明
=	等于
<>或者!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and	两个值之间
is null 为null（	is not null 不为null）
and	并且
or	或者
in	包含（not in 不包含范围中）
not	not 否，主要和is或者in一起使用
like	模糊查询，支持% _ ; %：匹配任意字符 _：匹配一个字符

1、等于查询（=）
①#查询薪水为5000的员工

select * from emp where sal = 5000;

②#查询职位为manager 的员工的信息

select * from emp where job = ‘manager’;

2、不等号的查询（<> 或 !=）
①#查询薪水不等于5000的员工的信息

select * from emp where sal != 5000;

②#查询职位不是manager的员工的信息

select * from emp where job <> ‘manager’;

3、区间条件的查询（< , <= , > , >= ,between …and）
①#薪水大于1600的员工信息

select * from emp where sal > 1600;

②#薪水在1600和3000之间的员工信息

select * from emp where sal >= 1600 && sal <=3000;

③#薪水在1600和3000之间的员工信息，包含1600，3000

select * from emp where between 1600 and 3000;

4、或者（or）【慎用】
①#查询部门编号为20或者30的部门信息

select * from emp where deptno = 20 or deptno = 30;

5、包含，不包含查询（in， not in）
①#查询部门编号为20或者30的员工信息

select * from emp where deptno in (20,30);

②#查询部门编号不为20也不为30的员工信息

select * from emp where deptno not in (20,30);

6、关于空的查询（is null ，is not null）
①#查询没有补助的员工信息

select * from emp where comm is null;

②#查询有补助的员工信息

select * from emp where comm is not null;

7、模糊查询（like , % _）
(1)%匹配符号
(2)_占位符
①#查询名字带有s的员工信息

select * from emp where ename like ‘%s%’;

②#查询名字第一个字母带有s的员工信息

select * from emp where ename like ‘s%’;

③#查询名字第二个字母带有m的员工信息

select * from emp where ename like ‘_s%’;

（六）排序查询
order by asc（升序） desc（降序）
①　#根据员工的薪水降序排列

select * from emp order by sal desc;

②　#根据员工的薪水升序排列

select * from emp order by sal asc;

③　#员工入职日期降序查询

select * from emp order by hiredate desc;

④　#查询职位是manager的员工信息，并按薪资从高到底排序；

select * from emp where job = ‘manager’ order by sal desc;

（七）聚合函数
SQL存在一些聚合函数，不能直接在where后当作条件使用，一般使用方式是放在返回值项后 或者 放在having关键字后
1、计算操作
(1)求和sum

select sum(sal) from emp;

(2)平均数 avg

select avg(sal) from emp;

(3)最大值 max

select max(sal) from emp;

(4)最小值 min

select min(sal) from emp;

(5)总数 count

select count(*) from emp;

（八）去重复distinct

#去重复（忽略人的情况下，查询公司一共有几个部门）
select distinct deptno from emp;

如果返回值一旦有重复，可以使用这个关键字 去重复项。
（九）分组查询group by & having

#找出不同工作类别中的最高薪资
select job, max(sal) from emp group by job;
#找出不同工作类别中的最高薪资，显示时按薪资从高到底显示
select job, max(sal) ms from emp group by job order by ms desc;
#求每个部门的平均薪资
select deptno, avg(sal) from emp group by deptno;
#求每个岗位的最高薪资，除manager外
select job, max(sal) from emp where job <> ‘manager’ group by job;
#找出每个工作岗位的平均薪资，要求显示平均薪资大于2000的(如果某些操作必须先分组在执行条件，group后面是不允许直接加where条件，所以我们使用group给我们提供的having关键字实现)
select job ,avg(sal) a from emp group by job having a > 2000;

（十）Limit关键字
Limit (极限) 在SQL中这个单词用来做分页或者取前几条
1、取前几条数据

#取最高薪资的前三名
select * from emp order by sal desc limit 3;

2、分页
优势：
(1)用户体验好
(2)每次查询固定条数大大减少内存开销
Limit 参数1，参数2
参数1：查询的起始索引位置
参数2：返回的条数
案例：每页3条数据，查询每页信息

#第一页
select * from emp limit 0,3;
#第二页
select * from emp limit 3,3;
#第三页
select * from emp limit 6,3;

（十一）子查询

#找出薪资比公司平均薪资高的员工，要求显示员工的名字和薪资
select avg(sal) as t from emp;
select ename, sal from emp where sal > 2073;
#合并，一个SQL作为另一个SQL的条件
select ename, sal from emp where sal > （select avg(sal) as t from emp）;