DOS命令操作数据库

登录数据库 mysql -uroot -p
退出：quit
进入mysql：/h帮助

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

查看数据库：show databases;
创建数据库：create database bbs;
删除数据库：drop database bbs;
创建表：use bbs;
create table user(id int, username varchar(40), password varchar(32) );
查看表：show tables;
查看创建表的语句：show create table user;
查看数据库的语句：show create database bbs;
删除表：drop tables user;
查看表结构：desc user;
修改表的字段：alter table user change password pass varchar(45);
alter table user change username name varchar(35);
删除表的字段：alter table user drop pass;
表中插入字段：alter table user add pass varchar(32) first;（插入到前面）
alter table user add pass varchar(32) after name;（插入到name后面）
修改表名：alter table user rename user_table;
修改表字段值：alter user_table modify name varchar(40);

phpmyadmin/Navicat premium可以图形化操作

索引
普通索引、唯一索引、主键索引、全文索引
alter table bbs_user add index(age);普通索引
alter table bbs_user add unique(password); 唯一索引
alter table bbs_user add fulltext(address);全文索引
alter table bbs_user add primary key(id);主键索引

查看索引：show index from bbs_user; （查看哪些字段下添加了索引）
查看当前表在哪个数据库：select database();

数据库增删改查部分：

插入数据方法1： insert into 表名 values(1,’王宝强’,123123,’河北邢台’,1,18); 和表结构一一对应
插入数据方法2常用： insert into 表名(id,username,password,address,sex,age) values(2,’谢霆锋’,123,’中国香港’,1,19); 【id自增，工具上一个id自增1】
插入数据方法3： insert into 表名(username,password,address ,sex,age) values(‘李连杰’,456,’大陆’,1,50),(‘成龙’,689,’香港’,1,55);
查看表数据： select * from 表名;

删除表数据：delete from 表名 where username=’测试’;
修改表数据：update 表名 set username=’测试’ where id=1;
修改多个字段：update 表名 set username=’小马’, password=’8888’, address=’外星’ where id=6;
去除重复值：select distinct address from shop_user;
between … and 取区间
in()
like模糊查询
order by 升序（desc降序）
分组group by
起别名 select username as name from bbs_user;

多表查询：

内联查询：select username,gname from shop_user inner join shop_goods on shop_user.gid=shop_goods.gid;
左连接查询：select shop_user.username left join shop_goods on shop_user.gid=shop_goods.gid;（左表为基准）
右连接查询：select shop_user.username right join shop_goods on shop_user.gid=shop_goods.gid（左表为基准）
嵌套查询：select * from shop_user where gid in(select gid from shop_goods);（不推荐使用，效率慢）

习题：
表记录的更新操作：
（1）向student表中插入记录（”2005010203”，”张静”，”女”，”1981-3-21”，”软件工程系”，”软件技术”）。
insert into student(sno,sname, ssex,sbirthday, sdept,speciality)
values(‘2005010203’,’张静’, ‘女’, ‘1981-3-21’,’软件工程系’,’软件技术’);
（2）插入学号为“2005010302”、姓名为“李四”的学生信息。
insert into student(sno,sname) values(‘2005010302’,’李四’);
（3）把计算机工程系的学生记录保存到表TS中（TS表已存在，表结构与student表相同）。
Create table TS like student；
Insert into TS select * from student where sdept=’计算机工程系’；
（4）将学号为“2005010202”的学生姓名改为“张华”，系别改为“电子工程系”，专业改为“电子应用技术”。
update student set sname=’张华’,sdept=’电子工程系’,speciality=’电子应用技术’where sno=’2005010202’;
（5）将“李勇”同学的专业改为“计算机信息管理”。
update student set speciality=’计算机信息管理’ where sname=’李勇’;
（6）删除学号为“2005010302”的学生记录。
delete from student where sno=’2005010302’;
（7）删除“计算机工程系”所有学生的选课记录。
delete from sc where sno in (select sno from student where sdept=’计算机工程系’);
（8）删除sc表中尚无成绩的选课记录。
delete from sc where degree is null;
（9）把“刘晨”同学的选修记录全部删除。
delete from sc where sno=(select sno from student where sname=’刘晨’);

