计算机二级MySQL基本操作题(一）修改表，分组查询，等值连接查询，创建视图，创建用户并授权。

第一题

学生数据库 kwgl
学生表 student(sid, sname, score, deptno)
学号、姓名、成绩、系别编号
系别表 dept(deptno, deptname)
系别编号、系名称

1. 创建表S
   use kwgl;
   create table S
   (
   SNO int primary key auto_increment,
   SName char(10),
   Sex char(1),
   Age int
   );

创建表语法
create table 表名
(
字段名1 数据类型 primary key auto_increment,
字段名2 数据类型,
字段名3 数据类型
);
primary key 表示主键约束，
主键约束写在最后一行为： primary key(字段名)。
auto_increment 表示自增约束。

2. 在表student的deptno字段创建索引 in_stu
   create index in_stu on student(deptno asc);

创建索引语法
create index 索引名 on 表名 (字段名 asc | desc);
asc 表示升序，desc 表示降序。

3. 查询表student 不同系别学生的平均成绩
   select deptno as “系别”, avg(score) as “平均成绩”
   from student
   group by deptno;

±------±---------+
| 系别 | 平均成绩 |
±------±---------+
| 10001 | 82.0000 |
| 10002 | 68.0000 |
| 10003 | 87.0000 |
| 10004 | 80.0000 |
| 10005 | 70.0000 |
±------±---------+

分组查询语法
select 字段名1 as “别名1”, avg(字段名2) as “别名2”
from 表名
group by 字段名；
as 表示给字段取别名，别名写在一对单引号或双引号内。
avg() 表示平均数函数。
group by 表示分组命令。

4. 查询“数学系”学生的学号、姓名和成绩
   use kwgl;
   select sid, sname, score
   from student s, dept d
   where s.deptno=d.deptno
   and d.deptname=“数学系”;

±---------±-------±------+
| sid | sname | score |
±---------±-------±------+
| 10130105 | 刘三波 | 85 |
| 10130108 | 蓝剑生 | 75 |
| 10130109 | 牛小五 | 65 |
| 10130110 | 施英杰 | 55 |
±---------±-------±------+

等值连接查询
表名1.字段名 = 表名2.字段名
两张表取别名后：别名1.字段名 = 别名2.字段名
查询字段正好有连接字段，任选一个：别名1.字段名

5. 创建用户“wang”，密码为“test1234”,IP:192.168.2.12
   create user “wang”@“192.168.2.12”
   identified by “test1234”;
   flush privileges;

创建用户语法
create user '用户名‘@’主机名或地址‘ identified by ‘密码’;
flush privileges; 表示刷新权限表，创建带密码的用户或者给用户授权都需要刷新权限表。

第二题

企业数据库 db_emp
职工表 tb_employee(eno, ename, age, title, salary, deptno)
部门表 tb_dept(deptno, dname, manager, telephone)

1. 添加公关部
   use db_emp;
   insert into tb_dept(deptno, dname, manager, telephone)
   values(“D4”, “公关部”, “Liming”, “010-82953306”);

添加数据语法
insert into 表名 (字段名1, 字段名2, …)
values(值1, 值2, …);
如果表名后没有字段名，输入值的顺序必须按表字段的顺序输入，没有值的字段输入null。

2. 设置默认工资3500
   alter table tb_employee alter column salary
   set default “3500”;

修改字段的默认值语法
alter table 表名 alter column 字段名 set default “值”;

3. 查询销售部员工总人数
   select count(*) as “总人数”
   from tb_employee
   where deptno=(select deptno from tb_dept where dname=“销售部”);

4. 创建视图v_emp
   create view v_emp
   as select eno, ename, age, salary
   from tb_employee e, tb_dept d
   where e.deptno=d.deptno and dname=“采购部”;

创建视图语法
create view 视图名
as select 字段名 from 表名 where 条件表达式；

5. 创建用户Yaoming
   create user “Yaoming”@“localhost” identified by “abc123”;
   flush privileges;

第三题

商场数据库 db_mall
商品表 tb_commodity(cno, cname, ctype, origin, birth, price, desc1)

1. 计算电视机的平均价格 AveragePrice
   use db_mall;
   select avg(price) as “AveragePrice”
   from tb_commodity
   where cname=“电视机”;

2. 删除商品表的 desc1字段
   alter table tb_commodity drop desc1;

3. 添加商品信息
   insert into tb_commodity
   values(null, “钢笔”, “文具”, “上海”, “2012-12-25”, “25”);

4. 创建北京产地的视图 v_bjcommodity
   create view v_bjcommodity
   as select * from tb_commodity where origin=“北京”;

5. 创建用户client并授权
   grant select(cno, cname) on db_mall.tb_commodity to “client”@“localhost”;
   flush privileges;

授权用户权限语法
grant 授权的操作(字段名) on 数据库名.表名 to “用户名”@“主机名或IP地址”;

第四题

学生数据库 db_student
学生表 tb_student(sno, sname, sage, smajor）
课程成绩表 tb_score(sno,cname,grade)

1. 给学生表添加字段
   use db_student;
   alter table tb_student add column ssex char(1) default “M”;

column 可省略。

2. 把学号100的学生专业改为“计算机”
   update tb_student set smajor=“计算机”
   where sno=100;

3. 创建视图v_avg(cname, caverage)
   create view v_avg(cname, caverage)
   as select cname, avg(grade)
   from tb_score
   group by cname;

4. 创建唯一索引 idx_stu
   alter table tb_student add unique index idx_stu(sno);

或者
create unique index idx_stu on tb_student(sno);

查看索引
show index from tb_student;

5. 创建用户newuser并授权
   grant select on tb_student to “newuser”@“localhost”;
   flush privileges;

第五题

企业数据库 db_yggl
部门表 tb_dept(deptno, deptname, manager, office, telephone)
职工表 tb_emp(eno, name, sex, birth, education, worktime, title, salary telephone, deptno)

1. 创建降序索引 idx_dept
   use db_yggl;
   alter table tb_dept add unique index idx_dept(deptname desc);

或者
create unique index idx_dept on tb_dept(deptname desc);

2. 添加记录
   insert into tb_dept(deptno, deptname)
   values(“D4”, “营销部”);

3. 查询员工姓名和年龄
   select name as “姓名”, 2014-birth as “年龄”
   from tb_emp;

as可省略

4. 创建视图v_dept
   create view v_dept
   as select manager, name, education, salary
   from tb_dept d, tb_emp e
   where d.deptno=e.deptno and deptname=“市场部”;

5. 创建用户stud并授权
   grant select on tb_emp to “stud”@“localhost”;
   flush privileges;