做数据图表网站,辽宁建设工程信息网大连,微信推广加人,做搜狗网站排名文章目录 基本概念数据类型数据类型分类 约束主键约束#xff08;PRIMARY KEY#xff09;外键约束#xff08;FOREIGN KEY#xff09;使用非空约束#xff08;not null#xff09;使用唯一性约束#xff08;UNIQUE#xff09;使用默认约束#xff08;DEFAULT#xff… 文章目录 基本概念数据类型数据类型分类 约束主键约束PRIMARY KEY外键约束FOREIGN KEY使用非空约束not null使用唯一性约束UNIQUE使用默认约束DEFAULT设置表的属性值自动增加AUTO_INCREMENT运算符 查询内连接查询 (inner join on)外连接查询 (left/right join on)复合条件连接查询子查询合并查询结果 (union) 基本概念
数据库DataBaseDB
是指长期保存在计算机的存储设备上按照定规则组织起来可以被各种用户或应用共享的数据集合。
数据库管理系统DataBase Management SystemDBMS
是指一种操作和管理数据库的大型软件用于建立、使用和维护数据库对数据库进行统一管理和控制以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。当前比较流行和常用的数据库管理系统有 Oracle、MySQL、SQL Server 和 DB2 等。
数据库系统DataBase SystemDBS
是指在计算机系统中引入数据库后的系统通常由计算机硬件、软件、数据库管理系统和数据管理员组成。
结构化查询语言Structure Query Language、SQL
SQL 为关系型数据库语言的国际标准。
数据库管理系统提供许多功能可以通过 SQL结构化查询语言来定义和操作数据维护数据的完整性和安全性以及进行各种数据库的管理等。那么数据库管理系统所支持的 SQL 提供哪些功能呢? 数据定义语言Data Definition LanguageDDL 数据库管理系统提供了数据定义语言定义数据库涉及各种对象定义数据的完整性约束、保密限制等约束。 CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100),email VARCHAR(100)
);数据操作语言Data Manipulation LanguageDML 数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作有两类检索查询和更新插入、删除和更新)。 SELECT * FROM users WHERE age 18;
INSERT INTO users (name, email, age) VALUES (Alice, aliceexample.com, 25);
UPDATE users SET age 26 WHERE name Alice;
DELETE FROM users WHERE name Alice;数据控制语言Data Control LanguageDCL 数据库管理系统提供了数据控制语言实现对数据库的控制包含数据完整性控制、数据安全性控制和数据库的恢复等。 -- 授予用户对数据库对象的权限
GRANT SELECT, INSERT ON users TO usernamelocalhost;
-- 撤销用户对数据库对象的权限
REVOKE INSERT ON users FROM usernamelocalhost;MySQL
是一款免费开源、小型、关系型数据库管理系统。
数据类型
数据类型分类
MySQL 支持多种数据类型主要有数值类型、日期/时间类型和字符串类型。
数值数据类型 整数类型 TINYINT1字节、SMALLINT2字节、MEDIUMINT3字节、INT4字节、BIGINT8字节浮点小数数据类型 FLOAT4字节、 DOUBLE8字节定点小数类型 DECIMAL(M,D)存储精确的定点数M 为总位数D 为小数位数位类型BIT1~8字节M 是位数范围从 1 到 64。 日期/时间类型 YEAR1字节表示年份TIME3字节表示时间时分秒格式为 ‘HH:MM:SS’DATE4字节表示日期年月日格式为 ‘YYYY-MM-DD’DATETIME8字节表示日期和时间年月日时分秒格式为 ‘YYYY-MM-DD HH:MM:SS’TIMESTAMP4字节表示时间戳需要经常插入或者更新日期为当前系统时刻 字符串类型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。字符串类型又分为文本字符串和二进制字符串。
CHAR 与 VARCHAR 之间的特点与选择
CHAR 是固定长度字符串最大长度为 255 字节存储时如果字符串长度小于 M会用空格填充VARCHAR 是可变长度字符串最大长度为 65,535 字节包括额外的字节用于存储长度信息不会填充空格实际存储的字节数取决于字符串的实际长度CHAR 会自动删除插入数据的尾部空格VARCHAR 不会删除尾部空格CHAR 是固定长度所以它的处理速度比 VARCHAR 的速度要快但是它的缺点就是浪费存储空间适合存储长度相对一致的字符串例如国家代码、邮政编码等VARCHAR 类型在存储时可能会更节省空间适合存储长度变化较大的字符串在处理速度上VARCHAR 可能稍慢于 CHAR但差别通常不显著尤其是在现代数据库中
存储引擎对于选择 CHAR 和 VARCHAR 的影响
对于 MyISAM 存储引擎最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化从而使数据检索更快用空间换时间如果数据长度相对固定且查询性能优先使用 CHAR 类型是一个好的选择对于 InnoDB 存储引擎使用可变长度的数据列因为 InnoDB 数据表的存储格式不分固定长度和可变长度因此使用 CHAR 不一定比使用 VARCHAR 更好但由于 VARCHAR 是按照实际的长度存储比较节省空间所以对磁盘 IO 和数据存储总量比较好优先选择 VARCHAR 类型尤其是在数据长度波动较大时
约束
主键约束PRIMARY KEY
主键又称主码是表中一列或多列的组合。主键约束Primary Key Constraint要求主键列的数据唯一并且不允许为空。主键能够惟一地标识表中的一条记录可以结合外键来定义不同数据表之间的关系并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系它们之间是一一对应的。主键分为两种类型单字段主键和多字段联合主键。
1、单字段主键主键由一个字段组成。 在定义列的同时指定主键语法规则如下 -- 字段名 数据类型 PRIMARY KEY [默认值]
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100),email VARCHAR(100)
);在定义完所有列之后指定主键。 -- [CONSTRAINT 约束名] PRIMARY KEY [字段名]
CREATE TABLE users (id INT,name VARCHAR(100),email VARCHAR(100),CONSTRAINT pk_users_id PRIMARY KEY (id)
);2、多字段联合主键主键由多个字段联合组成。 语法规则如下: -- PRIMARY KEY [字段1,字段2,. . .字段n]
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);外键约束FOREIGN KEY
外键用来在两个表的数据之间建立链接它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性一个表的外键可以为空值若不为空值则每一个外键值必须等于另一个表中主键的某个值。
外键首先它是表中的一个字段它可以不是本表的主键但对应另外一个表的主键。外键主要作用是保证数据引用的完整性定义外键后不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如部门表 tb_dept 的主键是id在员工表 tb_emp 中有一个键 deptld 与这个 id 关联。
主表父表对于两个具有关联关系的表而言包含主键的表被其他表引用从表子表对于两个具有关联关系的表而言包含外键的表引用主表中的主键
创建外键的语法规则如下:
-- [CONSTRAINT 外键名] FOREIGN KEY (字段名1 [,字段名2,…]) REFERENCES 主表名 (主键列1 [,主键列2,…])
-- 如部门表 tb_dept 和一个员工表 tb_emp希望在员工表中添加一个外键以引用部门表的主键 id。
CREATE TABLE tb_dept (id INT PRIMARY KEY,name VARCHAR(100)
);CREATE TABLE tb_emp (emp_id INT PRIMARY KEY,emp_name VARCHAR(100),deptId INT,CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);“外键名” 为定义的外键约束的名称一个表中不能有相同名称的外键“字段名” 表示子表需要添加外键约束的字段列“主表名” 即被子表外键所依赖的表的名称“主键列” 表示主表中定义的主键列或者列组合。
使用非空约束not null
非空约束Not Null Constraint指字段的值不能为空NULL。对于使用了非空约束的字段如果用户在添加数据时没有指定值数据库系统会报错。非空约束的语法规则如下
-- 字段名 数据类型 not null
CREATE TABLE users (id INT PRIMARY KEY,username VARCHAR(50) NOT NULL, -- 用户名不能为空email VARCHAR(100) NOT NULL, -- 电子邮件不能为空created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);需要在已存在的表中添加非空约束可以使用 ALTER TABLE 语句。例如
ALTER TABLE users
MODIFY COLUMN email VARCHAR(100) NOT NULL;使用唯一性约束UNIQUE
唯一性约束Unique Constraint要求该列唯一允许为空但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。唯一性约束的语法规则如下 在定义完列之后直接指定唯一约束语法规则如下 -- 字段名 数据类型 UNIQUE
CREATE TABLE users (id INT PRIMARY KEY,username VARCHAR(50) UNIQUE, -- 用户名必须唯一email VARCHAR(100) UNIQUE, -- 电子邮件必须唯一created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);在定义完所有列之后指定唯一约束语法规则如下 -- [CONSTRAINT 约束名] UNIQUE(字段名)
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);UNIQUE 和 PRIMARY KEY 的区别 一个表中可以有多个字段声明为 UNIQUE但只能有一个 PRIMARY KEY 声明 声明为 PRIMARY KEY 的列不允许有空值但是声明为 UNIQUE 的字段允许空值NULL的存在。
使用默认约束DEFAULT
默认约束Default Constraint指定某列的默认值当插入一条新记录时如果该列没有显式提供值数据库系统将自动使用默认值。如男性同学较多性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值那么系统会自动为这个字段赋值为‘男’。默认约束的语法规则如下:
-- 字段名 数据类型 DEFAULT 默认值
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,gender VARCHAR(10) DEFAULT 男, -- 默认值为 男age INT DEFAULT 18, -- 默认值为 18enrolled_date DATE DEFAULT CURRENT_DATE
);需要在已存在的表中添加默认约束可以使用 ALTER TABLE 语句。例如
ALTER TABLE students
ALTER COLUMN gender SET DEFAULT 男;设置表的属性值自动增加AUTO_INCREMENT
AUTO_INCREMENT 关键字是一种用于自动生成唯一整数值的特性通常用于指定表的主键字段以确保每条记录都有唯一的标识符。默认在 MySQL 中 AUTO_INCREMENT 的初始值值是1每新增一条记录字段值自动加 1。一个表只能有一个字段使用 AUTO_INCREMENT 约束且该字段必须为主键的一部分。 AUTO_INCREMENT 约束的字段可以是任何整数类型TINYINT、SMALLIN、INT、BIGINT等。设置表的属性值自动增加的语法规则如下
-- 字段名 数据类型 AUTO_INCREMENT
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, -- 主键自动增加username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL
);运算符
LIKE 运算符LIKE 运算符是 SQL 中用于进行模式匹配的运算符以便在查询中筛选符合特定模式的记录LIKE 运算符与通配符结合使用可以实现灵活的字符串匹配
百分号 (%)匹配任何数目的字符甚至包括零字符。例如‘a%’ 匹配以字母 a 开头的任何字符串包括 a、abc、a123 等下划线 (_)只能匹配一个字符。例如‘a_’ 匹配以字母 a 开头且后面跟着一个字符的字符串如 ab、a1、a_ 等。
查询 连接查询
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中表建立时各数据之间的关系不必确定常把一个实体的所有信息存放在多个表中。当查询数据时通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。
内连接查询 (inner join on)
内连接INNER JOIN返回两个表中匹配的记录组合成新的记录。只有当两个表中存在匹配的记录时结果集才会包含这些记录。
连接条件同时满足的数据行
SELECT 列名1, 列名2, ...
FROM 表1
INNER JOIN 表2 ON 表1.字段名 表2.字段名;SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id orders.user_id;外连接查询 (left/right join on)
外连接查询将查询多个表中相关联的记录以及不匹配的记录。内连接时返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据即返回查询结果集合中的不仅包含符合连接条件的行还包括左表左外连接或左连接、右表右外连接或右连接或两个边接表全外连接中的所有数据行。外连接分为左外连接或左连接、右外连接或右连接、全外连接 LEFT JOIN左连接返回包括左表中的所有记录、右表中与连接条件匹配的记录。如果右表没有匹配结果中的右表字段将为 NULL SELECT 列名1, 列名2, ...
FROM 表1
LEFT JOIN 表2 ON 表1.字段名 表2.字段名;-- 返回所有用户即使他们没有任何订单未匹配的订单 ID 将显示为 NULL
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id orders.user_id;RIGHT JOIN右连接返回包括右表中的所有记录、左表中与连接条件匹配的记录。如果左表没有匹配结果中的左表字段将为 NULL SELECT 列名1, 列名2, ...
FROM 表1
RIGHT JOIN 表2 ON 表1.字段名 表2.字段名;-- 返回所有订单即使某些订单没有对应的用户未匹配的用户字段将显示为 NULL。
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id orders.user_id;FULL OUTER JOIN全外连接返回两个表中所有的记录匹配的记录会合并在一起未匹配的记录会在另一侧显示为 NULL SELECT 列名1, 列名2, ...
FROM 表1
FULL OUTER JOIN 表2 ON 表1.字段名 表2.字段名;SELECT employees.name, departments.dept_name
FROM employees
FULL OUTER JOIN departments ON employees.dept_id departments.dept_id;复合条件连接查询
复合条件连接查询是在连接查询的过程中通过添加过滤条件限制查询的结果使查询的结果更加准确。可以使用 AND 或 OR 操作符来组合多个条件。
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id orders.user_id
AND orders.status completed;子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。通常用于在外层查询中提供额外的条件或数据。 单行子查询单行子查询返回一个单一的值单行单列 SELECT name
FROM employees
WHERE salary (SELECT AVG(salary) FROM employees);多行子查询多行子查询返回多行数据通常与 IN、ANY 或 ALL 运算符结合使用 SELECT name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location New York);相关子查询相关子查询依赖于外层查询的值。每次外层查询执行时相关子查询都会被重新执行 SELECT e1.name
FROM employees e1
WHERE e1.salary (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.dept_id e2.dept_id);使用子查询作为新表可以使用子查询作为新表通常称为派生表或临时表来简化数据处理和查询逻辑。这种方法允许将子查询的结果作为一个表供外层查询使用。 SELECT 列名1, 列名2, ...
FROM (SELECT 列名1, 列名2, ...FROM 表名WHERE 条件) AS 子查询别名
WHERE 其他条件;SELECT d.dept_name, avg_data.avg_salary
FROM (SELECT dept_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY dept_id) AS avg_data
JOIN departments d ON avg_data.dept_id d.dept_id
WHERE avg_data.avg_salary 50000;合并查询结果 (union)
利用 UNION 关键字可以给出多条 SELECT 语句并将它们的结果组合成单个结果集。合并时两个表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔。UNION 不使用关键字 ALL执行的时候删除重复的记录所有返回的行都是唯一的使用关键字 ALL 的作用是不删除重复行也不对结果进行自动排序。
返回所有记录去重不会有重复的记录
SELECT name, email FROM employees
UNION
SELECT name, email FROM contractors;返回所有记录包括所有重复记录
SELECT name, email FROM employees
UNION ALL
SELECT name, email FROM contractors;