创建表：
CREATE DATABASE IF NOT EXISTS db_bbs;
USE db_bbs;
CREATE TABLE tb_user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(30),
pwd varchar(30),
sex char(2),
email varchar(200),
tel varchar(20),
remark varchar(300),
PRIMARY KEY (id)
);
ALTER TABLE tb_user AUTO_INCREMENT=1000;
RENAME TABLE tb_user To db_bsnew；

表记录的检索：
作业一：
正确答案：
（1）查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息（用3条SQL语句）。
select from student;
select from course;
select from sc;
（2）查询所有学生的学号、姓名、性别和出生日期。
select sno,sname,ssex,sbirthday from student;
（3）查询所有课程的课程名称。
select cname from course;
（4）查询前10门课程的课程号及课程名称。
select cno,cname from course limit 10;
（5）查询所有学生的姓名及年龄。
select sname,year(curdate())-year(sbirthday) from student;
（6）查询所有年龄大于18岁的女生的学号和姓名。
select sno,sname from student
where (year(curdate())-year(sbirthday))>18 and ssex=’女’;
（7）查询所有男生的信息。
select from student where ssex=’男’;
（8）查询所有任课教师的姓名和所在系别。
select Tname,deptno from teacher;
（9）查询“电子商务”专业的学生姓名、性别和出生日期。
select sname,ssex,sbirthday from student where speciality=’电子商务’;
（10）查询student表中的所有系名。
select distinct sdept from student;
（11）查询“C01”课程的开课学期。
select cterm from teaching where cno=’c01’;
（12）查询成绩在80～90分的学生学号及课程号。
select sno,cno from sc where degree between 80 and 90;
（13）查询在1970年1月1日之前出生的男教师信息。
select from teacher where year(Tbirthday)<1970 and Tsex='男';
（14）输出有成绩的学生学号。
select distinct sno from sc where degree is not null;
（15）查询所有姓“刘”的学生信息。
select from student where sname like’刘%’;
（16）查询生源地不是山东省的学生信息。
select from student where saddress like ‘__省%’ and saddress not like ‘%山东省%’;
（17）查询成绩为79分、89分或99分的记录。
select from sc where degree in(79,89,99);
或者select from sc where degree=79 or degree=89 or degree=99;
（18）查询名字中第2个字是“小”字的男生的学生姓名和地址。
select sname,saddress from student where sname like ‘小%’ and ssex=’男’;
（19）查询名称以“计算机”开头的课程名称。
select cname from course where cname like ‘计算机/_%’ escape ‘/‘;
（20）查询计算机工程系和软件工程系的学生信息。
select from student where sdept=’计算机工程系’ or sdept=’软件工程系’;
或者 select * from student where sdept in(‘计算机工程系’,’软件工程系’);
3．思考题
（1）LIKE的通配符有哪些？分别代表什么含义？
% ：任意多个字符
_ ：单个字符
（2）知道学生的出生日期，如何求出其年龄？
用当前的年份减去学生的出生年份，其中，CURDATE( )函数返回当前的系统日期和时间， YEAR( )函数返回指定日期的年部分的整数。
（3）关键字ALL和DISTINCT有什么不同的含义？
ALL：表示输出所有记录，包括重复值
DISTINCT：表示在查询结果中去掉重复值
（4）IS能用“=”来代替吗？
不能
（5）数据的范围除了可以利用BETWEEN…AND运算符表示外，能否用其他方法表示？怎样表示？
还可以用IN来 IN()实现

作业二：
正确答案：
一、查询代码。
（1）统计有学生选修的课程的门数。
select count(distinct cno) from sc;
（2）计算“C01”课程的平均成绩。
select avg(degree) from sc where cno=’c01’;
（3）查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列。
select sno,degree from sc where cno=’c03’ order by degree desc;
（4）查询各个课程号及相应的选课人数。
select cno,count() from sc group by cno;
（5）统计每门课程的选课人数和最高分。
select cno,count(),max(degree) from sc group by cno;
（6）统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。
select sno,count(),sum(degree) from sc group by sno order by count() desc;
（7）查询选修了3门以上课程的学生学号。
select sno from sc group by sno having count()>3;
（8）查询成绩不及格的学生学号及课程号，并按成绩降序排列。
select sno,cno from sc where degree=1;
或：select distinct sno from sc;
（10）统计输出各系学生的人数。
select sdept,count() from student group by sdept;
（11）统计各系学生人数。
select sdept,count() from student group by sdept;
（12）统计各系的男、女生人数。
select sdept,ssex,count() from student group by sdept,ssex;
（13）统计各班级的学生人数。
select classno,count() from student group by classno;
（14）统计各班的男、女生人数。
select classno,ssex,count() from student group by classno,ssex;
（15）统计各系的老师人数，并按人数降序排序。
select deptno,count() from teacher group by deptno order by count() asc;
（16）统计不及格人数超过10人的课程号。
select cno,count() from sc where degree10;
（17）查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序。
select from student
where sdept=’软件工程系’ and ssex=’男’ order by sbirthday asc,saddress desc;

