Mysql一：基础学习

一、DDL语句：数据库操作：database

1、创建数据库:

create database 数据库名;
create database 数据库名 character set 字符集;

注意：
1、mysql创建数据库时指定编码很重要，指定数据库的编码，可以很大程度上避免导入带来的乱码问题。
2、我们遵循的标准是：数据库、表、字段、页面或文本的编码要统一起来。
3、创建数据库要指定：数据库编码、数据库排序规则。

创建数据库实例：

create database liupy3 character set utf8;

 

2、查看数据库:

查看数据库服务器中的所有的数据库:

show databases;

查看某个数据库的定义的信息:

show create database 数据库名;

 

3、删除数据库（慎用）：

drop database 数据库名称;

 

4、其他数据库操作命令：

切换数据库：

use 数据库名;

查看正在使用的数据库:

select database();

 
 

二、表操作：

1、字段类型：

• 常用的类型有：

数字型：int
浮点型：double
字符型：varchar（可变长字符串）
日期类型：date（只有年月日，没有时分秒）
        datetime（年月日，时分秒）
boolean类型：不支持，一般使用tinyint替代（值为0和1）

• 字段类型详解：
  
  
  
   

2、创建表：

2.1、创建表sql语句：

create table 表名(
 字段名 类型(长度) 约束,
 字段名 类型(长度) 约束
);

单表约束：
 - 主键约束：primary key
 - 唯一约束：unique
 - 非空约束：not null
 - 
注意：
主键约束 = 唯一约束 + 非空约束

 
2.2、常见的表的约束：

 

• 主键约束（唯一、非空）：
  在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约束是通过PRIMARY KEY定义的，它可以唯一标识表中的记录，这就好比身份证可以用来标识人的身份一样。在MySQL中，主键约束分两种，具体如下：

1. 单字段主键：
   单字段主键指的是由一个字段构成的主键，其基本语法如下：
   
   上述案例中，表student创建了id、name和grade三个字段，其中id字段是主键。

2. 多字段主键：
   多字段主键指的是多个字段组合而成的主键，一般中间表采用这种方式，其基本的语法格式如下所示：
   
   上述案例中，表student创建了id、name和grade三个字段，其中id和name两个字段组合可以唯一确定一条记录。

【补充】一个表最多可以创建一个主键。

 

• 外键约束：
  如果表A的主键是表B中的字段，则该字段称为表B的外键；另外表A称为主表，表B称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或删除的级联操作将使得日常维护更轻松。外键主要用来保证数据的完整性和一致性。其基本语法如下：
  

【补充】：
1、一般很少用外键，而是采用建立中间表的方式管理多个表；
2、一个表允许有多个外键，且外键只适用于InnoDB表，MyISAM表不支持外键。InnoDB表和
MyISAM表的主要区别是：InnoDB表支持事务、行锁和外键，MyISAM不支持；InnoDB表索引式修改效率较
MyISAM高，MyISAM表查询、插入和删除效率较InnoDB高，而且MyISAM索引和数据分离，索引压缩存储，
而InnoDB索引和数据紧密结合，不支持压缩，所以MyISAM内存利用率比InnoDB高；另外MyISAM的移植性
也更强。

 

• 非空约束：
  非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOT NULL定义的，其基本语法
  如下：
  
  上述案例中，表student包含id、name和grade三个字段。其中id为主键，name为非空字段。
  【补充】一个表可以有多个非空约束字段。
   
• 唯一约束：
  唯一约束用于保存数据表中字段的唯一性，类似于主键，即表中字段值不能重复出现。唯一约束
  是通过UNIQUE定义的，其基本语法如下：
  上述案例中，表student包含id、name和grade三个字段。其中id为主键，name字段为唯一值，不能重复。

【补充】一个表可以有多个唯一约束字段。

 

3、查看表：

查看数据库中的所有表：

show tables;

查看表结构：

desc 表名;

 

4、删除表：

drop table 表名;

 

5、修改表：

alter table 表名 add 列名 类型(长度) 约束;   --修改表添加列.

alter table 表名 modify 列名 类型(长度) 约束;  --修改表修改列的类型长度及约束.

alter table 表名 change 旧列名 新列名 类型(长度) 约束;  --修改表修改列名.

alter table 表名 drop 列名;          --修改表删除列.

rename table 表名 to 新表名;         --修改表名

alter table 表名 character set 字符集;    --修改表的字符集

 
 

三、DML语句：

1、插入记录 insert：

 

• 语法：

insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..); -- 向表中插入某些列

insert into 表 values (值1,值2,值3..); --向表中插入所有列

insert into 表 (列名1,列名2,列名3..) values select (列名1,列名2,列名3..) from 表

insert into 表 values select * from 表

 

• 注意：

1. 列名数与values后面的值的个数相等
2. 列的顺序与插入的值得顺序一致
3. 列名的类型与插入的值要一致.
4. 插入值得时候不能超过最大长度.
5. 值如果是字符串或者日期需要加引号’’ （一般是单引号）
6. 如果前面不写列名，values后面的值必须和表结构保持一致，是需要每个字段都赋值的；
    

• 例如：

INSERT INTO sort(sid,sname) VALUES('s001', '电器');

INSERT INTO sort(sid,sname) VALUES('s002', '服饰');

INSERT INTO sort VALUES('s003', '化妆品');

INSERT INTO sort VALUES('s004','书籍');

 

2、更新记录 update：

• 语法：

update 表名 set 字段名=值,字段名=值;

update 表名 set 字段名=值,字段名=值 where 条件;

• 注意：

1. 列名的类型与修改的值要一致.
2. 修改值得时候不能超过最大长度.
3. 值如果是字符串或者日期需要加’’.

 

3、删除记录delete：

• 语法：

delete from 表名 [where 条件];

• delete和truncate区别：

1、删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？

2、删除方式：
- delete ：一条一条删除，不清空auto_increment记录数。
- delete：会产生碎片，并不会释放空间。
- 
- truncate ：直接将表删除，重新建表，auto_increment将置为零，从新开始。
- truncate ：不会产生碎片，释放空间。
- 

 
 

四、DQL语句：

1、准备工作:

创建商品表：

案例演示：
#商品表
CREATE TABLE product (
	pid INT PRIMARY KEY AUTO_INCREMENT, # 自增加 AUTO_INCREMENT
	pname VARCHAR(20),#商品名称
	price DOUBLE, #商品价格
	pdate DATE, # 日期
	cid int  #分类ID
);

#目录表
create table category(
	 id INT PRIMARY KEY ,
	 cname varchar(100)
);

INSERT INTO product VALUES(NULL,'泰国大榴莲', 98, NULL, 1);
INSERT INTO product VALUES(NULL,'泰国大枣', 38, NULL, 1);
INSERT INTO product VALUES(NULL,'新疆切糕', 68, NULL, 2);
INSERT INTO product VALUES(NULL,'十三香', 10, NULL, 2);
INSERT INTO product VALUES(NULL,'泰国大枣', 20, NULL, 2);
insert into product values(null,'泰国大枣',98,null,20); #没有对应
insert into product values(null,'iPhone手机',800,null,30);#没有对应

INSERT INTO category VALUES(1,'国外食品');
INSERT INTO category VALUES(2,'国内食品');
INSERT INTO category VALUES(3,'国内服装'); #没有对应

 

2、简单查询：

1. SQL语法关键字：

SELECT
FROM

2. 案例：

1. 查询所有的商品.
	select * from product;

2. 查询商品名和商品价格.
	select pname,price from product;

3. 别名查询，使用的as关键字，as可以省略的.
	表别名：
	select * from product as p;

	列别名：
	select pname as pn from product; 

4. 去掉重复值.
	select distinct price from product;
	
5. 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
	select pname,price+10 from product;

 

3、条件查询：

1. SQL语法关键字：

WHERE

2. 案例：

1. 查询商品名称为十三香的商品所有信息：
	select * from product where pname = '十三香';
	
2. 查询商品价格>60元的所有的商品信息:
	select * from product where price > 60;
	

6. where后的条件写法：

1、	> ,<,=,>=,<=,<>

2、 like 使用占位符 _ 和 %, _代表一个字符 %代表任意个字符.
  select * from product where pname like '%新%';
  
3、  in在某个范围中获得值（exists）.
  select * from product where pid in (2,5,8);
  

 

4、排序：

1、SQL语法关键字：

ORDER BY

ASC（升序） DESC（降序）

2、案例：

1. 查询所有的商品，按价格进行排序.(asc-升序,desc-降序)
	select * from product order by price;
	
2. 查询名称有新的商品的信息并且按价格降序排序.
	select * from product where pname like '%新%' order by price desc;
	

 

5、聚合函数（组函数）：

聚合函数：对一组值执行计算，并返回单个值，也被称为组函数，经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。

1、特点：只对单列进行操作。

2、常用的聚合函数：

	sum()：求某一列的和
	avg()：求某一列的平均值
	max()：求某一列的最大值
	min()：求某一列的最小值
	count()：求某一列的元素个数

3、案例：

1. 获得所有商品的价格的总和：
	select sum(price) from product;
2. 获得所有商品的平均价格：
	select avg(price) from product;
3. 获得所有商品的个数：
	select count(*) from product;
	

 

6、分组：

1、SQL语法关键字：

	GROUP BY
	
	HAVING

