MySql基础及约束
review #数据库 #MySQL
数据库
数据库基础知识
- 存储数据的仓库,数据是有组织的存储的
- 英文：database，简称DB
数据库管理系统是管理数据库的大型软件，英文为DataBase Managerment System 简称DBMS
关系表数据库
SQl是结构化查询语言，用来操作关系型数据库，定义了操作所有关系型数据库的统一标准。常见的关系型数据库有：Oracle、MySQL、SQL Server、SQLite（常用于嵌入式系统，轻量化数据库）、DB2、MariaDB
关系表数据库是建立在关系模型基础上的数据库，简单说关系数据库是由多张能相互连接的二维表组成的数据库
-
优点：
- 使用表结构，格式一致便于维护
- 使用通用SQL语言操作，使用方便，便于复杂查询
- 数据存储在磁盘，安全
Navicat快捷键
- ctrl+q 打开查询窗口
- ctrl+/ 注释sql语句
- ctrl+shift +/ 解除注释
- ctrl+r 运行查询窗口的sql语句
- ctrl+shift+r 只运行选中的sql语句
- F6 打开一个mysql命令行窗口
- ctrl+d （1）：查看表结构详情，包括索引 触发器，存储过程，外键，唯一键;（2）：复制一行
- ctrl+l 删除一行
- ctrl+n 打开一个新的查询窗口
- ctrl+w 关闭一个查询窗口
- ctrl+tab 多窗口切换
- Navicat for MySQL 快捷键(cnblogs.com)
通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- MySQL数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 注释
- 单行注释: 一 注释内容 或#注释内容(MySQL 特有)
- 多行注释: /* */
- SQL分类
- DDL(Data Definition Language)数据定义语言，用来定义数据库对象：数据库，表，列等
- DML(Data Manipulation Language)数据操作语言，用来对数据库中表的数据进行增删改
- DQL(Data Query Language)数据查询语言，用来查询数据库中表的记录（数据）
- DCL(Data Control Language)数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户
DDL数据定义语言
库操作
- 查看数据库:
SHOW DATABASES;
- 创建数据库db1:
CREATE DATABASE DB1;
- 判断数据库是否存在并创建数据库:
CREATE DATABASE IF NOT EXISTS DB2;
- 删除数据库:
DROP DATABASE IF EXISTS DB2;
- 使用数据库:
USE DB1;
- 查看当前用的数据库:
SELECT DATABASE();
表操作
- 查看表:
SHOW TABLES;
#查看表详细信息
SHOW CREATE TABLE 表名;
- 查看表结构:
DESC 表名;
- 创建表:
CREATE TABLE FIRSTTABLE(
ID INT(4),#字段1 字段类型(长度) [完整性约束],
SNAME VARCHAR(20),
AGE INT(3)
)#创建表
数据类型:
| 数据类型 | 大小 | 描述 | 分类 |
|---|---|---|---|
| TINYINT | 1 byte | 小整数值 | 数值类型 |
| SMALLINT | 2 bytes | 大整数值 | |
| MEDIUMINT | 3 bytes | 大整数值 | |
| INT或INTEGER | 4 bytes | 大整数值 | |
| BIGINT | 8 bytes | 极大整数值 | |
| FLOAT | 4 bytes | 单精度浮点数值 | |
| DOUBLE | 8 bytes | 双精度浮点数值(score double(总长度,小数点后保留的位数) 如( 0~100, 2) | |
| DECIMAL | 小数值 | ||
| DATE | 3 | 日期值 | 日期和时间类型 |
| TIME | 3 | 时间值或持续时间 | |
| YEAR | 1 | 年份值 | |
| DATETIME | 8 | 混合日期和时间值 | |
| TIMESTAMP | 4 | 混合日期和时间值，时间戳 | |
| CHAR | 0-255 bytes | 定长字符串 | 字符串类型 |
| VARCHAR | 0-65535 bytes | 变长字符串 | |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 | |
| TINYTEXT | 0-255 bytes | 短文本字符串 | |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 | |
| TEXT | 0-65 535 bytes | 长文本数据 | |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 | |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 | |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 | |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
CREATE TABLE STUDENT(
ID INT,
NAME VARCHAR(10),
GENDER CHAR(1),
BIRTHDAY DATE,
SCORE DOUBLE(5,2),
EMEAL VARCHAR(64),
TEL VARCHAR(15),
STATUS TINYINT
);
删除表:
DROP TABLE 表名; # DROP TABLE if exists 表名;
修改表:
- 改表名
ALTER TABLE 表名 RENAME 新表名;
- 添加列:
ALTER TABLE 表名 ADD 列名 数据类型;
- 修改表数据类型:
-- 方法1
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 方法2
ALTER TABLE FIRSTTABLE ADD ctctInfo INT ( 20 ) AFTER AGES;
#添加字段联系方式到first首位,after尾;after 字段名添加到某字段后
- 改表名和数据类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
- 删除列:
ALTER TABLE 表名 DROP 列名;
DML数据操作语言
增
- 给指定列添加数据：插入全部列时可省略列表（不建议）
INSERT INTO 表名(列名1，列名2....) VALUES (值1，值2....);
INSERT INTO
表名(列名1，列名2，…) VALUES(值1，值2，…)，(值1，值2，…)，(值1，值2，…)…;
-- 批量插入数据
INSERT INTO0 表名 VALUES(值1，值2，…)，(值1，值2，…)，(值1，值2，…)…
改
- 查看表内数据:
UPDATE 表名 SET 字段1=值1[,字段2=值2…] [WHERE 条件]
# UPDATE student SET BIRTHDAY=19991206,SCORE=90 WHERE `NAME`='李四';
删
- 删除表内数据:
DELETE FROM 表名 [WHEEE 条件];
注意如果update和delete没有加where条件会修改表中全部数据
DQL数据查询语言
查询分为：基础查询、条件查询（WHERE)、分组查询（GROUP BY）、排序查询（ORDER BY）、分页查询（LIMIT）
- 查询语法格式
| 编写顺序 | 执行顺序 |
|---|---|
| SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 分页限定; |
FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 SELECT 字段列表 ORDER BY 排序字段列表 LIMIT 分页参数 |
编写顺序
SELECT
字段列表
FROM
表名列表(这个表可为Select查询得到的表,可为连接得到的表)
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定;
执行顺序
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
SELECT
字段列表
ORDER BY
排序字段列表
LIMIT
分页参数
基础查询
- 查询格式
- 注意：distinct 可去除重复数据，必须直接放在列名前。只能作用于当前查询所有列，不能部分使用
SELECT [ALL|DISTINCT] select_list
[INTO new table]
FROM table source
[WHERE search conditions]
[GROUP BY group_by_expressioni]
[HAVING search conditions]
[ ORDER BY order_expression [ASC |DESC]]
- 查看表内数据:
SELECT column_name,column_name
FROM table_name;#查看部分列
SELECT * FROM table_name;#查看全部列
注意: 不建议使用select * 操作
- AS别名: as也可省略换成空格
select `name`,math as '数学',english as '英语' FROM stu;#输出结果列名为 name 数学 英语
条件查询
- 查看表内数据:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
# SELECT * FROM Websites WHERE country='CN';
WHERE 子句用于提取那些满足指定条件的记录。
注意：null 是大于所有数值型还是小于所有数值型是由 DBMS 决定的
| 运算符 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于，注:\在 SQL 的一些版本中，该操作符可被写成 != |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| && || ! And or not | 与或非(逻辑运算)，and优先级大于or |
| Like | Like常和通配符搭配模糊查询,搜索某种模式,如SELECT * FROM stu WHERE `NAME` LIKE '%德%'; |
| < = > | 安全等与,可判断null和普通数值 |
| Between X And Y | 在某个范围内(x到y之间) |
| IN | 指定针对某个列的多个可能值 |
SQL 逻辑运算符：
这是在 SQL 所有的逻辑运算符的列表。SQL 运算符_w3cschool
| 运算符 | 描述 |
|---|---|
| ALL | ALL运算符用于将值与另一个值集中的所有值进行比较。 |
| AND | AND运算符允许在SQL语句的WHERE子句中指定多个条件。 |
| ANY | ANY运算符用于根据条件将值与列表中的任何适用值进行比较。 |
| BETWEEN | BETWEEN运算符用于搜索在给定最小值和最大值内的值。 |
| EXISTS | EXISTS运算符用于搜索指定表中是否存在满足特定条件的行。 |
| IN | IN运算符用于将值与已指定的文字值列表进行比较。例如：选择语句 where 列名 [NOT] IN（常量1, 常量2,…常量n） |
| LIKE | LIKE运算符用于使用通配符运算符将值与类似值进行比较。 |
| NOT | NOT运算符反转使用它的逻辑运算符的含义。 例如：NOT EXISTS, NOT BETWEEN, NOT IN等等，这是一个否定运算符。 |
| OR | OR运算符用于组合SQL语句的WHERE子句中的多个条件。 |
| IS NULL | IS NULL运算符用于将值与NULL值进行比较。 |
| UNIQUE | UNIQUE运算符搜索指定表的每一行的唯一性(无重复项)。 |
通配符:
| % | 任意个字符 |
|---|---|
| _ | 单个字符 |
| \ | 转义符可转义通配符 |
| escape | '_$_%' escape '$' #转义$为转义符 |
排序查询
- 语法: ASC升序, DESC降序，多排序条件前边值一样时才会根据第二条件排序
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1]，排序字段名2 [排序方式2]...;
分组查询
- 分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意: 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
-
where和having 区别:
- 执行时机不一样：where 是分前讲行限定where条件、 不满足having分组之后对结果进行过滤。
- 可判断的条件不一样：where不能对聚合函数进行判断, having可以。
执行顺序: where > 聚合函数 > having
-
查看表内数据:
SELECT * FROM 表名;
聚合查询
聚集函数：
-
Avg: 返回所给字段平均值
-
Count:根据所给的关联字段返回被关联model的数量
-
Max： 返回所给字段的最大值
-
Min：返回所给字段的最小值
-
Sum：计算所给字段值总和
SELECT AVG(math) FROM stu;
分页查询
- 起始索引=(当前页码-1)X每页显示条数
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数；
- 注意：分页查询Iimit 是MysQL数据库的方言
Oracle分页查询使用rownumber
SQL Server分页查询使用 top
DCL用户管理语言
查询用户
USE mysql;
SELECT * FROM user;
创建用户
约束
约束是作用在表中列上的规则，用于限制加入表中的数据，保证了数据库数据的正确性有效性完整性。
| 约束名 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 保证所有数据没有null值 | NOT NULL |
| 唯一约束 | 保证数据不重复 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识要求非空且唯一 | PRIMARY KEY |
| 检查约束 | 保证表中数据满足某一条件 | CHECK |
| 默认约束 | 填充数据时未指定值则采用默认值 | DEFAULT |
| 外键约束 | 让两个表数据建立联系，保证数据一致性和完整性 | FOREIGN KEY |
| 自动增长约束 | 顾名思义 | AUTO_INCREMENT |
注意：MySQL不支持CHECK约束
主键约束
#字段级添加，不可多个
CTRATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
字段名2 字段类型(长度),
字段名3 字段类型(长度),
.....
字段名n 字段类型(长度) PRIMARY KEY
)
DESC 表名;#查看表结构
#表级添加,可添加多个或单个(可用来创建联合主键)
CTRATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
字段名2 字段类型(长度),
字段名3 字段类型(长度),
.....
[CONTRAINT 约束名] PRIMARY KEY(字段名,字段名1,……)
)
ALTER TABLE sqlconstraints ADD [CONSTRAINT 约束名] PRIMARY KEY(字段名);#添加主键约束
ALTER TABLE sqlconstraints ADD CONSTRAINT S_ID PRIMARY KEY(ID);#添加主键约束
ALTER TABLE sqlconstraints DROP PRIMARY KEY;#删除表内主键约束
唯一约束
#字段(列级)级添加，不可多个
CTRATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
字段名2 字段类型(长度),
字段名3 字段类型(长度),
.....
字段名n 字段类型(长度) UNIQUE
)
#表级添加,可添加多个或单个
CTRATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
字段名2 字段类型(长度),
字段名3 字段类型(长度),
.....
[CONTRAINT 约束名] UNIQUE(字段名,字段名1,……)
)
ALTER TABLE sqlconstraints ADD [CONSTRAINT 约束名] PRIMARY KEY(字段名);#添加主键约束
ALTER TABLE sqlconstraints ADD CONSTRAINT S_SNAME UNIQUE(SNAME);
DESC sqlconstraints;#展示表基本结构
#ALTER TABLE sqlconstraints DROP INDEX 唯一约束名;
#删除唯一约束
/*
单子段没有指定唯一约束名时,默认名唯一约束名为字段名
多字段默认为第一个字段名
指定了约束名则删除写的约束名(尚学堂207集)
*/
非空约束 NOT NULL
- 只能为列级添加 ''、0 都不是null
创建非空约束 not null
#字段(列级)级添加，不可多个
CTReATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
.....
字段名n 字段类型(长度) NUT NULL
)
ALTER TABLE sqlconstraints MODIFY AGE INT(3) NOT NULL;#创建非空约束,MODIFY为修改列属性
ALTER TABLE sqlconstraints MODIFY AGE INT(3);#删除非空约束
DESC sqlconstraints#展示表结构
默认值约束 DEFAULT
/*指在没有对某字段插入具体值时会插入默认值
#字段(列级)级添加，不可多个
CTREATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
.....
字段名n 字段类型(长度) DEFAULT 具体值
)
*/
ALTER TABLE sqlconstraints MODIFY 字段名 字段类型(长度);#删除
ALTER TABLE sqlconstraints MODIFY CLASS INT(4) DEFAULT 1907;#添加默认值
ALTER TABLE sqlconstraints MODIFY CLASS INT(4);#删除默认值
自动增长约束
是唯一的,多配合主键使用,只适用于整数,初值为1
CTREATE TABLE 表名(
字段名 字段类型(长度),
字段名1 字段类型(长度),
字段名2 字段类型(长度),
字段名3 字段类型(长度),
.....
字段名n 字段类型(长度) PRIMARY KEY AUTO_INCREMENT
)
ALTER TABLE sqlconstraints MODIFY ID INT(3) AUTO_INCREMENT;#添加自动增长约束
DESC sqlconstraints;
ALTER TABLE sqlconstraints MODIFY 字段名 字段类型(长度);#删除自动增长约束
外键约束
| 类型 | 实现方式 |
|---|---|
| 一对多 | 在多的一方建立外键，指向一的一方的主键 |
| 多对多 | 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键 ，中间表为它关联表的子表 |
| 一对一 | 在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE) |
/*FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。常和主键联系起来
对某张表修改删除时要保证数据完整性
MySQL中外约束可列级添加但不会生效
#表级添加,可添加多个或单个
CTRATE TABLE 表名(
字段名 字段类型(长度),
.....
[CONTRAINT 约束名] FOREIGN KEY(字段名) REFERENCES 表(字段名)
)
例如:班级表和学生表
*/
一对多
CREATE TABLE CLASS(
C_CON INT(8) PRIMARY KEY AUTO_INCREMENT,
#字段1 字段类型(长度) [完整性约束],
CNAME VARCHAR(20)
)#创建班级表
CREATE TABLE STUDENTS(
CON INT(8) PRIMARY KEY AUTO_INCREMENT,
ID INT(4),#字段1 字段类型(长度) [完整性约束],
SNAME VARCHAR(20),
AGE INT(3),
CONSTRAINT fk_STUDENT_CON FOREIGN KEY(CON)
REFERENCES CLASS(C_CON)#fk_STUDENT_CON外键名不可重复
)#创建表
DESC STUDENTS;
SHOW CREATE TABLE STUDENTS;
ALTER TABLE STUDENTS ADD CONSTRAINT fk_STUDENT_CON FOREIGN KEY(CON)
REFERENCES CLASS(C_CON)#创表后添加外界约束
ALTER TABLE STUDENTS DROP FOREIGN KEY fk_STUDENT#删除外界约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名
一对一
create table tb_user_desc (
id int primary key auto_increment,
city varchar(20),
edu varchar(10),
income int,
status char(2),
des varchar(100)
);
create table tb_user (
id int primary key auto_increment,
photo varchar(100),
nickname varchar(50),
age int,
gender char(1),
desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
查看表结构模型图
SQL CHECK 约束
CHECK 约束用于限制列中的值的范围。myql不支持
建表后添加
-
添加一个
ALTER TABLE 表名 ADD CHECK (P_Id>0) -
添加多个
ALTER TABLE 表名
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
- 删除check约束
ALTER TABLE Persons DROP Check chk_Person
删除有关系的表
- 先删除有外键约束的表再删表,先students,后class
- 先删外界约束,后删表
E-R图转换为对应的关系模型
| 实体 | 不可再分割的名词,方框表示 |
|---|---|
| 实体的属性 | 椭圆表示 |
| 实体间关系 | 菱形表示 |
| 关系 | 表 ,二维表 |
|---|---|
| 元组 | 表中一行 |
| 属性 | 表中一列的列名，域名 |
| 码 | 表中某关系组可唯一确定一个元组(表中一行)也就为本关系的码 ， |
| 域 | 相同数据类型的集合，表中一列 |
| 分量 | 元组的一个属性值,列值 |
| 关系模式 | 例: 关系名(属性1,属性2属性3....) 就是表头 |
实体间联系的转换
实体转换为一个关系模型(关系模型由一组关系构成(一个关系对应一张表))
| 1对1联系(a方b方) | 在任意a方加入b主码为变更为b外键,并加入联系本身的属性 |
|---|---|
| 1对n联系 | 将1方主键加入n方作为n方的外键,联系的属性也加入n方 |
| n对m联系 | 将联系本身转换为一个关系模型,模式名为联系名,主键为m和n的联合主键 |
例题:
某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址，产品的属性有产品编号、产品名、规格，职工的属性有职工号、姓名。
2)将该E-R模型转换为关系模型；(5分)(要求：1:1和1:n的联系进行合并)
转化后的关系模式如下
工厂(工厂编号，厂名，地址)
产品(产品编号，产品名，规格)
职工（职工号，姓名，工产编号，聘期，工资)
生产(工产编号，产品编号，计划数量)
(3)指出转换结果中每个关系模式的主码和外码。(5分)
每个关系模式的主码、外码如下：
工厂：主码是工产编号；
产品：主码是产品编号；
职工：职工号，外码是工厂编号
生产：主码是(工产编号，产品编号)，外码是工产编号、产品编号
本文来自博客园，作者：KMP，转载请注明原文链接：https://www.cnblogs.com/touchTomorrow/p/17605363.html
