一、MySQL基础

1.1、MySQL概述

数据库顾名思义，就是存储数据的仓库，这些数据是有组织存储的(DataBase简称DB)。

而数据库管理系统，即操作和管理数据的大型软件(DataBase Management System简称DBMS)。

如何操作数据库管理系统，则需要通过SQL进行，那什么是SQL呢，SQL是操作关系型数据库的编程语言，定义了一套操作关系型数据库的标准。SQL全称为Structured Query Language结构化查询语言。

当前主流的关系型数据库管理系统有:

来源于:https://db-engines.com/en/ranking

Oracle：大型的收费数据库，Oracle公司产品，价格昂贵。
MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。
目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本。
SQL Server：Microsoft 公司推出的收费的中型数据库，C#、.net等语言常用。
PostgreSQL：开源免费的中小型数据库。
DB2：IBM公司的大型收费数据库产品。
SQLLite：嵌入式的微型数据库。Android内置的数据库采用的就是该数据库。
MariaDB：开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品，与 MySQL数据库有很好的兼容性。

小结:

1、什么是数据库及数据库管理系统

数据库就是数据存储的仓库，而数据库管理系统是操作和管理数据库的大型软件

2、SQL是什么鬼

SQL是操作关系型数据库的编程语言，是一套标准。

1.2、MySQL的版本选择，安装及启动。

MySQL的版本分为官方版和社区版

社区版为：MySQL community server

社区版免费，但是mysql官方不提供任何技术支持。

官方商业版：MySQL enterprise editor

收费，可以试用30天，官方提供技术支持。

生产环境MySQL版本选择---MySQL数据库GA版本选择流程

1 、分析企业业务需要使用到MySQL哪些基本功能和特性特性重点方向研究为:MySQL复制分区表 Plugin-innodb等

2 、MySQL数据库产品线第一个GA版本推出时间至少超过10个月再考虑使用到生产环境

3 、MySQL数据库产品线的最新GA版本一般向后退3~4个版本的GA版本数据库作为可选的目标

4 、仔细阅读选择的目标数据库GA版本若是为之前的版本修改了大量的BUG信息则此GA版本慎重选择

5 、仔细阅读目标GA版本数据库之后的第一版本若是修改的BUG信息量非常大直接放弃目标版本向前推进一个版本号作为目标版本

6 、按照4,5步所描述的办法直到选定版本之后的一个版本,BUG修改量不大严重BUG极少且不能为最新的GA版本数据库产品

7 、详细阅读选定的数据库GA版本之后 2~3个版本的BUG修复信息主要是跟目标GA版本相关的并且想办法重现以及寻找规避的办法

8 、对经过上述7个步骤选的GA版本结合企业业务可能需要用的功能都必须进行功能测试以及业务模拟性能测试

9 、挑选的数据库GA版本作为内部开发测试数据库环境跑个大概半年时间

10 、优化企业非核心业务采用新版本的数据库GA版本软件

11 、经过上述挑选后如果没有重要的功能BUG或性能瓶颈则可以开始考虑作为任何业务数据库服务的后端数据库软件

12 、向行内高手请教。

MySQL下载

最新版是8.27（2022-04-01）

把它wget 下来wget
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz 或者 wget
https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

二进制安装过程，我们徒手安装。

添加数据盘,格式化并挂载

mkfs.xfs /dev/sdb
mkdir /data
blkid
echo 'UUID=781cc418-5a68-496a-926c-c6d849116e8e /data xfs defaults 0 0' >>/etc/fstab
mount -a
df -h
目录结构
mkdir /data/{mydata/binlog,mydata/data,/mydata/logs,soft,script} -pv

初始安装

rpm -qa|egrep 'mariadb|mysql'|xargs -I {} rpm -e {}
useradd -s /sbin/nologin mysql -M
tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql
cd /data/soft/mysql/bin && echo "PATH=`pwd`:$PATH" >>/etc/profile
source /etc/profile
chown -R mysql.mysql /data

初始化数据

# 5.6 ./scripts/mysql_install_db ...
# 5.7
# 参数说明
# --initialize参数
# 1. 对于密码复杂度进行定制: 12位 ,4种
# 2. 密码过期时间: 180
# 3. 给root@localhost用户设置临时密码

mysqld --initialize --user=mysql --basedir=/data/soft/mysql --datadir=/data/mydata/mdata/data

# 缺少libaio库 ;解决 yum install -y libaio-devel

[root@db-master data]# mysqld --initialize --user=mysql --basedir=/data/soft/mysql --datadir=/data/mydata/mdata/data
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
...
[root@db-master data]# mysqld --initialize --user=mysql --basedir=/data/soft/mysql --datadir=/data/mydata/mdata/data
2021-05-31T13:06:26.448029Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-31T13:06:27.100208Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-31T13:06:27.272602Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-31T13:06:27.345115Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 02483300-c211-11eb-8b49-000c29a338cc.
2021-05-31T13:06:27.347433Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-31T13:06:29.784362Z 0 [Warning] CA certificate ca.pem is self signed.
# 这一行生成临时密码：_VmEktyR)8c  2021-05-31T13:06:31.667890Z 1 [Note] A temporary password is generated for root@localhost: _VmEktyR)8c;
[root@db-master data]# echo $?
0
OK 初始化完成

