MySQL数据库学习笔记

2018-09-10  本文已影响0人  真不懂事

数据库基础

数据库系统概述

数据库技术的发展

  1. 人工管理阶段

特点：1.数据不保存
   2.使用应用程序管理数据
   3.数据不共享且不具有独立性

  1. 文件系统

特点：1.数据可以长期保存
   2.由文件系统管理数据
   3.共享性差，数据独立性差

  1. 数据库系统

特点：满足多应用多用户共享数据的需求

数据库系统的组成

  数据库系统(DataBase System)是采用数据库技术的计算机系统，是由数据库、数据库管理系统、数据库管理员、支持数据库系统的硬件和软件以及用户五部分构成的运行实体。数据库管理员是对数据库进行规划、设计、维护和监视等的专业人员。

数据模型的概念

  数据模型是数据库系统的核心和基础，是关于描述数据与数据之间的联系、数据的语义、数据一致性约束的概念性工具的集合。
  数据模型通常是由数据结构、数据操作和完整性约束三部分组成。
   1. 数据结构：是对系统静态特征的描述，描述对象包括数据的类型、内容性质和数据之间的相互关系。
  2. 数据操作：是对系统动态特征的描述，是对数据库各种实例的操作。
  3. 完整性约束：是完整性规则的集合，它定义了给定数据模型中数据及其联系所具有的制约和依存规则。

常见的数据模型

  1. 层次模型
     用树状结构表示实体类型及实体间联系的数据模型
  2. 网状模型
     用有向图结构表示实体类型及实体间联系的数据模型。编写的程序复杂，独立性差。
  3. 关系模型
     用二维表来描述数据。关系模型数据结构简单、清晰、具有很高的数据独立性，是目前主流的数据库数据模型。

关系模型的术语：
1.关系：一个二维表就是一个关系
2.数组：二维表中的一行，及表中的记录
3.属性：二维表中的一列，用类型和值表示(字段)
4.域：每个属性取值的变化范围
关系中的数据约束：
1.实体完整性约束：主键的属性值不能是空值
2.参照完整性约束：关系之间的基本约束
3.用户定义的完整性约束：反映了具体应用中数据的语义要求

关系数据库的规范化

  关系数据库中每一个关系都要满足一定的规范。分五个等级
