欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MySQL数据库应用--数据库概念和SQL基础部分学习笔记整理

程序员文章站 2022-03-31 22:09:38
数据库概念术语数据（data）对客观事物的（抽象）符号表示，如图形符号、数字、字母等，数据是数据库中存储的基本对象。数据库（1）是长期存储在计算机内的、有特定组织结构的、可共享的数据集合。（2）数据冗余度小，可以节省数据的存储空间。数据库管理系统（Database Managerment System，简称DBMS）数据库管理系统是管理数据库的软件，数据库管理系统不仅能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。数据库系统...

数据库概念

术语

  • 数据（data）

对客观事物的（抽象）符号表示，如图形符号、数字、字母等，数据是数据库中存储的基本对象。

  • 数据库

（1）是长期存储在计算机内的、有特定组织结构的、可共享的数据集合。
（2）数据冗余度小，可以节省数据的存储空间。

  • 数据库管理系统（Database Managerment System，简称DBMS）

数据库管理系统是管理数据库的软件，数据库管理系统不仅能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。

  • 数据库系统（Database System简称DBS）

数据库及其管理软件组成的系统。是一个实际可运行的存储、维护和应用系统提供数据的软件系统

  • 数据持久化

数据持久化就是将内存中的数据模型转换为存储模型，以及将存储模型转换为内存中的数据模型的统称，数据模型可以是任何数据结构或对象模型，存储模型可以是关系模型、XML、二进制等。

  • 企业应用中数据存储面临的问题

（1）数据量较大，如何保证检索和访问效率
（2）如何保证数据的一致性和完整性
（3）如何解决数据的共享和安全

  • 数据库的发展史

文件系统：使用磁盘文件来存储数据
第一代数据库：网状模型、层次模型的数据库
第二代数据库：关系型数据库和结构化查询语言（SQL）

关系型数据库

  • 关系型数据库采用表组织数据，一个数据库有许多个表组成，多个表数据之间存在着关系，包括一对一、一对多、多对多等关系。
  • 目前被广泛使用的关系型数据库管理系统有Oracle、DB2、Sydbse、SQLServer、Mysql等。
  • 关系型数据库是二维表的集合。
  • 表中的每一行也叫一条记录，代表一个具体实体的数据。
  • 列是实体属性的映射，用于描述的实体的属性值，列也被称为字段。

MySQL数据库

MySQL是被广泛使用的开源的数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛应用在Internet上的中小型网站中，

  • MySQL是一种关系型数据库管理系统。
  • MySQL有快速、可靠和易用的特点。
  • mysql工作在客户端/服务器模式下。

MySql中的几个命令

  • SHOW DATABASES
    功能： 列出MySQL服务器中的数据库。
  • CREATE DATABASE 数据库名
    功能： 用给定的名字创建一个数据库
  • DROP DATABASE 数据库名
    功能： 删除数据库中的所有表以及数据
  • USE 数据库名
    **功能：**把指定数据库作为当前数据库使用，用于后续语句
  • SHOW TABLES
    功能： 显示当前数据库中已有的表
  • DESC 表名
    功能： 查看表中各列的信息

SQL

结构化查询语言（Structured Query Language
SQL是在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。

使用SQL语句可以完成如下任务：

  • 从数据库中检索需要的信息
  • 对数据库的数据进行更新
  • 改变数据库的结构
  • 更改系统的安全设置
  • 增加用户对数据库或表的许可权限
    SQL语句主要可以划分为以下四类：
  • DDL（Data Definition language）:数据库定义语言
    对数据库对象（库、表、列、索引）的操作。
create drop alter rename truncate
  • DML（Data Manipulation Language）:数据操作语言
    对数据库记录的操作
insert delete update select
  • DCL（Data Control Language）：数据控制语言
    对数据库、表、字段、用户的访问权限进行控制。
grant revoke
  • Transaction Control：事务控制
commit rollback savepoint

SQL语句的基本书写规范

书写规范：

sql语句的关键字不区分大小写
SQL语句可单行或多行书写，以“;”结尾
关键字不能夸多行。

注释：

SQL标准：/多行注释/
MySQL单行注释：#

