MySQL基础
MySQL概述
什么是数据库
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
基本术语
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
- 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键:外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
- 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
- 表头(header): 每一列的名称;
- 列(col): 具有相同数据类型的数据的集合;
- 行(row): 每一行用来描述某条记录的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 键的值在当前列中具有唯一性。
MySQL数据库
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
- MySQL 是开源的,目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上,并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对 PHP 有很好的支持,PHP 是很适合用于 Web 程序开发。
- MySQL 支持大型数据库,支持 5000 万条记录的数据仓库,32 位系统表文件最大可支持 4GB,64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的,采用了 GPL 协议,你可以修改源码来开发自己的 MySQL 系统。
SQL语句分类
- DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
- DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
- DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
- DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
- TCL(Transition Control Language):事务控制语言,用来管理事务。
三大范式
- 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线;也就是说:每列的值具有原子性,不可再分割。
- 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得,满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键,那么主键以外的列必须完全依赖于主键;如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分。
- 第三范式(3NF)是在第二范式的基础上建立起来的,即满足第三范式必须要先满足第二范式。第三范式(3NF)要求:表中的非主键列必须和主键直接相关而不能间接相关;也就是说:非主键列之间不能相关依赖。
数据库的数据类型
整数类型
浮点数类型和定点数类型
字符串类型
日期与时间类型
YEAR类型
TIME类型
DATETIME类型
TIMESTAMP类型
二进制类型
数据库的基本操作
使用命令行连接MySQL数据库
-p密码不要有空格
mysql -h 主机名 -P 端口 -u 用户名 -p密码
启动、关闭mysql数据库
net start mysql
net stop mysql
创建数据库
create database 数据库名
;
create schema 数据库名
删除数据库
drop database 数据库名
;
查询所有数据库
show databases;
切换数据库
use 数据库名;
查看当前使用的数据库
select database();
数据表的基本操作
数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意:在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作,否则会抛出“No database selected”错误。
create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型,);
示例:
1 | CREATE TABLE `emp` ( |
查看数据表
show tables;
查看表的字段信息
desc 表名;
修改数据表
修改表名
alter table 表名 rename to re名字
修改字段名
alter table stu change name sname varchar(10);
修改字段数据类型
alter table stu modify sname int;
增加字段
alter table stu add address varchar(50);
删除数据表
drop table 表名;
示例:
1 | SELECT * FROM `emp` |
增删改查
Insert
插入数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值 1,值 2,...);
同时插入多条语句
INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;
示例:
1 | CREATE TABLE `goods` ( |
注意事项:
- 插入的数据应与字段的数据类型相同
- 数据的长度应在规定的范围内
- 在values中类列出的数据位置必须与被加入的列的排列位置相对应
- 字符和日期型数据应包含在单引号中
- 列可以插入控制[前提是该字段允许为空]
- 如果是给表中所有字段添加数据,可以不写字段名称
update
UPDATE tb_name SET 字段名=修改值 WHERE [条件]
示例:
1 | -- 将所有员工薪水改为500 |
使用细节:
- update可以用新值更新原有表中行中的各列
- set子句指示要修改哪个
- where子句指定更新哪行,没有where会修改所有列
delete
DELETE FROM 表名 [WHERE 条件表达式];
示例:
1 | -- 删除员工ocean |
使用细节:
- 如果不使用where子句,将删除表中的所有数据
- delete语句不能删除某一列的值
- 使用delete语句进删除记录,不能删除表本身。删除表本身需使用
drop table 表名
]
selete
- 基本用法
- 使用表达式对查询的列进行运算
- 起别名
示例:
– 查询表中所有学生的信息。
– 查询表中所有学生的姓名和对应的英语成绩。
– 过滤表中重复数据 distinct
– 要查询的记录,每个字段都相同,才会去重
– 统计每个学生的总分
– 在所有学生总分加 10 分的情况
– 使用别名表示学生分数。
1 | CREATE TABLE student( |
注意事项:
- select指定查询哪些列的数据
- column指定列名
- *代表查询所有列
- From指定查询哪张表
- DISTINCT可选,指显示结果时,是否去掉重复数据
在where子句中经常使用的运算符
比较运算符 | 含义 |
---|---|
> < <= >= = <> != | 大于 小于 大于(小于)等于 不等于 |
BETWEEN …AND… | 显示在某一区间的值 |
IN(set) | 显示在in列表中的值 |
LIKE ‘ ‘ NOT LIKE ‘ ‘ | 模糊查询 |
IS NULL | 判断是否为空 |
逻辑运算符 | 含义 |
---|---|
and | 多个条件同时成立 |
or | 多个条件任一成立 |
not | 不成立 |
示例:
1 | SELECT * FROM student |
使用order by子句排序查询
order by指定排序的列,排序的列既可以是表中的列名,也可以是select语句后指定的列名
ASC 升序[默认] 、DESC降序
order by子句应位于select语句结尾
示例:
1 | -- 对数学成绩升序输出 |
group by
分页查询
select ... limit start,rows
从start+1 行开始取,取出rows行
1 | -- 第一页 |
合计/统计函数
- count:返回行的总数
- sum:返回满足where条件的行的和
- avg:返回满足where条件的一列的平均值
- max\min:返回满足where条件的一列的最大最小值
count(*)和count(列)的区别:
- count(*)返回满足条件的记录的行数
- count(列)统计满足条件的某列有多少个,但是会排除为null的情况
用法示例:
1 | -- 统计一个班级共有多少学生 |
数据表的约束
为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;这些规则常称为表的约束。常见约束如下:
约束条件 | 说明 |
---|---|
PRIMARY KEY | 主键约束用于唯一标识对应的记录 |
FOREIGN KEY | 外键约束 |
NOT NULL | 非空约束 |
UNIQUE | 唯一性约束 |
DEFAULT | 默认值约束,用于设置字段的默认值 |
以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说,表的约束实际上就是表中数据的限制条件。
主键约束
主键约束(primary key)用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为null,主键的值是不可以重复的。
一张表最大只能有一个主键,但可以是复合主键
字段名 数据类型 primary key;
1 | -- 方式一 |
非空约束
非空约束(NOT NULL)指的是字段的值不能为空
字段名 数据类型 NOT NULL
1 | create table student02( |
默认值约束
默认值约束即DEFAULT用于给数据表中的字段指定默认值,即当在表中插入一条新记录时若未给该字段赋值,那么,数据库系统会自动为这个字段插人默认值
字段名 数据类型 DEFAULT 默认值;
1 | create table student03( |
唯一性约束
唯一性约束即UNIQUE用于保证数据表中字段的唯一性,即表中字段的值不能重复出现
字段名 数据类型 UNIQUE
1 | create table student04( |
使用细节:
- 如果没有指定not null,则unique字段可以有多个null
- 一张表可以有多个unique字段
外键约束
外键约束即FOREIGN KEY常用于多张表之间的约束
用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是null
1 | -- 在创建数据表时语法如下: |
示例:
1 | CREATE TABLE ee( |
数据一致性
建立外键是为了保证数据的完整和统一性。但是,如果主表中的数据被删除或修改从表中对应的数据该怎么办呢?很明显,从表中对应的数据也应该被删除,否则数据库中会存在很多无意义的垃圾数据。
删除外键
alter table 从表名 drop foreign key 外键名;
细节
- 从表里的外键通常为主表的主键
- 从表里外键的数据类型必须与主表中主键的数据类型一致
- 主表发生变化时应注意主表与从表的数据一致性问题
- 外键指向的表的字段,要求是primary key或者是unique
- 表单类型是innodb,才能支持外键
- 外键字段的类型要和主键字段的类型一致(长度可不同)
- 外键字段的值,必须在主键字段中出现过,或者为null[前提是外键字段允许为null]
- 一旦简历主外键关系,数据就不能随意删除
check约束
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间,如果不在该范围内就会提示出错
注:mysql5.7不支持check,只做语法校验,但不会生效;mysql8.0支持
1 | CREATE TABLE t23 ( |
mysql 表外连接
- 左外连接(左侧的表完全显示)
- 右外连接(右侧的表完全显示)
左外连接
SELECT ...FROM 表1 LEFT JOIN 表2 ON 条件
1 | SELECT `name`, stu.id, grade |
多表查询
多表查询是指基于两个和两个以上的表查询
使用条件约束,防止笛卡尔集出现
多表查询的条件不能少于(表的个数-1),否则会出现笛卡尔集
1 | -- 显示部门号为 10 的部门名、员工名和工资 |
自连接
特点:
- 把同一张表当作两张表使用
- 需要给表分别取别名
- 列名不明确,可以指定列的别名
使用示例:
1 |
|
表子查询
子查询
子查询时值嵌入在其他sql语句中的select语句,也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子句查询
多行子句查询值返回多行数据的子查询 使用关键字IN
使用示例:
1 | -- 显示与SMITH同一部门的所有员工 |
子查询当做临时表使用
1 | -- 查询ecshop中各类别中,价格最高的商品 |
all 和 any的使用
all:所有
any:任一
1 | -- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号 |
多列子查询
示例:
- 查询与allen部门和岗位完全相同的所有雇员(不包含ALLEN本人)
- (字段 1, 字段 2 …) = (select 字段 1,字段 2 from …)
1 | -- 得到ALLEN的部门和岗位 |
练习
- 查找每个部门工资高于本部平均工资的人
- 查找每个部门工资最高的人的详细信息
- 查找每个部门的信息(部门名,编号,地址)和人员数量
1 | -- 查找每个部门工资高于本部平均工资的人 |