WHCSRL 技术网

MySQL笔记

一、初识MySQL

1.1、数据库分类

关系型数据库:(SQL)

  • MySQL,Oracle,DB2…
  • 通过表和表之间,行和列之间的关系进行数据的存储

非关系型数据库:(NoSQL) Not Only

  • Redis,MongDB
  • 非关系型数据库,对象存储,通过自身的属性来决定。

DBMS(数据库管理系统)

  • 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;

1.2、MySQL简介

MySQL是一个关系型数据库管理系统

之前是属于瑞典MySQL AB公司,现在属于Oracle旗下产品

MySQL是最好的RDBMS(关系数据库管理系统)应用软件之一。

是开源的数据库软件~体积小、速度快、招人成本低、所有人必须会,适用于中小型网站、或者大型网站,集群。

官网:http://ww.mysql.com

1.3、链接数据库

mysql -uroot -p123456   --连接数据库

updata mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
--修改用户密码

flush privileges; --刷新权限

-------------------------------------------------
show databases; --查看所有的数据库
use school  --切换数据库  use  数据库名
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息

create database 表名  --创建一个数据库
exit;   --退出链接
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

二、操作数据库

  • 操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
  • 操作数据库

1.创建数据库

create database [if not extsts] teacher --创建数据库
  • 1

2.删除数据库

drop database [if exists] teacher  --删除数据库
  • 1

3.使用数据库

use 'school'   --使用数据库  如果表名或者字段是一个特殊字符,就需要带``
  • 1

4.查看数据库

show databases  --查看所有的数据库
  • 1

2.1、数据库的列类型

数值:

tinyint十分小的数据1个字节
smallint较小的数据2个字节
mediumint中等大小的数据3个字节
int标准的整数4个字节 常用的 int
bigint较大的数据8个字节
float浮点数4个字节
double浮点数8个字节
decimal字符串形式的浮点数金融计算的时候一般使用decimal

字符串:

char字符串固定大小0~255
varchar可变字符串0~65535 常用的变量 对应Java中的String
tinytext微型文本2^8-1
text文本串2^16-1 保存大文本

时间日期:

dateYYYY-MM-DD日期格式
timeHH:MM:SS时间格式
datetimeYYYY-MM-DD HH:mm:ss最常用的时间格式
timestamp时间戳1970.1.1到现在的毫秒数!也较为常用
year表示年份

null:

  • 没有值,未知
  • ==注意,不要使用null进行运算, 如果使用结果为null

2.2、数据库的字段属性(重点)

Unsigned:

  • 无符号的整数
  • 声明了该列不能声明为负数

zerofill :

  • 0填充的
  • 不足的位数,使用0来填充,

自增:

  • 通常理解为自增,自动在上一条记录的基础上 + 1(默认)
  • 通常用来设计唯一的主键~ index,必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

非空 NULL not null :

  • 假设设置为 not null ,如果不给它赋值,就会报错!
  • NULL,如果不填写值,默认值就是null~

拓展:

每一个表,都必须存在以下5个字段,未来做项目时用的,表示一个记录存在的意义!
id  主键
`version`    乐观锁
is_delete    伪删除
gmt_create   创建时间
gmt_updata   修改时间
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

2.3、创建数据库表

create table if nof extsts `student`(
	`id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd ` varchar(20) not null default '123456' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(50) default null comment '邮箱',
    primary key(`id`)--设置id为主索引
)engine=innodb default charset=utf8
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
格式:
CREATE TABLE [IN NOT EXTSTS]`表名`(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
...
'字段名' 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
show create database school  --查看创建数据库的语句
show create table student --查看student数据表的定义语句
desc student  --显示表的结构
  • 1
  • 2
  • 3

2.4、数据表的类型

--关于数据库的引擎
/*
innodb   数据库默认使用的
myisam   早些年使用的
*/
  • 1
  • 2
  • 3
  • 4
  • 5

2.5、修改表

--修改表名 alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1

--增加表的字段 alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11)

--修改表的字段 (重命名,修改约束!)
--alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(11)  --修改约束

