信息技术产品与数据库操作实战-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43916586/article/details/119896772

小作业：

作业1：

#创建商品表：
CREATE TABLE product(
	pid INT,
	pname VARCHAR(20),
	price DOUBLE,
	category_id VARCHAR(32) -- 商品分类
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');

INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);




SELECT * FROM product WHERE pname='花花公子';

SELECT pname FROM product WHERE price=800;

SELECT pname FROM product WHERE price!=800;

SELECT * FROM product WHERE price>60;

SELECT pname FROM product WHERE price BETWEEN 200 AND 1000;

SELECT pname FROM product WHERE price=200 OR price=800;

SELECT pname FROM product WHERE pname LIKE '%霸%';

SELECT pname FROM product WHERE pname LIKE '香%';

SELECT pname FROM product WHERE pname LIKE '_想%';
-- 查询没有分类的商品
SELECT pname FROM product WHERE category_id IS NULL;
-- 查询有分类的商品
SELECT pname FROM product WHERE category_id IS NOT NULL;

作业2

CREATE DATABASE webdb2 CHARACTER SET utf8 ;

CREATE TABLE users 
(	
	id INT,
	age INT,
	NAME VARCHAR(20),
	birthday DATE,
	salary DOUBLE
);

INSERT INTO users (id,NAME,age,birthday,salary) VALUES (1,'张星',21,'2000-11-12',12000);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (2,'张三',29,'1987-11-12',8000);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (3,'李四',22,'2020-11-12',1320);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (4,'王五',33,'2001-11-12',600);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (5,'赵六',19,'2003-11-12',1200);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (6,'李白',28,'2014-11-12',3000);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (7,'杜甫',21,'2000-11-12',NULL);
INSERT INTO users (id,NAME,age,birthday,salary) VALUES (8,'小明',25,'2010-12-12',NULL);

SELECT salary AS "薪资", salary*12 AS "年薪" ,NAME AS "姓名" FROM users;

-- 查询如果员工没有薪资
SELECT *,IFNULL(salary,"没有薪资") FROM users ORDER BY salary DESC;

SELECT NAME FROM users WHERE NAME LIKE '张%' AND salary > 900;

SELECT * FROM users WHERE birthday>'1990-1-1';

SELECT * FROM users WHERE salary<1200 OR salary>5000;

SELECT * FROM users WHERE NAME LIKE '%明';

作业3

DROP DATABASE itcast_demo;
DELETE FROM users WHERE age=22;


CREATE DATABASE IF NOT EXISTS db_student;

CREATE TABLE student
(
	id INT(10) PRIMARY KEY AUTO_INCREMENT UNIQUE ,
	NAME VARCHAR(20) NOT NULL,
	sex VARCHAR(4),
	birth YEAR,
	department VARCHAR(20) NOT NULL,
	address VARCHAR(50)
);

CREATE TABLE score
(
	id INT(10) PRIMARY KEY AUTO_INCREMENT UNIQUE ,
	stu_id INT(10) NOT NULL,
	c_name VARCHAR(20),
	grade INT(10)
);

DESC student;

# 删除表
DROP TABLES student,score;

ALTER TABLE student CHANGE sex gender ;

ALTER TABLE score ADD remark VARCHAR(500);

INSERT INTO student (id ,NAME ,gender,birth,department,address)
VALUES (901 , "张老大"  , '男','1985',"计算机系","北京市海淀区"),
(902 , "张老二" ,  '男' , '1986' , "中文系"  ,   "北京市昌平区"),
(903 , "张三"  ,  '女' , '1990' , "中文系"   ,  "湖南省永州市 " ),
(904 , "李四"  ,  '男' , '1990' , "英语系"    , "辽宁省阜新市  "),
(905 , "王五"  ,  '女' , '1991' , "英语系"    , "福建省厦门市 " ),
(906 , "王六"  ,  '男' , '1988' , "计算机系"  , "湖南省衡阳市"  );


INSERT INTO score(stu_id,c_name,grade)
VALUES(901  , "计算机  " ,98),
(901  , "英语 " ,80),
(902  , "计算机 ", 65),
(902  , "中文 " ,88),
(903  ,"中文  " ,95),
(904  , "计算机 ", 70),
(904  , "英语 " ,92),
(905  , "英语 " ,94),
(906  , "计算机 ", 90),
(906 ,  "英语 " ,  85);

SELECT * FROM student;

SELECT * FROM student  LIMIT 2,4;

SELECT id,NAME,department FROM student;

SELECT * FROM student WHERE department="计算机系" OR department="英语系";

SELECT * FROM student WHERE birth BETWEEN '2003' AND '1999';