MySQL支持的数据类型

  • MySQL支持所有标准SQL中的数值类型
  • MySQL还扩展了TINYINT,MEDIUMINT,BIGINT这三种整型，用来存放数据的BIT类型。
  • mysql也支持字符串和日期类型

整数类型

类型 字节 范围（有符号） 范围（无符号）
TINYINT 1 -128,127 0,255
SMALLINT 2 -32768,32767 0,65535
MEDIUMINT 3 -8388608,8388697 0,16777215
INT/INTEGER 4 -2147482648,2147483647 0,4294967295
BIGINT 8

TINYINT SMALLINT MEDIUMINT INT BIGINT可以指定显示宽度。如：INT(10)，显示宽度取值范围从1-255，它表示该列值显示的字符位数，不影响他能够存储的值得范围。
如果定义整数列时没有指明显示宽度，则缺省显示宽度为该类型的最长为数，INT类型就是11位。
可使用UNSIGNED修饰符表示无符号整数类型。

实数类型

  • DECLMAL（M,D）

M是数字最大位数，范围1-65；D是小数点后面的数字个数，范围0-30，不得超过M。如果未给出M和D，等效于DECIMAL(10,0)。DECIMAL数据类型在内存中以字符串存储，用于精确度要求非常高的计算。

  • FLOAT

4字节 单精度浮点型

  • DOUBLE(M,D)

8字节 双精度浮点型

注：
1、浮点类型不能是unsigned 的，起取值范围也与整型不同，这些类型有最大值，而且还有最小非零值。
2、M和D对float和Double都是可选的，但对decimal是必须的。

字符串类型

  • CHAR(M)、VARCHAR(M)
    CHAR(M) 为固定长度存储。M最大取值为255，单位为字符。如未提供M，默认长度为1。实际长度不足则用空格填充。
    VARCHAR(M) 类型为变长字符串，M为最大长度，单位为字符。M不能超过记录行最大长度65535字节，在UTF-8字符集下，则为21845字符。
  • TEXT 文本数据，最大65535字节
  • longtext 字符大文本数据，最大65535字节
  • 字节数据类型：
    BLOB二进制形式的长文本数据，最大65535字节
    LONGBLOB 二进制大文本数据，最大4294967295字节

日期/时间类型

  • DATE、 TIME、 DATETIME
    可以是引号引起来的字符串，也可以是数值。
    DATE TIME DATETIME值得格式分别为“YYYY-MM-DD”、“hh:mm:ss”、“YYY-MM-DD hh:mm:ss”
  • TIMESTAMP
    时间的值主要由MySQL自动设置，用于记录某个记录的更改或创建时间。也可以插入一个null值，MySQL会自动设置为当前系统时间。

总结常用数据类型

MySQL数据库应用--数据库概念和SQL基础部分学习笔记整理

管理数据库中的表

  • 数据库对象的命名规则：

必须以字母开头，后续字符可以是字母、数字和三个特殊字符（#_$）
最大长度不能超过64字符
不能使用mysql的保留字
同一个数据库中的对象不能同名。

  • 创建表：
CREATE TABLE students(
			student_id INT PEIMARY KEY NOT NULL UNIQUE,
			student_name VARCHAR(20) NOT NULL,
);
  • 删除表
DROP TABLE student;
  • 修改表

1、修改列类型

ALTER TABLE students MODIFY students CHAR(20);

不是任何情况都可以修改的，当字段只包含空值时，类型、大小都可以修改，否则修改可能不能成功
2、增加列

ALTER TABLE students ADD sex VARCHAR(20);

3、删除列

ALTER TABLE students DROP COLUMN sex;

4、更改表名

RENAME TABLE students to student_new;

约束

约束是在表上所定于的数据校验规则，用于保证数据的完整性。

  • 大部分数据库支持下面五种约束：

NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 检查

创建约束的时机

  • 在建表的同时定义约束
  • 建表后添加约束

列级约束与表级约束

  • 列级约束，在定义列时说明约束。
    语法：
    colName colType [primary key | not null | unique | check() | references]
CREATE TABLE students(
	id 	int PRIMARY KEY NOT NULL UNIQUE CHECK()
);
  • 在所有列定义完毕之后，在进行约束定义。
