MySQL数据库 - 基操

最新推荐文章于 2024-10-24 23:13:54 发布

居一哥

最新推荐文章于 2024-10-24 23:13:54 发布

阅读量181

点赞数

分类专栏： MySQL数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42147737/article/details/105669381

版权

MySQL数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、什么是数据库

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库
常见数据库
- Oracle
- DB2
- SQL Server
- Postgre SQL
- MySQL
相关术语
- 数据库系统(Database System):DBS
  - 数据库(Database)
  - 数据库管理系统(Database Management System):DBMS
  - 应用开发工具
  - 管理员及用户
- SQL语言
  - Structured Query Language：结构化查询语言
    - DDL - 数据定义语言
    - DML - 数据操作语言
    - DQL - 数据查询语言
    - DCL - 数据控制语言

二、MySql相关操作

配置文件
my.cnf是MySQL的配置文件
登陆/退出MySQL
- 登录
  1. mysql -uroot -p
  2. mysql -uroot -proot
  3. 得到版本号
    - mysql -V
    - mysql --version
  4. 登陆的同时打开指定数据库
    mysql -uroot -p -D db_name
- 退出
  1. exit
  2. quit
- 登录信息中需要掌握
  1. 命令行结束符默认使用;或者\g来结束
  2. 可以通过help或者\h或者?加上相关关键字来查看手册
  3. \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;
- 注意
  1. 数据库名称最好有意义
  2. 名称不要包含特殊字符或者是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中的数据类型

数值型
1. 整数类型
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qgd6ZmeU-1587478492357)(en-resource://database/695:1)]
2. 浮点类型
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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个文件
  1. .frm：表结构文件
  2. .MYD：数据文件
  3. .MYI：索引文件
- 可以在创建的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
  1. DATA DIRECORY [=] 数据保存的绝对路径
  2. INDEX DIRECTORY [=] 索引文件保存的绝对路径
- MyISAM单表最大支持的数据量2的64次方条记录
- 每个表最多可以建立64个索引
- 如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
- MyISAM引擎的存储格式
  1. 定长（FIXED 静态）
    是指字段中不包含VARCHAR/TEXT/BLOB
  2. 动态（DYNAMIC）
    只要字段中包含了VARCHAR/TEXT/BLOB
  3. 压缩（COMPRESSED）
    myisampack创建

七、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 条件 - 会筛选出符合条件的记录
  1. 比较运算符（> >= < <= != <> <=>）
    - <=> 和 =的区别：<=>可以检测NULL值
    - 安全等于运算符（<=>）<=>可以用来判断null值。在两个操作数均为null时,其返回值为1而不为null，而当一个操作数为null时，其返回值为0而不为null。
  2. 检测值是否为NULL或者NOT NULL
    - IS [NOT] NULL -
  3. 指定范围
    - [NOT] BETWEEN ... AND
  4. 指定集合
    - [NOT] IN(值,...)
  5. 逻辑运算符
    - AND
      逻辑与
    - OR
      逻辑或
  6. 匹配字符
    - [NOT] LIKE
    - %：任意长度的字符串
    - -：任意一个字符
- GROUP BY 分组
  1. 把值相同放到一个组中，最终查询出的结果只会显示组中一条记录
  2. 分组配合GROUP_CONCAT()查看组中某个字段的详细信息
  3. 配合聚合函数使用
    - COUNT()
      1. 统计记录总数
      2. 如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来
      3. 写COUNT(*)会统计NULL值
    - SUM()
      求和
    - MAX()
      求最大值
    - MIN()
      求最小值
    - AVG()
      求平均值
  4. 配合WITH ROLLUP关键使用
    - 会在记录末尾添加一条记录，是上面所有记录的总和
      5.** HAVING子句对分组结果进行二次筛选**
- ORDER BY 排序
  ORDER BY 字段名称 ASC|DESC
- LIMIT 限制结果集显示条数
  1. LIMIT 值
    显示结果集的前几条记录
  2. 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 条件;

外键约束
1. 只有InnoDB存储引擎支持外键
2. 创建外键
  - 建表是指定外键
    1. SQL语句
```
[CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES  主表(字段名称)
```
    2. 子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
    3. 如果外键字段没有创建索引，MySQL会自动帮我们添加索引
    4. 子表的外键关联的必须是父表的主键
    5. 外键约束的参照操作
      - CASCADE
        从父表删除或更新，子表也跟着删除或者更新，级联的操作
      - SET NULL
        从父表删除或者更新记录，并设置子表的外键列为NULL。
      - NO ACTION | RESTRICT
        拒绝对父表做更新或者删除操作
  - 动态添加外键
```
#动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功
 ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
```
  - 动态删除外键
```
ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;
```
特殊形式的查询
1. 子查询
```
 SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
```
  1. 内层语句查询的结果可以做为外层语句查询的条件
  2. 由IN引发的子查询
  3. 由比较运算符引出子查询
  4. 由EXISTS引发的子查询
  5. ANY SOME ALL
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P7afeAH8-1587478492363)(en-resource://database/703:1)]
  6. INSERT … SELECT
  7. CREATE … SELECT
  8. CREATE TABLE tbl_name LIKE tbl_name;
2. 联合查询
  1. UNION
```
SELECT 字段名称,... FROM tbl_name1 
UNION
SELECT 字段名称... FROM tbl_name2;
```
  2. UNION ALL
```
SELECT 字段名称,... FROM tbl_name1 
UNION ALL
SELECT 字段名称... FROM tbl_name2;
```
  3. UNION ALL 是简单的合并，UNION会去掉表中重复记录
3. 自身连接查询-无限极分类的实现形式

八、MySQL常用函数

数学函数
- CEIL()
  进一取整
- FLOOR()
  舍掉小数部分
- ROUND()
  四舍五入
- TRUNCATE()
  截取小数点后几位
- MOD()
  取余数
- ABS()
  取绝对值
- POWER()
  幂运算
- PI()
  圆周率
- RAND()或者RAND(X)
  0~1之间的随机数
- SIGN(X)
  得到数字符号
- EXP(X)
  计算e的x次方
字符串函数
日期函数
其他函数