MySQL数据库应用--数据库概念和SQL基础部分学习笔记整理
数据库概念
术语
- 数据(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会自动设置为当前系统时间。
总结常用数据类型
管理数据库中的表
- 数据库对象的命名规则:
必须以字母开头,后续字符可以是字母、数字和三个特殊字符(#_$)
最大长度不能超过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)
)
检查约束
- 检查约束用于给出检查表达式,设置列值需符合的条件。
- 既可以使用列级约束语法,也可使用表级约束语法,但MySQL均会忽略
CREATE TABLE TB_STUDENT(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
AGE INT CHECK(AGE>18 AND AGE<60)
)
管理约束
- 添加/删除非空约束:
/*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;
- 添加主键、外键、和唯一约束时,均使用表级约束的写法:
/*语法: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);
- 删除主键约束:
- 主键列非自增:
/*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;
- 删除外键约束:
/*ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名*/
ALTER TABLE XM_STUDENT_ DROP FOREIGN KEY XM_ID_
- 删除唯一约束:
/*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
设置自动增长及默认值
练习
- 建立一个“商品”表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)
)
- 建立一个“入库”表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
上一篇: 江米泡多久可以包粽子