SQL语法

1. 排序 (order by)

SELECT * FROM 表名 ORDER BY 排序字段 ASC(升序)|DESC(降序);

• 数据库的查询(排序：order by) 默认是按升序排列的，但是升序ASC最好写上，代码可读性！
• distinct去掉重复 select distinct * from 表名 order by column1,column2,column3 desc;（去重）
• 如果排序列的值相同，通过添加条件，可以再加一个来进行排序

select * from 表名 order by column1,column2,column3 desc;
按照降序查找，首先按照column1列名排序，如果column1的值相同，再按照column2排序，类推

特点：

• 指定列(一个条件一个列，多个条件多个列，排在前面的优先级高)

• 指定升序或者降序

• order by 排序只对数字和英文字符串有效,排序对汉字无效，符合字典顺序(字母、数字顺序)

  排序对汉字无效的解决办法 - > 通过汉字生成拼音，然后按照拼音(字典)顺序

2. 聚合函数

聚合函数(类似于Java中的方法: 函数名())
五个常用的聚合函数

语句执行顺序

SELECT COUNT(*) FROM product WHERE price > 200;
执行顺序：from -> where -> 聚合函数

3. 分组 (group by)(条件只能用having)

SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件; 

• 将数据按照某个规则或者某个特征分成N组（一般不对id进行分组，因为id唯一，分组就只有一个或零个，没有意义）
• 只起到分组的作用，没有统计作用(一般配合聚合函数count来统计结果)
• 如果是对分组之后的结果再进行过滤,则必须使用having 不能使用where

语句执行顺序

from -> group by -> 聚合函数(count) -> having -> select

4. 分页显示(limit [***])

SELECT 字段1，字段2... FROM 表名 LIMIT M,N 

SELECT 字段1，字段2… FROM 表名 LIMIT M,N
M：表示从哪一行的索引(从0开始)开始显示
N：表示要显示几行
第1页： M=N*(1-1) 第2页： M=N*(2-1)
第page页： M=N*(page-1) 开始(page一般会显示在url地址上，方便读取)

数据库分页也是在数据库里写查询语句，不同的是查询的都是指定条数到指定条数的数据，不是一次性把数据全查出来。

多表关系

  在实际的开发中,项目一定是有多张表组成的,这些表之间有着对应关系。
  表与表之间的关联关系主要是：

• 一对一关联 (A表的一行对应B表的一行，反之也成立，此时，两张表可以合并成一张表)
• 一对多关联 (A表的一行对应B表的多行，反之不成立)
  一对多关联，A表的一行对应B表的多行，在B表中要有外键对应A表的主键
• 多对多关联 (A表的一行对应B表的多行,B的一行对应A表的多行)
  多对多关联，要创建中间表，分别对应A、B两表的主键作为外键。

多表查询

1.交叉连接查询

  交叉连接查询是将两张表相乘，就是就笛卡尔集。(A和B —> A * B)
  多表查询可以理解成在笛卡尔集的基础上进行条件筛选

2. 内连接(inner join 条件用on)

  求的是多张表的交集 - 本质就是在笛卡尔集上加条件

两种实现方法：

• 隐式内连接：select * from A,B where 条件;

• 显式内连接：select * from A inner join B on 条件;（效率更高） (因为用了inner join关键字，能够直接识别是内连接查询，所以叫显式内连接)

两者区别：

隐式内连接在笛卡尔集的基础上筛选（需要先求出笛卡尔集）
显式内连接是直接在原始表上筛选，效率更高。

3. 外连接（outer join）

select * from tableA left outer join tableB tableA.id = tableB.id_fk;

  外连接就是两个表中选一个表将数据全部输出，另一个表没有对应数据则输出NULL

有两种外连接方式：

• 左外连接以左表为主,左表中的数据全部输出,右表中如果没有同等的数据则补NULL（left outer join）
• 右外连接以右表为主,右表中的数据全部输出,左表中如果没有同等的数据则补NULL(right outer join)

