mysql 在线模型 mysql数据模型

一、数据库是什么？

数据模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

• 层次模型：层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树
• 网状模型：网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网
• 关系模型：关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表

数据类型 

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON 

主流关系数据库

目前，主流的关系数据库主要分为以下几类：

1. 商用数据库，例如：Oracle，SQL Server，DB2等；
2. 开源数据库，例如：MySQL，PostgreSQL等；
3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

二、mysql安装（mac）

1、下载mysql并双击打开

2、将软件包拖过去

3、配置：

*在终端切换到根目录

$ cd ~

$ vim ./.bash_profile

*进入vim 编辑环，输入

export PATH=$PATH:/usr/local/mysql/bin

export PATH=$PATH:/usr/local/mysql/support-files

 *在终端界面下输入以下命令，让配置文件的修改生效

$ source ~/.bash_profile

$ echo $PATH 

4、初始化设置

mysql -u root -p

退出：exit 

备注：

进入到 /usr/local/mysql/support-files 目录。里面有个文件:my-default.cnf

将其复制到桌面上，改名为my.cnf，将内容替换为。

 

1. [mysqld]
2. default-storage-engine=INNODB
3. character-set-server=utf8
4. port = 3306
5.  
6. [client]
7. default-character-set=utf8

将修改后的文件my.cnf复制到 /etc 目录下。

重启mysql

5.1 检测修改结果

$mysql>>>show variables like '%char%';

至此数据库就可以愉快的使用啦！

三、主键·外键·索引

主键

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键

 * 联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升

外键

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键 

外键并不是通过列名实现的，而是通过定义外键约束实现的：

 

1. ALTER TABLE students
2. ADD CONSTRAINT fk_class_id
3. FOREIGN KEY (class_id)
4. REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。 

要删除一个外键约束，也是通过ALTER TABLE实现的：

 

1. ALTER TABLE students
2. DROP FOREIGN KEY fk_class_id;

 注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

一对一 

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

四、查询数据

查询students表的所有数据

SELECT * FROM students;

查询分数在80分以上的学生记录。

语法：SELECT * FROM <表名> WHERE <条件表达式>

SELECT * FROM students WHERE score >= 80;

第一种条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。

SELECT * FROM students WHERE score >= 80 AND gender = 'M';

第二种条件是<条件1> OR <条件2>

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

第三种条件是NOT <条件>

SELECT * FROM students WHERE NOT class_id = 2;

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

投影查询

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。

例如，从students表中返回id、score和name这三列

SELECT id, score, name FROM students;

投影查询同样可以接WHERE条件，实现复杂的查询 

SELECT id, score points, name FROM students WHERE gender = 'M';

使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。

SELECT语句可以对结果集的列进行重命名。

五、花样查询数据

排序查询

在查询语句后面添加 ORDER BY，默认从低到高排序，例如：

SELECT id, name, gender, score FROM students ORDER BY score;

如果想从高到低就在末尾加上DESC， 例如：

SELECT id, name, gender, score FROM students ORDER BY score DESC;

多标准排序，如按score, gender排序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

 

1. SELECT id, name, gender, score
2. FROM students
3. WHERE class_id = 1
4. ORDER BY score DESC;

使用ORDER BY可以对结果集进行排序；

可以对多列进行升序、倒序排序。

分页查询

查询可以通过LIMIT <M> OFFSET <N>子句实现 

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0 

 

1. SELECT id, name, gender, score
2. FROM students
3. ORDER BY score DESC
4. LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始. 

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

聚合查询 

SQL内置的COUNT()函数查询： 

SELECT COUNT(*) FROM students;

或者娶个别名：

SELECT COUNT(*) num FROM students;

聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等： 

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数: 

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

分组聚合

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

 按照class_id分组，显示各班总人数。 

练习：请使用一条SELECT查询查出每个班级男生和女生的平均分：

SELECT class_id, gender, AVG(score) NUM FROM students GROUP BY class_id, gender;

多表查询

语法：SELECT * FROM <表1> <表2>

例如：SELECT * FROM students, classes;

取别名：FROM <表名1> <别名1>, <表名2> <别名2>

连接查询

 选出所有学生，同时返回班级名称。

 

1. SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
2. FROM students s
3. INNER JOIN classes c
4. ON s.class_id = c.id;

 

注意INNER JOIN查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

内连接与外链接：

JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；

JOIN查询仍然可以使用WHERE条件和ORDER BY排序。