Mysql基础

登陆数据库

mysql -u用户 -p密码

testda31 ~ # mysql -uroot -p******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19855128
Server version: 10.3.15-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> 
 

显示数据库

show databases;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| test1              |
| mysql              |
| test2              |
| test3              |
+--------------------+
6 rows in set (0.000 sec)

MariaDB [(none)]> 

切换到具体某个数据库查看数据表

use 库名
show tables;

MariaDB [test]> use test1 
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
MariaDB [tomwebmail]> show tables;
+--------------------------+
| Tables_in_test    |
+--------------------------+
| LOGIN_FORWARD            |
| USER_LOGIN_HISTORY       |
| USER_MAIL_FILTER         |
| USER_MOVE_LIST           |
| USER_PREFERENCES_SETTING |
| USER_REMAIL_STATUS       |
| USER_W_OR_BLIST          |
| ecard                    |
| sys_notice               |
| user_change_pwd          |
| user_for_dialog          |
| user_login_count         |
+--------------------------+
12 rows in set (0.000 sec)

MariaDB [tomwebmail]> 

显示数据库表的结构

desc 表名;

MariaDB [tomwebmail]> desc ecard;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| cardid   | int(11)      | NO   | PRI | NULL    | auto_increment |
| cardname | varchar(255) | NO   |     | NULL    |                |
| userid   | varchar(255) | NO   | MUL | NULL    |                |
| body     | text         | NO   |     | NULL    |                |
| qrsrc    | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

查看建表语句

show create table 表名

MariaDB [vmail]> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `username` varchar(128) CHARACTER SET utf8 NOT NULL,
  `home` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `test_style` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `quota_storage` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `quota_messages` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `last_login` varchar(32) DEFAULT NULL COMMENT '最后使用时间',
  `used_quota_storage` bigint(20) DEFAULT NULL COMMENT '已用',
  `used_quota_messages` bigint(20) DEFAULT NULL COMMENT '总数',
  `lazy_expunge_days` varchar(5) DEFAULT NULL COMMENT '数据'’,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

查看当前用户

select user();

MariaDB [mysql]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

查看当前使用的数据库

select databsase();

MariaDB [vmail]> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.000 sec)

创建数据库

create database 库名

MariaDB [vmail]> create database testdb1;
Query OK, 1 row affected (0.001 sec)
MariaDB [vmail]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb1            |
+--------------------+
5 rows in set (0.001 sec)

删除数据库

drop database 库名;

创建表

CREATE TABLE 表名称( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... )

MariaDB [vmail]> use testdb1
Database changed
MariaDB [testdb1]> create table tab(id int(4),name char(40)) charset =utf8;
Query OK, 0 rows affected (0.041 sec)

MariaDB [testdb1]> show tables;
+-------------------+
| Tables_in_testdb1 |
+-------------------+
| tab               |
+-------------------+
1 row in set (0.001 sec)

MariaDB [testdb1]> desc tab;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.001 sec)

删除表

drop table t1;

查看当前数据库版本

select version();

MariaDB [testdb1]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.15-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

查看数据库状态

show status;

MariaDB [testdb1]> show status;
+--------------------------------------------------------------+--------------------------------------------------+
| Variable_name                                                | Value                                            |
+--------------------------------------------------------------+--------------------------------------------------+
| Aborted_clients                                              | 1937                                             |
| Aborted_connects                                             | 19852601                                         |
| Access_denied_errors                                         | 0                                                |
	.....省略.......
| wsrep_provider_vendor                                        |                                                  |
| wsrep_provider_version                                       |                                                  |
| wsrep_ready                                                  | OFF                                              |
| wsrep_thread_count                                           | 0                                                |
+--------------------------------------------------------------+--------------------------------------------------+
532 rows in set (0.008 sec)