4. 子查询

子查询就是select语句的嵌套：一个select的查询结果作为另一个select查询语法的一部分

select *from User where cid = (select cid from category where cname='电子')

SQL Server添加字段注释，添加表注释comments

在SQL Server语法中，给表字段与表添加注释与Oracle语法有的很大的区别。

• Oracle语法添加字段注释

语法

COMMENT on column 表.字段 is '字段注释';
COMMENT ON table 表 IS '表注释';

示例

--添加userTest表的userName字段注释为"用户姓名"
COMMENT on column userTest.userName is '用户姓名';
--添加userTest表的表注释为"用户信息表"
COMMENT ON table userTest IS '用户信息表';

--对于删除、更新注释语法一样，直接更改即可。
--删除userTest表的userName字段注释
COMMENT on column userTest.userName is '';
--删除userTest表的表注释
COMMENT ON table userTest IS '';

• Sql Server语法添加字段注释

语法

--添加字段注释
execute sp_addextendedproperty 'MS_Description','填写你的字段注释','user','DB schema','table','填写表名','column','字段名';
--修改字段注释
execute sp_updateextendedproperty 'MS_Description','填写你的字段注释','user','DB schema','table','填写表名','column','字段名';
--删除字段注释
execute sp_dropextendedproperty 'MS_Description','user','DB schema','table','填写表名','column','字段名';

-- 添加表注释
execute sp_addextendedproperty 'MS_Description','填写你的表注释','user','DB schema','table','填写表名',null,null;
-- 修改表注释
execute sp_updateextendedproperty 'MS_Description','填写你的表注释','user','DB schema','table','填写表名',null,null;
-- 删除表注释
execute sp_dropextendedproperty 'MS_Description','user','DB schema','table','填写表名',null,null;

示例

--字段注释处理
--添加dbo下的userTest表的userName字段注释为"用户名"
execute sp_addextendedproperty 'MS_Description','用户名','user','dbo','table','userTest','column','userName';
--修改dbo下的userTest表的userName字段注释为"xxx"
execute sp_updateextendedproperty 'MS_Description','xxx','user','dbo','table','userTest','column','userName';
--删除dbo下的userTest表的userName字段注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','userTest','column','userName';

---表注释处理
-- 添加dbo下的userTest表注释为"用户信息测试表"
execute sp_addextendedproperty 'MS_Description','用户信息测试表','user','dbo','table','userTest',null,null;
-- 修改dbo下的userTest表注释为"xxxxx"
execute sp_updateextendedproperty 'MS_Description','xxxxx','user','dbo','table','userTest',null,null;
-- 删除dbo下的userTest表注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','userTest',null,null;

约束

作用：对表中的数据进行限定，保证数据的正确性、有效性、完整性！

约束分类：

主键约束：

特点：

• 主键约束包含：非空和唯一两个功能
• 一张表只能有一个列作为主键
• 主键一般用于表中数据的唯一标识

语法：

演示：

-- 标准语法
CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY,
    列名 数据类型,
    ...
);

-- 创建学生表，并加主键
CREATE TABLE student(
		id INT PRIMARY KEY,-- id是主键
		NAME VARCHAR(30),
		age TINYINT
);


-- 查询学生表详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES(NULL,'韩信',99); -- 不能为空
INSERT INTO student VALUES(1,'韩信',99);-- 不能重复
INSERT INTO student VALUES（1,'露娜',98);
INSERT INTO student VALUES(2,'李白',97);

-- 删除主键
ALTER TABLE  student DROP PRIMARY KEY;

-- 建表以后单独添加主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;

主键自增：

• Mysql中的自增约束，必须配合键的约束一起用
• 如果删除某一行数据再添加一行，那id就会再删除的id的基础上加1

-- 建表时添加主键自增约束
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30),
	age TINYINT
);

