MySQL学习笔记（一）

MySQL is not My SQL

安装（.msi版本）

安装的是社区免费版，Windows版本

1.下载安装包

• https://dev.mysql.com/downloads/installer/

2.安装

• 参考链接

3.测试

> mysql> select version();
> +-----------+
> | version() |
> +-----------+
> | 8.0.13    |
> +-----------+
> 1 row in set (0.00 sec)

安装（.zip版本）

参考博客1，博客2

1.下载压缩包并解压

• https://dev.mysql.com/downloads/mysql/

2.配置环境变量

• 例如：D:\program\mysql-8.0.18-winx64\bin

3.编辑配置文件my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=D:\program\mysql-8.0.18-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\program\mysql-8.0.18-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

4.以管理员身份运行cmd，进入mysql的bin目录

mysqld --initialize --console
mysqld --install
net start mysql
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '********';

Get Started

MySQL服务的启动和停止

• 方式1
  • 我的电脑–> (右键)管理–>服务和应用程序–>服务–>找到MySQL服务右键启动或关闭（cmd输入services.msc）
  • 快捷键win+R调出运行窗口，输入services.msc也能打开服务界面

• 方式2（以管理员身份打开cmd）

net start mysql80 # 开启MySQL服务
net stop mysql80 # 关闭MySQL服务

MySQL登录与退出

# 登录
mysql 【-h 主机名 -P 端口号】 –u 用户名 –p 密码
mysql -u root -p
# 退出
exit或ctrl+c

查看服务器版本

• 方式一：登录到mysql服务端

select version();

• 方式二：没有登录到mysql服务端

mysql --version
mysql --V

MySQL语法规范

• 不区分大小写，但建议关键字大写，表名、列名小写
• 字段名（和表名）最好用着重号括起来：字段名
• 每条命令最好用分号结尾
• 每条命令根据需要，可以进行缩进或换行
• 字符串和日期用单引号括起来
• 注释

# 单行注释
-- 单行注释
/* 多行注释  */

用户权限操作

• 查看当前用户

select user();

• 创建用户

// 低版本
create user '用户名'@'localhost' identified by '密码';

// 高版本
create user '用户名'@'localhost' identified with mysql_native_password by '密码';

'localhost' - 本机才能访问
'%' - 所有ip都能访问
'xxx.xxx.xxx.xxx' - 指定ip才能访问

• 更改用户密码

alter user '用户名'@'localhost' identified by '新密码';
# 修改root用户的密码为root
alter user 'root'@'localhost' identified by 'root';

• 查看用户权限

show grants for '用户名'@'localhost';

• 刷新权限，使更改的设置生效

flush privileges;

• 把某个数据库的管理权限给予某个MySQL用户

grant all privileges on 数据库名.该数据库的某个表名 to '用户名'@'localhost';
flush privileges;
-- 可在后面加上with grant option，作用是被赋予的权限能再赋给别人

// 全部数据库
grant all privileges on *.* to '用户名'@'localhost';
flush privileges;

• 移除某个MySQL用户的数据库权限

revoke all privileges on 数据库名.该数据库的某个表名 from 'mysql'@'%';
-- 名字可替换成*，代表所有

• 删除用户

delete from mysql.user where user='用户名';
drop user '用户名'@'localhost';
flush privileges;

数据库的操作

创建数据库

create database 数据库名;
create database if not exists 数据库名;

删除数据库

drop database 数据库名;
drop database if exists 数据库名;

选中数据库

use 数据库名;

查看数据库

• 查看所有数据库

show databases;

• 查看数据库定义

show create database 数据库名;

• 查看当前正在使用的数据库

select database();

• 查看当前数据库中所有的表

show tables;

• 查看其他数据库中所有的表

show tables from 库名;

• 查看参数

show variables;
show variables like 'max_connect%';

备份与恢复

-- 备份到SQL文件
mysqldump -u root -p '你的密码' mysql > xxx/xxx.sql
-- 从SQL文件中恢复
mysql -u root -p '你的密码' mysql < xxx/xxx.sql

常用数据类型

