2. 操作数据库
2.1 操作数据库
2.2 数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小 3个字节
- int 标准的整数 4个字节 常用
- bigint 较大的数据
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65535 常用 String
- tinytext 微型文本 2^8 -1
- text 文本串 2^16 -1
时间日期
java.util.Date
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 常用时间
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year
null
- 没有值
- 不要使用null进行计算, 结果为null
2.3 数据库的字段属性
unsigned:
-
无符号的整数
-
不能声明为负数
zerofill:
- 0填充
- 不足为数,使用0来填充
自增
- 自动在上一条记录上+1
- 设计唯一的主键
非空
- 如果不填,会报错
默认
- 如果不填,会填默认值
非空
id
`version`
is_delete
gmt_update
2.4 创建数据库
CREATE TABLE
IF
NOT EXISTS `student2` (
`id` INT ( 4 ) NOT NULL auto_increment COMMENT '学号',
`name` VARCHAR ( 30 ) DEFAULT '匿名' COMMENT '姓名' ,
`pwd` varchar(20) not null DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) not NULL DEFAULT '女' comment '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`address` varchar(100) default NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
) ENGINE=INNODB default charset=utf8
查看
show create database school -- 查看数据库创建的语句
show create table student -- 查看student表的创建语句
desc student -- 显示表的结构
数据库引擎
InnoDB 默认使用
Myisam 早些年使用的
| myisam | innodb | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作
-
myisam: 节约空间,速度较快
-
innodb: 安全性高, 事务的处理, 多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下 本质还是文件的存储, 一个文件夹对应一个数据库.
mysql引擎在物理文件上的区别
- innoDB在数据库表中只有一个*.frm, 以及上一目录中的ibdata1文件
- myisam对应文件
- *.frm表结构的定义文件
- *.myd 数据文件
- *.myi 索引文件(index)
设置数据库表的字符集编码
charset=utf8
不设置的话会是mysql 默认的字符集编码(不支持中文)
mysql的默认编码是Latin1, 不支持中文
可以在my.ini总配置默认的编码: character-set-server=utf8
2.6 修改删除表
修改
-- 修改表名
alter table teacher rename as teacher1;
-- 增加表的字段
alter table teacher1 add age int(10);
-- 修改表的字段(重命名)
alter table teacher1 modify age varchar(11); -- 修改约束
alter table teacher1 change age age1 int(1); -- 字段重命名
--删除表的字段
alter table teacher1 drop age1;
删除
-- 删除表
drop table if exists teacher1;
所有的创建和删除操作尽量加上判断, 以面报错
注意点
- ` 反引号 字段名, 使用这个包裹
- 注释 – /**/
- sql关键字大小写不敏感, 建议写小写
- 所有的符号全部用英文
3 mysql数据管理
3.1 外键
方式一 创建表的时候创建外键
-- 创建外键需要的表
CREATE TABLE `grade` (
`gradeid` int(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
`name` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建外键
CREATE TABLE
IF NOT EXISTS `student2` (
`id` INT ( 4 ) NOT NULL auto_increment COMMENT '学号',
`name` VARCHAR ( 30 ) DEFAULT '匿名' COMMENT '姓名' ,
`pwd` varchar(20) not null DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) not NULL DEFAULT '女' comment '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`gradeid` int(4) not null comment '学生年级',
`address` varchar(100) 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`)
以上操作都是物理外键, 数据库级别的外键属性, 我们不建议使用( 避免数据库过多造成困扰)
最佳实践:
- 数据库就是单纯的表, 只用来存数据, 只有行(数据) 和列(字段)
- 我们想使用多张表的数据, 想使用外键(程序去实现)
3.2 DML语言 (增删改查)
DML语言: 数据库操作语言
-
insert
-
update
-
delete
3.3 添加
insert
insert into student
字段和字段之间使用英文逗号隔开
字段是可以省略的, 但是后面值必须一一对应
可以同时插入多条数据, values 后面的值需要用逗号分开 values( ),( ),( )
3.4 修改
update
update 表名 set column_name = 新的值 where 条件
修改多个属性, 逗号隔开.
条件: where子句 运算符 id等于某个值, 大于某个值, 在某个区间内修改
注意:
-
column_name是数据库的列, 尽量戴上``
-
条件, 筛选的条件, 如果没有指定 ,则会修改所有的列
-
value, 是一个具体的值, 也可以是一个变量current_timestamp
-
多个设置的属性之间,使用英文逗号隔开
3.5 删除
delete
delete from 表名 where 条件
truncate 命令
作用;完全清空一个数据库的表, 表的结构和索引约束不会变
deleete 和truncate的区别
-
相同点: 都能删除数据, 都不会删除表结构
-
不同点:
- truncate 重新设置自增列, 计数器会归零
- truncate 不会影响事务
delete删除的问题: 重启数据库
- innoDB 自增列会从1开始(存在内存中, 断电及失)
- MyISAM 继续从上一个自增列开始(存在文件中, 不会丢失)
4 DQL 查询数据
4.1 DQL
DQL : date query language
- 查询都用他
- 简单查询, 负责查询都能做
- 数据库中的核心语言, 最重要的语句
select 语法
select [ALL| DISTINCT]
{* | TABLE.* | ...}
FROM TABLE_NAME [AS ALIAS]
[LEFT| RIGHT| INNER JOIN TABLE_NAME2 ON]
[WHERE ...] -- 指定结果按需满足的条件
[GROUP BY...] -- 指定结果按哪几个字段分组
[HAVING...] -- 过滤分组记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按条件排序
[LIMIT {[OFFSET, ]ROW_COUNT | ROW_COUNTOFFSET OFFSET }]
4.2 指定查询字段
select 字段 from 表
--
select * from student;
-- 别名
select name as a from student as s;
-- 拼接字段
select concat(id,name) from test;
去重 distinct
select distinct name from student;
去除select 查询出来的结果中重复的数据
数据库的列(表达式)
select version();
select score+1 as 新成绩 from result;
4.3 where子句
作用: 检索数据中符合条件的值
模糊查询:比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 操作符为空,结果为真 |
| is not null | a is 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之中, 结果为真 |
-- 查询所有姓刘的同学
select * from student
where name like '刘%';
-- 查询姓刘的同学, 名字后面只有一个字
select * from student
where name like '刘_';
--- in 只能用具体的值--
select *from student
where studentNo in (1001, 1002, 1003);
4.4 连表查询
join 对比
left join 左连接: 查询两个表共有+左边独有的
right join : 查询两个表共有+右边独有的
inner join: 查询两个表共有+左边单独有+右边单独有的数据
自连接
自己的表和自己的表连接, 核心, 一张表拆成两张一样的表使用
4.5 分页和排序
排序
分页
-- 排序
-- 升序 asc
--分页
-- 语法: limit 起始位置, 页面的大小
-- limit 0, 5 显示的1~5
-- limit 1,5 现实的2~6
--- limit 6, 5 前面基础上显示第二页了
select s.studentNo, studentName, studentResult
from student s
inner join result r
on s.studentNo = r.studentNo
where subjectName='数据库-1'
order by studentResult ASC
limit 0,5;
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit (n-1)*pageSize , pageSize
-- [pageSize: 页面大小]
-- [(n-1)*pageSize: 起始页]
-- [n:当前页]
-- [数据总数/页面大小: 总页数]
语法: limit(起始下标, pageSize)
4.6 子查询/ 嵌套查询
本质: 在 where 语句中嵌套一个子查询语句
select * from result
where subjectNo = (select subjectNo from subject
where subjectName ='数据库');
5 MySQL函数
5.1 常用函数
--数学运算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回一个0~1之间随机数
-- 字符串函数
select char_length('aaa') -- 字符串长度
select concat('w','af') -- 拼接
select insert('abcdefg',1,2 ,'xxx') -- 替换第1个开始的2个字符为xxx
select lower('') -- 小写字母
select instr('hello','e') -- 返回字符第一次出现的索引
select replace()
select substr()
-- 时间和日期函数
select current_date() -- 获取当前日期
select curdate() -- 获取当前日期
select now() -- 获取当前时间
select localtime() -- 获取本地时间
5.2 聚合函数
| 函数名 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
5.3 数据库级别的MD5加密
什么是MD5, 主要增强算法复杂度和不可逆性.
MD5不可逆, 具体的值的md5值是一样的.
MD5破解网站的原理,背后有一个字典
select md5(str)
扫一扫
1562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
