mysql数据库介绍

数据库DataBase（DB）
概述：
长期存放再计算机内有组织、可共享的数据仓库。
特性：
按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性和易扩散性，并可为各种用户共享。

------------------------------------------------------------------------------------------------
数据管理系统：
主流系统：
MySQL、Oracle、SQLite、Access、MS SQL Server
数据库服务器、数据管理系统、数据库、表与记录的关系:
记录：1 xxx 324245234 22（多个字段的信息组成一条记录，即文件中的一行内容）
表：即文件，用来存放多行内容/多条记录，student,scholl,class_list（即文件）
数据库：存储数据的仓库，组织文件/表；oldboy_stu（即文件夹）
数据库管理系统：管理和控制数据库的软件；如mysql。
数据库服务器：一台计算机，运行数据库管理软件（对内存要求比较高）

数据库服务器->数据库管理系统->数据库->表->记录

--------------------------------------------------------------------------------
Mysql：
概述：
基于socket编写的c/s架构的软件，是一个关系型的数据库管理系统。
关系型数据库管理系统(RDBMS)，是一种以相关表格形式存储信息的DBMS。
RDBMS基于DBMS。
数据库管理软件分类:
关系型:
关系型数据库需要有表结构.(表结构就是定义一个表的字段、类型、主键、外键、索引，这些基本的属性组成了数据库的表结构)
如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型:
key-value存储,没有表结构.
mongodb，redis，memcache；

-----------------------------------------------------------------------------------------
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

--------------------------------------------------------------------------------------------
存储引擎
数据库中的表有着不同的类型，不同的类型会对应mysql不同的存取机制,表类型又成为存储引擎；
ps: 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据
自己的需要编写自己的存储引擎

SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。
MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;
有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。

---------------------------------------------------------------------------------------------
mysql支持的存储引擎
1、InnoDB 存储引擎：支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。
2、MyISAM 存储引擎：不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。
3、Memory 存储引擎：正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。
4、BLACKHOLE：黑洞存储引擎，可以应用于主备复制中的分发主库。
5、NDB 存储引擎： NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
6、Infobright 存储引擎：第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。
7、NTSE 存储引擎：网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
---------------------------------------------------------------------------------------------------
表的操作
表的分类：
.frm是存储数据表的框架结构
.ibd是mysql数据文件
.MYD是MyISAM表的数据文件的扩展名
.MYI是MyISAM表的索引的扩展名
发现后两种存储引擎只有表结构，无数据
memory，在重启mysql或者重启机器后，表内数据清空
blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录

字段：表记录对应的标题，称为表的字段。

---------------------------------------------------------------------------------------------
数据类型
每种数据类型都有自己的宽度(字节)，但是宽度是可选的。
分类：
1、数值类<分为有符号(可插入负数)和无符号>
MySQL支持所有标注SQL数值类型数据。
数值插入默认是有符号的，
插入无符号：
create table 表名(x tinyint unsigned)
用零填充：
create table 表名(x tinyint(数值) unsigned zerofill)
整型：
小整数值(1字节)： tinyint
大整数值(2字节)： int
极大整数值(8字节)：bigint。
浮点型：
单精度浮点数值：float -在位数比较端得情况下不精准
双精度浮点数值：double -在位数比较长的情况下不精准
小数值： deecimal -非常精确
2、日期/时间
DATE - TIME - DATETIME - TIMESTAMP - YEAR
作用：
存储用户注册时间、文章发布时间、员工入职时间、出生时间、过期时间等。
获取当前类型指定的时间：
insert into 表名 values(now())
语法：
year
YYYY（1901/2155）
date
YYYY-MM-DD（1000-01-01/9999-12-31）
time
HH:MM:SS（'-838:59:59'/'838:59:59'）
datetime
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
timestamp
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
3、字符串(字符)类型
char类型：
特点：
定长，浪费空间，存取速度快。
字符长度范围：
0-255 (一个中文为一个字符，是utf8编码的3个字节)
存储：
存储char类型的值时，会往右填充空格来满足长度
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，
varchar类型：
特点：
变长，精确，节省空间，存取速度慢。
字符长度范围：
0-65535（如果大于21845会提示用其他类型，mysql行最大限制为65535字节，字符编码为utf-8）
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容。
length()：查看字节数。
char_length():查看字符数 。
4、枚举类型和集合类型
enum 单选 只能在指定的范围内选定一个值。
set 多选 在指定范围呢选择一个值或多个值。

