MySQL 数据库

注意 5.7版本改动

mysql5.7版本的password已经改成了authentication_string

>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select User from user;  #此处为查询用户命令
+-----------+
| User      |
+-----------+
| *******  |
| mysql.sys |
| root      |
+-----------+
3 rows in set (0.00 sec)

mysql> update user set password=password("*******") where user="*******";  #修改密码报错
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #修改密码成功
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;  #立即生效
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p #以该用户登录成功.
Enter password: ********
…………………………
mysql>

1.概念介绍

工作中需要许多数据

单一一台电脑操作数据的话当这台电脑出问题之后，数据就会受到影响，并且不容易实现共享。

这就需要套接字创造专属的服务端和客户端了

操作MySQL数据库就需要学习sql语句

1. 数据库服务器：运行数据管理软件的计算机

2. 数据库管理软件：MySQL，Oracle，db2 ，slqserver

3. 库：文件夹

4. 表：文件

5. 记录：事物一系列典型的特征

6. 数据：描述事物特征的符号

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xf6uGH1J-1619403619480)(MySQL 数据库.assets/1616934607569.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OzDEliBV-1619403619483)(MySQL 数据库.assets/1616934747932.png)]

2.破解mysql登录系统（管理员登入）

首先以管理员身份停止mysql服务

>net stop MySQL

>mysqld --skip-grant-tables# 跳过登录授权

之后普通登录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q9BEHFWu-1619403619485)(MySQL 数据库.assets/1616991029170.png)]

以管理员身份运行mysql后修改mysql库中的root账户的密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9zuFLHD1-1619403619487)(MySQL 数据库.assets/1616991268131.png)]

修改完成之后退出账户。

然后以管理员身份运行cmd

找到MySQL进程，并且杀死

之后启动MySQL服务

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gNhaywJS-1619403619489)(MySQL 数据库.assets/1616991513567.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gyc1wFOq-1619403619490)(MySQL 数据库.assets/1616991607102.png)]

3.mysql连接远程服务端

通常我们没有指定登录的端口和IP时，都默认是127.0.0.1和3306

默认本地

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jRuNELZl-1619403619491)(MySQL 数据库.assets/1616991728014.png)]

4.mysql一些命令

登录之前：

mysql mysql -u[账户名] -p[密码] # 登录

登录之后：

\s # 查看一些用户信息和配置 如：字符编码

\c # 可以退出当前行命令

\q 或者 exit # 退出mysql命令行


select user(); # 查看当前登录的账号，注意结束符号‘;’

5. mysql语句

---操作文件夹（库）--
   增
   create database db1 charset utf8; # 造数据库（本质是在data目录中造一个文件夹）
   查
   show create database db1; # 查看刚刚建的数据库
   show databases; # 查看所有数据库
   改
   alter datebase db1 charset gbk; # 改数据库的字符编码
   删
   drop database db1 # 删除数据库
   
****************************************** 
   
---操作文件（表）--
   use db1; # 切换文件夹： 
   select database(); # 查看当前所在的库（文件夹）
   增
   create table t1(id int,name char);
   查
   show create table t1
   show tables; # 查看所有的表
   desc t1; # 查看表的结构（有什么变量名啥的）
   改
   alter table t1 modify name char(6); # 更改名字长度
   alter table t1 name NAME char(7); # 更改
   删
   drop table t1;
   
*******************************************
   
---操作文件内容（记录）--
   增
   insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');
   查
   select id,name from db1.t1; # 查看部分内容
   select * from db1.t1; # 查看所有内容
   改
   update db1.t1 set name='SB';
   update db1.t1 set name='SB' where id=2;
   删
   delete from t1;
   delete from t1 where id=2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OfBI2fHP-1619403619491)(MySQL 数据库.assets/1616998116651.png)]

6.库操作

注意可以使用help xxx 命令来查看xxx语句的功能

譬如：help create…

注意库的命名格式，几乎与 py 一致。

增删改查都在上方了。

7.表操作

存储引擎：

存储引擎就是表的类型。不同的语言MYsql调用不同的接口。

不同类型的表，使用不同类型的存储引擎。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t0ma7RH9-1619403619492)(MySQL 数据库.assets/1617112998535.png)]

查看Mysql支持的存储引擎

show engines; # 查看Mysql支持的存储引擎

咱们经常用的引擎是 InnoDB

指定表类型/存储引擎
create table t1(id int)engine=innodb; # 默认的引擎
create table t2(id int)engine=memory; # 
create table t3(id int)engine=blackhole; # 黑洞引擎，数据丢进去就没了
create table t4(id int)engine=myisam; # 

