MySQL最全的基础语法及练习

阅读路线:

  • 创建数据库

  • 删除数据库

  • 选择数据库

  • 创建数据表

  • 删除数据表

  • 插入数据

  • 查询数据

  • 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 字段值。

  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值