MYSQL最全的基础语法及练习

作者: 数据蛙datafrog | 来源:发表于2020-05-04 08:55 被阅读0次

阅读路线:

创建数据库
删除数据库
选择数据库
创建数据表
删除数据表
插入数据
查询数据
WHERE子句
UPDATE 更新
DELETE语句
LIKE语句
UNION 操作符
排序
GROUP BY语句
连接的使用

创建数据库

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:

CREATE DATABASE test;       -- test为我们创建数据库的名称

删除数据库

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。
drop 命令格式：

-- 例如删除名为 test 的数据库：
DROP DATABASE test;

选择数据库

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。
以下实例选取了数据库 test:

USE tset;

创建数据表

创建MySQL数据表需要以下信息：

表名
表字段名
定义每个表字段

以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (
    column_name column_type
);

以下例子中我们将在 test数据库中创建数据表test_47：

CREATE TABLE IF NOT EXISTS `test_47` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `author` VARCHAR(40) NOT NULL,
    `date` DATE,
    PRIMARY KEY (`id`)
);

实例解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

表创建成功后，可以使用以下命令查看表结构:

DESC test_47;

输出:

删除数据表

以下为删除MySQL数据表的通用语法：

DROP TABLE table_name;

插入数据

用 INSERT 语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号隔开
语法格式：

INSERT INTO table_name (column_list) 
VALUES (value_list1) , (value_list2),  ...  ,(value_listn);

以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 test_47 插入数据

INSERT INTO test_47 (id, title, author, date)
VALUES (1, '学习mysql', '数据蛙', now()),
    (2, '学习python', '数据蛙', now()),
    (3, '学习BI', '数据蛙', now());

读取数据表:

SELECT * FROM test_47;

输出:

查询数据

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
使用 WHERE 语句来包含任何条件。
使用 LIMIT 属性来设定返回的记录数。
通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

WHERE子句

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下实例将读取 test_47 表中 id 字段值为 2 的所有记录：

SELECT * from test_47 WHERE id = 2;

输出:

UPDATE 更新

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

可以同时更新一个或多个字段。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

以下实例将更新数据表中 id 为 3 的 title 字段值

UPDATE test_47 SET title='学习算法' WHERE id=3;

执行成功后查看更新的数据:

SELECT * FROM test_47 WHERE id = 3;

输出:

从结果上看，id 为 3 的 title 值已被修改。

DELETE 语句

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
可以在 WHERE 子句中指定任何条件
可以在单个表中一次性删除记录。

实例:
以下实例将删除 test_47 表中 id 为3 的记录：

DELETE FROM test_47 WHERE id=3;

执行后，查看执行删除操作后的数据表:

LIKE子句

LIKE运算符用于WHERE表达式中，以搜索匹配字段中的指定内容

LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

可以使用LIKE子句代替等号 =
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
可以使用 AND 或者 OR 指定一个或多个条件。

实例:
以下是我们将 tes 表中获取 title 字段中以 sql 为结尾的的所有记录：

SELECT *
FROM test_47
WHERE title LIKE '%sql';

输出:

UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数:

expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

实例数据集:

-- 创建Websites表
CREATE TABLE IF NOT EXISTS `Websites` (
    id int(10),
    name varchar(20),
    url varchar(100),
    alexa int(10),
    country varchar(20)
);

-- 向Websites插入数据:
INSERT INTO Websites (id, name, url, alexa, country)
VALUES 
    (1, 'Google', 'https://www.google.cm/ ', 1, ' USA'),
    (2, ' 淘宝', ' https://www.taobao.com/ ', 13, 'CN'),
    (3, '菜鸟教程', 'http://www.runoob.com/', 4689, ' CN '),
    (4, '微博 ', 'http://weibo.com/', 20, 'CN'),
    (5, 'Facebook ', ' https://www.facebook.com/', 3, 'USA'),
    (7, 'stackoverflow', 'http://stackoverflow.com/ ', 0, 'IND ');

-- 创建Apps表:
CREATE TABLE IF NOT EXISTS `Apps` (
    id int(5),
    app_name varchar(20),
    url varchar(100),
    country varchar(5)
);

-- 向Apps表插入数据
INSERT INTO Apps (id, app_name, url, country)
VALUES 
    (1, 'QQ APP', 'http://im.qq.com/  ', ' CN '),
    (2, '微博 APP ', 'http://weibo.com/ ', ' CN  '),
    (3, '淘宝 APP', ' https://www.taobao.com/ ', ' CN ');

Websites表:

Apps表

SQL UNION 实例:
下面的 SQL 语句从 "Websites" 和 "Apps" 表中选取所有不同的country（只有不同的值）：

CREATE TABLE IF NOT EXISTS `Apps` (
    id int(5),
    app_name varchar(20),
    url varchar(100),
    country varchar(5)
);

输出:

注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

SQL UNION ALL 实例:
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "Apps" 表中选取所有的country（也有重复的值）：

SELECT country
FROM Websites
UNION ALL
SELECT country
FROM Apps
ORDER BY country;

输出:

带有 WHERE 的 SQL UNION ALL:
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "Apps" 表中选取所有的中国(CN)的数据（也有重复的值）：

