01_Mysql基本操作

一、MySql系统管理、用户管理、库表创建管理

---

1.1 系统管理

1.1.1 - 查看mysql版本

[root@mysql ~]# mysql --version
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
SHOW VARIABLES LIKE "%version%";

1.1.2 - 只允许本机登录

修改配置文件/etc/my.cnf，添加bind-address=127.0.0.1

1.1.3 - mycli源码包安装

在终端界面用于连接mysql的工具，具有自动补全、语法高亮、多行编辑的特点。
安装mycli需要Python3.8以上的版本

#1.安装编译依赖
yum groupinstall "Development Tools" -y
yum install openssl-devel libffi-devel zlib-devel bzip2-devel -y

#2.下载并编译 Python 3.8
wget https://www.python.org/ftp/python/3.8.18/Python-3.8.18.tgz
tar xzf Python-3.8.18.tgz
cd Python-3.8.18
./configure --enable-optimizations
make -j $(nproc)
sudo make altinstall # 安装到 /usr/local/bin/python3.8

#3.创建链接文件将python3.8改成python3
[root@mysql ~]# ln -s /usr/local/bin/python3.8 /usr/local/bin/python3

#4.查看pip3版本
[root@mysql ~]# pip3 --version
pip 9.0.3 from /usr/lib/python3.6/site-packages (python 3.6)

#5.pip3安装mycli
[root@mysql ~]# pip install mycli


#6.添加PATH变量
[root@mysql ~]# grep "export" ~/.bash_profile
export PATH=$PATH:/usr/local/python3/bin

#7.立即生效
[root@mysql ~]# source ~/.bash_profile

#8.开启换行
[root@localhost ~]# grep "multi_line" ~/.myclirc
multi_line = True

#9.配置文件（~/.myclirc）
[main]
prompt = "\u@\h:\d> " # 自定义提示符
multi_line = True # 默认启用多行模式
table_format = psql # 表格输出格式（可选ascii/csv）

1.1.4 - mycli yum包安装

在Rocky linux 10 上安装，centos7安装需要源码包安装

[root@mysql ~]# yum -y install python3 mysql8.4-server

[root@mysql ~]# pip3 install mycli

1.2 密码管理

1.2.1 - 使用临时密码登录mysql

cat /var/log/mysqld.log |grep "temporary password"
mysql -uroot -p

1.2.2 - mysql 5查看当前的用户名和修改mysql 登录密码

#查看当前的用户名
select user();

#修改mysql 登录密码
set password=password("P@ssword");
flush privileges;

alter user root@localhost identified with mysql_native_password by "P@ssw0rd"
flush privileges;

1.2.3 - 忘记密码重置

1.修改配置文件:/etc/my.cnf,添加:skip-grant-tables
2.重启系统:systemctl restart mysqld
3.登录数据库:mysql -uroot
4.修改密码

update mysql.user set authentication_string=password("abc1234") where user="root"

1.2.4 - mysql8.4.4 修改密码

create user 'wangwu'@'%';
alter user 'wangwu'@'%' identified by '123456';
grant all on *.* to 'wangwu'@'%';

1.2.5 - mysql8.4.4 root用户没有权限修改密码

# 1.修改密码报错

mysql> alter user 'admin'@'%' identified by '123456';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

# 2.root用户可能未分配权限，查看是否存在SYSTEM_USER 权限

SHOW GRANTS FOR 'root'@'localhost';


# 3.root用户授权SYSTEM_USER 权限

GRANT SYSTEM_USER ON *.* TO 'root'@'%';

1.3 用户管理

1.3.1 - 创建用户

create user 'zhangsan'@'localhost' identified by '123456'

1.3.2 - 授权

grant all privileges on *.* to "zhangsan"@"localhost" identified by "123456" with grant option;
flush privileges;

1.3.3 - 查看授予权限的用户

show grants;
show grants for "zhangsan"@"locahost" --查看指定的用户权限

1.3.4 - 回收权限

revoke all privileges on *.* from "zhangsan"@"localhost"

1.3.5 - 删除用户

drop user 'zhangsan'@'localhost'

