“Mysql 基础 Mysql study之常用操作”的版本间的差异

来自linux中国网wiki
跳到导航 跳到搜索
 
(未显示同一用户的23个中间版本)
第4行: 第4行:
 
<pre>
 
<pre>
 
本质关闭和启动的最直接就是他们了,启动脚本也是启用他们的
 
本质关闭和启动的最直接就是他们了,启动脚本也是启用他们的
 +
 +
 +
MySQL [(none)]> shutdown;
 +
Query OK, 0 rows affected (0.035 sec)
 +
 +
MySQL [(none)]>
 +
MySQL [(none)]> show databases;
 +
ERROR 2013 (HY000): Lost connection to server during query
 +
MySQL [(none)]> Bye
 +
[root@mylove]# netstat  -nlpt
 +
Active Internet connections (only server
  
  
第14行: 第25行:
 
mysqld_safe my.cnf
 
mysqld_safe my.cnf
 
</pre>
 
</pre>
 +
 +
=== mysql主从常见命令===
 +
<pre>
 +
 +
mysql > RESET MASTER
 +
作用包括:
 +
 +
    删除binlog索引文件中列出的所有binlog文件
 +
    清空binlog索引文件
 +
    创建一个新的binlog文件
 +
    清空系统变量gtid_purged和gtid_executed
 +
    在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 mysql.gtid_executed 数据表。
 +
 +
 +
 +
</pre>
 +
https://dev.mysql.com/doc/refman/5.7/en/reset-master.html
 +
 +
[https://blog.csdn.net/lanyang123456/article/details/85016447  mysql主从复制--重置操作reset master, reset slave]
 +
 +
[https://www.douban.com/note/446551760/ RESET MASTER 和RESET SLAVE 命令的使用方法 注意事项]
  
 
=== 日常基本操作===
 
=== 日常基本操作===
第20行: 第52行:
 
  CREATE DATABASE youdbname CHARACTER SET utf8mb4  COLLATE utf8mb4_general_ci; #创建库
 
  CREATE DATABASE youdbname CHARACTER SET utf8mb4  COLLATE utf8mb4_general_ci; #创建库
 
  #create database dbname default charset=utf8;  
 
  #create database dbname default charset=utf8;  
 +
 +
<pre>
 +
mysql  库名 表名 纯数字  注意
 +
 +
一般不建议用纯数字命名表名
 +
使用纯数字表名  要在表名前后加上 ``
 +
感叹号左边的那个键
 +
 +
sql举例 insert into `112` values(1,2,3,4,5);
 +
</pre>
 +
  
 
====SQL  select  DELETE  etc 语句====
 
====SQL  select  DELETE  etc 语句====
第26行: 第69行:
 
注意  del是不对的 例如  
 
注意  del是不对的 例如  
  
  del hello_wallet.t_wallet_biz_transfer_apply where id = '1356131535272050002' ;
+
  这个是不对的 del hello_wallet.t_wallet_biz_transfer_apply where id = '1356131535272050002' ;
  
 
参考
 
参考
 
http://www.w3school.com.cn/sql/sql_delete.asp
 
http://www.w3school.com.cn/sql/sql_delete.asp
 
  
  
第113行: 第155行:
 
| Select_priv | enum(‘N’,’Y’) | NO | | N | |
 
| Select_priv | enum(‘N’,’Y’) | NO | | N | |
 
| Insert_priv | enum(‘N’,’Y’) | NO | | N | |
 
| Insert_priv | enum(‘N’,’Y’) | NO | | N | |
| Update_priv | enum(‘N’,’Y’) | NO | | N | |
+
| Update_priv | enum(‘N’,’Y’) | NO | | N | | </pre>
 
 
#创建表
 
  
 +
=====#创建表=====
 +
<pre>
 
CREATE TABLE IF NOT EXISTS employees ( #
 
CREATE TABLE IF NOT EXISTS employees ( #
  
第143行: 第185行:
 
ENGINE=MyISAM DEFAULT CHARSET=utf8;  
 
ENGINE=MyISAM DEFAULT CHARSET=utf8;  
  
 +
CREATE TABLE `wallet`.`t_wallet_biz_benefit_mining_order`  (
 +
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
 +
  `order_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单编号',
 +
  `user_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户ID',
 +
  `activity_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '活动ID',
 +
  `user_coin_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户钱包主键ID',
 +
  `deposit_amount` decimal(28, 8) NULL DEFAULT NULL COMMENT '存入金额',
 +
  `deposit_coin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '存入币种ID',
 +
  `cumulative_income` decimal(28, 8) NULL DEFAULT NULL COMMENT '累计收益',
 +
  `income_coin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '累计收益币种ID',
 +
  `status` tinyint(1) NULL DEFAULT NULL COMMENT '状态【1:计息中 2:已转出】',
 +
  `current_weights` int(11) NULL DEFAULT NULL COMMENT '订单当前权重',
 +
  `weight_update_time` datetime(0) NULL DEFAULT NULL COMMENT '权重更新时间',
 +
  `weights_reset_time` datetime(0) NULL DEFAULT NULL COMMENT '权重重置时间',
 +
  `expiration` datetime(0) NULL DEFAULT NULL COMMENT '订单过期时间',
 +
  `mining_date` int(5) NULL DEFAULT NULL COMMENT '挖矿时长',
 +
  `settlement_time` datetime(0) NULL DEFAULT NULL COMMENT '结算时间',
 +
  `next_settlement_time` datetime(0) NULL DEFAULT NULL COMMENT '下次结算时间',
 +
  `fund_apply_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '资金申请id',
 +
  `remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
 +
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
 +
  `create_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
 +
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
 +
  `update_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
 +
  `del_flag` tinyint(1) NULL DEFAULT NULL COMMENT '删除标记【 0:未删除,1:已删除】',
 +
  PRIMARY KEY (`id`) USING BTREE
 +
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '做市挖矿订单表' ROW_FORMAT = Dynamic;
 +
</pre>
 +
 +
 +
===== other =====
 +
<pre>
 +
mysql> desc employees;
 
mysql> describe employees;
 
mysql> describe employees;
 
+—————+————-+——+—–+———+——-+
 
+—————+————-+——+—–+———+——-+
第206行: 第281行:
 
命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);
 
命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);
  
create table mm(
+
create table my(
  
 
id int(4) not null primary key auto_increment,
 
id int(4) not null primary key auto_increment,
第581行: 第656行:
 
[mysqld]
 
[mysqld]
 
character_set_server = utf8  </pre>
 
character_set_server = utf8  </pre>
 +
 +
==== 表结构查看====
 +
<pre>
 +
show create table `register_link`;
 +
</pre>
  
 
==== 表操作(创建和插入)====
 
==== 表操作(创建和插入)====
第597行: 第677行:
 
   PRIMARY KEY (`id`)
 
   PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 +
 +
 +
新添加字段  del_flag之后
 +
 +
ALTER TABLE `wallet`.`t_wallet_biz_withdraw_apply`  ADD COLUMN `fee_fund_apply_id` varchar(30) NULL COMMENT '手续费资金申请ID' AFTER `del_flag`;
  
 
</pre>
 
</pre>
  
 
清空数据
 
清空数据
  RUNCATE TABLE table
+
  TRUNCATE TABLE youtable
 
 
  
 
==== 表操作(update)====
 
==== 表操作(update)====
第635行: 第719行:
 
[https://blog.csdn.net/thomas0yang/article/details/8175361 MySQL数据库维护手册]
 
[https://blog.csdn.net/thomas0yang/article/details/8175361 MySQL数据库维护手册]
  
==常用运维脚本==
+
 
 +
==== 索引查看====
 +
<pre>
 +
SHOW INDEX FROM <表名> [ FROM <数据库名>]
 +
 +
 +
SHOW INDEX FROM tb_stu_info\G
 +
 
 +
</pre>
 +
http://c.biancheng.net/view/7364.html
 +
 
 +
==== 查看mysql数据库(表)的大小====
 +
https://blog.csdn.net/qq_40391091/article/details/126642427
 +
 
 +
https://blog.csdn.net/u014609263/article/details/87934638
 +
 
 +
https://www.yisu.com/zixun/689989.html
 +
 
 +
==== mysql锁库与解锁====
 +
 
 +
FLUSH TABLES WITH READ LOCK
 +
 
 +
这个命令是全局读锁定,执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操作顺利进行。
 +
 
 +
解锁的语句也是
 +
unlock tables
 +
 
 +
 
 +
[https://blog.csdn.net/aichogn/article/details/117789488  mysql中flush tables和flush tables with read lock详解]
 +
 
 +
==mysql常用运维脚本==
 
<pre>
 
<pre>
 +
 +
再优化
 +
 +
注意  这个 cat 密码有 ! 导致有时不行 
 +
 +
所以最好要写成
 +
 +
#!/bin/bash
 +
#批量创建数据库
 +
user=root
 +
password=handdba
 +
socket=/var/lib/mysql/mysql.sock
 +
mycmd="mysql -u$user -p$password -S $socket"
 +
for((i=13;i<73;i++))
 +
do
 +
$mycmd -e "create database db$i"
 +
done
 +
 +
 +
#Nov 05  2021 更加原版的输出
 +
cat  dbin.sh
 +
mysql -udevops -h172.126.0.6 -p`cat /home/eva` -e "`cat $1`;"
 +
 +
#Usage  bash dbin.sh  你的sql脚本
 +
  
 
mysql -h databases.com -uadmin -p`cat /evan/11`  < $1
 
mysql -h databases.com -uadmin -p`cat /evan/11`  < $1
第668行: 第807行:
  
 
</pre>
 
</pre>
 +
 +
[https://blog.csdn.net/jiaqingShareing/article/details/82148481  利用shell脚本批量创建数据库以及数据表]
  
 
==mysqldump==
 
==mysqldump==
第685行: 第826行:
 
     4、导出數據库為dbname某张表(test)结构及表數據(不加-d)
 
     4、导出數據库為dbname某张表(test)结构及表數據(不加-d)
 
     mysqldump -uroot -pdbpasswd dbname test>db.sql;  
 
     mysqldump -uroot -pdbpasswd dbname test>db.sql;  
 +
 +
 +
mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES
 +
 +
普通用户没有LOCK权限 所以要加  --single-transaction
 +
 +
I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions, either:
 +
 +
    your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
 +
    run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u user -p ...
 +
  
 
</pre>
 
</pre>
  
 +
=== mysqldump 排队表备份--ignore-table===
 +
<pre>
 +
#可以多个表 ignore
 +
mysqldump --databases mytest --ignore-table=mytest.ti_o_sms > mytest.sql
 +
</pre>
 +
[https://blog.csdn.net/karen121l/article/details/7289486  如何用mysqldump --ignore-table多个表]
 +
 +
[https://www.cnblogs.com/chuanzhang053/p/10216751.html mysqldump导出数据时,某些表不导出,排除某些表,不导出某些表]
 +
 +
[https://blog.csdn.net/weixin_34216036/article/details/93424123?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4.add_param_isCf&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4.add_param_isCf  mysqldump导出数据时,某些表不导出,排除某些表,不导出某些表]
 +
 +
[https://blog.csdn.net/weixin_33919950/article/details/89795809  mysqldump排除某几张表不导出,其余都导出。]
  
 
==trouble==
 
==trouble==
第726行: 第890行:
 
[https://www.cnblogs.com/chenmh/p/5300370.html MySQL mysqldump数据导出详解]
 
[https://www.cnblogs.com/chenmh/p/5300370.html MySQL mysqldump数据导出详解]
  
 
+
[https://blog.51cto.com/u_15127526/4379949  MySQL 8.MySQL数据库导入、导出和授权]
 
[[category:mysql]]
 
[[category:mysql]]

2022年12月13日 (二) 06:21的最新版本

mysql study之常用操作

关闭 启动

本质关闭和启动的最直接就是他们了,启动脚本也是启用他们的


MySQL [(none)]> shutdown;
Query OK, 0 rows affected (0.035 sec)

MySQL [(none)]> 
MySQL [(none)]> show databases;
ERROR 2013 (HY000): Lost connection to server during query
MySQL [(none)]> Bye
[root@mylove]# netstat  -nlpt 
Active Internet connections (only server


关闭mysql 数据库
mysqladmin --defaults-file=/etc/my.cnf -uroot -p'xx'  shutdown   2>&1
killall -15 mysqld


启动
mysqld_safe my.cnf

mysql主从常见命令


mysql > RESET MASTER
作用包括:

    删除binlog索引文件中列出的所有binlog文件
    清空binlog索引文件
    创建一个新的binlog文件
    清空系统变量gtid_purged和gtid_executed
    在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 mysql.gtid_executed 数据表。



https://dev.mysql.com/doc/refman/5.7/en/reset-master.html

mysql主从复制--重置操作reset master, reset slave

RESET MASTER 和RESET SLAVE 命令的使用方法 注意事项

日常基本操作

Sql 基础

CREATE DATABASE youdbname CHARACTER SET utf8mb4  COLLATE utf8mb4_general_ci; #创建库
#create database dbname default charset=utf8; 
mysql  库名 表名 纯数字  注意 

一般不建议用纯数字命名表名
使用纯数字表名  要在表名前后加上 ``
感叹号左边的那个键

sql举例 insert into `112` values(1,2,3,4,5); 


SQL select DELETE etc 语句

DELETE FROM 表名称 WHERE 列名称 = 值

注意 del是不对的 例如

这个是不对的 del hello_wallet.t_wallet_biz_transfer_apply where id = '1356131535272050002' ;

参考 http://www.w3school.com.cn/sql/sql_delete.asp


首次记录时间:2016年8月14日
有个哥说介绍一些mysql常用操作,于是有了此文

补充
RDBS, Relation data base system(关系型数据库系统),数据以二维表形式存储,每行(row)代表同一行数据,为记录
每列表示记录中的某个属性,为字段也

不能用 kill -9 因为会 自动重启 mysql service
root@debian:/# mysqladmin -pevan shutdown
root@debian:/h# ps -ef | grep mysqld
root 9950 5490 0 19:11 pts/0 00:00:00 grep mysqld

root@debian:# mysqladmin -pevan status
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!

rm -f /var/run/mysqld/mysqld.sock

root@debian:# mysqladmin -pevan status
Uptime: 19 Threads: 1 Questions: 2 Slow

p628 /etc/init.d/mysqld

mysql> show variables like ‘%buffer%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+——————————+———–+

mysqladmin

#change the root password is 123456
mysqladmin -uroot -p password 123456

evan@debian:~/bakkali/software$ mysqladmin -uroot -p processlist
Enter password:
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 53 | root | localhost | | Sleep | 118 | | |
| 54 | root | localhost | | Query | 1 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

26.5 p639
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |

mysql> describe user;
+————————+———————————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————————+———————————–+——+—–+———+——-+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum(‘N’,’Y’) | NO | | N | |
| Insert_priv | enum(‘N’,’Y’) | NO | | N | |
| Update_priv | enum(‘N’,’Y’) | NO | | N | | 
#创建表
CREATE TABLE IF NOT EXISTS employees ( #

EMPLOYEE_ID varchar(10) not null, #// EMPLOYEE_ID 字段的类型为varchar(10),字段值不允许为空

FIRST_NAME varchar(10) not null,

LAST_NAME varchar(10) not null,

EMALL varchar(50) not null,

HIRE_DATE date, #date 类型

JOB_ID int not null,

SALARY int default 0, #//SALARY 字段默认值为0

MANAGER_ID int ,

DEPARTMENT_ID int not null,

primary key (EMPLOYEE_ID) ) #// 主键为EMPLOYEE_ID

#// 使用数据库引擎为MyISAM

ENGINE=MyISAM DEFAULT CHARSET=utf8; 

CREATE TABLE `wallet`.`t_wallet_biz_benefit_mining_order`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `order_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单编号',
  `user_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户ID',
  `activity_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '活动ID',
  `user_coin_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户钱包主键ID',
  `deposit_amount` decimal(28, 8) NULL DEFAULT NULL COMMENT '存入金额',
  `deposit_coin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '存入币种ID',
  `cumulative_income` decimal(28, 8) NULL DEFAULT NULL COMMENT '累计收益',
  `income_coin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '累计收益币种ID',
  `status` tinyint(1) NULL DEFAULT NULL COMMENT '状态【1:计息中 2:已转出】',
  `current_weights` int(11) NULL DEFAULT NULL COMMENT '订单当前权重',
  `weight_update_time` datetime(0) NULL DEFAULT NULL COMMENT '权重更新时间',
  `weights_reset_time` datetime(0) NULL DEFAULT NULL COMMENT '权重重置时间',
  `expiration` datetime(0) NULL DEFAULT NULL COMMENT '订单过期时间',
  `mining_date` int(5) NULL DEFAULT NULL COMMENT '挖矿时长',
  `settlement_time` datetime(0) NULL DEFAULT NULL COMMENT '结算时间',
  `next_settlement_time` datetime(0) NULL DEFAULT NULL COMMENT '下次结算时间',
  `fund_apply_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '资金申请id',
  `remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `update_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `del_flag` tinyint(1) NULL DEFAULT NULL COMMENT '删除标记【 0:未删除,1:已删除】',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '做市挖矿订单表' ROW_FORMAT = Dynamic;


other
mysql> desc employees;
mysql> describe employees;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | |
| FIRST_NAME | varchar(10) | NO | | NULL | |
| LAST_NAME | varchar(10) | NO | | NULL | |
| EMALL | varchar(50) | NO | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| SALARY | int(11) | YES | | 0 | |
| MANAGER_ID | int(11) | YES | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+—————+————-+——+—–+———+——-+
9 rows in set (0.00 sec)

#######################
修改新密码

set password for root@localhost = password('');


use mysql;
update user set password=PASSWORD('evan') where user='root'; # on mysql 5.7

##最好要加这一行

flush privileges;

shell>mysqladmin -uroot -p password  新密码
#然后在提示符后输入老密码

增加新用户

格式:grant select on 数据库.* to 用户名@登录主机 identified by ‘密码’
举例:
例 1:增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有
查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,然后键入以下命令:
mysql>
 grant select,insert,update,delete on *.* to test1@localhost identified by 'abc';

Query OK, 0 rows affected
#或者
mysql>
grant all privileges on *.* to test1@localhost identified by 'abc';

flush privileges;

删除用户
delete from user where user=’用户名’ and host=’localhost’;
delete from user where user='test2' and host='localhost';

flush privileges;

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

表操作

备注:操作之前使用“use <数据库名>”应连接某个数据库。
建表
命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);

create table my(

id int(4) not null primary key auto_increment,

name char(20) not null,

sex int(4) not null default '0',

degree double(16,2))

#ENGINE=InnoDB;

ENGINE=MyISAM DEFAULT CHARSET=utf8;

查看表结构
命令: desc 表名,   show create table 表名; 或者show columns from 表名
mysql> describe my;

mysql> desc my;

show columns from my;

删除表
drop table

插入数据
命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]
mysql> insert into my values(1,'tom',0,96);

mysql> insert into my(name,sex,degree) values('evan',1,90);

查询表中的数据
mysql> select * from my;
+—-+——+—–+——–+
| id | name | sex | degree |
+—-+——+—–+——–+
| 1 | tom | 0 | 96.00 |
| 2 | evan | 1 | 90.00 |
+—-+——+—–+——–+

#select 2020
#前 2 行数据
select * from my limit 0,2;

select * from my order by id limit 0,2;

UPDATE hello_wallet.t_wallet_user SET trade_lock_status = 0 WHERE telephone = '185206773';
select  *  from  hello_wallet.t_wallet_user  where   telephone = '185206773'\G

删除表中数据
命令:delete from 表名 where 表达式
delete from my where id=2;

修改表中数据
命令:update 表名 set 字段=新值,… where 条件
update my set name=’eva’ where id=1;
mysql> select * from my order by id limit 0,2;
+—-+——+—–+——–+
| id | name | sex | degree |
+—-+——+—–+——–+
| 1 | eva | 0 | 96.00 |
+—-+——+—–+——–+

在表中增加字段
命令:alter table 表名 add 字段 类型 其他;
例如:在表 MyClass 中添加了一个字段 pass,类型为 int(4),默认值为 0
mysql> alter table my add pass int(4) default '0';

更改表名
命令:rename table 原表名 to 新表名;
rename table my to mm;

更新字段内容
命令:update 表名 set 字段名 = 新内容
update 表名 set 字段名 = replace(字段名, ‘旧内容’, ‘新内容’);
update my set name='nick' where name='eva';

update my set name=replace(name,'jkjk','evan');

数据库导入导出

将数据库 com 中的 my 导出到 文件中
mysqldump -uroot -pevan com my > my.sql

数据库 mydb 的结构导出到 e:\MySQL\mydb_stru.sql 文件中
mysqldump -uroot -pevan com --add-drop-table >com.stru.sql 

导出结构不导出数据 Fri Aug 21 10:48:02 CST 2020
mysqldump --opt -d 数据库名 -u root -p > xxx.sql

只导出数据不导出数据结构
mysqldump -u [数据库用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径]
mysqldump -uroot -pevan -t com >com0.sql

#here
导出数据库中的Events
格式:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]

导出数据库中的存储过程和函数
格式:mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]

从外部文件导入数据库中
1)使用“source”命令

2)使用 “<” 小于符号 *************************************** ##20160819 26.5.4 改变表结果 ALTER TABLE 数据表名 更改1 [,更改2 …] #添加TELPHONE 字段 ALTER TABLE employees add TELPHONE char(20); #更改字段 change alter table employees change TELPHONE TEL char(20); ##更改字段 类型 modify alter table employees modify TEL varchar(20); ##删除 字段 drop alter table employees drop tel; #表改名 rename alter table employees rename to emp; 26.5.5 复制数据表 CREATE TABLE 新表名 LIKE 源表名 create table employees2 like employees; #只复制了表结构, 要复制结构和数据的 为 create table emp3 select * from emp; 26.5.6 删除数据表 DROP TABLE tablename; 26.6 数据管理 26.6.1查询数据 SELECT * | 字段列表 FROM 数据表 WHERE 条件; 1.所以数据 select * from emp; 2.某些字段的数据 mysql> select EMPLOYEE_ID, last_name,salary from emp;
+————-+————+——–+
| EMPLOYEE_ID | last_name | salary |
+————-+————+——–+
| kyct | [email protected] | 9 |
+————-+————+——–+

3,满足某些条件的数据
mysql> select EMPLOYEE_ID, last_name,salary from emp where salary < 10000; +————-+————+——–+ | EMPLOYEE_ID | last_name | salary | +————-+————+——–+ | kyct | [email protected] | 9 | +————-+————+——–+ 4.查询数据的总数 mysql> select count(*) from emp;
+———-+
| count(*) |
+———-+
| 1 |
+———-+

26.6.2 插入
INSERT INTO 表名(字段1, 字段2, …) VALUES (值1,值2,…);
INSERT INTO 表名(字段1, 字段2, …) SELECT 字段1, 字段2, …) FROM 源表;

mysql> desc emp;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | |
| FIRST_NAME | varchar(10) | NO | | NULL | |
| LAST_NAME | varchar(10) | NO | | NULL | |
| EMALL | varchar(50) | NO | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| SALARY | int(11) | YES | | 0 | |
| MANAGER_ID | int(11) | YES | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+—————+————-+——+—–+———+——-+

mysql> insert into emp values (‘kyct’,’kyct’,’[email protected]’,7,’2016-08-20′,3,9,8,9);

insert into emp (employee_id,LAST_NAME,job_id) values (10888,’jim’,5);

2. 插入其它有的数据
mysql> create table emp2 like emp;
Query OK, 0 rows affected (0.34 sec)

mysql> select * from emp2;

mysql> insert into emp2 select * from emp where employee_id=10888;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysql> select * from emp2;
+————-+————+———–+——-+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+——-+———–+——–+——–+————+—————+
| 10888 | | jim | | NULL | 5 | 0 | NULL | 0 |
+————-+————+———–+——-+———–+——–+——–+————+—————+

26.6.3 更新数据
UPDATE 表名 SET 字段1 = 值1 [,字段2 = 值2 …] WHERE 查询条件;

mysql> select * from emp2;
+————-+————+———–+——-+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+——-+———–+——–+——–+————+—————+
| 10888 | | jim | | NULL | 5 | 0 | NULL | 0 |
+————-+————+———–+——-+———–+——–+——–+————+—————+

mysql> update emp2 set emall=’[email protected]’,salary=2000 where last_name=’jim’;

mysql> select * from emp2;
+————-+————+———–+—————+———–+——–+——–+————+—————+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID |
+————-+————+———–+—————+———–+——–+——–+————+—————+
| 10888 | | jim | [email protected] | NULL | 5 | 2000 | NULL | 0 |
+————-+————+———–+—————+———–+——–+——–+————+—————+

26.6.4 删除数据
DELETE FROM 表名 WHERE 查询条件;
delete from emp where last_name=’jim’;

26.7 索引管理

26.7.1 创建索引
mysql> create table depar (
-> depa_id char(10) not null,
-> depa_name varchar(50) not null,
-> mana_id char(10),
-> index ind_depa01 (depa_id) //创建索引ind_depa01
-> );

#上面为创建表depar的同时,在 depa_id 字段创建一个名为 ind_depa01 的索引
如果表已存在 CREATE INDEX

CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段1 [,字段2 …]);
create unique index ind_depa02 on dep(EMPLOYEE_ID);

#表emp 的 EMPLOYEE_ID 字段上,创建一个名为 ind_depa02 的唯一索引
create unique index ind_depa02 on emp(EMPLOYEE_ID);

26.7.2 删除索引
DROP INDEX 索引名 ON 表名

26.8 用户和权限管理

mysql> select host,user,password,select_priv from user;
+———–+——————+——————————————-+————-+
| host | user | password | select_priv |
+———–+——————+——————————————-+————-+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y |
| debian | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| 127.0.0.1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| ::1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y |
| localhost | debian-sys-maint | *E7BF66CEE2E00177E47E00EDE25DAFADF8AADF17 | Y |
+———–+——————+——————————————-+————-+

26.8.2 用户管理

1.添加
#add user 记得要刷新权限表
insert into user (host,user,password) values(‘%’,’test’,password(‘123456’));
flush privileges;

2.用户查看
#查看所有用户 在这个新授权时非常有用 也就是看用户是不是已存在 
mysql> SELECT user,host,password FROM mysql.user;

#查看MySQL的当前用户
mysql> SELECT USER();



3.更新口令
mysql> update user set password=password(‘1234′) where user=’test’;
mysql> flush privileges;

delete from user where user=’test’;

26.8.3 用户授权

GRANT 权限[(字段)] ON 数据库名.表名 TO 用户名@域名或者ip 地址 [ IDENTIFIED BY ‘口令’ ] [ WITH GRANT OPTION
];

1.授权数据库中所有的对象的权限
hr库中所有的表的查询权限授给 evan
grant select on hr.* to evan;

2.授权数据库中个别对象的权限

#库hr中数据表emp 的查询和更新权限授给 test用户
mysql> grant select on hr.emp to test;

mysql> grant update on hr.emp to test;

3.控制访问的主机
grant all on dbname.tablename to user@’ip’;

4.
授test 用户 hr.emp 表的删除权限,并允许test 把该项权限授其它的用户
grant delete on hr.emp to test with grant option;

5 创建用户

grant select on evan.emp to sam identified by ‘123’;

mysql> select host,user,password from user;
+———–+——————+——————————————-+
| host | user | password |
+———–+——————+——————————————-+
| % | test | *A4B6157319038724E3560894F7F932C8886EBFCF |
| % | evan | |
| % | sam | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+———–+——————+——————————————-+
CREATE USER evan@localhost IDENTIFIED BY ‘1234’;

26.8.4 回收权限


REVOKE 权限 [( 字段)] ON dbname.tablename from user@ip

revoke select on hr.* from test;

mysql创建用户以及授权
http://blog.linuxchina.net/?p=1344

mysql授权查看
http://blog.linuxchina.net/?p=950



26.9 mysql 备份恢复 (5种 mysqldump,mysqlhocopy,sql,binlog,直接备份数据文件 )

26.9.1 mysqldump
1. 导出指定的表,不指定将导出库中的所有表
mysqldump [options] dbname [table]

2.导出一个多个指定数据库中的所有表
mysqldump [options] –database dbname1 [dbname2,…]

3.导出系统中所有数据库
mysqldump [options] –all-databases

常用的选项
–databases,-B 指定要导出的库
-default-character-set=charset_name 默认字符集
–ignore-table=dbname.tablename #忽略的表,不导出
–lock-all-tables, -X 导出前,锁定所以的数据表,保持系统数据的一致
–lock-tables, -l 锁定当前导出的数据表 #这个用处应该比较多
–log-error=filnename 保存错误和警告日志到文件
–no-create-db, -n 只导出数据,不导出CREATE TABLE 语句
–no-data, -d 只导出表结构,不导出数据
–routines, -R 导出存储过程和函数

1.备份表
mysqldump -uroot -p dbname table >table.sql

2.整个库
mysqldump -uroot -p –database dbname > db.sql

3.只导出表结构
mysqldump -uroot -p –no-data dbname table >table.sql

4.恢复数据
mysql -uroot -p dbanne < file.sql
或者 source

****************************************************************

26.9.2 mysqlhotcopy 感觉不好用
其实是使用lock tables cp scp 来完成备份的 只能备份myisam存储引擎
1.备份

2.恢复 直接是 cp -Rf src targer

26.9.3 使用SQL语句备份和恢复
BACKUP TABLE ,SELECT NITO OUTFILE

26.9.4 二进制日志(binlog)
恢复
mysqlbinlog -d games_s25 --start-datetime="2016-07-25 02:05:00" --stop-datetime="2016-07-25 04:40:00" "mysql-bin.000201" | mysql -uroot -ppassword

26.9.5 直接备份数据文件 ,flush tablename with read lock;#会比较 好
MySQL基本操作


#其它
#查看当前写入的日志
mysql> show master status ;

#查看某一库的字符集

mysql> show create database test;


#查看MySQL支持的字符集
1
mysql> show character set ;
#查看默认字符集

mysql> show variables like '%char%';

查看某一表的字符集
mysql> show create table test.t1 \G

#修改字符集

mysql>alter database mydb character set utf-8;
#可以直接在配置文件中做添加,然后重启服务

[client]
character_set_server = utf8
 
[mysqld]
character_set_server = utf8  

表结构查看

show create table `register_link`;

表操作(创建和插入)

create table t1(name varchar(20),fa char(1));
 insert into t1 values("e","m");

create table t2(name varchar(20),fa char(1));
 insert into t2 values("t","m");


DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
  `id` int(11) NOT NULL,
  `t_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


新添加字段  del_flag之后

ALTER TABLE `wallet`.`t_wallet_biz_withdraw_apply`  ADD COLUMN `fee_fund_apply_id` varchar(30) NULL COMMENT '手续费资金申请ID' AFTER `del_flag`;

清空数据

TRUNCATE TABLE youtable

表操作(update)


UPDATE t_market_timer_task set status=1  where task_id=1;

ySQL [market]> select  * from  t_market_timer_task where task_id=1;
+---------+--------------------------+--------------------------+-----------------+--------+-------------------------------------------------------------+---------+---------------------+-----------+---------------------+-----------+----------+
| task_id | task_name                | task_describe            | cron_expression | status | job_class                                                   | remarks | create_time         | create_by | update_time         | update_by | del_flag |
+---------+--------------------------+--------------------------+-----------------+--------+-------------------------------------------------------------+---------+---------------------+-----------+---------------------+-----------+----------+
| 1       | Global-MarketOverviewJob |数据拉取         | 0 0/5 * * * ?   |      1 | com.market.web.schedule.global.MarketOverviewJob | NULL    | 2011-07-30 14:39:21 | NULL      | 2011-07-30 14:39:23 | NULL      |        0 |
+---------+--------------------------+--------------------------+-----------------+--------+-------------------------------------------------------------+---------+---------------------+-----------+---------------------+-----------+----------+
1 row in set (0.01 sec)

数据库及表编码查看

查看数据库编码格式
show variables like 'character_set_database';


查看数据表的编码格式
show create table <表名>;

查看mysql数据库及表编码格式


MySQL数据库维护手册


索引查看

 SHOW INDEX FROM <表名> [ FROM <数据库名>]
 
 
 SHOW INDEX FROM tb_stu_info\G

http://c.biancheng.net/view/7364.html

查看mysql数据库(表)的大小

https://blog.csdn.net/qq_40391091/article/details/126642427

https://blog.csdn.net/u014609263/article/details/87934638

https://www.yisu.com/zixun/689989.html

mysql锁库与解锁

FLUSH TABLES WITH READ LOCK

这个命令是全局读锁定,执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操作顺利进行。

解锁的语句也是

unlock tables


mysql中flush tables和flush tables with read lock详解

mysql常用运维脚本


再优化 

注意  这个 cat 密码有 ! 导致有时不行  

所以最好要写成 

#!/bin/bash
#批量创建数据库
user=root
password=handdba
socket=/var/lib/mysql/mysql.sock
mycmd="mysql -u$user -p$password -S $socket"
for((i=13;i<73;i++))
do
$mycmd -e "create database db$i"
done


#Nov 05  2021 更加原版的输出 
cat  dbin.sh
mysql -udevops -h172.126.0.6 -p`cat /home/eva` -e "`cat $1`;" 

#Usage  bash dbin.sh  你的sql脚本


mysql -h databases.com -uadmin -p`cat /evan/11`   < $1

#Usage  bash dbin.sh  你的sql脚本



for loop in  `cat ../li`
 do
 
 
   mysql  -uroot -p'222233'  -h 127.0.0.1  sns  < $loop.sql
# echo  $loop 
done


cat  evan/dump 
for loop in  `cat li`
 do
 
  mysqldump    -uroot   -ppassword   -h youip  sns $loop >$loop.sql
 #echo  $loop 
done


for  loop in  cat list
do 

mysql -uroot -p45EOV -h127.0.0.1 -e  " CREATE DATABASE ${loop} CHARACTER SET utf8mb4  COLLATE utf8mb4_general_ci;"

利用shell脚本批量创建数据库以及数据表

mysqldump

 命令行下具体用法如下:  mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;
 远程的 记得加-h 

    1、导出數據库為dbname的表结构(其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql)
    mysqldump -uroot -pdbpasswd -d dbname >db.sql;

    2、导出數據库為dbname某张表(test)结构
    mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

    3、导出數據库為dbname所有表结构及表數據(不加-d)
    mysqldump -uroot -pdbpasswd  dbname >db.sql;

    4、导出數據库為dbname某张表(test)结构及表數據(不加-d)
    mysqldump -uroot -pdbpasswd dbname test>db.sql; 


mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

普通用户没有LOCK权限 所以要加  --single-transaction

I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions, either:

    your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
    run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u user -p ...


mysqldump 排队表备份--ignore-table

#可以多个表 ignore
mysqldump --databases mytest --ignore-table=mytest.ti_o_sms > mytest.sql

如何用mysqldump --ignore-table多个表

mysqldump导出数据时,某些表不导出,排除某些表,不导出某些表

mysqldump导出数据时,某些表不导出,排除某些表,不导出某些表

mysqldump排除某几张表不导出,其余都导出。

trouble

MySQL数据库名中划线问题

数据库的名字中有中划线,导致名字识别失败,用了 use dbname; 依然提示No database selected 解决方案

把数据库名用反引号 `` (和命令执行同一个的)引起来就可以了

日常报错


没认真写   fre_fund_apply_id 写成 free_fund_apply_id
ALTER TABLE `hello_wallet`.`t_wallet_biz_withdraw_apply`  ADD COLUMN `fee_fund_apply_id` varchar(30) NULL COMMENT '手续费资金申请ID' AFTER `del_flag`;

ALTER TABLE `hello_wallet`.`t_wallet_biz_withdraw_apply` ADD COLUMN `fee_coin_id` varchar(30) NULL COMMENT '手续费币种ID' AFTER `fee_fund_apply_id`;




MySQL [(none)]> ALTER TABLE `hello_wallet`.`t_wallet_biz_withdraw_apply`  ADD COLUMN `fee_fund_apply_id` varchar(30) NULL COMMENT '手续费资金申请ID' AFTER `del_flag`;
ERROR 1060 (42S21): Duplicate column name 'fee_fund_apply_id'
MySQL [(none)]> ALTER TABLE `hello_wallet`.`t_wallet_biz_withdraw_apply` ADD COLUMN `fee_coin_id` varchar(30) NULL COMMENT '手续费币种ID' AFTER `free_fund_apply_id`;
ERROR 1054 (42S22): Unknown column 'free_fund_apply_id' in 't_wallet_biz_withdraw_apply'

参考

http://dufei.blog.51cto.com/382644/88646


MySQL常用命令总结


MySQL(十四)管理维护及性能优化

MySQL mysqldump数据导出详解

MySQL 8.MySQL数据库导入、导出和授权