SELECT country, name
FROM Websites
WHERE country = 'CN'
UNION ALL
SELECT country, app_name
FROM Apps
WHERE country = 'CN'
ORDER BY country;

输出:

排序

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
语法:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
可以设定多个字段来排序。
可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列
实例数据集:

-- 创建runoob_tbl表:
CREATE TABLE IF NOT EXISTS `runoob_tbl` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `author` VARCHAR(40) NOT NULL,
    `date` DATE,
    PRIMARY KEY (`id`)
);

INSERT INTO runoob_tbl (id, title, author, date)
VALUES (3, '学习 Java ', 'RUNOOB.COM', '2015-05-01 '),
    (4, '学习 Python RUNOOB.COM ', '2016-03-06'),
    (2, '学习 MySQL', '数据蛙 ', ' 2017-04-12'),
    (1, ' 学习 PHP', ' 数据蛙', '2017-04-12');

实例:
以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 runoob_tbl 中的数据：

SELECT * FROM runoob_tbl ORDER BY date ASC;

输出:

SELECT * from runoob_tbl ORDER BY submission_date DESC;

输出:

GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

创建实例数据集:

-- 创建employee_tbl表
CREATE TABLE `employee_tbl` (
    `id` int(11) NOT NULL,
    `name` char(10) NOT NULL DEFAULT '',
    `date` datetime NOT NULL,
    `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
    PRIMARY KEY (`id`)
);

-- 向employee_tbl表插入数据:
INSERT INTO `employee_tbl`
VALUES
    ('1', '小明', '2016-04-22 15:25:33', '1'),
    ('2', '小王', '2016-04-20 15:25:47', '3'),
    ('3', '小丽', '2016-04-19 15:26:02', '2'),
    ('4', '小王', '2016-04-07 15:26:14', '4'),
    ('5', '小明', '2016-04-11 15:26:40', '4'),
    ('6', '小明', '2016-04-04 15:26:54', '2');

employee_tbl表:

实例:
1.使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

输出:

2:使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT...)；
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

SELECT name, SUM(singin) AS singin_count
FROM employee_tbl
GROUP BY name WITH ROLLUP;

输出:

其中记录 NULL 表示所有人的登录次数；
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

SELECT coalesce(a, b, c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
以下实例中如果名字为空我们使用总数代替：

SELECT coalesce(name, '总数'), SUM(singin) AS singin_count
FROM employee_tbl
GROUP BY name WITH ROLLUP;

输出:

连接的使用

实际应用中我们经常需要从多个数据表中读取数据；接下来向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

创建实例数据集:

-- 创建runoob_tbl表
CREATE TABLE `runoob_tbl` (
    `runoob_id` int(11) NOT NULL AUTO_INCREMENT,
    `runoob_title` varchar(100) NOT NULL,
    `runoob_author` varchar(40) NOT NULL,
    `submission_date` date DEFAULT NULL,
    PRIMARY KEY (`runoob_id`)
);

-- 向runoob_tbl插入数据
INSERT INTO `runoob_tbl`
VALUES ('1', '学习 PHP', '菜鸟教程', '2017-04-12'),
    ('2', '学习 MySQL', '菜鸟教程', '2017-04-12'),
    ('3', '学习 Java', 'RUNOOB.COM', '2015-05-01'),
    ('4', '学习 Python', 'RUNOOB.COM', '2016-03-06'),
    ('5', '学习 C', 'FK', '2017-04-05');

-- 创建tcount_tbl表
CREATE TABLE `tcount_tbl` (
    `runoob_author` varchar(255) NOT NULL DEFAULT '',
    `runoob_count` int(11) NOT NULL DEFAULT '0'
);

-- 向tcount_tbl表插入数据
INSERT INTO `tcount_tbl`
VALUES ('菜鸟教程', '10'),
    ('RUNOOB.COM ', '20'),
    ('Google', '22');

runoob_tbl表:

tcount_tbl表:

实例1:

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表，来读取runoob_tbl表中所有runoob_author字段，在tcount_tbl表对应的runoob_count字段值：

SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

输出:

以上 SQL 语句等价于：

SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a, tcount_tbl b
WHERE a.runoob_author = b.runoob_author;

实例2:

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL LEFT JOIN 的应用：

SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

输出:

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中没有对应的 runoob_author 字段值。
实例3:

以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL RIGHT JOIN的应用：

SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

输出:

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

MYSQL最全的基础语法及练习

创建数据库

删除数据库

选择数据库

创建数据表

删除数据表

插入数据

查询数据

WHERE子句

UPDATE 更新

DELETE 语句

LIKE子句

UNION 操作符

排序

GROUP BY 语句

连接的使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

大数据，机器学习，人工智能

大数据爬虫Python AI Sql

大数据

数据蛙数据分析每周作业

MYSQL最全的基础语法及练习

创建数据库

删除数据库

选择数据库

创建数据表

删除数据表

插入数据

查询数据

WHERE子句

UPDATE 更新

DELETE 语句

LIKE子句

UNION 操作符

排序

GROUP BY 语句

连接的使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

大数据，机器学习，人工智能

大数据 爬虫Python AI Sql

大数据

数据蛙数据分析每周作业

大数据爬虫Python AI Sql