MySQL数据库基础

MySQL

1、初识MySQL

JavaEE: 企业级Java开发,Web

前端(页面:展示)

后台(连接点:连接数据库JDBC,链接前端(控制,控制视图跳转和给前端传递数据))

数据库(存数据,Txt,Excel,Word)

只会写代码,学好数据库,基本混饭吃

操作系统,数据结构与算法,当一个不错的程序员

离散数学,数字电路,体系结构,编译原理+实战经验 高级程序员

1.1、为什么学数据库

1. 趋势,岗位需求
2. 现在的世界,大数据时代,得数据库者得天下
3. 被迫需求:存数据
4. 数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

数据库(DB,DataBase)

概念:数据仓库,一种软件,安装在操作系统(Windows.Linux,Mac…)之上,SQL,可以存储大量的数据

作用:存储数据,管理数据

1.3、数据库分类

关系型数据库:

MySQL,Oracle,Sql Server,DB2,SQLlite

通过表和表之间,行和列之间的关系进行数据的存储

非关系型数据库:

Redis,MongDB

对象存储,通过对象的自身的属性来决定

DBMS(数据库管理系统)

• 数据库的管理软件,科学有效的管理,维护和获取数据
• MySQL,数据库管理系统

1.4、MySQL简介

MySQL是一个关系型数据库管理系统,是最流行的关系型数据库管理系统之一,也是最好的关系型数据库管理系统之一

优点:体积小,速度快,总体拥有成本低,开源,适用于中小型网站,或者大型网站,集群

安装建议:

1. 尽量不要使用exe安装,卸载麻烦
2. 尽可能使用压缩包安装

1.5、安装MySQL

网上教程很多,这里不再阐述

1.6安装SQLyog

同1.5

---

创建数据库时选项:

引擎:InnoDB

字符集:utf8

核对:utf8_general_ci

1.7、连接数据库

1.7.1 命令行连接

--连接数据库
mysql -uroot -p密码 

--修改用户root的密码为123456
update mysql.user set authentication_string=password('123456') where user='root' and Host= 'localhost'; 

--刷新权限
flush privileges;

---------------------------------------------------------------------

--mysql里所有语句都必须使用;结尾
show databases; --查看所有的数据库

mysql> use数据库名; -- 使用某个数据库(切换到某个数据库)
Database changed --回复此信息表示切换成功

show tables; --查看当前使用的数据库中的所有表
mysql> describe 表名; --显示当前表名表的信息

create database 数据库名; --创建一个名为数据库名的数据库

exit; --退出连接

--单行注释(--)
/*
多行注释
*/

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不区分大小写

2.1、操作数据库

1. 创建数据库

-- []里的内容表示可选内容,可写可不写
/*创建一个名为westos的数据库[如果不存在此数据库的话] 
使用utf8基字符集,utf8_general_ci数据库排列规则*/
CREATE DATABASE [IF NOT EXISTS] westos CHARACTER SET utf8 COLLATE utf8_general_ci; 

2. 删除数据库

/*删除名为westos的数据库[如果存在此数据库的话]*/
DROP DATABASE [IF EXISTS] westos; 

3. 使用数据库

/*使用数据库school (`键在tab键的上面,不是单引号)*/
USE `school`; 

4. 查看数据库

/*查看所有的数据库*/
SHOW DATABASES; 

2.2、数据库的列类型

数值

数据类型	表示的数据	备注
tinyint	非常小的整数	一个字节
smallint	较小的整数	两个字节
mediumint	中等大小的整数	三个字节
int	标准的整数	四个字节 (常用)
bigint	较大的整数	八个字节
float	浮点数	四个字节
double	浮点数	八个字节(精度问题)
fecimal	字符串形式的浮点数	金融计算一般使用此类型

字符串

数据类型	表示的数据	备注
char	字符串	大小:0-255
varchar	可变字符串	大小:0-65535 (常用)
tinytext	微型文本	大小:0-(2^8-1)
text	文本串	大小:0-(2^16-1)(保存大文本)

时间日期

数据类型	表示的数据	备注
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)

自增(AUTO_INCREMENT)

• 通常理解为自增,自动在上一条记录的基础上+1,(默认)
• 通常用来设计唯一的主键~index,必须是整数类型
• 可以自定义设计主键的起始值和步长(步长:每次增加的数量)

非空(NOT NULL)

• 假设某列声明为非空,那么在插入数据时,此列必须要有数据,否则会报错
• 在不声明非空的情况下,无数据填入时,默认值为null

默认

• 设置默认的值
• 比如在sex列设置默认,值为男,那么如果在插入数据时没有指定该列的值,则会自动填写此列的值为男

---

拓展:

/*每一个表都必须存在以下五个字段
做项目时使用的
id				主键
`version`		乐观锁
is_delete		伪删除
gmt_create		创建时间
gmt_update		修改时间
*/

---

2.4、创建数据库表

