mysql 简介、安装、创建、查看、修改、删除

一、数据库简介

数据库是一种特殊文件，中间存储着数据。

RDBMS

• 关系型数据主要产品：(数据库排名：https://db-engines.com/en/ranking)

  • oracle：以前大型项目中银行，电信等项目

  • mysql：web时代使用最广泛

  • ms sql server：在微软项目中使用

  • sqlite：轻量级数据库，主要应用于移动平台
    ​

RDBMS和数据库的关系

二、MySQL安装

linux：

2.1 服务器端安装：

1. 设置阿里源，安装mysql-server，sudo apt-get install mysql-server，并设置开机自启动。
2. 服务器一般用于接受客户端请求，执行sql语句，管理数据库
3. 启动服务 sudo service mysql start ，结束服务 sudo service mysql stop，重启服务 sudo service mysql restart
4. 查看进程中是否存在mysql服务 ps aux|grep mysql

配置：

1. 配置文件目录为/etc/mysql/mysql.cnf

2. 进入conf.d目录，打开mysql.conf，发现并没有配置，进入mysql.conf.d目录，打开mysql.cnf，可以看到配置项

3. 主要配置如下

   bind-address表示服务器绑定的ip，默认为127.0.0.1
   
   port表示端口，默认为3306
   
   datadir表示数据库目录，默认为/var/lib/mysql
   
   general_log_file表示普通日志，默认为/var/log/mysql/mysql.log
   
   log_error表示错误日志，默认为/var/log/mysql/error.log
   

2.2 客服端

客户端为开发人员和dba使用，通过socket方式与服务器通信，常用有navicat，命令行mysql

2.2.1 图形化客户端navicat**

• 问题1：中文乱码
  打开start_navicat文件，将export LANG="en_US.UTF-8" 改成 export LANG="zh_CN.UTF-8"
• 问题2：试用期
  删除用户目录下的.navicat64目录

2.2.2命令行客户端

1. 安装：sudo apt-get install mysql-client
2. 连接：mysql -u root -p 然后输入密码进入，或者mysql -u root -p密码
3. 退出：quit 或者 exit 和 ctrl+d

windos:

参考：https://blog.csdn.net/zhouzezhou/article/details/52446608

https://blog.csdn.net/tojohnonly/article/details/79819703

三、数据完整性

一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中。在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。

3.1 数据类型

• 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
• 常用数据类型如下
  • 整数：int，bit
  • 小数：decimal
  • 字符串：varchar,char
  • 日期时间: date, time, datetime
  • 枚举类型(enum)
• 特别说明的类型如下：
  • decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
  • char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为'ab '
  • varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
  • 字符串text表示存储大文本，当字符大于4000时推荐使用
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
• 更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106

3.2 约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
• 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

数值类型(常用)

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295

日期时间类型

类型	字节大小	示例
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

四、命令行脚本

• windows通过dos命令进入mysql：
  进入mysql的bin目录，运行mysql -h localhost -uroot -p(密码)，然后输入密码就ok

• ubuntu：
  打开终端，运行mysql -uroot -p

查看版本：select version(); ，显示当前时间：select now()

4.1 数据库操作

• 查看所有数据库 show databases;
• 使用数据库 use 数据库名;
• 查看当前使用的数据库 select database();
• 创建数据库 create database 数据库名 charset=utf8;(有些默认不是utf8)
  • show create database 数据库名;可以查看创建过程
• 删除数据库 drop database 数据库名;

4.2 数据表操作

• 查看所有表 show tables;

• 查看表结构 desc 表名;

• 创建表

  • 创建学生表

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

  • 创建学生表

  CREATE TABLE classes(
  	id int unsigned auto_increment primary key not null,
      name varchar(10)
  );
  

• 修改表

  • 添加字段 alter table 表名 add 列名 类型;
  • 修改字段：

-- 重命名版(全部修改)
alter table 表名 change 原名 新名 类型及约束;
-- 不重命名版(修改类型和约束)
alter table 表名 modify 列名 类型约束;

• 删除字段 alter table 表名 drop 列名

• 删除表 drop table 表名

• 查看表的创建语句 show create table

4.3 数据增删改查

curd：创建(Create)，更新(Update)，读取(Retrieve)，删除(Delete)

• 查询

  -- 查询所有列
  select * from 表名；
  eg: select * from students;
  -- 查询指定列，使用as指定别名
  select 列1,列2,... from 表名；
  eg：select id,name from students;
  eg：select id,name as 姓名 from students;
  

• 增加

  主键列是自动增长，但是在全列插入时需要站位，通常用0或者default或者null来占位。

  -- 全列插入：值和表对应
  insert into 表名 values();
  eg: insert into students values(0,'郭靖',18,18.88,1,0);
  
  -- 部分列插入: 给出部分列
  insert into 表名(列1,...) values(值1,...);
  eg: insert into students(name, age, height) values('张三', 18, 188);
  
  -- 多行插入
  insert into 表名 values(), (),...;
  eg: insert into students values(0,'王五',18,18.88,1,0), (0,'六六',18,18.88,1,0);
  eg: insert into students(name, age, height) values('qiqi', 18, 188),('baba', 18, 188);
  

• 修改

  update 表名 set 列1=值1,列2=值2... where 条件
  eg: update students set age=22 where gender=1;
  

• 删除

  -- 物理删除
  delete from 表名 where 条件
  eg: delete from students where gender=null;
  -- 逻辑删除
  alter table students add isdelete bit default 0;
  update students set isdelete=1 where gender=1;
  

4.4 数据备份&恢复**

备份：
mysqldump -uroot -p密码 数据库名 > python.sql;

恢复：
mysql -uroot -p密码 新数据库名 < python.sql;

五、数据库设计

5.1 三范式

• 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列
• 第二范式（2NF）：首先是 1NF，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
• 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。

5.2 E-R模型

• E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
• R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多

5.3 逻辑删除

• 对于重要数据，并不希望物理删除，一旦删除，数据无法找回
• 删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0

5.4 扩展

• 58到家数据库30条军规解读