7.22mysql

最新推荐文章于 2024-05-19 17:03:37 发布

Aurora雪

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量25

点赞数

分类专栏： mysql 文章标签：数据库 java sql

本文链接：https://blog.csdn.net/qq_52927369/article/details/131904888

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

mysql> create database company;

Query OK, 1 row affected (0.01 sec)

mysql> use company;

Database changed

mysql> create table `dept`(`DEPTNO` int comment '部分编号',`DNAME` varchar(14) comment '部分名称',`LOC` varchar(14) comment '部门地点');

Query OK, 0 rows affected (0.03 sec)

mysql> desc dept;

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.02 sec)

mysql> desc emp;

±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| EMPNO | int | YES | | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double | YES | | NULL | |
| COMM | double | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
±---------±------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> create table `salgrade`(`GRADE` int comment '工资等级',`LOSAL` int comment '最低工资',`HISAL` int comment '最高工资');

Query OK, 0 rows affected (0.01 sec)

mysql> desc salgrade;

±------±-----±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----±-----±----±--------±------+
| GRADE | int | YES | | NULL | |
| LOSAL | int | YES | | NULL | |
| HISAL | int | YES | | NULL | |
±------±-----±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> insert into `dept`(DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','BOSTON');

Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from dept;

mysql> insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (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',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,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','CLEERK',7782,'1982-01-23',1300,NULL,10);

Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0

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 | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 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 | CLEERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
±------±-------±----------±-----±-----------±-----±-----±-------+
14 rows in set (0.00 sec)

mysql> insert into salgrade(GRADE,LOSAL,HISAL) values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

Query OK, 5 rows affected (0.00 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>--修改emp表中sal字段为salary
mysql> alter table emp change SAL salary double;

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from emp;

±------±-------±----------±-----±-----------±-------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | 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 | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 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 | CLEERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
±------±-------±----------±-----±-----------±-------±-----±-------+
14 rows in set (0.00 sec)

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

mysql> --查找员工姓名中包含'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 | 7839 | 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> --查找所有员工姓名中包含'A'及'E'的员工信息
mysql> select * from emp where ENAME like '%A%' and ENAME like '%E%';

±------±------±---------±-----±-----------±-------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
±------±------±---------±-----±-----------±-------±-----±-------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
±------±------±---------±-----±-----------±-------±-----±-------+
3 rows in set (0.00 sec)

mysql> --找所有的职位为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 | 30 |
±------±-------±---------±-----±-----------±-------±-----±-------+
4 rows in set (0.00 sec)

mysql> -- 将工资低于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> 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 | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1700 | 0 | 30 |
| 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 | CLEERK | 7782 | 1982-01-23 | 1500 | NULL | 10 |
±------±-------±----------±-----±-----------±-------±-----±-------+
14 rows in set (0.00 sec)

mysql> ---查询没有上级领导的所有员工信息
mysql> select * from emp where MGR is NULL;

±------±------±----------±-----±-----------±-------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | salary | COMM | DEPTNO |
±------±------±----------±-----±-----------±-------±-----±-------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000 | NULL | 10 |
±------±------±----------±-----±-----------±-------±-----±-------+
1 row in set (0.00 sec)

mysql> --查询没有奖金的所有员工信息
mysql> select * from emp where COMM is null or COMM = 0;

±------±-------±----------±-----±-----------±-------±-----±-------+
| 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 | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1700 | 0 | 30 |
| 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 | CLEERK | 7782 | 1982-01-23 | 1500 | NULL | 10 |
±------±-------±----------±-----±-----------±-------±-----±-------+
11 rows in set (0.00 sec)

mysql> --将部门表中的40部门的地址修改成'xian'
mysql> update dept set LOC = 'xian' where DEPTNO = 40;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> -- 假设李华的工资是2000，请查询出他的工资等级
mysql> select GRADE from salgrade where HISAL = 2000 ORDER BY HISAL ASC LIMIT 1;

±------+
| GRADE |
±------+
| 3 |
±------+

mysql> --将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> 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(product_id,product_name,category,price) 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> --写一条SQL查询语句，找出所有属于"Electronics"类 别的产品信息。
mysql> select * from products where category = 'Electronics';

±-----------±-------------±------------±------+
| product_id | product_name | category | price |
±-----------±-------------±------------±------+
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 5 | Mouse | Electronics | 20 |
±-----------±-------------±------------±------+
3 rows in set (0.00 sec)

mysql> -- 写一条SQL查询语句，找出价格高于等于100.00的产品 信息。^C
mysql> select * from products where price >= '100.00';

±-----------±-------------±------------±------+
| product_id | product_name | category | price |
±-----------±-------------±------------±------+
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Headphones | Accessories | 100 |
±-----------±-------------±------------±------+
3 rows in set (0.00 sec)

mysql> -- 写一条SQL查询语句，找出价格在20.00到1000.00之 间的产品信息。
mysql> select * from products where price between '20.00' and '1000.00';

mysql> --对于"products"表，有一个新的需求： 将"category"列改名为"product_category"^C
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> --将"price"列的数据类型从DECIMAL(10, 2)改为 DECIMAL(12, 2)。请提供相应的SQL语句来执行这些修改。
mysql> ALTER TABLE products MODIFY price DECIMAL(12, 2);

Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

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

Query OK, 1 row affected (0.00 sec)

mysql> select * from products;

±-----------±-------------±-----------------±-------+
| product_id | product_name | product_category | price |
±-----------±-------------±-----------------±-------+
| 2 | Smartphone | Electronics | 800.00 |
| 3 | Headphones | Accessories | 100.00 |
| 4 | T-shirt | Clothing | 25.00 |
| 5 | Mouse | Electronics | 20.00 |
±-----------±-------------±-----------------±-------+
4 rows in set (0.00 sec)

mysql> --写一条SQL查询语句，找出商品名称包含字母"e"的商品 信息。

mysql> select * from products where product_name like '%e%';

±-----------±-------------±-----------------±-------+
| product_id | product_name | product_category | price |
±-----------±-------------±-----------------±-------+
| 2 | Smartphone | Electronics | 800.00 |
| 3 | Headphones | Accessories | 100.00 |
| 5 | Mouse | Electronics | 20.00 |
±-----------±-------------±-----------------±-------+
3 rows in set (0.00 sec)

mysql> --如果要购买10个Mouse，请显示出最终的价格。
mysql> SELECT 20 * 10 AS final_price;

±------------+
| final_price |
±------------+
| 200 |
±------------+
1 row in set (0.00 sec)

mysql> --将商品的名字，商品的类别全部转换成小写形式并展示 所有信息。
mysql> SELECT product_id,LOWER(product_name) AS product_name_lower, LOWER(product_category) AS product_category_lower, price FROM products;
//通过 LOWER 函数将名称和类别转换为小写形式。

±-----------±-------------------±-----------------------±-------+
| product_id | product_name_lower | product_category_lower | price |
±-----------±-------------------±-----------------------±-------+
| 2 | smartphone | electronics | 800.00 |
| 3 | headphones | accessories | 100.00 |
| 4 | t-shirt | clothing | 25.00 |
| 5 | mouse | electronics | 20.00 |
±-----------±-------------------±-----------------------±-------+
4 rows in set (0.00 sec)

mysql> --将商品类别和名称拼接起来并显示其余的信息。比如： Elctronics-Mouse
mysql> SELECT product_id, CONCAT(product_category, '-',product_name) AS category_name, price FROM products;
//通过 CONCAT 函数，将类别和名称拼接在一起，并将结果显示为 category_name 列。

±-----------±-----------------------±-------+
| product_id | category_name | price |
±-----------±-----------------------±-------+
| 2 | Electronics-Smartphone | 800.00 |
| 3 | Accessories-Headphones | 100.00 |
| 4 | Clothing-T-shirt | 25.00 |
| 5 | Electronics-Mouse | 20.00 |
±-----------±-----------------------±-------+
4 rows in set (0.00 sec)