MySql的SQL详解

Database 操作语句

创建数据库：

create {database | schema } [if not exists] db_name [create_specification]
# 注意，在mysql中schema和database是等效的

# 1. 数据库字符集
# 2. 字符校对集： 决定数据排序的
create_specification:
    [defualt] character set [=] charset_name
    [default] collate [=] collation_name

# 注意 character set 也可以写作 charset 和 char set;
# 推荐使用charset

# 创建以个字符集为utf-8的数据库
create [database | schema] if not exists study charset utf8;

查看警告或错误

• show warning

• show errors

展示所有的数据库：

show [databases | schemas];

查看当前选择的数据库

select database();

查看指定的数据库的创建语句，可用于查看数据库的编码：

show create database database_name;

使用指定的数据库：

use databse_name;

删除指定的数据库：

drop [databse | schema] [if not exists] databse_name;

修改数据库码表和字符校对（不常用）:

• alter database 数据库名称 character set 数据库码表 [collate 校对集名称];

• example： alter database day15 character set utf8 collate utf8_bin;

Table 操作语句

• 创建数据表

  CREATE TABLE [IF NOT EXISTS] t_tablename(column_name data_type,.........);//列名 数据类型，下一列名 数据类型，….

  example:

  CREATE TABLE t_tab1(
  id int /*约束/主键约束 auto_increment*/,
  username VARCHAR(20),
  age TINYINT UNSIGNED,
  salary FLOAT(5,2) UNSIGNED
  );

• 查看数据表：

  SHOW TABLES [FROM db_name] [LIKE 'pattern' |WHERE expr]

• 查看数据表的结构 (查看列)

  SHOW COLUMNS FROM tbl_name;

  DESC tbl_name; description

• 修改表的名称

  rename table 旧表名 to 新名;

  rename table t_user to user;

• (用的极少)修改表的字符集. (如果创建表时不指定,默认使用数据库的字符集)

  alter table 表名 character set 字符集 collate 校对集;

  alter table t_user character set utf8 collate utf8_bin;

• 添加一列

  alter table 表名 add 列名 类型;

  alter table t_user add photo blob;

• 修改列的类型

  alter table 表名 modify 列名 类型;

  alter table t_user modify photo varchar(20);

• 修改列的名称

  alter table 表名 change 旧列名 新列名 数据类型；

  将 photo这一列 改名为 image

  alter table t_user change photo image varchar(20);

• 删除某列

  alter table 表名 drop 列名;

  alter table t_user drop image;

• 插入记录

  INSERT [INTO] t_tblname [(col_name,....)] VALUES (val,...)

  可以给全部的字段赋值，也可以给指定的字段赋值

  例如：

  INSERT tab1(username,salary) VALUES('John',485.88); 给指定的username和salary赋值 其他不赋值

• 查看记录：

  SELECT expr,...... FROM tbl_name;

• 空值和非空

  NULL ，字段值可以为空

  NOT NULL，字段值禁止为空

  例如：

  CREATE TABLE tab2(
  user VARCHAR (20),
  age TINYINT NOT NULL,
  salary FLOAT NULL
  );

• 自动编号AUTO_INCREMENT

  • 自动编号：保证记录的唯一性

  • 类型必须为整型（可以是FLOAT(5,0)等，小数点后必须为0），必须和主键PRIMARY KEY组合使用

  • 默认情况下，起始值为1，每次的增量为1

  • 主键自动为 NOT NULL

  • AUTO_INCREMENT必须和主键(PRIMARY KEY)一起使用,但是主键不一定必须和AUTO_INCREMENT一起使用

• 约束

  分类：表级约束（针对两个及以上的字段），列级约束（针对于单个字段）

  类型：NOT NULL 非空约束

  PRIMARY KEY 主键约束

  UNIQUE KEY 唯一约束

  DEFAULT 默认约束

  FOREIGN KEY 外键约束

• 外键约束：

  作用：保持数据一致性，完整性。实现一对一或一对多关系。

  要求：

  父表和子表必须使用相同的存储引擎，且禁止使用临时表。

  数据表的存储引擎只能为InnoDB。

  外键列和参照列必须具有相似的数据类型。其中数字的长度 或 是否有符号位必须相同，而字符的长度可以不同。

  外键列和参照列必须创建索引。如果外键列不存在索引，mysql将自动创建索引。

join连接查询

笛卡儿积： 集合的元素，做两两组合

比如，

集合A: {1, 2, 3}

集合B: {6，7}

则集合的笛卡儿积为： {(1,6), (1,7), (2, 6), (2,7), (3,6), (3,7)}， 共 3*2 个元素。

即集合的相乘操作 A*B。

使用原始的方式，进行表的连接查询

CREATE TABLE t_category (
  id   INT               AUTO_INCREMENT PRIMARY KEY,
  name CHAR(20) NOT NULL DEFAULT ''
)
  ENGINE myisam
  CHARSET UTF8;

CREATE TABLE t_goods (
  id          INT               AUTO_INCREMENT PRIMARY KEY,
  name        CHAR(20) NOT NULL DEFAULT '',
  category_id INT
)
  ENGINE myisam
  CHARSET UTF8;

INSERT INTO t_category (name)
VALUES ('生活用品'),
       ('酒水饮料'),
       ('家用电器');

