一、什么是数据库
- 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库
- 常见数据库
- Oracle
- DB2
- SQL Server
- Postgre SQL
- MySQL
- 相关术语
- 数据库系统(Database System):DBS
- 数据库(Database)
- 数据库管理系统(Database Management System):DBMS
- 应用开发工具
- 管理员及用户
- SQL语言
- Structured Query Language:结构化查询语言
- DDL - 数据定义语言
- DML - 数据操作语言
- DQL - 数据查询语言
- DCL - 数据控制语言
- Structured Query Language:结构化查询语言
- 数据库系统(Database System):DBS
二、MySql相关操作
- 配置文件
my.cnf是MySQL的配置文件 - 登陆/退出MySQL
- 登录
- mysql -uroot -p
- mysql -uroot -proot
- 得到版本号
mysql -V
mysql --version
- 登陆的同时打开指定数据库
mysql -uroot -p -D db_name
- 退出
exit
quit
- 登录信息中需要掌握
- 命令行结束符默认使用;或者\g来结束
- 可以通过help或者\h或者?加上相关关键字来查看手册
- \c可以取消当前命令的执行
- 登录
- SQL语句语法规范
- 常用MySQL的关键字我们需要大写,库名、表名、字段名称等使用小写
- SQL语句支持折行操作,拆分的时候不能把完整单词拆开
- 数据库名称、表名称、字段名称不要使用MySQL的保留字,如果必须要使用,需要用反引号``将其括起来
- 常用SQL语句
- 得到登陆的用户
SELECT USER() select user()
- 得到MySQL的版本信息
SELECT VERSION() select version()
- 得到当前的日期时间
SELECT NOW() select now()
- 得到当前打开的数据库
SELECT DATABASE() select database()
- 得到登陆的用户
三、数据库相关操作
- 创建数据库
CREATE {DATABASE|SCHEMA} db_name;
- 检测数据库名称是否存在,不存在则创建
CREATE DATABASE [IF NOT EXISTS] db_name;
- 在创建数据库的同时指定编码方式
CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;
- 注意
- 数据库名称最好有意义
- 名称不要包含特殊字符或者是MySQL关键字
- 查看当前服务器下全部数据
SHOW DATABASES|SCHEMAS;
- 查看指定数据库的详细信息
SHOW CREATE DATABASE db_name;
- 指定修改数据库的编码方式
ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;
- 打开指定数据库
USE db_name;
- 得到当前打开的数据库
SELECT DATABASE()|SCHEMA();
- 删除指定数据库
DROP DATABASE db_name;
- 如果数据库存在则删除:
DROP DATABASE [IF EXISTS] db_name;
四、数据表相关操作
-
数据表
- 是数据库最重要的组成部分之一,数据是保存在数据表中
- 数据表由行(row)和列(column)来组成
- 每个数据表中至少有一列,行可以有零行一行或者多行组成
- 表名要求唯一,不要包含特殊字符,最好含义明确
-
创建表
CREATE TABLE [IF NOT EXISTS] tbl_name( 字段名称 字段类型 [完整性约束条件], 字段名称 字段类型 [完整性约束条件], ... )ENGINE=存储引擎 CHARSET=编码方式;
- unsigned:UNSIGNED无符号,没有负数,从0开始
- zerofill:ZEROFILL零填充,当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
- not null:NOT NULL非空约束,也就是插入值的时候这个字段必须要给值,值不能为空
- default:DEFAULT默认值,如果插入记录的时候没有给字段赋值,则使用默认值
- primary key:PRIMARY KEY主键,标识记录的唯一性,值不能重复,一个表只能有一个主键,自动禁止为空
- auto_increment:AUTO_INCREMENT自动增长,只能用于数值列,而且配合索引使用,默认起始值从1开始,每次增长1
- unique key:UNIQUE KEY唯一性,一个表中可以有多个字段是唯一索引,同样的值不能重复,但是NULL值除外
- foreign key:FOREIGN KEY外键约束
-
查看当前数据库下已有数据表
-SHOW TABLES;
- SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE ‘pattern’ | WHERE expr]; -
查看数据表的详细信息
SHOW CREATE TABLE tbl_name;
-
查看表结构
DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;
-
删除指定的数据表
DROP TABLE [IF EXISTS] tbl_name;
-
表结构相关操作
-
添加字段
ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
-
删除字段
ALTER TABLE tbl_name DROP 字段名称
-
添加默认值
ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
-
删除默认值
ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
-
修改字段类型、字段属性
ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
-
修改字段名称、字段类型、字段属性
ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段 名称]
-
添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
-
删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY;
-
添加唯一
ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [index_name] (字段名称)
-
删除唯一
ALTER TABLE tbl_name DROP index_name;
-
修改数据表名称
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name RENAME TABLE tbl_name TO new_tbl_name;
-
修改AUTO_INCREMENT的值
ALTER TABLE tbl_name AUTO_INCREMENT=值
五、MySQL中的数据类型
-
数值型
- 整数类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qgd6ZmeU-1587478492357)(en-resource://database/695:1)] - 浮点类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Anw8NoZL-1587478492359)(en-resource://database/697:1)]
- 整数类型
-
字符串类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6g4b00zw-1587478492360)(en-resource://database/699:1)]
1. CHAR效率高于VARCHAR,CHAR相当于拿空间换时间,VARCHAR拿时间换空间
2. CHAR默认存储数据的时候,后面会用空格填充到指定长度;而在检索的时候会去掉后面空格;VARCHAR在保存的时候不进行填充,尾部的空格会留下
3. TEXT列不能有默认值,检索的时候不存在大小写转换 -
日期时间类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u1xJOZ60-1587478492362)(en-resource://database/701:1)] -
二进制类型
六、MySQL存储引擎
- InnoDB存储引擎
- 设计遵循ACID模型,支持事务,具有从服务崩溃中恢复的能力,能够最大限度保护用户的数据
- 支持行级锁,可以提升多用户并发时的读写性能
- 支持外键,保证数据的一致性和完整性
- InnoDB拥有自己独立的缓冲池,常用的数据和索引都在缓存中
- 对于INSERT、UPDATE、DELETE操作,InnoDB会使用一种change buffering的机制来自动优化,还可以提供一致性的读,并且还能够缓存变更的数据,减少磁盘I/O,提高性能
- 创建InnoDB表之后会产生两个文件
.frm表结构文件
.ibd,数据和索引存储表空间中 - 所有的表都需要创建主键,最好是配合上AUTO_INCREMENT,也可以放到经常查询的列作为主键
- MyISAM存储引擎
- 默认MyISAM的表会在磁盘中产生3个文件
- .frm:表结构文件
- .MYD:数据文件
- .MYI:索引文件
- 可以在创建的时候指定数据文件和索引文件的存储位置,只有MyISAM表支持
- DATA DIRECORY [=] 数据保存的绝对路径
- INDEX DIRECTORY [=] 索引文件保存的绝对路径
- MyISAM单表最大支持的数据量2的64次方条记录
- 每个表最多可以建立64个索引
- 如果是复合索引,每个复合索引最多包含16个列,索引值最大长度是1000B
- MyISAM引擎的存储格式
- 定长(FIXED 静态)
是指字段中不包含VARCHAR/TEXT/BLOB - 动态(DYNAMIC)
只要字段中包含了VARCHAR/TEXT/BLOB - 压缩(COMPRESSED)
myisampack创建
- 定长(FIXED 静态)
- 默认MyISAM的表会在磁盘中产生3个文件
七、MySQL数据操作
-
添加记录
-INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);
- 不指定字段名称
INSERT tbl_name VALUE(value...)
需要按照建表时的字段顺序给每一个字段赋值 - 列出指定字段
INSERT tbl_name(字段名称,...) VALUES(值,...)
- INSERT … SET的形式
INSERT tbl_name SET 字段名称=值,...;
- INSERT … SELECT
INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]
- 一次添加多条记录
INSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,....),(值,...)
- 不指定字段名称
-
修改记录
UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]
- 如果不添加条件,整个表中的记录都会被更新
-
删除记录
DELETE FROM tbl_name [WHERE 条件]
- 如果不添加条件,表中所有记录都会被删除
- DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值,可以通过ALTER 语句将其重置为1
- 彻底清空数据表
TRUNCATE [TABLE] tbl_name
;- 清除表中所有记录
- 会重置AUTO_INCREMENT的值
-
查询记录
SELECT select_expr,... FROM tbl_name [WHERE 条件] [GROUP BY {col_name|position} HAVING 二次筛选] [ORDER BY {col_name|position|expr} [ASC|DESC]] [LIMIT 限制结果集的显示条数]
- 查询表中所有记录
SELECT * FROM tbl_name;
*所有字段 - 指定字段的信息
SELECT 字段名称,... FROM tbl_name
- 库名.表名
SELECT 字段名称,... FROM db_name.tbl_name;
- 给字段起别名
SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
- 给数据表起别名
SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
- 表名.字段名的
SELECT tbl_name.col_name,... FROM tbl_name;
- WHERE 条件 - 会筛选出符合条件的记录
- 比较运算符(> >= < <= != <> <=>)
- <=> 和 =的区别:<=>可以检测NULL值
- 安全等于运算符(<=>)<=>可以用来判断null值。在两个操作数均为null时,其返回值为1而不为null,而当一个操作数为null时,其返回值为0而不为null。
- 检测值是否为NULL或者NOT NULL
IS [NOT] NULL
-
- 指定范围
[NOT] BETWEEN ... AND
- 指定集合
[NOT] IN(值,...)
- 逻辑运算符
- AND
逻辑与 - OR
逻辑或
- AND
- 匹配字符
[NOT] LIKE
%
:任意长度的字符串-
: 任意一个字符
- 比较运算符(> >= < <= != <> <=>)
- GROUP BY 分组
- 把值相同放到一个组中,最终查询出的结果只会显示组中一条记录
- 分组配合GROUP_CONCAT()查看组中某个字段的详细信息
- 配合聚合函数使用
- COUNT()
- 统计记录总数
- 如果写的是COUNT(字段名称),字段中的值为NULL,不统计进来
- 写COUNT(*)会统计NULL值
- SUM()
求和 - MAX()
求最大值 - MIN()
求最小值 - AVG()
求平均值
- COUNT()
- 配合WITH ROLLUP关键使用
- 会在记录末尾添加一条记录,是上面所有记录的总和
5.** HAVING子句对分组结果进行二次筛选**
- 会在记录末尾添加一条记录,是上面所有记录的总和
- ORDER BY 排序
ORDER BY 字段名称 ASC|DESC
- LIMIT 限制结果集显示条数
LIMIT 值
显示结果集的前几条记录LIMIT offset,row_count
从offset开始,显示几条记录,offset从0开始
- 查询表中所有记录
- 多表查询
- 笛卡尔积的形式
- 内连接的形式
- 查询两个表中符合连接条件的记录
SELECT 字段名称,... FROM tbl_name1 INNER JOIN tbl_name2 ON 连接条件
- 外连接的形式
- 左外连接
#先显示左表中的全部记录,再去右表中查询复合条件的记录,不符 合的以NULL代替 SELECT 字段名称,... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 条件;
- 右外链接
# 先显示右表中的全部记录,再去左表中查询复合条件的记录,不符合的以NULL代替 SELECT 字段名称,... FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON 条件;
- 左外连接
- 外键约束
- 只有InnoDB存储引擎支持外键
- 创建外键
- 建表是指定外键
- SQL语句
[CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
- 子表的外键字段和主表的主键字段类型要相似;如果是数值型要求一致,并且无符号也要一致;如果是字符型,要求类型一致,长度可以不同
- 如果外键字段没有创建索引,MySQL会自动帮我们添加索引
- 子表的外键关联的必须是父表的主键
- 外键约束的参照操作
CASCADE
从父表删除或更新,子表也跟着删除或者更新,级联的操作SET NULL
从父表删除或者更新记录,并设置子表的外键列为NULL。NO ACTION | RESTRICT
拒绝对父表做更新或者删除操作
- SQL语句
- 动态添加外键
#动态添加外键之前表中的记录一定合法的记录,没有脏值,否则外键添加不成功 ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
- 动态删除外键
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;
- 建表是指定外键
- 特殊形式的查询
-
子查询
SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
- 内层语句查询的结果可以做为外层语句查询的条件
- 由IN引发的子查询
- 由比较运算符引出子查询
- 由EXISTS引发的子查询
- ANY SOME ALL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P7afeAH8-1587478492363)(en-resource://database/703:1)] - INSERT … SELECT
- CREATE … SELECT
- CREATE TABLE tbl_name LIKE tbl_name;
-
联合查询
- UNION
SELECT 字段名称,... FROM tbl_name1 UNION SELECT 字段名称... FROM tbl_name2;
- UNION ALL
SELECT 字段名称,... FROM tbl_name1 UNION ALL SELECT 字段名称... FROM tbl_name2;
- UNION ALL 是简单的合并,UNION会去掉表中重复记录
- UNION
-
自身连接查询-无限极分类的实现形式
-
八、MySQL常用函数
- 数学函数
- CEIL()
进一取整 - FLOOR()
舍掉小数部分 - ROUND()
四舍五入 - TRUNCATE()
截取小数点后几位 - MOD()
取余数 - ABS()
取绝对值 - POWER()
幂运算 - PI()
圆周率 - RAND()或者RAND(X)
0~1之间的随机数 - SIGN(X)
得到数字符号 - EXP(X)
计算e的x次方
- CEIL()
- 字符串函数
- 日期函数
- 其他函数
常用SQL语句
- 查看上一步操作产生的警告
show warnings;
- mysql中的注释
- #注释内容
- –注释内容