增加题目答案：
（1）统计输出各专业班级数。
select speciality,count(classname) from class group by speciality;
（2）统计各系年龄小于31岁的学生人数。
select sdept,count() from student where year(curdate()) -year(sbirthday)<31 group by sdept;
（3）统计各系各班级的学生人数。
select sdept,classno,count() from student group by sdept,classno;
（4）统计姓王老师的男、女教师人数。
select tsex,count(*) from teacher where tname like ‘王%’group by tsex;

二、思考题
（1）聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？
可以直接用在SELECT子句、HAVING子句、GROUP BY子句中
不能用在WHERE子句中
（2）WHERE子句与HAVING子句有何不同？
HAVING条件作用于结果组，选择满足条件的结果组；
WHERE条件作用于被查询的表，从中选择满足条件的记录。
（3）对查询结果进行重新排序时，必须指定排序方式吗？
不用，可以安照默认的排序方式
（4）在对数据进行分组统计数据时，能不能按照多个字段进行分组？
能

作业三
正确答案：
一、查询代码
（1）查询计算机工程系女学生的学生学号、姓名及考试成绩。
select a.sno,sname,degree from student a,sc b
where a.sno=b.sno and sdept=’计算机工程系’ and ssex=’女’;
（2）查询“李勇”同学所选课程的成绩。
select cno,degree from student a,sc bwhere a.sno=b.sno and sname=’李勇’;
（3）查询“李新”老师所授课程的课程名称。
select cname from teacher a,teaching b,course c
where a.Tno=b.tno and b.cno=c.cno and Tname=’李新’;
（4）查询女教师所授课程的课程号及课程名称。
select b.cno,cname from teacher a,teaching b,course c
where a.Tno=b.tno and b.cno=c.cno and Tsex=’女’;
（5）查询至少选修一门课程的女学生姓名。
select distinct sname from student a,sc b where a.sno=b.sno and ssex=’女’;
（6）查询姓“王”的学生所学的课程名称。
Select distinct cname from student a,sc b,course c
where a.sno=b.sno and b.cno=c.cno and sname like ‘王%’;
（7）查询选修“数据库”课程且成绩在80～90分的学生学号及成绩。
select a.sno,degree from sc a,course b
where a.cno=b.cno and cname like’数据库%’ and degree between 80 and 90;
（8）查询课程成绩及格的男同学的学生信息及课程号与成绩。
select a.,cno,degree from student a,sc b
where a.sno=b.sno and degree>60 and ssex=’男’;
（9）查询选修“C04”课程的学生的平均年龄。
select avg(year(curdate())-year(sbirthday)) from student a,sc b
where a.sno=b.sno and cno=’c04’;
（10）查询选修课程名为“数学”的学生学号和姓名。
select a.sno,sname from student a,sc b,course c
where a.sno=b.sno and b.cno=c.cno and cname like’%数学%’;
（11）查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。
select b.tno,sno,degree from teacher a,teaching b,sc c
where a.Tno=b.tno and b.cno=c.cno and Tname=’钱军’;
（12）查询在第3学期所开课程的课程名称及学生的成绩。
select cname,degree from teaching a,course b,sc c
where a.cno=b.cno and b.cno=c.cno and cterm=3;
（13）查询“C02”课程不及格的学生信息。
select a. from student a,sc b
where a.sno=b.sno and cno=’c02’ and degree90;
（15）查询同时选修了“C04”和“C02”课程的学生姓名和成绩。
select sname,b.cno,b.degree,c.cno,c.degree from student a,sc b, sc c
where a.sno=b.sno and b.sno=c.sno and b.cno=’c04’ and c.cno=’c02’;
二、思考题
（1）指定一个较短的别名有什么好处？
简化表名，一个查询语句里可能会多次用到别名，所以较短的别名便于应用
（2）内连接与外连接有什么区别？
内连接中，只有在两个表中匹配的行才能在结果中集中出现；
在外连接中，可以只限制一个表，而对另一个表不加限制（所有的行都出现在结果集中）

