SQL语言学习小结（二）

BETWEEN关键字：

        如果我们在where条件语句中，需要获取日期，数字甚至文本的两个值之间的数据，那么我们可以使用如下语句：

        SELECT 列 FROM 表名 WHERE 条件列 BETWEEN 值1 AND 值2;

<span style="font-family:Microsoft YaHei;">select * from student where birth between '1990-01-01' and '1993-01-01';+-----+------+-----+------------+------------+--------------+| id  | name | sex | birth      | department | address      |+-----+------+-----+------------+------------+--------------+| 903 | 张三 | 女  | 1990-02-27 | 中文系     | 湖南省永州市 || 904 | 李四 | 男  | 1990-11-09 | 英语系     | 辽宁省阜新市 || 905 | 王五 | 女  | 1991-01-19 | 英语系     | 福建省厦门市 || 907 | 赵七 | 男  | 1992-09-09 | 政治系     | 四川省成都市 |+-----+------+-----+------------+------------+--------------+</span>

        如果需要的是值1与值2以外的，我们可以在BETWEEN前加上NOT：

        SELECT 列 FROM 表名 WHERE 条件列 NOT BETWEEN 值1 AND 值2;

<span style="font-family:Microsoft YaHei;">select * from student where birth not between '1990-01-01' and '1993-01-01';+-----+--------+-----+------------+------------+--------------+| id  | name   | sex | birth      | department | address      |+-----+--------+-----+------------+------------+--------------+| 901 | 张老大 | 男  | 1985-07-22 | 计算机     | 北京市海淀区 || 902 | 李二狗 | 男  | 1986-01-11 | 中文系     | 北京市昌平区 || 906 | 王六   | 男  | 1988-06-23 | 计算机     | 湖南省衡阳市 |+-----+--------+-----+------------+------------+--------------+</span>

        PS：这里大家要注意一点的就是between...and...中的边际值，在不同的数据库环境中有不同的处理。MySQL在BETWEEN...AND中包含边界值，而NOT语句中不包含。

Alias（别名）：

         所谓别名，就是为我们指定的表或列设定一个方便阅读的名字。

        使用格式：表名 AS 表的别名，列名 AS 列的别名。

        SELECT  表1的别名.列1 AS 列1的别名, 表2的别名.列3 AS 列3的别名 FROM 表1 AS 表1的别名,表2 AS 表2的别名;

<span style="font-family:Microsoft YaHei;">mysql> select st.id as '学号', st.name as '姓名', sc.course as '课程', sc.grade as '分数'    -> from student as st, score as sc where st.id = sc.s_id;+------+--------+--------+------+| 学号 | 姓名   | 课程   | 分数 |+------+--------+--------+------+|  901 | 张老大 | 计算机 |   98 ||  901 | 张老大 | 英语   |   80 ||  902 | 李二狗 | 计算机 |   65 ||  902 | 李二狗 | 中文   |   88 ||  903 | 张三   | 中文   |   95 ||  904 | 李四   | 计算机 |   70 ||  904 | 李四   | 英语   |   92 ||  905 | 王五   | 英语   |   94 ||  906 | 王六   | 计算机 |   90 ||  906 | 王六   | 英语   |   85 |+------+--------+--------+------+</span>

JOIN关键字：

        [ INNER ] JOIN：左右表都匹配就返回。

        SELECT * FROM 表1 [INNER] JOIN  表2 ON 表1.id = 表2.id ;

<span style="font-family:Microsoft YaHei;">mysql> select * from student join score on student.id = score.s_id;+-----+--------+-----+------------+------------+--------------+------+--------+-------+| id  | name   | sex | birth      | department | address      | s_id | course | grade |+-----+--------+-----+------------+------------+--------------+------+--------+-------+| 901 | 张老大 | 男  | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 计算机 |    98 || 901 | 张老大 | 男  | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 英语   |    80 || 902 | 李二狗 | 男  | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 计算机 |    65 || 902 | 李二狗 | 男  | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 中文   |    88 || 903 | 张三   | 女  | 1990-02-27 | 中文系     | 湖南省永州市 |  903 | 中文   |    95 || 904 | 李四   | 男  | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 计算机 |    70 || 904 | 李四   | 男  | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 英语   |    92 || 905 | 王五   | 女  | 1991-01-19 | 英语系     | 福建省厦门市 |  905 | 英语   |    94 || 906 | 王六   | 男  | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 计算机 |    90 || 906 | 王六   | 男  | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 英语   |    85 |+-----+--------+-----+------------+------------+--------------+------+--------+-------+</span>

         LEFT JOIN：左表所有数据都要返回到结果集中。如果右表无法匹配的左表的数据，右表用NULL进行配对。

         SELECT * FROM 左表 LEFT JOIN  右表 ON 左表.id = 右表.id ;

