MySQL

下载

下载普通的包mysql-8.0.26-winx64.zip 200M左右

解压

配置环境变量

可以新建个变量MYSQL_HOME

安装服务，初始化

0， cd %MYSQL_HOME%\bin 并切换盘符 ，

虽说已经配置了环境变量了，但是初始操作还是建议cd过去操作，减少意外发生

1，安装。mysqld -install

注意有一个d

看贴有的说加上 -console 参数可以 显示密码，但是试了不行，反而安装不成功，建议不加

2，生成data目录，mysqld --initialize

注意有一个d

看贴说有的加上 -insecure --user=mysql ， 加上配置文件的skip-grant-tables 可以跳过验证直接登录，不清楚原因，过于复杂不建议加上该参数

3，生产data目录后，里面有个后缀.err的文件打开查看日志写着

A temporary password is generated for root@localhost: rump:QiUe7qQ

注意第一个冒号后面的全是密码，不包括第一个空格

4，启动服务，net start mysql

想停止服务就是 net stop mysql

如果启动失败，之前有安装过服务可以删除mysql服务，并重新安装 mysqld -install

sc delete mysql 删除服务

sc query mysql 查询服务

5，登录mysql -uroot -p

粘贴第二步data目录下.err文件写的密码

6，修改密码

alter user root@localhost identified by '123456';

如果是跳过验证登录的，可以修改表，然后刷新权限

update mysql.user set authentication_string=password('123465') where user='root' and host='localhost';

password是加密函数

flush privileges;

my.ini

可能需要my.ini文件时，

我安装的是8.0.26版本，没有使用该文件，一切顺利

在mysql 目录下新建文件，内容如下：

[mysql]
#字符集
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
max_connections=200
port=3306
#mysql的安装目录，不是bin目录，即bin目录的上一级
basedir=xxx
#mysql的安装目录下的data目录，路径分割用反斜杠就可以了
datadir=xxx\data\
#这个是mysql启动时，跳过验证，即不需要密码直接回车可以登录
#skip-grant-tables

sqlyog

登录失败

sqlyog plugin caching_sha2_password could not be loaded 报错

1，cmd登录mysql

2，alter user 'root'@'localhost' identified by 'password' password expire never; #修改加密规则

3，alter user 'root'@'localhost' identified with mysql_native_password by 'password'; #更新用户的密码

3，alter user root@localhost identified by '123456'; # 更改新的密码

3，flush privileges; #刷新权限

改为小写

工具-首选项-字体编辑设置-事例-关键字/函数

单行注释：--

多行注释：/**/

关键字，通配符

库名，表名，列名，主从键，可以用反引号引起来，避免与关键字冲突

通配符 %： 表示一个或多个，比如 %利% 匹配 澳大利亚 ，张% 匹配 张三，张无忌

通配符 _： 下划线 ，占位一个，张_ 匹配 张三，不能匹配张无忌

数据库操作

建库

create database dbname;

库名：

基字符集： utf8

数据库排序规则：utf8_general_ci

建表

表名：

所属库：

引擎：InnoDB

字符集：utf8

核对：utf8_general_ci

列名：id, version, is_delete, gmt_create, gmt_update

数据类型：int， varchar

长度：字符长度，数字0填充显示的位数

默认值：

主键：primary, constraint, foreign, references

key fk_fkeyname (colname) constraint fk_fkeyname foreign key (colname) references dbname.tbname (refcolname)

非空：

unsigned：无符号

自增：

zerofill：零填充

更新：

注释：

修改表

alter table tbname

rename as/ 表重命名

add colname coldesc/ 字段增加

add constraint FK_foreignkeyname foreign key (foreigncolname) references dbname.tbname (refcolname)

一般外键名或叫做约束名 固定使用FK_开头

modify colname coldesc/ 字段修改

change colnameold colname coldesc/ 字段重命名

drop colname/ 字段删除

数据库常用sql

select version(); 查询数据库版本

show full processlist; 查询连接用户

show status\G 将DB所有的状态