--------------------------------------------------------------------------------------------------
完整性约束
约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为：

PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) #标识该字段为该表的外键
NOT NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT #为该字段设置默认值

 

UNSIGNED #无符号
ZEROFILL #使用0填充

#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

not null 与default：是否可空，null表示空，非字符串
不可空：not null
可空： null
unique（唯一）:
在innodb存储引擎中，每个表至少有一个主键，一般都是在ID上
单列唯一：只要列中内容相同就不能插入。
unique（唯一内容）
多列唯一(联合唯一)：只要内容中有一项不相同就可以插入。
unique(内容，内容)
primary key(主键)
单列做主键：不可为空+唯一。
primary key(内容);
多列做主键：联合做主键，不可为空+唯一。
primary key(内容，内容)
auto_increment(自增长)
delete from t1；删除表记录，但是保留ID自增长的记录。
truncate table student； 清空表，且清空ID自增长的记录。
查看可用的:show variables like 'auto_inc%'<auto_inc%是查找内容==='内容'%>
foreign key（外键）
优点：
1、节省内存空间
2、查询速度快
缺点：建立强关系。
1、先创建被关联表（主表）
2、在创建关联表（从表）
3、先往被关联表中插入数据，再插入到关联表中
?
?
外键的变种：三种关系
1、一对多（多对一）
先站在坐标的角度看，如果左表的多，对应着右边的一成立。反之，不成立
2、一对一
3、多对多

----------------------------------------------------------------------------------------------------------------
单表查询
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级（重点）
from、where、group by、having、select、distinct、order by、limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数

多表查询

navicat可视化工具

 

------------------------------------------------------------------------------------------------------------

mysql CMD指令:
初始化 mysqld --initialize-insecure
启动服务端 net start mysql
关闭服务端 net stop mysql
启动客户端 mysql -u root -p (启动后会连接已开启的服务端,root是最高权限 -u是user)
设置密码 mysqladmin -uroot -p password "密码"
查看登陆账户 select user();
更改密码 mysqladmin -uroot -p"4181117" password "q4181117"
忘记密码 1、管理员权限打开CMD
2、停止mysql服务 net stop mysql
3、跳过授权表 mysqld --skip-grant-tables
4、重新查看 mysql -uroot -p
5、更改密码 update mysql.user set authentication_string =password('密码') where User='root';
6、刷新权限 flush privileges;
7、退出 exit
8、用户加载权限 tasklist |findstr mysql
9、杀死当前进程 taskkill /F /PID 6052
10、还原 net start mysql
mysql -uroot -p

统一字符编码 1、查看 \s
2、在mysql文件下穿件 my.ini文件
3、将一下内容拷贝到文件中
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
4、重启服务
mysql文件夹操作<库的操作>
增
create database db1 charset utf8; <创建 数据库 db1 编码方式为utf8；>
查
查看当前创建的数据库
show create database db1;
查看所有数据库
show databases;
改
alter database db1 charset gbk; <改变 数据库 db1 编码方式为gbk>
删
drop database db1； <减少 数据库 db1>
mysql操作文件<操作表>
切换文件夹 use db1; <使用 db1>
查看当前所在文件夹 select database(); <挑选 所有数据库>
增
create table t1(id int,name char); <创建 表单 t1(id 整数，name 人名)>
查
查看当前的这张t1表 show create table t1; <显示 创建 表单 t1>
查看所有表 show tables; <显示 所有表单>
改
修改name字符长度
alter table t1 modify name char(6); <char(6)是指name的字符长度；modify是修改的意思>
修改name为大写NAME
alter table t1 change name NAME char(7);
删
drop table t1;
mysql操作文件内容(记录)
增
插入数据，规定id，name数据leilei
insert t1(id,name) values(1,"mjj01"),(2,"mjj02"),(3,"mjj03");
查
select id from db1.t1;
select id,name from db1.t1;
select * from db1.t1;
改
update db1.t1 set name='zhangsan';
update db1.t1 set name='alex' where id=2;
删
delete from t1;
delete from t1 where id=2;
数据库操作
查看系统库
show databases;
虚拟库:不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等.
information_schema;
特殊数据库：MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象.
performance_schema;
授权库：主要存储系统用户的权限信息。
mysql；
测试数据库：MySQL数据库系统自动创建的测试数据库。
test；
创建数据库
1、求救语句：help create database；
2、创建数据库语法：CREATE DATABASE 数据库名 charset utf8；
ps：数据库命名规则与变量命名规则一样。
数据库相关操作
查看数据库：show databases;
查看当前库：show create database db1;
查看所在的库：select database();
选择数据库： use 数据库名;
删除数据库： DROP DATABASE 数据库名；
修改数据库： alter database db1 charset utf8;
指定表类型/存储引擎：create table t1（id int）engine=innodb;

