[svc][db]mysql日常维护语句

mysql启动

<code>docker run  -p 3306:3306 -v /data/mysql:/var/lib/mysql -v /etc/localtime:/etc/localtime --name mysql5 --restart=always -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6.23 --character-set-server=utf8 --collation-server=utf8_general_ci</code>

建库-建表-插数据

<code>create database bbs;
create table student(id int,name varchar(40));
show create create table student;
desc student;
insert into student values(1,'maotai');</code>

查看命令帮助

<code>MySQL [(none)]> help create</code>

查表结构

<code>- 查看表结构(MySQL [bbs]> help show grants)可以看到语法
MySQL [bbs]> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                 |
+---------+------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(44) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 查看表结构
MySQL [bbs]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(44) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)</code>

更新用户密码

<code>## 更改mysql密码: mysqladmin
- 为root设置密码
mysqladmin -uroot password 12345678
- 为root修改密码(已有密码)
$ mysqladmin -uroot -p123456 password 12345678
Warning: Using a password on the command line interface can be insecure.
- 为root修改密码(多实例)
$ mysqladmin -uroot -p123456 password 12345678 -S /data/3306/mysql.sock
## 更改密码: update语句
- 查看表字段
MySQL [(none)]> desc mysql.user
- 查看用户
MySQL [(none)]> select user,host,password from mysql.user;
- 修改密码(明文: 这样是错误的)
MySQL [(none)]> update mysql.user set password='123456' where user='root' and host='%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MySQL [(none)]> select user,host,password from mysql.user;
+------+------+----------+
| user | host | password |
+------+------+----------+
| root | %    | 123456   |
+------+------+----------+
1 row in set (0.00 sec)
- 正确的姿势
MySQL [(none)]> update mysql.user set password=password('123456') where user='root' and host='%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)
小结:
1,带where条件
2,指定password()函数
## set
- 适合改密码场景
MySQL [(none)]> set password=password('maotai');
Query OK, 0 rows affected (0.00 sec)</code>

[svc][db]mysql日常维护语句

标签：ges   local   mat   null   har   查看命令   结构   多实例   show