mysql 5.7数据库操作记录_MySQL5.7数据库基本操作

最新推荐文章于 2021-02-26 17:40:19 发布

weixin_39788960

最新推荐文章于 2021-02-26 17:40:19 发布

阅读量288

点赞数

文章标签： mysql 5.7数据库操作记录

本文链接：https://blog.csdn.net/weixin_39788960/article/details/113267217

版权

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

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)

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;

+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

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;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| number | int(11) | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

18.向原有表中添加字段

mysql> desc chenyu;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| number | int(11) | 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;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| number | int(11) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

19.修改表中字段类型

mysql> desc chenyu;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| number | int(11) | YES | | 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;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| number | int(11) | 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;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| number | int(11) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

4 rows in set (0.03 sec)

21.修改表的字段名称及指定为空或非空

mysql> desc chenyu;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| number | int(11) | YES | | 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;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| number | int(11) | 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;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| number | int(11) | 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; 查看库中内容与之前所备份的库中内容是否一致；

weixin_39788960

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 5.7数据库操作记录_MySQL5.7数据库基本操作

1.首次进入数据库[root@node251 tmp]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.19Copyright (c) 2000, 2017, Oracle ...
复制链接

扫一扫