数据库基本概念、SQL语言、MySQL库&表&记录&字段的增删改查操作整理
1 数据库概述
1.1 基本概念
1.1.1 相关定义
1.1.2 数据库的发展
-
人工管理阶段
缺点 : 数据存储量有限,共享处理麻烦,操作容易混乱
-
文件管理阶段 (.txt .doc .xls)
优点 : 数据可以长期保存,可以存储大量的数据,使用简单。
缺点 : 数据一致性差,数据查找修改不方便,数据冗余度可能比较大。
-
数据库管理阶段
优点 : 数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用处理
缺点 : 需要使用sql 或者其他特定的语句,相对比较专业,普及性较低
1.1.3 关系型数据库和非关系型数据库
关系型: 采用关系模型(二维表)来组织数据结构的数据库 ,如Oracle 、SQL_Server、 MySQL
非关系型: 不采用关系模型组织数据结构的数据库,如:MongoDB、Redis
2 SQL语言
2.1 什么是SQL
SQL即结构化查询语言(Structured Query Language),一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。SQL的操作对象是DBMS
2.2 SQL语言特点
- SQL语言基本上独立于数据库本身
- 各种不同的数据库对SQL语言的支持与标准存在着细微的不同
- 每条命令以 ; 结尾
- SQL命令(除了数据库名和表名)关键字和字符串可以不区分字母大小写
3 MySQL
1996年,MySQL 1.0发布,作者Monty Widenius, 为一个叫TcX的公司打工,当时只是内部发布。到了96年10月,MySQL 3.11.1发布了,一个月后,Linux版本出现了。真正的MySQL关系型数据库于1998年1月发行第一个版本。MySQL是个开源数据库,后来瑞典有了专门的MySQL开发公司,将该数据库发展壮大,在之后被Sun收购,Sun又被Oracle收购。
官网地址:[https://www.mysql.com/]
3.1 MySQL特点
- 是开源数据库,使用C和C++编写
- 能够工作在众多不同的平台上
- 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
3.2 MySQL的安装
- Windows安装MySQL
- 下载MySQL安装包(windows) https://dev.mysql.com/downloads/windows/installer/8.0.html
- 直接运行安装文件安装
- Linux安装MySQL服务(以Ubuntu为例)
- 终端执行: sudo apt install mysql-server
- 配置文件:/etc/mysql
- 数据库存储目录 :/var/lib/mysql
3.3 启动和连接MySQL服务
-
服务端启动
- 查看MySQL状态 :
sudo service mysql status
- 启动/停止/重启服务:
sudo service mysql start/stop/restart
- 连接数据库
mysql -h 主机地址 -u 用户名 -p
> 注意:
> 1. 回车后输入数据库密码 (我们设置的是123456)
> 2. 如果链接自己主机数据库可省略 -h 选项
-
关闭连接
ctrl-D exit
3.4 MySQL数据库结构
数据元素 --> 记录 -->数据表 --> 数据库
3.5 基本概念解析
- 数据表(table) : 存放数据的表格
- 字段(column): 每个列,用来表示该列数据的含义
- 记录(row): 每个行,表示一组完整的数据
3.6 数据库管理
3.6.1 查看已有库
mysql> SHOW DATABASES;
3.6.2 创建库
mysql> CREATE DATABASE <库名> [character set utf8];;
e.g. 创建stu数据库,编码为utf8
create database stu character set utf8;
create database stu charset=utf8;
注意:库名的命名
- 数字、字母、下划线,但不能使用纯数字
- 库名区分字母大小写
- 不要使用特殊字符和mysql关键字
3.6.3 切换库
mysql> use 库名;
e.g. 使用stu数据库
use stu;
3.6.4 查看当前所在库
mysql> SELECT DATABASE();
3.6.5 删除库 (这个太刑了) [删库后,库中的数据也会随之丢失]
mysql> DROP DATABASE <库名>;
e.g. 删除test数据库
drop database test;
3.7 数据表管理
3.7.1 基本思考过程
- 确定存储内容
- 明确字段构成
- 确定字段数据类型
3.7.2 基础数据类型
3.7.2.1 数值类型:
整数类型:INT,SM ALLINT,TINYINT,MEDIUMINT,BIGINT
对于准确性要求比较高的字段,比如money,用decimal类型减少存储误差。
声明语法是DECIMAL(M,D)。M是数字的最大数字位数,D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字,小数点后占2位,取值范围-9999.99到9999.99。
浮点类型:FLOAT,DOUBLE,DECIMAL
比特值类型:BIT。指0,1值表达2种情况,如真,假
3.7.2.2 字符串类型:
- 普通字符串: CHAR,VARCHAR
- 存储文本:TEXT
- 存储二进制数据: BLOB
- 存储选项型数据:ENUM,SET
注意:
- char:定长,即指定存储字节数后,无论实际存储了多少字节数据,最终都占指定的字节大小。默认只能存1字节数据。存取效率高。
- varchar:不定长,效率偏低 ,但是节省空间,实际占用空间根据实际存储数据大小而定。必须要指定存储大小 varchar(50),MySQL4.1以前,VARCHAR数据类型所支持的最大长度255,5.0以上版本支持65535字节长度,utf8编码下最多支持21843个字符(不为空)。
- enum用来存储给出的多个值中的一个值,即单选,enum(‘A’,‘B’,‘C’)
- set用来存储给出的多个值中一个或多个值,即多选,set(‘A’,‘B’,‘C’)
3.7.3 表的基本操作
3.7.3.1 创建表
mysql> CREATE TABLE <表名>(字段名 数据类型 约束,字段名 数据类型 约束,...字段名 数据类型 约束);
[字段之前用逗号,字段名和数据类型之间用空格]
-字段约束
* 如果你想设置数字为无符号则加上 **UNSIGNED**
* 如果你不想字段为 NULL 可以设置字段的属性为 **NOT NULL**, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
* **DEFAULT** 表示设置一个字段的默认值
* **COMMENT** 增加字段说明
* **AUTO_INCREMENT** 定义列为自增的属性,一般用于主键,数值会自动加1。
* **PRIMARY KEY**关键字用于定义列为主键。主键的值不能重复,且不能为空。
e.g. 创建班级表
create table class (id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned not null,sex enum('w','m'),score float default 0.0);
e.g. 创建兴趣班表
create table hobby (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),level char not null,price decimal(6,2),remark text);
×××小技巧:如何相对高效地输入长篇语句?
1. 打开Pycharm或其他IDE,新建一个普通文档
2. 手动输入后缀名为sql
3. 利用IDE的自动补全插件快速写好,然后粘贴到console(终端的粘贴快捷键是Ctrl + Shift + V)
3.7.3.2 查看数据表
mysql> SHOW TABLES;
3.7.3.3 查看表结构
mysql> DESC <表名>;
3.7.3.4 查看数据表创建信息
mysql> SHOW CREATE TABLE <表名>;
3.7.3.5 删除表
mysql> DROP TABLE <表名>;
3.8 表数据基本操作
在实际开发中,查询操作占据了对数据库操作的半壁江山
3.8.1 插入(insert) [增]
INSERT INTO <表名> VALUES(值1,值2...),(值1,值2...),...;
INSERT INTO <表名> (字段1,...) values (值1,值2...),...;
e.g.
insert into class values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);
insert into class (name,age,sex,score) values ('Lucy',17,'w',81);
3.8.2 查询(select) [查]
SELECT * FROM <表名> [WHERE 条件];
SELECT 字段1,字段2 FROM <表名> [WHERE 条件];
e.g.
select * from class;
select name,age from class;
3.8.3 WHERE子句
where子句在sql语句中扮演了重要角色,主要通过一定的运算条件进行数据的筛选,在查询,删除,修改中都有使用。
3.8.3.1 算数运算符
3.8.3.2 比较运算符
e.g.
select * from class_1 where age > 8;
select * from class_1 where age between 8 and 10;
select * from class_1 where age in (8,9);
注意:在MySQL中,不支持连续不等式的书写
以“分数在70与80之间”为查询条件,常见的有以下写法:
3.8.3.3 逻辑运算符
e.g.
select * from class where sex='m' and age>9;
优先级:算>比>逻
查询小练习
1. 查找30多元的图书
2.查找人民教育出版社出版的图书
3.查找老舍写的,中国文学出版社出版的图书
4.查找备注不为空的图书
5.查找价格超过60元的图书,只看书名和价格
6.查找鲁迅写的或者茅盾写的图书
3.8.4 更新表记录(update) [改]
UPDATE 表名 SET 字段1=值1,字段2=值2,... WHERE <条件>;
e.g.
update class set age=11 where name='Abby';
* 注意:update语句后如果不加where条件,所有记录全部更新
3.8.5 删除表记录(delete) [删]
DELETE FROM 表名 WHERE 条件;
注意:delete语句后如果不加where条件,所有记录全部清空
e.g.
delete from class where name='Abby';
** 注意:delete语句生效后,被删除的记录主键会空闲出来,除非手动添加,否则就一直空着,因为主键最大的作用就是避免记录重复,不强求连续
3.9 表字段的操作(alter)
语法 :ALTER TABLE <表名> 执行动作;
* 添加字段(ADD)
ALTER TABLE <表名> ADD <字段名> 数据类型;
ALTER TABLE <表名> ADD <字段名> 数据类型 FIRST;
ALTER TABLE <表名> ADD <字段名> 数据类型 AFTER <字段名>;
* 删除字段(DROP)
ALTER TABLE <表名> DROP <字段名>;
* 修改数据类型(MODIFY)
ALTER TABLE <表名> MODIFY <字段名> 新数据类型;
* 修改字段名(CHANGE)
ALTER TABLE <表名> CHANGE 旧字段名 新字段名 新数据类型;
e.g.
alter table hobby add tel char(11) after name;
alter table hobby modify tel char(16);
alter table hobby change tel phone char(16);