SELECT department,COUNT(department) AS "人数" FROM student  GROUP BY department;

SELECT c_name AS "科目",MAX(grade)AS "最高分" FROM score GROUP BY c_name;

SELECT c_name AS "科目",AVG(grade)AS "平均分" FROM score GROUP BY c_name;

SELECT id,stu_id,c_name,grade FROM score WHERE c_name="计算机" ORDER BY grade DESC;

作业3：

CREATE DATABASE itcast_demo;

CREATE TABLE t_user
(
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	sex CHAR(2),
	NAME VARCHAR(20),
	PASSWORD VARCHAR(10)
);

INSERT INTO t_user(sex,NAME,PASSWORD) 
VALUES('男',"zhangsan","123123"),
('女',"lisi","000000"),
('男',"wangwu","000000");

UPDATE t_user SET PASSWORD="321321" WHERE NAME="lisi";

DELETE FROM t_user WHERE PASSWORD="000000";

SELECT NAME FROM t_user WHERE sex='男';

SELECT sex,COUNT(sex) FROM t_user GROUP BY sex; 

CREATE TABLE t_user_count
(
	sex CHAR(2),
	num INT(2)
);

#蠕虫复制
INSERT INTO t_user_count SELECT sex,COUNT(sex) FROM t_user GROUP BY sex;

作业4：

CREATE DATABASE IF NOT EXISTS dp_emp;


CREATE TABLE emp
(
	empno INT PRIMARY KEY,
	empname VARCHAR(10) NOT  NULL,
	job VARCHAR(10) NOT  NULL,
	manager INT,
	hiredate DATE,
	salary DOUBLE,
	comm DOUBLE,
	deptno INT
);

CREATE TABLE dept
(
	deptno INT PRIMARY KEY,
	dname VARCHAR(20) NOT  NULL,
	loc VARCHAR(20) 
);

CREATE TABLE salgrade
(
	grade INT PRIMARY KEY,
	losal DOUBLE ,
	hisal DOUBLE
);

INSERT INTO emp(empno,empname,job,manager,hiredate,salary,comm,deptno)
VALUES(369,"SMITH",  "CLERK", 7902 ,  '1980-12-17',800	,NULL,20),
(7499,"ALLEN ",  "SALESMAN", 7698 ,  '1981-02-20',1600,300, 30),
(7521,"WARD ",  "SALESMAN", 7698 ,  '1981-02-22',1250,500, 30),
(7566,"JONES ",  "MANAGER ", 7839 ,  '1981-04-02',2975,NULL,20),
(7654,"MARTIN",  "SALESMAN", 7698 ,  '1981-09-28',1250,1400,30),
(7698,"BLAKE ",  "MANAGER ", 7839 ,  '1981-05-01',2850,NULL,30),
(7782,"CLARK ",  "MANAGER ", 7839 ,  '1981-06-09',2450,NULL,10),
(7788,"SCOTT ",  "ANALYST ", 7566 ,  '1987-07-03',3000,NULL,20),
(7839,"KING ", "PRESIDENT",NULL ,  '1981-11-17',5000,NULL, 10),
(7844,"TURNER",  "SALESMAN", 7698 ,  '1981-09-08',1500,NULL,30),
(7876,"ADAMS ",  "CLERK", 7788 ,  '1987-07-13',1100,NULL,20),
(7900,"JAMES ",  "CLERK", 7698 ,  '1981-12-03',950 ,NULL,30),
(7902,"FORD ",  "ANALYST ", 7566 ,  '1981-12-03',3000,NULL, 20),
(7934,"MILLER",  "CLERK", 7782 ,  '1981-01-23',1300,NULL,10);

INSERT INTO salgrade(grade,losal,hisal)
VALUES(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);


INSERT INTO dept(deptno,dname,loc)
VALUES(10,"ACCOUNTING",	"NEW YORK"),
(20,"RESEARCH","DALLAS"),
(30,"SALES  ","CHICAGO "),
(40,"OPERATIONS","BOSTON  ");


SELECT * FROM emp WHERE deptno=30;

SELECT empno,empname,deptno FROM emp WHERE job="CLERK";

SELECT * FROM emp WHERE comm>salary*0.6;

SELECT * FROM emp WHERE comm<100 OR comm IS NULL;

SELECT * FROM emp ORDER BY job DESC,salary ASC;

SELECT job,COUNT(job) AS numb FROM emp GROUP BY job ORDER BY numb DESC; 

SELECT job, MAX(salary) AS maxsal FROM emp GROUP BY job ORDER BY maxsal ASC;

2021-08-24作业