作业四：
正确答案：
（1）查询“李勇”同学所选课程的成绩。
select cno,degree from sc
where sno in (select sno from student where sname=’李勇’);
（2）查询“李新”老师所授课程的课程名称。
select cname from course
where cno=(select cno from teaching where tno=(select Tno from teacher where Tname=’李新’));
或
select cname from course,teaching where course.cno=teaching.cno and tno=(select Tno from teacher where Tname=’李新’);
（3）查询女教师所授课程的课程号及课程名称。
select cno,cname from course
where cno in (select cno from teaching where tno in(select Tno from teacher where Tsex=’女’));
或
select cno,cname from course
where cno in (select cno from teaching,teacher where teaching.tno=teacher.Tno and Tsex=’女’);
（4）查询姓“王”的学生所学的课程名称。
select cname from course
where cno in (select cno from sc where sno in (select sno from student where sname like ‘王%’) );
或
select cname from course where cno in (select cno from student,sc where student.sno=sc.sno and sname like ‘王%’) ;
（5）查询“C02”课程不及格的学生信息。
select * from student
where sno in (select sno from sc where cno=’c02’ and degreeall(select sno from student where sname=’刘晨’)
and year(curdate())-year(sbirthday)all (select sbirthday from student where ssex=’女’)
and ssex=’男’;
（14）查询成绩比该课程平均成绩高的学生的学号及成绩。
select sno,degree from sc a where
degree> (select avg(degree) from sc b where b.cno=a.cno);
（15）查询不讲授“C01”课的教师姓名。
select Tname from teacher
where Tno not in (select tno from teaching where cno=’c01’ );
（16）查询没有选修“C02”课程的学生学号及姓名。
select sno,sname from student
where sno not in (select sno from sc where cno=’c02’);
（17）查询选修了“数据库”课程的学生学号、姓名及系别。
select sno,sname,sdept from student
where sno in (select sno from sc
where cno=(select cno from course where cname like’%数据库%’));
或
select sno,sname,sdept from student
where sno in (select sno from sc,course where sc.cno=course.cno and cname like’%数据库%’);
附加．思考题
（1）IN与“=”在什么情况下作用相同？
子查询返回值唯一时
（2）使用存在量词[NOT] EXISTS的嵌套查询时，何时外层查询的WHERE条件为真？何时为假？
子查询有满足条件的记录时为真，反之，条件为假
（3）当既能用连接查询又能用嵌套查询时，应该选择哪种查询较好？为什么？
嵌套查询 执行速度比较快，执行效率高
（4）子查询一般分为几种？
两种：相关子查询和不相关子查询
（5）相关子查询的执行过程是什么？
1、子查询为外部查询的每一个元组执行一次，外部查询将子查询引用列的值传给子查询。
2、如果子查询的任何行与其匹配，外部查询则取此行放入结果表。
3、再回到1追到处理完外部表的每一行。

视图：
正确答案：
1、使用SQL语句创建、管理视图。
① 创建视图。
a．创建一个名为sc_view1的视图，从数据库gradem的sc表中查询出成绩大于90分的所有学生选修课程成绩的信息。
create view sc_view1 as select from sc where degree>90;
b．创建一个名为sc_view2的视图，从数据库gradem的sc表中查询出成绩小于80分的所有学生的学号、课程号、成绩等信息。
create view sc_view2
as select sno,cno,degree from sc where degree90 and cterm=1;
④ 视图的删除。
将视图sc_view1删除。
drop view sc_view1;
⑤ 管理视图中的数据。
a．从视图sc_view2中查询出学号为“2007030125”、课程号为“a01”的学生选修成绩的信息。
select from sc_view2 where sno=’2007030125’ and cno=’a01’;
b．将视图sc_view2中学号为“2007030122”、课程号为“a02”的成绩改为87。
update sc_view2 set degree=87 where sno=’2007030122’ and cno=’a02’;
c．从视图sc_view2中将学号为“2007030123”、课程号为“a01”的学生信息删除。
delete from sc_view2 where sno=’2007030123’ and cno=’a01’;
2、思考题
（1）向视图中插入的数据能进入到基本表中去吗？
能。能够同时改变视图所对应的表中的数据
（2）修改基本表的数据会自动反映到相应的视图中去吗？
会。视图是一个虚拟表，其内容由查询定义。视图中的数据是由一张或多张表中的数据组成的。所以说，如果你改动了基本表，如果你的视图来源于这个基本表，那视图给你呈现的结果也会随之发生变化。

触发器：
正确答案：
（1）使用SQL语句创建触发器。
①delimiter //
create trigger student_sc_insert
before insert on sc for each ROW
BEGIN
if (select sno from student where sno=new.sno) is NULL THEN
insert into student(sno) values(new.sno);
end if;
end //
②create trigger student_delete
after delete on student for each ROW
delete from sc where sno=old.sno;
或：
delimiter //
create trigger student_delete
after delete on student for each ROW
BEGIN

delete from sc where sno=old.sno;
END //
③delimiter //
create trigger student_sno
after update on student for each ROW
begin
if(new.sno!=old.sno) THEN
update sc set sno=new.sno where sno=old.sno;
end if;
end //
（2）查看、删除触发器。
① 查看触发器的定义、状态和语法信息。
show triggers;
select * from information_schema.triggers;
② 删除触发器。使用DROP TRIGGER删除student_sno触发器。
drop trigger student_sno;

存储过程与存储函数：
正确答案：
1、使用SQL语句创建存储过程和函数
(1) 创建不带参数的存储过程。
① 创建一个从student表查询班级号为“20070301”班的学生资料的存储过程proc_1，其中包括学号、姓名、性别、出生年月等。调用proc_1存储过程，观察执行结果。
delimiter //
create procedure proc_1()
begin

select sno,sname,ssex,sbirthday from student
where classno =’20070301’;
end//
call proc_1();
② 在gradem数据库中创建存储过程proc_2，要求实现如下功能：存在不及格情况的学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、系别等。调用proc_2存储过程，观察执行结果。
create procedure proc_2()
BEGIN
select a.sno,sname,ssex,cno,degree,sdept from student a,sc b
where a.sno=b.sno and degree=60 THEN ‘pass’
WHEN degree <60 THEN 'fail'
ELSE ‘null’ END ) AS ‘level’ FROM sc WHERE sno=inputsno and cno=inputcno);
end //
select func_5(‘2007010105’,’a01’)AS ‘level’ ;