--alter table 表名 change 旧名字  新名字  列属性[]
alter table teacher1 change age age1 int(1)  --字段重命名

--删除表的字段 alter table 表名 drop 字段名
alter table teacher1 drop age1

--删除表(如果表存在再删除)
drop table if exists teacher1

--所有的创建和删除尽量加上判断,以免报错
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

注意点:

  • ``字段名,使用这个包裹!
  • 注释 – 或者 /**/
  • sql大小写不敏感,建议写小写 因为大写有些都不认识
  • 所有符号全部用英文

三、数据管理

3.1、外键

--创建表成功后添加外键约束 
alter table `student` add constraint `FK_qradeid` foreing key(`gradeid`) references `grabe`(`gradeid`);
  • 1
  • 2

3.2 DML语言(重要,最好全部背下来)

--插入语句(添加)
--insert into 表名([字段1,字段2,字段3,...]) values('值1'),('值2'),('值3')...)
insert into `student`(`name`,`pwd`,`sex`) valuse (`张三`,`aaaaa`,``),(`李四`,`520131`,``)

  • 1
  • 2
  • 3
  • 4
--修改表类容   加了条件
update `student` set name ='小红',eml = 137089080@qq.com where id = 1; 

--不指定条件的情况下,会改动所有表
update `student` set name = `祖国我爱你`;

语法:
--update 表名 set 字段属性 = [修改的值],字段属性 = [修改的值] where(条件) 字段属性 = 值;  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • where 通过条件定位数据

条件操作符:

操作符含义范围结果
=等于1=1true
<>或!=不等于2<>1true
>
<
>=
<=
between…add…再某个范围内[5,10]
add我和你 &&
or我或你 ||
--删除
语法:delete from 表名 where 条件
--删除表
delete from `student`		

--删除表类容
delete from `student` where id = 1;

--清空表里面所有类容
truncate `student`
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

3.3、DQL查询数据(*最重要)

  • DQL:(Data Query Langage:数据查询语言)
  • 所有的查询操作都用它 Select(选择)
  • 简单的查询,复杂的查询它都能做
  • 是数据库最核心的语言
--查询全部的学生 select 字段 from 表
select * from student

--查询指定字段
select `studentNo`,`studentName` from student

--可以起别名,给结果起一个名字 用 AS  就可以给字段起别名,也可以给表起别名
select `studentNo` AS 学号,`studentName` AS 学生姓名 from student AS 学生表

--函数 concat(a,b)字符串的形式拼接
select concat('姓名',studentName) AS 新名字 from student
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
--去重 distinct
--作用,去除select查询出来的结果中重复的数据,重复的数据只显示一条
select * from result  --查询全部的考试成绩
select `StudentNo` from result  --查询有哪些同学参加了考试
select distinct `StudentNo` from result  --发现重复数据,使用distinct去重
  • 1
  • 2
  • 3
  • 4
  • 5
=============== 条件查询where ============================
--查询考试成绩再95-100分之间
select studentNo,`StudentResult` from result where StudentResult>=95 and StudentResult<=100

--模糊查询(区间)
select studentNo,`StudentResult` from result where StudentResult between 95 and 100

--除了1000号学生之外的同学成绩
select student, `StudentResult` from result where studentNo!=1000;

--!=  not
select studentNo,`StudentResult` from result where not studentNo = 1000;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 模糊查询
运算符语法描述
is nulla is null如果操作符为null,结果为真
is not nulla is not null如果操作符不为null,结果为真
betweena between b and c若a在b和c之间,结果为真
likea like bSQL匹配,如果a匹配b,则结果为真
ina in(a1,a2,a3…)假设a在a1,或者a2…其中的某一个值中,结果为真
====================  模糊查询  ======================
--查询表中姓刘的同学  使用like结合通配符 %%(代表0到任意个字符),_(一个字符)
select `StudentNo`,`StudentName` from `student` where StudentName like '刘%%'
select `StudentNo`,`StudentName` from `student` where StudentName like '刘_'
--查询名字中带有嘉字的同学
select `StudentNo`,`StudentName` from `student` where StudentName like '%%嘉%%'

