mysql学习笔记

最新推荐文章于 2024-02-27 17:29:03 发布

一只勤劳的小鸡

最新推荐文章于 2024-02-27 17:29:03 发布

阅读量284

点赞数 1

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_26144863/article/details/112061451

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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!");
    }
}

1、驱动和IDEA

2、 JDBC事务

3、连接池C3P0,DBCP

一只勤劳的小鸡

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记

1、MySQL1、数据库分类MySQL和NoSQL(not onlySQL)MYSQLMySQL、Oracle表和表之间、行和列之间的关系进行存储。NOSQLRedis、MongDB非关系型，储存对象，通过对象的属性存储数据2、DBMS（DataBase manage system ）#mermaid-svg-cke4OQGvYGW8vyzW .label{font-family:'trebuchet ms', verdana, arial;font-family:var(--me
复制链接

扫一扫