<span style="font-family:Microsoft YaHei;">mysql> select * from student left join score on student.id = score.s_id;+-----+--------+-----+------------+------------+--------------+------+--------+-------+| id  | name   | sex | birth      | department | address      | s_id | course | grade |+-----+--------+-----+------------+------------+--------------+------+--------+-------+| 900 | 孙七   | 男  | 1989-09-12 | 计算机     | 山东省淄博市 | NULL | NULL   | NULL  || 901 | 张老大 | 男  | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 计算机 |    98 || 901 | 张老大 | 男  | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 英语   |    80 || 902 | 李二狗 | 男  | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 计算机 |    65 || 902 | 李二狗 | 男  | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 中文   |    88 || 903 | 张三   | 女  | 1990-02-27 | 中文系     | 湖南省永州市 |  903 | 中文   |    95 || 904 | 李四   | 男  | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 计算机 |    70 || 904 | 李四   | 男  | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 英语   |    92 || 905 | 王五   | 女  | 1991-01-19 | 英语系     | 福建省厦门市 |  905 | 英语   |    94 || 906 | 王六   | 男  | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 计算机 |    90 || 906 | 王六   | 男  | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 英语   |    85 || 907 | 赵七   | 男  | 1992-09-09 | 政治系     | 四川省成都市 | NULL | NULL   | NULL  |+-----+--------+-----+------------+------------+--------------+------+--------+-------+</span>

         RIGHT JOIN：右表所有行的数据都要返回，不管是否匹配。右表中无法再左表匹配的数据，左表用NULL填充。

         SELECT * FROM 左表 RIGHT JOIN  右表 ON 左表.id = 右表.id ;

<span style="font-family:Microsoft YaHei;">mysql> select * from student right join score on student.id = score.s_id;+------+--------+------+------------+------------+--------------+------+--------+-------+| id   | name   | sex  | birth      | department | address      | s_id | course | grade |+------+--------+------+------------+------------+--------------+------+--------+-------+|  901 | 张老大 | 男   | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 计算机 |    98 ||  901 | 张老大 | 男   | 1985-07-22 | 计算机     | 北京市海淀区 |  901 | 英语   |    80 ||  902 | 李二狗 | 男   | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 计算机 |    65 ||  902 | 李二狗 | 男   | 1986-01-11 | 中文系     | 北京市昌平区 |  902 | 中文   |    88 ||  903 | 张三   | 女   | 1990-02-27 | 中文系     | 湖南省永州市 |  903 | 中文   |    95 ||  904 | 李四   | 男   | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 计算机 |    70 ||  904 | 李四   | 男   | 1990-11-09 | 英语系     | 辽宁省阜新市 |  904 | 英语   |    92 ||  905 | 王五   | 女   | 1991-01-19 | 英语系     | 福建省厦门市 |  905 | 英语   |    94 ||  906 | 王六   | 男   | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 计算机 |    90 ||  906 | 王六   | 男   | 1988-06-23 | 计算机     | 湖南省衡阳市 |  906 | 英语   |    85 || NULL | NULL   | NULL | NULL       | NULL       | NULL         |  910 | 中文   |    88 |+------+--------+------+------------+------------+--------------+------+--------+-------+</span>

         FULL JOIN：只要其中一个表中存在匹配，就返回行。不过只有MySQL5.1以上才能使用这个全联接功能。

         SELECT * FROM 表1 FULL JOIN  表2 ON 表1.id = 表2.id ;

UNION关键字：

        UNION关键字，联合，用于合并两个或多个SELECT语句的结果集。

         格式：(SELECT 列 FROM 表) UNION [ALL] (SELECT 列 FROM 表) 

        要求：    

        1.两次查询子句返回的结果集的列数必须一样，类型相似。

        2.union会自动去掉重复行，不想去掉就在UNION后面加上ALL。

        3.如果查询字句中没有order by，limit，可以不使用（）。

        4.在联合语句的查询子句中如果要使用ORDER BY进行排序，那么必须跟上LIMIT限定，否则ORDER BY无效。

        5.返回结果集的字段名，是用第一个查询语句的字段名来命名。

        6.用于将两个查询逻辑完全相反的结果集合并到一起。

        PS：此处说‘两’的地方都可以换为‘多’。

<span style="font-family:Microsoft YaHei;">mysql> (select id from student order by id limit 4)    -> union    -> (select id from student order by id desc limit 4);+-----+| id  |+-----+| 900 || 901 || 902 || 903 || 907 || 906 || 905 || 904 |+-----+</span>

SQL约束：

        约束用于限制介入表的数据的类型。

        我们可以在建表时设定约束，或者使用ALTER TABLE语句修改。

        我们将主要了解一下几种约束：

        1.NOT NULL  （非空）：

                NOT NULL约束该字段不能接受NULL值，也就是说如果不向此字段加入数据值，就无法插入新的数据。

                CREATE TABLE 表名(字段名  字段类型 NOT NULL) ;

                ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 新字段类型 NOT NULL;

                ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL;

        2.UNIQUE（唯一性）：

                 UNIQUE约束,用来标识这个字段的数据的唯一性。说白了就是防止重复。