2、案例：

1. 根据cno字段分组，分组后统计商品的个数.
	select cid,count(*) from product group by cid;
	
2. 根据cno分组，分组统计每组商品的平均价格，并且平均价格> 60;
	select cid,avg(price) from product group by cid having avg(price)>60;
	

3、注意事项：

1. select语句中的列（非聚合函数列），必须出现在group by子句中；
2. group by子句中的列，不一定要出现在select语句中；
3. 没有出现在group by中的字段，如果在select中使用，则这些字段必须使用聚合函数，否则报错。
4. 聚合函数只能出现select语句中或者having语句中，一定不能出现在where语句中。

 

7、分页查询：

1、关键字：

lIMIT [offset,] rows

1、lIMIT 关键字不是 SQL92 标准提出的关键字，它是 MySQL 独有的语法。
2、通过 limit 关键字， MySQL 实现了物理分页。

 
2、分页分为逻辑分页和物理分页：

1、逻辑分页：将数据库中的数据查询到内存之后再进行分页。

2、物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，
只是分页后的数据。

 
3、格式：

SELECT * FROM table LIMIT [offset,] rows

offset ：偏移量
rows ：每页多少行记录。

 

8、子查询：

1、定义：

1、子查询允许把一个查询嵌套在另一个查询当中。
2、子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。
3、子查询可以包含普通select，可以包括的任何子句，比如：distinct、 group by、
	order by、limit、join和union等；
3、但是对应的外部查询必须是以下语句之一：select、insert、update、delete。

2、位置：

1、select中、from 后、where 中.
2、group by 和order by 中无实用意义。

 

9、其他查询语句：

• union 集合的并集（不包含重复记录）
• unionall 集合的并集（包含重复记录）

 
 

五、SQL解析顺序：

1、SQL书写顺序：

接下来再走一步，让我们看看一条SQL语句的前世今生。
首先看一下示例语句：

	SELECT DISTINCT
	 	< select_list >
	FROM
	 	< left_table > < join_type >
	JOIN < right_table > ON < join_condition >
	WHERE
	 	< where_condition >
	GROUP BY
	 	< group_by_list >
	HAVING
	 	< having_condition >
	ORDER BY
	 	< order_by_condition >
	LIMIT < limit_number >

然而它的执行顺序是这样的：

 

2、SQL的执行顺序解析：

 
1、FROM：


 
2、ON过滤：

 
3、OUTER JOIN添加外部列（外连接）

 
4、WHERE:

 
5、GROUP BY：

 
6、HAVING：

 
where 和having的区别：：

1、where是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束、过滤，再返回结果前起作用，并且where后不能使用“聚合函数”。
2、聚合函数：对一组值执行计算，并返回单个值，也被称为组函数，经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。例如
AVG 返回指定组中的平均值COUNT 返回指定组中项目的数量MAX 返回指定数据的最大值。MIN 返回指定数据的最小值。SUM 返回指定数据的和，只能用于数字列，空值被忽略。
3、having：having是一个过滤声明，是在查询数据库结果返回之后进行过滤，即在结果返回值后起作用，与聚合函数共同使用。
4、使用group by进行过滤，则只能使用having。执行顺序：where>聚合函数(sum,min,max,avg,count)>having，故where不使用聚合函数。

 
7、SELECT:

这个子句对SELECT子句中的元素进行处理，生成VT5表。
(5-J1)计算表达式 计算SELECT 子句中的表达式，生成VT5-J1

 
8、DISTINCT:

 
9、ORDER BY：

 
10、LIMIT（MySQL特有）：

 

3、解析顺序总结：

流程分析：

 -  FROM（将最近的两张表，进行笛卡尔积）---VT1
 -  ON（将VT1按照它的条件进行过滤）---VT2
 -  LEFT JOIN（保留左表的记录）---VT3
 -  WHERE（过滤VT3中的记录）--VT4…VTn
 -  GROUP BY（对VT4的记录进行分组）---VT5
 -  HAVING（对VT5中的记录进行过滤）---VT6
 -  SELECT（对VT6中的记录，选取指定的列）--VT7
 -  ORDER BY（对VT7的记录进行排序）--VT8
 -  LIMIT（对排序之后的值进行分页）--MySQL特有的语法

 
流程说明：

• 单表查询：
  根据 WHERE 条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT 的选择列选择相应的列进行返回最终结果。

• 两表连接查询：
  对两表求积（笛卡尔积）并用 ON 条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据 SELECT 指定的列返回查询结果。

• 多表连接查询：
  先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

 

4、WHERE条件解析顺序：

1. MySQL ：从左往右去执行 WHERE 条件的。
2. Oracle ：从右往左去执行 WHERE 条件的。

 
 

六、多表之间的关系：

