MySQL (2)

BETWEEN关键字

如果我们在WHERE条件语句中，需要获取日期，数字甚至文本的两个值之间的数据，那么我们可以使用如下语句:(例子都使用SELECT语句)

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

这样将查询出条件列中值在值1与值2之间的所有数据的指定列的值。

例：

<span style="font-size:12px;">SELECT name FROM students WHERE id BETWEEN 2 AND 5;</span>

在表students中查询id在2到5之间的所有的name数据。（包含边界值2和5）

注：不同的数据库对BETWEEN...AND操作符的处理方式是由差异的。有些数据库是包含边界值，而有些数据库不包含。

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

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

SELECT name FROM students WHERE id NOT BETWEEN 2 AND 5;

在表students中查询id除了2到5之间的所有的name数据。（也排除边界值2和5）

SQL Alias(别名)

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

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

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

例：SELECT st1.name AS st1_name, st2.name AS st2_name FROM student1 AS st1, student2 AS st2;

给表student1添加了别名为st1，表student2添加了别名st2，所有在SELECT 后直接用的st1、st2来分别代替students1和students2，

另外又给表students1下的name添加了别名st1_name，可以在结果集中清楚的看到这一列是来自student1中的name,st2_name类似。

SQL JOIN

通常我们一个软件的数据库中，不会只有一张表，我们要处理繁多的零散的数据，我们将数据保存于数据库中，我们使用表将数据们有序地存在一起，

不过一张表显然是不够我们使用的。正常情况下，我们的软件的数据库将维护很多张表，而表与表之间又有这一定联系，这个时候我们将会在获取数据时，

同时访问多张表，并将数据返回到一个结果集上。SQL JOIN 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

不同的SQL JOIN 

1)（INNER）JOIN : 如果表中有至少一个匹配，则返回行(左右表都匹配就返回)，INNER可省略。

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

例：

SELECT st.id, st.name, sc.course, sc.grade FROM students as st [INNERE] JOIN score as sc ON st.id = sc.id;

+-----+--------+--------+-------+| id  | name   | course | grade |+-----+--------+--------+-------+| 901 | 张老大 | 计算机 |    98 || 901 | 张老大 | 英语   |    80 || 902 | 李二狗 | 计算机 |    65 || 902 | 李二狗 | 中文   |    88 || 903 | 张三   | 中文   |    95 || 904 | 李四   | 计算机 |    70 || 904 | 李四   | 英语   |    92 || 905 | 王五   | 英语   |    94 || 906 | 王六   | 计算机 |    90 || 906 | 王六   | 英语   |    85 || 907 | 张琪   | 英语   |    99 |+-----+--------+--------+-------+

查询表students和score中id相匹配的students中的id和name,scores中的course和grade数据。

2）LEFT JOIN:即使右表中没有匹配，也从左表返回所有的行。（左表条件中所有数据都要返回到结果集中，如果右表无法匹配左表的的数据，右表用NULL进行配对）。

格式:SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.id = 表2.id ;

例：

<pre name="code" class="sql">SELECT st.id, st.name, sc.course, sc.grade FROM students as st LEFT JOIN score as sc ON st.id = sc.id;

+-----+--------+--------+-------+| id  | name   | course | grade |+-----+--------+--------+-------+| 901 | 张老大 | 计算机 |    98 || 901 | 张老大 | 英语   |    80 || 902 | 李二狗 | 计算机 |    65 || 902 | 李二狗 | 中文   |    88 || 903 | 张三   | 中文   |    95 || 904 | 李四   | 计算机 |    70 || 904 | 李四   | 英语   |    92 || 905 | 王五   | 英语   |    94 || 906 | 王六   | 计算机 |    90 || 906 | 王六   | 英语   |    85 || 907 | 张琪   | 英语   |    99 || 908 | 王萍   | NULL   |  NULL |+-----+--------+--------+-------+

查询表students和score中id相匹配的students中的id和name,scores中的course和grade数据,右表无法匹配左表的的数据，右表用NULL进行配对。

3)RIGHT JOIN:即使左表中没有匹配，也从右表返回所有行。（右表所有行数据都要返回，不管是否匹配，右表无法与左表匹配的数据,左表用NULL填充）。

格式:SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.id = 表2.id ;

例:

SELECT st.id, st.name, sc.course, sc.grade FROM students as st LEFT JOIN score as sc ON st.id = sc.id;

+------+--------+--------+-------+| id   | name   | course | grade |+------+--------+--------+-------+|  901 | 张老大 | 计算机 |    98 ||  901 | 张老大 | 英语   |    80 ||  902 | 李二狗 | 计算机 |    65 ||  902 | 李二狗 | 中文   |    88 ||  903 | 张三   | 中文   |    95 ||  904 | 李四   | 计算机 |    70 ||  904 | 李四   | 英语   |    92 ||  905 | 王五   | 英语   |    94 ||  906 | 王六   | 计算机 |    90 ||  906 | 王六   | 英语   |    85 ||  907 | 张琪   | 英语   |    99 || NULL | NULL   | 中文   |    98 |+------+--------+--------+-------+

查询表students和score中id相匹配的students中的id和name,scores中的course和grade数据,右表无法匹配左表的的数据，左表用NULL进行配对。

4）FULL JOIN:只要其中一个表中存在匹配，就返回行。（MySQL 5.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可以不使用（），ORDER BY 一定要和limit一起使用，否则不起作用。

例：

(SELECT id,name FROM students ORDER BY id LIMIT 20)UNION ALL(SELECT s_id,grade FROM score ORDER BY s_id DESC LIMIT 20);

