mysql 8.0.22安装windows及基础

一、mysql 8.0.22安装windows
1.官网下载mysql免安装版软件， 解压.
2.管理员运行CMD ,执行命令：mysqld --install
3.初始化数据库  mysqld --initialize --console  会产生随机密码
4.启动mysql服务 net start mysql
5.修改密码 mysql -h localhost -P 3306 -u root -p 输入随机密码后修改新密码 alter user 'root'@'localhost' identified by 'xxxxxxx';
6.设置环境变量 mysql  路径    path  %mysql%\bin

二、mysql常见命令
show tables;
show databases;
show engines;
show processlist;  #查询mysql有哪些后台进程
use 库名;
show table from 库名;
show index from 表名;
show function status like '%func%';
show procedure status like '%pro%';
show create function 函数名;
show create procedure 过程名;
show index from 表名;
show global variables like '%datadir%';  #展示数据文件的目录
SELECT   *   FROM   information_schema.Routines     WHERE   ROUTINE_NAME  in ('fn_getitemclock','my_pro1') ; #--查询所有函数或过程信息
desc table_name;
select version(),database(),user() from dual;

单行注释: #
单行注释: -- 中间有空格
多行注释: /*  */

三、设置root远程登录mysql数据库
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yehaver' WITH GRANT OPTION;

update user set host = '%' WHERE user='root';  #设置root用户可以在哪台机器上登录，默认是localhost本机登录，%所有机器可以登录
flush privileges;  #更新权限后需要刷新才能生效
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yehaver' PASSWORD expire never;
flush privileges;  #更新权限后再刷新才能生效

四、mysql标准语句及函数，仅提供和oracle不同的地方
CONCAT:  拼接，类似oracle ||
ifnull:  类型Oracle nvl
<=>:  安全等于， 少用
truncate 类似oracle trunc
rand() 生成(0-1)随机数  和oracle的dbms_random.value一样

mysql varchar  用字符做单位
oracle varchar2 用字节做单位

mysql 查看使用字符集 show variables like '%char%';
oracle查看使用字符集 select * from v$nls_parameters t where t.PARAMETER='NLS_CHARACTERSET';

如果字符集为 utf8(utf8mb4)  则一个汉字占用三到四个字节;实际是3个
如果字符集为 GBK(ZHS16GBK)  则一个汉字占用两个字节

length  mysql取的是字节数，oracle取的是字符数
char_length  mysql取的是字符数,
lengthb  oracle取的是字节数

now()  #当前日期及时间
curdate(),current_date(),date(now()),  #仅当前日期
curtime(),current_time(),  #仅当前时间
current_timestamp(),  #日期时间
year(now())  #对应时间的年份
month(now()) #对应时间的月份
day(now())  #对应时间的日份
hour(now()) #对应时间的小时
minute(now()) #对应时间的分钟
second(now()) #对应时间的秒钟

str_to_date('20201212 12:23:34','%Y%m%d %H:%i:%s') 字符串转换为日期
%Y  YYYY  四位年
%y  YY  两位年
%m  MM  两位月
%c  M   前不加0的月
%d  DD   加0的日
%H  HH24  24小时制
%h  HH12  12小时制
%i  mm    加0分钟
%s  ss    加0秒钟
date_format(now(),'%Y%m%d %H:%i:%s')  日期格式转换为字符串

if(10<5,'大','小') 类似oracle case when else end mysql也有case when和oracle一样

分页查询
select * from COLUMNS order by id desc limit offset,size;  -- offset:从0开始，展示第五条记录就是4;size:页面展示数据多少条;
select * from COLUMNS order by id desc limit (page-1) * size,size;  page第几页，size每页展示条数

五、DML，DDL语句与oracle不同地方，mysql 8.0也开始支持窗口函数(分析函数)
update boys bo
inner join beauty b 
on  bo.id = b.id
set b.phone = '114'   -- oracle需要使用merge into
where bo.boyname = '张无忌';

delete table1别名,table2别名
from table1 别名1
inner|left|right join table2 别名2
on 链接条件
where 筛选条件

mysql建自增长列id,id会随着增加数据自增长。
当使用delete from 删除此自增长表后，再插入数据，自增长列的值从断点开始
当使用truncate table删除后，再插入数据，自增长列的值从1开始

CREATE DATABASE IF NOT EXISTS BOOKS; --创建库
ALTER DATABASE BOOKS CHARACTER SET GBK; --修改库的字符集
DROP DATABASE IF EXISTS BOOKS; --删除库
CREATE TABLE IF EXISTS MY_TABLES  --建表
(
);
-----------------总结，基本上所有ddl语句都可以加IF NOT EXISTS或者IF EXISTS-----------
create table my_test
(
col1 int primary key auto_increment,  -- 如果插入数值超出了整形范围，会报out of range异常，但数据可能会（低版本插入，高版本搞错）插入临界值;
                            --auto_increment_increment（步长） 和auto_increment_offset（初始值1）参数来设置； set auto_increment_increment=3;修改步长等参数
                            -- 插入主键自增长列（一个表只能一个自增长列）时，可以指定自增长主键值（只能时整数型），当指定
col2 int unsigned, -- 整形无负号，插入负数为0，会报out of range异常，但数据可能会（低版本插入，高版本搞错）插入临界值
col3 int(8) not null,  --- int默认长度为11，也可自己设置长度，会报out of range异常，但数据可能会（低版本插入，高版本搞错）插入临界值
col4 float,  -- 如果插入数值超出了整形范围，插入临界值
col5 double default 1, -- 如果插入数值超出了整形范围，插入临界值
col6 decimal(32,4), -- 如果插入数值超出了整形范围，插入临界值
col7 set('工程师','设计师','老师'),  -- 列表型，可以给多个值
col8 enum('男','女'),  -- 枚举型
constraint pk primary key(id), #主键  ； 可以是联合多个字段主键
constraint uq unique(seat), #唯一约束  ； 可以是联合多个字段唯一
constraint ck check(gender in ('男','女')), #check约束
constraint fk_stuinfo_major foreign key(major_id) references major_table(id) (on delete cascade / on delete set null)#外键，依赖主键表的字段id必须是主键或者唯一键
)engine=innodb auto_increment=1 default charset=utf8;