1、表与表之间的关系

表与表之间的关系，说的就是表与表之间数据的关系。主要分为：

1. 一对一关系
   常见实例：一夫一妻
2. 一对多关系
   常见实例：会员和订单
3. 多对多关系（需要中间表实现）
   常见实例：商品和订单
    

2、外键：

如何表示表与表之间的关系呢？就是使用外键约束表示的。
要想理解外键，我们先去理解表的角色：主表和从表（需要建立关系才有了主从表的角色区分）：

4. 主从表的理解：
   
    
5. 主键外键的理解：
   
    
6. 如何操作外键：
   

 

7. 使用外键目的：

保证数据完整性（数据保存在多张表中的时候）
在互联网项目中，一般情况下，不建议建立外键关系。

 

3、一对一关系（了解）：

在实际工作中，一对一在开发中应用不多，因为一对一完全可以创建成一张表

案例：一个丈夫只能有一个妻子

• 建表语句：

CREATE TABLE wife(
 id  INT PRIMARY KEY ,
 wname  VARCHAR(20),
 sex CHAR(1)
);
CREATE TABLE husband(
 id  INT PRIMARY KEY ,
 hname  VARCHAR(20),
 sex CHAR(1)
);

• 一对一关系创建方式1之外键唯一：
  添加外键列wid，指定该列的约束为唯一（不加唯一约束就是一对多关系）

	ALTER TABLE husband ADD wid INT  UNIQUE;  

• 添加外键约束：

alter table husband add foreign key (wid) references wife(id);

• 一对一关系创建方式2之主键做外键：（课后作业）：
  思路：使用主表的主键作为外键去关联从表的主键

 

4、一对多关系：

• 案例：一个分类对应多个商品。
• 总结：有外键的就是多的一方。、
• 注意事项：
  一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一。
• 一对多关系创建：
  添加外键列
  添加外键约束

 

5、多对多关系：

1、案例：

 
2、多对多关系创建：

创建中间表，并在其中创建多对多关系中两张表的外键列
在中间表中添加外键约束
在中间表中添加联合主键约束
用户和角色
1个用户对多个角色
1个角色对多个用户
中间表用户角色表 uid rid

 
 

七、多表关联查询：

mySQL查询包含两种联接查询，分别是内连接(inner join)和外连接(out join)。基本的连接查询的方式如下几种：

• CROSS JOIN （交叉连接）
• INNER JOIN （内连接或等值连接）。
• OUTER JOIN （外连接）
   

1、内连接：

 
注意：内连接的一个重要性质：内连接查询结果与表的顺序无关。
 

1.1、交叉连接（很少用）：

当然，他还有其他的名字，比如：笛卡尔积，交叉积，还有最奇怪的名字“没有连接”（no join）。

关键字：

CROSS JOIN

交叉连接也叫笛卡尔积连接。笛卡尔积是指在数学中，两个集合 X 和 Y 的笛卡尓积（ Cartesian product ），又称直积，表示为 X*Y ，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。

交叉连接的查询结果表现：

行数相乘、列数相加。

 

• 隐式交叉连接：

SELECT *  FROM  A, B;

 

• 显示交叉连接：

SELECT * FROM A CROSS JOIN B;

 

1.2、相等连接：

关键字：

INNER JOIN

等值接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

• 隐式内连接：

SELECT *  FROM A,B  WHERE A.id = B.id;

 

• 显式内连接：

SELECT * FROM A INNER JOIN B ON A.id = B.id;

 

2、外连接：

注意：

1. 外连接不同于内连接的一个性质：外连接查询与表的顺序有关。
2. 外连接需要有主表或者保留表的概念。
3. 外连接又分为： 左外连接、右外连接、全外连接。
    

2.1、左外连接：

LEFT OUTER JOIN（左外连接）接收左表的所有行，并用这些行与右表进行匹配。
当左表与右表具有一对多的关系时，左外连接特别有用。

LEFT JOIN 或者 LEFT OUTER JOIN

SELECT *  FROM A LEFT  JOIN B ON A.id = B.id;

LEFT OUTER JOIN左边的表product我们称为左表（主表），右边的category称为右表，所以LEFT OUTER JOIN会取得左表product的所有行和右表的category的行进行匹配，行数等于左表的行数，与右表无关；右表如果能比配上，则字段有值，没有匹配上，字段的值为null。

 

2.2、右外连接（少用）：

注意：

1. 与左外连接完全相同，只不过是主表不一样。右外连接，右边的表为主表；
2. 查询的行数=右表的行数。

RIGHT JOIN 或者 RIGHT OUTER JOIN
SELECT *  FROM A RIGHT  JOIN B ON A.id = B.id;

 

2.3、全外连接（MySQL不支持）：