5.mysql数据库2024-10-26

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
SHOW DATABASES;
SELECT DATABASE();
SHOW TABLES FROM my_data;
SHOW CREATE DATABASE my_data;
USE my_data;
SELECT DATABASE();
SHOW DATABASES;
SHOW TABLES FROM my_data;
DROP DATABASE IF EXISTS database_name;

CREATE DATABASE IF NOT EXISTS blog_platform CHARACTER SET utf8mb4;
USE blog_platform;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
ALTER DATABASE blog_platform COLLATE utf8mb4_0900_as_cs;
DROP DATABASE IF EXISTS blog_platform;

#1.创建数据表DDL
CREATE DATABASE IF NOT EXISTS my_data;
use my_data;
select database();
drop table if exists social_work_library;
create table if not exists social_work_library (
social_name varchar(20) comment '姓名',
social_id varchar(40) comment '身份证号码',
social_sex varchar(10) comment '性别',
social_age tinyint unsigned comment '年龄',
social_height DOUBLE(4,1) COMMENT '身高',
social_money int,
social_phone varchar(11) comment '手机号码',
social_qq int unsigned comment 'qq号码',
social_wechat varchar(40) comment '微信号码',
social_address varchar(40) comment '住址',
social_remarks varchar(200) COMMENT '备注',
social_create_time datetime default current_timestamp COMMENT '创建时间',
social_updata_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间'
)charset = utf8mb4 comment '社会人员';
show tables;

#2.添加数据DML

insert into social_work_library values(, , ,);
INSERT INTO social_work_library(列名, , ) VALUES(, , ,);
INSERT INTO social_work_library(列名, , ) VALUES(, , ,), (, , );

SHOW CREATE table social_work_library;

INSERT INTO social_work_library(social_name,social_phone) VALUES('张三',12345678901),('李四',152533);

INSERT INTO social_work_library(social_name,social_id, social_sex, social_age, social_height, social_meoner, social_phome, social_qq, social_wechat, social_address, social_remarks) VALUES(),();

show tables from my_data;

#修改数据
update social_work_library set social_name='王五' where social_name='张三'；

#删除数据
delete from social_work_library WHERE social_name='张三'；

#3.查询数据DQL

select version();

SELECT * FROM social_work_library;

select social_name, social_phone from social_work_library;

SELECT social_work_library.social_name, social_work_library.social_phone FROM social_work_library;

SELECT social_name '姓名', social_phone '电话号' FROM social_work_library;
SELECT social_name as '姓名', social_phone as '电话号' FROM social_work_library;

SELECT '由root提供' as '新列', social_name, social_phone FROM social_work_library;
SELECT social_name, social_phone, '由root提供' AS '新列' FROM social_work_library;
#去重
SELECT distinct social_name, social_phone FROM social_work_library;

#展现表结构
describe social_work_library;
desc social_work_library;

SELECT * FROM social_work_library WHERE social_name = '张三';

select 4/2,100,100+2 div 2;

WHERE NAME_CONST == NULL

SELECT * FROM social_work_library where social_work_library.`social_name` is not null;
SELECT * FROM social_work_library WHERE social_work_library.`social_phone` like '152%';

#函数
select PI(88);
ROUND() 四舍五入取整
#聚合函数
count(*) 统计

#分组查询
select 分组列，分组列，聚合函数 from 列名 [where ] [ group by 分组列，分组列 having 分组后的条件]

select social_create_time,count(*) from social_work_library GROUP BY social_create_time;

#排序 asc正序 desc倒序
select social_updata_time from social_work_library order by social_updata_time;
SELECT * FROM social_work_library ORDER BY social_updata_time, social_phone desc;

#分页查询（数据切割） -- 放在select最后
limit 0,2; 查询两条，从头开始查查两条 limit 2,3; 从第3条数据开始查查3条数据
limit 偏移量查的条数

SELECT * FROM social_work_library ORDER BY social_updata_time, social_phone DESC limit 0, 2;
SELECT * FROM social_work_library ORDER BY social_updata_time, social_phone DESC LIMIT 0, 1;
SELECT * FROM social_work_library ORDER BY social_updata_time, social_phone DESC LIMIT 1, 1;

#非空约束 NOT NULL
#创建时添加
CREATE TABLE IF NOT EXISTS my_life (
social_int INT AUTO_INCREMENT,
social_name VARCHAR(20) not null COMMENT '姓名',
social_id VARCHAR(40) COMMENT '身份证号码',
social_sex VARCHAR(10) COMMENT '性别',
social_age TINYINT UNSIGNED COMMENT '年龄',
social_height DOUBLE(4,1) COMMENT '身高',
social_money INT,
social_phone VARCHAR(11) COMMENT '手机号码',
social_qq INT UNSIGNED COMMENT 'qq号码',
social_wechat VARCHAR(40) COMMENT '微信号码',
social_address VARCHAR(40) COMMENT '住址',
social_remarks VARCHAR(200) COMMENT '备注',
social_create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
social_updata_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间'
)CHARSET = utf8mb4 COMMENT '朋友';

#建表后添加
alter table social_work_library modify social_address VARCHAR(40) not null COMMENT '住址';

######修改不允许远程登录

USE mysql;
SELECT * FROM USER;
UPDATE USER SET HOST = '%' WHERE USER = 'root';
SELECT * FROM USER;
SELECT HOST,USER FROM USER;
FLUSH PRIVILEGES;
# 授权法
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRECEDES;

