数据库基础学习

首先，连接数据库：
C:\Users\Administrator>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.21 MySQL Community Server - GPL

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
创建数据库：
mysql> create database bjpowernode1;
Query OK, 1 row affected (0.06 sec)
创建数据库要切换到该数据库，简易的理解为用此数据库：
mysql> use bjpowernode1;
Database changed
导入数据库里面的表文件：
mysql> source D:\MySQL学习\mysql_202002\02_recources\bjpowernode.sql
Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.14 sec)

Query OK, 0 rows affected, 5 warnings (0.03 sec)

Query OK, 0 rows affected (0.02 sec)
然后查看数据库版本信息：
mysql> select version();
±----------+
| version() |
±----------+
| 8.0.21 |
±----------+
1 row in set (0.00 sec)
查看当前时间：
mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2020-08-08 22:25:03 |
±--------------------+
1 row in set (0.00 sec)
查看当前库有多少个表：
mysql> show tables
选择emp表里面的员工工号
mysql> select empno from emp;
±------+
| empno |
±------+
| 7369 |
| 7499 |
| 7521 |
| 7566 |
| 7654 |
| 7698 |
| 7782 |
| 7788 |
| 7839 |
| 7844 |
| 7876 |
| 7900 |
| 7902 |
| 7934 |
±------+
14 rows in set (0.00 sec)
查询emp表里面所有员工的enamel（姓名）、工资（sal）、奖金（comm）；
mysql> select ename as “员工姓名”,sal as “工资”,comm as “奖金” from emp; #可中文以用双引号，可以用单引号，但是建议用双引号
±---------±--------±--------+
| 员工姓名 | 工资 | 奖金 |
±---------±--------±--------+
| SMITH | 800.00 | NULL |
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| JONES | 2975.00 | NULL |
| MARTIN | 1250.00 | 1400.00 |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
±---------±--------±--------+
14 rows in set (0.00 sec)

mysql> select ename 员工姓名,sal 工资,comm 奖金 from emp;
±---------±--------±--------+
| 员工姓名 | 工资 | 奖金 |
±---------±--------±--------+
| SMITH | 800.00 | NULL |
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| JONES | 2975.00 | NULL |
| MARTIN | 1250.00 | 1400.00 |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
±---------±--------±--------+
14 rows in set (0.00 sec)
\c是系统不识别的情况下，可以直接退出；
查询emp表里面售货员的工资涨5000和经理的工资涨8000并显示在addsal列；
mysql> select job,sal,case job when “saleman” then sal + 5000
-> when “manager” then sal +8000
-> else sal
-> end as addsal
-> from emp; #case…when ；else…end为结束case语法，as语法；else就是其他的还按照原来的sal显示；
±----------±--------±---------+
| job | sal | addsal |
±----------±--------±---------+
| CLERK | 800.00 | 800.00 |
| SALESMAN | 1600.00 | 1600.00 |
| SALESMAN | 1250.00 | 1250.00 |
| MANAGER | 2975.00 | 10975.00 |
| SALESMAN | 1250.00 | 1250.00 |
| MANAGER | 2850.00 | 10850.00 |
| MANAGER | 2450.00 | 10450.00 |
| ANALYST | 3000.00 | 3000.00 |
| PRESIDENT | 5000.00 | 5000.00 |
| SALESMAN | 1500.00 | 1500.00 |
| CLERK | 1100.00 | 1100.00 |
| CLERK | 950.00 | 950.00 |
| ANALYST | 3000.00 | 3000.00 |
| CLERK | 1300.00 | 1300.00 |
±----------±--------±---------+
14 rows in set (0.03 sec)
注释有什么用？可以一起运行。
写代码的时候做一下说明来提高可读性
注释怎么用
1.单行注释
1.语法：-- 注释语句
– 查询员工的姓名和工资
SELECT ename,sal FROM emp;
2.多行注释
2.语法
/*
注释语句
*/
/查询员工的姓名，工资
部门编号和奖金/
SELECT ename,sal,deptno,COMM FROM emp;

– 01条件查询
读取指定的数据
语法：
SELECT 字段1，字段2，字段3。。。from 表名称
WHERE 条件1，条件2.。。
2：案例：
– 查询工资等于3000 的员工姓名和工资
select ename,sal from emp where sal=3000;

– <>!= 不等于
select ename,sal from emp where sal <> 3000;
– < 小于
select ename,sal from emp where sal < 3000;
– <= 小于等于
– >= 大于等于
– 查询大于等于1600并且<=3000 の
SELECT * FROM emp WHERE SAL >=1600 AND SAL <=3000;
– BETWEEN AND
SELECT * FROM emp WHERE SAL BETWEEN 1600 AND 3000;
– 没有奖金的员工有哪些
SELECT * FROM emp WHERE COMM IS NULL;
– AND 查询工种和工资大于2500的
SELECT * FROM emp WHERE JOB = “manager” AND SAL > 2500;
– IN 查询经理和售货员的信息
SELECT * FROM emp WHERE JOB IN (“manager”,“salesman”);
– OR 查询什么或者什么的信息。。
SELECT * FROM emp WHERE JOB =“manager” OR JOB=“salesman”;
– in和or的区别：
in比or快 OR从a1匹配没匹配失败再去和a1匹配
IN 二叉树搜索

– 查询薪水大于1800并且员工编号是20和30的
用括号改变优先级
拆分语句
SELECT * FROM emp WHERE sal > 1800 AND (deptno = 20 OR deptno = 30)
– 薪水不包含1600和3000的；
SELECT * FROM emp WHERE sal NOT IN (1600,3000);
SELECT * FROM emp WHERE sal <> 1600 AND SAL <> 3000;
SELECT * FROM emp WHERE sal != 1600 AND SAL != 3000;
SELECT * FROM emp WHERE NOT (sal = 1600 or SAL = 3000);
– 模糊查询 LIKE"",是对字符串的操作，需要加双引号。 _是占位符，%是通配符，匹配0个或者多个任意的字符
SELECT * FROM emp WHERE ENAME LIKE “%a%”;
SELECT * FROM emp WHERE ENAME LIKE “_a%”;
– 排序 ORDER BY按照一个或多个列进行升序或者排序
[图片]
ORDER BY 列的名称，asc（默认） DESC
[图片]
ORDER BY 6,6是字段的位置进行排序；
[图片]

数据处理函数：

format （X,D）
使当前数字以千位展示，保留D位小数
eg：
[图片]
round 四舍五入
select round（123.45）— 123
select round(1234.56,1)----1234.6