MySQL基础

什么是数据库

数据库是一个存在于应用层的网络服务器，它类似于一个文件系统，使用户或用户程序更方便地管理系统上的文件。

数据库还是要自己去访问文件的

1. 数据库也是用文件的方式进行数据保存的

2. 数据可中直接和文件打交道的，是数据库的存储引擎

服务器、数据库、表的关系

SQL分类

DDL（data definition language）

数据定义语言，用来维护存储数据的结构。如：create、drop、alter等

DML（data manipulation language）

数据操纵语言，用来对数据进行操作。如：insert、delete、update等

DML中又单独分了一个DQL，数据查询语言，如：select。

DCL（data control language）

数据控制语言。主要负责权限管理和事务。

如：grant、revoke、commit等

mysql库操作

操纵数据库

创建数据库：create database 库名;

显示创建语句：show show create database 库名;

删除数据库：drop database 库名;

查看数据库：show databases;

修改数据库：alter database 库名 charset=字符集/collation=校验规则;对数据库的修改主要指修改数据库的字符集、校验规则。

字符集和校验规则

字符集指编码规则，校验规则指解码规则。

校验规则如：utf8_general_ci（不区分大小写），utf8_bin（区分大小写）

查看系统默认字符集以及校验规则

show variables like 'character_set_database';

show variables like 'collation_database';

查看数据库支持的字符集

show charset;

查看数据库支持的字符集校验规则

show collation;

数据的备份和还原

备份库：mysqldump -uroot -p -B 库名 ... > 需要备份到的路径;备份数据库时，如果没有带-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

备份表：mysqldump -uroot -p 库名 表名 ... > 需要备份到的路径;

还原：source 完整路径;

查看连接情况

语法：show processlist;

mysql表操作

创建表：