-- 添加数据
INSERT INTO student VALUES (NULL,'韩信',99);
INSERT INTO student VALUES (NULL,'李白',98);

-- 删除主键自增约束
ALTER TABLE student MODIFY id INT;

-- 建表后单独添加主键自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

唯一约束：

唯一约束就是不让值重复，使用关键字UNIQUE

-- 唯一约束  标准语法
CREATE TABLE 表名(
	列名 数据类型 UNIQUE,
    列名 数据类型,
    ...
);

-- 建表时添加唯一约束
CREATE TABLE  student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30),
	age TINYINT UNIQUE
);

-- 添加数据
INSERT INTO student VALUES (NULL,'韩信',99);
INSERT INTO student VALUES (NULL,'李白',99);

-- 删除唯一约束
ALTER TABLE student DROP INDEX age;

-- 建表后添加唯一约束，如果要约束的列已经有重复数据是添加不了的
ALTER TABLE student MODIFY age INT UNIQUE;

非空约束：

-- 非空约束标准语法
CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
    列名 数据类型,
    ...
);

-- 创建表时添加非空约束
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,    -- 给name添加非空约束
	age INT UNIQUE
);

-- 添加数据
INSERT INTO student VALUES (NULL,'韩信',99),(NULL,'李白',98);

-- 删除非空约束
ALTER TABLE student MODIFY name VARCHAR(20);

-- 创建表后单独添加非空约束,如果有已经存在的null值会提醒，但不报错，原来的null值会变空
ALTER  TABLE student MODIFY name VARCHAR(20) NOT NULL;

外键约束：

• 一个表中的某个字段引用其他表的主键，这个字段称为外键
• 主表：主键所在的表,约束别人的表,将数据给别人用
• 副表/从表：外键所在的表,被约束的表,使用别人的数据

作用：

作用就是让表和表之间产生关系，保证数据的准确性

为什么有外键约束：

表和表之间的数据有关联的时候，没有相关的数据约束无法保证数据的准确性！

什么时候用？

表和表之间有关联的时候呗，比如是用户和订单两个表之间

• 当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id
  
  需要达到目的：需要约束dep_id只能是department表中已经存在id
  解决方式：使用外键约束

-- 创建表的时候添加外键约束
-- 格式    CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
--  创建用户和订单表
CREATE TABLE user(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- 姓名
);

-- 创建orderlist订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,                              -- 订单所属用户
	CONSTRAINT ou_wjys FOREIGN KEY (uid) REFERENCES USER(id)
);

-- 添加USER表数据
INSERT INTO USER VALUES (NULL,'韩信'),(NULL,'李白'),(NULL,'露娜');

-- 添加orderlist表数据
INSERT INTO orderlist VALUES
(NULL,'001',1),(NULL,'001',1),
(NULL,'002',2),(NULL,'002',2),
(NULL,'003',3),(NULL,'003',3);


-- 查询数据
SELECT * FROM USER;
SELECT * FROM orderlist;

-- 查询表结构
DESC USER;
DESC orderlist;

-- 删除外键
ALTER TABLE orderlist DROP FOREIGN KEY ou_wjys;

-- 创建表后单独添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_wjys FOREIGN KEY (uid) REFERENCES USER(id);

外键级联更新和级联删除：

什么是级联更新和级联删除

把user用户表中的某个用户删，该用户所有的订单也随之被删除
把user用户表中的某个用户id修改，订单表中该用户所属的订单用户编号也随之修改

语法格式：

级联更新：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE
级联更新和级联删除：ON UPDATE CASCADE ON DELETE CASCADE

-- 添加级联更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
-- 添加级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
-- 添加级联更新和级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

演示：

-- 添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_wjys FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE; 

-- 将韩信这个用户的id改为9
UPDATE USER SET id = 9 WHERE id = 1 ;

-- 将韩信这个用户删除
DELETE FROM USER  WHERE id = 9;