2020
|
04
|
25
项目开发规范
每一个表，都必须存在以下五个字段！
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
一、初识MySQL
javaEE：企业级java开发 web
前端(页面：展示，数据！)
后台(连接点：连接数据库 JDBC...，连接前端(控制，控制视图跳转，和给前端传递数据))
数据库(存数据，txt ，Excel ，world)
1.1、为什么学习数据库
1、岗位需求
2、现在的世界，大数据时代~，得数据库者得天下。
3、被迫需求：存数据
4、数据库是所有软件体系中最核心的存在 DBA：数据库管理员
1.2、什么是数据库
数据库(DB，DataBase)
概念：数据仓库，软件，安装在操作系统 ( Windows，Linux，Mac.... ) 之上！SQL，可以存储大量的数据。500万！
作用：存数据，管理数据。
1.3、数据库分类
关系型数据库：(SQL)
狭义：Excel(行、列)
- MySQL，Oracle，Sql Server，DB2，SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表...
非关系型数据库：(NoSQL)：Not Only
狭义：json，{key:value}
- Redis，MongoDB
- 非关系型数据库，对象存储，通过对象自身的属性来决定。
DBMS(数据库管理系统)
- 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
- MySQL，本质是数据库管理系统！
1.4、MySQL简介
MySQL是一个[关系型数据库管理系统]，由瑞典MySQL AB 公司开发，属于[Oracle] 旗下产品。MySQL 是最流行的[关系型数据库管理系统]之一，在 WEB 应用方面，MySQL是最好的 [RDBMS](Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问[数据库]的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是[开放源码]这一特点，一般中小型网站(或者大型网站，集群)的开发都选择 MySQL 作为网站数据库。
官网：https://www.mysql.com/
1.5、安装MySQL
安装建议：1.尽量不要使用.msi，会影响注册表，卸载麻烦；2.尽可能使用压缩包安装~
拓展：
sc delete MySQL80：清空MySQL80这个服务，之后即可重装
net start MySQL80：管理员cmd中，启动MySQL80这个服务
1.6、使用Navicat管理mysql
新建数据库：
新建表：
每个Navcat的执行操作，本质就是对应了一个sql语句
1.7、连接数据库
-- ：单行注释
/**/：多行注释
命令行连接：
mysql -uroot -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改用户密码flush privileges; -- 刷新权限-- ------------------------------------------------------ 所有的语句都使用 ; 结尾show databases; -- 查看所有的数据库mysql> use mysqlstudy; -- 切换数据库 use 数据库名Database changedshow tables; -- 查看当前数据库中所有的表describe student; -- 显示student表的所有信息exit; -- 退出连接
数据库的四种语言：
数据库 XX 语言
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
二、操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
mysql关键字不区分大小写
2.1、操作数据库(了解)
[ ] 中的内容可选
1、创建数据库
create database [if not exists] westos
2、删除数据库
drop database [if exists] westos
3、使用数据库
-- tab 键上面,若果表名或者字段名是一个特殊字符，就需要带 ``use `mysqlstudy`
4、查看所有的数据库
show databases
学习思路：
- 对照Navicat可视化历史记录查看SQL
- 固定的语法或关键字必须强行记住！
2.2、数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节 (常用)- bigint 较大的数据 8个字节
- float 单精度浮点数 4个字节
- double 双精度浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候，一般都是使用decimal
字符串
- char 字符串固定大小 0~255
varchar 可变长字符串 0~65535 常用的变量，对应java中的String- tinytext 微型文本 2^8 - 1
- text 文本串 2^16 - 1 保存大文本
char 和 varchar 的单位是字节，tinytext 和 text 的单位是比特，一比特 = 八个字节
时间日期
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来填充，例：int(3)，5 ---> 005
自增：
- 通常理解为自增，自动在上一条记录的基础上 + 1(默认)
- 通常用来设计唯一的主键~index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空： not null
- 假设设置为 not null ，如果不给它赋值，就会报错！
- NULL，如果不填写值，默认就是null！
默认：
- 设置默认的值
- sex，默认值为 男，如果不指定该列的值，就会默认为 男
2.4、问题拓展
int(n)，n指最大显示宽度为n
int最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关
说白了就是你设定最大宽度，插入的数据不够，就从填充0，给你填满
例如：我们指定的int(10)、int(11)是指定显示宽度，不是存储大小或者值的范围
看一个例子：int(3)的情况下 插入数字20，和数字2以及数字200，显示结果为：020；002；200；
char 和 varchar 则相反
2.4、创建数据库表
-- 目标：创建一个school数据库-- 创建学生表(列，字段) 使用SQL创建-- 学号int 登录密码 varchar(20) 姓名,性别,varchar(2),出生日期(datatime),家庭住址,email-- 注意点，使用英文(),表的名称和字段尽量使用 `` 括起来-- 字符串使用 单引号括起来-- 所有的语句后面加 ,(英文的),最后一个不用加-- auto_increment 自增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 '密码', `gender` 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-- 查看表的结构desc student/*================================================*/
格式：
create table [if not exists] `表名`( `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], ....... `字段名` 列类型 [属性] [索引] [注释])[表类型] [字符集设置] [注释]
常用命令：
-- 查看创建数据库的语句show create database school-- 查看表的定义语句show create table student-- 查看表的结构desc student
2.5、数据表的类型
engine=innodb default-- 关于数据库引擎/*innodb 现在默认使用~myisam 早些年使用的*/
innodb引擎在5.6.4版本提供了对全文索引的支持
myisam | innodb | |
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
| 支持 | 现在支持了 |
表空间的大小 | 较小 | 较大，约为2倍 |
常规使用操作：
- myisam 节约空间，速度较快
- innodb 安全性高，事务的处理多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在 data 目录下，一个文件夹就对应一个数据库
本质还是文件的存储！
数值数据库表的字符集编码
charset=utf8
不设置的话，会是MySQL默认的字符集编码Latin1，(不支持中文！)
2.6、修改删除表
修改表
-- 修改表名：alter table 旧表名 rename as 新表名alter table teacher rename as re_teacher-- 增加表的字段：alter table 表名 add 要添加的字段名 列属性alter table re_teacher add age int(3)-- 修改表的字段(重命名,修改约束)alter table re_teacher modify age varchar(11) -- 修改约束alter table re_teacher change age age1 int(1) -- 字段重命名-- 删除表的字段 ：alter table 表名 drop 字段名alter table re_teacher drop ag1
删除表
-- 删除表(如果表存在再删除)drop table if exists re_teacher
所有的创建和删除操作尽量加上判断，以免报错~
注意点：
- `` 所有的字段名，表名，使用这个包裹
- 注释 -- /**/
- sql 关键字大小写不敏感，建议用小写
- 所有的符号全部用英文！
三、MySQL数据管理
3.1、外键(了解)
方式一、在创建表的时候，增加约束(麻烦，复杂)
create table `grade`( `gradeid` int(10) not null auto_increment comment '年级id', `gradename` varchar(50) not null comment '年级名称', primary key(`gradeid`))engine=innodb default charset=utf8-- 学生表的 gradeid 字段要去用年级表的 gradeid-- 定义外键key-- 给这个外键添加约束(执行引用)references 引用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 '密码', `gender` varchar(2) not null default '女' comment '性别', `birthday` datetime default null comment '出生日期', `gradeid` int(10) not null comment '学生的年级', `address` varchar(100) default null comment '家庭住址', `email` varchar(50) default null comment '邮箱', primary key(`id`), key `fk_gradeid` (`gradeid`), constraint `gradeid` foreign key(`gradeid`) references `grade`(`gradeid`))engine=innodb default charset=utf8
删除有外键关系的表时，必须要先删除引用别人的表(这里的 student表)，再删除被引用的表(grade表)
创建表成功后，添加外键约束
create table `grade`( `gradeid` int(10) not null auto_increment comment '年级id', `gradename` varchar(50) not null comment '年级名称', primary key(`gradeid`))engine=innodb default charset=utf8create 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 '密码', `gender` varchar(2) not null default '女' comment '性别', `birthday` datetime default null comment '出生日期', `gradeid` int(10) not null comment '学生的年级', `address` varchar(100) default null comment '家庭住址', `email` varchar(50) default null comment '邮箱', primary key(`id`))engine=innodb default charset=utf8-- 创建表的时候没有指定外键关系，创建后通过修改添加外键alter table `student`add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
以上的操作都是物理外键，即数据库级别的外键，不建议使用！(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
- 我们想使用多张表的数据，想使用外键(用程序去实现)
很多人在工作中确实也不会使用外键。包括在阿里的JAVA规范中也有下面这一条
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
3.2、DML语言(※)
数据库存在的意义：数据存储，数据管理
DML语言：数据操作语言
- insert
- update
- delet
3.3、添加(插入)
insert
-- 插入语句(添加) insert into 表名([字段名1,字段名2,字段名3...]) values('值1'),('值2'),('值3')... 每个值中有对应多个字段的数据insert into `grade`(`gradename`) values('大一')-- 由于主键可以省略(如果不写表的字段,它就会一一匹配)-- 一般写插入语句,一定要数据和字段一一对应！-- 一个字段一次插入多个数据insert into `grade`(`gradename`)values('大二'),('大三')insert into `student`(`name`) values('lingStudy')insert into `student`(`name`,`pwd`,`gender`)VALUES('路之逸','0011642','男'), ('编程之外','0011zlf','男'), ('码云','450011642','男')
语法：
insert into 表名([字段名1,字段名2,字段名3...]) values('值1'),('值2'),('值3')... -- 每个值中有对应多个字段的数据
注意事项：
1.字段和字段直接使用英文逗号隔开
2.字段是可以省略的，但是后面的值必须要一一对应，不能少
insert into `student`values(5,'gpedit','testPassword','女','1999-01-01',1,'西安',)
3.可以同时插入多条数据，values 后面的值需要使用 , 隔开 values(),(),()...
3.4、修改
update
-- 语法：update 表名 set column_name=new_value,[column_name2=new_value,...] where 条件
-- 修改学生的名字,带了条件update `student` set `name`='苏逸' where id=5;-- 不指定条件的情况下,会改动所有的记录update `student` set `name`='宫本千夏'-- 修改多个属性,逗号隔开update `student` set `name`='宫本千夏',`' where id=5;
条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改...
操作符 | 含义 | 范围 | 结果 |
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
>= | |||
<= | |||
between 2 and 5 | 在2~5范围内 | [2,5] | 2,3,4,5 |
and | && 我和你 | 5>1 and 1>2 | false |
or | || 我或你 | 5>1 or 1>2 | true |
-- 通过多个条件定位数据,无上限update `student` set `name`='长江七号' where `name`='编程之外' and `gender`='女'
语法：update 表名 set column_name=new_value,[column_name2=new_value,...] where 条件
注意：
- colnum_name 是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- 多个设置的属性之间，使用英文逗号隔开
- value，是一个具体的值，也可以是一个
变量(current_time)
update `student` set `birthday`=current_time where `name`='长江七号' and `gender`='女'
3.5、删除
delete
语法：delete from 表名 [where 条件]
-- 删除数据(避免这样写，会全部删除)delete from `student`-- 删除指定数据delete from `student` where id=1;
truncate 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变！
-- 清空 student 表truncate `student`
delete 和 truncate 区别
- 相同点：都能删除数据，都不会删除表结构
- 不同：
- truncate 重新设置
自增列计数器会归零 - truncate 不会影响事务
-- 测试 delete 和 truncate 区别create table `test`( `id` int(4) not null auto_increment, `coll` varchar(10) not null, primary key(`id`))engine=innodb default charset=utf8insert into `test`(`coll`) values('1'),('2'),('3')delete from `test` -- 不会影响自增truncate table `test` -- 自增会归零
mysql基础第一部分先更这么多了，