CREATE TABLE xm_student(
		id INT,
		name VARCHAR(20),
		sex VARCHAR(20),
		CONSTRAINT stu_pk PRIMARY KEY(ID)
)
  • 表级约束，在所有列定义完毕之后，在进行约束定义。
    语法：

主键约束(PRIMARY KEY)

  • 主键是表中唯一确定一行记录的字段。主键约束从功能上看相当于非空且唯一。
  • 一个表中只允许有一个主键。主键可以是单字段或是多字段的组合，多列联合主键只能使用表级约束语法。
  • 当建立主键约束时，MySQL会自动为主键对应的索引。
CREATE TABLE tb_student(
		id INT PRIMARY KEY,
		name VARCHAR(20),
)

非空约束（NOT NULL）

非空约束用来确保字段值不为空，该约束只能使用列级约束语法。

CREATE TABLE XM_STUDENT(
		ID INT PRIMARY KEY,
		NAME VARCHAR(20) NOT NULL
)

唯一约束

  • 确保该约束对应的字段或字段组合不出现重复值。
  • 具有唯一性约束的字段允许为NULL，
  • 同一张表内可创建多个唯一约束。
  • 唯一约束可由多列组合而成，此时只能使用表级约束语法。
  • MySQL会自动为具有唯一约束的列建立索引。
CREATE TABLE XM_NAME(
		ID INT PRIMARY KEY,
		NAME VARCHAR(20),
		CONSTRAINT UQ_NAME UNIQUE(NAME)
)

外键约束

  • 外键是构建于一个表的两个字段或者两个表的两个字段之间的关系。
  • 外键约束确保了相关的两个字段的如下关系：

（1）子表被外键约束的列，其参照的只能是主表具有主键约束或唯一约束的列；
（2）子表被外键约束的列，其值必须在主表列值的范围内，或者为空。
（3）当主表的记录被子表参照时，主表记录不允许被删除

/*子表tb_emp被主表xm_student约束*/
CREATE TABLE tb_emp(
	employee_id INT PRIMARY KEY,
	employee_name VARCHAR(20),
	dept_id INT,
	CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES XM_STUDENT(id)
)

检查约束

  1. 检查约束用于给出检查表达式，设置列值需符合的条件。
  2. 既可以使用列级约束语法，也可使用表级约束语法，但MySQL均会忽略
CREATE TABLE TB_STUDENT(
		ID INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20),
		AGE INT CHECK(AGE>18 AND AGE<60)
)

管理约束

  1. 添加/删除非空约束：
/*ALTER TABLE TableName MODIFY ColName ColType {NULL/NOT NULL}*/
-- 添加
ALTER TABLE XM_STUDENT MODIFY ID int NOT NULL;
-- 删除
ALTER TABLE XM_STUDENT MODIFY ID INT NULL;
  1. 添加主键、外键、和唯一约束时，均使用表级约束的写法：
/*语法：ALTER TABLE TableName ADD [constraint consName] consType(column);*/
ALTER TABLE XM_STUDENT ADD CONSTRAINT PK_ID PRIMARY KEY(ID);
ALTER TABLE XM_STUDENT ADD PRIMARY KEY(ID);
  1. 删除主键约束：
  • 主键列非自增：
/*ALTER TABLE 表名 DROP PRIMARY KEY*/
ALTER TABLE XM_STUDENT DROP PRIMARY KEY;
  • 主键列为自增
/*ALTER TABLE 表名 MODIFY 列名 INT,DROP PRIMARY KEY;*/
ALTER TABLE XM_STUDENT MODIFY XM_ID INT,DROP PRIMARY KEY;
  1. 删除外键约束：
/*ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名*/
ALTER TABLE XM_STUDENT_ DROP FOREIGN KEY XM_ID_
  1. 删除唯一约束：
/*ALTER TABLE 表名 DROP INDEX 约束名*/
ALTER TABLE XM_STUDENT DROP INDEX NAME;

总结：