create table 表名 (
    field1 datatype,
    field2 datatype,
    field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

查看表结构

desc 表名;

删除表

drop table 表名;

修改表

添加列：alter table 表名 add 列名 类型, ... (after 列名);

修改列：alter table 表名 modify 列名 类型, ...;

删除列：alter table 表名 drop 列名;

数据类型

当插入的数据不合法时，MySQL会直接报错，中断本次操作。

set和enum的区别

set类型表示一个集合，对应的数据可以是set里面的多个元素。

enum类型表示枚举，对应的数据只能是enum中的一个元素。

datetime和timestamp的区别

1.datetime占用8字节，timestamp占用4字节。

2.对含有timestamp类型列的某行数据做任何操作，都会更新timestamp到当前时间。

表的约束

什么是约束？

约束是指强迫我们做出的操作符合某些条件，来保证数据的合法性。

有哪些约束？

not null

对应的字段不能为空。

default

默认值，插入时如果我们没有指定值，该列对应的字段会使用default中的值。

comment

列描述，用来在创建表时，标识字段的含义。

zerofill

配合数据类型后面的长度使用，当未达到对应长度时，会用 '0' 来填充。

primary key

主键，用来唯一地约束该字段里面的数据，不能为空，不能重复，一张表中至多有一个主键。主键所在列通常时int类型。

unique key

唯一键，与主键类似，用来唯一地约束该字段里面的数据不能重复。

auto_increment

自增长，若一张表的某个字段被标识auto_increment，那么这张表就会保存auto_increment的值，为当对应的字段中的最大值+1，当对应字段不给值时，会自动将auto_increment中的值赋给该字段。通常搭配主键使用。

特点：

1. 任何一个字段要设置自增长，前提是本身是一个索引。

2. 自增长字段必须是整数。

3. 一张表至多存在一个自增长。

foreign key

外键，用于定义主表和从表之间的关系，外键对应的字段必须是另一张表的主键或唯一键，外键对应字段中的值，必须在主表中存在。当从表中外键对应字段若存在值，则主表无法删除对应字段的数据。

主键与唯一键的区别

1.主键在一张表中至多有一个，唯一键在在一张表中可以有多个。

2.主键对应的字段不能为空，而唯一键对应的字段可以为空，为空的字段不做比较。

3.主键更多的是标识唯一性的，唯一键则更多保证不要和别的信息重复。

外键和外键约束

外键是用来实现表和表之间约束的关系。

外键约束是用来强约束表之间的关系，是通过外键来实现的

基本查询

Create

单行全列插入

insert into 表名 values (值...);

多行指定列插入

insert into 表名 (列名...) values (值...), (值...);

插入时存在则更新

insert into 表名 (列名...) values (值...) on duplicate key update 列名=值, ...;

替换

replace into 表名 (列名...) values (值...);

若主键或唯一键没有冲突，直接插入，若冲突，则删除后再插入。

Retrieve

全列查询

select * from 表名;

指定列查询

select 列名, 列名, ... from 表名;

查询字段可以为表达式

select 列名+... from 表名;

为查询列指定别名

select 列名 as 别名 from 表名;

结果去重

select distinct 列名 from 表名;

where条件

select 列名 from 表名 where 条件;

空查询

select 列名 from 表名 where 列名 is null;

select 列名 from 表名 where 列名 is not null;

结果排序

select 列名 from 表名 order by 列名 desc;降序

select 列名 from 表名 order by 列名 asc;升序

筛选分页结果

select 列名 from 表名 [where 条件] [order by 列名] [limit n];从0开始选n条结果。

select 列名 from 表名 [where 条件] [order by 列名] [limit s, n];从s开始选n条结果。

select 列名 from 表名 [where 条件] [order by 列名] [limit n offset s];从s开始选n条结果。

Update

update 表名 set 列名=值 [where 条件] [order by 列名] [limit n offset s];

Delete

delete from 表名 [where 条件] [order by 列名] [limit n offset s];

截断表

truncate 表名;

与delete的区别

1.只能对整表操作，不能针对部分数据操作。

2.实际上mysql不对数据操作，所以比delete更快，但truncate删除数据的时候，不经过真正的事务，无法回滚。

3.会重置auto_increment项。

插入查询结果

先执行select，把查询出来的结果插入到一张表中，需要表的结构与查询结果一致。

insert into 表名 select 列名 from 表名;

聚合函数

count()、sum()、avg()、max()、min()。

group by

将查询结果按照指定列分组。

select 列名 from 表名 group by 列名;

where与having的区别

where在查询结果前执行，用来筛选数据。having在查询结果后执行，用来对结果进行过滤。

having经常和group by搭配使用。

内置函数

日期函数

字符串函数

  

数学函数

其他函数

user()，查询当前用户。

md5(str)，对一个字符串进行md5摘要，摘要后得到一个32位字符串。

database()，显示当前正在使用。

password()，MySQL数据库使用该函数对用户加密。

ifnull(val1, val2)，如果val1为null，返回val2，否则返回val1的值。

复合查询

多表查询

当对多表进行查询时，表之间通常存在外键约束，表中的数据会做笛卡尔积，此时需要使用where条件过滤出有效数据。

例：显示雇员名、雇员工资以及所在部门的名字。

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

自连接

指在同一张表连接查询。

例：显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）。

select leader.empno, leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';

子查询

单行子查询

例：显示SMITH同一部门的员工。

select * from emp where deptno = (select deptno from emp where ename='smith')

多行子查询

in关键字

例：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10) and deptno!=10;

all关键字

例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno=30);

any关键字

例：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);

多列子查询

例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。

select ename from emp where (deptno, job)=(select deptno, job from emp where ename='smith') and ename!='smith';

在from子句中使用子查询

例：显示每个高于自己部门平均工资的员工的姓名、部门、工资。

select ename, deptno, sal from emp, (select avg(sal) as avg_sal, deptno from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal > avg_sal;

合并查询

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

例：显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资。

select ename, sal, job from EMP where sal>2500 union select ename, sal, job from EMP where job='MANAGER';

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

例：显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资。

select ename, sal, job from EMP where sal>2500 union all select ename, sal, job from EMP where job='MANAGER';

内外连接

内连接

内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选，之前的查询都是内连接。

语法：select 列名 from 表1 inner join 表2 on 连接条件 and 其他条件;

外连接

左外连接

使用多表查询，左侧的表完全显示，就称为左外连接。以左边为主，如果左表中的字段在右表中不存在，结果会以null的形式显示。

语法：select 列名 from 表1 left join 表2 on 连接条件 and 其他条件;

右外连接

使用多表查询，右侧的表完全显示，就称为右外连接。以右边为主，如果右表中的字段在左表中不存在，结果会以null的形式显示。

语法：select 列名 from 表1 right join 表2 on 连接条件 and 其他条件;