MySQL学习(一)-基本知识

目录

• 目标
• 简介
  • 什么是数据库？
  • 关系型数据库？
    • 关系模型
      • 定义
      • 基本概念
      • MySQL
    • Nosql
  • ORM
• 安装使用
  • 安装
  • 配置向导
  • 启动
  • 连接认证
  • SQL
    • 分类
    • 数据库操作
      • 创建数据库
      • 数据库查询
      • 数据库内容查看
      • 数据库删除
      • 数据库修改
      • 数据库重命名-没有命令直接操作
    • 表操作
      • 简介
      • 创建表
      • 查看表
      • 删除表
      • 修改表
    • 数据操作 - CRUD(create, Read, Update, Delete)
      • 创建数据（插入数据）
      • 获得数据（查询数据）
      • 修改数据
      • 删除数据
    • 字符集操作及校对规则
      • 字符集操作
      • 字符集的校对规则
    • 列类型
      • 数值类型
      • 日期型
      • 字符串型
        • Bindary/Varbinary/Blob
        • 总结

目标

• 如何使用Mysql
• 如何设计MySQL

简介

什么是数据库？

DB, DBMS, DBA

定义： 对大量信息进行高效管理的解决方案，按照一定数据结构进行数据的组织、存储和管理数据的库。

关系型数据库？

建立在关系模型上的数据库系统

关系模型

定义

• 数据结构可以规定，同类数据，结构一致，就是一个二维表格
• 数据之间的关系可以设置，实体之间可以联系

如日常生活中，学生管理系统，将实体（学生）和实体属性（学号、性别、姓名）保存在数据中，应该如何处理实体结构

基本概念

1. 数据库
   数据的仓库
2. 表
   数据保存在表内，同一个表内的数据，具有相同的数据格式
3. 行&列
   行用于记录数据
   列用于定义数据格式
4. 记录
   行里面的数据
5. 字段
   数据的某一个列
6. SQL
   数据库管理系统用于管理数据的语言，结构化查询语言

MySQL

1. 数据库
2. 如何向数据库系统发送指令

MySQL是基于C/S架构的。

Nosql

Not Only SQL, 非关系型数据库系统，如mongoDB，MemBase

ORM

面向对象模型的关系映射

安装使用

安装

1. 选择Custom

配置向导

启动

1. 启动和停止服务

net start service-name
net stop service-name

2. 直接使用mysqld.exe直接运行mysql程序

连接认证

C:\Users\GeneJiang>mysql -hlocalhost -P3306 -uroot -p

-h: 服务器名
-P: 端口号
-u：用户名
-p：密码

SQL

分类

• DML, 数据操作语言（操作数据）
  • 查询 DQL
  • 管理: 增、删、改 DML
• DDL, 数据定义语言（定义格式）
• DCL, 数据库控制语言（对数据库软件服务进行操作）

数据库操作

创建数据库

1. 语法

Create database db_name(数据库选项);

2. 几点说明

• 分号结束
• 命名规则
  • 见名知意
  • 使用下划线方式
  • 可以使用任意字符，但是一些特殊的，如纯数字、特殊符号, 如用特殊符号和关键字要加上 限定符：反引号 `
  • 关键字注意区分
  • 中文可以，但是不建议使用

3. 存储方式

在硬盘上，创建数据库就是创建一个目录，目录名就是数据库名。目录内存在一个db.opt文件，用于保存数据库属性选项信息。

4. Example

-- 创建数据库

CREATE DATABASE php_1;
CREATE DATABASE `1234`;
CREATE DATABASE `CREATE`;
CREATE DATABASE `传播智客`;

数据库查询

1.语法

SHOW DATABASES;

mysql也有用于维护自己的数据库

数据库内容查看

1.语法

SHOW CREATED db_name;

数据库删除

1. 语法

DROP DATABASE db_name;

数据库修改

1. 语法

ALTER DATABASE db_name [修改指令];

数据库重命名-没有命令直接操作

• 直接修改目录名
• 新建数据库，将数据库内容直接备份一份

表操作

简介

表是数据的容器，数据库是是表的容器，表必须属于一个数据库，进行表操作时候，必须要指定数据库

创建表

1. 语法

CREATE TABALE table_name(列结构) 表选项（）;

2. 说明

• 先分析实体的属性，这些属性如何保存
• 如班级：班级班号，班级的日期
• 列定义
  • 列名 数据类型（列的属性 约束）
  • 简单数据类型
    • vchar（字符数）
    • 时间类型 date
• 指定数据库
  • db_name.table_name();
  • use db_name;
• 特殊字符的数据库名，用``标识
• 同一个数据库中，如果要使用两个应用，建议用前缀进行区分。

3. example

CREATE TABLE python_class(
    class_no varchar(20),
    date_start date
);

查看表

1. 语法

SHOW TABLES table_name;
SHOW TABLES [like 'Pattern'];

SHOW CREATE TABLE table_name;
SHOW CREATE TABLE table_name\G

DESCRIBE table_name;
DESC table_name;

2. 说明

• 用pattern来快速的查找表
• 通配符
  • % - 任意字符的任意次数的组合
• 存储结构以*.frm关键字结束

3. example

CREATE TABLE info_student(
    name varchar(20),
    stu_no varchar(2)
);

CREATE TABLE exam_student(
    name varchar(20),
    stu_no varchar(2),
    score int
);

CREATE TABLE exam_question(
    content varchar(100), 
    answer varchar(20)
);

SHOW TABLE like 'exam_%';

-----------------------------------------------------------
-- Result
SHOW CREATE TABLE exam_student;
SHOW CREATE TABLE exam_student\G
------------------------------------------------------------

DESCRIBE table_name;
DESC table_name;

删除表

1. 语法

DROP TABLE [if exists] table_name;

2. 说明

• 如果没有表，可以用if exists进行判断
• 操作系统的安全限制，有时候不能删除，需要现在硬盘删除，然后使用命令进行删除
• 删除操作，一定要谨慎操作

修改表

1. 语法

-- 重命名表：修改表名,支持同时修改多个表，支持跨数据库重命名

RENAME TABLE old_tb_name to new_tb_name[, old_tb_name to new_tb_name];

--- 修改列的定义

ALTER TABLE table_name new_options;

-- 1. 增加一个列

ALTER TABLE tb_name ADD col_name col_type;
-- 2. 修改一个列
ALTER TABLE tb_name MODIFY col_name col_type;
-- 3. 删除一个列
ALTER TABLE tb_name DROP col_name;
-- 4. 重命名一个列
ALTER TABLE tb_name CHANGE old_col_name new_col_name;

--- 修改表的属性
ALTER TABLE tb_name CHARACTER SET gbk/utf8;

2. example

-- 表的列修改

-- 1.增加一个列
ALTER TABLE exam_student ADD height int；

-- 2. 删除一个列
ALTER TABLE exam_student DROP height;

-- 3. 修改一个列
ALTER TABLE exam_student MODIFY stu_no varchar(40);

-- 4. 重命名一个列
ALTER TABLE exam_student CHANGE score int_score int;

-- 表的属性修改

ALTER TABLE exam_student CHARACTER SET utf8;

数据操作 - CRUD(create, Read, Update, Delete)

创建数据（插入数据）

1. 语法

INSERT INTO tb_name( 字段列表）VALUES (值列表);

• 如果插入时，给所有字段值进行插入相应的值，可以省略字段列表。要求值得顺序应该与表中的字段的顺序一致。

2. Example

INSERT INTO exam_student (name, stu_no) VALUES ('jack', 1);

获得数据（查询数据）

1. 语法

SELECT 字段列表 FROM tb_name 查询条件;

• 字段列表，可以用*代替，表示所有列表;
• 查询条件可以省略，相当于WHERE 1。

2. Example

SELECT name, stu_no FROM exam_student WHERE 1;
SELECT * FROM exam_student WHERE 1;

修改数据

1. 语法

UPDATE tb_name SET 字段=新值,... 条件

2. Example

删除数据

1. 语法

DELETE FROM tb_name 条件

2. Example

DELETE FROM exam_student WHERE score <=90;

字符集操作及校对规则

字符集操作

1. 语法

SHOW VARIABLES LIKE 'character_%';

• 显示当前数据库服务器端和系统端的字符集格式

2. Example

mysql> SHOW VARIABLES LIKE 'character_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

字符集的校对规则

1. 简介

当前字符集内，字符之间的比较关系，如ASCI码的比较，97=>'a'
每个字符集都支持不定数量的校对规则。
2. 语法

SHOW COLLATION;
SHOW COLLATION LIKE 'characterset_zone_ci/cs/bin'

• 比较的规则
  • 字符集_地区名_比较规则
  • ci：不区分大小写
  • cs：区分大小写
  • bin：字节比较
• 校队规则根据默认的字符集进行变化，如果不设置校对规则，有默认的校对规则
• 影响字符的存储，是字符集；影响字符排序的，是校队规则

3. Example

• 显示校队规则

SHOW COLLATION LIKE 'gbk%';

• 校队规则具体体现

具体操作

-- 创建不同校队规则的表

CREATE TABLE tb_first(
    name varchar(10)
) CHARACTER SET gbk COLLATE gbk_chinese_ci;

CREATE TABLE tb_second(
    name varchar(10)
) CHARACTER SET gbk COLLATE gbk_bin;

-- 分别插入数据
INSERT INTO tb_first VALUES('a');
INSERT INTO tb_first VALUES('B');
INSERT INTO tb_first VALUES('C');

INSERT INTO tb_second VALUES('a');
INSERT INTO tb_second VALUES('B');
INSERT INTO tb_second VALUES('C');

-- 显示数据进行排序
SELECT * FROM tb_first ORDER BY name;
SELECT * FROM tb_second ORDER BY name;

结果

mysql> select * from tb_first order by name;
+------+
| name |
+------+
| a    |
| B    |
| C    |
+------+
3 rows in set (0.01 sec)

mysql> select * from tb_second order by name;
+------+
| name |
+------+
| B    |
| C    |
| a    |
+------+
3 rows in set (0.00 sec)

列类型

数值类型

1. 整数型

类型	字节	值范围
TINYINT	1	-128/127 or 0-255（无符号）
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8

• 使用unsigned控制正负
• 使用zerofil进行零填充, 类型(M), M表示字符显示的最小宽度
• 布尔型，就是tinyint(1)

CREATE TABLE tb_int(
    a TINYINT UNSIGNED, 
    b TINYINT
);

INSERT INTO tb_int VALUES (255, 127);
INSERT INTO tb_int VALUES (256, 128);

mysql> INSERT INTO tb_int VALUES (256, 128);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

ALTER TABLE tb_int add c TINYINT(3) ZEROFILL;

INSERT INTO tb_int VALUES (0, 111, 1);
INSERT INTO tb_int VALUES (0, 111, 11);
INSERT INTO tb_int VALUES (0, 111, 12);
INSERT INTO tb_int VALUES (0, 111, 113);

SELECT * FROM tb_int;

mysql> SELECT * FROM tb_int;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  255 |  127 | NULL |
|    0 |  111 |  001 |
|    0 |  111 |  011 |
|    0 |  111 |  012 |
|    0 |  111 |  113 |
+------+------+------+
5 rows in set (0.00 sec)

2. 小数型

• 简介

类型	字节
float	4
double	8
DECIMAL	(M,D),默认M是10, D是2

• 说明

  • 精度不同，范围不同
  • 单精度大概保留7位左右,双精度大概16位左右
  • Type(M,D)， M总共的位数，不包括符号和小数点；D表示小数位
  • 支持科学计数法
  • 支持zerofill和unsigned, 如 c DECIMAL(10,2) zerofill;

• Example

CREATE TABLE tb_float(
    a float, 
    b double
);

INSERT INTO tb_float VALUES (12345678.123, 1234567890.12345678);

SELECT * FROM tb_float;

结果1:

mysql> SELECT * FROM tb_float;
+----------+--------------------+
| a        | b                  |
+----------+--------------------+
| 12345700 | 1234567890.1234567 |
+----------+--------------------+
1 row in set (0.00 sec)

ALTER TABLE tb_float ADD c DECIMAL(10,2);

INSERT INTO tb_float VALUES(123, 123.44, 11111111.222);

SELECT * FROM tb_float;

结果2：

mysql> SELECT * FROM tb_float;
+----------+--------------------+-------------+
| a        | b                  | c           |
+----------+--------------------+-------------+
| 12345700 | 1234567890.1234567 |        NULL |
|      123 |             123.44 | 11111111.22 |
+----------+--------------------+-------------+
2 rows in set (0.00 sec)

日期型

• 年月日时分秒 DATETIME

  • 范围：'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
  • 可以是任意格式的字符
  • 如果出现歧义，不建议使用特殊的分隔符，会造成逻辑不清晰
  • 支持2位年份，1970-2069，不建议使用
  • 支持0年0月0日0时0分0秒，表示当前没有规定，2013-04-0表示4月整月（逻辑想法）

• 时间戳 TIMESTAMP

  • 存储是整型
  • 表示范围是：‘1970-01-01 00:00:00’到’2038-01-19 03:14:07‘
  • 检索是用+0，可以检索时间戳

Example：

CREATE TABLE tb_datetime(
    a DATETIME, 
    b TIMESTAMP
);

INSERT INTO tb_datetime VALUES ('2018-02-27 12:34:08', '2018-02-27 12:34:09');

SELECT * FROM tb_datetime;

结果：

mysql> SELECT * FROM tb_datetime;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2018-02-27 12:34:08 | 2018-02-27 12:34:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

• 年月日 DATE

  • 范围：'1000-01-01'到'9999-12-31
  • 3个字节

• TIME HH:MM:SS

  • 范围：-838:59:59'到'838:59:59'
  • 表示一天中的时间
  • 表示时间的间隔，可以用天来表示，格式：D HH:MM:SS

example:

-- time的格式

CREATE TABLE tb_datetime_1(
    ago time
);

INSERT INTO tb_datetime_1 VALUES ('23:12:12');
INSERT INTO tb_datetime_1 VALUES ('211212');
INSERT INTO tb_datetime_1 VALUES ('5 20:15:13');

结果：

mysql> SELECT * FROM tb_datetime_1;
+-----------+
| ago       |
+-----------+
| 23:12:12  |
| 21:12:12  |
| 140:15:13 |
+-----------+
3 rows in set (0.00 sec)

• YEAR
  • 范围：1901到2155

总结：

• php中，使用unix的整型时间戳来保存时间，不适用mysql中date的类型。

字符串型

• char(M)/varchar(M)

  • M 表示允许的字符串长度
  • char中M，表示严格规定的长度
  • varchar中M，是允许的最大长度
  • M表示的字符数，不是字节数
  • 注意：字段的最大长度，除了类型本身的限制，记录的总长度也有限制
  • 真实的varchar的长度：总的长度65535，当类型数据超过255个字符时，采用两个字节来表示长度，655355-2=65533，
  • 整条记录需要一个额外字节，来记录null值，除非所有的字段不是null值，则不需要额外的字节，来记录null值
  • 一个记录，如果有字段是非null，那就需要一个字节来存储null的值

  字符	Char(5)	VarChar(5)	解释
  ""	5个字节	1个字节
  'a'	5个字节	2个字节
  'ab'	5个字节	3个字节

• tinyText，text，mediumText，longtext

  • L + n。L为最大长度2^8+1, 2^16+2, 2^24+3, 2^32+4
  • 如何知道，字符串很大的时候，用text代替varchar

• enum 枚举

  • 用于单独的选项， 如gender enum('female', 'male')
  • 枚举是整型的一种
  • 最多65535个变量，2个字节

  example:

  -- enum type
  CREATE TABLE tb_enum(
      gender ENUM('female', 'male')
  );
  
  INSERT INTO tb_enum VALUES ('male');
  INSERT INTO tb_enum VALUES ('female');
  
  -- error
  INSERT INTO tb_enum VALUES ('other');
  
  SELECT gender FROM tb_enum;
  SELECT gender+0 FROM tb_enum;
  

• Set 集合

  • 用于不定项的设置
  • 元素数量：64， 1，2， 3， 4， 8， 最多保存65535元素。

Example：

    -- set type
    CREATE TABLE  tb_set(
        hobby SET('basket', 'football', 'pingpang')
    );
    
    INSERT INTO tb_set VALUES('basket');
    INSERT INTO tb_set VALUES('basket, football');

    mysql> SELECT hobby FROM tb_set;
    +--------+
    | hobby  |
    +--------+
    | basket |
    +--------+
    1 row in set (0.00 sec)
    
    mysql> SELECT hobby+0 FROM tb_set;
    +---------+
    | hobby+0 |
    +---------+
    |       1 |
    +---------+
    1 row in set (0.00 sec)

Bindary/Varbinary/Blob

在数据库中可以保存二进制，但是不会直接在数据库中直接保存图片

总结

常用类型：

1.int/tinyint
2. float/double/decimal
3. char/varchar/text
4. date