关系型数据库--mysql

MYSQL为众多关系型数据管理系统中的一种

安装

在Ubuntu中安装数据库和客户端: sudo apt-get install mysql-server mysql-client
服务端操作: sudo service mysql status(查看状态)/start(启动服务端)/stop(终止服务端)/restart(重启服务端)
连接服务器: mysql -u用户名 -p密码或mysql -u用户名 -p
退出 mysql客户端: exit或 quit或ctrl + d

MYSQL配置

MySQL配置文件的介绍

默认配置文件路径为:/etc/mysql/mysql.conf.d/mysqld.cnf

主要配置信息说明:

• port表示端口号，默认为3306
• bind-address表示服务器绑定的ip，默认为 127.0.0.1
• datadir表示数据库保存路径，默认为/var/lib/mysql
• log_error表示错误日志保存路径，默认为/var/log/mysql/error.log

数据类型和约束

常用数据类型

编号	类型	描述
1	整数	tinyint, smallint,mediuint, int，bigint, bit
2	浮点数	decimal(5, 2)表示共存 5 位数，小数占 2 位
3	字符串	char（指定长度）, varchar（最大长度）, text
4	日期时间	date, time, datetime, year
5	枚举类型	enum()将数据的所有可能列举到括号中

注意: 对于图片、音频、视频等文件，不宜存储在数据库中，而是上传到某个云 服务器上，然后在数据库的表中存储文件的 保存路径

1. 整数类型

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
tinyint	1	- 128 ~ 127	0 ~ 255
smallint	2	- 3 2768 ~ 3 2767	0 ~ 6 5535
mediumint	3	- 838 8608 ~ 838 8607	0 ~ 1677 7215
int	4	- 21 4748 3648 ~ 21 4748 3647	0 ~ 42 9496 7295
bigint	8	- 9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

常用函数：

• 绝对值：abs(数值)
• 求余数：mod(数值1，数值2)
• 四舍五入保留几位小数：round(数值，保留的位数) -1表示取整

3. 字符串

类型	说明	使用场景
char（字符长度）	固定长度	小型数据 身份证号、手机号、电话
varchar（字符最大长度）	可变长度	小型数据 姓名、地址、品牌、型号
text	可变长度	字符个数大于 4000 存储小型文章或者新闻
longtext	可变长度	极大型文本数据 存储极大型文本数据

• char(n)，其中1<n<255，默认值为1，如果填充数据字符数不够则系统自动在其后补空格；若超过，则系统自动截掉超出部分。
• varchar(n)，存储长度为实际数值的长度。不补零不截取，
• 一个中文算一个字符长度，占用的字节空间与编码格式有关（UTF8占3个，GBK占2个）
• 优先使用char型的原因：char型的数据长度固定，因此它比varchar型数据的处理速度快。但char型占用空间相对比varcahr型可能更多，这就是用空间换时间

常用函数：

• 统计字符串长度：Length(sub_1)
• 拼接字符串：concat (sub_1, sub_2) 其他数据类型也可当字符串拼接
• 大小写转化：lower()、upper()
• 替换子字符串：replace (sub_1, ‘被替换的子字符’,’替换后的子字符’)
• 截取子字符串：substring (sub_1, 截取的起始位置, 截取长度)

4. 时间类型

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

常用函数：

约束

作用：为表中的字段添加额外的约束，不满足约束的数据禁止存入数据库中

约束	含义	作用
primary key	主键约束	唯一确定一行记录的字段可设为主键
unique	唯一约束	字段值不允许重复
not null	非空约束	字段值不允许为空
auto_increment	自动增长	主要用于主键
default 默认值	默认约束	若字段没有设置值则为默认值
foreign key	外键约束	子表使用主表数据, 那么可以将子表该字段定义为主表主键的外键

注意：

• 自动增长约束和默认约束在数据插入时需要占位，通常使用空值(0或null或default)
• 外键约束: 子表中外键字段的取值必须在主表的主键中存在

导入导出数据

将整个数据库全部导出
mysql -u用户名 -p密码 < 要导入的数据库数据(text.sql)