#添加自增长约束主键primary key auto_increment
USE my_data;
desc social_work_library;
ALTER TABLE social_work_library MODIFY social_int int auto_increment;
ALTER TABLE social_work_library MODIFY social_int INT primary key auto_increment;

CREATE TABLE IF NOT EXISTS social_work_library (
social_int INT primary key auto_increment,
social_name VARCHAR(20) COMMENT '姓名',
social_id VARCHAR(40) COMMENT '身份证号码',
social_sex VARCHAR(10) COMMENT '性别',
social_age TINYINT UNSIGNED COMMENT '年龄',
social_height DOUBLE(4,1) COMMENT '身高',
social_money INT,
social_phone VARCHAR(11) COMMENT '手机号码',
social_qq INT UNSIGNED COMMENT 'qq号码',
social_wechat VARCHAR(40) COMMENT '微信号码',
social_address VARCHAR(40) COMMENT '住址',
social_remarks VARCHAR(200) COMMENT '备注',
social_create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
social_updata_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间'
)CHARSET = utf8mb4 COMMENT '社会人员';

desc social_work_library;
#删除表
USE my_data;
drop table social_work_library;
show tables;

###多表查询垂直合并union (去掉重复行) / union all (不去掉重复行)
第一条查询语句 union 第二条查询语句

###多表查询水平合并 jion on / left jion on

select * from 表1 join 表2 on 表1.列1 = 表2.列2；

SELECT * FROM 表1 left JOIN 表2 ON 表1.列1 = 表2.列2；

SELECT * FROM 表1 right JOIN 表2 ON 表1.列1 = 表2.列2；

#子查询（子查询语句）

#复制整张表

1.复制表结构
create table table1 like table2;
2.复制进去数据
insert into table1 (select * from table2);
一步到位
CREATE TABLE table1 as (SELECT * FROM table2);

###数据库事务--全部命令成功才修改数据

方案1：
1.手动提交模式（一个链接内有效）
set autocommit = false 或者 SET autocommit = 0
2.开启自动提交模式
SET autocommit = true 或者 SET autocommit = 1
3.查看是否自动提交
show variables like 'autocommit';

使用：输入多条命令然后最后输入commit;提交修改
commit; 或者 ROLLBACK;（回滚）

#查看所有mysql系统变量
SHOW VARIABLES;

方案2：
在自动提交模式开启一个事务
start transaction;
输入多条命令
COMMIT; 或者 ROLLBACK;（回滚）

#隔离级别--并发的事务之间避免干扰
1.read-uncommitted
2.read-committed
3.REPEATABLE-READ
4.serializable

SET TRANSACTION_isolation = '隔离级别';

###用户权限控制管理--数据库级别表级别特定操作
1.创建用户

create user 'username'@'localhost' identified by 'password';

1).username:创建的用户名
2).localhost:允许主机 %所有
3).password: 创建的用户名密码

2.赋予全部权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

3.赋予部分权限
GRANT select,insert ON *.* TO 'root'@'%';

4.撤销全部权限
revoke ALL PRIVILEGES ON *.* from 'root'@'%';

5.撤销部分权限
REVOKE SELECT,INSERT ON *.* FROM 'root'@'%';

6.查看用户权限
show GRANTs for 'root'@'%';

7.查看用户列表
select user,host from mysql.`user`;
SELECT * FROM mysql.`user`;

8.删除用户
drop user '用户名';
DROP USER 'root'@'localhost';

10.练习
CREATE USER 'LZY'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'LZY'@'%';
SELECT USER,HOST FROM mysql.`user`;
use mysql;
SHOW GRANTs FOR 'LZY'@'%';

###备份与还原
1.全量备份
mysqldump -u username -p password database_name > backup.sql
mysqldump -u username -p PASSWORD database_name 表名 > backup.sql
mysqldump -u username -p PASSWORD database_name 表1 表2 > backup.sql

2.全量备份还原(先创建库)
create database database_name；
mysql -u username -p PASSWORD database_name < backup.sql

###Binlog日志(二进制文件)
mysql配置文件：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
log-bin="DESKTOP-QD8OE7E-bin"

清空原有binlog文件
reset master；
重启一个新的日志文件
flush logs；

查看日志文件和命令清单
1.查看有那些日志文件
show binary logs;
2.查看日志文件信息
show binlog events;
SHOW BINLOG EVENTS in '清单文件名' from pos limit offset ,number; # from 从哪个位置开始查

SHOW BINLOG EVENTS in 'DESKTOP-QD8OE7E-bin.000002';

#误删除数据恢复--mysqlbinlog工具
查看日志文件类型
mysqlbinlog -v binlog;
mysqlbinlog -v 'DESKTOP-QD8OE7E-bin.000002';

#跳过步骤找回数据--把历史命令日志全部导出成sql脚本
cd 到日志文件
mysqlbinlog 'DESKTOP-QD8OE7E-bin.000002' > d:DESKTOP-QD8OE7E-bin.sql; #全部导出
mysqlbinlog --stop-position=删除命令开始的pos 'DESKTOP-QD8OE7E-bin.000002' > d:DESKTOP-QD8OE7E-bin.1.sql; #误删除位置之前的操作导出
mysqlbinlog --start-POSITION=删除命令结束的下一条命令开始的pos 'DESKTOP-QD8OE7E-bin.000002' > d:DESKTOP-QD8OE7E-bin.2.sql;#误删除位置之后的操作导出