정보처리기사
Part 1
데이터베이스
MySQL 기본명령어 정리
mysql登陆的命令格式为：
mysql -h [hostip] -u [username] -p [password] [database]
1.普通登陆mysql数据库
命令格式：mysql -u 用户名 -p 密码，例如: mysql -u root -p root
如果在-p后没有提供密码，则会在执行命令后要求用户输入密码
2.表操作
建立数据库空间：create database DataBaseTest;
使用新建立的数据库空间：use DataBaseTest;
建表：create table t_dept(
deptno int,
dname varchar(20),
loc varchar(40));
查看表定义：describe t_dept; / desc t_dept;
查看表的详细定义：show create table t_dept \G
删除表：drop table t_dept;
修改表名称：alter table t_dept rename tab_dept;
增加字段：alter table table_dept add descri varchar(20);
在表的第一个位置增加字段：alter table table_dept add descri_first varchar(20) first;
在表的指定字段后增加字段：alter table table_dept add descr_afteri varchar(20) after deptno;
删除字段：alter table table_dept drop deptno;
修改字段的数据类型：alter table table_dept modify deptno varchar(20);
修改字段的名称：alter table table_dept change loc location varchar(40);
同时修改字段的名称和属性：alter table table_dept change loc location varchar(20);
修改字段的顺序：
alter table table_dept modify location varchar(20) first;(将字段location调整至表的第一个位置)
alter table table_dept modify deptno int(11) after dname;(将字段deptno调整至字段dname之后的位置)
操作表的约束：
完整性约束条件关键字
NOT NULL：约束字段的值非空
DEFAULT：设置字段的默认值
UNIQUE KEY(UK)：约束字段的值唯一
PRIMARY KEY(PK)：约束字段为当前表的主键
AUTO_INCREMENT：约束字段的值为自动增加(自增)
FOREIGN KEY(FK)：约束字段为表的外键
设置非空约束：create table t_dept(
deptno int(20) not null,
dname varchar(20),
loc varchar(40));
设置字段的默认值：create table t_dept(
deptno int(20) not null,
dname varchar(20) default 'cjgong',
loc varchar(40));
设置唯一约束(unique，UK)：create table t_dept(
deptno int(20),
dname varchar(20) unique,
loc varchar(40));
給字段dname上的UK约束设置一个名字：
create table t_dept(
deptno int(20),
dname varchar(20),
loc varchar(40),
constraint uk_dname unique(dname));
设置主键约束：create table t_dept(
deptno int(20) primary key,
dname varchar(20),
loc varchar(40));
給字段depno上的PK主键约束设置一个名字：
create table t_dept(
deptno int(20),
dname varchar(20),
loc varchar(40)，
constraint pk_dname primary key(dname));
多字段主键设置：create table t_dept(
deptno int(20),
dname varchar(20),
loc varchar(40),
constraint pk_dname_deptno primary key(deptno, dname));
设置字段自增：create table t_dept(
deptno int(20) primary key auto_increment,
dname varchar(20),
loc varchar(40));
设置外键约束：create table t_dept(
deptno int(20) primary key,
dname varchar(20),
loc varchar(40));
create table t_employee(
empno int primary key,
ename varchar(20),
job varchar(40),
MGR int,
Hiredate date,
sal double(10, 2),
comm double(10, 2),
deptno int,
constraint fk_deptno foreign key(deptno)
reference t_dept(dept_no));
使用索引index的操作
数据库对象index是基于书的目录非常类似，主要为了提高从表中检索数据的速度，索引是创建在数据库表对象上的，由表的一个字段或是多个字段生成的键组成，这些键存储在(B-树或者Hash表中)，索引可分为B型索引(BTREE)和哈希索引(HASH)
在创建表的时候，在字段deptno上创建索引：
create table t_dept(
deptno int(20),
dname varchar(20),
loc varchar(40),
index index_deptno(deptno));
校验数据库表中t_dept的索引是否被引用，使用关键字explain：
explain
select * from t_dept where deptno=1\G;
在已经存在的表上创建普通索引：
create index index_deptno on t_dept (deptno); [再次确认show create table t_dept \G]
使用alter table语句来创建索引：
alter table t_dept add index index_deptno(deptno);
创建表时，创建唯一索引：
create table t_dept_index(
deptno int unique,
dname varchar(20),
loc varchar(40),
unique index index_deptno(deptno));
[再次校验：explain select * from t_dept_index where deptno=10\G;]
在已经存在的表上创建唯一索引：
create unique index index_deptno on t_dept(deptno);
使用alter table语句创建唯一索引：
alter table t_dept add unique index index_deptno(deptno);
[再次确认：show create table t_dept \G]
创建和查看唯一索引
创建表的时候创建全文索引
create table t_dept(
deptno int,
dname varchar(20),
loc varchar(40),
fulltext index index_loc(loc))engine=MyISAM;
[再次校验：explain select * from t_dept where dname='cjgong'\G;]
在已经存在的表上面创建全文索引：
create fulltext index index_loc on t_dept(loc);
通过alter table语句创建全文索引：
alter table t_dept add fulltext index index_loc(loc);
[再次校验：show create table t_dept \G]
创建和查看多列索引：
create table t_dept(
deptno int,
dname varchar(20),
loc varchar(40),
key index_dname_loc(dname, loc));
[再次校验：explain select * from t_dept where dname='cjgong'\G;]
在已经存在的表上创建多列索引：
create index index_dname_loc on t_dept(dname, loc);
通过SQL语句akter table创建表中的多列索引：
alter table t_dept add index index_dname_loc(dname, loc);
[再次校验：show create table t_dept \G;]
删除索引：
首先校验数据库表中的索引是否被引用
[校验：explain select * from t_dept where dname='cjgong'\G;]
使用drop语句删除索引对象：
drop index index_dname_loc on t_dept;
视图View
创建视图的基本步骤：
首先切换数据库的view数据库中
原表数据：
create table t_product(
id int(10),
name varchar(20),
price double(10, 2));
创建视图：create view view_selectproduct
as select if, name from t_product;
使用视图：select * from view_selectproduct;
创建各种视图View：
create view view_test1
as select 3.1415926;
封装使用SQL聚合函数查询语句的视图：
create view view_test2
as select count(name) from t_student;
封装实现排序功能的视图：
create view view_test3
as select t_student order by id desc;
封装了实现表的婑链接查询语句的视图：
create view view_test4
as select s.name from t_student as s, t_group as g
where s.group_id = g.id and g.id = 2;
封装实现了Table外连接(Left Join & Right Join)查询语句的视图：
create view view_test5
as select s.name
from t_student as s left join t_group as g on s.group_id = g.id
where g.id = 2;
封装了实现子查询相关查询语句的视图：
create view view_test6
as select s.name
from t_student as s
where s.group_id in (select id from t_group);
封装实现了记录联合(Union & Union All)查询语句的视图：
create view view_test7
as select id, name from t_student
union all
select id, name from t_group;
查看视图
use view;
show tables;
查看视图详细信息：
show table status
from view \G
查看某个专门视图的详细信息：
show table status
from view like "view_selectproduct" \G
查看视图的定义信息：
use view;
show create view view_selectproduct \G
查看视图的设计信息：
use view;
describe view_selectproduct;
desc view_selectproduct;
通过系统表查看视图信息：
use information_schema;
select * from views where table_name="view_selectproduct";
删除视图：
use view;
drop view view_selectproduct;
drop view view_selectproduct1, view_selectproduct2;
[校验确认view删除：use information_schema; select * from view_selectionproduct;]
修改视图：
use view;
drop view view_selectproduct;
create view view_selectproduct
as select name from t_product;
[最后校验查看删除后新建的视图：select * from view_selectproduct;]
为了简化视图修改的过程每次不必总是先删除视图，后在创建新视图：
create or replace view view_selectproduct
as select name from t_product;
[校验确认替换的视图对象：select * from view_selectproduct;]
alter语句修改视图：
use view;
alter view view_selectproduct
as select name from t_product;
[校验确认alter后的视图对象：select * from view_selectproduct;]
利用视图操作基本表：
1.检索视图对象：select * from view_selectproduct;
2.利用视图添加数据操作：insert into view_product(id, name, price, order_id) values(11, 'PEAR4', 12.3, 2);
3.利用视图添加数据操作(不带有视图中参数信息的插入)：insert into view_product values(11, 'PEAR4', 12.3, 2);
4.删除数据操作：delete from view_product where name='apple1';
5.更新数据操作：update view_product set price=3.5 where name='pear1';
单表记录查询
create table t_employee(
empon int(11),
ename varchar(20),
job varchar(40),
MGR int(11),
Hiredate date,
sal double(10, 2),
comm double(10, 2),
deptno int(11));
insert into t_employee values(7369, 'smith', 'clerk', 7902, '1981-03-12', 800.00, null, 20);
insert into t_employee values(7499, 'allen', ’salesman', 7698, '1982-04-10', 1600.00, 300.0, 30);
insert into t_employee values(7369, 'Kim-taekwi', 'boss', 1111, '1996-07-24', 9999.99, 999.9, 100);
[本列插入的元素与之前第一条插入的记录中的empno数据相同有冲突，故使用如下指令更新第三条记录：update t_employee set empon=6666 where ename='Kim-taekwi';]
insert into t_employee values(2843, 'jim', 'salesman', 8374, '1989-02-24', 903.10, null, 90);
insert into t_employee values(9384, 'babala', 'clerk', 9277, '1970-09-17', 400.00, 2222, 16);
insert into t_employee values(7199, 'kate', 'manager', 8367, '1999-03-28', 426.00, null, 20);
insert into t_employee values(7092, 'james', 'analyst', 7788, '1982-03-03', 880.00, null, 10);
insert into t_employee values(7833, 'King', 'president', null, '1869-03-12', 1500.00, null, 10);
insert into t_employee values(1020, 'jinliang', 'Master', 6666, '1970-09-09', 10000.00, null, 9);
insert into t_employee values(1211, 'Xiepei', 'president', null, '1970-12-11', 20000.00, null, 15);
insert into t_employee values(7844, 'turner', ’salesman', 7698, '1989-03-12', 1500.00, 0.00, 10);
此处将之前建表时错误命名的字段名称empon 改为empno
alter table t_employee change empon empno int(11);
避免重复数据查询：
before: select job from t_employee;
After: select distinct job from t_employee;
求每个雇员的年薪：
select ename, sal*12 yearSalary from t_employee; 或者改为如下
select ename, sal*12 as yearSalary from t_employee;
设置格式的数据查询：
select concat(ename, '雇员的年薪为：', sal*12) as yearSalary from t_employee;
条件数据记录查询
select ename from t_employee where job='jinliang';
select ename from t_employee where job='jinliang' and sal > 800;
select ename, empno, job from t_employee where sal between 800 and 10000;
查询不在范围内的数据记录：
select ename from t_employee where sal not between 1000 and 10000;
空值数据记录查询：
select ename, empno, job from t_employee where comm is null;
非空值数据记录查询：
select ename, empno, job from t_employee where comm is not null;
在集合中的数据记录查询：
select ename
from t_employee
where empno=7521 or empno=7782 or empno=6666;
select ename
from t_employee
where empno in (6666, 7521, 1020);
不再集合中的数据记录查询：
select ename from t_employee where empno not in (6666, 7521, 1020, 1211);
或者 select ename from t_employee where not empno in (6666, 7521, 1020, 1211);
在集合中存在NULL值，不行想查询结果：
select ename from t_employee where empno not in (6666, 7521, 1020, 1211, null);
带有like关键字的模糊查询：
select ename, empno, job from t_employee where ename like 'K%';
select ename, empno, job from t_employee where ename like 'a%';
select ename, empno, job from t_employee where not ename like 'A%';
模糊查询关于某条字段的信息第二个字母为A的全部雇员信息：
select ename, empno, job from t_employee where ename like '_A%';
select ename, empno, job from t_employee where not ename like '_A%';
not like模糊查询注意点：
select ename, empno, job from t_employee where not ename like 'A%'; 同
select ename, empno, job from t_employee where ename not like 'A%';
对于关键字like，如果匹配’%%'则表示查询所有的数据信息：
select ename from t_employee where sal like '%%';
排序数据记录查询：
按照sal的值对该表的所有数据记录升序排序
select * from t_employee order by sal asc;
order by默认为升序排序，故默认也可写为：
select * from t_employee order by sal;
降序排序查找数据记录：
select * from t_employee order by mgr desc;
按照多字段排序：
select * from t_employee order by sal asc, hiredate desc; 或写为
select * from t_employee order by sal, hiredate desc;
限制数据记录查询数量
使用MySQL提供的关键字limit来限制查询结果的数量
select field1 field2 ……. fieldn
from table_name
where condition
limit offset_start, row_count;
不指定初始位置：使用关键字limit，如果不指定初始位置，默认值为0，表示从第一条记录开始显示，具体语法形式为：limit row_count
如果row_count值小于查询结果的总数量，将会从第一条记录开始显示row_count条数据记录；如果row_count值大于查询结果的总数量，将会显示所有的查询结果
显示记录数小于查询结果：
select * from t_employee
where comm is null
limit 2;
[设置查询数据条件为comm is null，关键字limit显示数据记录数目为2]
显示记录数大于查询结果：
select * from t_employee
where comm is null
limit 11;
指定初始位置，limit关键字经常应用于分页系统中，对于第一页的数据记录来说，可以通过不指定初始位置来实现，但是对于第二页等其他分页来说必须制定初始位置(offset_start),否则无法实现分页功能
select * from t_employee
where comm is null
order by hiredate limit 0, 5; 或者 order by hiredate limit 5;
[上述语句中设置查询数据条件为comm is null, 最后通过关键字limit设置显示数据记录的数目为5, 从第一条记录开始]
select * from t_employee
where comm is null
order by hiredate limit 5, 5;
[上述语句设置查询数据条件为comm is null, 最后设置关键字limit显示数据记录的数目为5, 从第6条记录开始]
统计函数和分组记录的查询
count()函数：该函数实现统计表中记录的条数
avg()函数：实现计算字段值的平均值
sum()函数：实现计算字段值的总和
max()函数：实现查询字段值的最大值
min()函数：实现查询字段值的最小值
统计函数的查询语法规则：
select function(field)
from table-name
where condition
雇员数量统计：select count(*) number from t_employee;
实现统计领取奖金的雇员人数：
select count(comm) number
from t_employee;
where not comm=0;
分组数据group by查询：
在正常使用统计函数时，针对表中所有记录或者是特定条件(where子句)的数据进行统计计算，但是在实际应用中经常会把所有的数据记录进行分组，然后再对分组后的数据记录进行统计计算
select * from t_employee
group by deptno;
[由于数据库中表t_employee中的deptno字段中的值分别为10,20,30所以首先将所有的数据记录按照这三个值分成三组，然后在现实魅族中的一条记录]
实现统计功能的分组查询：(具体语法形态如下)
select group_concat(field)
from table-name
where condition group by field;
在表t_employee中，按照部门号码deptno对所有雇员进行分组，同时显示出每组中雇员名(ename)，和每组中的雇员个数：
select deptno, group_concat(ename) enames
from t_employee
group by deptno;
[执行结果分为3组，10,20,30各分为一组，同时通过group_concat()函数显示出每组中的雇员名字]
select deptno, group_concat(ename) enames, count(ename) number
from t_employee
group by deptno;
实现多个字段的分组查询
多字段分组查询的语法形式如下：
select group_concat(field), function(field)
from table-name
where condition group by field1, field2, ……, fieldn;
按照deptno和hiredate进行多字段分组查询：
select deptno, hiredate
from t_employee
group by deptno, hiredate;
[结果将首先按照deptno进行分为3组后，然后针对字段hiredate进行分组]
select deptno, hriedate, group_concat(ename) enames, count(ename)
from t_employee
group by deptno, hiredate;
分组数据查询--实现having子句的限定分组查询
select function(field)
from table-name
where condition
group by field1, field2, ……, fieldn
having condition;
执行统计函数avg()，显示每组中的平均工资：
select deptno, avg(sal) average
from t_employee
group by deptno;
select deptno, avg(sal) average, group_concat(ename) enames, count(ename) number
from t_employee
group by deptno
having avg(sal) > 2000;
[上述查询语句中，不仅通过统计函数avg()获取每个部门的平均工资，而且还通过函数group_concat()显示出每个部门的雇员名和通过count()函数统计出每个部门的雇员人数，最后通过having关键字进行条件的限制(结果显示平均工资大于2000的两组)]
多表数据记录查询
关系数据操作
并(UNION)：把具有相同字段数目和字段类型的表合并在一起
insert into t_dept values(10, 'accounting', 'new york')
insert into t_dept values(20, 'research', 'dallas');
insert into t_dept values(30, 'sales', 'chicago');
insert into t_dept values(40, 'operations', 'boston');
create table t_cstudent(
name varchar(20),
sex varchar(6));
insert into t_cstudent values('ccjgong1', 'female');
insert into t_cstudent values('ccjgong2', 'male');
insert into t_cstudent values('ccjgong3', 'male');
insert into t_cstudent values('ccjgong4', 'female');
insert into t_cstudent values('ccjgong5', 'female');
insert into t_cstudent values('jinzewei', 'male');
insert into t_cstudent values('Taekwi-Kim', 'male');
create table t_mstudent(
name varchar(20),
sex varchar(6));
insert into t_mstudent values('cmcjgong1', 'female');
insert into t_mstudent values('mcjgong2', 'male');
insert into t_mstudent values('mcjgong3', 'male');
insert into t_mstudent values('mcjgong4', 'female');
insert into t_mstudent values('mcjgong5', 'female');
insert into t_mstudent values('mc-jinzewei', 'male');
insert into t_mstudent values('mc-Taekwi-Kim', 'male');
笛卡尔积示例： select * from t_dept, t_employee order by ename desc;
等值Join示例： select * from t_dept equal join t_employee order by ename desc;
Natural Join示例： select * from t_dept natural join t_employee order by ename desc;
内连接查询
内连接查询语法条件：select field1, field2, ……, fieldn
from join-table-name1 inner join join-table-name2 {inner join join-table-namen}
on join_condition;
自连接
select e.ename employee_name, e.job
from t_employee e;
select e.ename employee_name, e.job, l.ename loader_name
from t_employee e inner join t_employee l on e.mgr=l.empno;
或者
select e.name employee_name, e.job, L.ename loader_name
from t_employee e, t_employee l
where e.mgr = l.empno;
三张表的查询1.部门表(t_dept)，2.雇员表(t_employee)，3.领导表(t_employee)
select e.empno, e.ename, employee_name, e.sal, e.job, l.ename loader_name
from t_employee e inner join t_employee l
on e.mgr = l.empno;
[结果显示关于雇员的编号，姓名，基本工资，职位和职位领导的名字]
select e.empno, e.ename employee_name, e.sal, e.job, l.ename loader_name, d.dname, d.loc
from t_employee e inner join t_employee l
on e.mgr = l.empno inner join t_dept d
on l.deptno = d.deptno;
或者
select e.empno, e.ename employee_name, e.sal, e.job, l.ename loader_name, d.dname, d.loc
from t_employee e, t_employee l, t_dept d
where e.mgr = l.empno and l.deptno = d.deptno;
不等连接
在内连接查询中的不等连接，即在关键字on后面的匹配条件中通过使用不等关系运算符来实现不等连接
select e.ename employee_name, e.job, l.ename loader_name
from t_employee e inner join t_employee l
on e.mgr = l.empno and e.empno > l.empno;
或者也可变形为
select e.ename employee_name, e.job, l.ename loader_name
from t_employee e inner join t_employee l
where e.mgr = l.empno and e.empno > l.empno;
外连接查询
外连接Outer Join查询语法：
select field1 field2 …… fieldn
from join-table-name1 left|right|full [outer] join join-table-name2
on join_condition;
按照外连接关键字分为：1.左外连接 2.右外连接 3.全外连接
左外连接：外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字left join左边的表为参考表
select e.ename employee, e.job
from t_employee e;
在查询中引入领导表，同时添加一条消除笛卡尔积的匹配条件：
select e.ename employee_name, e.job, l.ename loader_name
from t_employee e left join t_employee l
on e.mgr=l.empno;
select e.name employee_name, e.job, l.ename loader_name
from t_employee e inner join t_employee l
on e.mgr=l.empno;
右外连接：外连接查询中的右外连接，就是指新关系中执行匹配条件时，以关键字right join右边的表为参考表
select e.empno, e.ename, e.job, d.dname, d.loc
from t_dept d right join t_employee e
on e.deptno = d.deptno;
等值连接
select e.empno, e.ename, e.job, d.dname, d.loc
from t_employee e, t_dept d
where e.deptno = d.deptno;
合并查询数据库记录
在MySQL中使用关键字UNION来实现并操作，即可以通过其将多个select语句的查询结果合并在一起组成新的关系
合并查询语法形式如下：
select field1 field2 …… fieldn
from table_name1
union | union all
select field1 field2 …… fieldn
from table_name2
union | union all
select field1 field2 …… fieldn
from table_name3
union | union all
select * from t_cstudent
union
select * from t_mstudent;
[union 操作中除去了数据中的重复信息]
select * from t_cstudent
union all
select * from t_mstudent;
[union all操作并不出去数据中的重复信息]
子查询
在MySQl数据库中不建议使用连接查询来实现多表查询数据的记录，这是因为连接查询的性能很差，因此出现了替代连接查询的子查询
select * from t_employee
where sal>(select sal from t_employee where ename='smith');
select ename, sal, job
from t_employee
where (sal, job) = (select sal, job from t_employee where ename='jinliang');
返回结果为多行单列子查询
当子查询的返回结果为多行单列数据记录时，该子查询语句一般会在主查询语句的where子句中出现，通常会包含in, any, all, exists等关键字
1.带有关键字in的子查询
select *
from t_employee
where deptno in (select deptno from t_dept);
select *
from t_employee
where deptno not in (select deptno from t_dept);
2.带有关键字any的子查询
关键字any用来表示住查询的条件为了满足子查询返回结果中任意一条数据记录
该关键字的三种匹配方法：
=any：其功能与关键字in相同
>any：(>=any)比查询中返回数据记录中最小的还要大于(大于等于)的数据记录
select ename, sal
from t_employee
where sal > any(select sal from t_employee where job='manager');
select ename, sal
from t_employee
where sal >= any(select sal from t_employee where job='manager');
3.带有关键字all的子查询
>all(>=all)：比子查询中返回数据记录中最大的还要大于(大于等于)的数据记录
select ename, sal
from t_employee
where sal > all(select sal from t_employee where job = 'manager');
select ename, sal
from t_employee
where sal >= all(select sal from t_employee where job = 'manager');
select *
from t_employee a, t_dept c
where a.deptno = c.deptno;
4.带有exists的子查询
关键字exists是一个布尔类型，当返回结果集时返回true，不能反悔结果集时返回false，查询时exists对外表采用遍历方式是逐条查询，每次查询都会比较exists的条件语句，当exists里的条件语句返回记录行时则条件为真，此时返回当前遍历到的记录，反之，如果exists里的条件语句不能返回记录行，则丢弃当前遍历到的记录
select *
from t_dept c
where not exists(select * from t_employee where deptno = c.deptno);
select *
from t_dept c
where exists(select * from t_employee where deptno = c.deptno);
返回结果为多行多列子查询
当子查询的返回结果为多行多列记录时，该子查询语句一般会在查询语句的from子句里，被当作一张临时的表来被处理
select d.deptno, d.dname, d.loc, count(e.empno) number, avg(e.sal) average
from t_employee e inner join t_dept d
on e.deptno = d.deptno
group by d.deptno desc, d.dname, d.loc;
select count(*) number
from t_employee e, t_dept d;
[t_employee表和t_dept表的笛卡尔积数据记录个数统计]
select d.deptno, d.dname, d.loc, number, average
from t_dept d inner join(
select deptno dno, count(empno) number, avg(sal) average
from t_employee
group by deptno desc) employee
on d.deptno=employee.dno;
select count(*) number
from t_dept d, (
select deptno dno, count(empno) number, avg(sal) average
from t_employee
group by deptno) employee;
参考文献：정보처리기사 필기 기본서 1권 데이터베이스(영진닷컴 출판)
end
AI技术交流 + 兴趣讨论：
674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
