MySQL
什么是数据库
数据库(DB,DataBase)
概念:数据仓库,软件,安装在操作系统之上,跨平台,可以存储大量数据,500万条以上需要进行索引等处理
关系型数据库(SQL)
-
MySQL、Oracle、Sql server、DB2\SQLlite
-
通过表和表之间,行和列之间的关系进行数据的存储
优点:易于维护,都是表结构,格式一致
使用方便,通用的sql语言,可进行复杂查询
复杂操作,支持sql,可用于一个表和多个表之间非常复杂的查 询
缺点:读写性能比较差,尤其是海量数据的高效率读写
固定的表结构,灵活度不足
高并发读写需求,对于传统的关系型数据库来说硬盘I/O是个挑 战
非关系型数据库(NoSQL)Not Only
-
Redis、MongDB…
-
非关系型数据库严格来说并不是一种数据库,是一种数据结构化存储方法的集合
优点:格式灵活,存储的数据和格式可以是键值对、文档、图片等等,使用灵活、应用场景广泛,关系型数据库只支持基础类型(数据类型多样,不需要实现设计数据库,随取随用,如果数据量十分大的表,很多人无法设计)
速度快,nosql可以使用硬盘或者随机存储器作为载体,关系型数据库只能选择硬盘(大数据量高性能,Redis一秒写8万,读取11万,NoSQL的缓存记录是一种细粒度的缓存,性能比较高)
高扩展性,解耦,数据之间没有关系
成本低,nosql数据库部署简单,基本都是开源软件
大数据量高性能
缺点:不提供sql支持,学习和使用成本高
无事务处理
数据结构相对复杂,复杂查询稍欠
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理数据,维护和获取数据
- MySQL,数据库管理系统
MySQL安装
-
下载后得到zip压缩包
-
解压到自己想要安装的目录下
-
添加环境变量
a. 我的电脑->属性->高级->环境变量
b. 在PATH中添加,MySQL文件夹下的bin
c. 在MySQL文件夹下新建配置文件my.ini(exe自带,压缩包没有)
[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] # 设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=D:\Mysql-8.0.11\mysql-8.0.11-winx64 # 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错 # datadir=D:\Mysql-8.0.11\mysql-8.0.11-winx64\data # 允许最大连接数 max_connections=20 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB
d. 启动管理员模式下的cmd,进入mysql的bin,然后输入mysqld -install(安装mysql)
e. 输入mysqld --initialize-insecure --user=mysql初始化数据文件
f. 启动mysql修改密码
#启动服务 net start mysql #连接数据库 mysql -u root -p #修改用户密码 update mysql.user set authentication_string='password(123456)' where user ='root' and Host='localhost'; #刷新权限 flush privileges; #退出mysql编辑器 exit
g. 重启MySQL
net stop mysql net start mysql #测试修改密码 mysql -u root -p
清空服务
sc delete mysql
E:\MySQL\mysql-8.0.23-winx64\bin>mysqld -install The service already exists! The current server installed: E:\MySQL\mysql-8.0.23-winx64\bin\mysqld MySQL E:\MySQL\mysql-8.0.23-winx64\bin>mysqld --initialize-insecure --user=mysql E:\MySQL\mysql-8.0.23-winx64\bin>net start mysql MySQL 服务正在启动 ... MySQL 服务已经启动成功。 E:\MySQL\mysql-8.0.23-winx64\bin>mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> update mysql.user set authentication_string='password(123456)' where user ='root' and Host='localhost'; Query OK, 1 row affected (1.81 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.06 sec) mysql> exit Bye E:\MySQL\mysql-8.0.23-winx64\bin>net stop mysql MySQL 服务正在停止.. MySQL 服务已成功停止。 #执行下面语句前删除data文件夹,相当于重置数据库 E:\MySQL\mysql-8.0.23-winx64\bin>mysqld --initialize-insecure --user=mysql E:\MySQL\mysql-8.0.23-winx64\bin>net start mysql MySQL 服务正在启动 ... MySQL 服务已经启动成功。
创建数据库
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8 COLLATE utf8_general_ci; #CHARSET设置编码 #COLLATE设置字符集
问题:字符集的概念?编码和字符集的分别解释
删除数据库
drop database if exists west;
查看所有数据库
show databases;
使用数据库
use 数据库名;
创建表
create table student(id int(10) not null comment '学员id',name varchar(100) not null comment '学员姓名',age int (3) not null comment '学员年龄', primary key(id)) comment='学生表' engine=INNODB charset=utf8 collate=utf8_general_ci; #comment备注 #engine设置存储引擎类型 #charset设置编码 #collate设置字符集
修改表明
alter 旧表名 rename 新表名;
增加字段
alter table test1 add name int(10) not null;
修改字段
#修改字段名(也可以同时修改字段的属性,包括大小、类型、是否为空、设为主键等等) alter table test change name name1 int(10); #注:表中字段数据完全符合转换条件时可以修改,存在不符合新修改类型或大小时会报错 #修改字段的属性,包括大小、类型、是否为空、设为主键等等 alter table test modify name varchar(10);
删除字段
alter table test drop 字段名;
删除表
drop table 表名; #如果存在表再删除,防止报错(其他的新增或删除也可以加if exists) drop table if exists 表名;
显示所有的表
show tables;
显示表的详细信息
desc student;
显示主键信息
show keys from student;
显示表所有字段信息
show full fields from student;
显示建库语句
show create database school;
显示表创建语句
show create table student;
显示表索引
show index from student;
操作数据
#查询 select * from student; #删除 delete from student; #新增 insert into student (id,name,age) value ('1','张三','15'); #更新
ctrl+c强行终止命令(mysql中也可以)
如果表名或字段名是关键词,使用时加··(tab键上面的符号)
数据库的列类型
数值
-
tinyint(1byte) 十分小的数据
-
smallint(2byte) 较小的数据
-
mediumint(3byte) 中等大小的数据
-
int(4byte) 正常的整数(常用)
-
bigint(8byte) 较大的数据
-
float(4byte) 浮点数
-
double(8byte) 浮点数
-
decimal 字符串形式的浮点数(金融计算的时候,一般使用)
字符串
-
char(0~255) 字符串固定大小
-
varchar(0~65535) 可变字符串(常用),对应String
-
tinytext(2^8-1) 微型文本
-
ext(2^16-1) 文本串(保存大文本)
时间日期
-
date yyyy-MM-dd,日期
-
time HH:mm:ss,时间
-
datetime yyyy-MM-dd HH:mm:ss 最常用的时间格式
-
timestamp 时间戳,1970.1.1到现在的毫秒数(也较常用)
-
year 年份表示
null
-
没有值,未知
-
注意,使用null进行运算结果还是null,没有意义
-
数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明之后这个字段的值不能存储负值
create table test(id int(10) not null,age int(5) unsigned);
#字段后面添加 unsigned
#not null 非空,赋值时,如果字段没值会报错
zerofill:
- 0填充
- 定义字段长度,不足的位数用0填充
create table test (id int(10)default 0,age int(5) zerofill);
#字段后面加zerofill,位数不足补0,用于整型
#default 无值时填充默认值
自增:
- 通常用来设计唯一主键,必须是整数类型
- 可以自定义起始和步长
create table test(id int(10) auto_increment,age int(5),primary key(id));
#字段后面加上auto_increment(自增列只能有一个,而且必须是主键)
#查看自增值得起始值和步长
show variables like '%auto_inc%';
#修改自增步长(全局)
set auto_increment_increment=3;
数据引擎
InnoDB和MyISAM
MyISAM | InnoDB | |
---|---|---|
存储结构 | 每张表存放在三个文件:frm-表格定义、MYD(MYDATA)-数据文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件之中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB |
存储空间 | MyISAM可被压缩,存储空间较小 | InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储,所以跨平台的数据转移中会方便,在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份binlog,或者用mysqldump,在数据量达到几十G的时候就相对痛苦了 |
文件格式 | 数据和索引是分别存储的,数据.MYD,索引.MYI | 数据和索引是集中存储的,.ibd |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) | 表级锁定 | 行级锁定、表级锁定,锁定力度小并发能力高 |
SELECT | MyISAM更优 | |
INSERT、UPDATE、DELETE | InnoDB更优 | |
select count(*) | MyISAM更快,因为MyISAM内部维护了一个计数器,可以直接获取 | |
索引的实现方式 | B+树索引,MyISAM是堆表 | B+树索引,InnoDB是索引组织表 |
哈希索引 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
MySQL数据管理
对于外键(尽量不去使用,删除或者修改表时要考虑主从表关系,会很麻烦)
引用别人的解释数据库设计可以不要外键的7个理由
DML语言
数据操作语言
-
insert
insert into 表名 (字段) value (值);insert into 表名 (字段) values (值);
-
update
update 表名 set 字段名=值,字段名=值;#随笔:CURRENT_TIME获取当前时间
-
delete
delete from 表名;
-
truncate(清空表)
truncate 表名;
truncate和delete的区别
-
相同点:都能删除数据,都不会删除表结构
-
不同:
a. truncate 重新设置自增列,计数器会归零
b. truncate 不会影响事务
delete清除表,重启数据库,现象:
- innoDB:自增序列会重新计算(序列存在内存当中,服务停止会消失)
- MyISAM:序列不会重新计算(序列存在于文件当中,不会丢失)
DQL查询数据
Data Query Language:数据查询语言
concat(a,b) 拼接字符串
select concat('姓名:',name) as 人物 from student;#concat能够将括号中的逗号分割的两个值合并
dinstinct
select distinct age,id from test;
- 可以对null进行去重
- 放在字段最前方
- 能够对多字段去重
- 可以对*操作
version() 版本号
select version();#显示MySQL版本号
用来计算
select 2*10-1;
获取自增步长
select @@auto_increment_increment;
模糊查询
select * from student where name like '李_';#一个_代表一位select * from student where name like '%李';#%代表这个位置的所有符号
join
笛卡尔积
select * from a,b;select * from a cross join b;select * from a full join b;#两表相乘
inner join (取交集)
select * from a innner join b where a.字段=b.字段;
left join (取左表)
select * from a left join b where a.字段=b.字段;
right jion(取右表)
select * from a innner join b where a.字段=b.字段;
full join
select * from a full join b on a.id=b.id;#会报错,Unknown column 'a.id' in 'on clause'#等效实现select * from a left join b on a.id=b.idunionselect * from a right join b on a.id=b.id;
limit 起始值,数据量
select * from student order by id desc limit 2,2;#起始位置是0,#2,2 第一个2代表从第三行数据开始,第二个2表示展示到第四行,也就是展示了符合查询条件的第三条和第四条数据
count
select count(字段) from 表名;#count(字段)不统计字段为null的数据#count(1)统计全部数据
执行效率
字段为主键时:count(字段)比count(1)更快
字段不为主键:count(1)比count(字段)更快
没有主键时:count(1)效率优于count(*)
有主键:count(主键)是最优的
表只有一个字段:count(字段最优)
使用组合:聚合函数+group by +having
数据库级md5加密
md5(值)
select
select all/distinct 字段 from 表名 where 条件 group by 分组条件 having 分组过滤 order by 排序条件 limit 数据范围
事务
事务原则:ACID,原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
原子性:同批处理的sql执行结果一直,即成功或失败
一致性:
- 最终一致性:不论过程怎样,数据处理后代表的总价值与原来相 同
- 过程一致性:
持久性:事务提交前服务器宕机,数据维持处理前的状态
事务提交后服务器宕机,数据维持处理后的状态
即事物一旦提交不可逆
隔离性:事务之间的操作互不影响
事务的隔离级别
脏读:一个事务读取了另一个事务未提交的数据
不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同(不一定是错误,只是某些场合不对)
哪些场合?
虚读(幻读):指在一个事务内读取到了别的事务插入的数据,导致前后读取数据不一致
MySQL默认开启事务自动提交
set autocommit=0;/*关闭*/set autocommit=1;/*开启*/--手动执行事务set autocommit=0;--事务开启start transaction --标记一个事务开始insertupdate--提交:持久化commit--回滚:回到原来的样子rollback--事务结束set autocommit=1--了解savepoint 节点名 --设置事务的保存点rollback to savepoint 节点名 --回滚到保存点release savepoint 节点名 --撤销事务节点
索引
-
主键索引(primary key)
唯一的标识,主键不可重复
-
唯一索引(unique key)
避免重复的列出现,唯一索引可以重复,多个
-
常规索引(key/index)
默认的,index,key关键字来设置
-
全文索引(fullText)
在特定的数据库引擎下才有,MyISQM(不一定)
快速定位数据
基础语法
#索引的使用#1.在创建表的时候给字段增加索引#2.创建完毕后,增加索引#显示所有的索引信息show index from 表名;#增加索引alter table student add key name_index(name);#删除索引alter table student drop index name_index;drop index name_index on student;#增加一个全文索引(int不能添加全文索引)alter table student add fullText index name_index()name;#EXPLAIN 分析sql执行的状况EXPLAIN select * from student;
创建函数
##定义结束符,默认是分号delimiter $$#删除函数drop function if exists 函数名$$#创建函数create function mock_data()returns intbegin declare num int default 10000000; declare i int default 1000000; while i<num do insert into student (id,name,age) values (i,concat('实验',i),(i+2)); set i=i+1; end while; return i;end$$#创建函数时遇到的问题ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)#解决方式set global log_bin_trust_function_creators=TRUE;
索引原则
- 索引不是越多越好
- 不对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
- Hash:类型的索引
- Btree:InnoDB的默认数据结构
操作系统用户表
#创建用户create user 用户名 identified by 密码;#修改密码(修改当前用户密码)set passeord = password('111111');#修改密码(修改知应用户密码)set password for 用户名=('密码');#重命名 rename user 用户名 to 新用户名#用户授权GRANT ALL PRIVILEGES ON *.* TO 用户名;#*.*代表 库.表#all privileges 全部权限#查看权限show grants for 用户;show grants fror root@host #host代表地址#撤销权限revoke all privileges on*.* from 用户名;#删除用户drop user 用户名;
MySQL备份
为什么备份
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 使用命令行导出mysqldump命令行使用
#导出一张表mysqldump -h 地址 -u 用户 -p 密码 库 表 >导出的路径及文件名#导出多张表mysqldump -h 地址 -u 用户 -p 密码 库 表 表>导出的路径及文件名#导出库mysqldump -h 地址 -u 用户名 -p 密码 库>导出的路径及文件名#导入(登录,切换指定的数据库)source 文件;#导入(未登录)mysql -u 用户名 -p 密码 库<文件
规范数据库设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会很麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便系统开发
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需求处理的数据库的需求
- 概要设计:设计挂你图E-R图
设计数据库的步骤:
- 手机信息,分析需求
- 标识实体(把需求落实到每个字段)
- 标识实体之间的关系
三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 删除异常
第一范式
数据库中的每一列都是不可再分的原子数据项
第二范式
前提:满足第一范式
确保表中每一列数据都与主键相关,不能只与主键一部分相关
第三范式
前提:满足第二范式
确保表中每一列数据都和主键直接相关,不能间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业需求和目标,(成本、用户体验)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑规范性
- 故意给表增加一些冗余字段(从多表查询变为单表查询‘)
- 故意增加一些计算列;索引(从大数据量降低为小数据量的查询)
JDBC
数据库驱动
简化开发操作,提供的一个操作数据库的规范
java.sql/javax.sql
import java.sql.*;public class JDBCTest { public static void main(String[] args) { //1.添加驱动 try { Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } //2.用户信息和url String user = "root"; String password = ""; String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&&characterEncoding=utf8&&useSSL=true&&serverTimezone=GMT"; //3.连接成功,数据库对象 Connection connection = null; try { connection = DriverManager.getConnection(url,user,null); } catch (SQLException throwables) { throwables.printStackTrace(); } //4.执行SQL对象 Statement statement = null; try { statement = connection.createStatement(); } catch (SQLException throwables) { throwables.printStackTrace(); } //5.执行sql的对象去执行sql,可能存在结果,查看返回结果 String sql = "select * from a"; ResultSet result = null; try { result = statement.executeQuery(sql); } catch (SQLException throwables) { throwables.printStackTrace(); } try { while (result.next()) { System.out.println(result.getObject("id")); } } catch (SQLException throwables) { throwables.printStackTrace(); } //6.释放连接 try { result.close(); statement.close(); connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } }}//报错处理java.sql.SQLException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836) at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at JDBCTest.main(JDBCTest.java:18)Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132) at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2120) at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2143) at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1310) at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:967) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826) ... 6 moreException in thread "main" java.lang.NullPointerException at JDBCTest.main(JDBCTest.java:25)//处理//jdbc连接加上serverTimezone=GMT
资源持久化加载
资源文件:database.properties
url=地址user=用户password=密码
import java.io.*;import java.sql.*;import java.util.Properties;public class JDBCTest { public static vo main(String[] args) { //1.添加驱动(8.0.25不需要) //2.用户信息和url File file = new File("C:\\Users\\Administrator\\Desktop\\JavaSE\\基础语法\\src\\database.properties"); InputStream in = null; try { in = new FileInputStream(file); } catch (FileNotFoundException e) { e.printStackTrace(); } Properties properties = new Properties(); try { properties.load(in); } catch (IOException e) { e.printStackTrace(); } //3.连接成功,数据库对象 Connection connection = null; try { connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password")); } catch (SQLException throwables) { throwables.printStackTrace(); } //4.执行SQL对象 Statement statement = null; try { statement = connection.createStatement(); } catch (SQLException throwables) { throwables.printStackTrace(); } //5.执行sql的对象去执行sql,可能存在结果,查看返回结果 String sql = "select * from a"; ResultSet result = null; try { result = statement.executeQuery(sql); } catch (SQLException throwables) { throwables.printStackTrace(); } try { while (result.next()) { System.out.println(result.getObject("id")); } } catch (SQLException throwables) { throwables.printStackTrace(); } //6.释放连接 try { result.close(); statement.close(); connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } }}
SQL注入问题
PrepareSatement对象
可以防止SQL注入,并且效率更高
数据库连接池
数据库连接–执行完毕–释放
连接–释放十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:常用连接数
最大连接数
超出最大连接数等待
等待超时
编写连接池
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
希望在码农的山坡上越攀越高,欢迎各位大佬看官的指正及补充