因实际条件限制（即~~~穷~~~），选择在windows本地安装数据库用于日常练习联表查询，仅作草稿记录。

​​一、下载安装

1、下载地址：https://dev.mysql.com/downloads/windows/installer/

2、解压下载的安装包，解压后进行环境变量-系统变量Path配置

环境变量配置

3、在解压的路径下（如：D:\workSoftware\mysql\mysql-8.0.17-winx64\mysql-8.0.17-winx64）新建一个文件配置my.ini

[client]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

skip-grant-tables

# 设置3306端口

port = 3306

# 设置mysql的安装目录

basedir=D:\workSoftware\mysql\mysql-8.0.17-winx64

# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错

# datadir=D:\workSoftware\mysql

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

4、配置后，在命令窗口执行命令  mysqld  --initialize-insecure 自动生成data文件夹（win10可使用mysqld --initialize --console 自动生成data文件夹）

5、检查mysql版本：mysqladmin --version

检查版本

6、停止数据库命令：net stop mysql

7、启动数据库命令：net start mysql

注意：启动时如果遇到服务名无效，如图：是由于net start +服务名，启动的是win下面注册的服务，系统还并未注册mysql到服务中，所以此路径下无mysql服务

检查mysql版本

解决办法是：以管理员身份运行命令窗口，进入bin目录执行mysqld --install之后，再启动服务

以管理员身份运行窗口  

本地注册mysql到服务

8、更改用户密码：mysqladmin -u root password "new_password";  

默认用户root：mysql -u root -p

首次无密码直接回车，修改密码为root

9、关闭mysql开机自启动：在运行中输入Services.msc，找到Mysql，右键属性改成手动

10、Navicat连接mysql：文件-MySQL-填写连接ip和密码后点击连接

连接mysql

如果在局域网中使用Docker远程连接数据库报错：2059 - Authentication plugin ‘xxxxxxx’ cannot be loaded：XXXXXX （无法加载身份验证插件）

是因为mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后，加密规则是caching_sha2_password

此时需要更改加密规则：

（1）用户登录进入数据库：use mysql

进入数据库模式

（2）从数据库的user表中查询mysql用户原来使用的身份验证插件

select user,host,plugin,authentication_string from user;

查询mysql身份验证插件

（3）替换之前的加密规则为mysql_native_password

alter user 'root'@'localhost' identified with mysql_native_password by ‘root’;

替换之前的加密规则为mysql_native_password  

检查是否替换成功

（4）修改成功后再次点击连接数据库，就可以正常连接登录了

成功连接数据库

二、数据库基本语法

1、设置 set sql_safe_updates=1;（1表示开启该参数） 这个自带的参数解决更新时是否带where条件语句；

设置更新条件

2、数据库基本增删改查

use study;

SELECT *from websites;

#去重-查询

select distinct country from websites;

SELECT * from websites 

WHERE alexa > 2 AND (country = 'CN' or country = 'USA' );

#排序（默认升序）asc升序 desc降序,如果多列 按列名先后顺序排序

select * from websites ORDER BY country, alexa desc;

#插入

INSERT INTO websites 

VALUES ('6','我要自学网','https://www.51zxw.net/List.aspx?cid=451','5','CN');

insert into websites(name ,url ,country) 

VALUES ('测试添加','http://www.baidu.com','CN');

#设置更新未加where条件报错

-- set sql_safe_updates = 1;

-- show variables like 'sql_safe_updates';

#更新

-- update websites set alexa = 888 WHERE alexa = 0;

#删除行，不删除表结构、属性、索引等

-- DELETE FROM websites WHERE alexa = 888;

2、#规定返回记录的数目

select *from websites limit 3;

（Microsoft SQL Server 数据库语法：

SELECT TOP number|percent column_name(s)FROM table_name;

例：SELECT TOP 50 PERCENT * FROM Websites;

Oracle 语法：

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;）

3、IN 与 = 的异同

 相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义

 不同点：IN可以规定多个值，等于规定一个值

