数据库命令大全

1.Mysql服务的启动和退出；

 Net start mysql57
Net stop mysql57

2.使用命令登录MYSQL

Mysql -u root -p 

3.查看Mysql数据库的存储位置(路径)

Show variables like ‘datadir’;

4.查看当前系统中的所有数据库。

Show databases;

5.查看系统中的数据库引擎

Show engines;

6.创建两个数据库JXGLDB和MYTESTDB

Create database if not exists JXGLDB;
Create database if not exists MYTESTDB;

7.打开数据库JXGLDB；

Use JXGLDB;

8.修改数据库MYTESTDB；

Alter database MYTESTDB character set big5 collate big5_chinese_ci;

9.显示数据库JXGLDB的结构信息；

Show create database JXGLDB;

10.删除数据库MYTESTDB。

Drop database if exists MYTESTDB;

11.将字符集设置为’gbk’；

set names ‘gbk’

12.创建teacherinfo表

CREATE TABLE teacherinfo(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(4) NOT NULL UNIQUE,
Name VARCHAR(20) NOT NULL,
Sex VARCHAR(4) NOT NULL,
Birthday DATETIME,
Address VARCHAR(50));

13.将 teacherinfo表的tname字段的数据类型改为 VARCHAR（30），查看teacherinfo的结构；

ALTER TABLE teacherinfo MODIFY name VARCHAR (30) NOT NULL;    DESCRIBE teacherinfo;

14.将 birthday字段的位置改到name字段的前面，查看teacherinfo的结构；

ALTER TABLE teacherinfo MODIFY birthday DATETIME AFTER name;
DESCRIBE teacherinfo;

15.将tno字段改名为t_id，查看teacherinfo的结构；

ALTER TABLE teacherinfo CHANGE num t_id INT(10) NOT NULL;
 DESCRIBE teacherinfo;

16.将 teacherinfo表的taddress字段删除，查看teacherinfo的结构；

ALTER TABLE teacherinfo Drop address;
DESCRIBE teacherinfo;

17.在teacherinfo表中增加名为 twages的字段，数据类型为FLOAT，查看teacherinfo的结构；

ALTER TABLE teacherinfo ADD wages FLOAT;
 DESCRIBE teacherinfo;

18.将 teacherinfo表改名为 teacherinfo1，查看数据库表；

ALTER TABLE teacherinfo RENAME to teacherinfo_Info;
show tables;

19.将 teacherinfo1表的存储引更改为MyISAM类型；

ALTER TABLE teacherinfo_Info ENGINE=MyISAM;

20.显示创建teacherinfo1表结构；

show create table teacherinfo_Info;

21.复制teacherinfo1到表tinfo中，查看所有数据库表；

create table tinfo like teacherinfo_Info;
show tables;

22.删除tinfo表，查看所有数据库表。

drop table if exists tinfo;
 show tables;

23.创建数据库表teacher(使用复制teacherinfo1表的方法)；

create table teacher like teacherinfo_info;

24.向teacher表中插入任意一条记录（教工号为1000）；

