MySQL知识

一、数据库

存储的数据的仓库
最早开始存储:定义临时变量 int a = 10 ;
数组存储数据:弊端长度固定
StringBuffer:可以存储数据—字符序列---->转换成String
Collection/Map:存储一些元素或者键值元素,集合创建完毕,使用完毕也就不存在了
IO流可以永久的存储数据,IO–写数据—读数据(字节流)耗时 (本地存储)

二、常见的数据库

使用数据库DataBase(db)存储数据 :
分为:
关系型数据库: 支持"事务系统":读/写的过程:保证数据的安全性/一致性
支持高并发访问 :同时多个同时访问数据库
存储大类型数据: 大字节类型/大字字符类型
oracle/sqlServer/MySql/SQLite(轻量级的关系型数据库)/mariaDB
中小型企业:mysql居多 (InnoDB—存储引擎:支持行级锁)
大公司:Oracle居多
非关系数据库
常见noSQL数据库:没有sql语句
redis---->作为 “缓存用户的”
key-value元素存储进去

三、MySQL数据库

1.操作数据库定义语句DDL

1)查询所有库
mysql> show databases;

2)创建一个新的数据库

2.1)create database 库名; 直接创建

​ mysql> create database mydb01;

2.2)create database if not exists 库名; 判断不存在库才创建
mysql> create database if not exists mydb_02;

3)查看创建指定库的字符集编码格式：show create database 库名;
mysql> show create database mydb01;

4)修改指定库的字符集格式
alter database 库名 (default) character set 字符集格式;

​ mysql> alter database mydb01 default character set = utf8;

5)删除库
5.1)drop database 库名; 直接删除
mysql> drop database mydb01;

5.2)drop database if exists 库名; 判断存在库名,直接删除
mysql> drop database if exists mydb_02;

6)进入到指定的库中–使用这个库
use 库名;—提示 "database changed"表示正在使用
mysql> use ee2302;

7)mysql字段的查询正在使用库的函数 select database() ;
mysql> select database();

2.表的基本数据类型

常见的数据表的字段类型:
1)int :整数类型 默认11位 里面所占的实际值 (推荐)
age年龄字段 ----20
int(指定的字符数)
int(3) 指定当前值的位数3位 1—>001 (不推荐,八进制0开头)
2)字符串 varchar(指定最大长度)
name字段—varchar(10) :最大长度10位
3)小数类型 double
double(3,1)—小数是3位,小数点后保留一位数
4)日期类型 date 仅仅表示日期
datetime:日期+时间
timestamp:时间戳(某个时间点去操作了数据库中某个表或者某个表的记录的时间 )或者瞬时时间

3.操作表的DDL语句

1)创建表的语法----创建表之前,选库(use 库名;)
create table 表名(
字段名称1 字段类型1 主键 自增长,
字段名称2 字段类型2,
字段名称3 字段类型3,
…,
字段名称n 字段类型n) ;

create table student(
  id int primary key auto_increment,
  name varchar(10),
  age int default 20,
  gender varchar(3),
  address varchar(50) unique,
  birthday date 
  source_id int ,
  constraint sour_stu_fk foreign key (source_id) references source(id));

2)查询库中的所有表
show tablse;

3)查询表的结构:查询这个表的"列"有哪些
desc 表名;

4)修改表—修改的表的字段名称(列的名称)
alter table 表名 change 以前的字段名称 现在的新的字段名称 以前的字段类型;

4.1) 修改表—修改表的字段类型
alter table 表名 modify address 对应的新的字段类型;

4.2) 修改表—给表中添加一个新的字段名称(新的列)
alter table 表名 add 字段名称 字段类型;

4.3) 修改表—将表中的字段名称删除
alter table 表名 drop 字段名称;

5)复制一张一模一样的表
create table 新的表名 like 旧表名;

6)删除表
6.1)drop table 表名 ;直接删除

6.2)drop table if exists 表名; 如果存在这个表,删除

注：查询mysql服务器中所有的跟字符集相关的指令:模糊搜索
show variables like ‘%character%’ ;

4.操作表记录的DML语句

1)插入

1）给表中插入全表记录: 每一个值需要和当前字段类型一致

1.1)insert into 表名 values(值1,值2,值3,…值n) ;

1.2)插入全表记录:一次性插入多条

​ insert into 表名 valeus(值1,值2,值3…值n),(值1,值2,值3…值n),(…) ;

1.3)插入部分字段,没有插入字段的值默认值null

​ insert into 表名(字段名称1,字段名称2,部分字段) values(值1,值2,部分字段值),(值1,值2,部分字段值) ;

