Mysql简介及相关知识

一、 Mysql 简介 1 、介绍 1.1 什么是数据库 ? 数据库： database ，数据的仓库（用来存放数据库对象）按照一定的数据 结构来组织、存储和管理的数据的仓库，简单来说就是存储数据的仓库。 数据库系统组成： DBS 是由 DB 和 DBMS 两部分组成。 计算机硬件、 DBMS 、 DB 、 DBA 、 DBAS 。 DBS ：数据库系统 DBMS ：数据库管理系统 ----- 系统软件，用来管理数据库的软件系统，常见 的 DBMS 有 Mysql 、 oracle 、 Ms SQL server 、 DB2 、 sysbase 、 Access 等。 1.2 什么是 mysql ？ Mysql ：是一个开源的关系型数据库管理系统，由瑞典 Mysql AB 公司开 发，后来被 oracle 公司收购，所以目前属于 oracle 公司。 特点：体积小、速度快、成本低、开源，中小型网站都使用 Mysql 数据 库。 版本：企业版 Enterprise 、社区版 Community DBA ： Database Administrator 数据库管理员 1.3 数据、数据库、表 用户数据 ---> 表 ( 行、列 )---> 数据库 2 、安装 Mysql 2.1 版本 分平台： windows 、 Linux 、 MAC-OS 分版本： 5.x 、 6.x 、 7.x 、 8.x 2.2 安装 安装位置： D:\mysql-8.0.39-winx64 bin: 可执行文件 data: 数据库文件 my.ini or my.cnf 核心配置文件 2.3 服务 安装 Mysql 之后，会在操作系统中添加一个 mysql 服务 需要先启动服务才能使用 mysql; [root@linux ~] # systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; ena Active: active (running) since 五 2024 -09-13 00 :53:36 CST; Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.ht Main PID: 9437 (mysqld) Tasks: 41 二、基本操作 1 、连接 Mysql 语法： 安装 Mysql 以后，默认有一个管理员 root 显示登录 隐式登录 2 、查看数据库和表 mysql 库属于系统数据库，包含 mysql 的相关系统信息，不要修改 CGroup: /system.slice/mysqld.service └─9437 /usr/local/mysql/bin/mysqld --defaults-file = 9 月 13 00 :53:36 linux.test.com systemd[1]: Started MySQL Serve [root@linux ~] # systemctl start mysqld.service # 开启 mysql 服务 [root@linux ~] # systemctl enable mysqld.service # 设置开机自动启 mysql -u 用户名 -p 密码 -h 数据库服务器的地址 -D 数据库名 show databases ;----- 查看当前所有数据库 use 数据库名 ;------- 切换数据库 show tables ;------- 查看当前数据库中的所有表 select user ();----- 显示当前登陆的用户 select database ();---- 显示当前操作的数据库 3 、导入初始数据 3.1 导入数据 准备好 1 个以 .sql 结尾的文件是数据库脚本文件 先连接登陆 mysql 数据库。然后执行如下命令： 3.2 表结构 知识点： 表结构：只有列名称（属性）的空表 表记录：所有记录的集合 EMP 表 雇员表 source E:/init .sql ； desc 表名 ;----- 查看表结构 select * from 表名 ; ------ 查看表中的所有记录 列名 类型 含义 EMPNO int 整型 雇员编号 ENMAE varchar 可变长度的字符串 雇员姓名 JOB varchar 可变长度的字符串 工作，职位 MGR int 整型 上司或领导的编号 HIREDATE date 日期 入职时间 SAL double 双精度型小数 薪水、工资 COMM int 整型 奖金 DEPTNO int 整型 部门编号 列名 类型 含义 DEPTNO int 整型 部门编号 DNAME varchar 变长字符型 部门名 LOC varchar 变长字符型 部门位置 DEP 部门表 SALGRADE 工资等级表 列名 类型 含义 GRADE int 整型 等级编号 LOSAL int 整型 最低工资 HISAL int 整型 最高工资 bouns 奖金表 三、 SQL 简介 SQL ： Structured Query Language 结构化的查询语言，用来对数据库进 行查询、更新和管理的一种特殊的语言。 DML 是 SQL 语言的核心。 包含三个部分： DML Data Manipulation language 数据操纵语言 用于检索或更新数据库表： insert 、 delete 、 update 、 select 增删改查 DDL Data Defination language 数据定义语言 用于定义的数据的结构： create alter drop DCL Data Control language 数据控制语言 用于定义数据库用户的权限： grant revoke 四、表和库的管理 1 、数据类型 整数型： smallint 、 int 、 bigint 小数型： float 、 double 日期时间： date 、 time 、 datetime 、 timestamp 字符串： varchar 、 char ， text 其他： clob 存储文本大数据 blod 存储二进制大数据 2 、创建表 语法： 示例： create table 表名 ( 列名 数据类型 特征 , -- 字段名 属性名 列名 数据类型 特征 , …… 列名 数据类型 特征 ) charset =utf8; 3 、修改表 添加列 语法： create table t_user ( id int , username varchar ( 20 ), password varchar ( 50 ) ); create table t_student ( id int primary key auto_increment , -- 将 id 设为主键 自动增长 name varchar ( 10 ) not null , -- 不允许为空 age int , sex varchar ( 8 ) not null default ' 男 ' , -- 指定默认值 address varchar ( 100 ), height double , birthday date ) charset =utf8; insert into t_student(name,age,sex,birthday,height) values ( ' 张 insert into t_student(name,age,birthday,height) values ( ' 张三丰 insert into t_student(name,age,sex) values ( null , 22 , ' 女 ' ); insert into t_student values ( ' 钱多多 ' , 20 , ' 女 ' , ' 南京 ' , 172.1 ,now( alter table 表名 add 列名 数据类型 ; 示例： 在 t_student 表中增加 1 个名为 weight 体重字段，类型为双精度。 alter table t_student add weight double ; 修改列的类型 语法： alter table 表名 modify 列名 新数据类型 ; 示例： 将 t_student 表中 name 姓名字段的宽度修改为 250 。 alter table t_student modify name varchar ( 250 ); 修改列名 alter table 表名 change 原列名 新列名 数据类型 ; 示例： 将 t_student 表中 sex 字段名称修改为 gender 。 alter table t_student change sex gender varchar ( 8 ); 删除列 语法： 示例： 删除 t_student 表中 weight 列（字段）。 修改表名 语法： 示例： 将 t_student 表名修改为 student 。 4 、删除表 语法： alter table 表名 drop 列名 ; alter table t_student drop weight; alter table 原表名 rename 新表名 ; 或 rename table 原表名 to 新表名 ; alter table t_student rename student; rename table student to t_student; 示例： 删除 test 数据库中的 t_user 表？ 5 、截断表 清空表中的数据，作法类似于无条件的 delete 语句 语法： 示例： delete 与 truncate 的区别： delete 会记录日志，所以速度慢，而 truncate 不记录日志，清空表并释 放资源，速度快 drop table 表名； drop table if exists 表名 ; drop table t_user; drop table if exists t_user; truncate table 表名 ; truncate table t_student; select * from t_student; insert into t_student(name,gender) values ( 'tom' , ' 男 ' ); insert into t_student(name,gender) values ( 'mike' , ' 女 ' ); select * from t_student; delete 可以指定条件只删除部分数据，而 truncate 只能用来清空表中所 有数据 delete 不会将自动增长列归零，而 truncate 会使自动增长的列如 id 列归 零 6 、创建库 语法： 示例： 7 、删除库 语法： 示例： 五、查询操作 create database 数据库名 charset utf8; create database if not exists 数据库名 charset utf8; create database if not exists shop charset utf8; drop database 数据库名 ; drop database if exists 数据库名 ; drop database if exists shop; 1. 简介 1.1 语法 示例： ①查询所有雇员的姓名 1.2 用法 字符串连接 concat() 示例： 编号为 7369 的雇员，姓名为 smith, 职位为 clerk select 列名 from 表名 ; select 列名 1, 列名 2,... from 表名 ; select 列名 1 别名 1 ，列名 2 别名 2 ， .... from 表名 ; select ename from emp; select ename,job,hiredate from emp; select * from emp; select ename xm,job zw,hiredate rzsj from emp; select ename " 姓名 " ,job " 职位 " ,hiredate " 入职时间 " from emp; select empno,ename,sal your salary from emp;---- 语法错误 别名中有空格，需要使用双撇号 select empno,ename,sal "your salary" from emp; 四则运算 + - * / 例：查询雇员的姓名和年薪？ 在 MySQL 中， null 与任何值进行运算，结果都为 null 。 例：查询所有的职位 2. 限定查询 语法 : 2.1 比较运算符 select concat( " 编号为 " ,empno, " 的雇员，姓名为 " ,ename, ", 职位为 " ,jo select ename " 雇员姓名 " , sal* 12 " 年薪 " from emp; select ename " 雇员姓名 " , (sal+comm)* 12 " 年薪 " from emp; ---- select ename " 雇员姓名 " , (sal+ifnull(comm, 0 ))* 12 " 年薪 " from select job from emp;----- 有重复值 select distinct job from emp; select 列名 1, 列名 2,... from 表名 where 条件 ; > > = < <= = != 或 <> > > > > ``` > > > > ``` 例：查询工资大于 1500 的雇员信息 ```mysql select * from emp where sal>1500; select * from emp where sal>=1500; 例：查询雇员编号不是 7369 的雇员信息 例：查询姓名是 smith 的雇员编号，姓名，工资和入职时间。 注：字符串要用单撇号或双撇号括起来，同时 MySQL 中不区分大小写 2.2 null 或 not null 例：查询每月可以获得奖金的雇员信息？ select * from emp where empno!= 7369 ; select empno,ename,sal,hiredate from emp where ename= 'smith' ; select * from emp where comm is not null ; select * from emp where comm is null ; 注：判断是否为 null 时使用的是 is, 不能使用比较运算符。 2.3 and 例：查询基本工资大于 1000 ，并且可以获取奖金的雇员姓名、工资、奖金 2.4 or 例：查询从事销售工作，或工资大于等于 2000 的雇员信息？ 2.5 not 例：查询从事销售工作，并且工资不小于 1500 的雇员编号，姓名、职位和 入职时间 2.6 between ...and... 在 ... 与 ... 之间 例：查询基本工资大于 1500 ，但小于 3000 的雇员信息 select ename,sal,comm from emp where sal> 1000 and comm is not select * from emp where job = "salesman" or sal>= 2000 ; select empno,ename,job,sal,hiredate from emp where job != "sal select empno,ename,job,sal,hiredate from emp where not (job = " 注： between ... and ... 包含临界值 例：查询 1981 年入职的雇员编号、姓名、入职时间、所在部门编号？ 注意：日期必须使用单撇号或双撇号括起来 2.7 in 或 not in 例：查询编号为 7369 、 7499 、 7788 的雇员信息 例：查询姓名为 smith ， allen ， king 的雇员编号、姓名、入职时间 2.8 like 用来进行模糊查询，需要结合通配符一起使用 select * from emp where sal> 1500 and sal< 3000 ; select * from emp where sal between 1500 and 3000 ; select * from emp where sal>= 1500 and sal<= 3000 ; select empno,ename,hiredate,deptno from emp where hiredate bet select * from emp where empno= '7369' or empno = '7499' or empno select * from emp where empno in ( '7369' , '7499' , '7788' ); select empno,ename,hiredate from emp where ename in ( 'smith' , ' 常用的通配符： % 匹配任意长度的字符 _ 只能匹配单个字符 例：查询雇员姓名以 S 开头的雇员信息 例：查询雇员姓名中包含 M 的雇员信息 例：查询从事销售工作，并且姓名长度为 4 个字符的雇员信息 例：查询 1981 年入职的雇员编号、姓名、入职时间、所在部门编号 3. 排序 3.1 语法 语法： select * from emp where ename like 's%' ; select * from emp where ename like '%M%' ; select * from emp where job= 'salesman' and ename like '____' ; select empno,ename,hiredate,deptno from emp where hiredate lik 默认按升序排列； 3.2 示例 例：查询所有雇员信息，按工资由低到高进行排序 例：查询部门 10 的雇员信息，按工资由高到低进行排序，如果工资相同， 则按入职时间由早到晚进行排序。 例：查询雇员编号、姓名、年薪按年薪由高到低排序 六、多表查询 1. 简介 同时从多张表中查询数据，一般来说多张表之间都会存在某种关系 select 列名 1, 列名 2,... from 表名 where 条件 order by 排序字段 1 asc | desc , 排序字段 2 asc | desc ...; select * from emp order by sal; select * from emp where deptno= 10 order by sal desc ,hiredate; select empno,ename,(sal+ifnull(comm, 0 ))* 12 as ' 年薪 ' from emp o 2. 基本用法 2.1 语法 例：将 emp 表和 dept 表进行多表查询 ( 笛卡尔积 ) 通过两张表的关联字段进行比较，去掉笛卡尔积。多表查询时一般都会存 在某种关系。 2.2 示例 例：查询雇员编号、雇员姓名、工资、所在部门名称及位置。 select 列名 1, 列名 2,... from 表名 1 别名 1, 表名 2 别名 2,... where 条件 order by 排序字段 1 asc | desc , 排序字段 2 asc | desc ...; select * from emp,dept ; -- 笛卡尔积，在生产环境中，应尽量避免 select * from emp,dept where emp .deptno =dept .deptno ; 例：查询雇员姓名、工资、入职时间、所在部门编号、部门名称。 提示：对于两个表中都有的字段，我们一般选择父表的。 例：查询雇员姓名、雇员工资、领导姓名，领导工资。 七、子查询 1 、简介 一个查询嵌套着另一个查询，称为子查询 子查询必须放在小括号中 子查询可以出现在任意位置，如 select 、 where 、 having 等 select empno,ename,sal,dname,loc from emp,dept where emp .deptno =dept .deptno ; select empno,ename,sal,dname,loc from emp e,dept d where e .deptno =d .deptno ; select ename,sal,hiredate,d .deptno ,dname from emp e,dept d where e .deptno =d .deptno ; -- 如果多张表中出现同名的列，在查询时需要指 select e .ename ,e .sal ,e .hiredate ,d .deptno ,d .dname from emp e,dept d where e .deptno =d .deptno ; -- 开发时通用写法 2 、基本用法 2.1 语法 2.2 示例 例：查询工资比 7566 雇员工资高的员工信息？ 例：查询工资比部门 30 员工的工资高的雇员信息？ select ( 子查询 ) from ( 子查询 ) 别名 where ( 子查询 ) group by having ( 子查询 ) -- 使用连接查询 select e2.* from emp e2,emp e1 where e1 .empno = 7566 and e2 .sal >e1 .sal -- 使用子查询 select * from emp where sal>( select sal from emp where empno= 7566 ); 注意：将子查询与比较运算符一起使用时，必须保证子查询返回的结果不 能多于 1 个 例：查询雇员的编号、姓名、部门名称。 总结： 一般来说，多表连接查询可以使用子查询替换，但有的子查询不能使用 多表连接查询来替换 子查询的特点：灵活、方便，一般常作为增、删、改、查询操作的条 件，适合于操作一个表的数据 多表连接查询更适合于查看多表中的数据 3 、子查询的分类 可以分为三列： select sal from emp where deptno= 30 ; select * from emp where sal>( select sal from emp where deptno= 30 ) -- 错误的 -- 使用多表连接 select e .empno ,e .ename ,d .dname from emp e,dept d where d .deptno =e .deptno ; -- 使用子查询 select empno,ename,( select dname from dept where deptno=e .dept from emp e; 单列子查询 返回单行单列，使用频率最高 多行子查询 返回多行单列 多列子查询 返回单行多列或多行多列 3.1 单列子查询 例：查询工资比 7654 雇员工资高的，同时又与雇员 7900 从事相同工作的雇 员信息？ 例：查询工资最低的雇员的姓名、职位和工资？ 例：查询工资高于公司平均工资的雇员信息？ select * from emp where sal > ( select sal from emp where empno= 7654 ) and job = ( select job from emp where empno= 7900 ); select ename,job,sal from emp where sal = ( select min (sal) from emp ); 例：查询每个部门的编号和最低工资，要求最低工资大于等于部门 30 的最 低工资？ 例：查询部门的名称、部门的员工数、部门的平均工资、部门的最低收入 雇员的姓名。 select * from emp where sal>( select avg (sal) from emp ); select deptno, min (sal) from emp group by deptno having min (sal)>( select min (sal) from emp where deptno= 30 ); -- 拆分 select deptno, count (empno), avg (sal), min (sal) from emp group by deptno; 例：查询平均工资最低的职位及平均工资？ -- 方式 1 ：使用子查询 select ( select dname from dept where deptno=e .deptno ) dname, count (empno), avg(sal), min(sal), ( select ename from emp where sal=min(e .sal )) ename from emp e group by deptno; -- 方式 2 ：使用多表连接查询 select d .dname ,temp .cnt ,temp .avg ,temp .min ,e .ename from ( select deptno, count (empno) cnt, avg (sal) avg , min (sal) min f ) temp,dept d,emp e where d .deptno =temp .deptno and e .sal =temp .min -- 拆分 select min (t .avg ) from ( select avg (sal) avg from emp group by job ) t; select job, avg (sal) from emp group by job having avg (sal)=( select min (t .avg ) 3.2 多行子查询 对于多行子查询，可以使用如下三种操作符 in 例：查询所在部门编号大于等于 20 的雇员信息？ 例：查询工资与部门 20 中的任意员工相同的雇员信息。 any|some 三种用法： from ( select avg (sal) avg from emp group by job ) t ); select * from emp where deptno>= 20 ; select * from emp where deptno in ( select deptno from dept where deptno>= 20 ); select * from emp where sal in ( select sal from emp where deptno= 20 ); all 两种用法： 3.3 多列子查询 多列子查询一般出现在 from 子句中，作为查询结果集 例：在所从事销售工作的雇员中找出工资大于 1500 的雇员？ =any|some 与任意一个相同，此时与 in 操作符功能一样 >any|some 只要比结果中最小的大即可 <any|some 只要比结果中最大的小即可 select * from emp where sal =any( select sal from emp where deptno= 20 ); > all 比结果中最大的值要大 < all 比结果中最小的值要小 select * from emp where sal > all ( select sal from emp where deptno= 20 ); 八、分页查询 1 、 limit 关键字 作用：用来限制查询返回的记录数 语法规则： 可以接收一个或两个数字 参数 1 用来指定起始行的索引（下标），索引是从 0 开始，即第一行的索 引或下标为 0 能数 2 用来指定返回的记录条数 例：查询工资最高的前 3 名员工的信息？ select temp.* from ( select * from emp where job= "salesman" ) temp where temp .sal > 1500 ; select 列名 1, 列名 2,... from 表名 1 别名 1, 表名 2 别名 2,... where 条件 group by 分组字段 having 分组限定条件 order by 排序字段 1 asc | desc , 排序字段 2 asc | desc ... limit [ 参数 1,] 参数 2 例：查询工资大于 1000 的第 4-8 个雇员的信息。 例：查询工资最低的用户？ 2 、分页 例：每页显示 4 条 (pagesize 每页的大小 ), 显示第 3 页的内容 (pageindex 页码 ) 注意：在 mysql 中 limit 后面的参数不能包含任何运算，实际开发时都是在 编程语言中进行计算，然后将结果发送给数据库执行。 九、常用函数 1 、字符串函数 concat(s1,s2,s3,……) 功能：将多个字符串合并为 1 个字符串 select * from emp order by sal desc limit 0 , 3 ; select * from emp order by sal desc limit 3 ; -- 如果省略参数 1 ， select * from emp where sal> 1000 limit 3 , 5 ; select * from emp order by sal limit 1 ; select * from emp limit (pageindex- 1 )*pagesize,pagesize; -- 计 select * from emp limit ( 3 - 1 )* 4 , 4 ; select concat( 'aa' , 'bb' , 'cc' ); select concat( 'aa' , 'bb' , 'cc' ) from dual ; select concat( " 雇员编号为 : " ,empno, " 的姓名是： " ,ename) from e 注： dual 表是 mysql 提供的一张虚拟表，主要是为了满足 select...from ... 的语法习惯，一般测试时使用，无实际意义。 lower(s) 功能：将字符串 s 中的内容转换为小写字母 select lower('Hello World!') from dual ; upper(s) 功能：将字符串 s 中的内容转换为大写字母 select upper('Hello World!') from dual ; length(s) 功能：测试字符串 s 的长度 select length('hello world!') from dual ; reverse(s) 功能：将字符串反转 select reverse('hello') from dual ; trim(s) 功能：去掉字符串 s 的首尾空格 select trim(' hello world! ') from dual ; replace(s,s1,s2) 功能：将字符串 s 中的 s1 字符串替换为 s2 字符串 select replace('hello world','o','xxhh') from dual ; lpad(s,len,s1) 功能：在字符串 s 的左边使用 s1 进行填充，直到长度为 len 为止 select lpad('hello',8,'#') from dual ; rpad(s,len,s1) 功能：在字符串 s 的右边使用 s1 进行填充，直到长度为 len 为止 select rpad('hello',8,'#') from dual ; substr(s,i,len) 功能：从字符串 s 的第 i 个位置开始取 len 个字符 select substr('hello',2,3) from dual ; 2 、数值函数 ceil(n) 功能：返回大于 n 的最小整数 select ceil(10.1) from dual ; floor(n) 功能：返回小于 n 的最大整数 select floor(10.1) from dual ; round(n,y) 功能：对 n 进行四舍五入，保留 y 位小数 select round(3.1415926,3) from dual ; truncate(n,y) 功能：将 n 保留 y 位小数，不进行四舍五入 select truncate(3.1415926,3) from dual ; rand() 功能：返回 0-1 之间的随机数 select rand() from dual ; 3 、日期和时间函数 now() 返回当前日期时间 select now() from dual ; curdate() 返回当前日期 select curdate() from dual ; curtime() 返回当前时间 select curtime() from dual ; year(date) 返回日期的年 select year('2024 - 11 - 6') from dual ; month(date) 返回日期中的月 select month('2024 - 11 - 6') from dual ; day(date) 返回日期中的日 select day('2024 - 11 - 6') from dual ; timestampdiff(interval,datetime1,datetime2) 返回这两个日期时间之 间相隔的整数，单位是由 interval 来定义 interval 的取值： year 、 month 、 day 、 hour 、 minute 、 second select timestampdiff(day,'2003 - 8 - 28','2024 - 11 - 6') from dual ; date_format(date,pattern) 功能：格式化输出日期时间 select date_format(now(),'%Y 年 %m 月 %d 日 %H ： %i ： %s') ; pattern 格式化参数： %Y 表示四位数字的年 %m 表示两位数字的月 %d 表示两位数字的日 %H 表示两位数字的小时 ,24 小时制 %h 12 小时制 %i 表示两位数字的分钟 %s 表示两位数字的秒 4 、流程控制函数 if(f,v1,v2) 功能：如果 f 条件为真，则返回 v1 ，否则返回 v2 select if(5>2,'yes','no') from dual ; ifnull(v1,v2) 功能：如果 v1 不为 null ，则返回 v1 ，否则返回 v2 select ifnull(null,'0') from dual ; case when f1 then v1 when f2 then v2 ... else v end 如果 f1 为真，则 返回 v1; 如果 f2 为真，则返回 v2,... 否则返回 v 5 、系统信息函数 database() 返回当前操作的数据库 select database() from dual ; user() 返回当前登陆的用户 select user() from dual ; version() 返回 mysql 服务器的版本 select version() from dual ; select case when 5 > 2 then 'yes' end from dual; select case when 5 > 2 then 'yes' else 'no' end from dual; select case when 5 < 2 then 'one' when 6 > 4 then 'two' else 't