MySQL详解

MySQL

1.初识mysql

• JavaEE：企业级Java开发 Web
• 前端（页面：展示：数据）
• 后台 （连接点：连接数据库JDBC,连接前端（控制视图跳转，给前端传递数据））
• 数据库（存数据，Txt,Excel,Word）
  • 只会写代码，学好数据库，基本混饭吃！
  • 操作系统，数据结构与算法！当一个不错的程序猿！
  • 离散数学，数字电路，体系结构，编译原理。+实战经验，优秀程序猿

1.1 为什么学数据库

• 岗位需求
• 现在的世界，大数据时代，得数据者得天下
• 被迫需求：存数据
• 数据库是所有软件体系中最核心的存在 DBA

1.2 什么是数据库

• 数据库：(DB,DataBase)
  • 概念:数据仓库，软件，安装在操作系统之（windows,Linux。mac）上的！SQL,可以存储大量的数据，500万!
  • 作用:存储数据，管理数据 Excel

1.3 数据库分类

• 关系型数据库：(SQL)
  • MySQL, Oracle, sql Server, DB2, SQLite
  • 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表，······

• 非关系型数据库：(NoSQL) Not Only
  • Redis, MongDB
  • 非关系型数据库，对象存储，通过对象自身的属性来决定。

• DBMS(数据库管理系统)
  • 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据；
  • MySQL ，数据管理系统！

---

1.4 MySQL简介

• MySQL是一个关系型数据库管理系统。
• 发展史：
  • 前世： 瑞典MySQL AB 公司
  • 今身： 属于 Oracle 旗下产品
• MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

  • 开源的数据库软件

  • 体积小，速度快，总体拥有成本低，招人成本比较低，所有人必须会～。

  • 中小型网站，或者大型网站，集群

  • 官网： https://www.mysql.com/

1.5 MySQL基本的命令行操作

mysql -uroot -p123456 -- 连接数据库

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

flush privileges; -- 刷新权限
----------------------------
-- 所有的语句都使用;结尾

show databases; -- 查看所有的数据库

mysql> use school; -- 切换数据库  use 数据库名
Database changed

mysql> show tables; -- 显示数据库中所有表的信息

mysql> describe student; -- 显示表的详细信息

create database zyy;  -- 创建一个数据库（这里过于简洁，后面详细介绍）

exit -- 退出连接

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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

---

2.操作数据库

2.1 MySQL结构化查询语句分类

2.2 操作数据库语句

• 操作数据库 》 操作数据库中表 》操作数据库中表的数据
• mysql关键字不区分大小写

• 1.创建数据库

CREATE DATABASE [IF NOT EXISTS] student;-- create database [if not exists] 数据库名;
1

• 2.删除数据库

DROP DATABASE [IF EXISTS] westos;-- drop database [if exists] 数据库名;
1

• 3.使用数据库

-- tab 键上面，如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`--use 数据库名;
1
2

• 4.查看数据库

SHOW DATABASES; -- 查看所有的数据库
1

---

2.3 列的数据类型讲解

数值
1

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节

• mediumint 中等大小的数据 3个字节

• int 标准的整数 4个字节

• bigint 较大的数据 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节

• decimal 字符串形式的浮点数 （金融计算的时候，一般是使用decimal）

字符串
1

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535 （常量的变量 String）

• tinytext 微型文本 2^8 -1

• text 文件串 2^16 -1 (保存大文本)

时间日期 java.util.Date
1

• date YYYY-MM-DD 日期格式

• time HH:mm:ss 时间格式

• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

• timestamp 时间戳，1970.1.1到现在的毫秒数！较为常用！

• year 年份表示

null
1

• 没有值，未知
• 注意：不要使用NULL进行运算，结果为NULL

2.4 数据库的字段属性

• Unsigned:

  • 无符号的整数
  • 声明了该列不能声明为负数

• Zerofill:

  • 0填充的
  • 不足的位数，使用0来填充 int(3), 5 — 005

• 自增：

  • 通用理解为自增，自动在上一条记录的基础上+1（默认）
  • 通常用来设计唯一的主键，index，必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

• 非空 null/not null：

  • not null，如果不给他赋值，就会报错
  • null，如果不给他赋值，默认就是null

• 默认：

  • 设置默认的值
  • 如果不赋值，就会存默认值

• 拓展：

/*
每个表，都必须存在以下五个字段  未来做项目用的，表示一个记录存在的意义

id          主键
version     乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间

*/

1
2
3
4
5
6
7
8
9
10
11

2.5 创建数据库表

-- 目标：创建一个school数据库
-- 创建student学生表，使用sql创建
-- 学号 姓名 性别 出生日期 家庭地址  email

-- 注意点：使用英文()  表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- COMMENT 属性注释
-- DEFAULT 默认值
-- 字符串使用单引号括起来
-- 所有的语句后面加上英文逗号，最后一个不加
-- PRIMARY KEY主键，一个表一般只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 查看建立数据库的语句
SHOW CREATE DATABASE SCHOOL
DESC STUDENT --显示表的结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

• 格式

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

1
2
3
4
5
6
7

• 常用命令：

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

1
2
3
4

2.6 MyIASM和InnoDB区别

---

• 常规使用操作：

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

• 在物理空间存在的位置

  • 所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
  • 本质还是文件的存储！

• mysql引擎在物理文件上的区别

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

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

  • CHARSET=utf8
  • 不设置的话，会是mysql默认的字符集编码（不支持中文）,mysql的默认编码是Latin1,不支持中文
  • 在my.ini中配置默认的编码

       character-set-server=utf8
1

2.7 修改和删除数据表字段

• 修改

-- 修改表名.  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段.  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段 （重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 字段名 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age  age1 INT(1)-- 字段重命名

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 删除

-- 删除表（如果存在再删除）
DROP TABLE IF EXISTS teacher1
1
2

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

• 注意点：

  • `` 字段名，使用这个包裹!
  • 注释：单行注释 # 注释内容  多行注释 / 注释内容 /  单行注释 – 注释内容
  • sql 关键字大小写不敏感，建议写小写。
  • 所有的符号全部用英文