MySQL基本命令及基本操作（加if判断语句：）
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)MySQL数据库基本操作（2）
教程每周二、四、六更新
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云计算运维基础阶段的视频课程已经上线，欢迎关注：
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
select(加if判断语句)
mysql> 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 | Hobbit | 8 | 1 |
| 8 | Lair of Bones | 2 | 2 |
| 9 | abcdefg | 2 | 2034 |
+--------+----------------------------+----------+----------+
##判断authorid的值大于等于2，就判断是否优秀，普通，添加最后一列“是否优秀”：：：：：
mysql> select BookID,SeriesID,authorid,if(authorid >= 2,"优秀","普通") as 是否优秀 from books;
+--------+----------+----------+--------------+
| BookID | SeriesID | authorid | 是否优秀 |
+--------+----------+----------+--------------+
| 1 | 1 | 1 | 普通 |
| 2 | 1 | 1 | 普通 |
| 3 | 1 | 1 | 普通 |
| 4 | 2 | 2 | 优秀 |
| 5 | 2 | 2 | 优秀 |
| 6 | 2 | 2 | 优秀 |
| 7 | 8 | 1 | 普通 |
| 8 | 2 | 2 | 优秀 |
| 9 | 2 | 2034 | 优秀 |
+--------+----------+----------+--------------+
9 rows in set (0.00 sec)
##判断三种情况：：：
mysql> select Title,AuthorID,if(authorid >=2000,"优秀",if(authorid >=2,"普通","不优秀")) as是否优秀 from books;
另外一种写法：
mysql> select Title,AuthorID,
case when authorid >= 2000 then "优秀"
when authorid >= 2 then "普通"
else "不优秀"
end as 是否优秀
from books;
##多条件进行组合：：：：：：
mysql> select Title,seriesid,authorid,
case when authorid = 2 and seriesid = 3 then "book优秀"
when authorid > 2 and seriesid = 2 then "book普通"
else "一般"
end as 是否优秀
from books
;
###正则匹配regexp关键字,对其进行分类：：：：：：：
select title,swordability,
case when swordability regexp "[刀]" then "刀法"
when swordability regexp "[剑]" then "剑法"
else "其他"
end as 技能类型
from books;
实现效果如下：
#####截取省，市，县地区信息
mysql> select * from yourbooks;
+--------+--------------------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+--------------------------------------+----------+----------+
| 1 | 江苏省连云港市灌南县 | 1 | 1 |
| 2 | 山东省青岛市东海县 | 1 | 1 |
| 3 | 黑龙江省齐齐哈尔市甘南县 | 1 | 1 |
| 4 | The Sum of All Men | 2 | 2 |
| 5 | Brotherhood of the Wolf | 2 | 2 |
| 6 | Wizardborn | 2 | 2 |
| 7 | Hobbit | 8 | 1 |
| 8 | Lair of Bones | 2 | 2 |
| 9 | abcdefg | 2 | 2034 |
+--------+--------------------------------------+----------+----------+
9 rows in set (0.00 sec)
上代码：
mysql> select title,
mid(title,1,locate("省",title)) as 省,
mid(title,locate("省",title)+1,locate("市",title)-locate("省",title)) as 市,
mid(title,locate("市",title)+1,locate("县",title)-locate("市",title)) as 县
from yourbooks;
还有另外写法：如下！！
