mysql 启动
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
建库 - 建表 - 插数据
- create database bbs;
- create table student(id int,name varchar(40));
- show create create table student;
- desc student;
- insert into student values(1,'maotai');
查看命令帮助
MySQL [(none)]> help create
查表结构
- 查看表结构 (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)
更新用户密码
## 更改 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)
[svc][db]mysql 日常维护语句
来源: http://www.bubuko.com/infodetail-2509508.html