猿创征文 | MySQL从基础到高级

char ：定长字符串。

优点：存储性能高

缺点：浪费空间

eg ： name char ( 10 ) 如果存储的数据字符个数不足 10 个，也会占 10 个的空间

varchar ：变长字符串。

优点：节约空间

缺点：存储性能底

eg ： name varchar ( 10 ) 如果存储的数据字符个数不足 10 个，那就数据字符个数是几就占几个的空间

5.4 删除表

删除表

DROP TABLE 表名 ;

删除表时判断表是否存在

DROP TABLE IF EXISTS 表名 ;

5.5 修改表

修改表名

ALTER TABLE 表名 RENAME TO 新的表名 ;

-- 将表名 student 修改为 stu

alter table student rename to stu;

添加一列

ALTER TABLE 表名 ADD 列名数据类型 ;

-- 给 stu 表添加一列 address ，该字段类型是 varchar(50)

alter table stu add address varchar ( 50 ) ;

修改数据类型

ALTER TABLE 表名 MODIFY 列名新数据类型 ;

-- 将 stu 表中的 address 字段的类型改为 char(50)

alter table stu modify address char ( 50 ) ;

修改列名和数据类型

ALTER TABLE 表名 CHANGE 列名新列名新数据类型 ;

-- 将 stu 表中的 address 字段名改为 addr ，类型改为 varchar(50)

alter table stu change address addr varchar ( 50 ) ;

删除列

ALTER TABLE 表名 DROP 列名 ;

-- 将 stu 表中的 addr 字段删除

alter table stu drop addr;

6，navicat使用

6.1 navicat概述

Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。

这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。

官网： http://www.navicat.com.cn

6.2 navicat安装

6.3 navicat使用

6.3.1 建立和mysql服务的连接

第一步：点击连接，选择 MySQL

第二步：填写连接数据库必要的信息

6.3.2 操作

修改表结构。

编写 SQL 语句并执行。

7，DML

7.1 添加数据

给指定列添加数据

INSERT INTO 表名 ( 列名 1, 列名 2,… ) VALUES ( 值 1, 值 2,… ) ;

给全部列添加数据

INSERT INTO 表名 VALUES(值1,值2,…);

批量添加数据

INSERT INTO 表名 ( 列名 1, 列名 2,… ) VALUES ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) …;

INSERT INTO 表名 VALUES ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) …;

7.2 修改数据

修改表数据

UPDATE 表名 SET 列名 1 = 值 1, 列名 2 = 值 2,… [ WHERE 条件 ] ;

7.3 删除数据

删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

8，DQL

8.1 基础查询

查询多个字段

SELECT 字段列表 FROM 表名 ;

SELECT * FROM 表名 ; -- 查询所有数据

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名 ;

起别名

AS : AS 也可以省略

8.2 条件查询

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

模糊查询

模糊查询使用 like 关键字，可以使用通配符进行占位 :

（1） _ : 代表单个任意字符

（2）% : 代表任意个数字符

8.3 排序查询

8.3.1 语法

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名 1 [ 排序方式 1 ] , 排序字段名 2 [ 排序方式 2 ] …;

上述语句中的排序方式有两种，分别是：

ASC ：升序排列 （默认值）

DESC ：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

8.4 聚合函数

8.4.1 概念

将一列数据作为一个整体，进行纵向计算。

8.4.2 聚合函数分类

8.4.3 聚合函数语法

SELECT 聚合函数名 ( 列名 ) FROM 表 ;

注意： null 值不参与所有聚合函数运算。

8.5 分组查询

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

8.6 分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数 ;

注意：上述语句中的起始索引是从0开始。

起始索引计算公式：

起始索引 = ( 当前页码 - 1 ) * 每页显示的条数

9，约束

9.1 概念

约束是作用于表中列上的规则，用于限制加入表的数据。

例如：我们可以给 id 列加约束，让其值不能重复，不能为 null 值。

约束的存在保证了数据库中数据的正确性、有效性和完整性。

添加约束可以在添加数据的时候就限制不正确的数据，年龄是 3000 ，数学成绩是 -5 分这样无效的数据，继而保障数据的

完整性。

9.2 分类

非空约束：关键字是 NOT NULL

保证列中所有的数据不能有 null 值。

例如： id 列在添加马花疼这条数据时就不能添加成功。

唯一约束：关键字是 UNIQUE

保证列中所有数据各不相同。

例如： id 列中三条数据的值都是 1 ，这样的数据在添加时是绝对不允许的。

主键约束：关键字是 PRIMARY KEY

主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。

例如：上图表中 id 就可以作为主键，来标识每条数据。那么这样就要求数据中 id 的值不能重复，不能为 null 值。

检查约束：关键字是 CHECK

保证列中的值满足某一条件。

例如：我们可以给 age 列添加一个范围，最低年龄可以设置为 1 ，最大年龄就可以设置为 300 ，这样的数据才更合理些。

注意： MySQL 不支持检查约束。

默认约束：关键字是 DEFAULT

保存数据时，未指定值则采用默认值。

例如：我们在给 english 列添加该约束，指定默认值是 0 ，这样在添加数据时没有指定具体值时就会采用默认给定的 0 。

外键约束：关键字是 FOREIGN KEY

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

9.3 非空约束

概念

非空约束用于保证列中所有数据不能有 NULL 值

语法

添加约束