• 可用like 查询变量名称，如：show status like '%open%';
  • Uptime 服务器工作了多少秒，
  • Questions 发往服务器的查询的数量。
  • Open_tables 打开表的数量
  • Opened_tables 已经打开的表的数量。
  • Open_files 打开文件的数量。
  • Open_streams 打开流的数量(主要用于日志记载）

show databases; 查询所有库，包括系统库，用户库。

select database(); 查询库

describe tbname; 查询表字段 ，可简写为desc

show full tables from school 查询库中所有的表#full可不加，from子句也可不加，使用当前库，

show full fields from tbname;查询表字段信息

show keys from tbname\G 查询表的key

show index from tbname\G 查询表的key 查询表的索引信息

show table status from school like 'student'\G 查询表状态

show charset;

show collation;

show create database dbname;

show create table tbname;

事务（transaction）

四个特性：

• 原子性（atomicity，或称不可分割性）、
• 一致性（consistency）、
• 隔离性（isolation，又称独立性）、
• 持久性（durability）

mysql 事务支持

• set autocommit = 0; 默认是1=开启，

• start transaction;

• commit/rollback;

• set autocommit=1;

• savepoint pointname

• rollback to savepoint pointname

• release savepoint pointname

DBMS 数据库管理系统(Database Management System)

索引

create 索引类型 索引名称 on 表名(列名)

create index key/index_tbname_colname on tbname(colnam e)

ALTER TABLE table_name ADD INDEX index_name (column_list)

• primary key
  • 不可重复
  • 一张表只能有一个，因此有固定的索引名称-primary，其他类型索引需要取名称
• unique key
  • 不可重复

• key/index

• 全文索引fulltext
  • 可以查询文本中内容
• foreing key （不算索引，算个约束）
  • 已经不推荐使用了，约束层面用应用层解决

数据库语言 (database language)

DDL 定义definition

• create
  • create table tbname(col 修饰, col2 修饰, ..., colN 修饰, primay key (colname)) engin=innodb default charset=utf8
  • 建表语句，括号最后一条不用逗号，否则报错
• drop
  • 删除表 drop table if exists tbname;

DML 管理manage

• insert
  • insert into tbname(colname,colname...) values(v1,v2...),(v1,v2...) ;
  • into 关键字可以省略
  • 第一个括号的字段名称可以省略，此时 默认使用全部字段，字段和value需要一一对应
  • values 后面的括号可以有多个，表示插入多行数据
  • 如果只插入一列数据，可以使用value，不加s，插入多列必须加s，否则报错
• update
  • update tbname set col=val,col2=val2,... where ...
• delete
  • delete from tbname where ...
  • 所有行删除
• truncate 删除表中全部数据，清空，表结构不会改变，即表的索引字段等保留
  • truncate table tbname
  • 重新设置自增
  • 不会影响事务

DCL 控制control

• grant,revoke

• 创建用户create user Uname@host identified by '123456';

• 修改密码当前用户 set password=password('123456');

• 修改密码指定用户 set password for root =password('123456');

• 重命名 rename user unameold to unamenew

• 授权 grant all privileges on . to root [with grant option];

• 查看权限 show grants for root;

• 撤销权限revoke all privileges on . from root

• 删除用户drop user root1;

DQL 查询query

• select

关键字

• distinct
• inner/left/right join
• limit
• order by desc/asc
  • 可按多列排序，order by id desc, name asc, age desc
• explain select语句
  • 分析查询
  • explain ... where match(colname) against('searchtext');

连表查询

分组查询

排序 分页

子查询

• 标量子查询
• 列级子查询
• 表级子查询

自连表查询

CRUD 增Create，查（检索）Retrieve，改Update，删Delete

where, having 子句

多个子句 连接符， and， or

数据类型

整数

• tinyint 1字节

• smallint 2字节

• int 4字节

• mediumint 3字节

• bigint 8字节

小数

• float 4字节
• double 8字节
• decimal 字符串形式的小数（总长度，小数位）

字符串

• char 定长字符串 0-255，如md5加密文本，手机号，身份证号码，

• varchar 可变长字符串 0-65535

• tinytext 2^18-1

• text 2^16-1

日期

• year 年
• date 年月日
  • 查询当前日期 select current_date
• time 时分秒
  • 查询当前时间 select current_time
• datetime 年月日时分秒
  • 查询当前日期时间 select current_timestamp
• timestamp 1970年1月1日至今的毫秒值

其他

• null

  null 不等于null

  不要使用 == <> != 来判断

  使用 is null 或 is not null

常用函数

• abs：绝对值

• ceiling()： 向上取整

• floor()： 地板，向下取整

• rand()： 随机数

• sing()： 0返回0，负数返回-1，整数返回1

• char_length()： 字符个数

• concat()： 拼接字符串

• insert()： 替换

• lower()

• upper()

• replace()

• substr()

• reverse()

• now()

• localtime()

• sysdate()

• current_timestamp()

• current_date()

• current_time()

• year(now())

• month(now())

• day(now())

• hour(now())

• minute(now())

• second(now())

• system_user()

• version()

• 聚合函数

  • count()
  • avg()
  • sum()
  • max()
  • min()

关于两表七种条件连接，和一种无条件连接

show tables;
drop table if exists tba;
drop table if exists tbb;
create table tba(id int, aname varchar(5));
create table tbb(id int, blike varchar(5));
-- a 表 根据 用户id 存了3个名字 n1, n2, n3
-- b 表 根据 用户id 存了3个爱好 l1, l2, l4
insert into tba values(1,'n1'),(2,'n2'),(3,'n3');
insert into tbb values(1,'l1'),(2,'l2'),(4,'l4');

-- tba id：1, 2, 3,
-- tbb id：1, 2, 4,

select * from tba
select * from tbb
-- delete from tbb where name like '_11'

-- full/cross/inner/straight_join join = join,  性能可能不相同，其中 full 不能指定on条件
-- left/right outer join = left/right join, 其中 outer 可以省略

-- 0, 无条件连接，俗称笛卡尔积, cross
-- 1，左连接，a 全有          1, 2, 3
-- 2，右连接，b 全有          1, 2, 4
-- 3，内连接，交集, inner     1, 2
-- 4，左外连，a 独有          3
-- 5，右外连，b 独有          4
-- 6，全连接，并集            1, 2, 3, 4
-- 7，全外连，a 独有 + b 独有 3, 4

-------------------------------------------

-- 0, 条目9  笛卡尔积 两表相乘，无条件连接，所有数据都有，但是关联性很差 cross 可省略
select * from tba cross join tbb;

-- 1, 条目3， on条件只能满足两条，a表第三条不满足因为left 显示a表全部信息，则b表用null填充
select * from tba left join tbb on tba.`id`=tbb.`id`;

-- 2, 条目3， 同上
select * from tba right join tbb on tba.`id`=tbb.`id`;

-- 3, 条目2，求交集，inner 可省略，on 可换成where，建议使用inner 和 on
select * from tba inner join tbb on tba.`id`=tbb.`id`;

-- 4, 条目1，a 独有，直接选择b表为null 的即是，这里因为b表数据为空，查询a表独有，则使用tba.* 更有意义
select tba.* from tba left join tbb on tba.`id`=tbb.`id` where tbb.`id` is null;

-- 5, 条目1，同上
select tbb.* from tba right join tbb on tba.`id`=tbb.`id` where tba.`id` is null;

-- 6, 条目4，思路，可以查a 表全有的，加右表独有的，使用 nuion 需要注意查询的列 前后两表需要一致，即前面查询语句结果是4列，后面查询语句也是要4列
select * from tba left join tbb on tba.`id`=tbb.`id` union select * from tba right join tbb on tba.`id`=tbb.`id` where tba.`id` is null;

-- 7, 条目2，思路， 查询a 独有 + b 独有
select * from tba left join tbb on tba.`id`=tbb.`id` where tbb.`id` is null union select * from tba right join tbb on tba.`id`=tbb.`id` where tba.`id` is null;