安装好后看看生成的文件

[root@db-master data]# ll
total 110660
-rw-r----- 1 mysql mysql 56 May 31 21:06 auto.cnf
-rw------- 1 mysql mysql 1676 May 31 21:06 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 May 31 21:06 ca.pem
-rw-r--r-- 1 mysql mysql 1112 May 31 21:06 client-cert.pem
-rw------- 1 mysql mysql 1680 May 31 21:06 client-key.pem
-rw-r----- 1 mysql mysql 431 May 31 21:06 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 May 31 21:06 ibdata1
-rw-r----- 1 mysql mysql 50331648 May 31 21:06 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May 31 21:06 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 May 31 21:06 mysql
drwxr-x--- 2 mysql mysql 8192 May 31 21:06 performance_schema
-rw------- 1 mysql mysql 1676 May 31 21:06 private_key.pem
-rw-r--r-- 1 mysql mysql 452 May 31 21:06 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 May 31 21:06 server-cert.pem
-rw------- 1 mysql mysql 1680 May 31 21:06 server-key.pem
drwxr-x--- 2 mysql mysql 8192 May 31 21:06 sys
[root@db-master data]# pwd
/data/mydata/mdata/data

我们删掉data下的数据重新初始化，使用无限制,无临时密码的初始化方式

参数 --initialize-insecure

\rm -fr /data/mydata/mdata/data/*
mysqld --initialize-insecure --user=mysql --basedir=/data/soft/mysql --datadir=/data/mydata/mdata/data
配置文件my.cnf
cat >/etc/my.cnf<<EOF
[mysqld]
user=mysql
basedir=/data/soft/mysql
datadir=/data/mydata/mdata/data
socket=/tmp/mysql.sock
server_id=33061
port=3306
[mysql]
socket=/tmp/mysql.sock

启动方式

sys-v c6方式

cp /data/soft/mysql/support-files/mysql.server /etc/init.d/mysqld

service mysqld restart

systemd c7方式

[root@db-master logs]# cat /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/soft/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=5000

[root@db-master logs]# systemctl daemon-reload

[root@db-master logs]# systemctl start mysqld.service

[root@db-master logs]# systemctl status mysqld.service

[root@db-master logs]# systemctl enable mysqld.service

Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service.

[root@db-master logs]#

启动故障现象

without update PID 类似错误, 一般为目录权限问题, 或者是my.cnf 路径不对 ,或者是sock文件修改或删除过

看日志 ... 查看报错信息 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

屏幕打印输出日志 mysqld --defaults-file=/etc/my.cnf

...

管理员密码设定root@localhost

mysqladmin -uroot password 123456

mysqladmin -uroot -p password 456789

管理员用户密码忘记处理

--skip-grant-tables # 跳过授权表

--skip-networking # 跳过远程登录

1. 关闭数据库

systemctl stop mysqld

2. 启动数据库到维护模式

mysqld_safe --skip-grant-tables --skip-networking &

3. 登录并修改密码

alter user root@'localhost' identified by '123456'; # 提示报错因为忽略了授权表

flush privileges; # 启用授权表再更新密码

alter user root@'localhost' identified by '222222';

4. 关闭数据库正常启动验证

mysql> select user,host,authentication_string from mysql.user;
 --------------- ----------- ------------------------------------------- 
| user | host | authentication_string |
 --------------- ----------- ------------------------------------------- 
| root | localhost | *E710DC2512FCF6F18FE0D652B53290DCB11F3334 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 --------------- ----------- ------------------------------------------- 
3 rows in set (0.00 sec)
mysql> alter user root@'localhost' identified by '222';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> alter user root@'localhost' identified by '222';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

退出正常关闭启动即可测试

小结:

本小结主要介绍了MySQL的版本选择、下载安装、初始化配置等内容。

二、SQL语句

2.1、SQL通用语法

SQL语句可以单独一行或多行书写，以分号结尾。

SQL语句可以使用空格/缩进来增强语句的可读写。

MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

注释: 单行注释，-- 注释内容或 # 注释内容（#注释为MySQL特有）

多行注释，/*注释内容*/

2.2、SQL分类

DDL语句：Data Definition Language 语句定义语言，用来定义数据库对象(库，表，字段)

1、数据库操作

查询所有数据库: show databases;

查询当前数据库: select database();

创建数据库: create database [if not exists] 数据库名 [default charset 字符集] [collat e 排序规则]

删除数据库: drop database [if exists] 数据库名;

使用/选择/切换数据库: use 数据库名;

