MySQL数据库常用基本操作
MySQL数据库常用基本操作
郑加威 发表于9个月前
MySQL数据库常用基本操作
  • 发表于 9个月前
  • 阅读 16
  • 收藏 0
  • 点赞 0
  • 评论 0

移动开发云端新模式探索实践 >>>   

摘要: Linux环境下,MySQL数据库常用基本操作

登陆数据库

[root@test01 ~]# cd /mysql/bin
You have new mail in /var/spool/mail/root
[root@test01 bin]# ./mysql -u root -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 520
Server version: 5.5.47 Source distribution

Copyright (c) 2000, 2015, 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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monitor            |
| mysql              |
| performance_schema |
| qipusheng          |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> 
[root@test01 bin]# ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.47 Source distribution

Copyright (c) 2000, 2015, 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> 

显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monitor            |
| mysql              |
| performance_schema |
| qipusheng          |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

显示数据库参数

mysql> show variables  like '%max_%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| ft_max_word_len                         | 84                   |
| group_concat_max_len                    | 1024                 |
| innodb_max_dirty_pages_pct              | 75                   |
| innodb_max_purge_lag                    | 0                    |
| max_allowed_packet                      | 1048576              |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| max_connect_errors                      | 10                   |
| max_connections                         | 1000                 |
| max_delayed_threads                     | 20                   |
| max_error_count                         | 64                   |
| max_heap_table_size                     | 16777216             |
| max_insert_delayed_threads              | 20                   |
| max_join_size                           | 18446744073709551615 |
| max_length_for_sort_data                | 1024                 |
| max_long_data_size                      | 1048576              |
| max_prepared_stmt_count                 | 16382                |
| max_relay_log_size                      | 0                    |
| max_seeks_for_key                       | 18446744073709551615 |
| max_sort_length                         | 1024                 |
| max_sp_recursion_depth                  | 0                    |
| max_tmp_tables                          | 32                   |
| max_user_connections                    | 500                  |
| max_write_lock_count                    | 18446744073709551615 |
| myisam_max_sort_file_size               | 9223372036853727232  |
| performance_schema_max_cond_classes     | 80                   |
| performance_schema_max_cond_instances   | 1000                 |
| performance_schema_max_file_classes     | 50                   |
| performance_schema_max_file_handles     | 32768                |
| performance_schema_max_file_instances   | 10000                |
| performance_schema_max_mutex_classes    | 200                  |
| performance_schema_max_mutex_instances  | 1000000              |
| performance_schema_max_rwlock_classes   | 30                   |
| performance_schema_max_rwlock_instances | 1000000              |
| performance_schema_max_table_handles    | 100000               |
| performance_schema_max_table_instances  | 50000                |
| performance_schema_max_thread_classes   | 50                   |
| performance_schema_max_thread_instances | 1000                 |
| slave_max_allowed_packet                | 1073741824           |
| sql_max_join_size                       | 18446744073709551615 |
+-----------------------------------------+----------------------+
41 rows in set (0.00 sec)

mysql> 

 

选择数据库

mysql> use monitor;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

显示数据库中的表

mysql> show tables;
+-----------------------------+
| Tables_in_monitor           |
+-----------------------------+
| test_data                   |
| test_data_child             |
| test_data_main              |
| test_tree                   |
+-----------------------------+
97 rows in set (0.00 sec)

mysql> 

显示数据表的结构

mysql> describe test_tree;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | varchar(64)   | NO   | PRI | NULL    |       |
| parent_id   | varchar(64)   | NO   | MUL | NULL    |       |
| parent_ids  | varchar(2000) | NO   |     | NULL    |       |
| name        | varchar(100)  | NO   |     | NULL    |       |
| sort        | decimal(10,0) | NO   |     | NULL    |       |
| create_by   | varchar(64)   | NO   |     | NULL    |       |
| create_date | datetime      | NO   |     | NULL    |       |
| update_by   | varchar(64)   | NO   |     | NULL    |       |
| update_date | datetime      | NO   |     | NULL    |       |
| remarks     | varchar(255)  | YES  |     | NULL    |       |
| del_flag    | char(1)       | NO   | MUL | 0       |       |
+-------------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> 