INSERT INTO t_goods (name, category_id)
VALUES ('卫生纸', 1),
       ('洗衣粉', 1),
       ('可乐', 2),
       ('诺基亚K10', NULL);

一个类型，对应多个商品。

在数据库中，如何实现笛卡儿积？

select g.name, c.name
from t_goods g,
     t_category c;

---- 结果，总共3*4=12个元素，是笛卡儿积
卫生纸        生活用品
卫生纸        酒水饮料
卫生纸        家用电器
洗衣粉        生活用品
洗衣粉        酒水饮料
洗衣粉        家用电器
可乐        生活用品
可乐        酒水饮料
可乐        家用电器
诺基亚K10    生活用品
诺基亚K10    酒水饮料
诺基亚K10    家用电器

即 select * from tableA, tableB 就是tableA和tableB的笛卡儿积。

从行来说，是tableA和tableB行的两两组合，

从列来说，是tableA和tableB列的并集（指定列排除）

筛选出所需要的正确的数据：

select c.name as category, g.name as goods
from t_goods g,
     t_category c
where g.category_id = c.id;

上面就可以查出符合条件的结果，虽然使用笛卡儿积 + where查询条件可以完成需求，但是使用该种方式会在内存中生成一个非常大的数据，损耗系统性能， 并且索引没利用。

内连接

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

A [inner] join B on 条件

file

内连接和上面的笛卡儿积+条件所得的结果相同，内连接不会打印null值：

select c.name as category, g.name as goods
from t_category c
       inner join t_goods g on c.id = g.category_id;

左（外）连接

假设A表在左，B表在A表右滑动，A表与B表通过一个关系来 筛选B表的行。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

file

A left join B on 条件

这句话也会生成一个结果集，可以看成一张表，设为C，所以我们也可以对C表进行查询。

使用左连接进行查询：

select c.name as category, g.name as goods
from t_category c
       left join t_goods g on c.id = g.category_id;

-- 查询结果
生活用品    卫生纸
生活用品    洗衣粉
酒水饮料    可乐
家用电器    NULL

右（外）连接

与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

file

同左连接类似：

select c.name as category, g.name as goods
from t_category c
       right join t_goods g on c.id = g.category_id;

-- 查询结果
select c.name as category, g.name as goods
from t_category c
       right join t_goods g on c.id = g.category_id;

左连接就是左表不动 右连接就是右表不动

全(外)连接

全外连接。不支持

union 合并行操作

基本使用

用于合并2条或者多条语句的结果.

不适用 OR 关键字，计算商品价格大于4000 或者 小于 300 的商品。

select goods_name, goods_price from t_goods where goods_price > 4000
union
select goods_name, goods_price from t_goods where goods_price < 300

如果取出的列名不同，也是可以合并的，并且取出的列名以第一条sql为准。

当两张表查询的列的数量相同时，才可以使用union合并。

Union后的结果集 是可以再排序的：

select goods_name, goods_price from t_goods where goods_price > 4000
union
select goods_name, goods_price from t_goods where goods_price < 300

order by goods_price;

使用order by 的注意事项：

使用Union时，内层的order by 不起作用，这是因为，如果内层 order by 起作用，将会进行三次排序，这是非常消耗性能的操作。因为最后还需要对最终结果集进行排序，所以内层的order by没有意义，mysql的语句分析将内层的order by优化掉了。

当有limit时，order by 将不会被优化。

如果union后的结果有重复,不想去重怎么办？

union all

union面试题

create table a (
  id  char(1),
  num tinyint
);

create table b (
  id  char(1),
  num tinyint
);

insert into a (id, num)
values ('a', 5);
insert into a (id, num)
values ('b', 10);
insert into a (id, num)
values ('c', 15);
insert into a (id, num)
values ('d', 10);
insert into b (id, num)
values ('b', 5);
insert into b (id, num)
values ('c', 15);
insert into b (id, num)
values ('d', 20);
insert into b (id, num)
values ('e', 99);

-- 计算a表和b表相同id的num的和

-- 方式一：
select a.id, ifnull(a.num, 0) + ifnull(b.num, 0)
from a
       left join b on a.id = b.id
order by id;

-- 方式二：

select id, sum(tmp.num)
from ((select * from a)
        union all
      (select * from b)) as tmp
group by id;

group by详解

参考：https://blog.csdn.net/zj20142213/article/details/81073428

先来看下表1，表名为test：

img

表1执行如下SQL语句：

SELECT name FROM test GROUP BY name;

你应该很容易知道运行的结果，如下：

img

可是为了能够更好的理解**“group by”多个列“和”聚合函数“**的应用，我建议在思考的过程中，由表1到表2的过程中，增加一个虚构的中间表：虚拟表3。下面说说如何来思考上面SQL语句执行情况：

1. FROM test：该句执行后，应该结果和表1一样，就是原来的表。

2. FROM test Group BY name：该句执行后，我们想象生成了**虚拟表3，**如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

   

   img

3.接下来就要针对虚拟表3执行Select语句了：

1. 如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以你看，执行select *语句就报错了。

2. 我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

3. 那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用**聚合函数，聚合函数就用来输入多个数据，输出一个数据的。**如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

4. 例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表3的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：

   

   img
5. group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如此图
6. 接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number，结果如下图：

img