/*
表名 字段名尽量使用``(不是单引号,是tab键上方的那个键,就是~那个键,这个符号交反引号)括起来
注意:INT(4) 这个数据类型后面的括号里的数字表示在表中显示的位数,此意为int类型的数据,在表中显示4位
注意:默认值,备注等字符串是要用引号括起来,最好使用单引号
注意:在创建表的()里,每个字段声明结束都要加上,(英文逗号),最后一条除外
注意:在下面的代码中,在建表()里的最后一行声明了主键,但是还有另外一种方法声明,即直接把PRIMARY KEY写在要声明为主键的字段里,一般建议把主键写在最后
如果不存在(不存在名为student的表)的话 创建一个名为student的表(
创建一个字段名为id 格式为int(4) 非空(NOT	NULL) 自增(AUTO_INCREMENT) 备注(COMMENT)为'学号',
创建一个字段名为name 格式为VARCHAR(30) 非空 默认值(DEFAULT)为'匿名' 备注为'姓名',
创建一个字段名为pwd 格式为VARCHAR(20) 非空 默认值为'123456' 备注为'密码',
创建一个字段名为sex 格式为VARCHAR(2) 非空 默认值为'女' 备注为'性别',
创建一个字段名为birthday 格式为DATETIME  默认为空(DEFAULT NULL) 备注为'出生日期',
创建一个字段名为address 格式为VARCHAR(100)  默认为空 备注为'家庭住址',
创建一个字段名为email 格式为VARCHAR(50) 默认为空 备注为'邮箱',
主键为id
)引擎=INNODB 默认字符编码为utf8
*/
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 '性别',
	`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

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] ... [注释],
    `字段名` 列类型 [属性] [索引] ... [注释],
    ...
    `字段名` 列类型 [属性] [索引] ... [注释]
)[表类型] [字符集设置] [表注释]

常用命令

SHOW CREATE DATABASE school; -- 查看创建数据库school的语句
SHOW CREATE TABLE student; -- 查看创建表student的语句
DESC student; -- 显示表student的结构

2.5、数据表的类型(引擎)

/*
INNODB 默认使用的
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大,约为前者的两倍

各自优点:

• INNODB:安全性高,事务的处理,多表多用户操作
• MYISAM:节约空间,速度较快

在物理空间存在的位置

​ 所有的数据库文件都存在与data目录下,一个文件夹对应一个数据库

​ 本质还是文件的存储

MySQL引擎在物理文件上的区别

• INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
• MYISAM对应文件:
  • *.frm 表结构定义文件
  • *.MYD 数据文件(data)
  • *.MYI 索引文件(index)

设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是MySQL默认的字符集编码

MySQL的默认编码是Latin1,不支持中文

或者可以更改MySQL的默认编码:

- 在my.ini中配置默认编码(不建议使用,因为在你的电脑上配置了默认utf8,但是代码换到别人电脑上,别人不一定配置了默认utf8,就有可能会出错):

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名:  ALTER TABLE 旧表名 RENAME AS 新表名;
-- 将表teacher重命名为teacher1
ALTER TABLE teacher RENAME AS teacher1;
-- 增加表的字段: ALTER TABLE 目标表名 ADD 增加的字段名 列属性 ;
-- 在表teacher1中增加一个字段age age的字段类型为INT(11)
ALTER TABLE teacher1 ADD age INT(11);
-- 修改表的字段约束: ALTER TABLE 目标表名 MODIFY 目标字段名 新字段类型和约束 ;
-- 将表teacher1中的age字段约束由INT(11)改为VARBINARY(11)
ALTER TABLE teacheri MODIFY age VARBINARY(11); 
-- 修改表的字段名: ALTER TABLE 目标表名 CHANGE 原字段名 新字段名;
-- 将表teacher1中的age字段名改为age1
ALTER TABLE teacher1 CHANGE age age1;

删除

-- 删除表的字段: ALTER TABLE 目标表名 DROP 要删除的字段名;
-- 在表teacher1中删除字段age1
ALTER TABLE teacher1 DROP age1;
-- 删除表: DROP TABLE [IF EXISTS] 要删除的表名;
-- 删除表[如果这个表存在]teacher1
DROP TABLE [IF EXISTS] teacher1;

所有的创建和删除操作尽量加上判断,以免报错

3、MySQL的数据管理

3.1、外键

/*
ALTER TABLE `绑定的表名`
ADD CONSTRAINT `FK_绑定的字段名` FOREIGN KEY(`绑定的字段名`) REFERENCES `被绑定的表名`(`被绑定的字段名`);
想要绑定的两个字段都必须存在索引
*/
-- 将表student中的gradeid字段作为外键与grade表中的gradeid绑定
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

不建议使用外键与级联,一切外键概念最好都在应用层解决

如果使用外键的话,会导致每次做DELETE或者我UPDATE时都必须考虑外键约束,会导致开发的时候很痛苦,测试数据极为不方便

3.2、DML语言

数据库意义:数据存储,数据管理

DML语言:数据操作语言

• Insert(添加)
• update(修改)
• delete(删除)

3.3、添加

/* 插入语句（添加）
 INSERT INTO `要添加的表名`(`要添加的字段1`,`要添加的字段2`) VALUES ('添加的字段1的值','添加的字段二的值');
 给student表中的name和pwd两个字段添加数据,添加的数据为'小红','123456';*/
INSERT INTO `student`(`name`,`pwd`) VALUES ('小红','123456');

/* 插入语句（添加）（插入多行）
 INSERT INTO `要添加的表名`(`要添加的字段`) VALUES ('添加的第一行的值'),('添加的第二行的值');*/
INSERT INTO `student`(`name`) VALUES ('小明'),('小黑');
/*INSERT INTO `要添加的表名`(`要添加的字段1`,`要添加的字段二`) 
VALUES ('添加第一行的字段一的值','添加第一行的字段二的值'),('添加第二行的字段一的值','添加第二行的字段二的值');*/
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('一号','aaaaaa','男'),('二号','bbbbbb','男');
/*可以省略字段名,但是省略字段名之后,后面的字段值必须和表内所有字段一一对应
INSERT INTO `要添加的表名`
VALUES ('字段一的值','字段二的值','字段三的值','字段四的值','字段无的值','字段六的值','字段七的值');
即: 如果省略表名,则表内有多少个字段,就要写多少个数据,而且数据类型要与相应的字段相匹配*/
INSERT INTO `student`
VALUES ('7','三号','333333','女','2000-01-01','1','asdasd');

以上内容的添加结果如下:

3.4、修改

/*如果"判断条件"就把表"要修改的表名"中的"要修改的字段"修改为"修改后的值"
如果不加判断条件,则会把该表中的所有该字段的值都改为该值
UPDATE `要修改的表名`
SET `要修改的字段`='修改后的值'
WHERE 判断条件;*/
UPDATE `student`
SET `name`='修改后的一号'
WHERE id = 4;

/*修改多个字段
UPDATE `要修改的表名`
SET `要修改的字段1`='修改后的值1',`要修改的字段2`='修改后的值2'
WHERE 判断条件;*/
UPDATE `student`
SET `name`='修改后的二号',`pwd`='xghdeh'

/*执行下面语句后 会将id为6的行的name也改为修改后的二号,pwd改为xghd,此步骤为验证下一步做准备*/
WHERE id = 5;
UPDATE `student`
SET `name`='修改后的二号',`pwd`='xghd'
WHERE id = 6;

/*使用 AND 连接多个判断
下列语句只有在`name`='修改后的二号'和 `pwd`='xghdeh'这两个条件同时成立的时候才会修改*/
UPDATE `student`
SET `name`='再次修改',`pwd`='987654'
WHERE `name`='修改后的二号'AND `pwd`='xghdeh';

以上内容的修改结果如下:

条件:where子句 运算符:

操作符会返回一个布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6或5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	ftrue
BETWEEN…AND…	在某个范围内	BETWEEN 5 AND 6	在5到6之间
AND	与	5>1(条件1) AND 5>4(条件2)	true(条件1与条件2都为true)
OR	或	5>1(条件1) OR 5>6(条件2)	true(条件1或条件2为true)

AND 和 OR 两个运算符,理论上可以连接无限个判断条件

3.5、删除

delete命令

/* 删除指定数据
每次删除都是一行
删除`student`表中`id`='1' 或 `id`='2' 或 `pwd`='aaaaaa'的数据*/
DELETE FROM `student` WHERE `id`='1' OR `id`='2' OR `pwd`='aaaaaa';

以上内容的删除结果如下:

truncate命令

作用: 完全清空一个数据库表,表的结构和索引约束不会变

-- 清空表
TRUNCATE `student`;

以上内容的清空结果如下:

delete与truncate的比较

• 相同点:都能删除数据,都不会删除表结构
• 不同:
  • truncate 会重新设置自增列,计数器会归零
  • truncate 不会影响事务

-- 1
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('一号','111111','男');

--2
DELETE FROM `student`;
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('二号','222222','男');

--3
TRUNCATE `student`;
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('三号','333333','男');

比较delete与truncate的不同,执行上面的代码,首先执行第一部分,向student表中填入一条数据,此时表中的自增项(id)为1,然后执行第二条,使用delete删除表中所有数据,而后再插入一条数据,此时可以看到,表中只有一条数据,而它的自增项为2,然后执行第三步,使用truncate删除表之后再插入一条数据,我们可以看到此时插入数据的自增项为1,也就是说delete删除表之后,表单自增项不会清零重新开始,而truncate则会使自增项清零重新开始

了解:delete删除的问题

引擎不同,效果不同

• InnoDB 重启数据库后自增会从一开始重新计数(数据存在内存中,断电即失)
• MyISAM 重启数据库后自增还是会继续从上一个自增量开始(数据存在文件中,不会丢失)

4、DQL查询数据

4.1、DQL

Data Query LANGUAGE:数据查询语言

• 所有的查询操作都用它 Select
• 简单的查询,复杂的查询都能做
• 数据库中最核心的语言,最重要的语句
• 使用频率最高的语句

4.2、查询指定字段

-- 清空表
TRUNCATE `student`;

-- 向表中加入数据
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('一号','111111',