我的学习空间

-- 1. 添加字段 ssex，数据类型 CHAR(1)，默认值 'M'
ALTER TABLE tb_student ADD ssex CHAR(1) DEFAULT 'M';

-- 2. 将学号为 100 的学生专业改为“计算机”
UPDATE tb_student SET smajor = '计算机' WHERE sno = 100;

-- 3. 创建视图 v_avg，包含课程名称及平均成绩
CREATE VIEW v_avg(cname, caverage) AS
SELECT cname, AVG(grade) FROM tb_score GROUP BY cname;

-- 4. 在 tb_student 表上创建学号的唯一性索引 idx_stu
CREATE UNIQUE INDEX idx_stu ON tb_student(sno);

-- 5. 创建用户 newuser，并授予对 tb_student 表的 SELECT 权限
CREATE USER 'newuser'@'localhost' IDENTIFIED BY '';
GRANT SELECT ON db_student.tb_student TO 'newuser'@'localhost';

-- 1. 计算北京产的电机的价格总和，结果存入 sj11.txt
SELECT SUM(price) AS total FROM tb_commodity WHERE origin = '北京' AND cname = '电机';

-- 2. 删除 desc1 字段
ALTER TABLE tb_commodity DROP desc1;

-- 3. 插入一行数据（商品号 cno 是自增或主键？题目未指定，若需手动添加请补充 cno 值）
INSERT INTO tb_commodity (cname, ctype, origin, birth, price) 
VALUES ('钢笔', '文具', '上海', '2012-12-25', 25);

-- 4. 创建视图 v_bjcommodity（产地为北京）
CREATE VIEW v_bjcommodity AS
SELECT * FROM tb_commodity WHERE origin = '北京';

-- 5. 创建用户 client，授予对 cno 和 cname 的 SELECT 权限
CREATE USER 'client'@'localhost' IDENTIFIED BY '';
GRANT SELECT (cno, cname) ON db_mall.tb_commodity TO 'client'@'localhost';

-- 1. 新增“公关部”记录
INSERT INTO tb_dept (deptno, dname, manager, telephone) 
VALUES ('D4', '公关部', 'Liming', '010-82953306');

-- 2. 修改 salary 字段默认值为 3500
ALTER TABLE tb_employee ALTER COLUMN salary SET DEFAULT 3500;

-- 3. 查询“销售部”员工总人数，存入 sj13.txt
SELECT COUNT(*) AS 总人数
FROM tb_employee e
INNER JOIN tb_dept d ON e.deptno = d.deptno
WHERE d.dname = '销售部';

-- 4. 为“采购部”创建视图 v_emp
CREATE VIEW v_emp AS
SELECT eno, ename, age, salary
FROM tb_employee
WHERE deptno = (SELECT deptno FROM tb_dept WHERE dname = '采购部');

-- 5. 创建用户 Yaoming
CREATE USER 'Yaoming'@'localhost' IDENTIFIED BY 'abc123';

-- 1. 创建 Dept1 表，并建立主外键关联（自关联）
CREATE TABLE Dept1 (
    deptno INT PRIMARY KEY,
    dname CHAR(20),
    higherdeptno INT DEFAULT NULL,
    CONSTRAINT fk_higher FOREIGN KEY (higherdeptno) REFERENCES Dept1(deptno)
);

-- 2. 在 Dept2 表中插入记录
INSERT INTO Dept2 (deptno, dname, higherdeptno) VALUES (9, 'newdept', NULL);

-- 3. 创建视图 SJAVA（选修“JAVA”课程的学生）
CREATE VIEW SJAVA AS
SELECT S.sno, S.sname, SC.grade
FROM S
JOIN SC ON S.sno = SC.sno
JOIN C ON SC.cno = C.cno
WHERE C.cname = 'JAVA';

-- 4. 将学生“李红”选修的“DB”课程成绩改为 90
UPDATE SC
SET grade = 90
WHERE sno = (SELECT sno FROM S WHERE sname = '李红')
  AND cno = (SELECT cno FROM C WHERE cname = 'DB');

-- 5. 创建用户 backupdb
CREATE USER 'backupdb'@'localhost' IDENTIFIED BY 'backup';