MYSQL

Mysql8.0 OCP

以下是我在学习Mysql8.0 OCP过程中的一些笔记和总结。

1. Introduction to MySQL

mysql -u root -p

alter user user() inentified by 'new password';
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

docker pull mysql/mysql-server
dockerl load -i mysql-enterprise-server-version.tar
docker image

docker run --name=mysql1 -d mysql/mysql-server
docker logs mysql1
docker logs mysql1 2>$1 | GREP GENERATED
DOCKER EXEC -IT MYSQL1 MYSQL -UROOT -P

mysqld --user-mysql --datadir=/ver/lib/mysql --socket-/tmp/msqyl.sock

mysql_secure_insatllation
mysql_tzinfo_to_sql
mysql_upgrade

mysql_config_editor
- MYSQL_TEST_LOGIN_FILE
- .mylogin.cnf
- mysql --login-path=admin
- mysql -L admin


mysqlbinlog
mysqldumpslow
mysql_ssl_rsa_setup
ibd2sdi


[admin]
user = root
password = oracle
host = 127.0.0.1


mysql_config-editor set --login-path=login-path --user=username -- password --host=hostname

mysql_config_editor print --login-path=login-path
mysql_config-editor print --all
mysql_config_editor remove --login-path=login-path

mysql
mysqladmin
mysqldump/mysqlpump
mysqlimport
mysqlslap
mysqlshwo
mysqlcheck
mysqlsh

--user --password --host --socket

mysqladmin
--relative --sleep

mysqlcheck
--analyze --check --check-upgrade

semanage prot -a -t mysqld_port_t -p tcp 33060-33062
/datadir

W: http://mirrors.aliyun.com/docker-ce/linux/ubuntu/dists/noble/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.

# 简单粗暴
cp /etc/apt/trusted.gpg /etc/apt/trusted.gpg.d/trusted.gpg



## Ubuntu 16.04
addgroup mysql
adduser -g mysql -m mysql 
vim /etc/passwd #/sh -> bash
vim /etc/sudoer #mysql ALL(ALL) NOPAAWORD: ALL
su - mysql
sudo apt-get install make cmake gcc g++ bison libncurses5-dev build-essential


# 报错修改：mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
sudo apt-get install libncurses5-dev
find / -name libncurses.so*
ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6.4 /usr/lib/x86_64-linux-gnu/libncurses.so.5

#报错修改： mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
find / -name libtinfo.so*
ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6.4 /usr/lib/x86_64-linux-gnu/libtinfo.so.5

su - mysql
sudo mkdir -p /usr/local/mysql/data
sudo chown -R mysql:mysql /usr/local/mysql/data
sudo chmod 755 /usr/local/mysql/data

# 环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> ~/.bashrc
source ~/.bashrc

# 初始化mysql数据库
sudo mysqld --initialized --user=mysql
# 记住这里初始化的随机密码，否则很难办

# 启动
sudo systemctl start mysql
sudo systemctl status mysql 
mysql -uroot -p
# 输入刚才的随机密码

# 进入后立刻修改root密码
alter user 'root'@'localhost' identified by '123456';
# 测试退出后重新登录root
\q
mysql -uroot -p # 123456

# 创建用户
create user 'mysql'@'localhost' identified by '123456';
# mysql：用户名
# localhost：访问限制为本地 %为所有
# create user 'mysql'@'%' identified by '123456';

# 删除用户
create user username@hostname identified by '123456';
drop user if exists username@hostname;

# 查看用户
select user,host from mysql.user;
select user,host from mysql.user where user = 'mysql';

# 修改用户密码
alter user mysql@localhost identified by '123456789';

# 创建数据库
# DDL创建
mysql -u root -p
create database mydb;
# 使用mysqladmin创建
mysqladmin -uroot -p create new_database_name;
# 指定字符集和排序规则
mysqladmin -uroot -p create new_database_name --default-character-set=utf8mb4 --default-collation=utf8mb4_general_ci;

# 删除数据库
# DDL删除
mysql -u root -p
drop database mydb;
# 使用mysqladmin删除
mysqladmin -uroot -p drop database_name;

# 选择数据库
mysql -u root -p
use mydb;
# 指定数据库登入
mysql -u root -p -D mydb


# 创建表
create table t1(id int primary key auto_increment,name varchar(50),age int,email varchar(50),create_date timestamp default current_timestamp);
# 修改表
# 添加列
alter table t1 add column phone varchar(50) after name;
alter table t1 add column phone varchar(50) after name;
# 修改列
# 删除列
alter



# 赋权
grant all privileges on database_name.* to 'username'@'hostname';
grant select on databse_name.* to username@hostname;
# 刷新权限
flush privileges;
# 查看权限
show grants for 'username'@'hostname';
show grants for username@hostname;



# 查看数据库
show databases;

# 使用数据库
use mysql;

# 查看当前使用数据库
select database();
status;

# 查看所有的表
show tables;

# 查看表结构
describe table_name;


# 查看创建数据库信息
show create database mydb;
# 查看创建表信息
show create table t1;

# 数据类型
1. 数值
Tinyint 1Byte
smallint 2B
mediumint 3B
int 4B
bigint 8B
float 4B
double 8B
Decimal(A,B)
2. 日期/时间
3. 字符串
4. 枚举
5. 空间数据类型

union 去重
union all 不去重

order by 可以使用数字位置表示排序字段
order by 3 desc, 1 asc;

v8.0.16 可以对NULL列进行放前或者放后
order by name desc nulls first/last;

group by name with rollup;

inner join 简单理解等价=，但是inner join是先计算连接结果，再WHERE过滤；WHERE = 是先生成笛卡尔积，再条件过滤，肯定inner join效率更高。
left join / right join

mysql没有full outer join

1. inner join = inner outer join = join ≠ where+=
2. left join = left outer join 
3. right join = right out join
4. 没有full outer join,替换为 a left join b on (a.id = b.id) union/union all a right join b on (a.id = b.id)


NULL值处理
is null
is not null
<=> 等值或者等NULL

coalesce(a,b,c,d,...) 特殊的 coalesce(a,b) = ifnull(a,b) 只接收两个参数


# 事务控制
begin / start transaction
commit / commit work
rollback / rollback work
savepoint identifier # 保存点
release savepoint identifier # 删除保存点
rollback to identifier # 回滚到标记点
set transaction # 设置事务级别 read uncommitted 读未提交/ read committed 读已提交/ repeatable read 可重复读/ serializable 序列化

set autocommit = 0 # 禁止自动提交
set autocommit = 1 # 开启自动提交


begin;
update xxx set xxx where xxx;
select xxx set xxx where xxx;
if (xxx) then commit; else rollback; end if;

MyISAM

InnoDB

聚集（集簇）索引，主键索引+数据在一起；
非聚集索引，主键索引、数据分开管理。

为什么建表要设置主键，主键最好用自增整数字段？为什么最好别用UUID做主键？

基于B-Tree的结构，与多层比较机制，它要经常比较，所以自增整型能快速索引，另外存储用的少，省钱，主键用BigInt。

Hash，通过取模运算，找落点，等值查询必B+要快，但是没办法支持范围查找。