命令行连接!
mysql -uroot -p123456 ---连接数据库
sc delete mysql; 清空服务
update mysql.user set anthentication_string=password('123456')where user='root' and Host='localhost';--修改用户密码
flush privileges; --刷新权限
--所有的语句都是用;结尾
show databases; --查看所有的数据库
mysql> use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student(表名); --显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit; --退出连接
--单行注释(SQL的本来的注释)
/* SQL多行注释
hello
*/
数据库xxx语言 CRUD 增删改查!
DDL 定义
DML 操作
DQL 查询
DCL 控制
2.操作数据库
操作数据库》操作数据库中的表》操作数据库表的数据
mysql中的关键字不区分大小写
2.1操作数据库
1.创建数据库
CREATE DATABASE [if not EXISTS] person; --创建表,语句没有中括号,中括号中的内容为如果没有这个表的情况下创建表
2.删除数据库
DROP DATABASE if exists person;
--如果存在就删除
3.使用数据库
--table键的上面,如果你的表名或者字段名是一个特殊字符,就需要``括起来
use `school`
4.查看数据库
show databases --查看所有的数据库
2.2数据库的列类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小数据 3个字节
-
int 标准的整数 4个字节 常用 int
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节(精度有问题!)
-
decimal 字符串形式的浮点数,用于金融计算的时候,一般用decimal
字符串
-
char 字符串固定大小的 0-255
-
varchar 可变字符串 0-65535 常用的 String
-
tingtest 微型文本 2^8-1
-
text 文本串 2^16-1 保存大文本
时间日期
-
date YYYY--MM-DD 日期
-
time HH:MM:ss 时间格式
-
datetime date YYYY--MM-DD HH:MM:ss 最常用的时间格式
-
timestamp 时间戳 1970.1.1到现在的毫秒数!
-
year 年份表示
null
-
没有值,未知
-
==注意,不要使用NUll进行运算,结果为NUll
2.3数据库的字段属性(重点)
Unsigned 无符号:
-
无符号的整数
-
s声明了该列不能声明为负数
zerofill 0填充:
-
0填充的
-
不足的位数,使用0来填充,int(3),5 --005
auto_increment自增:
-
通常理解为自增,自动在上一条记录的基础上+1(默认)
-
通常用来设计唯一的主键·index,必须是整数类型
-
可以自定义设计主键自增的起始值和步长
非空 NUll not null
-
假设设置为 not null,如果不给它赋值,就会报错!
-
NULL,如果不填写值,默认就是null!
默认:
-
设置默认的值!
-
sex,默认值为男,如果不指定该列的值,则会有默认的值!
/* 每一个表,都必须存在以下五个字段!未来做项目用,表示一个记录存在意义。
id 主键
`vestion` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4创建数据库表(重点)
-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用SQL 创建
-- 学号 int 登陆密码 varchar(20)姓名,性别varchar(2),出生日期(datetime),家庭住址email
-- 注意点,使用英文(),表的名称和字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来!
-- 所有的语句后面加,(英文的),最后一个不用加
-- PRIMARY KEY(`id`) 主键是唯一的
CREATE TABLE `school`.`student` (
`id` INT(4) UNSIGNED ZEROFILL NOT NULL auto_increment COMMENT '学号',
`name` VARCHAR(20) 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(60) DEFAULT NULL COMMENT '住址',
`email` VARCHAR(50) default NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT charset=utf8;
/*
格式:
create table `数据库名`.`表名` (
·字段名· [类型varchar][属性Unsigned 无符号 ][注释 comment '学号'],
字段名· [类型varchar][属性zerofill 0填充][注释 comment '学号'],
字段名· [类型varchar][属性 auto_increment自增 ][注释 comment '学号'],
......
字段名· [类型varchar][属性][注释 comment '学号'],
PRIMARY KEY(`id`)
)[设置表类型 默认 ENGINE=INNODB][设置字符集charset=utf8]
*/
常用命令
SHOW create TABLE student; --查看student数据表定义的语句
DESC student; --显示表的结构
SHOW CREATE DATABASE school; --查看创建数据库的语句
2.5数据表的类型
-- 关于数据库引擎 /* INNODB 默认使用 MYISAM 早些年使用的 */
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 支持(全英文) |
表空间大小 | 较小 | 较大 约MYISAM2倍 |
常规使用操作:
-
MYISAM 节约空间,速度较快
-
INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还是文件的存储!
MySQL引擎在物理文件上的区别
-
innoDB 在数据库表中只有一个*。frm文件,以及上级目录下的 ibdata1文件
-
MYISAM对应文件
-
-
*.frm -表结构的定义文件
-
*.MYD 数据文件(data)
-
*.MYI 索引文件(index)
-
设置数据库表的字符集编码
charset=utf8
不设置的话,会是mysql默认的字符集集编码~不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
小技巧:通过 show create table student语句
显示表信息后右键复制,粘贴到查询面板上,就自动生成表的sql代码。
2.6修改删除表
修改
-- 修改表名: ALTER TABLE 旧表名 rename AS 新表名 ALTER TABLE user1 RENAME AS user -- 增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE `user` ADD `age` INT(11) -- 修改表的字段(重命名,修改约束!): ALTER TABLE 表名 MODIFY 字段名 列属性[] ALTER TABLE user1 MODIFY `age` VARCHAR(11)-- 修改约束 ALTER TABLE 表名 CHANGE `旧字段名` `新字段名` 列属性 ALTER TABLE user1 CHANGE `age` `age1` INT(1) -- 重命名 -- 删除表的字段 alter TABLE user1 DROP `age1` --删除表(如果表存在再删除) DROP TABLE IF EXISTS `user1`
==所有的创建和删除操作尽量加上判断,以免报错==
if not exists 如果不存在就创建
if exists 如果存在就删除
注意点:
-
``字段名,使用这个包裹!
-
注释: -- /**/
-
sql关键字大小写不敏感,建议写小写
-
所有的符号全部用英文
练习:
ALTER TABLE `user1` RENAME AS `user` --修改表名ALTER TABLE `旧表名` RENAME AS `新表名` ALTER TABLE `user` ADD `hobby` VARCHAR(20)-- 增加表的字段:ALTER TABLE `表名` ADD `字段名` 属性 ALTER TABLE `user` MODIFY `hobby` INT(20) -- 修改表的字段 的类型和属性 ALTER TABLE `user` CHANGE `hobby` `hobby1` VARCHAR(30) -- 修改表的字段名 与类型属性 alter TABLE `user` DROP `hobby1` -- 删除表的字段 DROP TABLE if EXISTS `user` -- 删除表 show CREATE DATABASE person -- 查看创建数据库的语句 show create table `student` -- 查看创建表的语句 DESC `student` -- 显示表的结构 DROP DATABASE if exists person -- 删除某个数据库如果存在的话 show DATABASES -- 查看所有的数据库 use `school` --使用数据库
3.MySQL数据管理
3.1外键(了解即可)
方式一:在创建表的时候,增加约束(麻烦,复杂)
CREATE TABLE IF NOT EXISTS `school`.`grade` ( `gradeid` INT(10) NOT NULL auto_increment COMMENT '年级编号', `gradename` VARCHAR(10) not null COMMENT '年级名字', PRIMARY KEY(`gradeid`) )ENGINE=INNODB DEFAULT CHARSET=utf8 CREATE TABLE if not EXISTS`student` ( `id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别', `gradeid` INT(10) NOT NULL auto_increment COMMENT '年级编号', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `address` varchar(60) DEFAULT NULL COMMENT '住址', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
方式二:创建表之后再创建外键关系
alter table `student` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`); -- alter table `表名` -- add constraint `约束名` foreign key(作为外键的列) references 哪个表(哪个字段);
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),在删除自己的表()
以上的操作都是物理外键,数据库级别的外键,,我们不建议使用,避免数据库过多造成困扰
最佳实践
-
数据库就是单纯的表,只用来存数据,只有行和列
-
我们想使用多张表的数据,想使用外键,程序去实现
3.2DML语言(全部记住)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
3.3添加 insert
-
insert
-- 插入语句(添加) -- insert into 表名([字段1,字段2,字段3])VALUES(`值1`),(`2`),(`3`) INSERT INTO `grade`(`gradename`)VALUES('大四'), -- 由于主键自增我们可以省略(如果不写字段,它就会一一匹配) -- 一般写插入语句,我们一定要数据和字段一一对应! -- 插入多个字段 INSERT INTO `grade` (`gradename`)VALUES('大二'),('大一')
语法:insert into 表名([字段1,字段2,字段3])VALUES(值1
),(2
),(3
)
注意事项:
-
字段和字段之间使用英文逗号隔开
-
字段是可以省略的,但是后面的值必须一一对应
-
可以同时插入多条数据,values后面的值,需要使用,隔开即可values(),(),....
3.4修改 update
-
update
update 修改谁(条件)set原来的值=新值
-- 修改学员名字,带了简介 UPDATE `student` SET `name`='赵武' WHERE id !=2; -- 不指定条件的情况下,会改动所有表! UPDATE `student` SET `name`='赵东' -- 修改多个属性,逗号隔开 UPDATE `student` SET `name`='大哥',`age`=18,`adress`='三屯' WHERE id=1;
条件:where 运算符 id等语某个值,大于某个值,在某个区间内修改...
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等语 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
between...and... | 在某个范围内 | [2,5] | |
and | 我和你&& | 5>1&&1>2 | false |
or | 我或你|| | 5>1&&1>2 | true |
-- 通过多个条件定位数据 update `student` set `name`='狂神' where name='大哥' and sex='男'
语法:update 表名 set colnum_name=value,[colnum_name=value,...] where[条件]
注意:
-
colnum_name 是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定,则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
3.5删除 delete
-
delete
语法:delete from 表名 [where] 条件
-- 删除数据(避免这样写,会全部删除) delete from `student` --删除指定数据 delete from `student` where id=1;
truncate 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
-- 清空数据库 truncate `student`
delete 和truncate区别
-
相同点:都能删除数据,都不会删除表结构
-
不同点:
-
-
TRUNCATE 重新设置 自增列 计数器会归零
-
TRUNCATE 不会影响事务
-
delete from `test` -- 不会影响自增 truncate table `test` -- 自增会归零
了解即可:DELETE删除的问题,重启数据库,现象
-
Innodb 自增列会从1开始(存在内存当中,断电即失)
-
MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
4.DQL查询数据(最重点)
4.1DQL
(Data Query LANGUAGE:数据查询语言)
-
所有的查询操作都用它 select
-
简单的查询,复杂的查询它都能作~
-
数据库中最核心的语言,最重要的语言
-
使用频率最高的语句
select完整的语法:
select [ALL | distinct] {*|table.* |[table.field1[as alias1][table.field1[as alias2]]} from table_name [as table_alias] [left | right | inner join table_name2] --联合查询 [where...] --指定结果需满足的条件 [group by..] --指定结果按照哪几个字段来分组 [having] --过滤分组的记录必须满足的次要条件 [order by...] --指定查询记录按一个或多个条件排序 [limit{[offset,]row_count |row_countoffset offset}]; --指定查询的记录从哪条至哪条
注意:[]括号代表可选的,{}括号代表必选的
4.2指定查询字段
-- 查询全部的学生 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
语法:select 字段名 ... from 表名
有的时候,列名字不是那么的见名知意。我们起别名 As 字段名 as 别名 表名 as 别名
练习:
-- 查询全部的学生 select 字段 from 表 SELECT * from student -- 查询指定字段 SELECT `name`,`pwd` from student -- 别名,给结果起一个名字,AS 给可以给字段起别名,也可以给表起 SELECT `name` AS 名字, `pwd` AS 密码 FROM student as 学生; --函数 concat(a,b) SELECT concat('名字:',name) AS 学生名单 from student;
去重 distinct
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有哪些同学参加了考试,成绩 select * from result -- 查询全部的考试成绩 select `studentNo` from result --查询有哪些同学参加了考试 select distinct `studentNo` from result --去除重复数据 select version() -- 查询系统版本(函数) select 100*3-1 as 计算结果 --用来计算(表达式) select @@auto_increment_increment --查询自增的步长(变量) --学员考试成绩 +1分查看 select `studentNo`,`studentResult`+1 as '提分后' from result
数据库中的表达式:文本值,列,NULL,函数,计算表达式,系统变量
select 表达式
from 表
4.3where条件子句
作用:检索数据中符合条件
的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,有一个为真,结果为真 |
not ! | not a !a | 逻辑非,真为假!假为真! |
尽量使用英文字母
--查询考试成绩在95-100分之间 SELECT `studentNo`,`studentResult` from result where studentResult>=95 and studentresult<=100 SELECT `studentNo`,`studentResult` from result where studentResult>=95 && studentresult<=100 --模糊查询(区间) select `studentno`,`studentresult` from result where studentresult between 95 and 100 -- 除了1000号学生之外的同学的成绩 select `studentno`,`studentresult` from result where studentno!=1000; where not studentno=1000;
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,则结果为真 |
is not null | a is not null | 如果操作符为 not 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其中的某一个值,结果为真 |
练习
-- 模糊查询 -- 查询姓刘的同学 -- like 结合(代表0-任意个字符) select `studentno`,`studentname` from result where studentname like '刘%' ; -- 查询姓刘后面一个字符的 select `studentno`,`studentname` from result where studentname like '刘_' ; -- 查询姓刘后面两个字符的 select `studentno`,`studentname` from result where studentname like '刘__' ; -- 查询名字中有加字的同学 select `studentno`,`studentname` from result where studentname like '%刘%' ; --------------------------------------------------------------- -- in 具体的一个或多个值 查询1001 1002 1003学员的成绩 select `studentno`,`studentname` from result where studentno IN(1001,1002,1003); select `studentno`,`studentname` from result where address in('北京'); ---------------null----not null-------------''--- -- 查询地址为空的学生 select `studentno`,`studentname` from result where address is NULL or adress=''; -- 查询没有生日日期的学生 select `studentno`,`studentname` from result where birthday is null ;
4.4联表查询
/* 思路: 1.分析需求,分析查询的字段来自哪些表,(连接查询) 2.确定使用哪些连接查询? 7种 确定交叉点,这两个表中哪个数据是相同的 判断条件:学生表中的studentno=成绩表中的studentno join(连接的表) on (判断的条件) 连接查询 where 等值查询 */ -- inner JOIN中间查询 select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数 from student as s inner join result as r on s.studentno=r.studentno -- right join右查询 select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数 from student as s RIGHT JOIN result as r on s.studentno=r.studentno -- left join右查询 select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数 from student as s left JOIN result as r on s.studentno=r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- 查询缺考同学信息 select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数 from student as s left JOIN result as r on s.studentno=r.studentno where studentresult is null ; -- 我们要 查询哪些数据 SELECT -- 需要从哪几个表中查 FROM 表 xxx join 连接的表 on 交叉条件 -- 假设存在一种多张表查询,慢慢来,先查询两张表增加然后再慢慢增加 -- FROM a left join b -- from a right join b -- 查询 学号,姓名,科目名,成绩, SELECT s.studentno ,studentname,subjectname,studentresult from student as s left join result r on s.studentno=r.studentno inner join subject as sub on r.subjectno=sub.subjectno
自连接
父类表:
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类表
categoryid | pid | categoryName |
---|---|---|
4 | 3 | 数据库 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
8 | 2 | 办公信息 |
操作:查询父类对应的子类关系。
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 创建表 CREATE TABLE `school`.`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 auto_increment=9 DEFAULT charset=utf8 -- 添加数据 insert into `category` (`categoryid`,`pid`,`categoryName`) VALUES('2','1','信息技术'), ('3','1','软件开发'), ('4','3','数据库'), ('5','1','美术设计'), ('6','3','web开发'), ('7','5','ps技术'), ('8','2','办公信息'); show create table `category` desc `category` -- 查询父子信息:把一张表看为两个一模一样的表 select a.`categoryName` as '父栏目', b.`categoryName` as'子栏目' FROM `category` as a,`category` as b where a.`categoryid`=b.`pid`;
4.5分页和排序
分页
4.6子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 查询java第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数) SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult` from student as s inner join result as r on s.`studentno`=r.`studentno` inner join `subject` as sub r.`subjectno`=sub.`subjectno` where `subjectname`='java第一学年' and `studentresult`>=80 order by `studentresult` desc limit 0,10 -- 查询学生编号,学生姓名,和高等数学-2大于80分的编号 select s.`studentno`,`studentname` from student as s inner join result as r on s.`studentno`=r.`studentno` inner join subject as sub on r.`subjectno`=sub.`subjectno` where `subjectname`='高等数学-2' and `studentresult`>80 -- 子查询 select s.`studentno`,`studentname` from student as s inner join result as r on s.`studentno`=r.`studentno` where `studentresult`>80 and `studentno`=( select `studentname` from subject where `subjectname`='高等数学-2' ) -- 再改造(由里及外) select `studentno`,`studentname` from student where `studentno`=( select `studentno` from result where `subjectno`=( select `subjectno` from `subject` where `subjectname`='高等数学-2' ) )
练习:
-- 练习:查询c语言-1 前5名同学的成绩的信息(学号,姓名,分数) select s.`studentno`,`studentname` from student as s inner join result as r on s.`studentno`=r.`studentno` inner join subject as sub on r.`subjectno`=`s.subjectno` where `subjectname`='c语言-1' ORDER BY `studentresult` DESC limit 0,5 -- 子查询 select s.`studentno`,`studentname` from student as s inner join result as r on s.`studentno`=r.`studentno` where `subjectno`=( select `subjectno` from `subject` where `subjectname`='c语言-1' ) ORDER BY `studentresult` DESC limit 0,5 -- 嵌套查询 select `studentno`,`studentname` from student where `studentno`=( select `studentno` from `result` where `subjectno`=( select `subjectno` from `subject` where `subjectname`='c语言-1' ) ) order by `studentresult` desc limit 0,5
4.7分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80 -- 核心:(根据不同课程分组) SELECT s.`subjectname`,avg(`studentresult`) as 平均分,max(`studentresult`),min(`studentresult`) from result as s inner join `subject` as sub on s.`subjectno`=sub.`subjectno` GROUP BY r.`subjectno` -- 通过什么字段来分组 HAVING 平均分>80
4.8select小结
顺序很重要
select 去重 要查询的字段 from 表(注意:表和字段可以取别名) xxx join 要链接的表 on 等值判断 where(具体的值,子查询语句) group by(通过哪个字段来分组) having (过滤分组后的信息,条件和where是一样的,位置不同) order by (通过哪个字段排序)[升序asc 降序desc] limit startindex, pagesize 业务层面: 查询:跨表,跨数据
5.MySQL函数
5.1常用函数
----------常用函数------------------------------ -- 数学运算 select ABS(-8) -- 绝对值 ABS(X) select CEILING(9.4) -- 向上取整 ceiling(x) SELECT FLOOR(9.4) -- 向下取整 FLOOR(X) SELECT RAND() -- 返回一个0-1之间的随机数 SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1,整数返回1 -- 字符串函数 select char_length('即使再小的帆,也能远航') -- 查字符串长度 CHAR_LENGTH(str) select CONCAT('','','') -- 拼接字符串 select `INSERT`('我爱变成hello',1,4,'超级热爱编程') -- 查询,从某个位置开始替换某个长度 SELECT LOWER('KuangShen') -- 小写字母 select UPPER('KuangShen') -- 大写字母 select INSTR('KuangShen',n) -- 但会第一次出现的子串的索引 SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 一段字符串,替换指定字符串 select SUBSTR('狂神说坚持就能成功',4.6) -- 一段字符串,从第几个字符开始,截取多少个字符 select reverse('清晨我上马') -- 反转字符串 -- 查询 姓 周的同学, 改为 邹 SELECT REPLACE(studentname,'周','邹') from student where studentname like '周%' -- 时间和日期函数(记住) 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() -- 用户名 select version() -- 版本
5.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 技术 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
... |
=================聚合函数=================== -- 都能统计 表中的数据 select count(studentname) from student; -- count(字段),会忽略所有的null值 SELECT count(*) from student; --count(*),不会忽略null值,本质 计算行数 SELECT count(1) from result; -- count(1),不会忽略所有null值 本质 计算行数 SELECT sum(`studentresult`) as 总和 from result SELECT avg(`studentresult`) as 平均分 from result SELECT max(`studentresult`) as 最高分 from result SELECT min(`studentresult`) as 最低分 from result
5.3数据库级别的MD5加密(扩展)
什么是MD5?
主要增强算法复杂度和不可逆性。
MD5不可逆,具体的值的MD5是一样的。
MD5 破解网站的原理,背后有一个字典,MD5加密后的值 加密前的值
-- ==================测试MD5加密============================= CREATE TABLE `school`.`testmd5`( `id` INT(10) NOT NULL, `name` VARCHAR(10) NOT NULL, `pwd` VARCHAR(20) NOT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT charset=utf8; -- 添加三个用户信息 INSERT INTO `testmd5` VALUES(1,'赵东','123'),(2,'有钱','1'),(3,'帅气','1'); -- 给用户的全部密码加密 UPDATE testmd5 SET pwd = md5(pwd) where id = 2 -- 添加信息的时候,给密码加密 INSERT INTO `testmd5` VALUES(4,'小明',MD5('123')); -- 如何效验,将用户传递进来的密码进行md5加密,用值来对比 SELECT * from testmd5 WHERE name ='小明' and pwd= MD5('123');
6.事务
6.1什么是事务
事务原则:ACID原则 原子性,一致性,隔离性,持久性
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保证一致
持久性(Durability)--- 事务提交
事务一旦提交则不可逆,被持久化到数据库中!
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间相互隔离。
隔离所导致的一些问题
-- mysql 是默认开启事务自动提交的 set autocommit =0 -- 关闭自动提交事务 set autocommit =1 -- 开启自动提交事务(默认) -- 手动处理事务 set autocommit =0 -- 关闭自动提交 -- 事务开启 start transaction -- 标记一个事务的开始,从这个之后的 sql 都在同一事务内 insert xxx insert xxxx -- 提交:持久化(成功!) COMMIT -- 回滚:回道原来的样子(失败!) ROLLBACK -- 事务结束 set atuommit=1 -- 开启自动提交 -- 了解 savepoint 保存点名 -- 设置一个事务的保存点 rollback to savepoint 保存点名 -- 回滚到保存点 release savepoint 保存点名 -- 撤销保存点
模拟事务
7.索引
7.1索引的分类
-
主键索引 (primary key)
-
唯一的标识,主键不可重复,只能有一个列作为主键
-
-
唯一索引(unique key)
-
避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
-
-
常规索引(key/index)
-
默认的,index,key关键字来设置
-
-
全文索引(FUllText)
-
在特定的数据库引擎下才有,MyISMA
-
快速定位数据
-
索引在校数据量的时候,用户不大,但是在大数据的时候,区别十分明显
7.2索引原则
-
索引不是越多越好
-
不要对经常变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree:innodb 的默认数据结构
8.权限管理和备份
8.1用户管理
======================创建用户=========================== -- 创建用户 create user 用户名 identified by ‘密码’ create user kuangshen identified by '123456' -- 修改密码(修改当前用户名密码)set password= password('新密码') SET PASSWORD= PASSWORD('123456') -- 修改密码(修改制定用户名密码) set password for root = password('123456') -- 重命名 rename user 原用户名 to 新用户名 rename user root to rootdouble -- 用户授权 grant all privileges 全部的权限 库.表 -- 除了给别人授权,其他都能够干 grant all privileges on *.* to kuangshen -- 查询权限 show grants FOR kuangshen -- 茶看制定用户的权限 show grants for root@localhost -- root 用户的权限 -- 撤销权限 revoke 那些权限, 在哪个库撤销 给谁撤销 revoke all privileges on *.* from kuangshen -- 删除用户 drop user kuangshen
8.2mysql备份
为什么要备份:
-
保证重要的数据不丢失
-
数据转移
MySQL 数据库备份的方式
-
直接拷贝物理文件
-
在可视化工具中手动导出
-
使用命令行导出 mysqldump 命令行使用
mysqldump -hlocalhost -uroot -p123456 school student >D:/1.sql 导出 从本地导出 用户名 密码 数据库名 表名 》指向 path(导出的路径) (导出多张表,在表名后空格 表名) # 导入 # 导出 # source 备份文件 source d:1.sql mysql -u用户名 -p密码 库民<备份文件
9.规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
-
数据冗余,浪费空间
-
数据库插入和删除都会麻烦、异常【屏蔽使用物理外界】
-
程序的性能差
良好的数据库设计:
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中,关于数据库的设计
-
分析需求:分析业务和需要处理的数据库的需求
-
概要设计L设计关系图E-R图
设计数据库的步骤:
-
收集信息,分析需求
-
用户表(用户登陆注销,用户的个人信息,写博客,创建分类)
-
分类表(文章分类,谁创建的)
-
文章表(文章的信息)
-
友链接(友链信息)
-
自定义表(系统信息,某个关键的子,或者一些主字段)
-
-
标识实体(把需求落地到每个字段)
-
标识实体之间的关系
-
写博客user-->blog
-
创建分类user -->category
-
关注:user-->User
-
9.2三大范式
为什么需要数据规范化
-
信息重复
-
更新异常
-
插入异常
-
无法正常显示信息
-
-
删除异常
-
丢失有效的信息
-
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
墙体:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标(成本,用户体验!)数据库的性能更加重要
-
在规范性能的问题的时候,需要适当的考虑一下规范性!
-
故意给某些表增加一些冗余的字段。(从多表查询中变为单标查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10.JDBC(重点)
10.1数据库驱动
驱动:声卡、显卡、数据库
10.2JDBC
sun公司为了简化开发人员的(对数据库的同一)操作,提供了一个(java操作数据库)规范,俗称JDBC,这些规范的实现具体由具体的厂商去做
10.3第一个JDBC程序
1.创建一个普通项目
2.导入驱动,
3.编写测试代码
// 我的第一个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设置中文字符为utf8 useSSL=true 使用安全连接 String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username="root"; String password="19941027"; //3.连接成功,数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4.执行SQL对象 Statement Statement statement = connection.createStatement(); //5.执行SQL的对象去执行 sql 可能存在结果,查看返回结果 String sql="select * from student"; ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("password="+resultSet.getObject("pwd")); System.out.println("sex="+resultSet.getObject("sex")); System.out.println("birthday="+resultSet.getObject("birthday")); System.out.println("address="+resultSet.getObject("address")); System.out.println("email="+resultSet.getObject("email")); System.out.println("========================================"); } //5.关闭连接 resultSet.close(); statement.close(); connection.close(); } }
步骤总结:
-
加载驱动
-
连接数据库DriverManager
-
获得执行sql的对象, statment
-
获得返回的结果集()
-
释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Class.forname("com.mysql.jdbc.Driver"); //推荐用第二种方式创建驱动 //原因:Driver底层是一个静态代码块里面有创建Driver代码,运行就会创建一次,如果用第一种就会创建两次 Connection connection = DriverManager.getConnection(url, username, password); //Connection 代表数据库 connection.rollback();//事务回滚 connection.commit();//事务提交 connection.setAutoCommit();//数据库设置自动提交
URL
//2.用户信息 和url //useUnicode=true 支持中文编码 characterEncoding=utf8设置中文字符为utf8 useSSL=true 使用安全连接 String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false"; // mysql --3306 //协议://主机:端口号/数据库名?参数1&参数2&参数3 //oralce --1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象 PrepareStatement执行SQL的对象
//5.执行SQL的对象去执行 sql 可能存在结果,查看返回结果 String sql="select * from student";//编写sql statement.executeQuery(sql);//执行查询操作,返回 ResultSet statement.execute(sql);//执行任何sql statement.executeUpdate(sql);//更新、插入、删除。都是用这个,返回一个受影响的行数
ResultSet查询的结果集:封装了所有的查询结果
释放资源
// 我的第一个JDBC程序 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/school?useSSL=false&useUnicode=true&characterEncode=utf8"; //useUnicode=true 支持中文编码 characterEncoding=utf8设置中文字符为utf8 useSSL=true 使用安全连接 String username="root"; String password="19941027"; //3.连接成功,创建数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4.创执行sql语句的对象 Statement statement = connection.createStatement(); //5.写sql语句,执行SQL语句 ,查询,用execute 返回结果集 String sql="select * from student"; ResultSet resultSet = statement.executeQuery(sql); //6.遍历查询结果 while (resultSet.next()){ System.out.println(""); System.out.println(""); System.out.println(""); System.out.println(""); System.out.println(""); System.out.println("============================="); } //7.释放资源 resultSet.close(); statement.close(); connection.close(); } }
10.4 statement对象
数据库配置文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=19941027
提取工具类
public class JdbcUtils { private static String dirver=null; private static String url=null; private static String username=null; private static String password=null; static { //获取类加载器,后得到资源() try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); dirver=properties.getProperty("driver"); url=properties.getProperty("url"); username=properties.getProperty("username"); password=properties.getProperty("password"); //1.驱动只加载一次 Class.forName(dirver); } 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){ if (rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (st!=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
测试增加数据
public class TestInsert { public static void main(String[] args) { //提升作用域,方便在finally后关闭资源 Connection conn=null; Statement st=null; ResultSet rs=null; //调用utils方法,连接数据库 try { conn=JdbcUtils.getConnection();//获取数据库连接 st=conn.createStatement();//获取执行sql对象 String sql="insert into student(id,`name`,`pwd`,`sex`,`birthday`,`address`,`email`) values (6,'赵龙','121212','男','1994-10-27','小别墅','11111111@qq.com')"; //增加数据的sql语句 //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入数据成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } } }
删除数据
public class TestDelete { public static void main(String[] args) { //提升作用域,方便在finally后关闭资源 Connection conn=null; Statement st=null; ResultSet rs=null; //调用utils方法,连接数据库 try { conn=JdbcUtils.getConnection();//获取数据库连接 st=conn.createStatement();//获取执行sql对象 String sql="delete from student where id=4 "; //增加数据的sql语句 //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } } }
更新数据
public class TestUpdate { public static void main(String[] args) { Connection conn=null; Statement st=null; ResultSet rs=null; try { conn=JdbcUtils.getConnection(); st=conn.createStatement(); String sql="update student set `name`='帅哥',`sex`='女' where id=3"; //增加数据的sql语句 //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } } }
查找数据
public class TestSelect { public static void main(String[] args) { Connection conn=null; Statement st=null; ResultSet rs=null; try { conn = JdbcUtils.getConnection(); st=conn.createStatement(); String sql="select * from student where id=5"; rs=st.executeQuery(sql); while (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("sex")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
SQL注入问题
存在漏洞,会被攻击,导致数据泄露。SQL会被拼接
public class TestSelect { public static void login(String username,String password){ Connection conn=null; Statement st=null; ResultSet rs=null; try { conn = JdbcUtils.getConnection(); st=conn.createStatement(); // select * from student where `name`='狂神' and `pwd`='123456'; String sql="select * from student where `name`='"+username+"' and `pwd`='"+password+"'"; rs=st.executeQuery(sql); while (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("pwd")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } public static void main(String[] args) { login("'or'1=1","'or'1=1"); } }
10.5 preparedStatement对象
preparedStatement可以防止sql注入。效率更高!
1.新增
public static void main(String[] args) { Connection conn=null; PreparedStatement st=null; ResultSet rs=null; //调用utils方法,连接数据库 try { conn=JdbcUtils.getConnection();//获取数据库连接 //预编译sql 先写sql 然后不执行 使用?占位符代替参数 String sql="insert into student(id,`name`,`pwd`,`sex`,`birthday`,`address`,`email`) values(?,?,?,?,?,?,?)"; st=conn.prepareStatement(sql);//获取执行sql对象 //手动给参数赋值 st.setInt(1,6); st.setString(2,"赵子龙"); st.setString(3,"123321"); st.setString(4,"男"); // new Date().getTime() 获取时间戳 st.setDate(5,new java.sql.Date(new Date(1).getTime())); st.setString(6,"常山"); st.setString(7,"9999999.com"); //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入数据成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } } }
2.删除
public static void main(String[] args) { //提升作用域,方便在finally后关闭资源 Connection conn=null; PreparedStatement st=null; ResultSet rs=null; //调用utils方法,连接数据库 try { conn=JdbcUtils.getConnection();//获取数据库连接 String sql="delete from student where id=? "; st=conn.prepareStatement(sql)//获取执行sql对象 st.setInt(1,1); //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } }
3.更新
public static void main(String[] args) { Connection conn=null; PreparedStatement st=null; ResultSet rs=null; try { conn=JdbcUtils.getConnection(); String sql="update student set `name`=?,`sex`=? where id=?"; st=conn.prepareStatement(sql); st.setString(1,"赵帅"); st.setString(2,"男"); st.setInt(3,2); //增加数据的sql语句 //利用executeUpdate获取结果,看结果是否大于0,是,代表插入数据成功 int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { //调用关闭资源方法,关闭开启的对象 JdbcUtils.release(conn,st,null);//因为增加没有用到rs方法,所以可以用null代替值 } } }
4.查询
public static void main(String[] args) { Connection conn=null; PreparedStatement st=null; ResultSet rs=null; try { conn=JdbcUtils.getConnection(); String sql="select * from student where id=?"; st=conn.prepareStatement(sql); st.setInt(1,1); rs = st.executeQuery(); if (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("pwd")); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } }
5.防止SQL注入
PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符
假设其中存在转义字符,就直接忽略, ‘ 会被直接转义
public static void main(String[] args) { login("赵龙","121212"); } public static void login(String username,String password){ Connection conn=null; PreparedStatement st=null; ResultSet rs=null; try { conn=JdbcUtils.getConnection(); String sql="select * from student where `name` =? and `pwd`=?"; st=conn.prepareStatement(sql); st.setString(1,username); st.setString(2,password); rs = st.executeQuery(); if (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("pwd")); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } }
10.6使用idea连接数据库
右侧有datebase按钮
10.7事务
10.9数据库连接池
数据库连接 -- 执行完毕-- 释放
连接-- 释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的