Mysql数据库学习（三）：表的crud操作、完整性约束、select单表_MySQL

bitsCN.com

一、表的crud操作

指增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)// select 查询后面再讲
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
create table t_emp(empno int, ename varchar(20), esex char(2));

alter table t_emp modify ename varchar(30);

alter table t_emp drop esex;

alter table t_emp add esex char(2);

insert into t_emp(empno, ename, esex) values(1000, 'tom', 'm');

insert into t_emp values(1000, 'maggie', 'f'); /* 还没设置主键，故empno可以相同 */

insert into t_emp(empno, ename) values(1002, 'john');

insert into t_emp(empno, ename, esex) values(1003, null, 'm');

insert into t_emp values(1004, '张三', '男');

show variables like 'character_set%'; /* 查看字符集设置 */
set character_set_database=utf8; /* 也可设置配置文件 */

set names gbk; /* 支持插入中文 */

update t_emp set empno=1001 where ename='maggie';

delete from t_emp where esex is null;

delete from t_emp; /* 表结构还在 */

drop table t_emp; /* 整表删除 */
二、完整性约束表完整性约束

主键 (constraint)外键 (constraint)用户自定义完整性约束 (check)

SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
create table t_emp(empno int not null primary key, ename varchar(20), esex char(2)); /* 创建时设置主键*/

create table t_emp(empno int, ename varchar(20), esex char(2), primary key (empno));

create table t_emp(empno int, ename varchar(20), esex char(2), constraint PK_EMPNO primary key(empno)); /* 设置主键*/

create table t_emp(empno int, ename varchar(20), esex char(2));

alter table t_emp add constraint PK_EMPNO primary key(empno); /* 这种方式也可以设置主键 */

insert into t_emp values(1000, 'john', 'm');

insert into t_emp values(1000, 'lily', 'f'); /* error，empno不能相等 */

insert into t_emp values(null, 'lily', 'f'); /* error，主键不能为空 */



create table t_emp(empno int, deptno int, ename varchar(20), esex char(2));

alter table t_emp add constraint PK_EMPNO primary key(empno);

create table t_dept(deptno int, dname varchar(20));

alter table t_dept add constraint PK_DEPTNO primary key(deptno);

alter table t_emp add constraint FK_DEPTNO foreign key(deptno) references t_dept(deptno); /*设置t_emp 的外键为t_dept 的主键 */

set names gbk;
insert into t_dept values(2001, '人事部');
insert into t_dept values(2002, '技术部');

insert into t_emp values(1001, 2001, 'john', 'm');

insert into t_emp values(1003, 2003, 'john', 'm');

create table t_test1(id int auto_increment primary key, name varchar(30), age int default 20);

insert into t_test1 values(null, 'aaa');

insert into t_test1 values(null, 'aaa', null);

insert into t_test1 (name) values( 'bbb');

create table t_test2(id int, name varchar(30), age int);
alter table t_test2 add constraint CC_AGE check (age >=18 and age<=60); /* 实际上现在mysql不支持check限制 */

alter table t_test2 add constraint CC_AGE check (length(name)>2);

三、select 查询
练习前先导入数据：create database scott;use scott;source C:/scott.sql scott.sql 点这下载
select 单表查询：
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select empno,ename,job from emp;
select * from emp;

SELECT empno as '工号',ename '姓名' FROM emp; /* as 后面是别名 */
SELECT empno, '暨南大学' FROM emp; /* 常量列查询 */
SELECT empno, concat(ename,'#') FROM emp; /* concat连接 */
SELECT empno, ename||'#' FROM emp; /* oracle可以用||作为连接符 */

SELECT empno, ename, job FROM emp WHERE ename = 'SMITH'
SELECT empno, ename, job FROM emp WHERE ename <> 'SMITH' /* 也可以使用!= */
SELECT empno, ename, sal FROM emp WHERE sal>= 1500
SELECT * FROM emp WHERE deptno=30 and sal>1500; /* and */
SELECT * FROM emp WHERE job='MANAGER' or job='SALESMAN' /* or */
SELECT * FROM emp where sal BETWEEN 800 and 1500;
SELECT * FROM emp where sal >= 800 and sal <= 1500;
SELECT empno, ename, sal, comm FROM emp WHERE comm is null
SELECT empno, ename, sal, comm FROM emp WHERE comm is not null /* not */