-- 创建主表
CREATE TABLE XM_STUDENT(
	XM_ID INT,
	XM_NAME VARCHAR(20)
)
-- 删除表
DROP TABLE xm_student_;
-- 创建子表
CREATE TABLE XM_STUDENT_(
	xm_id_ INT AUTO_INCREMENT PRIMARY KEY,
	XM_name_ VARCHAR(20)
)
-- 添加主键约束
ALTER TABLE XM_STUDENT ADD PRIMARY KEY(xm_id);
-- 添加非空约束
ALTER TABLE XM_STUDENT MODIFY XM_ID INT NOT NULL;
-- 添加唯一约束
ALTER TABLE xm_student ADD CONSTRAINT uq_name UNIQUE(xm_name);
ALTER TABLE xm_student ADD UNIQUE(xm_name);
-- 添加外键约束
ALTER TABLE XM_STUDENT_ ADD CONSTRAINT fk_id FOREIGN KEY(xm_id_) REFERENCES XM_STUDENT(xm_id);
-- 删除主键约束
ALTER TABLE xm_student DROP PRIMARY KEY;
-- 删除带自增的主键
ALTER TABLE XM_STUDENT_ MODIFY xm_id_ INT,DROP PRIMARY KEY;
-- 删除唯一约束
ALTER TABLE xm_student DROP INDEX uq_name;
ALTER TABLE xm_student DROP INDEX xm_name;

自动增长和默认值

  • auto_increment
    适用于int类型的主键列，列值将自动增长，一个表只能有一个列有 auto_increment。
  • default 子句用来给列设置默认值。
    示列： 
CREATE TABLE DEPTS(
	DEPT_ID INT PRIMARY KEY AUTO_INCREMENT,
	DEPT_NAME VARCHAR(30),
	DEPT_DESC VARCHAR(100)DEFAULT'部门描述的默认内容'
)

表的存储引擎

  • MySQL在存储数据时，可根据应用的特点选用不同的存储引擎。不同的存储引擎使用不同的存储机制、索引技术，以及对事物的支持等。

MyISAM:适用于大量读写，很少进行记录的更新和删除操作，且对事务的并发和完整性要求不高的场合。
InnoDB：适用于对事务完整性要求高，且存在大量并发事务的情况。
MEMORY：表中的数据存放在内存中，访问效率极高。但一旦服务关闭，数据全部丢失。
MERGE：是一组MyISAM表的组合，适用于单个MyISAM表满足不了容量要求的情况。
默认情况创建的表，将使用InnoDB存储引擎。

总结

1.有关数据库的操作

SHOW DATABASES;
CREATE DATABASE DatabaseName;
DROP DATABASE [IF EXISTS] DatabaseName;
USE DatabaseName;

2.有关数据表的操作

SHOW TABLES;
DESC tableName;
CREATE TABLE tableName(…);
DROP TABLE tableName;
ALTER TABLE tableName;

3.列类型

数值类型：INT、 DECIMAL、 FLOAT、 DOUBLE
日期类型：DATE、 TIME、 DATEIME
字符串类型：CHAR、 VARCHAR、TEXT、BLOB

约束

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
设置自动增长及默认值

练习

  1. 建立一个“商品”表goods_info，它由”商品编号”goods_id、”商品名”goods_name、”生产厂家”manufactory、”库存”stock五个属性组成。其中”商品编号”为主键，”商品名”不能为空且具有唯一性。
CREATE TABLE goods_info(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(20) UNIQUE NOT NULL,
	manufactory VARCHAR(20),
	stock VARCHAR(20)
)
  1. 建立一个“入库”表incoming，由”编号”id，”商品编号”goods_id ，”入库日期”in_date，”生产日期”produce_date”数量”quantity等5个属性组成。其中”编号”是主键，”商品编号”具有外键约束且不能为空。
CREATE TABLE incoming(
	id INT PRIMARY KEY,
	goods_id INT,
	in_date DATE,
	produce_date DATE,
	quantity INT,
	CONSTRAINT fk_id FOREIGN KEY(goods_id) REFERENCES goods_info(goods_id)	
)

本文地址：https://blog.csdn.net/achuxin123/article/details/107445059

相关标签： mysql sql

上一篇： nginx+php-fpm配置文件的组织结构介绍

下一篇： 创建属于其他Session的进程

推荐阅读