2、表的操作

use database_name

查询当前数据库所有表： show tables;

查询表结构: desc 表名;

查询指定表的建表语句: show create table 表名;

表的基础创建

查看用户表的表结构及创建表语句:

3、数据类型

MySQL中的数据类型有很多，主要分为:数值类型，字符串类型，日期时间

数值类型: SIGNED/UNSIGNED

age tinyint unsigned # 年龄不能为负数，使用无符号tinyint

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

提示：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

char 定长长度为32 的字符串(一次性提供32字符长度的存储空间,存不满,用空格填充), 最多存255个字符。

varchar 可变长字符串类型,存数据时,首先进行字符串长度的判断,比如需要存储的字符串是10个字符只会分配10个字符串长度存储空间,并且会单独占用一个字符长度来记录此次的字符长度超过255之后,需要两个字节长度记录字符长度 (按需进行存储空间分配), 65535个，不确定长度时可选varchar。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

4、修改表操作

添加字段: alter table 表名 add 字段名类型(长度) [comment 注释] [约束]

eg: 给stuff表添加昵称字段，biubiuname,类型为varchar(20)

mysql> alter table stuff add biubiuname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stuff;
 ------------ --------------------- ------ ----- --------- ------- 
| Field      | Type                | Null | Key | Default | Extra |
 ------------ --------------------- ------ ----- --------- ------- 
| id         | int(11)             | YES  |     | NULL    |       |
| emp_no     | varchar(10)         | YES  |     | NULL    |       |
| name       | varchar(10)         | YES  |     | NULL    |       |
| gender     | char(1)             | YES  |     | NULL    |       |
| age        | tinyint(3) unsigned | YES  |     | NULL    |       |
| idcard     | char(18)            | YES  |     | NULL    |       |
| entrydate  | date                | YES  |     | NULL    |       |
| biubiuname | varchar(20)         | YES  |     | NULL    |       |
 ------------ --------------------- ------ ----- --------- -------

修改数据类型： alter table 表名 modify 字段名新的数据类型

修改字段名和数据类型: alter table 表名 change 旧字段名新字段名类型(长度) [注释] [约束]

modify 与 change的区别,都是修改字段类型及其它属性信息,区别是modify不能修改字段名

删除字段: alter table 表名 drop 字段名

修改表名: alter table 表名 rename to 新表名

alter table stuff rename to staff;

删除表: drop table if exists 表名

truncate table 表名 # 删除指定表及数据，并重新创建该表(表结构)

DDL 小结：

1、数据库的操作： show database/ create database db_name/use db/

select database()/drop database db

2、表操作的语法: show tables/create table tb_name(字段类型。。。)

desc tb_name/show create table tb_name

alter table tb_name add/modify/change/drop/rename to new_tb ....

drop table tb_name

DML语句：Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改。

添加数据 insert:

给指定的字段添加数据

insert into 表名(字段1，字段2，。。。) values(值1，值2，。。。);

给全部字段添加数据

insert into 表名 values(值1，值2，。。。);

批量添加数据

insert into 表名(字段1,字段2,...) values(值1,值2，..),(值1,值2,..),(值1,值2,..);

insert into 表名 values(值1，值2,...),(值1，值2,...),(值1，值2,...);

提示: 插入数据时，指定的字段顺序必须与值的顺序一一对应。

字符串和日期型数据应加引号

插入的数据大小，应该在字段的规定范围内

修改数据 update，

update 表名 set 字段1=值1,字段2=值2，...[where 条件]; #如果不加条件则会修改整张表的数据 UPDATE staff SET name='赵六' WHERE id=2;

删除数据 delete，

delete from 表名 [where 条件]，提示: delete语句的条件可以有，也可以没有如果没加条件，则会删除整张表的所有数据。delete语句不能删除某一个字段的值(可以使用update)

DELETE from staff where id=1;

DELETE from staff where name='赵六';

DQL语句：Data Query Language 数据查询语言，用来查询数据库中表的记录

select
        字段列表
from
        表名列表
where
                条件列表
group by
                分组字段列表
having
                分组后的条件列表
order by
                排序字段列表
limit
                分页参数

基本查询: select 字段1,字段2，字段3，... from 表名;

select * from 表名

别名使用: select 字段1 [AS 别名], 字段2 [AS 别名2] from 表名;

去重 distinct查询

条件查询 [where]: select 字段列表 from 表名 where 条件列表;

条件:可以是比较运算符，逻辑运算符

条件运算符: > >= < <= = <> != between ... and...某某范围内(最小值,大值)

in(...),like占位符模糊匹配（_单个字符,%任意字符）, is null 是null。

逻辑运算符: and &&, or ||，not ！。

练习操作: 1、查询年龄大于25的员工

SELECT * from employee where age>25;

2、查询年龄小于20的员工

3、查询年龄小于等于23的员工

4、查询没有身份证号的员工

