MYSQL最全的基础语法及练习

2020-05-04 本文已影响0人数据蛙datafrog

阅读路线:

创建数据库
删除数据库
选择数据库
创建数据表
删除数据表
插入数据
查询数据
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 语句

连接的使用

猜你喜欢

热点阅读