mysql笔记
从今天起开始mysql的学习了
创建一个数据库的链接
字符集：utf8 – UTF-8 Unicode
校对：utf8_general_ci
每一个可视化窗口执行操作的本质都是执行sql语句
1、初识mysql
select version(); --查看mysql的版本
mysql -uroot -p1234 --连接数据库
flush pricileges; --刷新权限
select 110*3-10 as 计算结果; --select可计算结果
--所有的命令以 ；结尾
--ctrl+c可强行停止
exit --退出
--单行注释
/*
sql的多行注释
*/
show databases； --查看数据库
----------------------------------
use school; --切换数据库
----------------------------------
show tables； --查看数据库中所有的表
----------------------------------
describe student; --显示数据库中所有的表的的信息
----------------------------------
create database westos; --创建一个数据库
数据库XXX语言CRUD 增删改查！
- DML 定义
- DDL 操作
- DQL 查询
- DCL 控制
2、操作数据库
过程：操作数据库 >> 操作数据库中的表 >> 操作数据库中的表的数据
mysql语句不区分大小写
2.1、操作数据库
-
创建数据库
create database [if not exists] `westos`; --创建一个数据库，如果它不存在，[]表示可选 -
删除数据库
drop database [if exists] `westos` --删除一个数据库 -
使用数据库
use `school` --使用数据库，如果表名或者字段名是一个关键字，就使用` ` -
查看数据库
show databases; --查看数据库
2.2、数据库的列类型
数值
- tinyint 十分小的数据 一个字节
- smalint 较小的数据 两个字节
- mediumint 中等大小的数据 三个字节
- int 标准的整数 四个字节
- bigint 较大的数据 八个字节
- float 浮点数 四个字节
- double 浮点数 八个字节
- decimal 字符串形式的浮点数 金融计算的时候常用
字符串
- char 字符串固定大小 0-255
- varchar 可变字符串 0~65565 常用的变量 String
- tinytest 微型文本 2^8-1
- text 文本串 2^16-1
时间日期
java.util.Data
- data YYYY-MM-DD，日期
- time HH:mm:ss 时间格式
- datatime YYYY-MM-DD HH:mm:ss 最常用的
- timestamp 时间戳，1970.1.1到现在的毫秒数
- year 年份表示
null
- 没有值，代表未知
- 不要使用NULL进行计算，结果为null
2.3、数据库的字段属性（重点）
Unsigned ：
- 无符号的整数
- 声明了概率不能为负数
serofill：
- 0填充的（没什么用）
- 不足的位数，使用0来填充 int(3) 5-----005
自增：
- 通常理解为自动，自动在上一条记录的基础上加1（默认）
- 通常用来设置唯一的逐渐~index，必须是整形类型
- 可以自定义设计主键的起始值和步长
非空： NULL not null
- 假设设置为not null，如果不给他赋值，就会报错
- NULL，如果不填值，就为null
默认：
- 可以设置一个默认值
附：
id主键version乐观锁is_delete伪删除gmt_create创建时间gmt_updata修改时间
注：每一个表都必须存在以上字段，表示一个记录存在的意义
2.4、创建数据库表
--创建一个表
/*格式一:自己总结
create table `表名`(
`字段名一` 类型（长度） 是否为空（not null） default '默认值' comment '字段的注释',
`字段名二` 类型（长度） default '默认值' comment '注释',
primary key(`主键的字段名`)
)[表类型][字符集设置][注释]
*/
/*格式二：较为官方
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
*/
--注意:使用英文的()，表的名字和字段尽量使用``括起来
--AUTO_INCREMENT 自增
--可以不设置是否为空（not null），默认不为空,也可以不设置默认值（default '123456'）
--所有的语句后面加一个,最后一个不用加
--primary key(`id`) 设置一个唯一的主键
create table if not exists `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 '性别',
`brithday` datetime default null comment '出生日期',
`address` varchar(50) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8
常用命令：
show create database school; --查看创建数据库的语句
--结果：
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
show create table student; --同理，查看定义表的语句
desc student; --显示表的结构
2.5、数据表的类型
--关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
常规使用操作：
MYISAM节约空间，速度较快INNODB安全性高，事物的处理，多表多用户操作
区别：在物理空间的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储！！！
MySQL引擎在物理文件上的区别
- INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
- MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
charset=utf8
不设置的话，会是 mysql 默认的字符集编码
mysql 默认的编码是Layin1 ，不支持中文。
在my.ini 中设置默认的编码
character-set-server=utf8
2.6、修改和删除表
注：所有的删除和创建操作尽量加上判断，以免报错！！！
修改
--修改表名 alter table `旧表名` rename as `新表名`;
alter table `student` rename as `stu`;
---------------------------------------------------------
--给表增加一个字段 alter table `表名` add 字段名 类型(长度);
alter table `stu` add age int(11);
---------------------------------------------------------
--修改表的约束 alter table `表名` modify 字段名 要修改的约束;
alter table `stu` modify age int(11);
---------------------------------------------------------
--字段重命名 alter table 表名 change 旧字段名 新字段名 约束;
alter table stu change age age1 int(3);
---------------------------------------------------------
--删除字段 alter 表名 stu drop 字段名;
alter table stu drop age1;
删除
--删除表
drop table [if exists] teacher;
注意：
- ``字段名，使用这个括起来
- 注释 – /**/
- sql 关键字大小写不敏感，建议用小写
- 所有的符号全部用英文
3、MySQL数据管理
3.1、外键
注：删除有外键约束的表，要先删除引用这个表的表
--创建年级表
create table if not exists grade(
`gradeid` int(10) not null auto_increment comment '年级编号',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
)engine=innodb default charset=utf8
方式一，在创建表的时候，增加外键约束(比较麻烦)
/*创建学生表
学生表的 gradeid 字段要去引用年级表的 gradeid
定义外键key
给这个外键添加约束（执行引用）
*/
create table if not exists `student`(
`id` int(4) not null AUTO_INCREMENT comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`gradeid` int(10) not null comment '学生的年级',
primary key(`id`),
key `FK_gradeid` (`gradeid`),
constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`)
)engine=innodb default charset=utf8
方式二 创建完表之后添加外键约束
要添加约束的字段本来就已经存在了
--创建表
create table if not exists `student`(
`id` int(4) not null AUTO_INCREMENT comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`gradeid` int(10) not null comment '学生的年级',
primary key(`id`)
)engine=innodb default charset=utf8
/*添加约束
alter table `表名` add constraint `约束名` foreign key(作为外键的列) references 哪个表（哪个字段）
*/
alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
以上操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
建议：
- 数据库就是单纯的表，只用来存字段，只有行(数据)和列(字段)
- 我们想使用多张表的数据，想使用外键（程序去实现）
3.2、 DML语言
数据库意义： 数据存储、数据管理
DML语言：数据操作语言
- Insert
- updata
- delete
3.3、添加
insert
--插入语句，一般我们要数据和字段一一对应,如:
insert into 表名([字段一],[字段二],[字段三]) values('值一'),('值二'),('值三')....
---------------------------------------------------------
--在grade表中插入一组数据，由于主键自增，则可以省略主键（如果不写表的字段就会一一匹配）
insert into `grade`(`gradename`) values('大四');
--或
insert into `grade`(`gradeid`,`gradename`) values('3','大三');
---------------------------------------------------------
--插入多个值，id会自增
insert into `grade` (`gradename`) values('大二'),('大一');
---------------------------------------------------------
--插入一组数据
insert into `student`(`id`,`name`,`pwd`,`sex`,`address`,`email`)
values('2','大傻子','666','女','大街','1234');
---------------------------------------------------------
--也可以不写字段，但是每一个数据都要写且一一对应
insert into `student`
values(3,'二傻子','123','男','1999-03-21','天桥下','2432543');
注意事项：
- 字段和字段之间使用 英文逗号 隔开
- 字段是可以省略的，但是后面的值必须要一一对应
- 可以同时插入多条数据
3.4、修改
update 修改谁（条件） set 原来的值=新值
--修改id为2的name为 小傻逼，只修改一个值
update `student` set `name` = '小傻逼' where id = 2;
------------------------------------------------------------------
--如果没有条件，则默认修改全部
update `student` set `name` = '迪迦';
------------------------------------------------------------------
--修改多个属性，用逗号隔开
update `student` set `name`='盖亚',`email` = '324@dfdsf' where id = 3;
------------------------------------------------------------------
--多个条件
update `student` set `name`='泰罗',`sex`='男' where `name`='迪迦' and id=2;
条件：where子句 运算符 id 等于某个值，大于某个值，在某个区间内修改
操作符会返回一个Boolean值
运算符：
- between…and… 范围
- and 和
- or 或
- <> 不等于
注意：
- 条件 ，筛选的条件，如果没有指定则会修改所有的列
- value，是一个具体的值，也可以是一个变量
3.5、删除
delete 命令
语法：delete from 表名 [where 条件]
--会删除所有的数据（避免这样写，会被开除）
delete from `student`;
--删除指定数据
delete from `student` where id=1;
delete删除后，重启数据库
InnoDB自增列会从1开始（存在内存中）- MyISAM 继续从上一个自增量开始（存在文件中）
TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
--清空表
truncate `student`;
delete 和 truncate
- 相同点 ： 都能删除数据，都不会删除表结构
- 不同：
- truncate 重新设置自增列 计数器会归零
- truncate 不会影响事务
测试不同点：
create table `test`( --创建一个表
`id` int(4) not null auto_increment,
`coll` varchar(20) not null,
primary key(`id`)
)engine=innodb default charset=utf8
insert into `test`(`coll`) values('1'),('2'),('3') --插入三个值，id默认从1自增
- 使用 delete 删除
delete from `test` --删除所有元素
查看索引 
插入三个值后，删除全部信息，索引还是在4的位置
- 使用 truncate 删除
truncate table `student`; --标注删除表
查看索引：
索引回到1，即重置了索引。
4、DQL
4.1、DQL
（Data Query language ： 数据查询语言）
- 所有的查询操作都用它 select
- 数据库中最核心的，最重要的语句
- 使用频率最高的语句
4.2、指定查询字段
首先随便创建几个表（手疼）：
--创建一个年级表
create table if not exists `grade`(
`GradeID` int(11) not null auto_increment comment '年级编号',
`GradeName` varchar(50) not null comment '年级名称',
primary key (`GradeID`)
)engine=innodb default charset=utf8;
-------------------------------------------------------------------------
--插入数据
insert into `grade`(`GradeID`,`GradeName`) values(1,'大一'),
(2,'大二'),(3,'大三'),(4,'大四'),(5,'高中')
-------------------------------------------------------------------------
--创建一个考试成绩表
create table `result`(
`StudentNo` int(4) not null comment '学号',
`SubjectNo` int(4) not null comment '课程编号',
`ExamDate` Datetime not null comment '考试日期',
`StudentResult` int(4) not null comment '考试成绩',
key `SubjectNo` (`SubjectNo`)
)engine=innodb default charset=utf8;
--插入数据
insert into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
values(1,10,'1999-03-21',60),(2,20,'2000-03-21',70),
(3,30,'2001-03-21',80),(4,40,'2002-03-21',90);
-------------------------------------------------------------------------
--学生表
create table `student`(
`StudentNo` int(4) not null comment'学号',
`LoginPwd` varchar(20) default null,
`StudentName` varchar(20) default null comment '学生姓名',
`Sex` tinyint(1) default null comment '性别，取0或1',
`GradeId` int(11) default null comment '年级编号',
`Phone` varchar(50) not null comment '联系电话，允许为空,即可选输入',
`Address` varchar(255) not null comment '地址，允许为空',
`BornDate` datetime default null comment '出生时间',
`Email` varchar(50) not null comment '邮箱账号，允许为空，即可选输入',
`IdentityCard` varchar(18) default null comment '身份证号码',
primary key (`StudentNo`),
unique key `IdentityCard`(`IdentityCard`),
key `Email` (`Email`)
)engine=innodb default charset=utf8
--插入数据
insert into `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-------------------------------------------------------------------------
--科目表
create table `subject`(
`subjectno`int(11) not null auto_increment comment '课程编号',
`subjectname` varchar(50) default null comment '课程名称',
`classhour` int(4) default null comment '学时',
`gradeid` int(4) default null comment '年级编号',
primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;
--插入数据
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),(2,'高等数学-2',110,2),(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),(5,'C语言-1',110,1),(6,'C语言-2',110,2),
(7,'C语言-3',100,3),(8,'C语言-4',130,4),(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),(15,'数据库结构-3',100,3),(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量
开始使用查询操作：
语法： select 字段… from 表
--查询所有学生的信息
select * from student;
--查询指定字段
select `StudentNo`,`StudentName` from `student`;
有的时候，列的名字不是那么的简单，可以起别名
--别名：给结果起一个名字 as; 可以给字段起别名，也可以给表起别名
select `StudentNo` as 学号,`StudentName` as 学生姓名 from `student`;
--函数，拼接字符串 concat(a,b)
select concat('姓名:',StudentName) as 新名字 from `student`;
去重 distinct
作用：取出select查询出来的结果中重复的数据
select * from result --查询全部结果
select `StudentNo` from result --查询全部的学号，有重复
select distinct `StudentNo` from result --查询全部的学号，去除重复
select @@auto_increment_increment --查询自增数
--结果加1
select `StudentNo` , `StudentResult`+1 as '分数' from result
4.3、where条件子句
作用：检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个都为真，结果为真 |
| or || | a or b a||b | 逻辑或，其中一个为真，结果为真 |
| not ！ | not a !a | 逻辑非 |
--查询全部信息
select * from result
--查询分数在[70,90]之间的信息
select `StudentNo` ,`StudentResult` from result
where StudentResult>=70 and StudentResult<=90
--或,模糊查询
select `studentNo`, `StudentResult` from result
where studentResult between 70 and 90
--查询分数不是70的学生
select StudentNo ,studentResult from result
where not studentResult=70
--或
select StudentNo ,studentResult from result
where studentResult!=70
模糊查询 : 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null，结果为真 |
| is not null | a is not null | 如果操作符不为null，结果为真 |
| between | a between b and c | 若a在b和c之间，则结果为真 |
| like | a like b | 如果a能够匹配到b，则为真 |
| in | a in (a1,a2,a3) | 假设a在a1，或a2或a3中，则结果为真 |
--查询所有同学
select * from student
------------------- like ---------------------
--查询所有姓张的同学
select `StudentNo`,`StudentName` from student
where StudentName like '张%'
--查询所有姓张且名字是一个字的同学
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 ----------------------
--查询学号是1000或1001的学生
select `StudentNo`,`StudentName` from student
where StudentNo in (1000,1001)
--查询住址在 北京朝阳或上海的学生
select `StudentNo`,`StudentName` from student
where `address` in ('北京朝阳','上海')
--------------如果查不到则显示空
------------ null not null ----------------
--查询性别不为空的同学
select `StudentNo`,`StudentName` from student
where Sex is not null
4.4、连表查询
join 对比
多表查询思路：
-
分析需求。分析查询的字段来自哪些表（连表查询）
-
确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件：学生表的 s.StudentNo = 成绩表的 r.StudentNo
等值查询
--查询四条信息，从student表中，关联result表，交叉信息是StudentNo，where语句是判断条件
select s.StudentNo,`StudentName`,`SubjectNo`,`StudentResult`
from `student` as s inner join `result` as r
where s.StudentNo=r.StudentNo
join on 连接查询
--别名as可以省略，where可以用on代替
select s.StudentNo,`StudentName`,`SubjectNo`,`StudentResult`
from student s right join result r
on s.StudentNo=r.StudentNo
--right查询到右表中的所有信息，即便没有和左表匹配，所以可能会有空值
注：两种查询结果一样
select s.StudentNo,`StudentName`,`SubjectNo`,`StudentResult`
from student s right join result r
on s.StudentNo=r.StudentNo
where s.StudentNo is null --查询空值
练习：
--三表查询
select s.StudentNo,`StudentName`,`SubjectName`,`StudentResult`
from student s left join `result` r
on s.StudentNo = r.StudentNo
left join `subject` sub
on sub.subjectNo = r.subjectNo
-- 查询学院所属的年级（学号，学号的姓名，年级名称）
select `StudentNo`,`StudentName`,`gradeName` from
student s left join grade g
on s.GradeId =g.GradeID
-- 查询科目对应的年级 （科目名称、年级名称）
select `subjectname`,`GradeName` from
`subject` s left join `grade` g
on s.gradeid = g.GradeID
-- 查询参加了数据库-1 考试的同学信息：学号、学生姓名、科目名、分数
select s.`StudentNo`,s.`StudentName`,`subjectname`,`StudentResult`
from `student` s left join `subject` sub
on sub.subjectname='数据库结构-1'
inner join `result` r
on s.StudentNo =r.StudentNo
自连接
首先创建几个表
create table `category`(
`categoryid` int(10) unsigned not null auto_increment comment '主题id',
`pid` int(10) not null comment '父id',
`categoryName` varchar(50) not null comment '主题名字',
primary key (`categoryid`)
)engine=innodb default charset=utf8
insert into `category`(`categoryid`,`pid`,`categoryname`)
values('2', '1', '信息技术'),('3', '1', '软件开发'),('5', '1', '美术设计'),
('4', '3', '数据库'),('8', '2', '办公信息'),('6', '3', 'web开发'),
('7', '5', 'ps技术')
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可！！
以上关系；
--查询条件：一个表分为两个表，子类的父id等于父类的id
select a.`categoryName` as '父栏目' ,b.`categoryName` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid`=b.`pid`
4.5、排序和分页
排序：升序 asc 降序 desc
--查询所有信息，并且根据categoryid进行降序排列
select * from `category`
order by `categoryid` desc
分页
-- 分页，每页只显示5条数据
-- limit 起始值(从0开始)，每页显示个数
select * from `category`
order by `categoryid` desc
limit 0,5 -第一页，5组数据
-- 第n页 limit (n-1)*size size
练习：
-- 查询 高等数学-1 课程成绩排名前五的学生，并且要分数大于80的学生信息（学号、姓名、课程名称、分数）
select s.`StudentNo`,s.`StudentName`,`subjectname`,r.`StudentResult`
from `student` s left join `result` r
on s.StudentNo = r.StudentNo
inner join `subject` sub
on sub.subjectno = r.subjectno
where StudentResult>=80 and subjectname='高等数学-1'
order by r.StudentResult desc
limit 0,5
4.6、子查询
where （这个值是计算出来的）
本质：在where语句中嵌套一个语句
-- 查询 高等数学-1 的所有考试结果（学号，科目编号，成绩），降序
-- 方式一： 使用连表查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r inner join `subject` sub
on r.subjectNo = sub.subjectNo
where sub.subjectname = '高等数学-1'
order by StudentResult desc
方式二： 子查询
– 直接在result表中查询三个字段也是可以查到的，但是此时要查询这三个字段对应的科目名，即
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r
where `subjectNo` = () --括号中是准备查询的字段
但是此时在 result 中并没有 subjectName字段，所以要在 subject 表中查到 subjectNo对应的科目的科目号返回，即：
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r
where `subjectNo` = (
select `subjectNo` from `subject`
where subjectName = '高等数学-1'
)
order by StudentResult desc
练习：(子查询时由内而外的查询)
--查询：分数不小于80分的学生的学号和姓名，且科目是高等数学-1
-- 方式一：
select distinct s.`StudentNo`,s.`StudentName`
from `student` s
inner join result r
on s.StudentNo = r.StudentNo
where StudentResult >=80
--方式二：子查询
select distinct s.`StudentNo`,s.`StudentName`
from `student` s
inner join result r
on s.StudentNo = r.StudentNo
where `StudentResult` >=80
and `subjectNo` = any(
select `subjectNo` from `subject`
where `subjectName` = '高等数学-1'
)
--再改造，二次嵌套子查询
select StudentNo,StudentName
from student where StudentNo in (
select StudentNo from result
where StudentResult>=80 and subjectNo = any(
select subjectNo from `subject`
where subjectName = '高等数学-1'
)
)
方式二用到一个关键字any，子查询时可能会用到any关键字(不是很懂)。
-- 查询 高等数学-1 前五名同学的成绩的信息（学号、姓名、分数）
select s.`StudentNo`,`StudentName`,`StudentResult`
from student s
inner join result r
on s.StudentNo = r.StudentNo
and r.subjectNo = any(
select subjectNo from `subject`
where `subjectName` = '高等数学-1'
)order by `StudentResult` desc
limit 0,5
4.7、分组和过滤
-- 查询不同课程的平均分、最高分、最低分
-- 核心： （根据不同饿的课程分组）
select `Subjectname` 课程名,avg(StudentResult) 平均分,
max(StudentResult) 最高分,min(StudentResult) 最低分
from result r
inner join `subject` sub
on r.subjectNo = sub.subjectNo
group by r.subjectno
having 平均分>80
5、MySQL函数
5.1、常用函数
---------------------数学函数-------------------------
select abs(-10) -- 绝对值 10
select ceiling(5.5) -- 向右取整 6
select floor(-5.5) -- 向左取整 -6
select rand() -- 随机数，0~1之间的小数
select sign(9) -- 返回数据的符号0、-1、1
--------------------字符串函数------------------------
select char_length('rwee') -- 返回字符串额长度 4
select concat('迪迦','奥特曼','无敌') -- 连接函数 迪迦奥特曼无敌
select insert('我超爱编程',2,2,'热爱') -- 替换 我热爱编程
select lower('AAAbbb') -- 转小写 aaabbb
select upper('AAAbbb') -- 转大写 AAABBB
select instr('dsafds','a') -- 第几个出现的位置 3
select replace('迪迦奥特曼无敌','迪迦','泰罗') -- 替换
select substr('迪迦奥特曼无敌',2,3) -- 迪迦奥
select reverse('迪迦奥特曼无敌') -- 反转 敌无曼特奥迦迪
------------------时间和日期函数----------------------
select current_date() -- 当前日期
select curdate() -- 当前日期
select now() -- 当前时间
select localtime() -- 本地时间
select sysdate() -- 系统时间
select year(now()) -- 当前年份
select month(now()) -- 月
select day(now()) -- 日
select hour(now()) -- 时
select minute(now()) -- 分
select second(now()) -- 秒
-- 系统
select system_user()
select user()
5.2、聚合函数
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sun() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| … | … |
-- 查询一个表中有多少个记录
select count(studentname) from student; -- 指定货 会忽略所以的null值
select count(*) from student; -- 不会忽略null值
select count(1) from student; -- 计算行数
select sum(`StudentResult`) from result -- 求和
select avg(`StudentResult`) from result -- 平均值
select min(`StudentResult`) from result -- 最小值
select max(`StudentResult`) from result -- 最大值
5.3、数据库级别的MD5加密
主要增强算法复杂度和不可逆性；具体的值的md5是一样的
-- 创建一个表
create table `testmd5`(
`id` int(4) not null,
`name` varchar(20) not null,
`pwd` varchar(20) not null,
primary key(`id`)
)engine = innodb default charset=utf8
-- 插入一组数据
-- 明文密码
insert into testmd5 values
(1,'张三',12324),
(2,'李四',14324),
(3,'王五',12324),
(4,'赵六',12324)
-- 加密，插入后加密
update testmd5 set pwd = md5(pwd) where id=2
update testmd5 set pwd=md5(pwd) --给所有密码加密
-- 插入时加密
insert into testmd5 value(5,'小明',md5('6789'))
-- 如何校验：将用户传递进来的值加密，进行对比
select * from testmd5 where `name`='小明' and pwd=md5('6789')
6、事务
将一组SQL放在一个批次中去执行
事务原则：
ACID
-
原子性（Atomicity） 要么都成功，要么都失败
-
**一致性（Consistency ** 事务前后的数据完完整性要保持一致
-
持久性（Durability） 事务一旦提交则不可逆，被持久化到数据库中
-
隔离性（lsolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干预，事务之间要相互隔离
隔离所导致的一些问题
脏读：
值一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
执行事务
-- mysql 是默认开启事务自动提交的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 开启（默认）
-- 手动处理事务
set autocommit = 0; -- 挂壁自动提交
-- 事务开启
start transaction -- 标记一个事务的开始，conf这个之后的sql都在同一个事务内
-- 提交 ：持久化（成功）
commit
-- 回滚：回到原来的样子
rollback
-- 事务结束
set autocommit =1; -- 开启自动提交
-- 了解
savepoint 保存点名 -- 设置一个事务的保存到
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点
模拟场景
首先创建一张存款表,插入数据
create table if not exists `account`(
`id` int(4) not null,
`name` varchar(20) not null,
`money` decimal not null,
primary key(`id`)
)engine = innodb default charset=utf8
-- 插入数据
insert into `account` values(1000,'张三',800),(1001,'李四',200);
事务提交方式：
set autocommit = 0; -- 关闭自动提交
start transaction; -- 开始事务，下面两条是一组事务
update account set money = money-100 where `name` = '张三';
update account set money = money+100 where `name` = '李四';
select * from `account`;
commit; -- 事务提交
rollback; -- 事务回滚
set autocommit = 1; -- 开启自动提交
7、索引
MYSQL官方对索引的定义为:索引（index）是帮助MySQL搞笑获取数据的数据结构
提取句子主干，就可以的得到索引的本质：索引是数据结构
7.1、索引的分类
- 主键索引（primary key）
- 唯一标识，主键不可重复，只能有一个列作为主键
- 唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以表示为唯一索引
- 常规索引（key/index）
- 默认的，index。key关键字来设置
- 全文索引（fulltext）
注： 在一个表中，主键索引只能有一个，唯一索引可以有多个
所以想要设置某个日期列的默认值为当前时间，只能使用 timestamp类型，并设置 DEFAULT NOW()或 DEFAULT CURRENT_TIMESTAMP()作为默认值。
索引作用
加快查询速度
-- 创建一个表
create table if not exists `app_user`(
`id` bigint(20) unsigned not null auto_increment,
`name` varchar(50) default '' comment '用户昵称',
`email` varchar(50) not null comment '用户邮箱',
`phone` varchar(20) default '' comment '手机号',
`gender` tinyint(4) unsigned default '0' comment '性别（0:男；1:女）',
`password` varchar(100) not null comment '密码',
`age` tinyint(4) default '0' comment '年龄',
`create_time` datetime DEFAULT null,
`update_time` timestamp null default current_timestamp on update current_timestamp,
primary key(`id`)
)engine = innodb default charset=utf8;
插入100万条数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT -- 返回类型
BEGIN
DECLARE num INT DEFAULT 1000000; -- declare 定义
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES
(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),uuid(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
DROP FUNCTION mock_data; -- 删除函数
SELECT mock_data() -- 执行此函数 生成一百万条数据
执行几段查询
select * from app_user; -- 1.218ms
select * from `app_user` where `name` = '用户9999'; -- 0.664ms
create index id_app_user_name on app_user(`name`); -- 添加一个索引
alter table `app_user` drop index id_app_user_name; -- 删除索引
select * from `app_user` where `name` = '用户9999'; -- 6.314ms
-- 。。。。离谱，添加索引后查询应该更快
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段中
索引的数据结构
Hash类型的索引
Btree:InnDB的默认数据结构
8、规范数据库设计
8.1、为什么需要设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（心态信息，某个关键的字，或者一些主字段）
- 实体标识
8.2、三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式（1NF）
原子性：保证第一列不可再分
第二范式（2NF）
满足第一范式的前提下，每张表只描述一件事情
第三范式（3NF）
在满足第一范式和第二范式的前提下，主键要和表中的其他字段直接相关，而不能间接相关
规范性 和 性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本、用户体验）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
9、JDBC
9.1、数据库驱动
驱动：声卡、显卡、数据库
9.2、JDBC
对于开发人员来说，我们只需要掌握JDBC接口的操作即可。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-635pUbLn-1645103156734)(image-20220215210215515.png)]](https://i-blog.csdnimg.cn/blog_migrate/dddababf5817eb6450065a28d194d00b.png)
java.sql
javax.sql
还需要导入一个数据库驱动包
9.3、第一个JDBC程序
package com.lu.lesson01;
import java.sql.*;
public class JDBCFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/mystudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "1234";
//3、了解成功，数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4、执行SQL的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象 去 执行SQL，可能存在的结构，查看返回结果
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql); //结构集，封装了我们全部查询出来的结果
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("password"));
System.out.println(resultSet.getObject("email"));
System.out.println(resultSet.getObject("birthday"));
}
//6、释放链接
resultSet.close();
statement.close();
connection.close();
}
}
url
String url="jdbc:mysql://localhost:3306/mystudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
-- mysql默认的主机号 3306
Connection
代表数据库
connection.commit();
connection.rollback();
connection.setAutoCommit();
// 提交、回滚、是否自动提交
prepareStatement 执行SQL的对象
statement.execute(sql); //可以执行任何sql代码
statement.executeQuery(sql); //查询返回一个结果集 resultSet
statement.executeQuery(sql); //增、删、改，返回一个收影响的行数
ResultSet
查询的结果集，封装了所有的查询结果。获得指定的数据类型
/*
在不知道列的类型用getObject
知道用指定类型
*/
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getDate();
resultSet.getBoolean();
resultSet.getFloat();
关闭连接
//释放链接
resultSet.close();
statement.close();
connection.close();
9.3、Statement 对象
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的语句，executeUpdate执行完后，将会返回一个整数
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的Results对象
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mystudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=1234
工具类：
public class JdbcUtils {
public static String driver = null;
public static String url = null;
public static String username = null;
public static String password = null;
static {
try {
InputStream resourceAsStream = JdbcUtils.class.getResourceAsStream("/db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void release(Connection conn,Statement st,ResultSet rs) throws SQLException {
if (rs !=null){
rs.close();
}
if (st !=null){
st.close();
}
if (conn !=null){
conn.close();
}
}
}
测试：
package com.lu.lesson02;
public class Test {
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "insert into `users` (id,`name`,`password`,`email`,`birthday`) values(9,'lu',123451234,'ghjkl;','2022-02-16')";
System.out.println(statement.executeUpdate(sql));
} catch (Exception throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
SQL注入
package com.lu.lesson02;
public class SqlBug {
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from users where id = ''or '1=1'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
}
} catch (Exception throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
/*
3
王五
9
lu
20
张三
100
李四*/
SQL语句是非法的但是可以查出所有信息
9.4、事务
package com.lu.lesson02;
import com.lu.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransation {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
String sql1 = "update account set money = money + 200 where id = '1000'";
pst= conn.prepareStatement(sql1);
pst.executeUpdate();
String sql2 = "update account set money = money - 200 where id = '1001'";
pst= conn.prepareStatement(sql2);
pst.executeUpdate();
conn.commit();
JdbcUtils.release(conn,pst,rs);
}
}
9.5、数据库连接池
DBCP
需要用到commons-pool-1.6和commons-dbcp2-2.1.1
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mystudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=1234
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
工具类
package com.lu.lesson03.utils;
public class JdbcUtils_DBCP {
private static BasicDataSource dataSource = null;
static {
try {
InputStream resourceAsStream = JdbcUtils_DBCP.class.getResourceAsStream("/dbcpConfig.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection conn,Statement st,ResultSet rs) throws SQLException {
if (rs !=null){
rs.close();
}
if (st !=null){
st.close();
}
if (conn !=null){
conn.close();
}
}
}
测试
package com.lu.lesson03;
public class TestDBCP {
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_DBCP.getConnection();
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
}
} catch (Exception throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
