1、MySQL
1、数据库分类MySQL和NoSQL(not onlySQL)
MYSQL
- MySQL、Oracle
- 表和表之间、行和列之间的关系进行存储。
NOSQL
- Redis、MongDB
- 非关系型,储存对象,通过对象的属性存储数据
2、DBMS(DataBase manage system )
应用程序通过DBMS来管理DB中的数据。
2、安装MySQL
https://dev.mysql.com/downloads/file/?id=496745
教程:
https://www.cnblogs.com/hellokuangshen/p/10242958.html
-
解压
-
解压后的包bin放入环境变量
-
包中新建配置文件my.ini**(!!!此为5版本 若是8版本不用创建)**
[mysqld] basedir=# 换成包目录 datadir=# 包目录+ \data\ port=3306 skip-grant-tables
-
管理员CMD 在bin目录下 输入
mysqld -install //安装mysql
初始化数据
mysqld --initialize-insecure --user=mysql
启动mysql
net start mysql
重置密码
mysql -u root -p
修改密码(此为5版本!!!注意语句分号结尾!!!)
updata mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
此为8版本
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
刷新权限
flush privileges
5. 修改my.ini文件删除最后一句skip-grant-tables
6. 重启正常使用
net stop mysql
net start mysql
## 1、基本操作
链接数据库
```sql
--注释
--连续两个短杆
--连接数据库
mysql -u root -p
--修改密码 version8.0+
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
-- 查看所有数据库
show databases;
-- 切换数据库
use school;
-- 查看所有表
show tables;
-- 显示表中信息
describe students;
-- 创建一个数据库
create database name;
-- 退出连接
exit;
四种操作语言
DDL 定义语言
DML 操作
DQL 查询
DCL 控制
2、导入练习数据
导入MySQL官方练习数据
由于官网下载超级慢:
https://pan.baidu.com/s/1NSgeVNyjUB6VwVbV3JDEEQ
提取码:2pt2
- 修改
employees.sql
文件,将里面的[29,38,44,107,109,111,113,115,117]
行删除或者注释,注意注释用的--
后需要空格。(大致注释内容为select语句和set语句) - 然后使用
cmd
进入该文件夹, - 输入命令
mysql -t -uroot -p <employees.sql
回车后输入密码,等待即可完成 - 数据说明:https://www.cnblogs.com/stream886/p/6254630.html
导入MySQL必知必会示例
图书官网:https://forta.com/books/0672327120/
数据: https://forta.com/wp-content/uploads/books/0672327120/mysql_scripts.zip
包含两个文件,create.sql和populate.sql
- 使用
cmd
进入该文件夹, mysql -uroot -p
进入MySQL- 创建测试用数据库
creat database test
use test
source create.sql
source populate.sql
3、操作数据库
操作数据库> 操作数据库中的表 > 操作表里的数据
3.1、 操作数据库
1、创建数据库
create database [if not exists]name;--如果不存在
2、删除数据库
drop database [if exists]name;
3、使用数据库
use `database name`;--如果名字是特殊字符(保留字)要用``引住
4、查看数据库
show databases;
3.2、列数据类型
数值
- tinyint 1字节
- smallint 2字节
- mediumint 3字节
- int 4字节
- bigint 8字节 == long
- float 4字节
- double 8字节
- decimal 高精度金融计算
字符串
-
char 0~255
-
varchar 0~65535 == String
-
tintext 微型文本 2^8-1
-
text 存文章 2 ^16-1
时间
java.util.Date
-
date YYYY-MM-DD 日期
-
time HH-mm-ss 时间
-
datetime YYYY-MM-DD HH:mm:ss最常用
-
timestamp 时间戳 从1970.1.1
-
year
null
不要用null进行运算,任何运算都=null
3.3、字段属性(attribute)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RO4WFbUR-1609489858281)(\列属性.PNG)]
unsigned:
- 无符号整数
- 不能为负数
zerofill:
- 不足的位数,用0填充,如5,填充为00005
自增:
- 在上一条记录基础上 + 1(默认)
- 通常用来设计唯一的主键~index
- 可以自定义主键的起始值和步长
非空:
- 不允许空值,否则报错
默认(default):
- 不填充,就会自动填写
每一个表,必须存在以下字段
id
‘version’
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
3.4、创建表
格式
create table [if not exists] `name`(
`name1` type [attribute][index][comment ''],
`name2` type [attribute][index][comment ''],
...
`namen` type [attribute][index][comment ''],
primary key(`name1`)
)[表类型][字符集]
实例 students表
create table if not exists `students`(
`ID` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default'匿名' comment '姓名',
`address` varchar(100) default null comment '住址',
)enging=innodb default charset=utf8
show create database school – 查看创建数据库的语句
show create table students --查看students数据表的定义语句
desc students 显示表结构
3.5、表类型(引擎)
MYISAM | INNODB | |
---|---|---|
事务支持 | N | Y |
行锁 | N | Y |
外键约束 | N | Y |
全文索引 | Y | N |
内存占用 | 小 | 大 |
- myisam 节约空间
- innodb 安全性较高 ,支持多表多用户操作
物理层面
MySQL引擎在物理文件上的区别
- InnoDB 在表文件中*.frm文件 和 上级目录的ibdata1文件
- myisam
- *.frm 表结构定义文件
- *.MYD 数据文件
- *.MYI 索引文件(index)
设置字符集编码
默认编码不支持中文
charset=utf8
3.6、修改删除表
alter
修改表名
alter table 旧表名 RENAME as 新表名;
增加字段
alter table 表名 ADD 字段 数据类型[attributions];
-
修改字段
- modify 修改约束(数据类型、属性)
alter table 表名 modify 待改字段 新属性 ;
- change 字段重命名
alter table 表名 change 旧字段名 新字段名 属性 ;
-
删除
-
删除字段
alter table 表名 drop 待删字段;
-
删除表
drop table if exists 待删表名;
-
4 、数据库(数据)管理(增、删、改)
4.1、外键
不要使用数据库层面外键,外键约束关系到应用层实现
alter table `表名`
add constraint `fk_引用字段名` foreign key('引用字段名') references `被引用的表名`(`引用字段`);
4.2、insert into (增)
insert into `表名`(字段1, 字段2, 字段3)
value (value1, value2, value2) ,(value1, value2, value3)...;
4.3、 update (改)
update `表名` set `字段名`='新值' where 修改条件p;
4.4、 delete from(删)
delete from 表名 where 约束条件;
delete不会改变表的结构(计数器不会归零,在innodb中)
innodb数据在内存中,myisam在外部储存中。
truncate
truncate `表名`
重置表,一切恢复到初始化。
5、查询数据(DQL database query language)
5.1、select基本语句、别名、去重
查询所有
select * from `表名`;
查询字段
select 字段名1,字段名2... from `表名`;
将字段名(表头换一个名字)AS
select `字段名` as `别名` from `表名`;
显示部分数据
select `字段名` as `别名` from `表名` limt 10;
字符串连接函数concat(a, b)
select concat('新字符串',`字段名`) as `别名` from `表名`;
去重,重复的数据只显示一条distinct
select distinct 字段名1,字段名2... from `表名`;
5.1、where条件子句
模糊查询
select 字段名1,字段名2... from `表名` where 表达式;
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 为null时返回真 |
is not null | a is not null | 不为null时,返回真 |
between | a between b and c | 左右包含,a在bc之间 |
in | a in (b, c,…) | a是否与括号中一模一样 |
like | a like %a_ | % 匹配任意个数任意字符,_ 匹配任意单个字符 |
- like
- a like %一个% --匹配句子中含有“一个“的字符串
- a like _一个% --匹配句子中含有“一个”,且“一个”前有1个字符,后方有任意个数字符
where可嵌套查询。
实例
select StudentName from 表名 where StudentName like 李_;
5.2、join on联表查询
-- 基本操作
select 表A.表头a, 表B.表头a from 表A
inner join 表B
on 表A.c = 表B.c -- 联结条件
where -- 附加条件
- 分析需要哪些字段
- 确认使用哪种查询
- 确认交叉点
操作 | 描述 |
---|---|
inner join | 关键字在表中存在至少一个(即两个表中都存在)匹配时返回行 |
left join | 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 |
right join | 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。 |
自联接
将一张表拆成两张相同的表,根据父子关系进行操作。
5.3 分组group by 和再筛选 having
根据一个或多个列对结果集进行分组 ,仅在按组计算聚集时使用(聚集函数在第六章)
把数据分为多个逻辑分组,对逻辑分组进行聚集运算
SELECT `vend_id` ,COUNT(*) FROM products
GROUP BY `vend_id`
HAVING COUNT(*)>3
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-raWU0erd-1609489858283)(\5.3.PNG)]
结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ah2NFxm7-1609489858284)(\5.3.1.PNG)]
5.4 排序order by和分页limit
-
排序: 升序 ASC 降序 DESC
order by 字段 [ASC,DSC]
注意:排序必须是在join on,where这些查询出结果之后。
-
分页
limit 起始值(0开始), 步长。
用n代表当前页。有
limit (n-1)*步长,步长
5.5 select汇总
select [all | distinct]
{
* | table.* | [table.field[as alias1] [,table.field[as alias2] ...]]
}
from table_name [as table_alias]
[left | right | inner | join table_name2] -- 联表查询
[where ...] -- 满足条件,行级过滤
[group by...] -- 结果按哪几个字段分组,仅在按组计算聚集时使用
[having ] -- 分组后次要筛选
[order by [asc/ dsc]] -- 升序降序排序
[limit {[start,]row_count}]
6、字符串、时间、聚合、MD5加密函数
文本函数
函数 | 描述 |
---|---|
concat(a,b) | 连接a,b |
Length() | 返回长度 |
Ltrim(),Rtrim() | 去除左右空格 |
日期函数
函数 | 描述 |
---|---|
Now() | 当前日期和时间 |
Year(Now()) | 年 |
Month(Now()) | 月 |
Day(Now()) | 日 |
Time(Now()) | 时间 |
聚合函数
MAX() | 最大 |
---|---|
MIN() | 最小 |
AVG() | 平均 |
COUNT() | 特定列计数 |
COUNT(*) | 对表中行数目进行计数 |
SUM() | 求和 |
MD5加密
MD5(待加密信息)
MD5是不可逆加密
具体:https://baike.baidu.com/item/MD5/212708?fr=aladdin
7、事务ACID
csdn:https://blog.csdn.net/dengjili/article/details/82468576
7.1 什么是事务
== 同时成功和失败的一组sql操作 ==
A给B 转账200 A 1000-200 —>800
B收到A的转账 A 800 + 200 —> 1000
7.2 原子性,一致性,隔离性,持久性
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
7.3 违反原则
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
页面统计查询值
点击生成报表的时候,B有人转账进来300(事务已经提交)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
7.4数据库层事务操作
set autocommit = 0 -- 关闭自动提交
start transaction ;
-----------------
sql修改语句
-----------------
commit ;--提交
rollback; -- 回滚
set autocommit = 1;
8、索引
实现原理:https://www.cnblogs.com/tgycoder/p/5410057.html
索引(Index)是帮助MySQL高效获取数据的数据结构。索引是一种数据结构(树)。
作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
种类
-
主键索引 (Primary Key)
- 某一个属性组能唯一标识一条(行)记录
-
唯一索引 (Unique)
- 避免同一个表中某数据列中的值重复 。与主键索引的区别:主键索引只能有一个,唯一索引可能有多个。
-
常规索引 (Index/key)
快速定位特定数据
-- 创建表时添加 CREATE TABLE `result`( -- 省略一些代码 INDEX/KEY `ind` (`studentNo`,`subjectNo`) ) -- 创建后添加 ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`); -- 范式 -- 1 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; -- 2 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; 删除索引:DROP INDEX 索引名 ON 表名字; 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY; 显示索引信息: SHOW INDEX FROM student;
-
全文索引 (FullText)
https://blog.csdn.net/mrzhouxiaofei/article/details/79940958
使用全文索引
和常用的模糊匹配使用 like + % 不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如
select * from fulltext_test where match(content,tag) against('xxx xxx');
9、JDBC
范式
package com.runoob.test;
import java.sql.*;
public class MySQLDemo {
// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB";
// MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
//static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
//static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码,需要根据自己的设置
static final String USER = "root";
static final String PASS = "123456";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// 执行查询
System.out.println(" 实例化Statement对象...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, name, url FROM websites";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while(rs.next()){
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
String url = rs.getString("url");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 站点名称: " + name);
System.out.print(", 站点 URL: " + url);
System.out.print("\n");
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
// 处理 JDBC 错误
se.printStackTrace();
}catch(Exception e){
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 关闭资源
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}// 什么都不做
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}