类型	说明
INT或INTEGER	4字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存， 必须指定长度
FLOAT(M,D)	单精度， M=整数位+小数位， D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度， D<=M<=255,0<=D<=30，默认M+D<=15
DATE	YYYY-MM-DD
TIME	HH:mm:ss
DATETIME	格式’YYYY-MM-DD HH:mm:ss’，只能反映出插入时的当地时区
TIMESTAMP	时间戳，和实际时区有关，更能反映实际的日期

表结构的操作

创建表

create table 表名(
  列名1 列的类型(长度) 约束,	
  列名2 列的类型(长度) 约束，
  表级约束
);

删除表

drop table 表名;	-- 表的结构、属性以及索引也会被删除
TRUNCATE TABLE 表名;	-- 仅仅删除表格中的数据，注意不能回滚
DROP TABLE [IF EXISTS] 表名;

修改表

-- 添加列
alter table 表名 add 列名 列的类型 列的约束;
-- 删除列
alter table 表名 drop [column] 列名;
-- 修改列
alter table 表名 modify 列名 列的类型 列的约束;
-- 修改列名
alter table 表名 change 旧列名 新列名 列的类型 列的约束;
-- 修改表名
rename table  旧表名 to 新的表名;
-- 修改表的字符集
alter table 表名 character set 字符集;

查看表

-- 查看表的定义结构/创建语句
show create table 表名;
-- 查看表的结构
desc 表名;
-- or
describe 表名;
-- or
show columns from 表名;

复制表

-- 复制表的结构+数据
create table 复制表名 as select 字段 from 原表名【where 条件】;
-- 复制表的结构
create table 复制表名 as select 字段 from 原表名 where 1=2;

默认值

create table test(
    id int primary key auto_increment,
    age int default 22
);

约束

-- 举个例子
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

非空约束: not null

• 可以不唯一
• 一张表可以有多个
• 所有的类型的值都可以是null
• 空字符串”” 不等于null， 0也不等于null
• 创建：一般是列级
• 增加：alter table 表名 modify 列名 类型 not null;
• 取消：alter table 表名 modify 列名 类型 null;

唯一约束: unique

• 可以为空

• 一张表可以有多个唯一约束，

• 一个唯一约束可以是多个列的组合

• MySQL会给唯一约束的列上默认创建一个唯一索引

• 创建：如果不给唯一约束名称，就默认和列名相同

• 添加

  alter table user modify name varchar(20) unique;
  alter table user add unique(name, password);
  alter table user add constraint uk_name_pwd unique(name, password);
  

• 删除：alter table user drop index uk_name_pwd;

主键约束: primary key

• 唯一+非空

• 一张表只能有一个

• 创建：一般是列级；如果是组合主键，需用表级

• 增加：alter table persons add primary key (p_id);

• 删除：alter table persons drop primary key;

• 主键使用习惯

  • 总是定义主键
  • 不更新主键列只用的值
  • 不重用主键列的值
  • 不在主键列中使用可能会更改的值

外键约束：foreign key

• 不推荐使用
• 用于预防破坏表之间连接的动作
• 能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一
• 外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列
• 同一个表可以有多个外键约束

列级约束

• 列级约束只能作用在一个列上，跟在列的定义后面

表级约束

• 表级约束可以作用在多个列上，不与列一起，而是单独定义

索引

建立索引

-- 允许使用重复的值：
CREATE INDEX index_name ON table_name (column_name)
CREATE INDEX PersonIndex ON Person (LastName, FirstName)
CREATE INDEX PersonIndex ON Person (LastName DESC) 

-- 唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name ON table_name (column_name)

撤销索引

ALTER TABLE table_name DROP INDEX index_name

表中数据的CRUD操作

插入数据

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

-- 字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
-- 不可以为空的字段，必须插入值
-- 可以为空的字段，可以不用插入值，或用null填充
-- 如果主键设置了自增，在主键的位置填写null，数据库会自动生成主键
insert into 表名 values (值1,值2);
insert into 表名 values (值1,值2),(值1,值2),(值1,值2);