插入表中记录注意事项:
1)插入全表字段的值的数量要和字段的数量匹配
2)插入同类型字段要注意(字段名称和字段值的类型必须匹配)

插入之后,记录最后一次自增长id的值
SELECT LAST_INSERT_ID() ;

2）删除

1)一般带条件删除(基本带条件)
delete from 表名 where 字段名称= 值 ;
delete from 表名 where 字段名称= 值1 and 字段名称2 = 值2 ;
满足多个条件:删除 (如果这个字段是业务字段:可能会出现重复)

2)不带条件删除

​ delete from 表名; :删除全表记录

​ 删除全表记录的:truncate table 表名

delete from 表名和truncate table 表名的区别?
共同点:
delete from 表名和truncate table 表名都是删除全表的数据
不同点:
delete from 表名:仅仅只是删除表的记录,表的结构还在(约束相关的信息也就在)
不影响自增主键id的值,下一次插入数据之后,id值在上一次基础上继续自增!
truncate table 表名:不仅仅删除全表的记录,
会将表删除(表的结构都存在了),会自己创建一张结构一一的空表
直接影响自增长主键id 的值;

3）修改

1)不带条件(where)修改,属于 “批量修改” (不推荐)

​ update 表名 set 字段名称 = 值 ;或者 update 表名 set 字段名称1= 值1 ,字段名称2 = 值2;

2)推荐:带条件修改

​ update 表名 set 字段名称1 = 值1,字段名称2=值2,…字段名称n = 值n where 非业务字段名称= 值;

5.查询表的DQL语句

1）单表查询

1）查询全表的记录
select 字段名称1,字段名称2,部分字段名称或者指定所有字段名称 from 表名;

2)查询的时候给字段名称起别名
select 字段名称1 as 别名1,字段名称2 as 别名2…from 表名; (as可以省略不写)

3)基本select查询,去除重复的字段值
(字段名称前面加上DISTINCT:去除重复字段值)
或者distinct(字段名称)

​ SELECT DISTINCT address ‘地址’ FROM student3;

ifnull(字段名称,预期值) ;如果当前字段值是null,给一个预期值

2）条件查询

1)基本条件 where条件
赋值运算符
比较运算符:<,><=,>=,!=,<>,BETWEEN 值1 AND 值2
逻辑运算符:&& || and or

2)聚合函数:查询出来结果:单行单列的数据
count(字段名称):统计总条数 —>count(非业务字段) :统计总记录数

​ max(字段名称):最大值
​ min(字段名称):最小值
​ avg(字段名称):平均分
​ sum(字段名称):求和

3)模糊查询(重点)
应用场景:前端搜索框—输入 “关键字”—进行搜索
select 字段列表 from 表名 where 字段名称 like ‘%关键字%’ ; ‘关键字%’
%:代表任意字符
_:代表一个字符

in关键字 (值1,值2,值3,值n) ; 代表多个or连接(多个并集查询)

3）排序查询

默认值asc :升序
desc:降序
多个排序条件
select 字段列表 from 表名 order by 字段名称1 排序规则,字段名称2 排序规则…;

4）分组查询

select 字段列表 from 表名 group by 字段名称; – 分组基本查询

一般情况:group by 和where 搭配使用

group by 后面跟分组字段,在select的时候-查询分组字段
聚合函数在group by的后面不能使用

一个sql语句,where条件,有分组group by ,有having(它的后面可以跟聚合函数)
依次先后顺序,先满足where,在分组,分组基础上进行筛选

5）分页查询

limit关键字
语法:select 字段列表 from 表名 limit 起始行数,每页显示的条数;
起始行数:从0开始 — = (当前页码数-1)*每页显示的条数;

6）多表查询

隐式内连接
select 指定的字段名称 from 表名1,表名2 where 条件1 and 条件2… ;
显示内连接
select 指定的字段名称 from 表名1 inner join 表名2 on 连接条件;
左外连接语法:将左表数据全部查询以及它们的交集部分的数据
select 指定的字段名称 from 表名1 left outer join 表名2 on 连接条件 where 附件条件…

6.数据库的备份和还原

