| 数据库问题 | ||||||||||||||||||
一、数据库概述 1.1 什么是数据库 数据库：英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。 顾名思义：就是存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据。 1.2 什么是数据库管理系统 数据库管理系统：DataBaseManage，简称DBMS。 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。 1.2.1 常见的数据库管理系统 MySQL、Oracle、MS SqlServer、DB2、sybase等… 1.3 什么是SQL SQL：结构化查询语言。 程序员需要学习SQL语句，程序员通过编写的SQL语句，然后数据库管理系统(DBMS)负责执行SQL语句，最终来完成数据库中数据的增删改查操作。 SQL是一套标准，程序员主要学习的是SQL语句，这个SQL在MySQL中可以使用，在Oracle也可以使用，在DB2中也能使用。 1.4 DB、DBMS和SQL三者之间的关系 DBMS ——执行——> SQL(语句)——操作——> DB(数据) 1.5 学习数据库的步骤 先安装数据库管理系统MySQL，然后学习SQL语句怎么写，编写SQL语句之后，DBMS对SQL语句进行执行，最终来完成数据库的数据管理。 二、数据库的安装和卸载 2.1 数据库的安装 动力节点数据库安装视频 2.2 数据库的完美卸载 动力节点数据库的完美卸载 三、MySQL服务 3.1 查看MySQL服务器的状态 计算机 --右键–》管理 --双击–》服务和应用程序–双击–》服务----》找到MySQL服务。 MySQL的服务，默认是“启动”的状态，只用启动了MySQL才能用。 默认主功能台下是“自动”启动，自动启动表示下一次重启操作系统的是时候自动启动该服务。 3.2 正常的启动和关闭MySQL服务器 可以在该服务上点击右键，会出现： 启动 重启服务 停止服务等 还可以改变服务的默认配置： 服务上点击右键，属性，然后可以选择启动方式 3.3 使用命令启动和关闭MySQL服务器 关闭：net stop 服务名称 (例如 net stop MySQL) 启动：net start 服务名称 (例如 net start MySQL) 四、MySQL的登录 Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.21 MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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> 复制代码 五、MySQL的命令(不区分大小写) 注意：mysql语句输入时，不见分号(;)不执行sql语句 5.1 退出 命令：exit 5.2 查看mysql中有哪些数据 命令： show databases; 注意：以"分号(;)"结尾，分号是英文的。 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | book | | ccc | | crm | | cs | 5--9显示的是数据库的名称 +--------------------+ 21 rows in set (0.03 sec) 复制代码 5.3 如何选择要使用的数据库 命令：use 数据库名称; 注意：以"分号(;)"结尾，分号是英文的。 mysql> use mybatis; Database changed mysql> 表示正在使用一个名字叫做mybatis的数据库。 复制代码 5.4 如何创建数据库 命令：create 数据库名称 创建名字为mji的数据库 mysql> create database mji; Query OK, 1 row affected (0.08 sec) 复制代码 5.5 查看某个数据库下有哪些表 命令：show tables; 例如：查看mybatis下有哪些表 进入要查看的数据库 mysql> use mybatis; Database changed 在输入查看某个数据库有哪些表的命令 mysql> show tables; +-------------------+ | Tables_in_mybatis | +-------------------+ | account | | role | | ui | | user | | user_role | +-------------------+ 5 rows in set (0.01 sec) 复制代码 5.6 如何导入数据库文件(xx.sql) 命令：source 路径名 (路径名下应全是英文) 5.7 只看表的结构不看表数据 命令：desc 表名称; describe的缩写是：desc 例如：查看表明为“user”的表结构 mysql> desc user; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(32) | NO | | NULL | | | birthday | datetime | YES | | NULL | | | sex | char(1) | YES | | NULL | | | address | varchar(256) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.12 sec) 复制代码 5.7 查看MySQL数据库的版本号 命令：select version(); mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.21 | +-----------+ 复制代码 5.8 查看当前使用的是哪个数据库 命令：select database(); mysql> select database(); +------------+ | database() | +------------+ | mybatis | +------------+ 复制代码 5.9 终止一条sql语句的输入 命令：\c mysql> show -> -> -> -> \c mysql> 复制代码 六、数据库中的表 数据库中最基本的单元是表(table) 数据库当中是以表格的形式表示数据的，因为表比较直观 任何一张表都有行和列 行(row)：被称为数据/记录 例如:
被称为数据/纪录 列(column)：被称为字段 例如下表有：姓名字段、性别字段、年龄字段
七、SQL语句的分类 DQL：数据查询语言(凡是带有select关键字的都是查询语句) select… DML：数据操作语言，(凡是对表当中的数据进行增删改的都是DML) insert(增) delete(删) update(改) 这个主要是操作表中的数据data。 DDL：数据定义语言 凡是带有 create 、drop、alter的都是DDL。 DDL主要操作的是表的结构，不是表中的数据。 create ：新建 等同于增、drop：删除、alter：修改 这个增删改和DML不同，这个主要是对表结构进行增删改。 TCL：事务控制语言 包括： 事务提交：commit 事务回滚：rollback DCL：数据控制语言 例如：授权grant 、撤销权限revoke… 八、DQL语句 8.1 简单查询 8.1.1 查询一个字段 select 字段名 from 表名 注意： select和from都是关键字 字段名和表名都是标识符 强调：!!!!!! 对于SQL语句来说，是通用的。 所有的SQL语句都以分号(;)结尾 而且SQL语句不区分大小写，都行。 8.1.2 查询两个或多个字段 使用逗号隔开 select 字段1,字段2,字段3 … from 表名; mysql> select id,birthday from user; +----+---------------------+ | id | birthday | +----+---------------------+ | 41 | 2018-02-27 17:47:08 | | 42 | 2018-03-02 15:09:37 | | 43 | 2018-03-04 11:34:34 | | 45 | 2018-03-04 12:04:06 | | 46 | 2018-03-07 17:37:26 | | 48 | 2018-03-08 11:44:00 | | 55 | 2021-10-18 15:29:03 | | 56 | 2021-10-14 17:11:50 | | 61 | 2021-10-18 14:58:18 | +----+---------------------+ 复制代码 8.1.3 查询所有字段 第一种形式 使用*：select * from 表名; 第二种形式 输入所有字段名：select a,b,c,d,e from 表名; 建议使用第二种形式，因为第一种形式执行的效率低而且可读性差 8.1.4 给查询的列起别名 使用as关键字 命令：select b as c from 表名; 例如：给birthday起别名为B mysql> select id,birthday from user; +----+---------------------+ | id | birthday | +----+---------------------+ | 41 | 2018-02-27 17:47:08 | | 42 | 2018-03-02 15:09:37 | +----+---------------------+ 9 rows in set (0.00 sec) mysql> select id,birthday as B from user; +----+---------------------+ | id | B | +----+---------------------+ | 41 | 2018-02-27 17:47:08 | | 42 | 2018-03-02 15:09:37 | +----+---------------------+ 复制代码 注意：只是将显示的查询结果列名显示为B，原表列名还是叫birthday 如果使用中文起别名。需要用’ '括起来 select a as '中国' from user; 复制代码 记住：select语句是永远都不会进行修改操作。(因为只负责查询) as关键字也可以去掉，但是不能用逗号(,)隔开，用空格隔开 如果给字段名起的别名中带有空格，可以用 ’ ’ 或 " " 来实现 select a，dname ‘dept name’ from user; select a，dname “dept name” from user; 注意：在所有的数据库中，单引号(’ '),是标准 8.1.5 字段可以使用数学表达式 select c*12 from user; 8.2 条件查询 8.2.1 什么是条件查询 不是将表中所有数据查询出来，而是查询出来符合条件的。 语法格式： select 字段1,字段2,字段3 … from 表名 where 条件; 8.2.2 条件语句 8.2.2.1 =(等于)、<>或!= (不等于)、<(小于) [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m3ojoQ1a-1636791309998)(D:\photo\条件查询.png)] 8.2.2.2 between … and … 、>= and <= (两个值之间) [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AkObhWB9-1636791310001)(D:\photo\between and.png)] 注意：between …and… 遵循左小右大原则 8.2.2.3 null [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8haES4Do-1636791310003)(D:\photo\null.png)] **注意：**在数据库当中null不能使用等号进行衡量，需要使用 is null; 因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号来衡量。 8.2.2.4 and和or and和or同时出现，and的优先级高 如果想让or先执行，要用()括起来 例如 select * from user where a>2500 and(id=10 or id=20); 复制代码 8.2.2.5 in()在当中、not in()不在当中 select * from 表名 where 字段名 in(’ 数据’,‘数据’…) select * from 表名 where 字段名 not in(’ 数据’,‘数据’…) 其中in()中的数据表示的是要查询该字段下的数据一致的信息将被显示出来，而不是数据之间的数据。 mysql> select * from emp where sal in('800','950','1250'); +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 4 rows in set (0.00 sec) 其中数据 808 950 1250 表示的是字段sal 中的值，如果sal字段里面有该数据，该数据将被显示出来。 复制代码 8.2.2.6 模糊查询 like % ：匹配任意多个字符 下划线 _ ： 任意一个字符 %和_都是特殊的字符 找出名字含有o的 select * from user where name like '%o%'; 找出以k结尾的名字 select * from user where name like '%k'; 找出以k开头的名字 select * from user where name like 'k%'; 找出第二个字母是A的名字 select * from user where name like '_A%'; 找出第三个字母是A的名字 select * from user where name like '__A%'; 复制代码 如果名字中含有下划线，应该怎么查找? select * from user where name like '%\_%'; 利用转义字符 复制代码 九、排序(order by) 9.1 单个字段进行排序 查询所有员工薪资(sal)并排序 默认升序 (也是默认排序，默认排序就是升序) select * from user order by sal; 复制代码 指定升序: asc select * from user order by sal asc; 复制代码 指定降序: desc select * from user order by sal desc; 复制代码 9.2 多个字段进行排序 查询员工的名字(name)和薪资(sal)，薪资按升序排列，并且如果薪资一样的员工，在按照名字排序 select name,sal from user order by sal asc,name asc; sal在前起主导作用，只有sal一样，才会启用name 复制代码 根据字段的位置也可以排序 select name,sal from user order by 2; 2表示第二列 ，第二列是sal，第一列是name。 复制代码 9.3 综合案例 找出薪资在1250到3000的员工，并且按照薪(sal)资降序排列 select name,sal from user where sal between 1250 and 3000 order by sal desc; 复制代码 关键字顺序不能变 select ...... from ...... where ...... order by ...... 复制代码 以上语句的执行顺序必须掌握!!!!!! 第一步：from 第二步：where 第三步：select 第四步：order by(排序总是在最后执行) 十、数据处理函数(被称为单行处理函数) 单行处理函数的特点：一个输入对应一个输出 多行处理函数的特点：多个输入对应一个输出 10.1 常见的单行处理函数 select 字段 from 表名 select name from user; select 'abc' from user; select后面直接跟字面量/字面值 复制代码 10.1.1 lower 大写转小写 lower select lower(name) as name from user; 并起别名name 复制代码 10.1.2 upper 小写转大写 upper select upper(name) as name from user; 并起别名name 复制代码 10.1.3 substr 取子串 substr (substr(被截取的字符串,起始下标,截取的长度)) 注意!!! 起始下标从1开始 select substr(name,1,1) as name from user; 并起别名name 复制代码 10.1.4 concat concat 进行字符串的拼接 select concat(id,name) from user; 复制代码 10.1.5 length length 取长度 select length(name) from user; 复制代码 10.1.6 trim trim 去前后空格 select * from user trim(' KING'); 复制代码 10.1.7 round round 四舍五入 select round(123.6,1) from user; 复制代码 10.1.8 rand rand 生成随机数 select rand() from user; 复制代码 10.1.9 case … when … then … when … then … else … end 当员工的岗位(job)是a的时候，工资(sal)上调10%，当员工的岗位是b的时候，工资上调50%，其他的不变 select name, job, (case job when 'a' then sal*1.1 when 'b' then sal*1.5 else sal end) as newsal from user; 复制代码 10.1.10 ifnull 在数据库中，只要有null参与的数学运算，最后结果都是null 为避免出现这种情况，需要使用 ifnull 函数 ifnull 用法：ifnull(数据,被当作哪个值来处理) 例如 select name ，(sal+ifnull(comm,0) from user; 表示：补助(comm) 如果为null的话，用0来代替commm的值 复制代码 10.1.11 format 进行数字格式化 format(数字，'格式') 对数字进行千分位格式 select ename,format(sal,'$999,999') from emp; +--------+------------------------+ | ename | format(sal,'$999,999') | +--------+------------------------+ | SMITH | 800 | | ALLEN | 1,600 | | WARD | 1,250 | | JONES | 2,975 | | MARTIN | 1,250 | | BLAKE | 2,850 | | CLARK | 2,450 | | SCOTT | 3,000 | | KING | 5,000 | | TURNER | 1,500 | | ADAMS | 1,100 | | JAMES | 950 | | FORD | 3,000 | | MILLER | 1,300 | +--------+------------------------+ 14 rows in set, 14 warnings (0.10 sec) 复制代码 10.1.12 str_to_date 将字符串转化为日期类型 str_to_date('字符串日期','日期格式') 复制代码 10.1.13 date_format 以某个特定的各式日期展示 date_format(日期类型数据,‘日期格式’); date_format('1990-10-12','%Y/%m/%d'); 结果是 把1990-10-12以 1990/10/12的形式展示 +------+------------+------+ | id | birth | name | +------+------------+------+ | NULL | 1999-12-05 | NULL | | NULL | 1985-10-10 | NULL | | NULL | 1990-12-15 | NULL | mysql> select date_format(birth,'%Y/%m/%d') as bitth from t_user; +------------+ | bitth | +------------+ | 1999/12/05 | | 1985/10/10 | | 1990/12/15 | +------------+ 3 rows in set (0.00 sec) 复制代码 十一、分组函数(也叫做多行处理函数) 输入多行最终输出一行 五个： count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 注意： 分组函数在使用的时候必须先进行分组，然后才能用。 如果你没有对数据进行分组，整张表默认为一组 分组函数自动忽略null，不需要提前对null进行处理 分组函数不能直接使用在where子句中 所有的分组函数可以组合起来一起用 **count(字段)：**统计该字段下所有不为null的元素总数 **count(*)：**统计表当中的总行数 十二、分组查询 group by(非常重要，一定要掌握) 12.1 什么是分组查询 在实际的应用中可能会有这样的需求，需要先进行分组，然后对每一组的数据进行操作 这个时候我们需要使用分组查询，应如何进行分组查询呢? select ... from ... group by ... 复制代码 12.1.1 执行顺序 select ... from ... where ... group by ... order by ... 复制代码 注意!!!!!以上关键字的顺序不能颠倒 执行顺序 from where group by select order by 现在可以解决一个问题 为什么分组函数不能直接使用在where后面???? **原因：**因为where在执行的时候 group by没有执行，也就意味着分主函数没有进行分组，而分组函数只有分完组后才能用，所以分组函数不能直接使用在where后面 12.2 找出每个工作岗位的工资和 **实现思路：**按照工作岗位(job)进行分组，然后进行工资(sal)求和。 select job,sum(sal) from user group by job; 复制代码 以上这个语句的执行顺序? 先从user表中查询数据 根据job字段进行分组 然后对每一组的数据进行sum(sal) 12.3 重点结论 !!!!!!!!!!!!! 在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段以及分组函数，其他的一律不能跟。 即使跟了其他的字段在MySQL中依然会有数据产生，但是产生的这些数据是没有任何意义的，并且在Oracle中跟其他字段，将会出错 !!!!!!!!!!!!! 12.4 找出每个部门(deptno)的最高薪资(sal) select deptno,max(sal) from user group by deptno; 复制代码 12.5 找出每个部门(deptno)，不同工作岗位(job)的最高薪资(sal) select deptno,job,max(sal) from user group by deptno,job; 复制代码 12.6 找出每个部门(deptno)的最高薪资(sal)，要求显示最高薪资大于3000 12.6.1 having 使用having可以对分完组之后的数据进一步过滤 having不能单独使用，having不能代替where having必须和group by 联合使用 12.6.2 代码实现 使用having select max(sal) from user group by deptno having max(sal)>3000; -------------------------------------------------------- 使用where 先将大于3000的薪资找出来，然后在进行分组 select max(sal) from user where sal>3000 group by deptno; 复制代码 **优化策略!!!：**where和having，优先选择where，where实在完成不了的，在选择having 十三、单表查询总结 select ... from ... where ... group by ... having ... order by ... 复制代码 以上程序只能按照这个顺序来，不能颠倒!!!!!! 执行顺序： from where group by having select order by 从某张表中查询数据 先经过where条件筛选出有价值的数据 然后筛选出来的数据进行分组 分完组后可以使用having在进一步的筛选 然后select查询出来，最后排序输出。 13.1 综合例题 找出每个岗位(job)的平均薪资(sal)，要求显示的平均薪资大于1500，除’manager’岗位之外，要求按照平均薪资降序排列 select avg(sal) from user where job <> 'manager' group by job having avg(sal)>1500 order by avg(sal) desc; 复制代码 十四、distinct 去重 14.1 distinct使用时注意事项 把查询结果去除重复记录。 **注意：**原表不会被修改，指示查询结果去重。 未使用关键字distinct mysql> select job from emp ; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | SALESMAN | | MANAGER | | SALESMAN | | MANAGER | | MANAGER | | ANALYST | | PRESIDENT | | SALESMAN | | CLERK | | CLERK | | ANALYST | | CLERK | +-----------+ 14 rows in set (0.00 sec) --------------------------------------------------- 使用关键字distinct mysql> select distinct job from emp ; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+ 5 rows in set (0.00 sec) 复制代码 错误格式!!!!!!!!!!!!!!!!： select ename,distinct job from emp; mysql> select ename,distinct job from emp; 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 'distinct job from emp' at line 1 mysql> 复制代码 **如何进行修改上述错误：**需要把distinct写到所有字段之前 也就是distinct要出现在所有字段之前 mysql> select deptno,job from emp; +--------+-----------+ | deptno | job | +--------+-----------+ | 20 | CLERK | | 30 | SALESMAN | | 30 | SALESMAN | | 20 | MANAGER | | 30 | SALESMAN | | 30 | MANAGER | | 10 | MANAGER | | 20 | ANALYST | | 10 | PRESIDENT | | 30 | SALESMAN | | 20 | CLERK | | 30 | CLERK | | 20 | ANALYST | | 10 | CLERK | +--------+-----------+ 14 rows in set (0.00 sec) ------------------------ mysql> select distinct deptno,job from emp; +--------+-----------+ | deptno | job | +--------+-----------+ | 20 | CLERK | | 30 | SALESMAN | | 20 | MANAGER | | 30 | MANAGER | | 10 | MANAGER | | 20 | ANALYST | | 10 | PRESIDENT | | 30 | CLERK | | 10 | CLERK | +--------+-----------+ 9 rows in set (0.00 sec) 复制代码 上述程序中，distinct字段出现在job和deptno字段之前，表示的意思是，把job和deptno联合起来再去重。 14.2 count与distinct来联合使用 统计工作岗位的数量 select count(distinct job) from emp; 复制代码 十五、连接查询 15.1 什么是连接查询 从一张表中单独查询，称为单表查询 从emp表和dept表联合起来查询数据，从emp表中取员工姓名，从dept表中取部门名字， 这种跨表查询，多张表联合起来查询数据，被称为连接查询
|
关注官方微信