MySQL数据库基本操作
原创 Cloud研习社 Cloud研习社 2023-08-12 10:33 发表于山东 收录于合集 #一站式教程274个 #计算机248个 #mysql8个 #linux292个 #云计算285个教程每周二、四、六更新
# 查看有哪些数据库
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)MySQL数据库基本操作（2）
原创 Cloud研习社 Cloud研习社 2023-08-15 08:17 发表于山东 收录于合集 #一站式教程274个 #mysql8个 #linux292个 #计算机248个 #云计算285个教程每周二、四、六更新
MariaDB [test]> update books set Title = "The Hobbit" where BookID=7;
# 验证修改是否成功
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 Hobbit | 0 | 1 | # 可以看到，这里Title已经修改成了”The Hobbit“
+--------+----------------------------+----------+----------+插入数据
格式：
INSERT INTO tablename (field,field2,...) VALUES (value, value2,...);实例：
# 在表中插入一行
MariaDB [test]> INSERT INTO books (Title, SeriesID, AuthorID)
-> VALUES ("Lair of Bones", 2, 2);
# 验证是否插入成功
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 Hobbit | 0 | 1 |
| 8 | Lair of Bones | 2 | 2 | # 这里就是我们刚插入的行
+--------+----------------------------+----------+----------+删除数据
# 删除books表中authorid为2034的行。即删除所有authorid为2034的书的信息
delete from books where authorid='2034';复制表
MariaDB [test]> create table mybooks as select * from books;这样就完成了表（table）的复制。两个表的数据完全一样。（请自行查看验证）
复制表结构
这里只复制表结构，不复制数据。
MariaDB [test]> create table yourbooks like books;
Query OK, 0 rows affected (0.005 sec)
MariaDB [test]> describe yourbooks;
+----------+--------------+------+-----+---------+----------------+
| 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.001 sec)复制表内容
一般用于复制表结构后使用
MariaDB [test]> insert into yourbooks select * from books;
Query OK, 8 rows affected (0.002 sec)
Records: 8 Duplicates: 0 Warnings: 0现在books表里的内容就复制到了yourbooks中，可以用select * from yourbooks；命令来验证。
检索（查询）数据
select title from books; # 从books表中检索title（书名）
SELECT title FROM books LIMIT 5; # 从books表中检索title（书名）。只列前出5行雷哥的Linux云计算运维基础阶段的视频课程已经上线，欢迎关注：
标签：NULL,Title,命令,books,sec,MySQL,test,基本操作,MariaDB From： https://www.cnblogs.com/cherishthepresent/p/17636875.html