一、 mysql数据库概述

关系型数据库，默认监听端口：tcp：3306 。mariadb，mysql；

关系型:构建模型是关系-实体图，E-R图；

实体：具有属性的对象

联系：实体与实体之间的关系

属性：用来描述实体

实体集：实体的集合

二、 mysql安装与配置

1. mysql的编译安装
2. yum安装或者apt-get

以CentOS7为例：

1） 配置安装环境

IP，防火墙，Selinux

2） 配置yum源

echo -e “[local]\nname=local\nbaseurl=file:///mnt\nenabled=1\ngpgcheck=0” > /etc/yum.repos.d/local.repo

mount /dev/sr0 /mnt

3） 安装

yum install -y mariadb-server mariadb

1. MySQL数据库配置：

1） 配置文件： /etc/my.cnf

一般配置写在：[mysqld]字段下。

2） 为mysql数据库本地登录的root用户赋予密码：

mysqladmin -u root password ‘123.com'

1. 数据库本地登录

mysql -u root -p123.com [数据库名]

三、 数据库操作：

1. 查看命令：

1） 查看数据库：

show databases;

2） 进入数据库：

use 数据库名称;

3） 查看数据表

show tables;

4） 查看数据表的结构：

desc 表名称;

5） 查看表中的数据：

select * from 表名称\G; （\G：数据以行的形式输出。）

1. 数据库操作：

1） 创建数据库

create database 数据库名称;

2） 删除数据库

drop database 数据库名称;

1. 数据表操作：

1） 创建数据表

create table 数据表名称(字段名  数据类型,...);

例子：

create table stu_info(stuID int, stuName varchar(20), stuSex varchar(20), stuAge tinyint, stuAddress varchar(200), stuPhone varchar(11));

2） 删除数据表

drop table 数据表名称;

3） 修改数据表

alter table 数据表名称()；

1. 数据结构：

mysql数据库中的数据类型

（1）精准数字

int：整型，-2^31~2^31-1，占用4字节

tinyint：整型，0~255，占用1字节

decimal[(p[,s])]：p，数据长度；s，小数点后保留位数；