ALTER TABLE STUINFO CHANGE `NAME` my_name date;--修改表STUINFO的字段名NAME为my_name且为date类型，修改字段名和字段类型都用此语句
ALTER TABLE STUINFO RENAME TO STUINFOS; --修改表名一样
alter table stuinfo MODIFY my_name DATE; --改类型和oracle一样
alter table my_test add col_first int first;  -- 增加字段可以到指定位置，oracle不行
alter table my_test add col_first2 int after col1;-- 增加字段可以到指定位置，oracle不行

DROP INDEX INDEX_NAME ON TABLE_NAME; #和oracle不一样，增加了on table_name

tinyint  1  --少用
smallint 2  --少用
mediumint 3  --少用
int/integer 4  --常用
bigint 8  --常用

float(M,D) 4
double(M,D) 8 
decimal(M,D) M+2  

char(m)
varchar(m)
binary  -- 保存图片,二进制
varbinary  -- 保存图片,二进制
text   -- 大文本
blob -- 保存图片,二进制

date 4   只能保存日期
datetime  8  日期时间  1000-9999
timestamp 4  日期时间  1970-2038  使用now()插入数据时会受时区、语法、版本等限制
time  3 只有时间
year  1 只有年

六、mysql重要配置参数
set autocommit=on/off;  --mysql事务自动提交； set autocommit=off;设置将自动提交关闭,但只对当前session生效
start transaction;
dml
commit;或者rollback;

SQL标准中支持4种事务隔离级别，MySQL innodb引擎支持全部这4种事务隔离级别；
查询当前库的事务隔离级别 show variables like '%transaction_isolation%';

READ_UNCOMMITTED（读未提交），
READ_COMMITTED（读已提交），  oracle默认模式
REPEATABLE_READ（可重复读）， mysql安装数据库默认模式
SERIALIZABLE（串行读），  太严格，不采用

set session transaction isolatin level repeatable read; --设置当前会话隔离级别
set global transaction isolation level repeatable read; --设置系统当前隔离级别

七、变量(系统变量与用户变量)
系统变量,不加session和global时默认查session级别变量
show session variables;  -- 查看session系统变量
show global variables;  -- 查看全局系统变量

show session variables like 'autocommit';   -- 查看session系统指定的变量值
show global variables like 'autocommit';   -- 查看global系统指定的变量值
select @@session.autocommit; -- 查看session系统指定的变量值
select @@global.autocommit; -- 查看global系统指定的变量值

set session autocommit=off;  --设置系统变量的值
set global autocommit=off;  --设置系统变量的值

用户变量；只能用在当前会话，必须加@，且不用申明类型
SET @MY_VAR1 = 100; --声明变量并赋值
SET @MY_VAR2 := 101;--声明变量并赋值
SELECT @MY_VAR3 := 102;--声明变量并赋值
SELECT sum(col1) INTO @MY_VAR2 FROM 表名; --给变量赋值
SELKCT @MY_VAR2;  -- 查看变量值

局部变量；只能用在begin ... end中，不需加@，必须申明类型
DECLARE LOCAL_VAR1 INT;
DECLARE LOCAL_VAR2 DECIMAL(34,4) DEFAULT 1000;
SET local_VAR1 := 100;

八、过程与函数
DELIMITER $  定义类似CMD界面执行mysql时，以$做为开始执行

create procedure my_pro4(in i_name varchar(20),in i_age int)  -- in out  inout放入参前面，和oracle不一样， 入参也要指定长度,or replace 没有
begin
  declare v_var1 int default 0;
  
  insert into stuinfo_2
  select i_age,i_name from dual;
  
  select count(1) into v_var1 from stuinfo;
  
end $   ---创建过程要以$（DELIMITER定义的）符号结束

call my_pro1('哈哈')$  ---调用此过程，执行命令都要在尾部加上$（DELIMITER定义的）

create function my_func2(i_name varchar(20)) returns int  #--函数入参不能指定IN OUT INOUT,因为全部都是in，所以无需指定；返回要加returns
READS SQL DATA   #--必须指定，否则需要设置 set global log_bin_trust_function_creators=TRUE; 主从备份的原因
/*这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。
*/
begin
    declare v_var1 int default 0;
    select count(1) into v_var1
      from my_test2
    where user_name = i_name;
    
    return v_var1;
end $

select my_func2('yangweiming')$ #-- 调用此函数要先授权 Grant all privileges on test.* to 'root'@'%'$

分支语句1
create procedure p_case(in i_score int)
begin
  case when i_score > 90 then select 'a';
       when i_score > 80 then select 'b';
       when i_score > 60 then select 'c';
       else select 'd';
  end case;  # 必须加end case
end $

分支语句2
create procedure p_if(in i_score int)
begin
  if i_score > 90 then select 'a';
       elseif i_score > 80 then select 'b';
       elseif i_score > 60 then select 'c';
       else select 'd';
  end if;
end $

分支语句3
create procedure p_while(in i_max int)
begin
  declare i int default 1;
  declare j int default 1;
  while i <= i_max do
    set i := i + 1;
    set j := j+i;
  end while;
  select i;
  select j;
end $