表的操作
创建表：
create table 表名(
字段名 类型[(宽度 约束条件)]，
字段名 类型[(宽度 约束条件)]，
)
ps：1、同一张表中，字段名不能相同、2、宽度和约束条件可选;3、字段名和类型是必须的。

<整体流程>：
1、创建数据库：create database 数据库名 charset utf8;
2、进入数据库：use ；
3、创建表：
create table 表名(
id int,
name varchar(50),
age int(3));
4、插入表记录：
insert into 表名 values
(1,'mjj',18) <(id,name,age)>
5、查看表的数据和结构：
(1)查看表储存的数据
select * from 表名; <选择 * 选择 表名>
(2)查看表的结构
desc 表名; <降序排列 表名>
(3)查看表的详细结构
show create table 表名\G; <显示 创建 表 表名/G>
6、复制表：
1、创建新的数据库：
create database 数据库名 charset utf8;
2、进入新的数据库：
use 数据库名；
3、复制表：
（1）完全复制表结构和表记录
create table 新表名 select * from 旧数据库名.被复制表名；
（2）只复制表结构不复制记录（在db2数据库下新创建一个b2表，给一个where条件，条件要求不成立，条件为false，只拷贝表结构）
1、create table 新表名 select * from 旧数据库名.被复制表名 where 1>5;
2、create table 新表名 like 旧数据库名.被复制表名;
7、删除表
drop table 表名；

