一、Mysql基本命令
1、登录到 Mysql 服务器
[root@server1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.20 Source distribution 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>
2、执行Mysql 操作语句，查看数据库中有哪些库。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
3、退出Mysql 操作环境
mysql> exit Bye [root@server1 ~]#
二、使用Mysql 数据库
1、查看当前使用的库中包含的表
mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
2、查看表的结构
mysql> describe user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum(‘N‘,‘Y‘) | NO | | N | |
3、创建及删除库和表
①、创建新的库
mysql> create database stf; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | mysql | | performance_schema | | stf | | sys | +--------------------+ 6 rows in set (0.01 sec)
②、创建新的表
mysql> create table sym(sym_name char(16)not null,sym_passwd char(48)default‘‘,primary key(sym_name));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| sym |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.00 sec)
③、删除一个数据表
mysql> drop table mysql.sym; Query OK, 0 rows affected (0.00 sec) mysql>
④、删除一个数据库
mysql> drop database stf; Query OK, 0 rows affected (0.00 sec) mysql>
4、管理表中的数据记录
首先创建一个stf库和sym表。
mysql> create database stf; Query OK, 1 row affected (0.00 sec) mysql> use stf; Database changed mysql> create table sym(sym_name char(16)not null,sym_passwd char(48)default‘‘,primary key(sym_name)); Query OK, 0 rows affected (0.01 sec) mysql>
①、插入数据记录
insert into 语句：用于向表中插入新的数据记录
执行以下操作会将stf 库中的sym 表插入一条记录：用户名为“zhangsan”，对应的密码为‘123457’。
mysql> insert into sym(sym_name,sym_passwd)values(‘zhangsan‘,password(‘123457‘)); Query OK, 1 row affected, 1 warning (0.00 sec) mysql>
②、查询数据记录
mysql> select * from stf.sym; +----------+-------------------------------------------+ | sym_name | sym_passwd | +----------+-------------------------------------------+ | zhangsan | *F13F0EEE74714A1A9922D61FC15789AD75FE4958 | +----------+-------------------------------------------+ 1 row in set (0.00 sec)
当需要根据特定的条件查找记录时，where条件语句是必不可少的，例如若要在sym表中用户名为zhangsan的记录，可执行以下操作
mysql> select sym_name,sym_passwd from stf.sym where sym_name=‘zhangsan‘; +----------+-------------------------------------------+ | sym_name | sym_passwd | +----------+-------------------------------------------+ | zhangsan | *F13F0EEE74714A1A9922D61FC15789AD75FE4958 | +----------+-------------------------------------------+ 1 row in set (0.00 sec)
③、修改数据记录
update：用于修改、更新表中的数据记录。
mysql> update stf.sym set sym_passwd=password(‘‘)where sym_name=‘zhangsan‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stf.sym; +----------+------------+ | sym_name | sym_passwd | +----------+------------+ | zhangsan | | +----------+------------+ 1 row in set (0.00 sec)
④、修改数据库密码
[root@server1 ~]# mysqladmin -uroot -p password ‘931106‘ Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
⑤、删除数据记录
delete：用于删除表中数据记录。
mysql> delete from stf.sym where sym_name=‘zhangsan‘; Query OK, 1 row affected (0.00 sec) mysql> select * from stf.sym; Empty set (0.00 sec）