Mysql中between包含两个测试值的字段，MySQL中不支持 FULL OUTER JOIN

4、在使用 join 时，on 和 where 条件的区别：

 ①、 on 条件是在生成临时表时使用的条件，不管 on 中的条件是否为真，都会返回左边表中的记录。

 ②、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

select into from 和 insert into select的区别

相同：都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，在插入时会自动创建；insert into select from 要求目标表存在：

select *（查询出来的结果） into newtable（新的表名）form where （后续条件）

insert into  (准备好的表) select *（或者取用自己想要的结构）frome 表名 where 各种条件

#规定返回记录的数目

select *from websites limit 3;

#通配符查询(选取name以 G 开头的所有客户，%多个字符，_单个字符,[charlist]字符列中的任何单一字符,[^charlist]或[!charlist]不在字符列中的任何单一字符)

select * from websites where name like 'G%';

select * from websites where name not like 'G%';

#[charlist]通配符，选取 name 以 "G"、"F" 或 "s" 开始的所有网站,REGEXP表示正则

select *from websites where name regexp '^[GFs]';

#选取 name 以 A 到 H 字母开头的网站

select *from websites where name regexp '^[A-H]';

#选取 name 不以 A 到 H 字母开头的网站

select *from websites where name regexp '^[^A-H]';

#IN  在什么中，可多个值，=只有一个值

-- SELECT *from websites where name in('淘宝','微博');

# alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站

 select *from websites 

WHERE (alexa between 1 and 20) and country not in ('USA','IND');

#AS 取别名，如果列名称包含空格，要求使用双引号或方括号

-- SELECT name as n , country as c  from websites;

-- SELECT name ,concat(url,',',alexa,',',country) as site_info  from websites;

#联表查询

 select w.name ,w.url, w.country , a.date

 from websites as w, access_log as a 

where w.id = a.site_id;

#join (inner join)：把来自两个以上的表的行结合起来

select w.id, w.name, w.url, a.count, a.date

from websites as w 

join access_log as a 

on w.id = a.site_id 

order by a.count;

#left join（left outer join）右表无匹配记录也会显示

select w.id, w.name, w.url, a.count, a.date 

from websites as w 

left join access_log as a 

on w.id = a.site_id 

order by a.count desc;

#right join（right outer join）左表无匹配记录也会显示

INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`)

 VALUES ('10', '8', '111', '2020-11-02');

select w.id, w.name, w.url, a.count, a.date 

from websites as w 

right join access_log as a 

on w.id = a.site_id 

order by a.count desc;

#去重 union（默认选取不同值，否则，使用union all ）中每个查询语句列的数量、顺序、数据类型须相同

-- select country from websites union select country from apps order by country;

select country, name 

from websites 

WHERE country = 'CN' 

union all select country, app_name from apps 

where country = 'CN' 

order by country;

#insert into ... select选择一个表复制信息插入到另一个已存在的表中，目标表中已存在数据不受影响

-- insert into apps(url, country) select url, country from websites;

#eg:只复 QQ 的 APP 到 "Websites" 中

insert into websites(country, name) 

select country, app_name from apps where id = 1;

5、#创建表+约束

-- CREATE TABLE student(

-- S_id   VARCHAR(255) NOT NULL PRIMARY KEY,

-- S_name VARCHAR(255),

-- S_sex  VARCHAR(255),

-- S_year VARCHAR(255),

-- S_birthday DATE

-- )

#添加约束 在字段处可查看

-- alter table student modify S_name VARCHAR(255) not null;

#删除约束

-- alter table student modify S_name VARCHAR(255);

#添加约束 unique在索引处查看

-- alter table student add unique (S_id);

-- ALTER TABLE student ADD constraint uc_studentID UNIQUE (S_name,S_year);

#撤销约束

-- alter table student drop index S_id;

#每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

#外键foreign key

-- CREATE TABLE Orders(

-- O_id int NOT NULL,

-- OrderNo int NOT NULL,

-- S_id VARCHAR(255),

-- PRIMARY KEY (O_id),

-- FOREIGN KEY (S_id) REFERENCES student(S_id)

-- )

#检查约束 check 默认是 表名_chk_1~表名_chk_n

-- alter table student add check (S_name = '约翰');

-- ALTER TABLE student ADD constraint chk_studentID UNIQUE (S_name,S_year);

#撤销约束

-- alter table student drop check student_chk_1;

#默认约束 default

-- alter table student alter S_year set default '男';

#撤销约束

-- alter table student alter S_year drop default;

#索引 index 高效查询数据，可在常搜索的列或表上创建索引（索引需更新）

#创建一个唯一值的索引

-- create unique index index_name on student (S_name);

#删除索引

-- alter table student drop index index_name;

#drop 删除表 如果表有外键，需先删除外键再删除表

-- drop table ss;

#删除表内数据

-- truncate table websites;

#在表中添加列

-- alter table student add other VARCHAR(255);

#删除表中的列

-- alter table student drop column other;

#改变表中列的数据类型

-- alter table student modify column other int;

#auto_increment 自动创建主键字段的值,默认值1，每次递增1

-- CREATE TABLE Persons

-- (

-- ID int NOT NULL auto_increment, #auto_increment = 100 以100开始

-- LastName varchar(255) NOT NULL,

-- FirstName varchar(255),

-- Address varchar(255),

-- City varchar(255),

-- PRIMARY KEY (ID)

-- )

-- INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen');

6、视图的作用：

①视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

②视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

③从而加强了安全性，使用户只能看到视图所显示的数据。

④视图还可以被嵌套，一个视图中可以嵌套另一个视图。

#创建视图view，视图显示最新数据

-- create view new_view as select * from websites;

#更新视图create or replace view

-- create or replace view new_view1 as select *from websites;

#撤销视图

-- drop view new_view1;

#日期date

DATE - 格式：YYYY-MM-DD

DATETIME - 格式：YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

YEAR - 格式：YYYY 或 YY

7、NULL 用作未知的或不适用的值的占位符。

注：无法比较 NULL 和 0；它们是不等价的。

使用is null 和is not null

-- CREATE TABLE Products

-- (

-- P_Id int NOT NULL auto_increment,

-- ProductName varchar(255),

-- UnitPrice float,

-- UnitsInStock int,

-- UnitsOnOrder int,

-- PRIMARY KEY (P_Id)

-- )

#null 函数

-- select ProductName, UnitPrice*(UnitsInStock + IFNULL(UnitsInStock,0)) from products;

-- select ProductName, UnitPrice*(UnitsInStock + coalesce(UnitsInStock,0)) from products;

size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。

即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

三、mysql函数

Aggregate 函数（计算从列中取得的值，返回一个单一的值）：

AVG() - 返回平均值

COUNT() - 返回行数

Limit 1 - 返回第一个记录的值

Order by desc limit 1 - 返回最后一个记录的值

MAX() - 返回最大值

MIN() - 返回最小值

SUM() - 返回总和

#求access_log表中count列的平均值

-- select avg(count) as countAverage from access_log;

#选择访问量高于平均访问量的 "site_id" 和 "count"

-- select site_id,count from access_log where count > (select avg(count) from access_log);

#计算 "access_log" 表中 "site_id"=3 的总访问量(count)，返回指定列的值的数目（不包含null）

-- select count(count) from access_log where site_id = 3;

#计算 "access_log" 表中总记录数

-- select count(*) from access_log;

#计算 "access_log" 表中不同 site_id 的记录数

-- select count(distinct site_id) from access_log;

#选取 "Websites" 表的 "name" 列中第一个记录的值

-- select name from websites limit 1;

#选取 "Websites" 表的 "name" 列中最后一个记录的值

-- select name from websites order by id desc limit 1;

#从 "Websites" 表的 "alexa" 列获取最大值

-- select max(alexa) from websites;

#从 "Websites" 表的 "alexa" 列获取最小值

-- select min(alexa) from websites;

#查找 "access_log" 表的 "count" 字段的总数

-- select sum(count) from access_log;

#统计 access_log 各个 site_id 的访问量(group by结合聚合函数，根据一个或多个列对结果集进行分组)

-- select site_id, sum(access_log.count) from access_log group by site_id;

#group by 多表连接，统计有记录的网站的记录数量

-- select w.`name`,count(a.aid) from access_log as a

-- left join websites as w on a.site_id = w.id

-- group by w.`name`;

#having可以与聚合函数一起使用，WHERE 关键字无法与聚合函数一起使用

#查找总访问量大于 200 的网站

-- select w.`name`, w.url, sum(a.count) from (access_log as a inner join websites as w on a.site_id = w.id) group by w.`name` having sum(a.count) > 200;

注：如果mysql执行报错1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'study.w.url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

是由于默认的 MySQL 配置中sql_mode配置了only_full_group_by，需要GROUP BY中包含所有在 SELECT 中出现的字段

only_full_group_by：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行。

#查询sql_mode 配置，因为默认的 MySQL 配置中 sql_mode 配置了 only_full_group_by，需要 GROUP BY 中包含所有 在 SELECT 中出现的字段

-- select @@sql_mode;

查询sql_mode 配置

如果是Linux，就在配置文件（my.cnf）中修改sql_mode的配置（在/usr/local/etc/my.cnf路径下）。如果是Windows，就修改配置文件my.ini，修改后重启服务

修改my.ini配置文件

mysql修改配置文件my.ini后无法重启mysql服务，是因为少添加了sql_mode =

或者是未另存为ANSI格式

sql_mode=

并且注意去掉ONLY_FULL_GROUP_BY,

检查sql_mode

#查找总访问量大于 200 的网站，并且 alexa 排名小于 200

select w.`name`, w.url, sum(a.count) from access_log as a inner join websites as w on a.site_id = w.id where w.alexa < 200 group by w.`name` having sum(a.count) > 200;

#查找总访问量(count 字段)大于 200 的网站是否存在

-- select *from websites where exists (SELECT *from access_log WHERE websites.id = access_log.site_id and count > 200);

#查找出不符合查询语句的记录

-- select *from websites where not exists (SELECT *from access_log WHERE websites.id = access_log.site_id and count > 200);

Scalar 函数（基于输入值，返回一个单一的值）：

UCASE() - 将某个字段转换为大写

LCASE() - 将某个字段转换为小写

MID() - 从某个文本字段提取字符，MySql 中使用

SubString(字段，1，end) - 从某个文本字段提取字符

LEN() - 返回某个文本字段的长度

ROUND() - 对某个数值字段进行指定小数位数的四舍五入

NOW() - 返回当前的系统日期和时间

FORMAT() - 格式化某个字段的显示方式

#mid(列名, start[1,length]),从文本字段中提取字符

#从 "Websites" 表的 "name" 列中提取前 4 个字符

-- select mid(name,1,4) from websites;

#length()返回文本字段中值的长度

-- select length(url) from websites;

#round(X, D)，返回X的四舍五入的有D位小数的一个数字,如果是5会被舍掉，ROUND 返回值被变换为一个BIGINT

-- select round(UnitPrice, 1) from products;

#now()返回当前系统的日期和时间

-- select now() from websites;

#fromat()对字段的显示进行格式化

#从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期

-- select name ,url, date_format(now(), '%Y-%m-%d') as date from websites;

char和varchar的区别：可变字符和不可变字符（曾经面试被问过，脑子里全是乌鸦）

DNS 指的是域名系统

有需要实战的大神，可到牛客网，实战训练sql联表查询：

https://www.nowcoder.com/ta/sql?from=baidusql&bd_vid=6470612103282755647

本文章 如有雷同 纯属巧合~