--使用in查询在某个地方的学生
select `StudentNo`,`StudentName` from `student` where `Address` in('安徽','河南洛阳');

--查询地址为空的学生null
select `StudentNo`,`StudentName` from `student` where `Address`='' or Address is null

--查询有出生日期的同学  出生日期不为空
select `StudentNo`,`StudentNane` from `student` where BornData is not null
--查询没有出生日期的同学  出生日期为空
select `StudentNo`,`StudentName` from `student` shere BornData is null
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

3.4 、联表查询

=============== 联表查询 ========================
==查询参加了考试的同学(学号,姓名,科目编号,分数)
select * from student  ==查询学生表
select * from result   ==查询成绩表

==思路:1.分析查询的字段来自那些表,(链接查询) 2.确定使用那种查询 3.确定交叉点(这两个表中那个数据是相同的)
==判断条件:学生表中的student = 成绩表 studentNo
--使用inner join查询
select s.studentNo,studentName,SubjetNo,StudentResult
from student AS s
inner join result AS r
on s.studentNo = r.studentNo

--使用right join查询
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
right join result r
on s.studentNo = r.studentNo

--使用left join查询 查询两个表中缺考的同学
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
left join result r
on s.studentNo = r.result
where StudentResult is null
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
操作描述
inner join如果两个表中至少有一个匹配,就会返回执行
left join会从左表中返回所有的值,即使右表中没有匹配
right join会从右表中返回所有的值,即使左表中没有匹配

3.5、分页和排序

===================分页 limit  和排序  order by
--排序: 升序:asc  降序:desc
order by Student desc  --降序
order by Student asc   --升序

limit 0,5 --0代表起始值,5代表几条数据
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
--查询 JAVA第一学年  课程成绩排名前十的学生,并且分数要大于80分的学生信息(学号,姓名,课程名称,分数)
select s.`StudentNo`,`StudentName`,`SubjecName`,`StudentResult`
from `student` as s
inner join `result` as r
on s.`StudentNo` = r.`StudentNo`
inner join `subject` as sub
on sub.`SubjectNo` = r.`SubjectNo`
where SubjectName = 'Java第一学年' and StudentResult>=80
order by StudentResult desc
limit 0,10
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

3.6、子查询

