数据库
数据库
数据库的使用
操作数据库> 操作数据库中的表>操作数据库中表的数据
mysql中不区分大小写
-- 1、创建数据库
create database [ if not exists] westos
-- 2、移除数据库
drop database [if exists] hello
-- 3.使用数据库,如果库名为关键字则将库名放在`库名`中
use westos
select `user` from student -- 查看数据库
-- 查看数据库
show database
数据库列的类型
数值
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小数据 3个字节
int 标准证书 4个字节
bigint 较大数据 8个字节
float 单精度 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算时，一般使用decimal
字符串类型
char 字符串类型，固定大小 0-255
varchar 可变字符串 0-65535
tinytext 微型文本 2^8-1
text 文本串 2^16-1
时间日期
date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1 -现在 的毫秒数
null
没有值 未知
注意： 不要使用null进行运算 结果为null
数据库的字段属性
unsigned：无符号整数
zerofill：0填充 不足的位数用0来填充
自增： 再上一条的基础上加一
非空：null ， not null，假设设为not null 如果不给他赋值就会报错
默认：default 设置默认值，如果没有指定则会设置为默认值
create table if not EXISTS person(
`name` varchar(20) not null default '000' comment 'name',
`age` int(4) not null default 0 comment 'age',
`sex` varchar(8) not null default '男' comment 'sex',
`id` varchar(18) not null comment 'id',
`num` int(4) not null auto_increment comment 'number',
primary key (`num`)
)engine = innodb default CHARSET = utf8;
-- auto_increment定义的类型必须为主键，否则报错
show create database tb_mall
-- CREATE DATABASE `tb_mall` /*!40100 DEFAULT CHARACTER SET latin1 */
show create TABLE person -- 查看创建表的语句
/* CREATE TABLE `person` (
`name` varchar(20) NOT NULL DEFAULT '000' COMMENT 'name',
`age` int(4) NOT NULL DEFAULT '0' COMMENT 'age',
`sex` varchar(8) NOT NULL DEFAULT '男' COMMENT 'sex',
`id` varchar(18) NOT NULL COMMENT 'id',
`num` int(4) NOT NULL AUTO_INCREMENT COMMENT 'number',
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 */
desc person
-- 查看表的结构
数据库引擎
MyISAM与INNODB
MyISAM 早些年使用
INNODB mysql默认使用
| MyISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小（内存） | 较小 | 较大（约为MyISAM 两倍） |
| 常规操作 | MyISAM | INNODB |
|---|---|---|
| 节约空间 | 安全性高 | |
| 速度较快 | 支持事务处理 | |
| 多表多用户操作 | ||
所有的数据库文件都存在再data文件下
可以发现，innodb再数据库中只有一个*.frm文件，以及上级目录下的ibdata1文件
myisam对应文件
- *.frm 表结构的定义文件
- *.myd 数据文件（data）
- *.myi 索引文件（index）
charset = utf8 -- 设置数据库表的字符集编码，不设置的话，会是mysql默认的字符集编码 - 不支持中文
修改删除表
修改
-- 修改表 alter table 旧表名 rename as 新表名
alter table student rename as student1 -- 修改表明
alter table student1 rename as student
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 字段类型
ALTER TABLE student ADD `age` INT(4)
ALTER TABLE student ADD `school` varchar(20) not null DEFAULT 'school'
-- 修改表的字段
alter table student MODIFY age varchar(10) -- 修改约束和字段类型，这个无法重命名
alter table student CHANGE age `old` INT(4) -- 修改表名，change不能修改约束和字段类型
alter table student DROP age -- 删除表字段
MySQL数据管理
外键
create TABLE teacher (
`id` int(4) NOT null auto_increment COMMENT 'id',
`name` VARCHAR(20) NOT NULL,
`sex` VARCHAR(10) NOT NULL,
`address` VARCHAR(30) NOT NULL,
`email` VARCHAR(20),
PRIMARY key(`id`)
)ENGINE = INNODB DEFAULT charset = utf8;
ALTER TABLE teacher ADD greadeid VARCHAR(10)
alter table teacher modify greadeid varchar(10) not NULL
-- schoolin表的grade字段，要去引用teacher表的gradeid
-- 定义这个外键 KEY `fk_grade` (`grade`),
-- 给这个外键添加约束 （执行引用） references 引用
-- constraint `fk_hrade` foreign KEY (`grade`) references `teacher` (`greadeid`)
create TABLE `university` (
`id` int(4) NOT null auto_increment COMMENT 'id',
`name` VARCHAR(20) NOT NULL,
`grade` VARCHAR(10) NOT NULL,
`address` VARCHAR(30) NOT NULL,
`email` VARCHAR(20) default NULL,
`teacherId` int(4) not NULL,
PRIMARY key(`id`),
KEY `teacher_id` (`teacherId`),
constraint `teacher_id` foreign KEY (`teacherId`) references `teacher`(`id`)
)ENGINE = INNODB DEFAULT charset = utf8;
-- 注意，这里外键设置需要引用另一个表中的主键，不然报错1005.还需要使外键数据类型一致
//比较麻烦
换种方法添加外键
alter table student
ADD schoolid int(4) NOT null
/*alter table 添加外键的表名
add constraint `外键名（自定义）` foreign key
(`本表字段名`) references `其他表名` (`其他表主键`)*/
alter table student
add constraint `ky_id` foreign key
(`schoolid`) references `university` (`id`)
在关系型数据库中，外键也被称为关系键，它是关系型数据库中提供关系表之间连接的多个列1，这一组数据列是当前关系表中的外键，也必须是另一个关系表中的候选键（Candidate Key），我们可以通过候选键在当前表中找到唯一的元素2。在通常情况下，我们都会使用关系表中的主键作为其他表中的外键，这样才可以满足关系型数据库对外键的约束。
为什么在数据库中不推荐使用外键？
外键：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。
为什么不用外键：
增加了工作的复杂性：每次做DELETE或者UPDATE时都必须考虑外键约束，这样我们在开发和测试数据时会极其痛苦；并且外键的主从关系是固定的，当需求发生变化进行修改时会带来很多麻烦。
对分库分表不友好：因为分库分表下外键是无法生效的。
但其实外键本身也是有好处的，比如：
- 保证了数据库数据的一致性和完整性
- 级联操作方便，减轻了程序代码量.......
- 阿里巴巴开发手册中说到：【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
但在我看来，归根结底是因为我们想让数据和业务分开来。数据是数据，业务是业务。数据库只保存数据，尽量不要牵扯上业务逻辑（除必要逻辑外，比如权限管理等）。
DML语言
数据库意义：数据管理，数据存储；
dml语言：数据操作语言
添加insert
-- 插入语句
/*
INSERT INTO 表名 (插入的列名 ， ‘，’隔开)
VALUES (对应列名的值，要注意对应列名的类型)
*/
INSERT INTO vip (`id`,`name`,`sex`,`card`,`phone`,`v_type`,`endTime`)
VALUES (7,'张应富','男','15266447935','13561167854','初级会员','2023-12-12')
-- 主键可以自增，我们可以省略，如果不写一一匹配,数据字段一一对应
USE school
INSERT INTO student VALUES (2,'张应富','男',12,'山东大学',1)
USE hotel;
INSERT INTO guests VALUES (
8,'王涛','男',15266448382,13561167936,'2023-1-2-晚上10:11:11','2023-1-4-早上8:12:32','普通大床房',208
);
修改update
update 修该谁 (条件) set 原来的值 = 新值 where 条件判断
UPDATE guests set name = 'wangte' WHERE id = 1;
-- 修改属性
update `student` set `name` = 'value',`email` = 'email' where id = 1;
-- update `表名` set `列名` = 'value'[多值使用逗号隔开] where 条件判断语句
操作符
- =
- <>或!=
- < > <= >=
- between and 在 …… 之间
- or 或者
- and 和
没有指定条件则会修改所有的列value是一个具体的值或者是变量对象
删除 delete
delete from 表名 where 条件
DELETE from guests where id = 1
truncate 表名
truncate guests
都能删除数据，都不会删除表结构
truncate 重新设置 自增列 计数器会归零,不会影响事务
delete 不会重新删除自增列，不会影响自增，
create table school (
`id` int(4) not null auto_increment,
`name` int(4) not null default 0,
PRIMARY KEY (`id`)
)ENGINE = INNODB default charset = utf8;
DELETE FROM school WHERE id > 1
INSERT into school (`name`) values (1),(2),(3)
truncate school
DQL数据查询语言
data query language
SELECT * FROM school -- 查询school表中的全部数据
-- select 列名（* 为全部） from 表名 where 判断条件
select id AS xuehao,`name` xingming from school
-- 别名 AS 字段名 as 别名
-- select 列名 AS 自定义别名,`列名` 自定义别名 from 表名 as 自定义别名 where 判断条件
-- 函数 CONCAT(str1,str2,...)拼接字符串
select CONCAT('name',id,`name`) as xinname FROM school
select CONCAT('name',id,`name`) as xinname FROM school WHERE id = 1
-- 去重distinct，去除select语句中查询出来的重复数据只显示一条。
SELECT DISTINCT `id` FROM school -- 所有重复的数据都会被去除
-- 查询系统版本
select VERSION()
-- 计算结果
select 100*10-20 as 计算结果
select @@auto_increment_increment -- 查询2自增的步长
-- 列字段全加一
SELECT id+1 FROM school
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量
select 表达式 from 表 where 判断条件
where条件子句
由一个或多个表达式组成
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | A and B 或 A&&B | 与 |
| or || | A or B 或 A|| B | 或 |
| not ！ | not A 或 ！A | 非 |
尽量使用英文字母
select id,`name` from school where id = 1 && `name` = 1
select id,`name` from school where id = 1 and `name` = 1
select id,`name` from school where id = 1 or `name` = 2
select id,`name` from school where id = 1 || `name` = 2
select id,`name` from school where NOT id = 1
select id,`name` from school where id != 1
select id,`name` from school where id BETWEEN 1 and 3 -- 包含1和3
-- where 字段名 BETWEEN 区间上值 and 区间下值
select id,`name` from school where id BETWEEN 1 and 3 -- 包含1和3
select id,`name` from school where id not BETWEEN 1 and 3
select id,`name` from school where id BETWEEN 1 and 3
&& `name` = 2
select id,`name` from school where id BETWEEN 2 and 3
or `name` BETWEEN 1 and 2
模糊查询
比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | A is null | 如果操作符为null 结果为真 |
| is not null | A is not null | 如果操作符不为null 结果为真 |
| between and | A between B and C | A中再B和C之间的值 |
| like | A like B | SQL匹配 如果A匹配到B，则结果为真 |
| in | A in (A1，A2，A3,...) | 假设A再A1，A2，A3....中的某个值则结果为真 |
-- like 结合% （代表0-任意个字符） 或 _ （代表1个字符）来使用
SELECT * from vip WHERE `name` like '刘%' -- % （代表0-任意个字符）
SELECT * from vip WHERE `name` like '__' -- _ （代表1个字符）来使用
-- in(具体的一个或者多个值) 用法：where 列名 in(具体的多个值用，分开);
select * from vip where id in(2,4);
-- is null is not NULL
select * from vip where id is null
select * from vip where id is NOT null
join 联表查询
INNER join 表名 no 判断条件
right join 表名 no 判断条件
LEFT join 表名 no 判断条件
SELECT g.`name`,g.h_type,g.num,v.card
FROM vip AS v
INNER join guests AS g -- inner join 交集
ON v.`name` = g.`name`
-- SELECT 列表字段名，分清表字段可用别名.字段名 FROM 表名 AS 别名 INNER join 表名 AS 别名 on 判断条件
right join guests AS g on -- right join 右连接
SELECT v.`name`,g.h_type,g.num,v.card
FROM guests AS g
RIGHT join vip AS v -- RIGHT join vip 右连接到表
ON v.`name` = g.`name`
-- SELECT 列表字段名，分清表字段可用别名.字段名 FROM 表名 AS 别名 RIGHT join 表名 AS 别名 on 判断条件
SELECT v.`name`,g.h_type,g.num,v.card
FROM guests AS g
LEFT join vip AS v -- RIGHT join vip 左连接到表
ON v.`name` = g.`name`
-- SELECT 列表字段名，分清表字段可用别名.字段名 FROM 表名 AS 别名 left join 表名 AS 别名 on 判断条件
SELECT guests.`name`,guests.`phone`,home.num,price,home.h_type
FROM guests
LEFT JOIN vip
on vip.`name` = guests.`name`
inner join home
on home.num = guests.num
自连接
自连接：自己的表和自己的表连接，一张表拆分为两张一样的表
CREATE TABLE `category` (
`categoryId` int(10) NOT NULL auto_increment COMMIT '主题id',
`pid` int(10) NOT NULL '父id',
`name` varchar(20) not null '主题名称',
primary key(`categoryId`)
)ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO category VALUES(2,1,'信息技术');
INSERT INTO category VALUES(3,1,'计算机技术');
INSERT INTO category VALUES(4,1,'图画技术');
INSERT INTO category VALUES(5,1,'管理信息技术');
INSERT INTO category VALUES(6,2,'通信技术');
INSERT INTO category VALUES(7,3,'电路系统技术');
INSERT INTO category VALUES(8,4,'ps技术');
INSERT INTO category VALUES(9,5,'信息管理与信息系统技术');
INSERT INTO category VALUES(10,3,'软件工程');
select a.`name` as '父项目',b.`name` '子项目'
FROM category as a,category as b
WHERE a.pid = b.categoryId
分页和排序
分页 ： limit 排序： group by
SELECT * FROM tb_mall_goods_category
ORDER BY category_id DESC -- ORDER BY 字段名 // DESC 降序 ASC 升序
LIMIT 222,7-- limit 分页 0,7 起始位置,页面大小
-- 第一页 LIMIT 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- ……
-- 第N页 LIMIT （n-1）* pageSize，pageSize
-- pageSize 页面大小 ， n 当前页
-- 总页数 【数据总数/页面大小】
SELECT goods_name,goods_id,selling_price,stock_num
from tb_mall_goods_info
WHERE stock_num>5
GROUP BY selling_price DESC
LIMIT 0,5
子查询
本质：再where中再嵌套一套查询
SELECT * FROM tb_mall_goods_category
ORDER BY category_id DESC -- ORDER BY 字段名 // DESC 降序 ASC 升序
LIMIT 222,7-- limit 分页 0,7 起始位置,页面大小
-- 第一页 LIMIT 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- ……
-- 第N页 LIMIT （n-1）* pageSize，pageSize
-- pageSize 页面大小 ， n 当前页
-- 总页数 【数据总数/页面大小】
SELECT goods_name,goods_id,selling_price,stock_num
from tb_mall_goods_info
WHERE stock_num>5
GROUP BY selling_price DESC
LIMIT 0,5
use `school`;-- 创建学生表
drop table if exists `student`;
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=myisam default charset=utf8;
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment '年级编号',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 创建科目表
drop table if exists `subject`;
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;
-- 创建成绩表
drop table if exists `result`;
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 `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');
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1003,'123456','刘梦',0,1,'13800001234','三点意思','1980-1-1','text123@qq.com','1234532432342234'),
(1002,'123456','王涛',1,4,'13800002222','一五九二流','1992-1-2','text221@qq.com','123456199344235233');
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1004,'123456','刘梦',0,5,'13800001234','是否付对方','1990-1-1','text11234@qq.com','1234adasdas2342234'),
(1005,'123456','王涛',1,5,'13800002444','防晒霜到了','1997-1-2','text342534@qq.com','123dsfs199344235233');
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
--
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1001,1,'2013-11-11 16:00:00',78),
(1001,2,'2013-11-12 16:00:00',44),
(1001,3,'2013-11-11 09:00:00',77),
(1001,4,'2013-11-13 16:00:00',66),
(1001,5,'2013-11-14 16:00:00',76);
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1002,1,'2013-11-11 16:00:00',82),
(1002,2,'2013-11-12 16:00:00',72),
(1002,3,'2013-11-11 09:00:00',62),
(1002,4,'2013-11-13 16:00:00',92),
(1002,5,'2013-11-14 16:00:00',52);
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1003,1,'2013-11-11 16:00:00',95),
(1003,2,'2013-11-12 16:00:00',90),
(1003,3,'2013-11-11 09:00:00',98),
(1003,4,'2013-11-13 16:00:00',88),
(1003,5,'2013-11-14 16:00:00',78);
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1004,1,'2013-11-11 16:00:00',65),
(1004,2,'2013-11-12 16:00:00',70),
(1004,3,'2013-11-11 09:00:00',88),
(1004,4,'2013-11-13 16:00:00',98),
(1004,5,'2013-11-14 16:00:00',48);
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1005,1,'2013-11-11 16:00:00',55),
(1005,2,'2013-11-12 16:00:00',70),
(1005,3,'2013-11-11 09:00:00',58),
(1005,4,'2013-11-13 16:00:00',88),
(1005,5,'2013-11-14 16:00:00',98);
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1006,1,'2013-11-11 16:00:00',53),
(1006,2,'2013-11-12 16:00:00',74),
(1006,3,'2013-11-11 09:00:00',55),
(1006,4,'2013-11-13 16:00:00',89),
(1006,5,'2013-11-14 16:00:00',88);
-- 插入科目数据
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);
-- 查询课程为 高等数学-2 且分数不小于80 的学生的学号和姓名
SELECT s.studentno,s.studentname,gradeid from student s
WHERE s.studentno = (
SELECT r.studentno FROM result r
WHERE studentresult>80 AND r.subjectno =(
SELECT subjectno from `subject`
WHERE subjectname = '高等数学-2'
)
)
函数
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整 10
SELECT FLOOR(5.4) -- 向下取整 5
SELECT RAND() -- 返回一个0-1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('hello world!') -- 返回字符串长度
SELECT CONCAT('hell','o wo','rld') -- 拼接字符串
SELECT INSERT('i am happy!',5,1,' very ') -- 插入 从某个位置开始替换某个长度
SELECT LOWER('Hello World') -- 大写字母转换成小写
SELECT UPPER('hELLO,WORLD') -- 小写字母转换成大写字母
SELECT INSTR('zhang ying fu','n') -- 返回第一次出现的子串的索引位置
SELECT REPLACE('hello world',' ',' my ') -- 替换出现的指定字符串
SELECT SUBSTR('hello world!' FROM 5 FOR 6) -- 返回指定的字符串（原字符串，截取的位置，截取的长度）
SELECT REVERSE('hello') -- 反转
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(LOCALTIME())
SELECT SECOND(CURRENT_DATE())
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
聚合函数
| 函数 | 描述 |
|---|---|
| count(【列名】【*】【1】) | 统计表中的数据返回记录个数 |
| max() | 最大值 |
| min() | 最小值 |
| sum() | 求和 |
| AVG（） | 平均值 |
select 语法
SELECT COUNT(1) FROM result
SELECT COUNT(studentresult) FROM result
SELECT COUNT(*) FROM result
SELECT MAX(studentresult) as 最高分 from result
SELECT MIN(studentresult) as 最低分 from result
SELECT AVG(studentresult) as 平均分 from result
SELECT sum(studentresult) as 分数总和 from result
-- 查询不同课程的平均分，最高分，最低分
SELECT s.subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
inner JOIN `subject` s
on r.subjectno = s.subjectno
GROUP BY subjectname -- 分组，按照subjectname
-- 查询不同课程的平均分，最高分，最低分,且平均分大于80
SELECT s.subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
inner JOIN `subject` s
on r.subjectno = s.subjectno
GROUP BY subjectname
having AVG(studentresult)>80
md5加密
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
加密后的值始终不变
-- ======= 测试MD5加密 ==========
CREATE table `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(30) not null,
`password` varchar(30) not NULL,
PRIMARY key(`id`)
)engine = innodb default charset = utf8;
-- 插入
INSERT INTO testmd5 VALUES(
123,'zhangsan','zhangsan'
);
INSERT INTO testmd5 VALUES(
12345,'wangwu','wangwu'
);
INSERT INTO testmd5 VALUES(
456,'zhaoliu','zhaoliu'
);
-- 插入的都是明文密码
INSERT INTO testmd5 VALUES(
1234,'lisi','lisi'
);
-- 加密
alter TABLE testmd5 modify `password` varchar(60);
update testmd5 set `password` = MD5(`password`) WHERE `id` = 123
update testmd5 set `password` = MD5(`password`)
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- 校验，将用户的值传递进来，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `password` = MD5('zhaoliu')
SELECT * FROM testmd5 WHERE `password` = MD5(MD5('zhangsan'))
这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。
事务
事务：要么都成功，要么都失败
ACID是衡量事务的四个特性
-
原子性（Atomicity）：是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做，如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态
-
一致性（Consistency）是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
-
隔离性（Isolation）隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
-
持久性（Durability）：就是数据的持久化
需要注意的是，在最开始的定义中，事务必须要满足这四个特性。
但在许多地方的实际应用中，这四个特性并没有被完全实现，比如MySQL、Redis的事务就不支持原子性，所以后来这四个特性渐渐地变成了衡量事务的标准而不是事务的必须条件
脏读
指一个事务读取了另一个事务未提交的数据
不可重复读
在一个事务内读取表中的某一行数据，多次读取的结果不同，这个不一定错误，只是某些场合不同
事务
mysql是默认开启事务自动提交的
-- mysql 默认是开启事务提交的
SET autocommit = 0 -- 关闭
set autocommit = 1 -- 开启，默认
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START transaction -- 标记一个事务的开始从这个之后的sql都在一个事务内
-- 提交 持久化
COMMIT
-- 回滚 事务提交失败，回到原来的样子
rollback
-- 事务结束
set autocommit = 1 -- 开启自动提交
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点，跟游戏里的保存节点类似
RELEASE savepoint 保存点名 -- 撤销保存点
-- == 练习 == 转账 == == === == == === ==
CREATE DATABASE bank CHARACTER SET utf8
USE bank
CREATE TABLE account (
`id` int(3) NOT NULL auto_increment,
`name` VARCHAR(20) 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);
INSERT INTO account (`name`,`money`)
VALUES('B',6600.00);
INSERT INTO account (`name`,`money`)
VALUES('C',7000.00);
INSERT INTO account (`name`,`money`)
VALUES('D',12000.00);
-- 模拟转账
SET autocommit = 0; -- 关闭自动提交
-- 开始事务
start transaction;
UPDATE account set money = money+300
WHERE `name` = 'A';
UPDATE account set money = money-300
WHERE `name` = 'B';
UPDATE account set money = money - 500
WHERE `name` = 'C';
UPDATE account set money = money + 500
WHERE `name` = 'D';
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1 -- 开启自动提交
什么是索引
索引是帮助mysql高效获取数据的数据结构，索引是数据结构
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
主键索引（primary key（字段名））
我们知道每张表一般都会有自己的主键，mysql会在主键上建立一个索引，这就是主键索引。
主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。一般在建立表的时候选定。
普通索引（key/index）
普通索引是mysql里最基本的索引，没有什么特殊性，在任何一列上都能进行创建。
默认的
-- 创建索引的基本语法
CREATE INDEX indexName ON table(column(length));
-- 例子 length默认我们可以忽略
CREATE INDEX idx_name ON user(name);
唯一索引（unique key（字段名））
唯一索引可以重复，多个列都可以标识为唯一索引
全文索引（FullText）
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。正常情况下我们也不会使用到全文索引，因为这不是mysql的专长。
显示所有的索引信息
show index from 表名
-- 增加一个全文索引
alter table 表名 add fulltext index 索引名 (列名)
-- explain 分析sql的执行状况
索引不是越多越好‘
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用查询的数据上’
mysql备份
保证数据不丢失
数据转移
直接拷贝物理文件，data文件夹下所有文件
再可视化数据库软件中手动导出
使用命令行导出 mysqldump 命令
mysqldump -hlocalhost -uroot -proot school student >D:/a.sql
mysqldump -h主机名 -u用户名 -p密码 数据库 表名 > 物理磁盘导出位置
导入
登录mysql进去 source 文件地址
mysql -u 用户名 -p密码 库名 < 文件地址
数据库的三大范式
第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
第二范式(2NF)：前提满足第一范式；每张表只描述一件事情；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
第三范式(3NF)：前提满足第二范式；消除依赖的传递性，确保数据表的每一列都与主键直接相关，而不是间接相关；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
第一范式，属性列中不能有可以再分的列。
第二范式，主要是针对联合索引方面，非主键列不能只依赖于主键列的一部分。
第三范式，表中不能有循环依赖。
规范性和性能的问题
关联查询的表不得超过三张表
jdbc
数据库驱动
应用程序和数据库无法直接相连，则需要一些驱动来对数据库进行连接操作数据库
便于我们的管理，jdbc java炒作数据库的规范
java.sql
javax.sql
导包
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
语法基本固定
import java.sql.*;
//jdbc程序测试练习
public class DemoJDBC1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");//固定写法
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/school" +
"?useUnicode=true&characterEncoding=utf8&useSSL=true";//后面的是添加的参数，可更改，但多数情况下固定写法
String username = "root";
String password = "root";
//3、连接成功，数据库对象 Connection 连接数据库
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.print(resultSet.getObject("id") + "\t");
System.out.print(resultSet.getObject("name") + "\t");
System.out.print(resultSet.getObject("password") + "\t");
System.out.print(resultSet.getObject("email") + "\t");
System.out.println(resultSet.getObject("birthday"));
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
driver
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
static {
try {
//本质就是加载静态代码块，使用DriverManager.registerDriver(new Driver())来注册驱动
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
public Driver() throws SQLException {
// Required for Class.forName().newInstance()
}
}
-
加载驱动
-
//1、加载驱动 //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Class.forName("com.mysql.jdbc.Driver");//固定写法 //实际上就是使用Driver中的静态代码块来激活数据库驱动
-
-
用户信息和连接地址
-
//2、用户信息和url String url = "jdbc:mysql://localhost:3306/school" + "?useUnicode=true&characterEncoding=utf8&useSSL=true";//后面的是添加的参数，可更改，但多数情况下固定写法 String username = "root"; String password = "root"; //jdbc:mysql://地址:端口号/数据库 ? 参数1&参数2……
-
-
连接对象 connection 代表数据库，在数据库中能使用的再这对象中也可以使用
-
//3、连接成功，数据库对象 Connection 连接数据库 Connection connection = DriverManager.getConnection(url, username); connection.commit(); connection.rollback(); connection.setAutoCommit(true); connection.setAutoCommit(false);
-
-
Statement 执行sql的对象 PreparedStatement
String sql = "select * from users;";//编写sql
statement.executeQuery();//查询，返回ResultSet集
statement.execute();//执行任何sql
statement.executeUpdate();//执行更新，修改，删除，sql，返回一个受影响的行数
resultSet.next();//移动到下一行
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
抽象为工具类
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = root
package utils;
import com.mysql.jdbc.Driver;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
static String driver = null;
static String url = null;
static String username = null;
static String password = null;
static Connection connection = null;
static {
try {
InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
password = properties.getProperty("password");
username = properties.getProperty("username");
DriverManager.registerDriver(new Driver());
} catch (Exception throwables) {
throwables.printStackTrace();
}
}
//获得连接
public static Connection getConnection() throws SQLException {
connection = DriverManager.getConnection(url, username, password);
return connection;
}
//关闭资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoJDBC2 {
static Connection connection = null;
static Statement statement = null;
static ResultSet resultSet = null;
public static void main(String[] args) {
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.print(resultSet.getInt("id") + "\t");
System.out.print(resultSet.getString("name") + "\t");
System.out.print(resultSet.getString("password") + "\t");
System.out.print(resultSet.getString("email") + "\t");
System.out.println(resultSet.getDate("birthday"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeAll(connection, statement, resultSet);
}
}
}
PreparedStatement
可以防止sql注入，且效率更高
preparedStatement = connection.prepareStatement(sql);
package JDBCTest;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//模拟登录
public class LoginTest {
public static void main(String[] args) {
login("lishi","123456");
}
public static void login(String username,String password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from users where `name`=? and `password` = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getObject(1));
System.out.println(resultSet.getObject(2));
System.out.println(resultSet.getObject(3));
System.out.println(resultSet.getObject(4));
System.out.println(resultSet.getObject(5));
}
} catch (Exception throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeAll(connection,preparedStatement,resultSet);
}
}
}
package JDBCTest;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Date;
public class Test1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into users values (?,?,?,?,?);";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,5);
preparedStatement.setString(2,"sanba");
preparedStatement.setString(3,"123456");
preparedStatement.setString(4,"1272193546@qq.com");
preparedStatement.setDate(5, new Date(new java.util.Date().getTime()));//获得时间戳
int i = preparedStatement.executeUpdate();
if (i>0)
System.out.println(i);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeAll(conn,preparedStatement,null);
}
}
}//PreparedStatement可以预编译防止sql注入问题
数据库连接池
c3p0
配置文件
文件名必须叫 c3p0-config.xml
文件位置必须在src下
配置文件如下
<?xml version="1.0"encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名称</property>
<property name="user">root</property>
<property name="password">密码</property>
<property name="acquireIncrement">2</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</default-config>
ComboPooledDataSource dataSource=new ComboPooledDataSource(); Connection con=dataSource.getConnection();//直接得到该连接对象
@Testpublic void demo() throws PropertyVetoException, SQLException{
ComboPooledDataSource dataSource=new ComboPooledDataSource(); //设置四大参数的配置
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/数据库的名称");
dataSource.setUser("root");dataSource.setPassword("密码");//对缓冲池进行设置
// dataSource.setAcquireIncrement(6);
// dataSource.setInitialPoolSize(10);
// dataSource.setMinPoolSize(5);
// dataSource.setMaxPoolSize(40);
Connection con=dataSource.getConnection();//打印查看连接对象
System.out.println(con);//打印的结果为：com.mchange.v2.c3p0.impl.NewProxyConnection@5090d8eacon.close();
若在 < default-config >
后面加上
代码
ComboPooledDataSource dataSource=new ComboPooledDataSource(“ 数据厂商-config”);//得到配置
dbcp
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
