数据库01

建数据表

在已经创建好的数据库中建立新表。创建数据表的过程是，同时也是实施数据完整性（包括实体完整性、引用完整 性和域完整性等）约束的过程。
数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。

创建数据表的语句为CREATE TABLE，语法规则如下：

CREATE TABLE TAB_NAME(
字段1 类型 约束 默认值,
字段2 类型 约束 默认值,
字段n 类型 约束 默认值
)

使用CREATE TABLE创建表时，必须指定以下信息：
（1）要创建的表的名称，不区分大小写，不能使用SQL语言中的关键 字，如DROP、ALTER、INSERT等。
（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。

表约束

数据完整性

**1.实体完整性：**实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性)。
**2.域完整性：**限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则)。
**3.引用完整性：**在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的
引用要进行一致的更改。
**4.自定义完整性：**用户自己定义的业务规则。
实体完整性：唯一约束、主键约束、标识列
域完完整性：限制数据类型、外键约束、默认值、非空约束
引用完整性：外键
自定义完整性：过程，触发器等

约束

五类完整性约束：
NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECKED 检查(mysql不支持的)

1、主键约束
（1）定义列的同时指定主键，语法如下

字段名 字段类型 primary key

（2)定义表之后指定主键

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段);

ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(emp_id);

2.联合主键
主键由多个字段联合组成，语法规则如下：

primary key(字段1,字段2,...字段n)

主键选取原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
2. 主键应该是单列的，以便提高连接和筛选操作的效率。
3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
5. 主键应当有计算机自动生成。

3.非空约束
非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空 约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

字段名 数据类型 not null

4.唯一约束
唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出 现一个空值。唯一约束可以确保一列或者几列不出现重复值。 唯一性约束的语法规则如下

字段名 数据类型 UNIQUE

如果定义完表后也可以给某个字段添加唯一约束，语法如下：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);

删除唯一索引的语法：

ALTER TABLE 表名 DROP INDEX 约束名;

5.默认值
默认约束（Default Constraint）指定某列的默认值。如男性同学较多， 性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如
下：

 字段名 字段类型 default 默认值

6.外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表 可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空 值，若不为空值，则每一个外键值必须等于另一个表中主键
的某个值。

**外键：**首先它是表中的一个字段，虽可以不是本表的主键，但要对应另 外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后， 不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致 性、完整性。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所 在的那个表即是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所 在的那个表即是从表。

语法：

ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);

常用的表关系

**一对一：**人与身份证

/*
人    身份证
1     1
*/

**一对多：**部门与员工

/*
部门     员工
1        N
1        1
*/

多对多：用户与角色【中间表多列主键】

/* 
用户    角色
1       N
N       1
N       N
*/

**自关联：**省市

7.查看表结构

DESCRIBE 表名;

或：

 DESC 表名;

8.查看表详细结构语句：
SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句，语法格式如下：

 SHOW CREATE TABLE employee;

修改表结构

1. 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

2. 修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

3. 修改字段名

 ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

4. 添加字段

ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];

5. 删除字段

ALTER TABLE <表名> DROP <字段名> ;

6. 修改字段的排序位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

7. 删除表的外键约束

 ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

8. 更改表的存储引擎

 ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

9. 删除数据表

 DROP TABLE [IF EXISTS]表1, 表2,…表n;

数据CRUD操作

1、插入数据

INSERT INTO 表名 VALUES(val_list);
或
INSERT INTO 表名(col_list) VALUES(val_list);

可以插入多条记录

INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3),(val_list4);

备份数据表
如果表不存在，此时

 CREATE TABLE emp_bak AS SELECT * FROM emp;

如果表存在，此时

INSERT INTO emp_bak SELECT * FROM emp;

2、更新数据

 UPDATE table_name SET col=val,col=val [WHERE <condition>];

3、删除数据

 DELETE FROM table_name [WHERE <condition>];

4、查询数据
MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：

SELECT 字段列表 FROM 表1,表2... WHERE 表达式 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT [<offset>,] <row count>]

（1）查询所有字段

 SELECT * FROM 表名;

（2）查询指定字段

 SELECT 列名 FROM 表名

（3） 查询多个字段

SELECT 字段名1,字段名2,…,字段名n FROM 表名;

查询的时候可以起别名

 SELECT e.`empid` AS 员工编号 ,e.`empname` AS 员工名字 FROM emp e;

（4） 查询指定记录
数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数 据进行过滤，语法格式为：

 SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件

（5）带IN关键字的查询
N操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。
在IN关键字前面加上了NOT关键字，这使得查询的结果与前面一个的结果正好相反。

（6）带BETWEEN AND的范围查询
BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数,即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。
BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的 值，如果字段值不满足指定的范围内的值，则这些记录被返回。

（7）带LIKE的字符匹配查询
通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL 语句中支持多种通配符，可以和LIKE一起使用的通配符有‘%’和‘_’。

1. 百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符
   2)下划线通配符‘_’，一次只能匹配任意一个字符

SELECT * FROM emp WHERE empname LIKE 'a%'; -- 以a开头
SELECT * FROM emp WHERE empname LIKE '%a'; -- 以a结尾
SELECT * FROM emp WHERE empname LIKE '%a%'; -- 包含a

（8）查询空值
数据表创建的时候，设计者可以指定某列中是否包含空值（NULL）在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。与IS NULL相反的是NOT NULL，该关键字查找字段不为空的记录。

（9）带AND的多条件查询
MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开

（10）带OR的多条件查询
与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开

（11）查询结果不重复
在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。

SELECT DISTINCT empid,empname,empsex FROM emp;

（12）对查询结果排序
使用ORDER BY子句对指定的列数据进行排序。 desc:倒叙 asc:默认值，升序

1. 单列排序

 SELECT * FROM emp ORDER BY empid DESC;

2. 多列排序

SELECT * FROM emp ORDER BY empid DESC ,deptid DESC;

（13）聚合函数
有时候并不需要返回实际表中的数据，而只是对数据进行总结。 MySQL提供一些查询功能，可以对获取的数据进行分析和报告。这些函数 的功能有：计算数据表中记录行数的总数、计算某个字段列下数据的总和， 以及计算表中某个字段下的最大值、最小值或者平均值。常用的聚合函数 MAX()、MIN()、COUNT()、SUM()、AVG()。

1）COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果 返回列中包含的数据行数。其
使用方法有两种：
COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
2）SUM()是一个求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。
3）AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
4）MAX()函数返回指定列中的最大值。
MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列 中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较 时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最小，z的 最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，‘b’与‘t’比较时，‘t’为最大值；“bcd”与“bca”比较时，“bcd”为最大值。
5）MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

（14）分组查询
GROUP BY关键字通常和集合函数一起使用
查询每个部门的人数

 SELECT deptid,COUNT(1) FROM emp GROUP BY deptid;

统计每个部门的男女人数

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex;

分组后再排序

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex ORDER BY deptid DESC,COUNT(1) DESC;

（15）使用HAVING过滤分组
GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满 足条件的分组才会被显示

（16）LIMIT
SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回 第一行或者前几行，可使用LIMIT关键字。