-- 创建表时添加非空约束

CREATE TABLE 表名 (

列名数据类型 NOT NULL ,

…

) ;

-- 建完表后添加非空约束

ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL ;

删除约束

ALTER TABLE 表名 MODIFY 字段名数据类型 ;

9.4 唯一约束

概念

唯一约束用于保证列中所有数据各不相同。

语法

添加约束

-- 创建表时添加唯一约束

CREATE TABLE 表名 (

列名数据类型 UNIQUE [ AUTO_INCREMENT ] ,

-- AUTO_INCREMENT: 当不指定值时自动增长

…

) ;

CREATE TABLE 表名 (

列名数据类型 ,

…

[ CONSTRAINT ] [ 约束名称 ] UNIQUE ( 列名 )

) ;

-- 建完表后添加唯一约束

ALTER TABLE 表名 MODIFY 字段名数据类型 UNIQUE ;

删除约束

ALTER TABLE 表名 DROP INDEX 字段名 ;

9.5 主键约束

概念

主键是一行数据的唯一标识，要求非空且唯一。

一张表只能有一个主键

语法

添加约束

-- 创建表时添加主键约束

CREATE TABLE 表名 (

列名数据类型 PRIMARY KEY [ AUTO_INCREMENT ] ,

…

) ;

CREATE TABLE 表名 (

列名数据类型 ,

[ CONSTRAINT ] [ 约束名称 ] PRIMARY KEY ( 列名 )

) ;

-- 建完表后添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY ( 字段名 ) ;

ALTER TABLE 表名 DROP PRIMARY KEY;

9.6 默认约束

概念

保存数据时，未指定值则采用默认值

语法

添加约束

-- 创建表时添加默认约束

CREATE TABLE 表名 (

列名数据类型 DEFAULT 默认值 ,

…

) ;

-- 建完表后添加默认约束

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值 ;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT ;

9.7 外键约束

9.8.1 概述

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

9.8.2 语法

添加外键约束

-- 创建表时添加外键约束

CREATE TABLE 表名 (

列名数据类型 ,

…

[ CONSTRAINT ] [ 外键名称 ] FOREIGN KEY ( 外键列名 ) REFERENCES 主表 ( 主表列名 )

) ;

-- 建完表后添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段名称 ) REFERENCES 主表名称 ( 主表列名称 ) ;

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称 ;

10，数据库设计

2.1 数据库设计简介

软件的研发步骤

数据库设计概念

数据库设计就是根据业务系统的具体需求，结合我们所选用的 DBMS ，为这个业务系统构造出最优的数据存储模型。

建立数据库中的 表结构 以及 表与表之间的关联关系 的过程。

数据库设计的步骤

需求分析（数据是什么 ? 数据具有哪些属性 ? 数据与属性的特点是什么）

逻辑分析（通过 ER 图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

维护设计（ 1. 对新的需求进行建表； 2. 表优化）

表关系

一对一

如：用户和用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。

一对多

如：部门和员工

一个部门对应多个员工，一个员工对应一个部门。如下图：

多对多

如：商品和订单

一个商品对应多个订单，一个订单包含多个商品。如下图：

2.2 表关系(一对多)

一对多

如：部门和员工

一个部门对应多个员工，一个员工对应一个部门。

实现方式

在多的一方建立外键，指向一的一方的主键

案例

-- 删除表 
DROP TABLE IF EXISTS tb_emp; 
DROP TABLE IF EXISTS tb_dept; 
-- 部门表 
CREATE TABLE tb_dept( 
    id int primary key auto_increment, 
    dep_name varchar(20), 
    addr varchar(20) 
);
-- 员工表 
CREATE TABLE tb_emp(     
    id int primary key auto_increment, 
    name varchar(20),     
    age int, 
    dep_id int, 

    -- 添加外键 dep_id,关联 dept 表的id主键 
    CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id) 
);

查看表结构模型图：

2.3 表关系(多对多)

多对多

如：商品和订单

一个商品对应多个订单，一个订单包含多个商品

实现方式

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

案例

建表语句如下：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods; 
DROP TABLE IF EXISTS tb_order; 
DROP TABLE IF EXISTS tb_goods; 

-- 订单表 
CREATE TABLE tb_order( 
    id int primary key auto_increment, 
    payment double(10,2),     
    payment_type TINYINT,     
    status TINYINT 
);

-- 商品表 
CREATE TABLE tb_goods( 
    id int primary key auto_increment, 
    title varchar(100), 
    price double(10,2) 
);

-- 订单商品中间表 
CREATE TABLE tb_order_goods( 
    id int primary key auto_increment, 
    order_id int, 
    goods_id int,     
    count int 
);

-- 建完表后，添加外键 
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id); 
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

查看表结构模型图：

2.4 表关系(一对一)

一对一

如：用户和用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方式

在任意一方加入外键，关联另一方主键，并且设置外键为唯一 (UNIQUE)

案例

create table tb_user_desc ( 
    id int primary key auto_increment, 
    city varchar(20),     
    edu varchar(10), 
    income int, 
    status char(2), 
    des varchar(100)
);、

create table tb_user ( 
    id int primary key auto_increment, 
    photo varchar(100), 
    nickname varchar(50), 
    age int, 
    gender char(1), 
    desc_id int unique, 
    -- 添加外键 
    CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id) 
);

查看表结构模型图：