--查询数据库结构-1 的所有考试结果(学号、科目编号、成绩),降序排列
--方式一:使用连接查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` as r
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
where SubjectName = '数据库结构-1'
order by StudentResult desc

--方式二:使用子查询(由里及外判断)
select `StudentNo`,`SubjectNo`,`StudentResult`
from `result`
where SubjectNo = (
	select SubjectNo from `subject`
    where SubjectName = '数据结构-1'
)
order by StudentResult desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

3.7、MySQL函数学习

常用函数含义
select abs(-8)绝对值
select ceiling(9.4)向上取整
select floor(9.4)向下取整
select rand返回一个0~1之间的随机数
select sign判断一个数的符号 0-0 负数返回-1,正数返回1
select char_length(‘即使再小的帆也能远航’)返回字符串的长度
select concat(‘我’,‘爱’,‘你’,‘们’)拼接字符串
select insert(‘我爱编程hellworld’,1,2,‘超级热爱’)从某个位置开始替换某个长度
select lower(‘KuangShen’)小写字母
select upper(‘KuangShen’)大写字母
select instr(‘kuangshen’,‘h’)返回第一次出现的子串的索引
select current_date()获取当前日期
select curdate获取当前的日期
select now获取当前的时间
select localtime()获取本地时间
select sysdate获取系统时间
  • 聚合函数(常用)
函数名称描述
count()计数
sum()求和
avg平均值
max()最大值
min()最小值

四、事务

要么都成功,要么都失败

将一组SQL放在一个批次中去执行~

  • ACID :原子性 一致性 隔离性 持久性 (幻读,脏读)
  • 原子性:

​ 要么都成功,要么都失败。

  • 一致性:

    ​ 事务前后完整性要保证一致

  • 隔离性:

​ 两个用户同时操作时,数据库为每个用户开启一个单独的事务,不会被其他事务的操作数据所干扰。

  • 持久性:

​ 事务一旦提交则不可逆,被持久化到数据库中

--转账 创建一个商店
create database shop character set utf8 collate utf8_general_ci use shop

create table `account`(
   `id` int(3) not null auto_increment,
   `name` varchar(30) not null,
   `money` decimal(9,2) not null,
   primary key (`id`)
)engine = innodb default charset=utf8

--设置钱
insert into account(`name`,`money`)
values ('A',2000.00),('B',10000.00)

--模拟转账:事务
set autocommit = 0;  --关闭自动提交
start transaction  --开启一个事务
update account set money=money-500 where `name` = 'A' --A减500
update account set money=money+500 where `name` = 'B' --B加500

commit;  --提交事务
rollback; --回滚 从新执行

set autocommit = 1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

五、索引

  • MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。
  • 提取句子主干,就可以得到索引的本质:索引是数据结构。
  • 索引的分类:
  1. 主键索引:primary key
  • 唯一的标识,主键不可重复
  1. 唯一索引:unique key
  • 避免重复的列出现,它可以重复
  1. 常规索引:key 或者 index
  • 默认的,index key关键字来设置
  1. 全文索引:fullText
  • 在特定的数据库引擎下才有
--显示所有的索引信息
show index from student

--增加一个全文索引
alter table school.student add fulltext index studentName(`studentName`);


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

用户管理

--创建用户 create user 用户名 identified by '密码'
create user huawang identified by '123456'

--修改密码(修改当前用户密码)
set password = password('123456')

--修改密码(修改指定用户密码)
set password for huawang = password('123456')

--重命名,rename user 原来的名字 to 新的名字
rename user huawang to huawang2

--用户授权 
crant all privileges 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

MySQL备份

为什么要备份:

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件
  • 在Sqlyog这种可视化工具中手动导出
  • 使用命令行导出 mysqldump 命令行使用
mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 student >D:/a.sql
  • 1
  • 2

规范数据库设计

当数据库比较复杂的时候,我们就需要设计了=======

  • 糟糕的数据库设计:会造成数据沉于,浪费空间,数据库插入删除都会麻烦、会造成程序的性能差
  • 良好的数据库设计:可以节省内存空间,保证数据库的完整性,方便我们开发系统

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理数据库的需求
  • 概要设计:设计关系图E-R图

数据库三大范式

  • 第一范式

    • 原子性:保证每一列不可再分
  • 第二范式

    • 前提:满足第一范式
    • 每张表只描述一件事情
  • 第三范式

    • 前提:满足第一范式和第二范式
    • 第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
  • 规范性 和 性能的问题

    • 关联查询的表不得超过三张表
    • 考虑商业化的需求和目标,(成本以及用户体验!)数据库的性能更加重要
    • 在数据库性能问题的时候,需适当牺牲下规范性,故意增加一些数据沉余。

JDBC(重要)

  • sun公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,俗称 JDBC.

  • 这些规范的实现由具体的厂商去做~

  • 对于开发人员来说,我们只需要掌握 JDBC接口的操作即可

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5vniDqUg-1635737901524)(C:Users86150AppDataRoamingTypora ypora-user-imagesimage-20211031201700629.png)]

//第一个JDBC程序
public class jdbcFirstDemo{
    public static void main(String args) throws ClassNotFoundException, SQLException{
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
        
        //2.用户信息和url
        //固定的useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功,数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);
        
        //4.执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();
        
        //5.执行SQL的对象 去 执行SQL,可能存在结构,查看返回结构
        String sq1 = "SELECT * FROM users";
        
        //返回的结果集,结果集中封装了我们全部的查询出来的结果
        ResultSet resultSet = statement.executeQuery(sq1);
        
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject(columnLable:"id"));
            System.out.println("name="+resultSet.getObject(columnLable:"name"));
            System.out.println("pwd="+resultSet.getObject(columnLable:"pwd"));
            System.out.println("email="+resultSet.getObject(columnLable:"email"));
            System.out.println("birth="+resultSet.getObject(columnLable:"birth"));
            System.out.println("================================="));
            
        }
        
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
推荐阅读