显示表中记录

mysql> SELECT * FROM test_tree;
Empty set (0.00 sec)

mysql> 

建库

create databse 库名;

建表

create table 表名 (字段设定列表);

mysql> create table name(
    -> id int auto_increment not null primary key ,
    -> uname char(8),
    -> gender char(2),
    -> birthday date );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_userdb |
+------------------+
| name             |
+------------------+
1 row in set (0.00 sec)

mysql> describe name;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| uname    | char(8) | YES  |     | NULL    |                |
| gender   | char(2) | YES  |     | NULL    |                |
| birthday | date    | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

注: auto_increment 自增
     primary key    主键

增加记录

insert into name(uname,gender,birthday) values('张三','男','1971-10-01');

修改记录

update name set birthday='1971-01-10' where uname='张三';

删除记录

delete from name where uname='张三';

删除表

drop table 表名;

删除库

drop database 库名;

备份数据库

mysqldump -u root -p --opt 数据库名>备份名; //进入到库目录

恢复

mysql -u root -p 数据库名<备份名; //恢复时数据库必须存在,可以为空数据库

数据库授权

格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户user001密码为123456,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL,然后键入以下命令:

mysql> grant select,insert,update,delete on *.* to user001@"%" Identified by "123456";

说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL PRIVILEGES ; databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*. 

例子:

GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; 
GRANT ALL ON *.* TO 'pig'@'%'; 

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:  

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; 

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令: 
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; 

例2、增加一个用户user002密码为123456,让此用户只可以在localhost上登录,也可以设置指定IP,并可以对数据库test进行查询、插入、修改、删除的操作 (localhost指本地主机,即MySQL数据库所在的那台主机)

//这样用户即使用知道user_2的密码,他也无法从网上直接访问数据库,只能通过MYSQL主机来操作test库。
//首先用以root用户连入MySQL,然后键入以下命令:

mysql>grant select,insert,update,delete on test.* to user002@localhost identified by "123456";

注: 其次也可以采用修改表的方式,处理用户的登录方式:

刷新授权

mysql> flush privileges; //修改生效

撤销用户权限

mysql> REVOKE privilege ON databasename.tablename FROM 'username'@'host'; 

说明: privilege, databasename, tablename - 同授权部分. 

例子:

mysql> REVOKE SELECT ON *.* FROM 'pig'@'%'; 

注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):
GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';
命令并不能撤销该用户对test数据库中user表的SELECT 操作,
相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';
命令也不能撤销该用户对test数据库中user表的Select 权限. 

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看. 

 

设置与更改用户密码

mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用

mysql> SET PASSWORD = PASSWORD("newpassword"); 

例子:

mysql> SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456"); 

 

退出

mysql> exit;

数据库: Mysql
表: User
修改: User表中的Host列的值来现实登录入口

附表:在MySQL中的操作权限

ALTER Allows use of ALTER TABLE.
ALTER ROUTINE Alters or drops stored routines.
CREATE Allows use of CREATE TABLE.
CREATE ROUTINE Creates stored routines.
CREATE TEMPORARY TABLE Allows use of CREATE TEMPORARY TABLE.
CREATE USER Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW Allows use of CREATE VIEW.
DELETE Allows use of DELETE.
DROP Allows use of DROP TABLE.
EXECUTE Allows the user to run stored routines.
FILE Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.
INDEX Allows use of CREATE INDEX and DROP INDEX.
INSERT Allows use of INSERT.
LOCK TABLES Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.
PROCESS Allows use of SHOW FULL PROCESSLIST.
RELOAD Allows use of FLUSH.
REPLICATION Allows the user to ask where slave or master
CLIENT servers are.
REPLICATION SLAVE Needed for replication slaves.
SELECT Allows use of SELECT.
SHOW DATABASES Allows use of SHOW DATABASES.
SHOW VIEW Allows use of SHOW CREATE VIEW.
SHUTDOWN Allows use of mysqladmin shutdown.
SUPER Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE Allows use of UPDATE.
USAGE Allows connection without any specific privileges.
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 77
博文 607
码字总数 958259
×
郑加威
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: