mysql>select * from t1;
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|1| xiaoming |18| 0x00 ||2| xiaowang |18| 0x00 ||4| xiaoqiang | NULL | 0x00 ||5| xiaojun |20| 0x00 ||6| xiaoli |18| 0x00 |
+----+-----------+------+--------------------+
mysql> alter table t1 add isdelete bit default 0;#设计逻辑删除
mysql>select id,name from t1 where isdelete=0;
+----+-----------+
|id| name |
+----+-----------+
|1| xiaoming ||2| xiaowang ||4| xiaoqiang ||5| xiaojun ||6| xiaoli |
+----+-----------+
mysql> update t1 setisdelete=1 where id=4;
mysql>select id,name from t1 where isdelete=0;
+----+----------+
|id| name |
+----+----------+
|1| xiaoming ||2| xiaowang ||5| xiaojun ||6| xiaoli |
+----+----------+
4 rows inset(0.00 sec)
修改(改)
#表结构修改
after table t1 [add | drop | modify | change]
alter table t1 add addr varchar(20);#添加addr字段到最后
alter table t1 add job varchar(20) after name;#添加job字段到name后
alter table t1 drop job;#删除job选项
alter table t1 modify addr varchar(20) after name;#把addr的字段放到name后面
alter table t1 change addr job varchar(20);#把addr的字段修改为job字段
alter table t1 modify age tinyint unsigned default 20;#把age设置默认值为20
alter table t1 add isdelete bit default 0;#设计逻辑删除#修改表内容
update ti setname="xiaoming" where id=1;#不加where会更改所有的表明为“xiaoming”
update ti setname="xiaoming" where name="xiaoming";#或者可以这样
update ti setname="xiaoming",age="21" where id=1;#可以添加,更改多个字段
查询(查)
查看表结构
desc tb-name;
查看表内容
#全字段查询,不建议select * from t1;#查询指定字段select name,age from t1;
根据运算符查询
> 大于 |>= 大于等于 |< 小于 |<= 小于等于 |= 等于 |!= 不等于
#查询id大于4的用户id,用户以及年龄select id,name,age from t1 where id>4;#and 多个条件同时满足 #or 多个条件,满足其一即可
mysql>select id,name,age from t1 where id<=2 or name="xiaoli";
+----+----------+------+
|id| name | age |
+----+----------+------+
|1| xiaoming |18||2| xiaowang |18||6| xiaoli |18|
+----+----------+------+
3 rows inset(0.00 sec)
模糊查找
like 模糊查找
% 匹配任意多个字符
_ 匹配单个字符
#查找名字中含有xiao字符的数量
mysql>select id,name,age from t1 where name like "xiao%";
+----+-----------+------+
|id| name | age |
+----+-----------+------+
|1| xiaoming |18||2| xiaowang |18||4| xiaoqiang | NULL ||5| xiaojun |20||6| xiaoli |18|
+----+-----------+------+
#插入一条数据,查找名字中含有xiao并且后面跟一个单字符的数据
insert t1(name) values("xiaom");select id,name,age from t1 where name like "xiao_";# _ 可以加多个#通过变量查询主库的
mysql> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON || sql_log_bin | ON |
+---------------+-------+
2 rows inset(0.01 sec)
范围查找
in 表示非连续的范围
between A and B 表示一个连续的范围内
not in 不在某个条件内
is null 空值
#查找id等于2,4或者6的数据
mysql>select * from t1 where idin(2,4,6);
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|2| xiaowang |18| 0x00 ||4| xiaoqiang | NULL | 0x01 ||6| xiaoli |18| 0x00 |
+----+-----------+------+--------------------+
#查找id等于2到6的数据
mysql>select * from t1 where id between 2 and 6;
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|2| xiaowang |18| 0x00 ||4| xiaoqiang | NULL | 0x01 ||5| xiaojun |20| 0x00 ||6| xiaoli |18| 0x00 |
+----+-----------+------+--------------------+
#查找id不是2,4或者6的数据
mysql>select * from t1 where not idin(2,4,6);
+----+----------+------+--------------------+
|id| name | age | isdelete |
+----+----------+------+--------------------+
|1| xiaoming |18| 0x00 ||5| xiaojun |20| 0x00 |
+----+----------+------+--------------------+
聚合
#查询表里的最大年纪 最大max 最小min
mysql>select max(age) from t1;
+----------+
| max(age)|
+----------+
|20|
+----------+
#查找age最大的用户所有信息
mysql>select * from t1 where age in(select max(age) from t1);
+----+----------+------+--------------------+
|id| name | age | isdelete |
+----+----------+------+--------------------+
|5| xiaojun |20| 0x00 ||7| xiaohua1 |20| 0x00 ||8| xiaodong |20| 0x00 |
+----+----------+------+--------------------+
#统计isdelete=1的数目
mysql>select count(*) from t1 where isdelete=1;
+----------+
| count(*)|
+----------+
|2|
+----------+
查找空值
mysql>select * from t1 where age is null;
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|4| xiaoqiang | NULL | 0x01 |
+----+-----------+------+--------------------+
排序
#排序 order by 字段 asc 根据列从小到大(默认)
mysql>select * from t1 order by id;
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|1| xiaoming |18| 0x00 ||2| xiaowang |18| 0x00 ||4| xiaoqiang | NULL | 0x01 ||5| xiaojun |20| 0x00 ||6| xiaoli |18| 0x00 ||7| xiaohua1 |20| 0x01 ||8| xiaodong |20| 0x00 |
+----+-----------+------+--------------------+
#排序 order by 字段 desc 根据列从大到小
mysql>select * from t1 order by id desc ;
+----+-----------+------+--------------------+
|id| name | age | isdelete |
+----+-----------+------+--------------------+
|8| xiaodong |20| 0x00 ||7| xiaohua1 |20| 0x01 ||6| xiaoli |18| 0x00 ||5| xiaojun |20| 0x00 ||4| xiaoqiang | NULL | 0x01 ||2| xiaowang |18| 0x00 ||1| xiaoming |18| 0x00 |
+----+-----------+------+--------------------+
#分组统计同名的人的个数
group by 字段
mysql>select count(*),name from t1 group by name;
+----------+-----------+
| count(*)| name |
+----------+-----------+
|1| xiaoming ||1| xiaowang ||1| xiaoqiang ||1| xiaojun ||1| xiaoli ||1| xiaohua1 ||1| xiaodong |
+----------+-----------+
限制
#查询显示三行
mysql>select name,age from t1 limit 3;
+-----------+------+
| name | age |
+-----------+------+
| xiaoming |18|| xiaowang |18|| xiaoqiang | NULL |
+-----------+------+
#查询从第3行(不包含)开始往下2行
mysql>select name,age from t1 limit 3,2;
+---------+------+
| name | age |
+---------+------+
| xiaojun |20|| xiaoli |18|
+---------+------+
#显示从第三行后的4行
mysql>select * from t1 limit 4 offset 3;
+----+----------+------+--------------------+
|id| name | age | isdelete |
+----+----------+------+--------------------+
|5| xiaojun |20| 0x00 ||6| xiaoli |18| 0x00 ||7| xiaohua1 |20| 0x01 ||8| xiaodong |20| 0x00 |
+----+----------+------+--------------------+
外键查询
create table t1(
tid int unsigned auto_increment primary key,
tname varchar(20),
class varchar(20));
create table s1(
sid int unsigned auto_increment primary key,
sname varchar(20),
tid int,
foreign key (tid) references t1(tid));select s1.sname,t1.tname,t1.class from t1,s1 where t1.tid=s1.tid;
+----------+----------+-------+
| sname | tname | class |
+----------+----------+-------+
| xiaoming | wulaoshi | linux |
+----------+----------+-------+
视图(用于简化查询)
mysql>select s1.sname,t1.tname,t1.class from t1,s1 where t1.tid=s1.tid;
+----------+----------+-------+
| sname | tname | class |
+----------+----------+-------+
| xiaoming | wulaoshi | linux |
+----------+----------+-------+
mysql> create view v1 as select s1.sname,t1.tname,t1.class from t1,s1 where t1.tid=s1.tid;
mysql>select * from v1
+----------+----------+-------+
| sname | tname | class |
+----------+----------+-------+
| xiaoming | wulaoshi | linux |
+----------+----------+-------+
#把第一条命令赋值与v1试图,以后查询只要查询v1的所有,就相当于执行了第一条命令
4、修改
修改密码8.0
alter user user() identified by 'mysql-master01';
ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
修改密码5.7
setpassword=password('mysql-master01');
update user setpassword=password("123") where user="root" and host="localhost";
查询
查询主库的状态
mysql> show master status ;
+---------------+----------+--------------+------------------+----------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set|
+---------------+----------+--------------+------------------+----------------+
| binlog.000001 |659||||
+---------------+----------+--------------+------------------+----------------+
1 row inset(0.00 sec)
mysql> show binlog events in"mysql-bin.000002";
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 |4| Format_desc |1|125| Server ver: 8.0.25, Binlog ver: 4|| mysql-bin.000002 |125| Previous_gtids |1|156||| mysql-bin.000002 |156| Anonymous_Gtid |1|235| SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002 |235| Query |1|491| ALTER USER'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$A68 aQx&1(dD6a(2M8FCN8NXbLh9lRB90VLcWJDnSPDqguAsMS4b5UMMvINC' /* xid=3 */ || mysql-bin.000002 |491| Anonymous_Gtid |1|568| SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002 |568| Query |1|658| flush privileges || mysql-bin.000002 |658| Anonymous_Gtid |1|737| SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002 |737| Query |1|956| CREATE USER'slave01'@'192.168.155.%' IDENTIFIED WITH 'mysql_native_password' AS '*4512F73DF9A45D6AFA3887D36BDF8C1E3F5BB439' /* xid=6 */ || mysql-bin.000002 |956| Anonymous_Gtid |1|1033| SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002 |1033| Query |1|1191| GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'192.168.155.%' /* xid=7 */ || mysql-bin.000002 |1191| Anonymous_Gtid |1|1268| SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002 |1268| Query |1|1358| flush privileges |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows inset(0.00 sec)
查看所有用户(用户名、给谁授权)
SELECT user,host FROM mysql.user;
查看单个用户所有情况
SELECT * FROM mysql.user WHERE user='root'\G
查看数据库创建的sql命令
show create database test;
查看binlog文件
[root@web data]# mysqlbinlog web-relay-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;# at 4 #220308 22:17:55 server id 101 end_log_pos 125 CRC32 0xf203a455 Start: binlog v 4, server v 8.0.25 created 220308 22:17:55 at startup# This Format_description_event appears in a relay log and was generated by the slave thread.# at 125#220308 22:17:55 server id 101 end_log_pos 156 CRC32 0x1db3ec64 Previous-GTIDs# [empty]# at 156#220308 22:18:03 server id 101 end_log_pos 207 CRC32 0x90752069 Rotate to web-relay-bin.000002 pos: 4
SET @@SESSION.GTID_NEXT='AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;# at 4 # at 125 # at 156指的是binlog对应的数值
mysql自动补全安装yum install python3 python3-devel python3-pip -ypip install mycli然后使用mycli连接数据库1、创建创建用户以及密码。CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';创建带过期时间用户:CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY