7.22mysql

最新推荐文章于 2025-06-10 09:25:31 发布

咋说话呢？

最新推荐文章于 2025-06-10 09:25:31 发布

阅读量110

点赞数 1

文章标签： mysql adb android

本文链接：https://blog.youkuaiyun.com/weixin_70890083/article/details/131872690

版权

练习二创建company数据库，在数据库中根据以下图示创建表，表结构如下，并插入以下数据，完成下面的sql。

mysql> CREATE DATABASES company;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASES company' at line 1
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.01 sec)

mysql> USE company;
Database changed
mysql> CREATE TABLE dept(DEPTNO default 1 int comment'部门编号',DNAME default 2 varchar(14) comment '部门名称',LOC default 3 varchar(14) comment '部门地点');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default 1 int comment'部门编号',DNAME default 2 varchar(14) comment '部门\00E5' at line 1
mysql> CREATE TABLE dept(DEPTNO int comment'部门编号',DNAME varchar(14) comment '部门名称',LOC varchar(14) comment '部门地点');
Query OK, 0 rows affected (0.05 sec)

mysql> show table dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dept' at line 1
mysql> decs dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decs dept' at line 1
mysql> decs table dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decs table dept' at line 1
mysql> DESC dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | YES | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(14) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> CREATE TABLE emp(EMPNO int comment '员工编号',ENAME varchar(10) comment '员工姓名',JOB varchar(9) comment '员工职位',MGR int comment '领导编号',HIREDATE date comment '入职日期',SAL double comment '工资',COMM double comment '奖金',DEPTNO int comment '部门编号');
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE salgrade(GRADE int comment '工资等级',LOSAL int comment '最低工资',HISAL int comment '最高工资');
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7369,'SMITH','CLERK
',7902,'1980-01-17',800,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7521,'WARD','SALESMAN',7698,'1980-02-22',1250,500,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7566,'JONES','MANAGER',7839,'1980-04-02',2975,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM emp
-> ^C
mysql> SELECT * FROM emp;
+-------+--------+----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-01-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1980-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1980-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
+-------+--------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)

mysql> UPDATE emp SET HIREDATE='1981-02-22' WHERE EMPNO=7521;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE emp SET HIREDATE = '1981-04-02' WHERE EMPNO=7566;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7698,'BLAKE','MANAG
ER',7893,'1981-05-01',2850,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7788,'SCOYY','ANALYST',7566,'1987-07-13',3000,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) VALUES(7839,'KING','PRESIDENT','1981-11-17',5000,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7844,'TURNER',SALESMAN',7698,'1981-09-08',1500,0,10);
'> '
-> ^C
mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-01-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 | 7893 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOYY | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 10 |
| 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 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

mysql> INSERT INTO salgrade VALUES(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE products(product_id int comment '商品编号',product_name varchar(14) comment '商品名称',category varchar(14) comment '商品类别',price double comment '商品价格');
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO products VALUES(1,'Laptop','Electronics',1000.00),(2,'Smartphone','Electronics',800.00),(3,'Headphones','Accessories',100.00),(4,'T-shirt','Clothing',25.00),(5,'Mouse','Electronics',20.00);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from products;
+------------+--------------+-------------+-------+
| product_id | product_name | category | price |
+------------+--------------+-------------+-------+
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Headphones | Accessories | 100 |
| 4 | T-shirt | Clothing | 25 |
| 5 | Mouse | Electronics | 20 |
+------------+--------------+-------------+-------+
5 rows in set (0.00 sec)

mysql> -- 1.修改emp表中sal字段为salary
mysql> ALTER TABLE emp CHANGE sal salary double;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> -- 2.查找年薪在20000到30000之间的所有员工信息并按照工资降序显示
mysql> select * from emp where salary*12 >20000 and salary*12 < 30000 ORDER BY salary desc;

+-------+-------+---------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+-------+---------+------+------------+--------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
+-------+-------+---------+------+------------+--------+------+--------+
1 row in set (0.00 sec)

mysql> -- 3.查找员工姓名中包含'A'的所有员工信息
mysql> select * from emp where ENAME LIKE '%A%';
+-------+--------+----------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+--------+----------+------+------------+--------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7893 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+--------+----------+------+------------+--------+------+--------+
7 rows in set (0.00 sec)

mysql> -- 4.查找所有员工姓名中包含'A'及'E'的员工信息
mysql> select * from emp where ENAME LIKE '%A%E%';
+-------+-------+----------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+-------+----------+------+------------+--------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7698 | BLAKE | MANAGER | 7893 | 1981-05-01 | 2850 | NULL | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+-------+----------+------+------------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> -- 5.查找所有的职位为SALESMAN的员工信息
mysql> select * from emp where JOB = 'SALESMAN';
+-------+--------+----------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+--------+----------+------+------------+--------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 10 |
+-------+--------+----------+------+------------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> -- 6.将工资低于2000的员工工资涨薪200
mysql> UPDATE emp SET salary = salary + 200 where salary < 2000;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0

mysql> -- 8.查询没有奖金的所有员工信息
mysql> select * from emp where COMM is NULL;
+-------+--------+-----------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+--------+-----------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-01-17 | 1000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7893 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOYY | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1300 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1150 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1500 | NULL | 10 |
+-------+--------+-----------+------+------------+--------+------+--------+
10 rows in set (0.00 sec)

mysql> -- 9.将部门表中的40部门的地址修改成'xian'
mysql> update dept set LOC = 'xian' where DEPTNO = 40;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> -- 10.假设李华的工资是2000，请查询出他的工资等级
mysql> select GRADE from salgrade where HISAL=2000;
+-------+
| GRADE |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)