SELECT * FROM emp where sal not BETWEEN 800 and 1500; /* between */
SELECT * FROM emp where ename in ('SMITH', 'KING'); /* in */
SELECT * FROM emp where ename like 'S%'; /* 模糊查询 通配符: ‘%’（0个多个字符）； 通配符: ‘_’ （单个字符） */
SELECT * FROM emp where ename like 'S_ITH';

SELECT * FROM emp ORDER BY ename desc; /* order by 默认是升序 asc */
SELECT empno, ename, job FROM emp ORDER BY 2 desc;
SELECT * FROM emp ORDER BY job asc, sal desc;

SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select * from emp ORDER BY sal limit 5; /*limit可用于分页查询*/
select * from emp ORDER BY sal limit 0,5; /* 0表示offet, 5表示从0开始的5条记录*/
select * from emp ORDER BY sal limit 5,5;
select * from emp ORDER BY sal limit 10,5;

select job,deptno from emp;
select all job,deptno from emp; /* 默认是all */
select distinct job,deptno from emp; /* 去除重复记录 */


select * from dept where deptno in (SELECT DISTINCT deptno from emp); /* 查询有员工的部门信息 */

/* UNION (无重复并集)：当执行UNION 时，自动去掉结果集中的重复行，并以第一列的结果进行升序排序。*/
select empno,ename,job from emp where job='SALESMAN'
union /* union即联合查询 */
select empno,ename,job from emp where job='MANAGER';

select empno,ename,job from emp where job='SALESMAN' or job='MANAGER' /* 比较结果 */

/* UNION ALL (有重复并集)：不去掉重复行，并且不对结果集进行排序。*/
select job, sal from emp where empno=7902
union all
select job, sal from emp where empno=7788;

select job, sal from emp where empno=7902
union
select job, sal from emp where empno=7788;
select 多表查询：多表查询

交叉连接内连接自身连接外连接

左外连接

右外连接

全连接

自然连接

交叉连接是不带WHERE子句的多表查询，它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
内连接（等&＃20540;连接）：在连接条件中使用等于号(=)运算符比较被连接列的列&＃20540;，其查询结果中列出被连接表中的所有列，包括其中的重复列。
内连接（不等连接）：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列&＃20540;。这些运算符包括>、>=、<=、<、!>、!<和<>
内连接(自身连接)

外连接（左连接）：返回包括左表中的所有记录和右表中联结字段相等的记录；即左外连接就是在等&＃20540;连接的基础上加上主表中的未匹配数据。
外连接（右连接）：返回包括右表中的所有记录和左表中联结字段相等的记录；即右外连接是在等&＃20540;连接的基础上加上被连接表的不匹配数据。
外连接（全连接）：全外连接是在等&＃20540;连接的基础上将左表和右表的未匹配数据都加上。

自然连接：在连接条件中使用等于(=)运算符比较被连接列的列&＃20540;，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select * from emp,dept /*交叉连接 */

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; /*内连接（等&＃20540;连接） */
select * from emp,dept where emp.deptno=dept.deptno;

select * from emp INNER JOIN dept on emp.deptno > dept.deptno; /* 内连接（不等连接）*/
select * from emp,dept where emp.deptno > dept.deptno;

select A.ename 员工, B.ename 领导 from emp A, emp B where A.mgr = B.empno; /*内连接（自身连接） */

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
select * from emp left join dept on emp.deptno=dept.deptno /*外连接（左连接） */


/* scott.sql并未设置emp表的外键为deptno，故这里可以插入在dept表中不存在的deptno&＃20540;*/
/* 主要是为了演示左连接和右连接的区别 */
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9999, 'XXXX', 'CLERK', 7782, '1982-01-23', 1300, null, 90);

select * from emp right outer join dept on emp.deptno=dept.deptno /* 外连接（右连接） */

select * from emp left join dept on emp.deptno=dept.deptno

/* 自然连接会合并deptno一项，而外连接不会 */
SELECT * FROM emp NATURAL JOIN dept;

SELECT * FROM emp NATURAL LEFT JOIN dept;

SELECT * FROM emp NATURAL RIGHT JOIN dept;

参考：
《数据库系统概论》
mysql 5.1 参考手册


bitsCN.com