select * from employee where id_card="";

5、查询有身份证号的员工select * from employee where id_card!=''

6、查询年龄在25(包含)~38（包含）之间的员工

select * from employee where age>=25 && age<=38;< p="">

select * from employee where age>=25 and age<=38;< p="">

select * from employee where age between 25 and 38;

7、查询性别为女，且年龄小于25的

select * from employee where gender='女' and age<25;< p="">

8、查询年龄为18，或者20，或25的员工

select * from employee where age in(18,20,25); # 此处不用or，比较low

9、查询姓名为两个字的员工

select * from employee where user_name like '__'; # 两个下划线

提示: like 占位符，模糊匹配包括下划线_,表示匹配单个字符，%百分号匹配任意个字符

10、查询员工身份证号末尾是X的

select * from employee where id_card like '%X'; # 或者17个下划线加X

聚合函数 [count ,max, min, avg, sum]: 将一列数据作为一个整体，进行纵向计算

count: 统计数量

max：最大值

min：最小值

avg：平均值

sum：求和

这些聚合函数都是作用于表当做的某一列。

select 聚合函数(字段列表) from 表名;

练习: 1、统计employee 员工数量

* 表示整张表的所有字段

select count(*) from employee;

select count(id) from employee; # id 字段的总数量

提示: 所有聚合函数不做null值的运算，即不计算null。

2、统计employee的平均年龄

select avg(age) from employee;

3. 最大最小年龄

4、统计工作地为长沙的员工年龄之和

select sum(age) from employee where workaddress='长沙';

分组查询 [group by]

语法: select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

where和having的区别:

执行的时机不同，where是分组之前进行过滤，不满足where条件的，不参与分组。having是分组后对结果进行过滤。

判断条件不同，where不能对聚合函数进行判断，having可以。

练习:

1、按性别分组，统计男女员工数量

select gender,count(*) from employee group by gender;

2、按性别分组，统计男员工和女员工的平均年龄

select gender,avg(age) from employee group by gender;

3、查询年龄小于35的员工,并根据工作地进行分组，获取数量大于等于3的工作地

先根据条件查询小于35的，然后按workaddress分组，统计工作地数量count()

select workaddress,count(*) from employee where age < 35 group by workaddress;

select workaddress,count(*) as address_count 
from 
employee where age < 35 
group by workaddress 
having address_count>3;
                         
# 提示: 执行顺序，where > 聚合函数 > having
            分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段没有任何意义

排序查询 [order by] : select 字段列表表名 order by 字段1 排序方式1,字段2 排序方式2;

排序方式: asc 升序(默认)，desc 降序

select * from empoyee order by age asc/desc; # 按年龄升降排序

多字段排序，按年龄排序，年龄相同的按入职时间排序

select * from employee order by age,entrydate desc;

分页查询 [limit]： select 字段列表 from 表名 limit 起始索引，查询记录数;

提示：起始索引=(查询页码-1)*每页显示记录数.

分页查询是数据库的方言，不同的数据库有不同的实现,MySQL是limit

如果查询的是第一页，起始索引可以省略，直接简写为limit 10;

连续: 1、查询第一页员工数据，每页显示10条

select * from emplooyee limit 0,10;

2、查询第二页员工数据，每页显示10条记录

select * from employee limit 10,10; # 通过公式得起始索引值(2-1)*10

DQL语句连续: 1、查询年龄在20,21,22,23岁的女性员工信息

分析，条件是女性，在什么什么范围用in()

select * from employee where gender='女' and age in(20,21,22,23);

2、查询性别为男，并且年龄在20~40(含)以内的姓名为三个字的员工

select * from employee where gender='男' and age between 20 and 40 and user_name like '___';

3、统计员工表中，年龄小于60岁的，男性员工和女性员工的人数

分析: 按性别分组，条件是小于60岁

select gender,count(*) from employee where age<60 group by gender;

方法二:union all 实现

4、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序

select user_name,age,entrydate

from

employee where age<=35 order by age,entrydate desc;