-- insert into ... select ...
insert into 表名(列名1,列名2) select 值1,值2
insert into 表名(列名1,列名2) select 查询另一个表

删除数据

delete from 表名 【where 条件】;
truncate table 表名;	-- 先删除表,再重建表

-- sql192
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件 and 筛选条件

-- sql199
delete 表1的别名，表2的别名
from 表1 别名
inner | left | right join 表2 别名 on 连接条件
where 筛选条件

delete、truncate 比较

delete

• 仅删除表table_name内的所有内容，保留表的定义，不释放空间，数据可以回滚恢复
• 删除后再插入数据，自增长列从断点开始
• 效率取决于删除的数据量

truncate

• 删除表table_name里的内容，并释放空间，但不删除表的定义，表的结构还在，数据是不可以恢复的
• 删除后再插入数据，自增长列从1开始
• 效率取决于删除的数据量

更新数据

update 表名 set 列名=值, 列名=值 【where 条件】;

-- sql192
update 表1 别名，表2 别名
set 列=值,...
where 连接条件
and 筛选条件

-- sql199
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件

查询数据

基本查询

• 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
• 要查询的东西可以是常量值、表达式、字段、函数
• * 表示查询所有列
• distinct 表示查询不同行

select [distinct] [*] [列名1,列名2][要查询的字段|表达式|常量值|函数] 
from 表名 
where 条件 
group by ..having 条件过滤
order by 排序;

别名查询 as

• 在查询中涉及超过一个表
• 在查询中使用了函数
• 列名称很长或者可读性差
• 需要把两个列或者多个列结合在一起
• as可以省略
• 别名如果没有空格，可以不用单引号括起来，否则要加单引号

-- 列别名
SELECT 字段名 AS 字段别名 FROM 表名;
-- 表别名
SELECT 字段名 FROM 表名 AS 表别名;

条件查询 where

• 关系运算符

  • < <= > >=
  • =等于（不是==）
  • != 不等于
  • <=> 安全等于，既可以判断NULL值，又可以判断普通的数值
  • <> 安全不等于

• 逻辑运算符

  • and or
  • not
  • not between and
  • not in

• 连续范围

  • between A and B
  • [A, B]
  • A和B可以是数值，文本，日期

• 离散范围

  • in，WHERE column_name IN (value1,value2,…)

• 空值

  • in，WHERE column_name IN (value1,value2,…)

模糊查询 like

• 匹配的是整个列值

• 不能匹配null

• 通配符

  • _ 匹配一个字符
  • % 匹配零个或多个字符

• 通配符转义

  • 方法一： \
  • 方法二：escape关键字

正则表达式 regexp

• 在列值内进行匹配

• 通配符

  • . 匹配任意一个字符，例如pname REGEXP ‘.est’ 匹配 test, best

• 通配符转义：\\

• or，pname REGEXP ‘test | home’

• [charlist]，字符列中的任何单一字符

  [abc]
  [a-z]
  [123]
  [0-9]
  

• [^charlist]，[!charlist]，不在字符列中的任何单一字符

分组查询 group by

SELECT 【group by后的column_name, aggregate_function(column_name)】
FROM table_name
WHERE column_name operator value
GROUP BY column_name，column_name;

分组筛选 having

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

排序查询 order by

• asc（默认），升序ascend
• desc，降序descend
• order by 排序的字段|表达式|函数|别名 【asc|desc】
• order by 列名1 asc/desc, 列名2 asc/desc…
• order by子句在查询语句的最后面，除了limit子句

分页查询 limit

• 从0开始计数

select * from  表名 limit （currentPage-1）*pageSize+1,pageSize;

SELECT * FROM Persons LIMIT start_idx, idx_count;
SELECT * FROM Persons LIMIT  idx_count;
SELECT * FROM Persons LIMIT  idx_count offset start_idx;

函数

分组函数

• 和分组函数一同查询的必须是group by后的字段
• 不能在 WHERE 子句中使用组函数
• 可以在 HAVING 子句中使用组函数
• 下列语句中只有SELECT COUNT(*) 中null才会被计数，其余语句null不计数