表的增删改查

show create table t4\G
show create table mysql.user\G
# \G是便捷查看，不加\G的话就不容易看了

create table t2 select host,user from mysql.user where 1>5 # 条件为假，只拷贝表的结构不拷贝记录

create table t3 like mysql.user;
# 拷贝表结构，但是实际数据没有

8. 数据类型

整型类型

只有int类型你设置的宽度是显示宽度而非存储宽度

tinyint、smallint、mediumint、int、bigint

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WUPoCfVt-1619403619492)(MySQL 数据库.assets/1617180706856.png)]

定义记录的时候，整型不要加宽度，整型类型后的宽度*并不是他的存储宽度，而是显示宽度，

create table t5(id int(5) unsigned zerofill);
# 显示不够5位用0补 但是如果数据宽度大于5，不影响数据

浮点型

• double
• float
• decimal

create table t8(x float(255,30));

# 				整数部分的宽度，小数部分的宽度

平常用float，要求精度高一点就用double，非常高就用decimal。

9.日期类型

create table student(
	id int,
    name char(6),	# 这里的6就是存储宽度
    born_year year,
    birth_date date,
    class_time time,
    reg_time datetime
);

insert into student values
(1,'egon',now(),now(),now(),now());

小知识

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xixTxJp0-1619403619493)(MySQL 数据库.assets/1617240435379.png)]

和datetime类似的timestamp区别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yGuoC2hE-1619403619493)(MySQL 数据库.assets/1617240551789.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CKSy4ekj-1619403619494)(MySQL 数据库.assets/1617252627002.png)]

10.字符类型

字符类型的宽度指的是字符的个数

char:定长，不够指定长度就用空格补全

varchar:变长，不够长度，传几个储存几个

select char_length(name) from t13;
# 计算字符类型长度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gp1UY0zu-1619403619494)(MySQL 数据库.assets/1617252969306.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G4jZU9VO-1619403619495)(MySQL 数据库.assets/1617253261917.png)]

like查询必须是一样的才可以，

=查询是自动末尾去空格。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DrGreLYv-1619403619495)(MySQL 数据库.assets/1617253572109.png)]

一张表中最好只用一种，要不全用char要不全用varchar

char定长，效率高

varchar变长，效率低，因为存的时候要先存取头部长度，后存取数据。取的时候也是如此。

11.枚举类型与集合类型

enum 单选（多选一）

set 多选

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TboF1EDM-1619403619496)(MySQL 数据库.assets/1617254290824.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3sym8VQC-1619403619496)(MySQL 数据库.assets/1617254508095.png)]

12.约束条件

Null: Yes允许传空值

not null default 不能传空值，否则使用默认值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0WWATuRN-1619403619497)(MySQL 数据库.assets/1617255884021.png)]

unique key :

方式一：（单列唯一）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RCGj1IDb-1619403619497)(MySQL 数据库.assets/1617256220040.png)]

方式二：（单列唯一）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uI6cG0IN-1619403619498)(MySQL 数据库.assets/1617256259538.png)]

联合唯一：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Asd2vawe-1619403619498)(MySQL 数据库.assets/1617256411262.png)]

primary key

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kw5Zohxx-1619403619499)(MySQL 数据库.assets/1617259811191.png)]

只要你的表用的是innodb引擎，就应该有个主键。

单例主键

create table t17(
	id int primary key,
    name char(16)
);

insert into t17 values
(1,'egon'),
(2,'wpq')

复合主键

create table t19(
	ip char(15),
    port int 
    primary key(ip,port)
);

insert into t19 values
('1.1.1.1',80),
('1.1.1.1',81);

auto_increment

auto_increment 是一个自增长的约束条件

必须把auto_increment做成一个key

create table t20(
	id int primary key auto_increment,
    name char(16),
);

insert into t20(name) values
('alex'),
('egon'),
('wupeiqi');

# 如果你想强制传入id的话
insert into t20(id,name) values
(7,'yuanhao')
# 但是当你后边想继续传入值的话，
# id以7为基础累加

了解：

show variables like 'auto_inc%';

# 步长
auto _increment_increment 默认为1

# 其实偏移量
auto_increment_offset 默认为1

# 设置步长
set session auto auto_increment_increment=5;
set global auto auto_increment_increment=5;

# 设置起始偏移量
set global auto_increment_offset=3;
强调：起始偏移量一定要<=步长。

清空表:

delete from t20;

delete from t20 where id =3;

insert into t20(name) values
('xxx');

# delete 是清除固定范围的表

truncate t20;
# 清空表

# 清空之后的id 从0开始

foreign key:

十分重要——建立表之间的关系

