(Linux下Mysql数据库的基础操作)
一、Mysql数据介绍
Mysql数据库是一种关系型数据库管理系统，具有的优点有体积小、速度快、总体成本低，开源，可移植性（跨平台，在不同系统中使用），可以和开发语结合，属于轻量级数据库。
二、数据库相关术语介绍
1.数据库相关名词
数据库： database
表： table
行： row
列： column
索引： index
视图： view
用户： user
权限： privilege
存储过程： procedure
存储函数： function
触发器： trigger
事件调度器： event scheduler，任务计划
2.相关术语介绍
数据库中的表：表是一种结构化的文件，可用于存储特定类型的数据，表中的每一行，也称为一条记录。
数据库中的列：表中的一个字段，所有表都是由一个或多个列组成的。表中的每一列，称为属性，字段。
数据库中的索引： 将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储。
关系型数据库：关系数据库系统建立了关系模型，并用它来处理数据。关系模型在表中将信息与字段关联起来（也就是schemas），存储在数据表的行和列中。数据表可以彼此关联协作存储，也很容易提取数据。
非关系型数据库：非关系型数据不适合存储在数据表的行和列中，而是大块组合在一起。非关系型数据通常存储在数据集中，就像文档、键值对或者图结构。你的数据及其特性是选择数据存储和提取方式的首要影响因素。
三、Mysql数据库的管理
1.创建数据库用户
①创建用户
mysql> create user test@localhost identified by '123456';
Query OK, 0 rows affected (0.08 sec)
②授予用户权限
mysql> grant all privileges on *.* to test@localhost;
Query OK, 0 rows affected (0.10 sec)
③刷新权限缓存
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
④查看新建用户及登录
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)
[root@control ~]# mysql -utest -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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> show grants for 'test'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test`@`localhost` |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test`@`localhost` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
②查看单个用户是否存在
mysql> select user,host from mysql.user where user='test';
+------+-----------+
| user | host |
+------+-----------+
| test | localhost |
+------+-----------+
1 row in set (0.00 sec)
③查询当前用户
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
3.修改密码及删除用户
①修改密码
mysql> alter user 'test'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
②删除数据库用户
mysql>
mysql> drop user test@localhost;
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
四、创建数据库及表
1.查看当前数据库列表
mysql> use mysql;
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.显示指定数据库的表
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| 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 |
+---------------------------+
33 rows in set (0.01 sec)
3.显示数据表的详细信息
①显示mysql数据库中某个表信息
mysql> show columns from server_cost;
+---------------+---------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------------------------+
| cost_name | varchar(64) | NO | PRI | NULL | |
| cost_value | float | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| comment | varchar(1024) | YES | | NULL | |
| default_value | float | YES | | NULL | VIRTUAL GENERATED |
+---------------+---------------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
②显示mysql数据库中所有表信息
mysql> SHOW TABLE STATUS FROM mysql;
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+-----------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+-----------------------------------------+
| columns_priv | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Column privileges |
| component | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | 1 | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | | Components |
| db | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 16384 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Database privileges |
| default_roles | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Default roles |
| engine_cost | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| func | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | User defined functions |
| general_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | | General log |
| global_grants | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | 2021-05-07 11:42:36 | NULL | utf8_bin | NULL | stats_persistent=0 | Extended global grants |
| gtid_executed | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| help_category | InnoDB | 10 | Dynamic | 40 | 409 | 16384 | 0 | 16384 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help categories |
| help_keyword | InnoDB | 10 | Dynamic | 658 | 149 | 98304 | 0 | 81920 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help keywords |
| help_relation | InnoDB | 10 | Dynamic | 993 | 82 | 81920 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | keyword-topic relation |
| help_topic | InnoDB | 10 | Dynamic | 722 | 2201 | 1589248 | 0 | 81920 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help topics |
| innodb_index_stats | InnoDB | 10 | Dynamic | 10 | 1638 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-22 16:47:16 | NULL | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | |
| innodb_table_stats | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-22 16:47:16 | NULL | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | |
| password_history | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Password history for user accounts |
| plugin | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL plugins |
| procs_priv | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 16384 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Procedure privileges |
| proxies_priv | InnoDB | 10 | Dynamic | 1 | 16384 | 16384 | 0 | 16384 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | User proxy privileges |
| role_edges | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Role hierarchy and role grants |
| server_cost | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| servers | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL Foreign Servers table |
| slave_master_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Master Information |
| slave_relay_log_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Relay Log Information |
| slave_worker_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Worker Information |
| slow_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | | Slow log |
| tables_priv | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 16384 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | Table privileges |
| time_zone | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | 1 | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zones |
| time_zone_leap_second | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Leap seconds information for time zones |
| time_zone_name | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone names |
| time_zone_transition | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transitions |
| time_zone_transition_type | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transition types |
| user | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 4194304 | NULL | 2021-04-23 00:47:17 | 2021-05-07 11:42:36 | NULL | utf8_bin | NULL | stats_persistent=0 | Users and global privileges |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+-----------------------------------------+
33 rows in set (0.01 sec)
4.创建数据库的表
①创建新数据库
mysql> create DATABASE huawei;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| huawei |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
常用创建数据方式为默认编码格式为utf8格式。
mysql> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.06 sec)
②创建用户表
mysql> create table student(id int auto_increment primary key,name varchar(16) not null,age int,sex char(1));
Query OK, 0 rows affected (0.07 sec)
③查看当前使用数据库
mysql> select database();
+------------+
| database() |
+------------+
| huawei |
+------------+
1 row in set (0.00 sec)
④查看当前数据库的表
mysql> describe student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
⑤查看字符集
mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)
五、查询表其他相关信息
mysql> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.09 sec)