目标
如何使用Mysql
如何设计MySQL
简介
什么是数据库？
DB, DBMS, DBA
定义： 对大量信息进行高效管理的解决方案，按照一定数据结构进行数据的组织、存储和管理数据的库。
关系型数据库？
建立在关系模型上的数据库系统
关系模型
定义
数据结构可以规定，同类数据，结构一致，就是一个二维表格
数据之间的关系可以设置，实体之间可以联系
如日常生活中，学生管理系统，将实体(学生)和实体属性(学号、性别、姓名)保存在数据中，应该如何处理实体结构
基本概念
数据库
数据的仓库
表
数据保存在表内，同一个表内的数据，具有相同的数据格式
行&列
行用于记录数据
列用于定义数据格式
记录
行里面的数据
字段
数据的某一个列
SQL
数据库管理系统用于管理数据的语言，结构化查询语言
MySQL
数据库
如何向数据库系统发送指令
MySQL是基于C/S架构的。
Nosql
Not Only SQL, 非关系型数据库系统，如mongoDB，MemBase
ORM
面向对象模型的关系映射
安装使用
安装
选择Custom
配置向导
启动
启动和停止服务
net start service-name
net stop service-name
直接使用mysqld.exe直接运行mysql程序
连接认证
C:\Users\GeneJiang>mysql -hlocalhost -P3306 -uroot -p
-h: 服务器名
-P: 端口号
-u：用户名
-p：密码
SQL
分类
DML, 数据操作语言(操作数据)
查询 DQL
管理: 增、删、改 DML
DDL, 数据定义语言(定义格式)
DCL, 数据库控制语言(对数据库软件服务进行操作)
数据库操作
创建数据库
语法
Create database db_name(数据库选项);
几点说明
分号结束
命名规则
见名知意
使用下划线方式
可以使用任意字符，但是一些特殊的，如纯数字、特殊符号, 如用特殊符号和关键字要加上 限定符：反引号 `
关键字注意区分
中文可以，但是不建议使用
存储方式
在硬盘上，创建数据库就是创建一个目录，目录名就是数据库名。目录内存在一个db.opt文件，用于保存数据库属性选项信息。
Example
-- 创建数据库
CREATE DATABASE php_1;
CREATE DATABASE `1234`;
CREATE DATABASE `CREATE`;
CREATE DATABASE `传播智客`;
数据库查询
1.语法
SHOW DATABASES;
mysql也有用于维护自己的数据库
数据库内容查看
1.语法
SHOW CREATED db_name;
数据库删除
语法
DROP DATABASE db_name;
数据库修改
语法
ALTER DATABASE db_name [修改指令];
数据库重命名-没有命令直接操作
直接修改目录名
新建数据库，将数据库内容直接备份一份
表操作
简介
表是数据的容器，数据库是是表的容器，表必须属于一个数据库，进行表操作时候，必须要指定数据库
创建表
语法
CREATE TABALE table_name(列结构) 表选项();
说明
先分析实体的属性，这些属性如何保存
如班级：班级班号，班级的日期
列定义
列名 数据类型(列的属性 约束)
简单数据类型
vchar(字符数)
时间类型 date
指定数据库
db_name.table_name();
use db_name;
特殊字符的数据库名，用``标识
同一个数据库中，如果要使用两个应用，建议用前缀进行区分。
example
CREATE TABLE python_class(
class_no varchar(20),
date_start date
);
查看表
语法
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;
说明
用pattern来快速的查找表
通配符
% - 任意字符的任意次数的组合
存储结构以*.frm关键字结束
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;
删除表
语法
DROP TABLE [if exists] table_name;
说明
如果没有表，可以用if exists进行判断
操作系统的安全限制，有时候不能删除，需要现在硬盘删除，然后使用命令进行删除
删除操作，一定要谨慎操作
修改表
语法
-- 重命名表：修改表名,支持同时修改多个表，支持跨数据库重命名
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;
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)
创建数据(插入数据)
语法
INSERT INTO tb_name( 字段列表)VALUES (值列表);
如果插入时，给所有字段值进行插入相应的值，可以省略字段列表。要求值得顺序应该与表中的字段的顺序一致。
Example
INSERT INTO exam_student (name, stu_no) VALUES ('jack', 1);
获得数据(查询数据)
语法
SELECT 字段列表 FROM tb_name 查询条件;
字段列表，可以用*代替，表示所有列表;
查询条件可以省略，相当于WHERE 1。
Example
SELECT name, stu_no FROM exam_student WHERE 1;
SELECT * FROM exam_student WHERE 1;
修改数据
语法
UPDATE tb_name SET 字段=新值,... 条件
Example
删除数据
语法
DELETE FROM tb_name 条件
Example
DELETE FROM exam_student WHERE score <=90;
字符集操作及校对规则
字符集操作
语法
SHOW VARIABLES LIKE 'character_%';
显示当前数据库服务器端和系统端的字符集格式
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)
字符集的校对规则
简介
当前字符集内，字符之间的比较关系，如ASCI码的比较，97=>'a'
每个字符集都支持不定数量的校对规则。
2. 语法
SHOW COLLATION;
SHOW COLLATION LIKE 'characterset_zone_ci/cs/bin'
比较的规则
字符集_地区名_比较规则
ci：不区分大小写
cs：区分大小写
bin：字节比较
校队规则根据默认的字符集进行变化，如果不设置校对规则，有默认的校对规则
影响字符的存储，是字符集；影响字符排序的，是校队规则
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)
列类型
数值类型
整数型
类型
字节
值范围
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)
小数型
简介
类型
字节
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
580
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