numeric[(p[,s])：-10^38+1~10^38-1

（2）浮点数

real：正负十进制数；-3.40E+38~3.40E+38

float：-1.79E+308~-2.23E-308、0以及2.23E+308~1.79E-308

（3）字符串

nchar：定长字符串；char(10)，定长10个字符

nvarchar：可变长度字符串，varchar(10),最大10个字符

char：2倍的nchar存储容量

varchar：2倍的nvarchar存储容量

text：存储文本数据

（4）日期

date：只存储日期；公元元年~地球爆炸

time：只存储时间；精确到毫秒；10:13:00-000

datetime：存储日期及时间；1753-1-1至9999-1-1； 2021-08-20:10:13:00-000

（5）货币

money：存储货币值，取值范围为正负922 337 213 685 477.580 8之间

（6）二进制

bit：位数据类型，只取0或1为值

binary：存储固定长度二进制数据；1~8000

1. 数据操作：

1） 数据添加

insert [into] 数据表名称(字段1, 字段2,.....)  values(数字, ‘字符串’....)

注意： 字段数量一定要等于值的数量。

全表插入数据：

insert into stu_info values(2, 'John', 'male', 21, 'lanou for china', '11011011011');

2） 数据删除

全表删除：

delete from 数据表名称;

指定行删除：（使用where子句）

delete from 数据表名称 where 字段=值;

3） 数据更新

全表更新：

update 数据表名称 set 字段=新值;

指定单字段更新：（使用where子句）

update 数据表名称 set 字段=新值 where 字段=值;

指定多字段更新：

update 数据表名称 set 字段1=新值1, 字段2=新值2  where 字段=值;

案例：

update stu_info set stuPhone='13337373377',stuAddress='dalian for china'  where stuID=2;

4） 数据查询

单表查询：

（1） 全表查询：

select * from 数据表名称;

（2） 条件查询（where子句）

条件表达式：

like通常结合通配符查询：

_:通配单个字符

%：通配所有字符

select * from stu_info where stuName like ‘T%’;

（3） 排序查询

默认是升序排列，若要降序排列：desc

select * from stu_info order by stuAge desc;

（4） 分页查询

select * from 数据表名称 limit 起始行号,步长（查询多少行）;

（查询从起始行号下一行开始）

案例：

select * from stu_info limit 0,3;

（5） 聚合查询

sum()：求和

select sum(stuAge) from stu_info;

avg()：平均值

select avg(stuAge) from stu_info;

min()：最小值

select min(stuAge) from stu_info;

max()：最大值

select max(stuAge) from stu_info;

下午：

（6） 特定字段查询

select stuName,stuAge from stu_info;

（7） 将查询结果存储到新表

create table stu_info_new(select * from stu_info);

（8） 统计查询count()

select count(*)  from stu_info;

（9） 去重查询

select distinct stuSex from stu_info;

（10） 别名显示结果集：

select stuName [as] 姓名 from stu_info;

（11） 分组查询

select sum(stuAge) age, stuSex from stu_info group by stuSex;

分组前条件查询：

select sum(stuAge) age, stuSex from stu_info where stuSex='female' group by stuSex;

分组后条件查询：

select sum(stuAge) age, stuSex from stu_info  group by stuSex having sum(stuAge) > 45;

多表查询：

1） 联合查询

select * from A union all select * from B

注意：A和B表的数据结构要一致

2） 子查询

select * from stu_info where stuName in (select stuName from stu_info_new);

3） 内连接查询

select * from A,B where A.name=B.name;  或者

 select * from A inner join B on A.name=B.name;

4） 外连接查询：（两张或多张表必须有共同的字段名称）

左外连接查询：

select * from A left join B on A.name=B.name;

完整显示左边的表数据。

右外连接查询：

select * from A right join B on A.name=B.name;

完整显示右边的表数据。

数据库扩展：

一、 主键

主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS（数据据库管理系统） 更快的查找到表中的记录，都会在表中设置一个主键。

主键分为单字段主键和多字段联合主键，本节将分别讲解这两种主键约束的创建、修改和删除。

使用主键应注意以下几点：

    每个表只能定义一个主键。

    主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。

    一个字段名只能在联合主键字段表中出现一次。

    联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

在创建表时设置主键约束

在创建数据表时设置主键约束，既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键只能有一个。下面分别讲解设置单字段主键和多字段联合主键的方法。

1）设置单字段主键

在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。

在定义字段的同时指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

例 1

在 test_db 数据库中创建 tb_emp3 数据表，其主键为 id，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp3

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT

    -> );

Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp3;

+--------+------------------+------+------+---------+-------+

| Field  | Type              | Null | Key | Default | Extra |

+--------+------------------+------+------+---------+-------+

| id         | int(11)          | NO   | PRI | NULL    |       |

| name   | varchar(25) | YES  |       | NULL    |       |

| deptId | int(11)           | YES  |       | NULL    |       |

| salary | float              | YES  |        | NULL    |       |

+--------+-----------------+--------+------+----------+------+

4 rows in set (0.14 sec)

或者是在定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例 2

在 test_db 数据库中创建 tb_emp4  数据表，其主键为 id，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp4

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(id)

    -> );

Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp4;

+--------+------------------+------+------+----------+--------+

| Field  | Type               | Null | Key | Default | Extra |

+--------+------------------+------+------+----------+-------+

| id       | int(11)            | NO   | PRI  | NULL  |         |

| name | varchar(25)   | YES  |        | NULL   |         |

| deptId | int(11)          | YES  |         | NULL   |         |

| salary | float             | YES  |          | NULL   |        |

+--------+-----------------+--------+-------+----------+------+

4 rows in set (0.14 sec)

2）在创建表时设置联合主键

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

主键由多个字段联合组成，语法格式如下：

PRIMARY KEY [字段1，字段2，…,字段n]

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

例 3

创建数据表 tb_emp5，假设表中没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp5

    -> (

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(name,deptId)

    -> );

Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp5;

