个人笔记——MySQL数据库的基本使用

数据库简介

数据库可以视为一种特殊的文件，有自己独特的读写方式，极为高效
mysql（关系型数据库）在Ubuntu中的存储路径为：/var/lib/mysql，需要管理员权限才能访问

特点

• 持久化存储
• 读写速度极高
• 保证数据的有效性
• 对程序支持性特别好，容易扩展

关系型数据库核心元素

数据列：字段
数据行：记录
数据表：数据行的集合
数据库：数据表的集合
能唯一标记某个字段（如第一列）：主键

RDBMS

Relation Database Management System
当前主要类型的数据库有两种：关系型数据库、非关系型数据库，RDBMS是建立在关系型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据
查看数据库排名：https://db-engines.com/en/ranking
关系型数据库的主要产品：

• oracle：在以前的大型项目中使用，银行、电信等项目
• mysql：web时代使用最广泛的关系型数据库
• ms sql server：在微软的项目中使用
• sqlite：轻量级数据库，主要应用在移动平台

SQL

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作oracle、sql server、mysql、sqlite等等所有的关系型数据库
SQL语句主要分为：

• DQL：数据查询语言，用于对数据进行查询，如select
• DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
• TPL：事务处理语言，对事务进行处理，包括begin transaction、commint、rollback
• DCL：数据控制语言，进行授权与权限回收，如grant、revoke
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop
• CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
  对于web程序员而言，重点是数据的crud（增删查改），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其他语言了解即可
  以上不区分大小写

MySQL特点

• 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
• 支持多种操作系统,如Linux、Windows、AIX、FreeBSD、HP-UX、macOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
• 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
• 支持多线程，充分利用CPU资源
• 优化的SQL算法，有效地提高查询速度
• 提供多语言支持，常见的编码如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
• 提供用于管理、检查、优化数据库操作的管理工具
• 大型的数据库。可以处理拥有上千万条记录的大型数据库
• 支持多种存储引擎
• MySQL软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库
• MySQL使用标准的SQL数据语言形式
• MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统

Ubuntu上安装MySQL服务

• 在终端输入如下指令

sudo apt-get install mysql-server

• 启动服务

sudo servvice mysql start

• 查看进程中是否存在mysql服务

ps ajx | grep mysql

• 停止服务

sudo service stop mysql

• 重启服务

sudo service mysql restart

配置

配置文件目录：/etc/mysql/mysql.cnf
vim查看后会显示包含以下两个文件

然后进入配置

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

MySQL客户端

命令行客户端

• 在终端运行如下命令，按提示填写信息

sudo apt-get install mysql-client

• 最基本的连接命令如下

mysql -u root -p mysql
# u后面是用户名 p后面是密码

图形化界面客户端

可以到Navicat官网下载，获取压缩文件，放在桌面解压
然后进入解压的目录，运行如下命令

./start_navicat

连续点击两次取消后开始使用14天版本
如果过期，可以在家目录下删除 .navicat64 文件

数据类型和约束

数据类型

使用数据类型的原则：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
常用数据类型如下：

• 整数：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时推荐使用
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器，然后在表中存储这个文件的保存路径

约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 唯一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写则以填写值为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，回到关联的表中查询此值是否存在，如果存在则填写成功，如不存在则失败并跳出异常
• 注：虽然外键约束可以保证数据的有效性，但是在进行数据的crud时，都会降低数据库的性能，所以不推荐使用。

使用命令行操作数据库

数据库操作

• 打开终端，运行命令

mysql -uroot -p
# 回车后输入密码

• 退出登录

exit / quit / Ctrl + d

• 显示当前所有数据库

show databases；
-- sql语言别忘了最后加分号！！！

• 显示数据库版本

select version();

• 显示时间

select now();

• 创建数据库

creat database 数据库名

• 查看创建数据库的语句

show create database 数据库名;

• 删除数据库

drop database 数据库名

• 使用数据库

use 数据库名;

• 查看当前使用的数据库

select database();

数据表操作

• 查看当前数据库中所有表

show tables;

• 创建数据表

create table 数据表名(字段 类型 约束,字段 类型 约束)
例如：
create table xxxx(id int, name varchar(30));
create table yyyyy(id int primary key not null auto_increment, name varchar(30));
-- primary key标明设置主键； not null为非空； auto_increment为自动增长 
create table students(id int unsigned primary key not null auto_increment, 
								name varchar(30), 
								age tinyint unsigned default 0, 
								high decimal(5, 2), 
								gender enum("男", "女", "保密") default "保密", 
								cls_id int unsigned);
								# 对于数字的类型，遵循够用原则，越小越好
								# 可以使用default设置默认值
								# enum为枚举类型，提供选项
								# unsigned为无符号，即>=0（如不加，则默认正负值都可取）

• 查看数据表结构

desc 数据表名

• 向表中插入数据

insert into 数据表名 values(0, 除主键外的其他值);
# 以上方的students表为例
insert into students values(0, "老王", 18, 180.88, "男", 103);

• 查看表中数据

selet * from students;

• 显示创建表的语句

show create table 表名;

修改表结构

• 添加字段

alter table 表名 add 列名 类型;
alter table students add birthday datetime;

• 修改字段-重命名

alter table 表名 change 原名 新名 类型及约束;
alter table students change birthday birth datetime not null;

• 修改字段-不重命名

alter table 表名 modify 列名 类型及约束;
alter table students modify birth date not null;

• 删除字段

alter table  表名 drop 列名;
alter table students drop birthday;

• 删除表

drop table 表名;
drop table students;

数据的增删改查（curd）

create, update, retrieve, delete

增

完整插入

insert into 表名 values();

前面已经使用过，values()内的第一个值可以取0，也可以使用null或者default，这三者的效果相同，主键自动增长
如果字段中有约束是枚举的，则插入时可以再字段对应的位置使用序号，代替直接输入，例：

insert into students values(0, "老王", 18, 180.88, 1, 103);

部分插入

insert into 表名 (列名, 列名) values (值1, 值2);

多行插入

insert into 表名 (列名, 列名) values (值1, 值2),(值1, 值2);
# 多行插入也可以应用至全部插入
insert into students values(0, "老王", 18, 180.88, 1, 103), (0, "老李", 18, 180.88, 1, 103);

改

update 表名 set 列名1=值1 where 列名2（如id）=值2；
update student set gender=2 where name="老李";
update student set gender=2, age=8 where name="老李";

查

• 全部查看

select * from 表名

• 指定查看

select * from 表名 where 列名=值;
select * from 表名 where id>6;

• 查询指定列

select 列1,列2 from 表名;
# 可以使用as为列或表指定别名
select 列名1 as 别名, 列名2 as 别名 from 表名;

删

delete from 表名;  # 清空数据表
delete from 表名 where 列名=值;

以上是物理删除，是真的从表中删除，实际使用中不要这么做，要使用逻辑删除，如用一个字段来表示是否已删除

alter table students add is_delete bit default 0   # bit只能存储0或1
update students set is_delete=1 where id=1  # 修改想要删除对象的属性值
select * from students where is_delete=0  # 仅显示未删除的信息