1. 第一范式

  (1) 数据库的每个模型只可以包含一个值
  (2) 关系中的每个数组必须包含数量相同的值
  (3） 关系中每个数组一定不能相同

  如果数据表中的每个列都是不可再分割的基本数据项，即同一列中不能有多个值，那么此数据表符合第一范式。数据表中的字段都是单一的，不可分的。
2. 第二范式
  满足第二范式必须满足第一范式。要求数据表中每个实体必须可以被唯一的区分。通常需要为表做一个区分列，这个唯一属性列成为主键列。
3. 第三范式
  满足第三范式必须满足第二范式。第三范式要求关系表中不存在非关键字列对任意候选字列的传递函数依赖，也就是说主键必须唯一的决定关系表中的其他数据，不存在其他字段可以决定字段。

实体与关系

  实体是指客观存在并可相互区别的事物，实体可以是抽象的概念和关系。
   1. 一对一关系
   2. 一对多关系
   3. 多对多关系

数据库的体系结构

数据库三级模式结构

  1. 模式
  2. 内模式
  3. 外模式(是保证数据安全性的一个有力措施)

三级模式之间的映射

  1. 外模式/模式映射
  2. 模式/内模式映射

MySQL

MySQL基础知识

  MySQL目前最流行的开放源码的数据库管理系统，目前属于Oracle公司(世界第二大软件供应商)。它的象征是一只海豚，代表着MySQL数据库和团队的速度、能力、精确和优秀品质。

概念

  数据库就是一个存储数据的仓库。为了方百年数据的存储和管理，他将数据按照特定的规律存储在磁盘上。MySQL是目前运行速度最快的SQL数据管理系统。

优势

  1. 是一款自由的软件
  2. 多用户多线程SQL数据库服务器
  3. 以C/S架构实现
  4. 体积小、速度快、总体拥有成本低、开放源代码

MySQL特性

  MySQL是一个真正的多用户多线程SQL数据服务器。SQL是最流行和最标准化的数据库语言。

  1. 使用c、c++编写，源代码可移植
  2. 支持多种操作系统
  3. 为多种编程语言提供了API(接口)
  4. 支持多线程，充分利用CPU资源
  5. 优化的SQL查询算法，提高查询速度
  6. 既可以作为单独的应用程序，也能作为一个库嵌入其他软件中。提供多语言支持
  7. 提供TCP/IP、ODBC、JDBC等多种数据库链接途径
  8. 提供优化数据库操作的管理工具
  9. 可以处理拥有上千万条纪录的大型数据库

MySQL的应用环境

  目前Internet流行的网站架构方式是LAMP(Linux+Apache+MySQL+PHP)，Linux位操作系统、Apache作为web服务器、MySQL作为数据库、PHP作为服务器端脚本解释器。

MySQL的安装和配置

sudo apt update
sudo apt install mysql-server

注：

3306是mysql的默认端口号

netstat -nat | grep 3306:监听查看3306端口号所链接的程序
netstat -n(numeric)a(all)t(tcp) 查看所有链接的程序
lsof(列出打开的文件) -i:8888查看这个端口号被那个进程占用
man 查看帮助

MySQL的使用

systemctl restart（|start|stop|status ）mysql 或者
service mysql restart|(status|start|stop)重启(查看服务当前状态)mysql
service mysql status 查看mysql状态(对其他系统也可执行此操作来查看状态)
man 命令 获取命令信息

mysql_secure_installation
mysql -u root -p
grant all on . to root @"%" identified by "rootwww59861188";
flush privileges;
exit

mysql -u root -p

mysql -h ip地址 -u root -p

库的操作

show databases [like '模式' where 条件];

show engines;

show variables like 'storg_engine%'
SHOW VARIABLES LIKE 'default_storage_engine';

show status \G; ***分割显示数据库状态
show status; 直接显示

show grants;

create database 库名;
create database 库名 charset 'utf8'; 创建库并修改其编码方式
create database hpx charset = '编码格式'
修改编码方式可防止对库中的表进行操作时报字符类型错误
create database if not exists 库名 没有则创建，有则发出一个警告
create database if not exists hhh default character set utf8 作用是创建一个若不存在的库并将其修改为utf8编码模式
综合起来就是：CREATE DataBase [IF NOT EXISTS] 数据库名[
[DEFAULT] CHARACTER SET [=] 字符集
]
show create database stu_220; 显示创建库时的语句

注：

  1. 不能创建同名数据库
  2. 名字可以由任意字母、阿拉伯数字、下划线和'$'组成，可以是任意字符开头，但不能单独使用数字
  3. 名称最长可为64个字符
  4. 不能使用MySQL关键字作为数据库名、表名
  5. Linux系统数据库名和表名大小写敏感

ALTER DATABASE [库名] [DEFAULT] CHARACTER SET [=] 字符集

drop database 库名; 删除不存在的数据库时会报错，数据库不存在

use 库名;

数据表的操作

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名 [(create_definition,)][table_options][select_statement]

关键字 说明
TEMPORARY 使用该关键字表示创建一个临时表
IF NOT EXISTS 该关键字用于避免表存在时MySQL报告的错误
create_definition 表的列属性部分。表至少要包含一列
table_options 表的一些特性参数，多数情况下不必指定
select_statement select语句描述部分，用它可以快速创建表
参数 说明
col_name 字段名
type 字段类型
NOT NULL|NULL 指出该列是否允许是空值(默认允许为空值)
DEFAULT 默认值 表示默认值
AUTO_INCREMENT 表示是否自增，每个表只能有一个自增列，并且必须被索引
PRIMARY KEY 表示是否为主键。一个表只能有一个主键。若没指定主键但程序需要，将返回第一个没有任何NULL列的UNIQUE键，作为主键
reference_definition 为字段添加注释(必须写)

创建表实例：

create table stu_220(
    id int auto_increment primary key comment'主键',
    user varchar(30) not null  comment'用户',
    password varchar(30) not null primary key comment'密码',
    createtime datetime
)

SHOW [FULL]COLUMNS FROM 数据表名[FROM 库名];
SHOW [FULL]COLUMNS FROM 数据表名.数据库名; 没有成功
DESC 数据表名[列名];

ALTER [INGORE] TABLE 数据表名 alter_spec[.alter_spec ] | table_options
[INGORE]可选项：表示如果出现重复关键的行，则只执行一行，其他的删掉
alter_spec子句：用于定义要修改的内容
table_options：用于指定表的一些特性参数，多数情况下不需要指定
alter_spec子句的语法格式如下表

操作 语句
添加新字段 ADD [COLUMN] create_definition [FIRST|AFTER 列名]
添加索引名称 ADD INDEX[索引名] (索引列名)
添加主键名称 ADD PRIMARY KEY (索引列名)
添加唯一索引 ADD UNIQUE[索引名] (索引列名)
修改字段默认值 ALTER [COLUMN] 字段名 {SET DEFAULT 值|DROP DEFAULT}
修改字段名/类型 CHANGE [COLUMN] 字段名 create_definition
修改子句定义字段 MODIFY [COLUMN] create_definition
删除字段名称 DROP [COLUMN] 字段名
删除主键名称 DROP PRIMARY KEY
删除索引名称 DROP INDEX 索引名
更改表名 RENAME [AS] 新表名

ALTER TABLE允许指定多个操作，动作间使用逗号分隔

CREATE TABLE [IF NOT EXISTS] 表名 {LIKE 源数据表} 该语句为复制一张结构相同的空表
CREATE TABLE [IF NOT EXISTS] 数据表名 AS SELECT * FROM 源数据表

DROP TABLE [IF EXISTS] 数据表名;

truncate table 数据表名;
delete from 列表名

insert into stu(id,name,age,phone,sex,native_place) values(1427,'hpx',22,'11111','男','shanxi');

注：
没有赋值的字段，数据库系统会为其插入默认值。如某个字段没有默认值而且非空，就必须赋值，否则会报错。

INSERT INTO 表名1 (属性列表1) SELECT (属性列表2) FROM 表名2 WHERE 条件表达式

使用这种方法，必须保证字段列表1和2中的字段个数一及对应字段类型都是一样的。如果不一样，数据库系统会报错。

update 表名 set 要修改的值 [where子句] [order by] [limit 行数]

存储引擎及数据类型

存储引擎

什么是存储引擎?

InnoDB

MyISAM

MEMORY

如何选择合适的存储引擎？
  每种引擎都有各自的优势，要根据不同的特性选取合适的引擎

  提供几个选择标准，然后按照标准，选择对应的存储引擎即可，也可以根据常用引擎对比来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。
是否需要支持事务；
是否需要使用设备；
崩溃恢复，能否接受崩溃；
是否需要外键支持；
存储的限制；
对索引和缓存的支持；

修改默认存储引擎

cd /etc/mysql/mysql.conf.d/mysqld.cnf(配置文件))
vi mysqld.cnf
default-storage-engien=InnoDB