+------+--------+| id   | name   |+------+--------+|  901 | 张老大 ||  902 | 李二狗 ||  903 | 张三   ||  904 | 李四   ||  905 | 王五   ||  906 | 王六   ||  907 | 张琪   ||  908 | 王萍   ||  909 | 98     ||  907 | 99     ||  906 | 85     ||  906 | 90     ||  905 | 94     ||  904 | 92     ||  904 | 70     ||  903 | 95     ||  902 | 88     ||  902 | 65     ||  901 | 80     ||  901 | 98     |+------+--------+

将表students中的id和name按照id正序排序与表score中的s_id和grade按照s_id降序排序合并到一起。

SQL 约束

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

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

1、NOT NULL 约束（非空约束）

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

a)建表时添加NOT NULL约束

格式：CREATE TABLE 表名（字段名 字段类型 NOT NULL）

例：

CREATE TABLE menu(id int(10) NOT NULL,name varchar(20));

创建一张带有id和name的menu表，其中如果插入数据时id的值不能为NULL。

b)追加NOT NULL约束

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

例：

ALTER TABLE students MODIFY id int(10) NOT NULL;

为students表中的id追加NOT NULL约束。

c)删除NOT NULL 约束

和追加NOT NULL约束类似。

格式：ALTER TABLE 表名 MODIFY 字段名 字段类型;

2、UNIQUE约束

UNIQUE约束，用来标识这个字段的数据的唯一性。（防止重复）

a）建表时添加UNIQUE约束

格式1：添加一个约束

CREATE TABLE 表名(字段名1 字段类型1，字段2 字段类型2 ， UNIQUE (字段名1) );

例：

CREATE TABLE menu(id int(10), name varchar(20), UNIQUE (id));

建一张带有id和name的表，其中id添加了UNIQUE约束。

格式2：添加多个约束

CREATE TABLE 表名(字段名1 字段类型1，字段2  字段类型2 ，字段3 字段类型3， CONSTRAINT 约束名 UNIQUE (字段名1，字段名,2) );

例：

CREATE TABLE menu(id int(10), name varchar(20),birth date, CONSTRAINT id_name UNIQUE (id,name));

建一张带有id、name和birth的表，其中id和name添加了UNIQUE约束（id和name不能同时相同，可以有一个相同）。

b）追加UNIQUE约束

格式：ALTER TABLE 表名 ADD UNIQUE(列名);

例：

ALTER TABLE students ADD UNIQUE(id);

为表students中的id添加unique约束。

注意：向已存在的表中添加唯一约束时，需要注意这一列中是否已经有重复值，如果有，则无法添加UNIQUE约束。

c)删除UNIQUE约束

ALTER TABLE 表名 DROP INDEX 列名;

例：

ALTER TABLE students DROP INDEX id;

删除表students中的id的UNIQUE约束。

3、PRIMARY KEY（主键）

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

要求：

1、主键必须包含唯一的值。

2、主键不能包含NULL值。

3、每个表中只能有一个主键。

a）建表时添加主键约束

格式：CREATE TABLE 表名（字段名1 字段类型2，字段名2  字段类型2，PRIMARY KEY(字段名1)）;

例：

CREATE TABLE menu(id int(10) NOT NULL, name varchar(20), PRIMARY KEY (id));

创建一张含有id和name的名为menu的表，其中为id添加了主键约束。

b）追加主键约束

格式：ALTER TABLE 表名 ADD PRIMARY KEY(列);

例：

ALTER TABLE students ADD PRIMARY KEY(id);

为表students中的id添加主键约束。

c）删除主键约束

格式：ALTER TABLE 表名 DROP PRIMARY KEY;

例：

ALTER TABLE students DROP PRIMARY KEY;

删除students表中的主键约束。

4、FOREIGN KEY （外键）

外键约束，用来指向另一个表中点击PRIMARY KEY。

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

a)建表时添加外键约束

格式：CREATE TABLE 表名2（字段名1 字段类型，字段名2 字段类型，FOREIGN KEY(字段2) REFERENCES 表1（表1中的字段名））;

例：

CREATE TABLE score(id int(10) NOT NULL, name varchar(20), FOREIGN KEY (id) REFERENCES students(id));

建一张含有id和name的score表，其中的id添加数据时必须是students中的id的数据。

b）追加外键约束

格式：ALTER TABLE 表名2 ADD FOREIGN KEY (列) REFERENCES 表名1（表1中的列）;

例：

ALTER TABLE score ADD FOREIGN KEY(id) REFERENCES students(id);

为score表中的id添加外键。

c）删除外键约束

格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键约束;

例：

ALTER TABLE score DROP FOREIGN KEY id;

删除score表中id的外键约束。

5、CHECK 关键字

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.如果删除默认值，他是不会自动回到defaultNULL。而是没有了默认值，没有默认值那么我们插入数据的时候就一定需要插入这一列的数据，这个数据也可以

  是NULL。

  添加的方法：

  CREATE TABLE 表名 （字段名 字段类型 DEFAULT 默认值，字段名字段类型...）

  追加的方法：

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

  删除约束的方法：

  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT  ;

7、VIEW视图

VIEW视图，用于限制介入表的数据的类型。

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

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

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

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

a)创建视图

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

例：

CREATE VIEW v_students AS SELECT id,name FROM students;

创建一个v_students的视图。

b)修改视图

格式：CREATE OR REPLACE VIEW 视图名 AS SELECT STATEMENT(查询语句)；

例：

CREATE OR REPLACE VIEW v_students AS SELECT id,name,sex FROM students;

修改v_students视图。

c）删除视图

格式：DROP VIEW 视图名;

例：

DROP VIEW v_students;

删除v_students视图。