Mysql数据库

Mysql数据库

安装与配置

安装服务端

Linux安装

1
2
3
4
# Ubantu
sudo apt-get install mysql-server
# Centos
sudo apt-get install mysql-server

Mac安装

官网下载安装包直接安装

启动相关命令

查看MySQL服务状态:

1
sudo service mysql status

停止MySQL服务:

1
sudo service mysql stop

启动MySQL服务:

1
sudo service mysql start

重启MySQL服务:

1
sudo service mysql restart

配置

配置文件路径

/etc/mysql/mysql.conf.d/mysqld.cnf

主要配置信息说明:

1
2
3
4
5
6
7
port表示端口号，默认为3306

bind-address表示服务器绑定的ip，默认为127.0.0.1

datadir表示数据库保存路径，默认为/var/lib/mysql

log_error表示错误日志，默认为/var/log/mysql/error.log

安装客户端

图形化界面客户端Navicat

  1. 可以到Navicat官网下载
  2. 将压缩文件拷贝到Ubuntu虚拟机中，放到桌面上，解压

Navicat的使用说明:

1
2
3
tar zxvf navicat112_mysql_cs_x64.tar.gz

./start_navicat

命令行客户端(默认已安装)

1
sudo apt-get install mysql-client # Ubantu

连接数据库

1
2
3
4
mysql -uroot -p
# -u: 表示MySQL服务端的用户名
# -p: 表示MySQL服务端的密码
# quit 或者 exit 或者 ctr + d 表示退出

数据类型和约束

常用数据类型:

  • 整数：int，bit
  • 小数：decimal
  • 字符串：varchar,char
  • 日期时间: date, time, datetime
  • 枚举类型(enum)

数据类型说明:

  • decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
  • char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ‘，3表示字符数
  • varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’，3表示字符数
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
  • 字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

数据约束

约束是指数据在数据类型限定的基础上额外增加的要求.

常见的约束如下:

  • 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
  • 非空 not null: 此字段不允许填写空值.
  • 惟一 unique: 此字段的值不允许重复.
  • 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
  • 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

数据类型附录表

1. 整数类型
类型 字节大小 有符号范围(Signed) 无符号范围(Unsigned)
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615
2. 字符串
类型 说明 使用场景
CHAR 固定长度，小型数据 身份证号、手机号、电话、密码
VARCHAR 可变长度，小型数据 姓名、地址、品牌、型号
TEXT 可变长度，字符个数大于 4000 存储小型文章或者新闻
LONGTEXT 可变长度， 极大型文本数据 存储极大型文本数据
3. 时间类型
类型 字节大小 示例
DATE 4 ‘2020-01-01’
TIME 3 ‘12:29:59’
DATETIME 8 ‘2020-01-01 12:29:59’
YEAR 1 ‘2017’
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

SQL命令

数据库操作的SQL语句

  1. 查看所有数据库

    1
    show databases;

  2. 创建数据库

    1
    2
    3
    4
    5
    6
    create database 数据库名 charset=utf8;
    例：
    create database python charset=utf8;

    -- 查看数据库信息(可查看数据库字符集)
    show create datebase 数据库名

  3. 修改数据库字符集

    1
    alter database 数据库名 charset=utf8;

  4. 使用数据库

    1
    use 数据库名;

  5. 查看当前使用的数据库

    1
    select database();

  6. 删除数据库-慎重

    1
    2
    3
    drop database 数据库名;
    例：
    drop database python;

表结构操作的SQL语句

  1. 查看当前数据库中所有表

    1
    show tables;

  2. 创建表

    1
    2
    3
    4
    5
    6
    7
    create table students(
     id int unsigned primary key auto_increment not null,
     name varchar(20) not null,
     age tinyint unsigned default 0,
     height decimal(5,2),
     gender enum('男','女','人妖','保密')
    );

    说明:

    1
    2
    3
    4
    5
    create table 表名(
    字段名称 数据类型  可选的约束条件,
    column1 datatype contrai,
    ...
    );

  3. 修改表-添加字段

    1
    2
    3
    alter table 表名 add 列名 类型 约束;
    例：
    alter table students add birthday datetime;

  4. 修改表-修改字段类型

    1
    2
    3
    alter table 表名 modify 列名 类型 约束;
    例：
    alter table students modify birthday date not null;

    说明:

    • modify: 只能修改字段类型或者约束，不能修改字段名

  5. 修改表-修改字段名和字段类型

    1
    2
    3
    alter table 表名 change 原名 新名 类型及约束;
    例：
    alter table students change birthday birth datetime not null;

    说明:

    • change: 既能对字段重命名又能修改字段类型还能修改约束

  6. 修改表-删除字段

    1
    2
    3
    alter table 表名 drop 列名;
    例：
    alter table students drop birthday;

  7. 查看创表SQL语句

    1
    2
    3
    show create table 表名;
    例：
    show create table students;

  8. 查看创库SQL语句

    1
    2
    3
    show create database 数据库名;
    例：
    show create database mytest;

  9. 删除表

    1
    2
    3
    drop table 表名;
    例：
    drop table students;