# 创建关联表顺序

# 建立表关联，要先建被关联的表。并且保证被关联的表的字段唯一。


# 再建关联的表

# 删除关系表顺序
# 先删除关联表再删被关联表

想要一同更新关联表和被关联表

就需要

在被关联表下输入：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wO9kOGRh-1619403619499)(MySQL 数据库.assets/1617270407919.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oQyRVdzA-1619403619500)(MySQL 数据库.assets/1617270535117.png)]最好不要把多张表在数据库中耦合在一起，因为以后涉及到扩展就不好弄了。

扩展应该在应用程序层面上扩展，不应该在数据库扩展。影响底层关系。

13.表之间的关系

• 多对一

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A6dCfInF-1619403619500)(MySQL 数据库.assets/image-20210401193125072.png)]

• 多对多

双向的多对一是多对多。需要新建一个表用于储存那两张表的关系

constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,

• 一对一

关联方式：foreign key+unique

在后产生的那张表加foreign key和unique

查询记录

简单查询

select * from 表;

select id,xxx from 表;

避免重复查询

select distinct xxx from 表;

四则运算查询

# 直接运用计算
select name,salary*12 from 表;

# 运用运算并且起别名
select name,salary*12 as annual_salary from 表;

# 起别名不加 as 也可以
select name,salary*12 annual_salary from 表;

concat （拼接）/ concat_ws（拼接用符号分隔）

• 提取部分数据命名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4NBfMVYn-1619403619500)(MySQL 数据库.assets/image-20210402134737119.png)]

• 提取部分数据用分隔符组合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-maR9QNvz-1619403619501)(MySQL 数据库.assets/image-20210402135115335.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hL2975VC-1619403619501)(MySQL 数据库.assets/image-20210402135417600.png)]

where 过滤语句

select id,name from employee where id >7;

select name,salary from employee where post='teacher' and salary>8000

select name,salary from employee 

where 和 between（获取区间内信息）

where 和 not between（获取区间以外的信息）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3deJncxP-1619403619502)(MySQL 数据库.assets/image-20210402140601511.png)]

where 和 in （获取几个特定值的信息）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8isDHW4b-1619403619502)(MySQL 数据库.assets/image-20210402140605038.png)]

where 和 Null (判断)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rSYhY173-1619403619503)(MySQL 数据库.assets/image-20210402141250356.png)]

where 和 like（模糊查询）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9XtF4cNL-1619403619503)(MySQL 数据库.assets/image-20210402141622257.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O3sfsDMP-1619403619503)(MySQL 数据库.assets/image-20210402141803156.png)]

分组

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vwiV8UwS-1619403619504)(MySQL 数据库.assets/image-20210402145108258.png)]

聚合函数

max / min / avg（平均值）/ sum / count

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ATgK4tIB-1619403619504)(MySQL 数据库.assets/image-20210403205750342.png)]

select * from employee;

select post,count(id) as emp_count from employee group by post;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t5CsrmUM-1619403619505)(MySQL 数据库.assets/image-20210403205518877.png)]

# 注意
# 一定不要以unique的字段作为分组依据
# 因为每个都不一样了，所以分组没有意义

# 分组之后，只能去分组的字段，以及每个组聚合结果

没有group by则默认整体算作一组。

select max(salary) from employee;

group_concat

select post,group_concat(name) from employee group by post;

create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', 
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, 
depart_id int
);

having 过滤

分组之后的过滤

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fplLa2SR-1619403619505)(MySQL 数据库.assets/image-20210404214428103.png)]

order by

 ### 默认升序排列 ### 

select * from employee order by age asc; # 升序

select * from employee order by age desc; # 降序

select * from employee order by age asc,id desc;
# 先按照age升序排, 如果age相同则按照id 降序排

单表查询顺序

注意***分组之后才能用聚合函数***。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l6ifNHqX-1619403619506)(MySQL 数据库.assets/image-20210404215408418.png)]

#######单表查询运行顺序#######
1.from 

2.where过滤 / 
 group by分组 (分组之后才能用聚合函数)/ 
 having分组后过滤

3.运行distinct字段

4.order by / 
 limit n

limit的两个功能

• 限制功能：

select * from employee limit 3;
# 最多显示3个数据
select * from employee limit order by salary desc limit 1;# 查找薪资最高的一个人

• 分页功能

select * from employee limit 0,5; # 从0 开始，往后取5条。
select * from employee limit 5,5; # 从5开始，往后取5条。

需要注意的是数据库分页查看数据肯定不是靠一个个limit来实现的。肯定是有其他简单的功能来实现。

14.单表查询总结

15.正则表达式