+--------+------------------+------+-----+---------+-------+

| Field  | Type              | Null | Key | Default | Extra |

+--------+------------------+------+-----+---------+-------+

| name   | varchar(25) | NO   | PRI | NULL |         |

| deptId  | int(11)          | NO   | PRI | NULL |         |

| salary  | float              | YES |        | NULL |         |

+--------+------------------+-------+------+--------+-------+

3 rows in set (0.14 sec)

在修改表时添加主键约束

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值。

在修改数据表时添加主键约束的语法格式如下：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

查看 tb_emp2 数据表的表结构，SQL 语句和运行结果如下所示。

mysql> DESC tb_emp2;

+--------+-----------------+------+-------+---------+-------+

| Field  | Type             | Null | Key | Default | Extra |

+--------+-----------------+------+-------+---------+-------+

| id        | int(11)          | NO   |         | NULL   |         |

| name  | varchar(30) | YES  |         | NULL   |         |

| deptId | int(11)          | YES  |         | NULL  |         |

| salary | float             | YES  |         | NULL   |         |

+--------+-----------------+-------+-------+----------+------+

4 rows in set (0.14 sec)

例 4

修改数据表 tb_emp2，将字段 id 设置为主键，SQL 语句和运行结果如下。

mysql> ALTER TABLE tb_emp2

    -> ADD PRIMARY KEY(id);

Query OK, 0 rows affected (0.94 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_emp2;

+--------+-----------------+------+-----+---------+-------+

| Field  | Type             | Null  | Key| Default | Extra |

+--------+-----------------+------+-----+---------+-------+

| id       | int(11)           | NO  | PRI | NULL    |       |

| name | varchar(30)  | YES |        | NULL    |       |

| deptId | int(11)         | YES  |        | NULL    |       |

| salary | float            | YES  |        | NULL    |       |

+--------+----------------+-------+------+-----------+------+

4 rows in set (0.12 sec)

通常情况下，当在修改表时要设置表中某个字段的主键约束时，要确保设置成主键约束的字段中值不能够有重复的，并且要保证是非空的。否则，无法设置主键约束。

删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

删除主键约束的语法格式如下所示：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

例 5

删除 tb_emp2 表中的主键约束，SQL 语句和运行结果如下。

mysql> ALTER TABLE tb_emp2

    -> DROP PRIMARY KEY;

Query OK, 0 rows affected (0.94 sec)

Records: 0  Duplicates: 0  Warnings: 0

由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。

二、 外键

1. 基本概念

1） MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

2） 外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

3） 如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

4） 外键的使用条件

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

5） 外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

1. 使用方法

创建外键的语法：

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值）

⑤ NO ACTION（无动作，默认的）

示例：

1）创建表1

create table repo_table(

repo_id char(13) not null primary key,

repo_name char(14) not null)

type=innodb;

创建表2

mysql> create table busi_table(

-> busi_id char(13) not null primary key,

-> busi_name char(13) not null,

-> repo_id char(13) not null,

-> foreign key(repo_id) references repo_table(repo_id))

-> type=innodb;

2）插入数据

insert into repo_table values("12","sz"); //success

insert into repo_table values("13","cd"); //success

insert into busi_table values("1003","cd", "13"); //success

insert into busi_table values("1002","sz", "12"); //success

insert into busi_table values("1001","gx", "11"); //failed,提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`))

3）增加级联操作

mysql> alter table busi_table

-> add constraint id_check

-> foreign key(repo_id)

-> references repo_table(repo_id)

-> on delete cascade

-> on update cascade;

ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb;

3、相关操作

外键约束（表2）对父表（表1）的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

CASCADE

删除包含与已删除键值有参照关系的所有记录

SET NULL

修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)

RESTRICT

拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)

NO ACTION

啥也不做

三、 自动增长列

语法：

auto_increment unique;

或者

auto_increment primary key;

特点：默认起始值为1，步长为1；

案例：

create table user(userID int not null auto_increment unique, username varcar(20) not null primary key, password varchar(20) not null);