表数据操作的SQL语句

  1. 查询数据

    1
    2
    3
    4
    5
    6
    7
    8
    -- 1. 查询所有列
    select * from 表名;
    例：
    select * from students;
    -- 2. 查询指定列
    select 列1,列2,... from 表名;
    例：
    select id,name from students;

  2. 添加数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    -- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
    insert into 表名 values (...)
    例:
    insert into students values(0, 'xx', default, default, '男');
    -- 2. 部分列插入：值的顺序与给出的列顺序对应
    insert into 表名 (列1,...) values(值1,...)
    例:
    insert into students(name, age) values('王二小', 15);
    -- 3. 全列多行插入
    insert into 表名 values(...),(...)...;
    例:
    insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
    -- 4. 部分列多行插入
    insert into 表名(列1,...) values(值1,...),(值1,...)...;
    例：
    insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);

    说明:

    • 主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
    • 在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值

  3. 修改数据

    1
    2
    3
    update 表名 set 列1=值1,列2=值2... where 条件
    例：
    update students set age = 18, gender = '女' where id = 6;

  4. 删除数据

    1
    2
    3
    delete from 表名 where 条件
    例：
    delete from students where id=5;

    问题:

    上面的操作称之为物理删除，一旦删除就不容易恢复，我们可以使用逻辑删除的方式来解决这个问题。

    1
    2
    3
    4
    -- 添加删除表示字段，0表示未删除 1表示删除
    alter table students add isdelete bit default 0;
    -- 逻辑删除数据
    update students set isdelete = 1 where id = 8;

as和distinct关键字

as关键字

在使用SQL语句显示结果的时候，往往在屏幕显示的字段名并不具备良好的可读性，此时可以使用 as 给字段起一个别名。

  1. 使用 as 给字段起别名

    1
    select id as 序号, name as 名字, gender as 性别 from students;

  2. 可以通过 as 给表起别名

    1
    2
    3
    4
    5
    6
    7
    8
    -- 如果是单表查询 可以省略表名
    select id, name, gender from students;

    -- 表名.字段名
    select students.id,students.name,students.gender from students;

    -- 可以通过 as 给表起别名 
    select s.id,s.name,s.gender from students as s;

    说明:

    • 在这里给表起别名看起来并没有什么意义,然而并不是这样的，我们在后期学习 自连接 的时候，必须要对表起别名。

distinct关键字

distinct可以去除重复数据行。

1
2
3
4
5
6
7
select distinct 列1,... from 表名;

例： 查询班级中学生的性别
select name, gender from students;

-- 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct
select distinct name, gender from students;

where条件查询

使用where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。

where语句支持的运算符:

  1. 比较运算符
  2. 逻辑运算符
  3. 模糊查询
  4. 范围查询
  5. 空判断

where条件查询语法格式如下:

1
2
3
select * from 表名 where 条件;
例：
select * from students where id = 1;

比较运算符查询

  1. 等于: =
  2. 大于: >
  3. 大于等于: >=
  4. 小于: <
  5. 小于等于: <=
  6. 不等于: != 或 <>

例1：查询编号大于3的学生:

1
select * from students where id > 3;

例2：查询编号不大于4的学生:

1
select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生:

1
select * from students where name != '黄蓉';

例4：查询没被删除的学生:

1
select * from students where is_delete=0;

逻辑运算符查询

  1. and
  2. or
  3. not

例1：查询编号大于3的女同学:

1
select * from students where id > 3 and gender=0;

例2：查询编号小于4或没被删除的学生:

1
select * from students where id < 4 or is_delete=0;

例3：查询年龄不在10岁到15岁之间的学生:

1
select * from students where not (age >= 10 and age <= 15);

说明:

  • 多个条件判断想要作为一个整体，可以结合‘()’。

模糊查询

  1. like是模糊查询关键字
  2. %表示任意多个任意字符
  3. _表示一个任意字符