DBA(数据库管理员) 设计完数据库,对库中的所有的数据以及结构进行备份;
防止数据库的信息丢失等等
数据库还原:就是将备份库 加载已知存在的库中(使用备份好.sql脚本文件,执行这个sql脚本)
备份和还原:
1)命令行方式的备份(dos管理员的运行)
mysqldump -uroot -p输入密码 库名称 > 指定磁盘路径 “d:\mysql_dump\xx.sql文件”
2)命令行方式的还原:
1.1)dos登录mysql,将之前的库删除
1.2)新建一个新的库,使用库
1.3)source 执行本地或者远程路径上的.sql文件 (加载指定的sql脚本文件)
使用图形界面化工具去备份和还原
任何的图形界面化工具都是一样的
备份:
在已知的库上右键—backup—>backup SQL 备份到本地磁盘上的sql脚本
还原:删除已知库,新建库,使用库, 存在库上右键—>import—>选择执行 execute SQL script 本地sql文件

7.数据库的约束

约束:限制用户操作数据库的行为
举例:插入数据的时候,直接插入null值(这个字段是业务字段,是null,没有意义)
通过约束将上面的用户插入非法数据的行为进行限制!

1）默认约束:default

添加默认约束后,没有插入的字段:默认约束default起作用!

2）非空约束not null

用户直接插入null值(非法数据),加入约束之后:Column ‘name’ cannot be null

sql更改表中默认约束(去掉默认约束)
删除name字段非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) ;

sql 添加字段的not null非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) NOT NULL ;

3）唯一约束unique不能重复

通过sql语句更改表,删除唯一删除
错误语法: alter table test modify telephone varchar(11) ;
正常语法:alter table 表名 drop index 唯一约束的名称 (默认和当前的字段名称一致)

通过sql添加唯一约束
alter table 表名 add constraint(声明) 唯一约束索引的名称 unique(给哪个字段名称);

4） 主键约束 primary key

特点:非空且唯一，不能直接插入null值,不能重复
primary key 作用每一张表的id(非业务字段,设置为主键)

sql语句直接主键删除
alter table 表名 drop primary key ; – 将唯一删除,not null非空还存在

sql语句添加主键约束(唯一特性加上)
alter table 表名 modify id int PRIMARY KEY ;

5）外键约束 foreign key

有了外键约束,直接操作主表(删除/修改),直接报错! 因为从表的数据和主表有关联!

通过sql 添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键作用从表的字段名称) references 主表名(主键字段) ;

通过sql删除外键约束

​ alter table 表名 drop foreign key 外键名称 ;

级联

级联操作:CASCADE(修改以及删除主表数据,从表数据随之改变)
级联删除和级联修改

8.数据库的三大范式

1NF:
数据库表中的每一列是独立,不能在拆分的原子数据项
2NF:
在1NF基础上
1)数据库指定表中,描述一件事情(一张表描述一件事情)
2)数据库表中的非主键字段必须完全依赖主键字段
3NF:
在2NF基础上
数据库表中非主键字段之间不能产生传递依赖!
非主键字段A—字段B---->字段C :产生传递依赖
解决冗余:比如多对多—拆分中间表:设置外键连接

9.事务

1）什么是事务

关系型数据库中–支持"多事务",都会在事务机制,当某个业务同时操作多个sql语句或者是多张表的sql插入/删除/修改),这多个sql要么同时执行成功,要么同时执行失败,保证数据的完整性!

2）事务的特点和事务的隔离级别

事务的特点
原子性:多个sql要么同时执行成功,要么同时执行失败,保证数据的完整性!
一致性:事务同时去操作多个sql的时候,需要保证数据的一致性!
隔离性:多个事务(业务和业务之间)之间互相独立的,互不影响
持久性:当事务一旦提交,对数据的更改是永久性的,即使关机也存在!
事务的隔离级别:
– 下面依次从小到大,安全性低大高,效率:从高到低;
read uncommitted ; 读未提交
read committed ; 读已提交
repetable read ; 可重复读 mysql的默认级别:有效防止脏读以及不可重复读
serializable ; 串行话

3）通过mysql指令如何控制事务

开启事务:将mysql自动提交,切换手动提交事务
start transaction ;
设置回滚命令
rollback;
永久更新数据(保证数据的持久性)
commit ;

设置隔离级别
set global transaction isolation level 级别名称;
查看隔离级别
select @@tx_isolation ;

10.视图

1）什么是视图

就是一个虚拟表:只是定义视图,表的数据来源于基础表中的(数据库中真实存在表)
作用:保证数据的安全性/简单性(快速查看到真实表的结构,类型…)

2）创建视图

创建视图
CREATE VIEW <视图名> AS <SELECT语句>

3）删除视图

删除视图:就是drop view 视图名称,多个视图名称;
只是的删除视图的定义,不会影响基础表的任何内容

4）修改视图

修改视图的内容
ALTER VIEW <视图名> AS <SELECT语句>