导入数据库：
source 路径/数据库文件名(home/text.sql) 该命令导入数据库需要先登录数据库客户端

将本地的数据文件（.csv）导入到数据库中
格式：load data local infile "路径/文件名.后缀" into table 制定表名 fields terminated by '行记录分隔符';

load data local infile 'E:/newFolder/UserInfo.csv' into table userinfo fields terminated by ',';

SQL语句（基础）

SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS的数据库的语言。

作用：实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁

SQL语言主要分为：

• DQL：数据查询语言，用于对数据进行查询，如select
• DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
• TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
• DCL：数据控制语言，进行授权与权限回收，如grant、revoke
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop

操作数据库

操作	格式
创建数据库	create database 数据库名 charset=utf8;
使用指定数据库	use 指定数据库名;
查看当前使用的数据库	select database();
查看所有的数据库	show databases;
删除指定数据库（慎用）	drop database 指定数据库名;
查看创建数据库时的SQL语句	show create database 数据库名;

注意：

• 在创建数据库时若不指定charset=utf8则数据库中不能存储中文

操作表

操作	格式
创建表	create table 表名(字段名1 数据类型 字段约束，字段名2 数据类型 字段约束，...);
查看当前数据库中所有的表	show tables;
删除表（慎用）	drop table 表名;
查看创建表时的SQL语句	show create table 表名;

注意：创建表时，若有外键约束则在最后一行添加foreign key(指定字段) references 指定主表(主键)

操作字段

操作	格式
添加字段	alter table 指定表名 add 字段名 数据类型 字段约束;
修改字段的类型和约束	alter table 指定表名 modify 字段名 数据类型 字段约束;
修改字段的名、类型和约束	alter table 指定表名 change 原字段名 新字段名 数据类型 字段约束，change ...;
为已有字段添加外键约束	alter table 指定从表 add foreign key(指定字段) references 指定主表(主键);
删除表中字段	alter table 指定表名 drop 字段名;
删除表中字段的外键约束	alter table 表名 drop foreign key 外键名;，外键名从show create table 表名;中获取

表数据操作

表数据操作的范围，完全由where 条件控制，即条件筛选出几条数据就操作几条数据，若不添加条件判断则会操作数据库中的所有数据（增除外）

增加表记录

格式：insert into 表名 (字段1,字段2,...) values (字段1的值,字段2的值,...),(一行记录),... ;
要求：字段的顺序与给字段添加记录值的顺序必须完全对应

insert into heros (id,name,age,height,gender) values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');

• 若添加的记录为全字段插入且值的顺序与表结构字段的顺序完全对应时，字段可以省略

  insert into heros values(0, '张飞', 55, 1.82, '男'),(0, '关羽', 58, 1.85, '男');
  

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

删除表记录

1. 物理删除（不建议）
   格式：delete from 表名 where 条件

   delete from heros where name='关羽';
   

   一旦删除数据就不容易恢复 慎用

2. 逻辑删除

   1. 添加是否逻辑删除字段，且设置默认值为0，0表示未删除 1表示删除
      alter table heros add isdelete bit default 0;
   2. 将想要删除数据的逻辑删除字段中的0修改为1
      update heros set isdelete=1 where name='关羽';

   逻辑删除，本质就是修改操作

修改表记录

格式：update 表名 set 字段1=值1,字段2=值2... where 条件

update heros set age=56, height=1.82 where name='张飞';

注意：

• 该语句是修改 经条件筛选后的记录中 对应字段的值，即一次可以修改多条（如条件为 where gender='男'）
• 若修改记录时不添加条件，则会修改表中所有的记录对应字段的值

查询表记录

格式：select 字段1,字段2,... from 表名 where 条件;

select 字段1,字段2,... from heros where name='关羽';

若查询所有字段的数据可以使用*代替字段名（不建议）

select * from heros where name='关羽';

注意：

• 若是连表查询则必须说明是查询那个数据库中的那个字段，单表查询则可以省略表名

  select students.id,students.name,students.gender