sum : 求和
SELECT SUM(列名) FROM 表名;
avg() : 平均值
SELECT AVG(列名) FROM 表名;
max() : 最大值
SELECT MAX(列名) FROM 表名;
min() : 最小值
SELECT MIN(列名) FROM 表名;
count() : 统计数量
SELECT COUNT(列名) FROM 表名;
SELECT COUNT(DISTINCT 列名) FROM 表名;
SELECT COUNT(*) FROM 表名;

字符函数

concat拼接
substr截取子串（索引从1开始）
upper转换成大写
lower转换成小写
trim去前后指定的空格或字符
ltrim去左边空格或字符
rtrim去右边空格或字符
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引，找不到则返回0
length 获取字节个数

-- 
#姓和名首字符大写，其他字符小写然后用-拼接，显示出来
SELECT 
CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)，
'-',
UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) name
FROM employees;

SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;
       
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

SELECT LPAD('殷素素',2,'*') AS out_put;

SELECT RPAD('殷素素',12,'ab') AS out_put;

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

数学函数

日期函数

日期格式举例

• ‘%Y-%m-%d %H:%i:%s’
• ‘2020-01-06 23:18:47’

now
curdate
curtime
year
month
monthname
day
hour
minute
second
datediff
str_to_date
date_format

SELECT NOW();
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT DATEDIFF('1995-2-7','1995-4-6') diff;
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

其他函数

• ifnull(要判断的表达式，判断为真时的返回值)

  select id,name,ifnull(bonus,0) from employee;
  

• password

• md5

多表连接查询

sql92

• 内连接
  • 等值连接（n表连接，至少需要n-1个连接条件）
  • 非等值连接
  • 自连接

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

sql99

• 概念：http://www.ruanyifeng.com/blog/2019/01/table-join.html

  所谓"连接"，就是两张表根据关联字段，组合成一个数据集。问题是，两张表的关联字段的值往往是不一致的，如果关联字段不匹配，怎么处理？比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。

• 只返回两张表匹配的记录，这叫内连接（inner join）

  • 等值连接
  • 非等值连接
  • 自连接

• 返回匹配的记录，并且返回了多余记录，这叫外连接

  • 返回匹配的记录，以及表 A 多余的记录，这叫左（外）连接（left join）
  • 返回匹配的记录，以及表 B 多余的记录，这叫右（外）连接（right join）
  • 返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全（外）连接（full join）

• 交叉连接（cross join）

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

子查询

• 子查询都放在小括号内
• 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
• 子查询优先于主查询执行，主查询使用了子查询的执行结果

单行子查询

• 结果集只有一行
• 一般搭配单行操作符使用：> < = <> >= <=
• 非法使用子查询的情况：a、子查询的结果为一组值；b、子查询的结果为空

select last_name from employees
where salary > (select salary from employees where last_name='Abel');

多行子查询

• 结果集有多行
• 一般搭配多行操作符使用：any|some、all、in、not in
• in 属于子查询结果中的任意一个就行
• any和all 往往可以用其他查询代替

select first_name from employees where
department_id in(select department_id from departments where location_id=1700)

联合查询 union

• 多条查询语句的查询的列数必须是一致的
• 多条查询语句的查询的列的类型几乎相同
• union代表去重，union all代表不去重

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】

.....

select 字段|常量|表达式|函数 【from 表】 【where 条件】

注意

• 日期和字符串常量必须用单引号括起来

• 在SQL标准中是不存在双引号的

• 反引号是为了区分MySQL的保留字与普通字符而引入的符号

• + 号运算符

  -- 两个操作数都为数值型，则做加法运算
  select 100+90;
  
  -- 只要其中一方为字符型，试图将字符型数值转换成数值型。如果转换成功，则继续做加法运算
  select '123'+90;
  
  -- 如果转换失败，则将字符型数值转换成0
  select 'john'+90;
  
  -- 只要其中一方为null，则结果肯定为null
  select null+10;
  

• 如果一条记录有个字段为null，则’%%'无法匹配该字段