2、变量管理
（1）定义一个整型局部变量iAge和可变长字符型局部变量vAddress，并分别赋值20和“中国山东”，最后输出变量的值，并要求通过注释对语句的功能进行说明。
delimiter //
create procedure aa()
BEGIN
declare iAge int;
declare vAddress LONGTEXT;
set iAge=20,vAddress=’中国山东’;
select iAge,vAddress;
END//
call aa();
（2）通过全局变量获得当前服务器进程的ID标识和MySQL服务器的版本。
select @@version ;
（3）利用存储过程或函数，求1～100的偶数和。
delimiter //
create procedure abb1()
begin
set @sum=0,@i=2;
while @i<=100 DO

BEGIN

set @sum=@sum+@i;

set @i=@i+2;

END;
end while;
select @sum;
end//
call abb1();
3、 使用SQL语句查看、修改和删除存储过程
(1) 查看存储过程。
①分别利用SHOW STATUS语句和SHOW CREATE语句查看存储过程proc_1、存储函数func_5的状态和定义。
show procedure status like ‘proc_1’;
show procedure status like ‘func_5’;
show create procedure proc_1;
show create procedure func_5;
②从information_schema.routines表中查看proc_1、存储函数func_5的信息。
SELECT FROM information_schema.Routines WHERE ROUTINE_NAME=’proc_1’;
SELECT FROM information_schema.Routines WHERE ROUTINE_NAME=’func_5’;

(2)修改存储过程和函数。
①使用ALTER PROCEDURE语句修改存储过程proc_1的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
ALTER PROCEDURE proc_1
MODIFIES SQL DATA
SQL SECURITY INVOKER;
②修改存储函数func_5的定义。将读写权限改为READS SQL DATA，并加上注释信息’FUNCTION’。
ALTER function func_5
READS SQL DATA
COMMENT ‘FUNCTION’;
(3)删除存储过程和函数。
将存储过程 proc_1和存储函数func_5删除。
DROP PROCEDURE proc_1;
DROP FUNCTION func_5;