学会如何描述问题，用谷歌查找问题并解决

数据类型

数据类型 取值范围 说明 单位
TINYINT 符号值：-127~127 无符号值：0~255 最小的整数 1字节
BIT 符号值：-127~127 无符号值：0~255 最小的整数 1字节
BOOL 符号值：-127~127 无符号值：0~255 最小的整数 1字节
SMALLINT 符号值：-32768~32767 无符号值：0~65355 小型整数 2字节
MEDIUMINT 符号值：-8388608~8388607 无符号值：0~16777215 中型整数 3字节
INT 符号值：-2147683648~2147638647 无符号值：0~4294967295 标准整数 4字节
BIGINT 域很大 大整数 8字节

  默认显示宽度与类型的有符号值最大宽度相同，如tinyint的默认宽度为4。当插入数据的宽度大于设置宽度时，仍可以完整显示。但是数据的宽度不能大于默认宽度。

数据类型 取值范围 说明 单位
FLOAT +(-)3.402823466E+38 单精度浮点数 8或4字节
DOUBLE 双精度浮点数 8字节
DECIMAL 可变 一般整数 自定义长度

选用数字类型遵循原则

  1. 选择最小可用类型
  2. 对于完全都是数字的，可选用整数类型
  3. 浮点类型用于可能具有小数部分的数，如货物单价、付账等
      浮点数参数定义时，可指定宽度和保留几位小数，格式如float(M,D)。建议最好不要使用浮点数，一般情况下选择定点数。超过宽度时，系统会四舍五入，定点数会发出警告。且定点数若不指定宽度，就会默认为整数。

使用字符串类型遵循以下原则

  1. 从速度方面考虑，要选择固定的列，可以使用CHAR类型
  2. 要节省空间，使用动态的列，可以使用VARCHAR类型
  3. 要搜索的内容不区分大小写，可使用TEXT类型
  4. 要搜索的内容区分大小写，使用BLOB类型

日期和时间类型

  日期和时间类型包括：DATETIME、DATE、TIMESTAMP、TIME和YEAR。其中每种类型都有其取值范围，如赋予他一个不合法的值，将会被0代替。如下表。

类型 取值范围 说明
DATE 1000-01-01 9999-12-31 日期，格式YYYY-MM-DD
TIME -838:58:59 835:59:59 时间，格式HH：MM：SS
DATETIME 1000-01-01 00:00:00 9999-12-31 23:59:59 日期和时间，格式YYYY-MM-DD HH：MM：SS
TIMESTAMP 1970-01-01 00:00:00 2037年的某个时间 时间标签，在处理报告时使用显示格式取决于M的值
YEAR 1901-2155 年份可指定两位数字和四位数字的格式

  year指定两位数格式，'00'～'69'表示2000～2069,'70'～'99'表示1970~1999.TIMESTAMP格式输入null或不输入时，会显示系统时间。

insert into food(
    id,name,company,price,produce_time,validity_time,address) values
        (1,'aa','aa',2.5,2005,4,'南方'),
        (null,'cc牛奶','cc牛奶厂',3.5,2009,1,'河北'),
        (null,'ee果冻','ee果冻厂',1.5,2007,2,'北京'),
        (null,'ff咖啡','ff咖啡厂',20,2002,3,'天津'),
        (null,'gg奶糖','gg奶糖厂',14,2003,1,'山西');

create table teacher(
    id int(4) not null unique auto_increment primary key comment'编号，主键',
    num int(10) not null unique comment'教工号',
    name varchar(20) not null comment'姓名',
    sex varchar(4) not null comment'性别',
    birthday datetime comment'出生日期',
    address varchar(50) comment'家庭住址'
);

insert into teacher(num,name,sex,birthday,address)
values(1003,'王五','女','1976-10-30','北京市昌平区');

设置外键

create table  stu(
    id int not null comment '主键',
    phone int comment '电话号码',
    constraint 外键别名 foreign key(外键名)
    references 关联的表名(关联的表的主键)
);
上一篇下一篇

猜你喜欢

热点阅读