5、查询性别为男且年龄在20~40(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from employee where gender='男' and age between 20 and 40 order by age asc,entrydate asc limit 5;

DQL 语句的执行顺序

#验证 where后能用e别名说明 from先执行，然后是where

select * from employee as e where e.age > 15 order by age;

使用字段别名验证 order by 优先于字段

     mysql> select user_name as ename,age as eg from employee as e where e.age > 15 order by e.eg limit 5;
ERROR 1054 (42S22): Unknown column 'e.eg' in 'order clause'

DCL语句：Data Control Language 数据控制语言，用来创建数据库用户，控制数据库访问权限

DCL 用户管理

1、查询用户 select * from mysql.user;

mysql> select user,host from mysql.user;
 --------------- --------------- 
| user | host |
 --------------- --------------- 
。。。
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
 --------------- --------------- 
9 rows in set (0.00 sec)

2、创建用户: create user '用户名'@'主机名或ip或%' identified by '密码';

mysql> create user 't_user1'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> select user,host from mysql.user;
 --------------- --------------- 
| user | host |
 --------------- --------------- 
.....
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| t_user1 | localhost |
 --------------- --------------- 
9 rows in set (0.01 sec)

# 登录成功，但没有权限访问数据库
[root@db-master ~]# mysql -ut_user1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 81
。。。。
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
 -------------------- 
1 row in set (0.00 sec)

3、修改用户密码: alter user '用户名'@'主机名或ip' identified with mysql_native_password by '密码'; # MySQL 8.0以上的修改方式

mysql 5.7 以下常用: alter user root@'localhost' identified by 'abc123';

mysql> alter user 't_user1'@'localhost' identified with mysql_native_password by 'abc123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

# 新密码登录
[root@db-master ~]# mysql -ut_user1 -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 83
。。。
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

4、删除用户: drop user '用户名'@'主机名或ip';

[root@db-master ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 84
.....
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop user 't_user1'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
 --------------- --------------- 
| user | host |
 --------------- --------------- 
......
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
 --------------- --------------- 
8 rows in set (0.00 sec)
mysql>

5、权限控制

常用权限有: all,all privileges[所有权限]；select[查询权限]；insert[插入权限]；

update[修改权限]；delete[删除权限]；alter[修改表权限]；drop[删除权限]

create[创建库,表权限]；其它可以参考官方文档。

ALL 权限
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

查看用户拥有哪些权限: show grants for '用户'@'地址'；

授予权限: grant 权限列表 on 库.表 to '用户'@'地址'；

回收权限: revoke 权限列表 on 库.表 from '用户'@'地址'；

# 创建用户
mysql> create user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql>
  
  # 查看用户权限，这里没有授权，只有登录的权限。
mysql> show grants for 'test'@'localhost';
 ------------------------------------------ 
| Grants for test@localhost |
 ------------------------------------------ 
| GRANT USAGE ON *.* TO 'test'@'localhost' |
 ------------------------------------------ 
1 row in set (0.00 sec)

# 给用户授权
mysql> grant select,insert,update,delete on test.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

# 验证用户权限
mysql> show grants for 'test'@'localhost';
 ------------------------------------------------------------------------ 
| Grants for test@localhost |
 ------------------------------------------------------------------------ 
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'test'@'localhost' |
 ------------------------------------------------------------------------ 
2 rows in set (0.00 sec)

#用户权限回收，这里只回收delete
mysql> revoke delete on test.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

# 验证OK
mysql> show grants for 'test'@'localhost';
 ---------------------------------------------------------------- 
| Grants for test@localhost |
 ---------------------------------------------------------------- 
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'test'@'localhost' |
 ---------------------------------------------------------------- 
2 rows in set (0.00 sec)
mysql

提示: 多个权限直接逗号隔开，授权时数据库名和表名可以使用*.*表示所有库和表。

函数

函数是指一段可以直接被另一段程序调用的程序或代码，由于MySQL的函数已经封装内置好了，只要直接调用完成我们的业务需求即可。

1、字符串函数

concat(s1,s2,..,sn) 字符长拼接，将s1,s2,..sn拼接成一个字符串

lower(str) 将字符串str转化成小写

upper(str) 将字符串str转换成大写

lpad(str,n,pad) 左填充,用pad填充str左边的内容,达到n个字符串

员工工号用5位数来表示

update employee set staff_num=lpad(staff_num,5,0);

rpad(str,n,pad) 与lpad相反,填str右边

trim(str) 去掉字符串左右两边的空格

substring(str,start,len) 取子串,从str中的start位置开始返回len个子串

2、数值函数

ceil(x) 向上取整

floor(x) 向下取整

mod(x/y) 返回x/y的模

rand() 返回0-1内的随机数

round(x,y) 求参数x的四舍五入，保留y位小数

练习通过数据库生成6位随机数

select lpad(round(rand()*1000000,0),6,0); # 不满6位的填充0

3、日期函数

curdate() 返回当前日期

curtime() 返回当前时间

now() 返回当前日期和时间

year(date) 获取指定date的年份

month(date) 获取指定date月份

day(date) 获取指定date日期

date_add(date,interval expr type) 返回日期/时间值加上一个时间戳expr后的值

datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数

练习: 查询员工入职多久了

select user_name,datediff(curdate(),entrydate) from employee;

4、流程函数

流程函数是MySQL相对常用的一类函数, 用户可以使用这类函数在一个SQL语句中实现条件选择, 这样能够提高效率。

下面列出了MySQL跟条件有关的流程函数

mysql> select if(true,'ok','false');
 ----------------------- 
| if(true,'ok','false') |
 ----------------------- 
| ok |
 ----------------------- 
1 row in set (0.00 sec)
mysql> select ifnull('hello','world');
 ------------------------- 
| ifnull('hello','world') |
 ------------------------- 
| hello |
 ------------------------- 
1 row in set (0.00 sec)
mysql> select ifnull('','world');
 -------------------- 
| ifnull('','world') |
 -------------------- 
| |
 -------------------- 
1 row in set (0.00 sec)
mysql> select ifnull('null','world');
 ------------------------ 
| ifnull('null','world') |
 ------------------------ 
| null |
 ------------------------ 
1 row in set (0.01 sec)
mysql> select ifnull(null,'world');
 ---------------------- 
| ifnull(null,'world') |
 ---------------------- 
| world |
 ---------------------- 
1 row in set (0.00 sec)

# 
mysql> select user_name,(case workaddress when '长沙' then '一线长沙' when '超一线城市' then '一线城市' else '二线城市' end) as '工作城市' from employ
ee;
 ----------------- -------------- 
| user_name       | 工作城市     |
 ----------------- -------------- 
| 张三            | 一线长沙     |
| 张把把          | 一线长沙     |
| 李鬼            | 二线城市     |
| 陈三            | 二线城市     |
| 赵王衣蛾        | 一线长沙     |
| 刘璋            | 二线城市     |
| 华容            | 一线长沙     |
| 晁盖            | 二线城市     |
| 徐虎            | 二线城市     |
| 赵琪            | 一线长沙     |
| 不知道          | 一线长沙     |
| 什么鬼          | 一线长沙     |
| 不得了          | 一线长沙     |
| 完了            | 二线城市     |
| 董王            | 一线长沙     |
| 多尔衮          | 一线长沙     |
| 如尔哈赤        | 二线城市     |
| 波浪丢丢        | 一线长沙     |
| 逆天了          | 二线城市     |
| 跪舔了          | 一线长沙     |
| 妹妹不玩了      | 二线城市     |
| 韩梅梅          | 一线长沙     |
| 李雷            | 二线城市     |
| Lucy            | 二线城市     |
| lyly            | 二线城市     |
 ----------------- -------------- 
25 rows in set (0.00 sec)

#  case 。。。when。。。then。。。end是不是很简单
SELECT
id,
name,
(case when math >=85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学',
(case when english >=85 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语',
(case when chinese >=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) as '语文'
FROM
score;

约束

约束主要是用于表中的字段规则，如限制存储在表中的数据，确保数据有效性及完整性。

约束是作用在表中的字段上的，可以在建表该表时添加约束。

not null 限制该字段的数据不能为null

unique 唯一，保证该字段所有数据都唯一，不能重复

primary key 主键是一行数据的唯一标识非空且唯一

default 保存数据时如果未指定值，则使用默认值default

check 保证字段值满足某一个条件

foreign key 外键约束，两张表的数据之间建立连接，保证数据的一致完整性

create table app_user (

id int not null PRIMARY key auto_increment COMMENT '学号',

name VARCHAR(255) not null COMMENT '姓名',

age TINYINT UNSIGNED not null default 0 COMMENT '年龄',

gender char(1) comment '性别'，

dpt_id int comment '部门ID' # 关联部门表dpt的id

)charset=utf8mb4 engine=INNODB comment '员工表';

create table dpt(

id int primary key auto_increment comment 'ID',

name varchar(20) not null comment '部门'

)comment '部门表';

外键关联

create table 表名(

字段数据类型,

...

[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)

);

或者 alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

外键约束的4种类型，RESTRICT、NO ACTION、CASCADE、SET NULL主要针对于外键里的删除时和更新时。

RESTRICT（约束）：如果出现在删除时，意思是约束外键主键did记录（主表中的记录）不能直接删除，必须先删除被约束的表（从表）字段中dept_id所有这个外键主键值对应的记录，才能删除外键约束（主表中的记录）

NO ACTION：

CASCADE：删除选择这个时，删除主表中的记录时，主表中的这个主键id关联的从表的这个id值所在的记录也会被删除。建议不选。

SET NULL ：删除选择这个时，如果从表（被约束的字段所在的表中）被约束的字段的值设置为可以为空时，那么当删除主表的记录时，主表中被删除的这个记录对应的主键值（约束从表字段的那个值）在从表中对应的字段中出现的那个记录的被约束字段的值就会变为NULL。

最常用的是选择RESTRICT不让删的这个约束、或者选择SET NULL删除后值表为空。

目前公司都不太喜欢使用这种真实的外键约束，而是使用虚拟的外键约束。虚拟外键约束：就是人员表中的部门id字段中的id值是部门表中的主键id的值，这就是虚拟外键约束，也是目前来说比较流行使用的。

外键的删除 alter table 表名 drop foreign key 外键名称;

多表查询

一对多/多对一 :如部门与员工,一个部门对一多个员工,一个员工对应一个部门

实现: 在多的一方建立外键指向一的一方的主键

多对多: 学生和课程,一个学生选多个课程,课程也可以被多个学生选修

实现: 建立第三张中间表,中间表至少包含2个外键,分别关联两张表的主键，

如学生ID和课程ID。

建立三张表

mysql> create table student(
-> id int auto_increment primary key comment '主键ID',
-> name varchar(10) comment '学生姓名',
-> s_no varchar(10) comment '学号'
-> )comment '学生表';

mysql> desc student;
 ------- ------------- ------ ----- --------- ---------------- 
| Field | Type | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ---------------- 
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| s_no | varchar(10) | YES | | NULL | |
 ------- ------------- ------ ----- --------- ---------------- 
3 rows in set (0.00 sec)
mysql> insert into student values(null,'张无忌','20210112'),(null,'哑婆婆','20210311'),(null,'张三丰','20200112'),(null,'成昆','20210223'),(null,'韦一笑','20201230');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> create table caurse(
-> id int auto_increment primary key comment '主键ID',
-> name varchar(10) comment '课程名'
-> )comment '课程表';
Query OK, 0 rows affected (0.03 sec)
mysql> insert into caurse values(null,'k8s'),(null,'php'),(null,'mysql'),(null,'java');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> create table student_caurse(
-> id int auto_increment primary key comment '主键ID',
-> stu_id int not null comment '学生ID',
-> caur_id int not null comment '课程ID',
-> constraint fk_caurseid foreign key (caur_id) references caurse (id),
-> constraint fk_student foreign key (stu_id) references student (id)
-> )comment '学生课程中间表';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into student_caurse values(null,1,1),(null,1,2),(null,3,3),(null,2,2),(null,2,3),(null,3,4);
);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>

一对一

应用场景是对用户表的拆分,单表拆分,将一张表的基础字段放在一张表中,其它详情字段放在另一张表中，方便提升操作效率。

实现: 在任意一张表加入外键,关联另外一张表的主键,并且设置外键为唯一索引(unique)。

mysql> create table tb_user(
-> id int auto_increment primary key comment '主键ID',
-> name varchar(10) comment '姓名',
-> age tinyint unsigned comment '年龄',
-> gender char(1) comment '性别',
-> phone char(11) comment '手机号'
-> )comment '用户基本表';
Query OK, 0 rows affected (0.05 sec)

mysql> create table tb_user_edu(
-> id int auto_increment primary key comment '主键ID',
-> degree varchar(10) comment '学历',
-> major varchar(10) comment '专业',
-> primaryschool varchar(20) comment '小学',
-> middleschool varchar(20) comment '中学',
-> university varchar(20) comment '大学',
-> userid int unique comment '用户ID',
-> constraint fk_userid foreign key (userid) references tb_user(id)
-> )comment '用户学历表';

mysql> insert into tb_user values(null,'张五常','35','男','13300992233'),(null,'赵六栋','30','男','13402992213'),(null,'刘燕','30','女','13800293253')
,(null,'李妹妹','25','女','13130962530');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into tb_user_edu values(null,'本科','足球大爷','虹口第一小学','市一中','广州体育学院',1),(null,'本科','足球教练','平昌第一小学','海淀高中','北
,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

多表查询分类:

内连接: 查询A、B表的交集部分

select 字段列表 from 表1，表2 where 条件..... # 隐式内连接,两张表的交集

select 字段列表 from 表1 [inner] join 表2 on 连接条件 # 显示内连接。。

外连接: 左外连接，查询左表所有数据,以及两种表的交集部分.

select 字段列表 from 表1 left [outer] join 表2 on 条件。。。

右外连接,查询右表所有数据,以及两种表的交集部分.

select 字段列表 from 表1 right [outer] join 表2 on 条件。。

自连接: 当前表与自身的连接查询,自连接必须使用表别名，自连接可以是内连接，也可以是外连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件。。# 一张表取两别名

select * from employee e,employee c where e.managerid=c.id;

select * from employee e left join employee c on e.managerid=c.id;

联合查询:union,union all 将多次查询的结果合并起来，形成一个新的查询结果

select 字段列表 from 表A 。。。

union [all] # 两张表字段必须一致(包括类型)，加all 不去重

select 字段列表 from 表B 。。。

子查询，语句中嵌套select语句。

select from 表1 where 条件列=（select * from 表2）

子查询外部的语句可以是
insert/update/delete/select的任何一个。

按查询结果分为:

1. 标量子查询：返回单一值(数字/字符串/日期等)的标量，最简单的形式。

常用的操作运算符有:= <> > >= < <=< p="">

如查询研发部的所有员工信息: 通过部门ID查询

select * from employee where dpt_id=(select id from dpt where name = '研发部')
　　　　查询比'什么鬼' 入职时间晚的所有员工信息

         mysql> select entrydate from employee where user_name='什么鬼';
 ------------ 
| entrydate |
 ------------ 
| 2020-02-25 |
 ------------ 
1 row in set (0.00 sec)
mysql> select * from employee where entrydate > '2020-02-25';
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
| id | staff_num | gender | age | id_card | workaddress | entrydate | user_name |
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
| 2 | 00022 | 女 | 19 |31002120060323672x | 上海 | 2020-04-23 | 李鬼 |
| 4 | 00044 | 男 | 28 | 31002120040323673x | 长沙 | 2020-03-03 | 赵王衣蛾 |
| 7 | 00077 | 女 | 23 | 310021200003236733 | 深圳 | 2020-05-21 | 晁盖 |
| 14 | 00104 | 男 | 27 |31002120010323273x | 长沙 | 2020-03-18 | 董王 |
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
4 rows in set (0.00 sec)

合并起来

select * from employee where entrydate > (select entrydate from employee where user_name='什么鬼');

mysql> select * from employee where entrydate > (select entrydate from employee where user_name='什么鬼');
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
| id | staff_num | gender | age | id_card | workaddress | entrydate | user_name |
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
| 2 | 00022 | 女 | 19 | 31002120060323672x| 上海 | 2020-04-23 | 李鬼 |
| 4 | 00044 | 男 | 28 | 31002120040323673x | 长沙 | 2020-03-03 | 赵王衣蛾 |
| 7 | 00077 | 女 | 23 | 310021200003236733 | 深圳 | 2020-05-21 | 晁盖 |
| 14 | 00104 | 男 | 27 |31002120010323273x | 长沙 | 2020-03-18 | 董王 |
 ------ ----------- -------- ------ -------------------- ------------- ------------ -------------- 
4 rows in set (0.00 sec)

2. 列子查询：返回的结果集是 N 行一列。

常用操作符: in 、not in 、any、some 、all

子查询返回列表中,有任意一个满足即可(any,som)

子查询返回列表的所有值都必须满足 (all)

select id from dpt where name='研发部' and name='销售部'; #得到两个部门ID

select * from employee where dpt_id in(select id from dpt where name='研发部' and name='销售部'); # 根据部门ID 查员工信息

select * from employee where salary > all (select salary from employee where dpt_id=(select id from dpt where name='销售部')) # 查询工资大于销售部每个人的薪资

查询比研发部任意一员工工资高的员工信息

select salary from employee where dpt_id in(select id from dpt where name='研发部')

select * from employee where salary > any (select salary from employee where dpt_id in(select id from dpt where name='研发部'))

3. 行子查询：返回的结果集是一行 N 列。

常用操作符: = 、<> 、in 、not in

查询张三的工资和其直属上级员工信息

1、select salary,managerid from employee where name = '张三';

2、select * from emp where (salary,managerid)=(select salary,managerid from employee where name = '张三');
　 4. 表子查询：返回的结果集是 N 行 N 列。

常用操作符: in

查询与 "张三"，"李四" 职位和薪资相同的员工

1、 select job,salary from employee where name='张三' or name='李四';

2、select * from employee where in (select job,salary from employee where name='张三' or name='李四';)

查询入职时间大于''2016-03-12"的员工信息及所在部门

1、select * from employee where entrydate >'2016-03-12';

2、select e.*,d.* from (select * from employee where entrydate >'2016-03-12') as e left join dpt d on e.dpt_id=d.id;

多表查询巩固练习

1、查询员工的姓名年龄职位部门信息

2、查询年龄小于30岁员工的姓名年龄职位部门信息

3、查询所有员工的部门id 部门名称

4、查询所有年龄大于40岁的员工及归属的部门名称如果员工没有分配部门也需要展示出来

5、查询所有员工的工资登记

6、查询研发部所有员工的信息及工作等级

7、查询研发部员工的平均工资

8、查询工资比张三高的员工信息

9、查询比平均工资高的员工信息

10、查询低于本部门平均工资的员工信息

11、查询所有部门的信息并统计部门员工人数

12、查询所有学生的选课情况，展示出学生的姓名学号课程名等

薪资等级表

create table salgrade(

grade int,

losal int,

hisal int

) comment '薪资等级表';

员工信息表

CREATE TABLE `employee` (

`id` int(11) DEFAULT NULL COMMENT '编号',

`staff_num` varchar(10) DEFAULT NULL COMMENT '工号',

`gender` char(1) DEFAULT NULL COMMENT '性别',

`age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',

`id_card` char(18) DEFAULT NULL COMMENT '身份证',

`workaddress` varchar(50) DEFAULT NULL COMMENT '上班地址',

`entrydate` date DEFAULT NULL COMMENT '入职时间',

`user_name` varchar(50) DEFAULT NULL COMMENT '员工姓名'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

部门表

CREATE TABLE `dpt` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

`name` varchar(20) NOT NULL COMMENT '部门',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

插入数据进行测试。

MySQL教程（MySQL数据库入门教程）

一、MySQL基础

二、SQL语句

函数

约束

多表查询

相关推荐