INSERT INTO teacher VALUES(‘0001','1000','张龙','男','1984-11-08','1000');

25.向teacher表中同时插入任意三条记录（教工号分别为1001，1002，1003）；

INSERT INTO teacher (num,name,sex,birthday,address)
 VALUES(‘0002','1001','李梅','女','1970-01-21','8000'), 
(‘0003','1002','王一丰','男','1976-10-30','10000'),
(‘0004','1003','闫妮','女','1975-05-27','15000');
 Select * from teacher;

26.更新教工号为1003的记录，将生日（birthday）改为“1982-11-08”，并查看更新结果；

UPDATE teacher SET birthday='1984-11-08' WHERE t_id=’1003’;
 select * from teacher WHERE num=’1003’; 

27.将性别（sex）为“男”的记录的家庭住址（address）都变 为“北京市朝阳区”，并查看更新结果；

UPDATE teacher SET wages='10000' WHERE sex='男';
select * from teacher;

28.删除教工号（num）为1002的记录删除,并查看删除结果；

DELETE FROM teacherInfo WHERE num=’1002’;
select * from teacherInfo;

29.清空teacher数据库表数据，并查看结果；

truncate table teacherinfo;
 select * from teacherInfo;

30.在score表设置外键约束，然后插入数据，观察插入数据结果；

lter table score add constraint fk_st_score foreign key(sno) references student(sno);

31.将student表中sname字段设置为非空约束，观察插入数据结果；

alter table student modify sname VARchar(8) not null;
 insert into student 
values ('8008', NULL, '男',’2001-10-03’,'13302900008');

32.对student表的sphone添加唯一约束，观察插入数据结果；

alter table student add unique (sphone); 
 Insert into student  values ('8009', '徐克', '男','13302900008');

33.设置coures的cno为自增，观察插入数据结果；

alter table coures change cno id int not null auto_increment; 
INSERT INTO coures(
cno,cname,type, petiod ,Credit) 
VALUES(‘111118’,'数据库原理及应用','1',64,3); 

34.删除外键约束；

 alter table score drop foreign key fk_st_score;

35.便于按电话进行查询，为student表的sphone列上建立一个降序普通索引sphone_index，并查看结果。

create index phone_index  on student(sphone desc);
show index from student;

36.在course表的cname列上建立一个唯一性索引cname_index，并查看结果；

 create unique index cname_index on coures(cname);

37.在score表的sno和cno列上建立一个复合索引score_index，并查看结果；

create index score_index on score(sno,cno);

38.创建名为staff的员工表（结构如下表）和部门表（结构如下），并创建no字段为主索引，为name字段建立一个唯一性索引tname_index，并查看结果；
Staff表：

create table stuff(
    -> no char(3) PRIMARY KEY comment'员工编号',
    -> name char(6) unique key comment '员工姓名',
    -> dno char(2) comment'部门编号',
    -> major char(6) comment'所学专业',
    -> wages int(4) comment'工资'
-> );
create unique index tname_index on stuff(name);

39.创建名为dept的部门表；

create table dept(
    -> dno char (2) primary key comment'部门编号',
    -> dname char(6) comment'员工姓名'
-> );
 alter table dept add key (dname(2));

40.为staff表插入任意4条记录，其中dno的值分别为“11”，“12”，“13”(必有1条记录的dno有重复值)；其中2条记录的major为空；

insert into stuff values(
    -> '01','张三','11'计科',20000),
    -> ('02','李四','12',null,7800),
    -> ('03','吴一','12','大数据',19000),
    -> ('04','小李','13',null,9000);

41. 为dept表插入信息（11，行政部；12，技术部；13，销售部；14，财务部；15，后勤部），显示记录。

insert into dept values(
    -> '11','行政部'),
    -> ('12','技术部'),
-> ('13','销售部');
->(‘14’,’财务部’);
->(‘15’,’后勤部’);

42.查询staff表中都有哪几个部门；

select  dname from dept;

43.查询staff表中major是空的记录；

select * from stuff where major is NULL;

44.统计工资在3000元以上的员工信息；

select *from stuff where wages>3000;

45.从staff表中查询工资在2500到3500之间的员工的信息。可以通过两种方式来查询；

select *from stuff where wages>2500 and wages<3500;
select *from stuff where wages  between 2500 and 3500;

46.查询每个部门有多少员工（先按部门号进行分组，然后用COUNT()函数来计算每组的人数）；

 select dno ,count(no) from stuff group by dno;

47.查询每个部门的最高工资。先按部门号进行分组，然后用MAX()函数来计算最大值；

select dno, max(wages)from stuff group by dno;

48.14.计算每个部门的总工资。先按部门进行分组，然后用SUM()函数来求和；

 select dno as'部门编号',sum(wages) as '总工资' from stuff group by dno;

49.查询staff表，按照工资从高到低的顺序排列；

select *from stuff order  by wages desc;

50.查询所学专业为计算机类的员工姓名、所在部门号；

select name,dno from where major like '计%';

51.查询所有员工的部门信息；

select stuff.name,dept.dname from stuff,dept where stuff.dno=dept.dno;
 select stuff.name,dept.dname from dept inner join stuff on dept.dno=stuff.dno;

52.从dept表和staff表中查询出部门号，然后使用UNION合并查询结果；

select dno from stuff union select dno from dept;

53.查询行政部和销售部员工的信息。

select *from stuff
    -> where dno=any(
    -> select dno from dept
-> where dname in('行政部','销售部'));

54.用左连接的方式查询dept表和staff表；

select *from stuff left join dept on stuff.dno=dept.dno;

55.创建视图sd-view1，包含字段 staff.no, staff.name, dept.dname,staff.major, staff.wages,显示视图数据；

 create view sb_view1
    -> as select stuff.no,stuff.name,dept.dname,stuff.major,stuff.wages
    -> from dept,stuff where stuff.dno=dept.dno;

56.修改视图sd-view1中销售部李老师的专业为‘数据科学与技术’，分别查看视图和staff表的记录；

 update sb_view1 set major='大数据'where name='小李'and dname='销售部
select *from sb_view1;
select *from stuff;

存储过程与函数的创建管理
57.把结束符改为##，并执行 select 1+1##；

delimiter ##
select 1+1##

58.定义一个存储过程，作用是输出连接多个字符串后的值；

 Create	procedure	concat()	select	concat(@strr1,@str);
call concat();
set @strr1='aaa',@str2='bbb';
call concat();

59.创建存储过程proc_stu，从student表中检索出所有电话以133开头的学生的学号、姓名、出生日期和电话等信息；

create procedure proc_stu()
    -> reads sql data
    -> begin
    -> select sno,sname,sdate,sphone from student
    -> where sphone like'133%' order by sno;
    -> end **

60.创建存储过程avg_score，输入课程号后，统计该课程的平均成绩。并调用过程；

delimiter **
mysql> create procedure avg_score(in c_no char(6))
    -> begin
    -> select cno,avg(grade) from score
    -> where con=c_no;
    -> end **

61.创建函数num_stu( )，统计学生的总人数；

create function num_stu()
    -> returns integer
    -> begin
    -> return (select count(*) from student);
    -> end ##

62.创建存储过程stu_score，统计指定同学的考试门数；

create procedure stu_score(in s_no char (4),out count_num int)
    -> reads sql data
    -> begin
    -> select count(*) into count_num from score
    -> where sno=s_no;
    -> end **

63.创建存储过程do_query，输入学号，查看该生的成绩高于85分的科目数，如果超过2科，则输出very good!，并输出该生的成绩单，否则输出come on!；

Mysql>delimiter **
mysql>create procedure  do_query(in s_no char(4), out str char(12))
begin
declare  AA  tinyint  default 0;
select  count(*)  into  AA  from  score where  sno= s_no  and  grade>85;
if  AA>2  then
begin
set  str='very good! ';
select * from  score  where sno = s_no;
end;
elseif   AA<2 then
set   str='come on! ';
end if;
end **
mysql>delimiter ;

64.查看以’pro’打头的存储过程和存储过程proc_stu的定义；

show procedure  status like 'pro%';
show  create procedure pro_stu;

65.修改存储过程do_insert()的定义，将读写权限改为modifies sql data，并指明调用者可以执行；

alter procedure do_query
    -> modifies sql data sql security invoker;

66.删除存储过程do_ query ()的代码。

drop procedure do_query;