sqlite3与mysql查看数据库、表结构

创建数据库

• sqlite3
  数据库名后面一般都会加上.db后缀。创建完，做一下其他操作如下面的例子是查看数据库列表，否则不会创建数据库文件。

~/Desktop$ sqlite3 WebDB.db
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .databases 
main: /home/kyun/Desktop/WebDB.db
sqlite> 

  

 

  
1
  
2
  
3
  
4
  
5
  
6
 

• mysql

~/Desktop$ mysql -u kyunwong -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.1.43-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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)]> create database WebDB;
Query OK, 1 row affected (0.00 sec)

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
 

查看数据库列表

• sqlite3

sqlite> .databases 
main: /home/kyun/Desktop/WebDB.db


  

 

  
1
  
2
  
3
 

• mysql

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| WebDB |
| information_schema |
| mysql |
| performance_schema |
| wong_web_db |
+--------------------+
5 rows in set (0.01 sec)


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
 

打开数据库

• sqlite3

sqlite>  .open WebDB.db

  

 

  
1
 

• mysql

MariaDB [(none)]> use WebDB;
Database changed
MariaDB [WebDB]> 

  

 

  
1
  
2
  
3
 

查看数据库中的表

• sqlite3

sqlite> .tables
products

  

 

  
1
  
2
 

• mysql

MariaDB [WebDB]> show tables;
+-----------------+
| Tables_in_WebDB |
+-----------------+
| product |
+-----------------+
1 row in set (0.01 sec)

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

查看表字段信息

• sqlite3

sqlite> .schema products
CREATE TABLE products(id int not null primary key,name varchar(32),price int default 0);

  

 

  
1
  
2
 

• mysql

MariaDB [WebDB]> desc product;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO   | PRI | NULL | auto_increment |
| name  | varchar(32) | YES  | | NULL | |
| price | int(11) | YES  | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

或者

MariaDB [WebDB]> show create table product;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| product | CREATE TABLE `product` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `price` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
 

或者

MariaDB [WebDB]> show full fields from product;
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(10) | NULL | NO   | PRI | NULL | auto_increment | select,insert,update,references | |
| name  | varchar(32) | utf8mb4_general_ci | YES  | | NULL | | select,insert,update,references | |
| price | int(11) | NULL | YES  | | 0 | | select,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.00 sec)


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

或者

MariaDB [WebDB]> show fields  from product;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO   | PRI | NULL | auto_increment |
| name  | varchar(32) | YES  | | NULL | |
| price | int(11) | YES  | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

退出

• sqlite3

sqlite> .quit

  

 

  
1
 

• mysql

MariaDB [WebDB]> exit

  

 

  
1
 

文章来源: blog.csdn.net，作者：WongKyunban，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/weixin_40763897/article/details/103345501