阅读路线:
-
创建数据库
-
删除数据库
-
选择数据库
-
创建数据表
-
删除数据表
-
插入数据
-
查询数据
-
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 字段值。