关联约束表：
外键
1、创建被关联表
正常创建，create table dep(id int primary key,name varchar(20) not nill);
2、创建关联表
增加约束，create table emp(id int primary key,name varchar(20) not null,
dep_id int,<增加与被关联表之间关联的内容>
constraint fk_dep foreign key(dep_id) references dep(id)
<约束 fk_dep 设置关联（关联表的关联内容）参考内容 被关联文件（id）>
);
同步删除，同步更新(修改)：
1、同步删除：on delete cascade
2、同步更新：on update cascade
表的三种关系
1、一对多或者多对一
关联方式：foreign key
(1)创建被关联表(多) create table press( id int primary key auto_increment,name varchar(20));
(2)创建关联表(一) create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
constraint fk_book_press foreign key(press_id) references press(id)
on delete cascade <关联删除>
on update cascade <关联修改>
2、多对多
关联方式：foreign key+一张新表单
(1)创建两个被关联表(两个多)：
1、create table author(id int primary key auto_increment,name varchar(20));
2、create table press( id int primary key auto_increment,name varchar(20));
(2)创建存放两张表的关系的表单：
<先创建好表单框架，把两个表的关联部分创建出来>
create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,
<设置关联的两张表单的关联关系>
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
<设置两个表单的关联>
primary key(author_id,book_id)
(3)依次给两个被关联表单插入内容，再给关系表单插入内容。
3、一对一
关联方式：foreign key+unique
(1)、创建被关联表：
create table user(id int primary key auto_increment,name varchar(20));
(2)、创建关联表：
create table blog(id int primary key auto_increment,url varchar(100),
user_id int unique,<关联字段添加唯一>
constraint fk_user foreign key(user_id) references user(id)
on delete cascade on update cascade);
数据的增删改查
重点：1、插入数据 insert 2、更新数据 update 3、删除数据 delete
在MySQL管理软件的SQL语言中操作语言为DML语言。
插入数据insert
1、插入完整的数据(顺序插入)
(1) insert into 表名(字段1，字段2，字段3...字段n) values(值1，值2，值3...值n)；
(2) insert into 表名(值1，值2，值3...值n)；
2、指定字段插入数据
insert into 表名(字段1，字段2，字段3...) values (值1，值2，值3...);
3、插入多条记录
insert into 表名 values
(值1,值2,值3....值n),
(值1,值2,值3....值n),
(值1,值2,值3....值n),
(值1,值2,值3....值n),
(值1,值2,值3....值n);
4、插入查询结果
insert into 表名(字段1,字段2,字段3...字段n)
select(字段1,字段2,字段3...字段n) from 表名2
where 约束条件；
更新数据(更改数据)update
update 表名 set
字段1 = 值1,
字段2 = 值2,
where condition; <where 约束条件>
删除数据delete
delete from 表名
where condition; <where 约束条件>
表单查询
语法：
select 字段1,字段2... from 表名
where condition<约束条件>
group by field
having 筛选
order by field
limit 限制条数
*****{重点：
关键字的执行优先级(查询时的优先级)
1、找到表 from
2、拿到where指定约束条件，进入表中获取记录 where condition
3、将记录进行分类 group by，不进行分类就是对整体数据进行操作
4、将分组结果进行条件筛选 having 筛选条件
5、执行 select <选择>
6、去重
7、将结果按条件进行排序 order by
8、限制结果显示条数

where方法
1、where 约束
select 字段 from 表单 where 约束条件
2、and 多条件
select 字段 from 表单 where 约束条件 and 约束条件
3、between and 在两者之间
select 字段 from 表单 where between 约束条件 and 约束条件
4、update 刷新权限
表名 set 字段 where 更新点；
5、in | not in 存在或不存在
in 存在
not in 不存在
6、like 模糊查找
select * from 表名 where 字段 like '不完全关键字%|%不完全关键字'
group by分组查询
设置only_full_group_by
set global sql_mode='only_full_group_by'
聚合函数：
max() 最大值
min() 最小值
avg() 求平均值
count() 求总和
group_concat() 表示分组查询状态下，这个组别之下有哪些这个字段的数据。
分类格式：
select 分类，函数(参数项) from 表名 group by 分类；

having 过滤(二次约束)
二次过滤必须在分组group by后面，可以使用分组得字段
order by 排序：
单列排序
select 字段 from 表单 order by 排序根据 排序方式<asc 正排序><desc 倒排序>默认正排序
多列排序
<先按照age升序排序，如果年纪相同，则按照id降序>
SELECT 字段 from 表单
ORDER BY 根据 方式,
根据 方式;
limit 限制查询的记录数
SELECT 字段 FROM 表单 ORDER BY salary DESC
LIMIT 5,5; <起始值，往后查询的数量>
多表查询
外链接
链接所有内容
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2 <inner 内连接><left 左连接><right 右连接>
ON 表1.字段 = 表2.字段;

内连接：A INNER JOIN B ,在A中也有，在B中也有的数据才能查询出来
左连接：A LEFT JOIN B,连接查询的数据，在A中必须有，在B中可以有可以没有
右连接与左连接相反
(1)内连接，两表平等，有彼此共同字段
select * from 左表 inner join 右表 on 左表.相同项=右表.相同项;
(2)外链接之左连接
select * from 左表 inner join 右表 on 左表.相同项=右表.相同项;
select * as depart_name<相同项命名> from 左表 left join 右表 on 左表.相同项=右表.相同项;
(3)

符合条件连接查询

权限管理
创建用户名和密码
创建用户
create user '用户名'@'ip地址(任意ip为%)' identified by '密码';
<创建> <账户> <识别>
删除用户
drop user '用户名'@'ip地址';
<减少><账户>
修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
<重命名>
修改密码
set password for '用户名'@'ip地址'=Password('新密码')
用户授权
查看权限
grant select ,insert,update on db1.t1 to "mjj"@'%';
用户授权
权限：
查询:select
插入:insert
更新:update
删除:drop
所有权限:all privileges
grant 权限 on 数据库.表 to "账户"@'ip地址';
<grant> <*表示所有>
移除权限
取消用户对某表的任意操作
revoke all on 数据库.表单 from '用户'@"%";
取消来自远程服务器的用户对某数据库的所有表的所有权限
revoke all on 数据库.* from '用户'@"%";
取消来自远程服务器的用户所有数据库的所有的表的权限
revoke all privileges on *.* from '用户'@'%';
数据备份
备份：数据表结构+数据
mysqdump -u root db1 > db1.sql -p
# 备份：数据表结构
mysqdump -u root -d db1 > db1.sql -p
导入现有的数据到某个数据库
1.先创建一个新的数据库
create database db10;
2.将已有的数据库文件导入到db10数据库中
mysqdump -u root -d db10 < db1.sql -p

时间表单：
获取当前类型指定的时间：
insert into 表名 values(now())