MySQL 初步入门
一、什么是数据库
1-1、含义
数据库（Database）是按照数据结构类组织、存储和管理数据的仓库
1-2、常见数据库
Oracle、DB2、SQL Server、Postgre SQL、MySQL
1-3、SQL语言
含义
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言
作用
用于存取数据、查询、更新和管理关系数据库系统
SQL执行流程
from→where→group by→having→order by→limit
SQL 分类
| 种类 | 描述 |
|---|---|
| DQL语句 | 数据查询语言 select |
| DML语句 | 数据操作语言 insert delete update |
| DDL语句 | 数据库定义语言 create drop alter truncate |
| TCL语句 | 事务控制语言 commit rollback savepoint |
| DCL语句 | 数据库控制语言 grant revoke，安全，授权 |
二、什么是MySQL
2-1、含义
MySQL 是最流行的数据库之一，是一个免费开源的关系型数据库管理系统
2-2、特点
体积小、速度快、开源、免费
2-3、常用命令
连接 / 退出 MySQL
-- 连接
C:\Users\Administrator
λ mysql -u root -p
Enter password: root
-- 退出
mysql> exit
-- 查看版本
mysql --version
-- 查看当前用户
mysql> select user();
-- 查看当前版本
mysql> select version();
-- 查看当前日期时间
mysql> select now();
-- 查看当前打开的数据库
mysql> select database();
2-4、权限
-- 查看mysql都有哪些用户
select user,host from mysql.user;
-- 创建一个用户名，有所有数据库和表 读 的权限，并且复制root用户的所有数据库
grant select，可以添加delete，修改，添加等权限 on *.* to 用户名@ip地址 identified by 密码 with grant option;
-- 重新赋权限
revoke select on *.* from 用户名@ip地址;
-- 创建用户的时候赋予所有权限
grant all privileges on *.* to 用户名@ip地址 identified by 密码 with grant option;
-- 查看该用户都有哪些权限
show grants for '用户名'@'ip地址'
-- 删除用户
drop user '用户名'@'ip地址'
-- 集群删除
drop user '用户名'@'%'
-- 修改用户密码
set password for '用户名'@'ip地址'=password(新密码);
2-5、监控
# 查看mysql是否正常运行
mysqladmin -uroot -proot -hlocalhost ping
# 查看mysql的状态
mysqladmin -uroot -proot -hlocalhost status
# 查看当前有多少个用户在连接该mysql
mysqladmin -uroot -proot -hlocalhost processlist
三、数据库操作
3-1、常用命令
-- 创建数据库
create database db_test;
-- 如果不存在就创建数据库
create database if not exists db_test;
-- 创建数据库的同时，指定编码格式
create database db_test charset utf8;
-- 修改数据库编码格式
alter database db_test charset gdk;
-- 查看有多少个数据库
show databases;
-- 查看指定数据库的详细信息
show create database db_test;
-- 打开数据库
use db_test;
-- 删除指定数据库
drop database db_test;
3-2、备份
# 导出
mysqldump -u root -p db_test > f:/20210502_test.sql
# 导入
mysql > use db_test;
mysql > source f:/20210502_test.sql
四、表操作
4-1、含义
数据库表是数据库最重要的组成部分之一，数据是以表格的方式存储到数据表中
4-2、常用命令
前提条件：必须切换相应的数据库下操作，否则报错 ERROR 1046 (3D000): No database selected
/*
创建表结构：表名命名规范：t_ 表名,tbl_表名
约束：
1.NOT NULL：非空约束
2.DEFAULT：默认值
3.PRIMARY KEY：主键，标识记录唯一性
4.AUTO_INCREMENT：自增，只能用于数列值
5.UNIQUE KEY：唯一性，一张表中可以多个字段的唯一索引，同样的值不能重复，除了NULL之外
6.FOREIGN KEY：外键约束
*/
CREATE TABLE 表名 (
-- 字段名称 字段类型 完整性约束 描述
) ENGINE=存储引擎 DEFAULT CHARSET=字符集编码;
-- 查看该数据库下有几个表
show tables;
-- 查看该数据库下的所有表
show tables from 表名;
-- 查看表结构
desc 表名;
-- 查看详细表结构
show create table 表名;
-- 删除表
drop table 表名;
-- 重命名表
alter table 旧表名 rename 新表名;
-- 添加表字段
alter table 表名 ADD 字段 类型 NOT NULL
-- 修改表字段
alter table 表名 change 旧字段 新字段 数据类型
-- 修改字段数据类型
alter table 表名 change 字段 字段 数据类型
-- 删除字段
alter table 表名 drop column 字段;
-- 设置主键
alter table 表名 add primary key(字段);
-- 删除外键约束
alter table 表名 drop foreign key fk_xxx
-- 添加外键约束
alter table 表名 add constraint fk_引用id foreign key(引用id) references 被引用表名 (被引用id)
-- 删除外键约束
alter table 表名 add constraint fk_引用id foreign key(字段名) references 被引用表名 (被引用id) on delete cascade;
-- 更新外键约束
alter table 表名 add constraint fk_引用id foreign key(字段名) references 被引用表名 (被引用id) on update cascade;
五、数据类型
5-1、数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
5-2、日期和时间类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
5-3、字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| 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 | 极大文本数据 |
六、Innodb和MyISAM存储引擎
6-1、前言
自 MySQL5.x 之后，支持的存储引擎有十几种，其中最常见有 Innodb 和 MyiSAM
其中包括：
mysql> show engines \g;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
6-2、区别对比
| 功能 | MyISAM | InnoDB |
|---|---|---|
| 存储限制 | 256TB | 64TB |
| 支持事务 | 否 | 是 |
| 支持全文索引 | 是 | 否 |
| 支持B树索引 | 是 | 是 |
| 支持哈希索引 | 否 | 否 |
| 支持集群索引 | 否 | 是 |
| 支持数据索引 | 否 | 是 |
| 支持数据压缩 | 是 | 否 |
| 空间使用率 | 低 | 高 |
| 支持外键 | 否 | 是 |
6-3、MyISAM
简介
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一，MyISAM拥有较高的插入、查询速度。
特证
- 不支持事务，也不支持外键，但访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
- 使用MyISAM引擎创建数据库表，将产生3个文件。其中文件名和表名都相同，其中文件格式 为：
.frm、.MYD、.MYI - MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
查看文件位置
# window 环境
C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_test>dir
...
2021/05/05 周三 12:43 <DIR> .
2021/05/05 周三 12:43 <DIR> ..
2021/05/05 周三 12:43 61 db.opt
# # 存储表定义
2021/05/05 周三 12:43 8,556 myisam_table.frm
# 存储数据
2021/05/05 周三 12:43 0 myisam_table.MYD
# 存储索引
2021/05/05 周三 12:43 1,024 myisam_table.MYI
# linux 环境
[root@nexus db_test]# pwd
/var/lib/mysql/db_test
[root@localhost db_test]# ls -l
总用量 20
-rw-r-----. 1 mysql mysql 61 5月 5 12:11 db.opt
# 存储表定义
-rw-r-----. 1 mysql mysql 8556 5月 5 12:17 myisam_table.frm
# 存储数据
-rw-r-----. 1 mysql mysql 0 5月 5 12:17 myisam_table.MYD
# 存储索引
-rw-r-----. 1 mysql mysql 1024 5月 5 12:17 myisam_table.MYI
6-4、InnoDB
简介
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。
特证
- 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
- 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
- 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
- 外键约束。MySQL支持外键的存储引擎只有InnoDB。
- 支持自动增加列AUTO_INCREMENT属性。
查看文件位置
# window环境
C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_test>dir
...
2021/05/05 周三 12:54 <DIR> .
2021/05/05 周三 12:54 <DIR> ..
2021/05/05 周三 12:43 61 db.opt
2021/05/05 周三 12:54 8,556 innodb_table.frm
2021/05/05 周三 12:54 98,304 innodb_table.ibd
# linux 环境
[root@nexus db_test]# pwd
/var/lib/mysql/db_test
[root@nexus db_test]# ls -l
总用量 112
-rw-r-----. 1 mysql mysql 61 5月 5 12:11 db.opt
-rw-r-----. 1 mysql mysql 8556 5月 5 13:00 innodb_table.frm
-rw-r-----. 1 mysql mysql 98304 5月 5 13:00 innodb_table.ibd
6-5、常用命令
-- 查询所有存储引擎
mysql> show engines;
-- 修改指定存储引擎
alter table 表名 engine=存储引擎名称;
七、数据操作
7-1、新增
-- 指定字段新增
INSERT INTO 表名(field1,field2,field3,....)
VALUES (value1,value1,value3,....),(value1,value1,value3,....),(value1,value1,value3,....)
-- 不指定字段新增
INSERT INTO 表名 VALUE (value1,value1,value3,....),
-- 复制表
insert into 表1 (field1,field2,field3) select field1,field2,field3 from 表2
7-2、修改
-- 指定表字段修改数据，如果不条件会全部更新
UPDATE 表名 set field1=value1,field2=value2... [where 条件]
7-3、删除
-- 删除表中的所有数据，也可以按条件删除指定行
DELETE FROM 表名 [WHERE 条件];
-- 清空表数据，可以理解为重置表记录
TRUNCATE TABLE 表名;
7-4、查询记录
-- 语法顺序
select 字段,... from 表名
where 条件
group by 要分组的字段
having 二次筛选字段
order by 排序字段
limit 当前页码,每页显示的条数
-- 全表扫码
SELECT * FROM 表名;
-- 按指定条件查询
SELECT * FROM 表名 where 条件;
-- 指定字段查询
SELECT field1,field2,field3,field4 FROM 表名;
-- 设置别名
SELECT field as f FROM 表名 as 表别名
/*
查询记录数
1. count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
2. count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
3. count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空 字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计
4. 列名为主键，count(列名)会比count(1)快，执行效率是最优
5. 列名不为主键，count(1)会比count(列名)快
6. 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
7. *如果表只有一个字段，则 select count（*）最优
*/
SELECT count(1) FROM 表名 [where 条件];
7-5、运算符
| 符号 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| < | 小于 |
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
| between...and... | 在两者之间 |
| is null | 为空 |
| is not null | 不为空 |
| and | 并且 |
| or | 或者 |
| in | 包含 |
| or in | 不包含 |
| like | 模糊查询 |
| order by | 排序 |
| limit 起始下标，长度 | 分页 |
7-6、多行处理函数
| 符号 | 描述 |
|---|---|
| sum | 求和 |
| avg | 平均值 |
| max | 最大值 |
| min | 最小值 |
| count | 记录数 |
| distinct | 取出重复 |
| groud by | 分组 |
| having | 比较 |
7-7、关联查询
以 员工、部门 来作为导向
内连接关联查询
内连接：如果员工表和部门表都能匹配到就会查询出现，否则就不会查询出来。直观点来说：就是如果员工不属于指定部门或者说部门里没有这个员工是不会查询出来。
SELECT u.*,d.deptname
FROM tbl_user as u
INNER JOIN tbl_dept as d on u.dept_id = d.id;
左右外连接关联查询
左外连接：以左边的表的数据为准，去匹配右边的表的数据。如果匹配到就显示，匹配不到就显示为null。而右外连接刚好相反，是以右边的表为准。
-- 左
SELECT u.*,d.deptname FROM tbl_user as u
LEFT OUTER JOIN tbl_dept as d on u.dept_id = d.id;
-- 右
SELECT u.*,d.deptname FROM tbl_user as u
RIGHT OUTER JOIN tbl_dept as d on u.dept_id = d.id;
7-5、子查询
子查询：一条查询 sql 语句嵌套在另一条查询 sql 语句内部的查询
SELECT * FROM tbl_user
WHERE dept_id = (SELECT id from tbl_dept WHERE deptname='研发部')
浙公网安备 33010602011771号