文章目录
–
一、初识数据库
1.三层结构
1.所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（database manage system）
2.一个数据库中可以创建多个表，以保存数据（信息）。
3.数据库管理系统（DBMS）、数据库和表的关系如图所示∶
2.存储方式
3.SQL语句分类
DDL∶数据定义语句 【create 表，库…】；
DML∶数据操作语句【增加 insert，修改 update，删除 delete】
DQL∶数据查询语句【select】
DCL∶数据控制语句【管理数据库∶比如用户权限 grant revoke 】
二、学习数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] db name[create_specification [, create_specification…]
create_specification:
[DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name
1.CHARACTER SET∶指定数据库采用的字符集，如果不指定字符集，默认utf8 。
2.COLLATE∶指定数据库字符集的校对规则（常用的utf8 bin【区分大小写】、utf8_general ci【不区分大小写】注意默认是 utf8_general ci）【举例说明database.sql文件】。
2.查看、删除数据库
1.显示数据库语句∶SHOW DATABASES
2.显示数据库创建语句∶
SHOW CREATE DATABASE db name
3.数据库删除语句【一定要慎用】∶
DROP DATABASE [IF EXISTS] db name
3.备份数据库
（1）备份数据库（注意∶在DOS执行）
命令行 mysql dump-u用户名-p-B 数据库1数据库2数据库n>文件名.sq
（2）恢复数据库（（注意∶进入Mysql命令行再执行）
Source 文件名.sql
4.备份恢复数据库的表
备份库的表：mysql dump-u 用户名-p密码 数据库 表1表2表n>d\文件名.sql
5.创建表
CREATE TABLE table name （
field1 datatype,
field2 datatype,
field3 datatype
）character set 字符集 collate 校对规则 engine 存储引擎
field;指定列名 datatype∶指定列类型（字段类型）
character set∶如不指定则为所在数据库字符集
collate∶如不指定则为所在数据库校对规则
engine∶引擎
#指令创建表
#注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
6.Mysql常用数据类型(列类型)
（1）数值型(整数)的基本使用：
（2）如何定义一个无符号的整数：
- create table t10（id tinyint）; 默认是有符号的
- create table t11（id tinyint unsigned）; 无符号的
（3）数值型(bit)的使用：
1.基本使用
mysql> create table t05(num bit(8));
mysql> insert into t05 (1, 3);
mysql> insert into t05 values(2, 65);
2.细节说明
- bit 字段显示时，按照位的方式显示。
- 查询的时候仍然可以用使用添加的数值 。
- 如果一个值只有0，1可以考虑使用bit
- （1）可以节约空间，位类型。
- （2）M指定位数，默认值1，范围1-64 。
- （3）使用不多。
（4）数值型(小数)的基本使用：
（5）字符串的基本使用：
- CHAR(size) 固定长度字符串最大255字符
- VARCHAR(size) 0~65535
- 可变长度字符串 最大65532字节 【ut18编码最大21844字符 1-3个字节用于记录大小】
（6）字符串使用细节：
细节1
char（4）//这个4表示字符数（（最大255），不是字节数，不管是中文还是字母都是放四个按字符计算.
varchar（4）//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据
不管是 中文还是英文字母，都是最多存放4个，是按照字符来存放的.
细节2
char（4）是定长（固定的大小，就是说，即使你 插入 ‘aa’，也会占用分配的4个字符的空间.
varichar（4）是变长（变化的大小），就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（说明∶varchar本身还需要占用1-3个字节来记录存放内容长度）L（实际数据大小）+（1-3）字节
细节3
什么时候使用 char，什么时候使用varchar
1.如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等.
2.如果一个字段的长度是不确定，我们使用varchar，比如留言文章
查询速度∶ char> varchar
细节4
在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列，注意 Text不能有默认值.
大小0-2^16字节如果希望存放更多字符，可以选择MEDIUMTEXT 0-2^24 或者 LONGTEXT 0~2^32
（7）日期类型的基本使用：
CREATE TABLE birthday6 (t1 DATE,
t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULT
CURRENT TIMESTAMP ON UPDATE
CURRENT TIMESTAMP）;
timestamp时间戳 mysql> INSERT INTO birthday (t1,t2) VALUES ('2022-11-11,‘2022-11-1110:10:10’);
- 日期类型的细节说明 TimeStamp在Insert和update时，自动更新 datetime.sql
7.创建表
字段类型：
8.修改表
9.数据库-增删查改
1.Insert语句（添加数据）
2.Update语句（更新数据）
3.Delete语句（删除数据）
4.Select语句（查找数据）
（1）Insert语句：
细节说明：
- 插入的数据应与字段的数据类型相同。
- 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。
- 字符和日期型数据应包含在单引号中。
- 列可以插入空值[前提是该字段允许为空]，insert into table value(null)。
- insert intotab_name(列名…)values(),(),()形式添加多条记录
- 如果是给表中的所有字段添加数据，可以不写前面的字段名称。
- 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。
- 如果某个列没有指定notnull,那么当添加数据时，没有给定值，则会默认给null --如果我们希望指定某个列的默认值，可以在创建表时指定。
（2）Update语句：
细节说明：
- UPDATE语法可以用新值更新原有表行中的各列。
- SET子句指示要修改哪些列和要给予哪些值。
- WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行（记录）。
- 如果需要修改多个字段，可以通过 set 字段1=值1，字段2=值2…
（3）Delete语句：
细节说明：
- 如果不使用where子句，将删除表中所有数据。
- Delete语句不能删除某一列的值（可使用update 设为 null 或者"）
- 使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。
（4）Select语句：
- where子句中运算符：
-
orderby排序查询结果：
细节说明： -
order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
-
Asc 升序【默认】、Desc 降序
-
ORDER BY子句应位于 SELECT 语句的结尾。
10.合计/统计函数
（1）count函数：
Count 返回行的总数
（2）sun函数：
Sum函数返回满足where条件的行的和-一般使用在数值列
（3）avg函数：
AVG函数返回满足where条件的一列的平均值
（4）max / min 函数：
Max/min函数返回满足where条件的一列的最大/最小值
（5）group by 函数：
使用group by子句对列进行分组[先创建测试表]
（6）having 函数：
使用having子句对分组后的结果进行过滤
11.字符串相关函数
12.数学相关函数
rand() 返回一个随机浮点数 v，范围在 0 到 1 之间（即，范围为 0 <= v <= 1.0）。若已指定一个整数参数 v ，则它被用作种子值，用来产生重复序列。
13.时间日期相关函数
14.加密和系统函数
15.流程控制函数
16.表查询–加强（order by）
举例说明：
如何按照工资的从低到高的顺序[升序]，显示雇员的信息：
SELECT* FROM emp ORDER BY sal（默认升序）
按照部门号升序而雇员的工资降序排列,显示雇员信息 ：
ELECT*FROM emp ORDER BY deptno ASC,sal DESC
17.分页查询（limit）
推导一个公式
SELECTFROM emp
ORDER BY empno
LIMIT 每页显示记录数(第几页-1),每页显示记录数
数据分组的总结：
18.mysql多表查询
多表查询是指基于两个和两个以上的表查询.在实际应用中，查询单个表可能不能满足你的需求。
（1）自连接
自连接是指在同一张表的连接查询【将同一张表看做两张表】。
自连接的特点
1.把同一张表当做两张表使用
2.需要给表取别名表名表别名
3.列名不明确，可以指定列的别名列名as列的别名
19.mysql表子查询
什么是子查询：
子查询是指嵌入在其它sql语句中的 select 语句,也叫嵌套查询。
（1）单行子查询
单行子查询是指只返回一行数据的子查询语句
（2）多行子查询
多行子查询指返回多行数据的子查询==>使用关键字in
（2）多列子查询
多列子查序则是指查询返回多个列数据的子查询语句：(字段1，字段2…)=(select字段1，字段2from。。。。)
20.合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all
21.外连接
（1）左外连接（left outer join，outer可以省略)
左外连接是在两表进行自然连接，只把左表保留在结果集中，右表对应的列上填null。
Select * from table1 left join table2 on table1.C=table2.C
（2）右外连接(rignt outer join，outer可以省略)
右外连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。
Select * from table1 right outer join table2 on table1.C = table2.C
22.mysql约束
（1）基本介绍
（2）primarykey(主键)
primary key（主键）-细节说明：
1.primary key不能重复而且不能为nulI。
2.一张表最多只能有一个主键， 但可以是复合主键。
3. 主键的指定方式 有两种：
●直接在字段名后指定∶字段名 primakry key
● 在表定义最后写 primary key（列名）
4. 使用desc 表名，可以看到primary key的情况。
5. 老师提醒∶在实际开发中，每个表往往都会设计一个主键。
（3）not null(非空)
（4）unique(唯一)
unique 细节（注意）∶
- 如果没有指定 not null，则unique 字段可以有多个null
2.一张表可以有多个 unique 字段
（5）foreign key(外键)
用于定义主表和从表之间的关系∶
外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为nulI 。
FOREIGN KEY （本表字段名） REFERENCES
主表名（主键名或unique字段名）。
细节说明：
1.外键指向的表的字段，要求是primary key 或者是unique
2.表的类型是 innodb，这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致（长度可以不同）。
4.外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
5. 一旦建立主外键的关系，数据不能随意删除了.
（6）check
用于强制行数据必须满足的条件，假定在 sal 列上定义了 check 约束，并要求sal列值在1000-2000之间，如果不再1000-2000之间就会提示出错。
提示∶ oracle 和sql server 均支持check，但是mysql5.7 目前还不支持check，只做语法校验，但不会生效。
在mysq|中实现check的功能，一般是在程序中控制，或者通过触发器完成。
23.自增长
（1）使用方式：
字段名 整型 primary key auto_increment
添加 自增长的字段方式
insert into xx（字段1，字段2……）values（null，‘值’…）;
insert into xxx （字段2…）values（‘值1’，‘值2’…）;
insert into xxx values（null，‘值1’，….）
（2）使用细节：
- 一般来说自增长是和primary key 配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）
- 自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto_ increment = 新的开始值;
- 如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据.
24.索引
（1）索引原理：
- 没有索引为什么会慢? 因为全表扫描。
- 使用索引为什么会快? 形成一个索引的数据结构：
比如二叉树索引的代价：
a.磁盘占用。
b.对dml（update delete insert）语句的效率影响。
（2）索引类型：
- 主键索引，主键自动的为主索引（类型Primary key））
- 唯一索引 （UNIQUE）
- 普通索引 （INDEX）
- 全文索引 （FULLTEXT）【适用于MyISAM】
一般开发，不使用mysq自带的全文索引，而是使用∶全文搜索Solr 和 ElasticSearch（ES）
（3）索引使用：
（4）哪些列上使用索引：
较频繁的作为查询条件字段应该创建索引 select* from emp where empno = 1。
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件select* from emp where sex =‘男’。
更新非常频繁的字段不适合创建索引 select* from emp where logincount=1 。
不会出现在WHERE子句中字段不该创建索引。
25.事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的 dml 语句要么全部成功，要么全部失败。如∶转账就要用事务来处理，用以保证数据的一致性。
（1）事务和锁
（2）回退事务
在介绍回退事务前，先介绍一下保存点（savepoint）。保存点是事务中的点.用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点.。当执行回退事务时，通过指定保存点可以回退到指定的点。
（3）提交事务
使用commit语句可以提交事务.当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束子事务后，其它会话【其他连接】可以查到事务变化后的新数据【所有数据就正式生效】。
（4）事务细节
1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚。
2.如果开始一个事务，你没有创建保存点. 你可以执行 rollback，默认就是回退到你事务开始的状态。
3. 你也可以在这个事务中（还没有提交时），创建多个保存点.比如∶savepoint aaa; 执行 dml， savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点。
5.mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使.
6.开始一个事务 start transaction，set autocommit=off;
（5）事务隔离级别
a.事务隔离介绍：
多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
如果不考虑隔离性，可能会引发如下问题：
- 脏读
- 不可重复读
- 幻读
b.查看事务隔离级别：
1.脏读（dirty read）∶当一个事务读取另一个事务尚未提交的改变（update，insert，delete）时，产生脏读。
2.不可重复读（nonrepeatable read）∶同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
3.幻读（phantom read）∶同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
c.事务隔离级别：
d.设置事务隔离级别：
（6）事务ACID
a.事务特性：
- 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
- 隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 - 持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
b.表类型和存储引擎:
c.主要的存储引擎/表类型特点：
d.细节说明：
这里重点给大家介绍三种:MyISAM、InnoDB、MEMORY
- MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
- MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。
e.如何选择表的存储引擎:
1.如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快。
2.如果需要支持事务，选择InnoDB。
3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法用户的在线状态）
f.修改存储引擎:
26.视图
（1）基本概念：
- 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
- 视图和基表关系的示意图：
（2）基本使用：
- create view 视图名 as select语句
- alter view 视图名 as select语句 --更新成新的视图
- SHOW CREATE VIEW 视图名
- drop view 视图名1,视图名2
（3）视图细节：
（4）视图作用：
1.安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
-
性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
-
灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
27.Mysql管理
（1）Mysql 用户：
mysql中的用户，都存储在系统数据库 mysql 中 user 表中。
（2）创建用户：
create user ‘用户名’@'允许登录位置’identified by '密码’说明∶创建用户，同时指定密码。
（3）删除用户：
drop user ‘用户名’@‘允许登录位置’;
（4）用户修改密码：
修改自己的密码∶ set password = password（‘密码’）; 修改他人的密码（需要有修改用户密码权限）： set password for ‘用户名’@‘登录位置’ = password（‘密码’）;
（5）mysql中的权限：
（6）给用户授权：
（7）回收用户授权：
基本语法∶ revoke 权限列表 on 库.对象名 from’用户名"@"登录位置’;
（8）权限生效指令：
如果权限没有生效，可以执行下面命令. 基本语法∶ FLUSH PRIVILEGES;
（9）细节说明：
- 在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有连接权限 create user Xxx ;
- 你也可以这样指定： create user ‘xxx’@'192.168.1.%'表示xxx用户在
192.168.1.*的ip可以登录mysql - 在删除用户的时候，如果 host 不是%，需要明确指定’用户’@‘host值’
总结
这里简要介绍了Mysql的各种基本知识操作，如有错误还请及时通知，会在第一时间改正。写作不易，继续努力！！！
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
