1.首次进入数据库
[root@node251 tmp]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
2.修改初始化密码
mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.退出数据库
mysql> exit
Bye
4.使用修改后的密码登录数据库
[root@node251 tmp]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
5.查看库名
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)
6.查看数据库默认编码
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
7.删除数据库
drop database mysql; 或者 drop database if exists mysql;
8.新建库
（create database if not exists test; 该句加了判断，如果没有test这个库则创建）
mysql> create database test;
Query OK, 1 row affected (0.04 sec)
9.查看是否创建库成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
10.进入库
mysql> use test;
11.查看/创建表
mysql> show tables;
mysql> create table person (number INT(11),name VARCHAR(255),birthday DATE;
Query OK, 0 rows affected (0.27 sec)
(添加字段、字符类型、字符长度)
12.查看所创建的表结构
mysql> show create table person;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
13.查看表所有的列
mysql> show full columns from person;
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| number | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
| name | varchar(255) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| birthday | date | NULL | YES | | NULL | | select,insert,update,references | |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)
14.查看库中的所有表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| chen |
| person |
+----------------+
2 rows in set (0.00 sec)
15.创建临时表
mysql> CREATE TEMPORARY TABLE temp_person (
-> number INT(11),
-> name VARCHAR(255),
-> birthday DATE
-> );
Query OK, 0 rows affected (0.00 sec)
注：在创建表格时，可以使用TEMPORARY关键词。只有在当前连接情况下，TEMPORARY表才是可见的。当连接关闭时，TEMPORARY表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被取消时为止。）您必须拥有CREATE TEMPORARY TABLES权限，才能创建临时表。
如果表已存在，则使用关键词IF NOT EXISTS可以防止发生错误。如下例：
mysql> CREATE TABLE IF NOT EXISTS person2 (
-> number INT(11),
-> name VARCHAR(255),
-> birthday DATE
-> );
Query OK, 0 rows affected (0.32 sec)
注：原有表的结构与CREATE TABLE语句中表示的表的结构是否相同，这一点没有验证。注释：如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS，则不论表是否已存在，由SELECT部分选择的记录都会被插入
16.删除表
drop table 表名 ; 或者 drop table if exists 表名;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| chen |
| person |
| person2 |
+----------------+
3 rows in set (0.00 sec)
mysql> drop table person2;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| chen |
| person |
+----------------+
2 rows in set (0.00 sec)
17.显示表的详细描述
mysql> describe chen; // 或 desc chen;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
18.向原有表中添加字段
mysql> desc chenyu;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table chenyu add transactor varchar(10) not NULL;
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc chenyu;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| transactor | varchar(10) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
19.修改表中字段类型
mysql> desc chenyu;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| transactor | varchar(10) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table chenyu modify column transactor char(30);
Query OK, 1 row affected (0.77 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc chenyu;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| transactor | char(30) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
20.修改表中字段类型是否允许为非空
mysql> alter table chenyu modify column transactor char(30) not Null;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc chenyu;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| transactor | char(30) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
21.修改表的字段名称及指定为空或非空
mysql> desc chenyu;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| transactor | char(30) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> alter table chenyu change transactor year char(50) Null;
Query OK, 1 row affected (0.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc chenyu;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| year | char(50) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
22.删除表中某一字段
mysql> alter table chenyu drop year;
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc chenyu;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
23.显示当前mysql版本和创建日期
mysql> select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.7.19 | 2017-08-10 |
+-----------+--------------+
1 row in set (0.02 sec)
24.重命名表
mysql> alter table chen rename chenyu;
Query OK, 0 rows affected (0.13 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| chenyu |
| person |
+----------------+
2 rows in set (0.00 sec)
25.显示表中记录
mysql> select * from chenyu;
Empty set (0.00 sec)
26.向表中添加数据
mysql> insert into chenyu values("1","chen","1994-06-15");
Query OK, 1 row affected (0.04 sec)
mysql> select * from chenyu;
+--------+------+------------+
| number | name | birthday |
+--------+------+------------+
| 1 | chen | 1994-06-15 |
+--------+------+------------+
1 row in set (0.00 sec)
27.清空表中记录
mysql> delete from chenyu;
Query OK, 0 rows affected (0.00 sec)
28.查看当前所处的库
mysql> select database(); ##（若输出为null,则说明）
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
29.查看数据库状态
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 3
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.19 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 29 min 38 sec
Threads: 1 Questions: 35 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 100 Queries per second avg: 0.006
--------------
30.查看所支持的字符集
mysql> show character set;
31.查看系统变量
mysql> show variables;
32. 显示执行内容系统变量，显示与字符集有关的系统变量
mysql> show variables like '%char%';
33.mysql账号管理
33.1查询账号
mysql > select * from user; 或 mysql > select host,user,password from user;
33.2创建账号
mysql >mysql> create user admin identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("123456"));
注：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。*例如：'test'@'192.168.1.100',只允许指定IP的主机进行访问
然后登录一下：
mysql>exit;
mysql -u test –p123456
mysql>
33.3修改密码
mydql> set password for 账号 = password('密码');
33.4 删除账号
删除用户
mysql -u root -p
mysql>delete from user where user='admin' and host='localhost';
mysql>flush privileges;
mysql>drop database testDB; //删除用户的数据库
删除账户及权限：>drop user 用户名@'%';
>drop user 用户名@ localhost;
33.5授权
1.新建testDB库
mysql> create database testDB;
Query OK, 1 row affected (0.02 sec)
2.查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| testDB |
+--------------------+
6 rows in set (0.00 sec)
3.授权test 用户拥有testDB数据库的权限（某个数据库的所有权限）；
mysql> grant all privileges on testDB.* to admin@localhost identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";
4.刷新系统权限表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5.指定部分权限给一用户
mysql>grant select,update on testDB.* to test@localhost identified by '123456';
mysql>flush privileges; //刷新系统权限表
6.授权一用户拥有所有数据库的某些权限：
mysql>grant select,delete,update,create,drop on *.* to test@"%" identified by "123456";
// test用户对所有数据库都有select,delete,update,create,drop 权限。
// @"%" 表示对所有非本地主机授权，不包括localhost
//对localhost授权: grant all privileges on testDB.* to test@localhost identified by '123456';
34.root账号密码丢失
34.1 使用跳过权限验证启动mysql服务
cmd > mysqld --console --skip-grant-tables
34.2修改密码，只能使用update语句
mysql> update user set password = password('123456') where host='localhost' and user='root';
mysql>flush privileges;
35.数据库备份
cd /usr/local/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
mysqldump -u root -p test>test.sql，输入密码即可。
36.数据库还原
1.进入MySQL的控制台" mysql> "
2.建立你要还原的数据库，例： create database beifen;
3.切换到刚建立的数据库 use beifen;
4.导入数据，输入source test.sql; 开始导入，再次出现"mysql>"并且没有提示错误即还原成功。
5.show tables; 查看库中内容与之前所备份的库中内容是否一致；
浙公网安备 33010602011771号