<span style="font-family:Microsoft YaHei;">CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),UNIQUE (Id_P));</span>

                当表已存在时（单列约束名就为列名）：

ALTER TABLE Persons ADD UNIQUE (P_Id);

                当表已存在时（多列约束名得自己定义）：如果我们是为多列设定了一个UNIQUE约束，那么需要这多列全部重复，才会生效。

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

                撤销UNIQUE约束：

ALTER TABLE Persons DROP INDEX uc_PersonID;

        3.PRIMARY KEY（主键）：

                主键，用于约束唯一标识数据库表中的每条记录。

                要求：

                1.主键必须包含唯一的值（不能重复）。

                2.主键不能包含NULL值（不能不填（第一次可以不填，会自动补默认值））。

                3.每个表只能有一个主键。

                单列主键：

<span style="font-family:Microsoft YaHei;">CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (Id_P));</span>

                多列主键：

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName));

                当表已存在时（单列）：

ALTER TABLE Persons ADD PRIMARY KEY (Id_P);

                当表已存在时（多列）：

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName);

                撤销 PRIMARY KEY 约束：

ALTER TABLE Persons DROP PRIMARY KEY;

        4.FOREIGN KEY（外键）：

                外键用来预防破坏表之间连接的动作。也可以防止非法数据的插入因为外键必须是他指向的那个表的值之一。

                单列外键：

CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,Id_P int,PRIMARY KEY (O_Id),FOREIGN KEY (Id_P) REFERENCES Persons(Id_P));

                多列外键：

CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,Id_P int,PRIMARY KEY (O_Id),CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P));

                当表已存在时（单列）：

ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);

                当表已存在时（多列）：

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);

                撤销外键：

ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;

        5.CHECK（值限定）：

                设定列的值的范围。

                添加的方法：

                        CREATE TABLE 表名 (字段名 字段类型, ... ,CHECK (字段名0>=值)) ;

                追加的方法：

                        单列：ALTER TABLE 表名 ADD CHECK(列>=值);

                        多列：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(列>=值 AND 列<=值);

                删除约束的方法：

                        ALTER TABLE 表名 DROP CHECK 约束名 ;

        6.DEFAULT（默认值）：

                设定的默认值。

                1.我们MySQL会给这些列自动加一个默认值为NULL的默认值。

                2.我们如果追加默认值会覆盖掉前面设定的默认值。

                3.如果删除默认值，他是不会自动回到default NULL。而是没有了默认值，没有默认值那么我们插入数据的时候就一定需要插入这一列的数据，这个数据也可以是NULL。

                添加的方法：

                        CREATE TABLE 表名 (字段名 字段类型 DEFAULT 默认值,字段名 字段类型...);

                追加的方法：

                        ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值 ;

                删除约束的方法：

                        ALTER TABLE 表名 ALTER 列名 DROP DEFAULT  ;

VIEW视图：

        约束用于限制介入表的数据的类型。

        1.暴露部分数据给外部。

        2.视图主要用于读取数据，因为插入数据要受到原来的表的限定。

        3.视图实质上并不是一张表，尽管看起来一模一样，但是数据实质上市存在原来的表中的。

        4.简化我们某些较为复杂的业务逻辑。

        创建视图：

                CREATE VIEW 视图名  AS  SELECT STATEMENT(查询语句);

<span style="font-family:Microsoft YaHei;">mysql> create view v_sst as select st.id as '学号', st.name as '学生姓名', sc.course as '课程', sc.grade as '分数', te.name as '教师姓名' from student as st join score as sc on st.id = sc.s_id join teacher as te on sc.course = te.course;</span>

<span style="font-family:Microsoft YaHei;">mysql> select * from v_sst;+------+----------+--------+------+----------+| 学号 | 学生姓名 | 课程   | 分数 | 教师姓名 |+------+----------+--------+------+----------+|  901 | 张老大   | 计算机 |   98 | 诸葛亮   ||  901 | 张老大   | 英语   |   80 | 李白     ||  902 | 李二狗   | 计算机 |   65 | 诸葛亮   ||  902 | 李二狗   | 中文   |   88 | 孔子     ||  903 | 张三     | 中文   |   95 | 孔子     ||  904 | 李四     | 计算机 |   70 | 诸葛亮   ||  904 | 李四     | 英语   |   92 | 李白     ||  905 | 王五     | 英语   |   94 | 李白     ||  906 | 王六     | 计算机 |   90 | 诸葛亮   ||  906 | 王六     | 英语   |   85 | 李白     |+------+----------+--------+------+----------+</span>

        修改视图：(和我们创建时的语句是一样的)

                CREATE VIEW 视图名  AS  SELECT STATEMENT(查询语句);

        删除视图：

                DROP VIEW 视图名;

DROP VIEW v_sst;