mysql> -- 11.将MILLER的入职日期修改为1982年2月23日
mysql> UPDATE emp SET HIREDATE= '1982-02-23' WHERE ENAME = 'MILLER';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp;
+-------+--------+-----------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
+-------+--------+-----------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-01-17 | 1000 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1800 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1450 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1450 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7893 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOYY | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1700 | 0 | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1300 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1150 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-02-23 | 1500 | NULL | 10 |
+-------+--------+-----------+------+------------+--------+------+--------+
14 rows in set (0.00 sec)

mysql> -- 2.写一条SQL查询语句，找出价格高于等于100.00的产品信息。
mysql> select * from products where price > 100 and price = 100;
Empty set (0.00 sec)

mysql> -- 3.写一条SQL查询语句，找出价格在20.00到1000.00之间的产品信息。
mysql> select * from products where price between 200 and 100;
Empty set (0.00 sec)

mysql> select * from products where price between 20 and 1000;
+------------+--------------+-------------+-------+
| product_id | product_name | category | price |
+------------+--------------+-------------+-------+
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Headphones | Accessories | 100 |
| 4 | T-shirt | Clothing | 25 |
| 5 | Mouse | Electronics | 20 |
+------------+--------------+-------------+-------+
5 rows in set (0.00 sec)

mysql> -- 4.对于"products"表，有一个新的需求：将"category"列改名为"product_category"
mysql> ALTER TABLE products CHANGE category product_category varchar(14);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from products;
+------------+--------------+------------------+-------+
| product_id | product_name | product_category | price |
+------------+--------------+------------------+-------+
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Headphones | Accessories | 100 |
| 4 | T-shirt | Clothing | 25 |
| 5 | Mouse | Electronics | 20 |
+------------+--------------+------------------+-------+
5 rows in set (0.00 sec)

mysql> -- 5.将"price"列的数据类型从DECIMAL(10, 2)改为 DECIMAL(12, 2)。请提供相应的SQL语句来执行这些修改。
mysql> ALTER TABLE products MODIFY price DECIMAL(12,2);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> -- 6.对于"products"表，又有一个新的需求：由于某些原因，我们不再销售名为"Laptop"的产品，需要从表中删除该记录。请提供一条SQL删除语句来执行此操作。
mysql> delete from products where product_name = 'Laptop';
Query OK, 1 row affected (0.00 sec)

mysql> -- 8.如果要购买10个Mouse，请显示出最终的价格。
mysql> select price*10 AS 价格 from products where product_name='Mouse';
+--------+
| 价格 |
+--------+
| 200.00 |
+--------+
1 row in set (0.00 sec)

mysql> -- 10.将商品类别和名称拼接起来并显示其余的信息。比如： Elctronics-Mouse
mysql> select product_id,price,concat(product_category,'-',product_name) as product from pr
oducts;
+------------+--------+------------------------+
| product_id | price | product |
+------------+--------+------------------------+
| 2 | 800.00 | Electronics-Smartphone |
| 3 | 100.00 | Accessories-Headphones |
| 4 | 25.00 | Clothing-T-shirt |
| 5 | 20.00 | Electronics-Mouse |
+------------+--------+------------------------+
4 rows in set (0.00 sec)

mysql>