MySQL数据库基本操作

2023年 8月 13日 63.1k 0

基本操作有:查看有哪些数据库、查看有哪些表、创建数据库、创建表、查看表信息、向表中插入数据等

图片[1]-MySQL数据库基本操作-不念博客

# 查看有哪些数据库
MariaDB [(none)]> show databases;

# 切换到test数据库
MariaDB [(none)]> use test;

# 查看当前数据库有哪些表
MariaDB [test]> show tables;
Empty set (0.000 sec) # 表明当前数据库是空的

# 如果test数据库不存在,则创建
MariaDB [test]> CREATE DATABASE IF NOT EXISTS test;

# 在数据库test种创建表三个表:books、authors、series
MariaDB [test]> CREATE TABLE IF NOT EXISTS books ( # 创建books表(前提是books表不存在,如果已经存在,则不创建)
    -> BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> Title VARCHAR(100) NOT NULL,
    -> SeriesID INT, AuthorID INT);
Query OK, 0 rows affected (0.033 sec)

MariaDB [test]> CREATE TABLE IF NOT EXISTS authors # 创建authors表(前提是authors表不存在,如果已经存在,则不创建)
    -> (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> CREATE TABLE IF NOT EXISTS series # 创建series表(前提是series表不存在,如果已经存在,则不创建)
    -> (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.005 sec)

# 接下来我们再来看看表是否添加成功
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| authors |
| books |
| series |
+----------------+
3 rows in set (0.000 sec)

# 向books表中插入数据
MariaDB [test]> INSERT INTO books (Title,SeriesID,AuthorID)
    -> VALUES('The Fellowship of the Ring',1,1),
    -> ('The Two Towers',1,1), ('The Return of the King',1,1),
    -> ('The Sum of All Men',2,2), ('Brotherhood of the Wolf',2,2),
    -> ('Wizardborn',2,2), ('The Hobbbit',0,1);
Query OK, 7 rows affected (0.004 sec)
Records: 7  Duplicates: 0  Warnings: 0

# 查看表信息
MariaDB [test]> describe books;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| BookID | int(11) | NO | PRI | NULL | auto_increment |
| Title | varchar(100) | NO |     | NULL |                |
| SeriesID | int(11) | YES | | NULL | |
| AuthorID | int(11) | YES |     | NULL |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

# 查询数据(从表中查询数据)
MariaDB [test]> select * from books;
+--------+----------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
| 1 | The Fellowship of the Ring | 1 |        1 |
|      2 | The Two Towers |        1 | 1 |
| 3 | The Return of the King | 1 |        1 |
|      4 | The Sum of All Men |        2 | 2 |
| 5 | Brotherhood of the Wolf | 2 |        2 |
|      6 | Wizardborn |        2 | 2 |
| 7 | The Hobbbit | 0 |        1 |
+--------+----------------------------+----------+----------+
7 rows in set (0.000 sec)

小知识:sql语句允许换行,直到遇到分号+回车才会认为sql语句输入结束,进入执行阶段

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论