MySQL教程-01

mysql

创建数据表

       通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。
创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

实例：
在数据库中创建数据表runoob_tbl：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：MySQL命令终止符为分号 ;

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

创建成功后，通过命令行查看表结构：

show tables;
desc runoob_tbl;

删除数据表

       MySQL中删除数据表是非常容易操作的，但是你在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

删除MySQL数据表的通用语法：:

DROP TABLE table_name ;

插入数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。
向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

tips：如果数据是字符型，必须使用单引号或者双引号，如：“value”。

实例：
使用 SQL INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入3条数据：

INSERT INTO runoob_tbl 
(runoob_title,runoob_author,submission_date) 
VALUES 
("学习 mysql","我不是菜鸟",NOW());

insert into runoob_tbl
(runoob_title,runoob_author,submission_date)
values
("学习java","我不是菜鸟",NOW());

insert into runoob_tbl 
(runoob_title,runoob_author,submission_date) 
values 
("学习CSS","bilibili.com",NOW());

读取数据表：

select * from runoob_tbl;

• 在以上实例中，我们并没有提供 runoob_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。
• 实例中== NOW() 是一个 MySQL 函数==，该函数返回日期和时间。

添加数据的时候可以规定列进行添加。
如果所有的列都要添加数据可以不规定列进行添加数据：

insert into runoob_tbl
values
(0,"学习javascript","BILIBILI.COM",'2022-02-17');

• 第一列如果没有设置主键自增（PRINARY KEY AUTO_INCREMENT）的话添加第一列数据比较容易错乱，要不断的查询表看数据。
• 如果添加过主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null，这样添加数据可以自增，从而可以添加全部数据，而不用特意规定那几列添加数据。
  

查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。
在MySQL数据库中查询数据通用的 SELECT 语法：

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

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

返回所有数据

1. 返回数据表 runoob_tbl 的所有记录:

select * from runoob_tbl;

where子句

我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
使用 WHERE 子句从数据表中读取数据的通用语法：

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

• 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
• 在 WHERE 子句中指定任何条件。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。
下表中实例假定 A 为 10, B 为 20

• 如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。
• 使用主键来作为 WHERE 子句的条件查询是非常快速的。
• 如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

实例：
读取 runoob_tbl 表中 runoob_author 字段值为 ”我不是菜鸟“的所有记录：

select * from runoob_tbl where runoob_author='我不是菜鸟';

**tips:**MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

如下图：where子句不区分大小写，查找runoob_author字段所有值为bilibili.com的记录，所有大小写的记录都查找出来了。
因此使用BINARY关键字来区分大小写：

select * from runoob_tbl where binary runoob_author='BILIBILI.COM';

UPDATE更新

       如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。
UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

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

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。
• 可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

在 SQL UPDATE 命令使用 WHERE 子句来更新 runoob_tbl 表中指定的数据：
实例：
更新数据表中 runoob_id 为 3 的 runoob_title 字段值：

update runoob_tbl set runoob_title='学习c++' where runoob_id=3;

DELETE 语句

使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。
SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

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

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

实例：
删除 runoob_tbl 表中 runoob_id 为3 的记录：

delete from runoob_tbl where runoob_id=3;

LIKE 子句

       我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “runoob_author = ‘RUNOOB.COM’”。

       但是有时候我们需要获取 runoob_author 字段含有 “COM” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

---

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

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

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用== WHERE…LIKE 子句来指定条件==。

---

学一半leader安排任务了。。。。晚上加班吧。。。。
我胡汉三又回来了！！！！！接上面！

---

使用 WHERE…LIKE 子句来从MySQL数据表 runoob_tbl 中读取数据。
实例：
将 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：

select * from runoob_tbl where runoob_author like '%com';

tips:

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

在 where like 的条件查询中，SQL 提供了四种匹配方式：

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

UNION 操作符

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

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: 可选，返回所有结果集，包含重复数据。

UNION实例：

先准备好两个表并插入几条数据如下图：

CREATE TABLE websites(
user_id INT NOT NULL AUTO_INCREMENT,
name varchar(10) not null,
 url varchar(100) not null,
 alexa INT NOT NULL,
country varchar(20) not null,
 PRIMARY KEY(user_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 create table apps( 
id INT NOT NULL AUTO_INCREMENT, 
app_name varchar(50) not null, 
url varchar(100) not null, 
country varchar(10)not null, 
primary key(id)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

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

UNION ALL 实例:

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

带有 WHERE 的 UNION ALL

使用 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 表中使用SELECT 语句来读取数据。
      如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：

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

• 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 可以设定多个字段来排序。
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 可以添加 WHERE…LIKE 子句来设置条件。

实例：
读取 runoob_tbl 表中所有数据并按 submission_date 字段的升序排列。

select * from runoob_tbl order by submission_date asc;

读取 runoob_tbl 表中所有数据并按 submission_date 字段的降序排列

select * from runoob_tbl order by submission_date desc;

🎈涨芝士🧀啦：
MySQL 拼音排序
如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * 
FROM runoob_tbl
ORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * 
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);

---

GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法：

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

举个栗子🌰：
等等等一下。。。先往数据库中导入一些数据。

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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');

咳咳：📢接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

 select name,count(*) from employee_tbl group by name;

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

 SELECT name, SUM(signin) as signin_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(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;