1.3.6 - 允许远程登录

grant all privileges on *.* to "zhangsan"@"%" identified by "123456" with grant option;

 

1.4 数据库管理

1.4.1 - 创建数据库

create database student charset=utf8

1.4.2 - 显示数据库和表

show databases;
show tables;

1.4.3 - 切换数据库和查看当前在那个数据库中

use <database_name>;
select dababase();

1.4.4 - 修改数据库的编码方式

alter database <database_name> chaset=utf8;

1.4.5 - 查看数据库的编码集

show create database <database_name>;

1.4.6 - 删除数据库

 drop database world

 

1.5 数据表管理

1.5.1 - 创建表格

create table name(
id int primary key auto_increment,
name varchar(128),
tel varchar(32)
);

1.5.2 - 删除数据库和表

drop database <database_name>;
drop table <table_name>;

1.5.3 - 查看表结构和查看建表语句

describe student;
show create table <tableNmae>;

1.5.4 - 修改表名字

alter table <table_name> rename student_name;

1.5.5 - 将表移动到另一个库中

alter table student_name rename to test.student;

1.5.6 - 给表添加字段

alter table student add score float;

1.5.7 - 添加新的字段放在首位

alter table student add parents_age int(8) first;

1.5.8 - 指定一个字段,添加新的字段在它之后

alter table student add parents_job varchar(12) after score ;

1.5.9 - 修改字段属性

alter table student modify tel varchar(18) unique;

1.5.10 - 修改字段名字

alter table student change name student_name varchar(32);

1.5.11 - 修改字段位置

alter table <table_name> change 原字段名 新字段名 数据类型 after 指定字段;
alter table student change parents_age father_age int(8) after score;

1.5.12 - 删除字段

alter table drop 字段名;

1.5.13 - 复制表数据 -不推荐使用

create table <table_new_name>select * from <copy_table>

1.5.14 - 复制表结构

create table <table_new_name> like <copy_table>

1.5.15 - 复制表数据

insert into <table_new_name> select * from <copy_table>

1.5.16 - 查看默认的编码集

show variables like "%character%";

1.5.17 - 更改表的编码集

alter table <table_name> charset=utf8;

1.5.18 - 更改字段的编码

alter table <table_name> modify name varchar(32) charset utf8

 

二、数据的增删改查

---

 

2.1 插入语句

2.1.1 指定表插入数据

insert into <table_name> values('字段值')
insert into <table_name>(字段) values('字段值')

2.2 查询语句

2.2.1 导入数据

使用Mysql 官方的数据做查询测试，下载地址：

https://dev.mysql.com/doc/index-other.html

[root@mysql ~]# mysql -uroot -p123456 < world.sql

2.2.1 指定表条件查询数据

select 字段名,字段名 from <表名> where 字段名='条件'

2.2.3 查询多列数据合并cancat

select concat(name,"--",countrycode,"--",district) from city where countrycode="CHN"

mysql> select concat(name,"--",countrycode,"--",district) from city where countrycode="CHN"
 limit 5;
+---------------------------------------------+
| concat(name,"--",countrycode,"--",district) |
+---------------------------------------------+
| Shanghai--CHN--Shanghai                     |
| Peking--CHN--Peking                         |
| Chongqing--CHN--Chongqing                   |
| Tianjin--CHN--Tianjin                       |
| Wuhan--CHN--Hubei                           |
+---------------------------------------------+

2.2.4 查询单列多行数据合并group_concat

mysql> select group_concat(name) from city where countrycode="CHN" and district="Jiangsu" and population between 1000 and 150000;
+------------------------------------------------------------+
| group_concat(name)                                         |
+------------------------------------------------------------+
| Huai´an,Qidong,Liyang,Yizheng,Suqian,Kunshan,Zhangjiagang  |
+------------------------------------------------------------+
1 row in set (0.01 sec)

 

2.3 修改语句

2.3.1 指定表修改数据

update student set name="herry" where id=1;

2.4 删除语句

2.4.1 指定表删除数据

delete from student where id=5;

truncate 表名;

delete 是清空表中的数据
drop 是删除整张表