例1：查询姓黄的学生:

1
select * from students where name like '黄%';

例2：查询姓黄并且“名”是一个字的学生:

1
select * from students where name like '黄_';

例3：查询姓黄或叫靖的学生:

1
select * from students where name like '黄%' or name like '%靖';

范围查询

  1. between .. and .. 表示在一个连续的范围内查询
  2. in 表示在一个非连续的范围内查询

例1：查询编号为3至8的学生:

1
select * from students where id between 3 and 8;

例2：查询编号不是3至8的男生:

1
select * from students where (not id between 3 and 8) and gender='男';

空判断查询

  1. 判断为空使用: is null
  2. 判断非空使用: is not null

例1：查询没有填写身高的学生:

1
select * from students where height is null;

注意:

  1. 不能使用 where height = null 判断为空
  2. 不能使用 where height != null 判断非空
  3. null 不等于 ‘’ 空字符串

排序

排序查询语法：

1
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

语法说明:

  1. 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
  2. asc从小到大排列，即升序
  3. desc从大到小排序，即降序
  4. 默认按照列值从小到大排序（即asc关键字）

例1：查询未删除男生信息，按学号降序:

1
select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:

1
select * from students  order by age desc,height desc;

分页查询

分页查询的语法

1
select * from 表名 limit start,count

说明:

  1. limit是分页查询关键字
  2. start表示开始行索引，默认是0
  3. count表示查询条数

例：查询前3行男生信息:

1
2
3
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;

例：查询学生表，获取第n页数据的SQL语句:

1
select * from students limit (n-1)*m,m

聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

常用的聚合函数:

  1. count(col): 表示求指定列的总行数
  2. max(col): 表示求指定列的最大值
  3. min(col): 表示求指定列的最小值
  4. sum(col): 表示求指定列的和
  5. avg(col): 表示求指定列的平均值

求总行数

1
2
3
4
-- 返回非NULL数据的总行数.
select count(height) from students; 
-- 返回总行数，包含null值记录;
select count(*) from students;

求最大值

1
2
-- 查询女生的编号最大值
select max(id) from students where gender = 2;

求最小值

1
2
-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

求和

1
2
3
4
-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;

求平均值

1
2
3
4
-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

说明

  • ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

聚合函数的特点

  • 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

分组查询

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

分组查询基本的语法格式如下：

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

说明:

  • 列名: 是指按照指定字段的值进行分组。
  • HAVING 条件表达式: 用来过滤分组后的数据。
  • WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

group by

group by可用于单个字段分组，也可用于多个字段分组

1
2
3
4
-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;

group by + group_concat()

group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

1
2
-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

group by + 聚合函数

1
2
3
4
-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;

group by + having

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

1
2
-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;

group by + with rollup

with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

1
2
3
4
-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

连接查询

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。

连接查询可以分为: 内连接查询、左连接查询、右连接查询、自连接查询

内连接查询

查询两个表中符合条件的共有记录

格式:

1
2
3
-- inner join 就是内连接查询关键字
-- on 就是连接查询条件
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

格式:

1
2
-- left join 就是左连接查询关键字
select 字段 from 左表 left join 右表 on 左表.字段1 = 右表.字段2

右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

格式:**

1
2
-- right join 就是右连接查询关键字
select 字段 from 左表 right join 右表 on 左表.字段1 = 右表.字段2

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.

子查询是一个完整的SQL语句，子查询被嵌入到一对小括号里面

主查询和子查询的关系:

  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
  3. 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询的使用

例： 查询大于平均年龄的学生:

1
select * from students where age > (select avg(age) from students);

例：查询学生在班的所有班级名字:

1
select name from classes where id in (select cls_id from students where cls_id is not null);

例：查找年龄最大,身高最高的学生:

1
select * from students where (age, height) =  (select max(age), max(height) from students);

数据库设计之三范式

范式: 对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。

  • 第一范式（1NF）: 强调的是列的原子性，即列不能够再分成其他几列。
  • 第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
  • 第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

外键SQL语句的编写

外键约束的作用

外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

对于已经存在的字段添加外键约束

1
2
-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);

在创建数据表时设置外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建学校表
create table school(
    id int not null primary key auto_increment, 
    name varchar(10)
);

-- 创建老师表
create table teacher(
    id int not null primary key auto_increment, 
    name varchar(10), 
    s_id int not null, 
    foreign key(s_id) references school(id)
);

删除外键约束

1
2
3
4
5
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;

-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;