mysql数据库基础

最新推荐文章于 2023-09-05 11:19:30 发布

月影无痕xy

最新推荐文章于 2023-09-05 11:19:30 发布

阅读量513

点赞数

文章标签：数据库 java sql

本文链接：https://blog.csdn.net/moonshadow44/article/details/128164281

版权

mysql

select version(); 查看版本号

每一个字段都有：字段名(字符串，数字，日期等等)，数据类型，约束等属性

约束：约束也有很多，其中一个叫唯一性约束，这种约束添加后，该字段中的数据不能重复行上叫记录，列上叫约束

show tables 查看某个数据库下有哪些表

一、关于SQL语言

DQL：数据查询语言(select等)

DML：数据操作语言(增删改，对表中数据) insert 增 delete 删 update 改

DDL：数据定义语言：凡是带有create、drop、alter的都是DDL，DDL主要操作的是表的结构，不是表中数据 create：新建 drop：删除 alter：修改虽然都是增删改，但是与DML不同，这些主要是操作表的结构

TCL：是事务控制语言包括事务提交：commit 事务回滚：rollback

DCL：是数据控制语言例如：授权grant、撤销权限revoke......

二、简单功能

展示数据库: show databases;

使用数据库:use 数据库名

查看当前使用数据库 select database

查看表中数据：select * from 表名;

不看表数据，只看结构:desc 表名;

\c可以用于中断命令

三、简单查询

查询一个字段 : select 字段名 from 表名（select和from都是关键字，字段名和表名都是标识符）

查询多个字段:使用,隔开

查询所有字段:select * from 表名

给查询的列起别名:比如 select dname as deptname from dept; 就是给dname起别名为deptname 只是将查询的显示结果改变，原表列名还是叫dname，别名带空格就加单双引号，数据库中字符串都由单引号括起来，标准

字段可以使用数学表达式

四、条件查询

不是将表中所有数据都查出来，查询出来符合条件的数据

语法格式：select 字段一，字段二.... from 表名 where 条件

条件语句：

基本大小语句，但是不等于用<>

is NULL(is not NULL) ,

and ,

or ,

in(包含，相当于多个or) ,

not not (可以取非，主要用在is或in中)

like like 称为模糊查询，支持%或者下划线匹配

%匹配任意个字符，

下划线，一个下划线匹配一个字符

使用例子 mysql> select ename from emp where sal>=2000; 就是查询满足sal>=2000的ename有哪些

and优先级比or高，优先执行and

in ： select ename from emp where job in ('manager','clerk'); in不是区间，跟的是具体的值

模糊查询：

比如select ename from emp where ename like '%o%' 可以查询ename字段里含有o的字段

select ename from emp where ename like '%T'; 查询ename以T结尾的字段

select ename from emp where ename like('_A%'); 查询ename第二字字符为A的字段

注：下划线代表任意字符，如果查询带下划线的字段，需要\对下划线进行转义，即_

五、排序

select ename from emp order by sal; 以sal排序，默认是升序

指定降序 select ename from emp order by sal desc; 加一个desc 降序

指定升序 select ename from emp order by sal asc; 加一个asc 升序

两个字段排序 select ename from emp order by sal asc, ename asc :先按sal升序排列，若有相同，再按照ename升序排列

也可以select ename,sal from emp order by 2; 按照查询结果的第二列排序，此中为sal，仅理解，不建议使用

执行顺序: select...from...where...order by

六、数据处理函数/单行处理函数

单行处理函数：一个输入对应一个输出

相对的是多行处理函数：一次处理多条记录，多个输入，一个输出

lower:转换小写 select lower(ename) from emp

select lower(ename) as ename from emp

epper：转换大写

substr：取子串 (sunstr(被截取的字符串，起始下标，截取的长度))

select substr(ename,1,1) as ename from emp; 注意：其实下标从一开始

select ename from emp where substr(ename,1,1)='A' 查询enema中首字母为A的成员

trim 去空格

round ：四舍五入 select round(1234.89,1) as result from emp; 1234.9保留到小数点后1位 select round(sal,0) as '薪水' from emp; 将sal中数据四舍五入

rand()生成随机数 select round(rand()*100,0) from emp; 生成100以内随机数

ifnull ifnull(数据，被当做哪个值) 因为只要null参与运算，最终结果都为null 所以为避免如此ifnull可以让某数据为null时，被当做另一个值

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp

case..when..then..when..then..else..end 使用案例：党员工工作岗位是manager时，工资上调10%,salesman工资上调50%,其余不变(注意：！！此语句只是显示，并不改变数据库)

select ename,job,sal as oldsal,(case job when 'manager' then sal1.1 when 'salesman' then sal1.5 else sal end)asnewsal from emp

七、分组函数(多行处理函数)

特点：输入多行，输出一行，会自动忽略null，不需要提前对null进行处理

count 计数

count(具体字段)：统计该字段下所有不为null的元素总数

count(*)：统计表当中的总行数。(只要有一行数据count则++)

sum 求和

avg 平均值

max 最大值

min 最小值

注意：分组函数在使用的时候必须先分组才能使用，如果没有分组则默认整张表为一组

分组函数不能直接用在where语句

比如不能 where sal >min(sal)

八、分组查询

select...from...where...group by...order by...

以上是关键词顺序，执行顺序为1、from 2、where 3、group by 4、select 5、order by

所以上面不能在where语句用分组函数，因为where语句时还没有group by分组，分组函数使用的时候必须先分组

select job,sum(sal) from emp group by job ;

在一条select语句中，如果有group by 语句的话，select后面只能跟：参与分组的字段以及分组函数

如果查询不同部门不同工作岗位的最高薪资技巧：两个字段联合成一个字段看(联合分组)

select deptno,job,max(sal) from emp group by deptno,job;

having可以对分组后的数据进一步过滤，但是不能单独使用，也不能取代where

比如要查询每个部门最高薪资，要求显示最高薪资大于3000的

select deptno,max(sal) from emp group by job having max(sal)>3000;

但是where和having优先选择where，where无法完成再选择having

比如找出每个部门平均薪资，要求显示平均薪资高于2500的

select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

distinct关键字

将查询结果去除重复，原表数据不变

select distinct job from emp;

九、连接查询

从一张表中单独查询称为单表查询

比如从emp表中取员工名字，dept中取部门名字，这种跨表查询，多张表联合起来查询数据被称为连接查询

分类：

内连接：等值连接，非等值连接，自连接

外连接：左外连接，右外连接

全连接

笛卡尔积

当两个表进行连接查询且没有任何限制的时候，最终结果条数为两个表条数的成绩，这种现象称为笛卡尔积

select e.ename,d.dname from emp e,dept d 起别名 where e.deptno = d.deptno; 限制条件，可以看成两个表的连接条件

内连接之等值连接

案例，查询每个员工所在部门名称，显示员工名和部门名

SQL92语法

select e.ename,d.dname from emp e,dept d 起别名 where e.deptno = d.deptno; 限制条件，可以看成两个表的连接条件

SQL99语法：

select -> e.ename,d.dname -> from -> emp e -> join -> dept d -> on -> e.deptno=d.deptno;

where 后续还可以加的筛选条件

92语法后续表连接的条件和后续条件杂糅一起，99语法则是连接条件和后续进一步筛选条件分开，后续如果要进一步添加筛选条件直接加where就可以

99语法

select

e.ename,d.dname

from

emp e

(inner)join inner可以省略

dept d

e.deptno=d.deptno

内连接之非等值连接

案例：找出每个员工薪资等级，要求显示员工名，薪资，薪资等级

select -> e.ename,e.sal,s.grade -> from -> emp e -> join -> salgrade s -> on -> e.sal between s.losal and s.hisal;

内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名

技巧：一张表看成两张表

emp a 员工表

emp b 领导表

select -> a.ename as '员工',b.ename as '领导' -> from -> emp a -> join -> emp b -> on -> a.mgr = b.empno; mgr：对应老板编号 empno：员工编号即员工的领导编号等于领导的员工编号

外连接

既可以显示匹配的字段，也可以显示未匹配的字段，此句中right，可以显示dept中未与emp匹配的字段，既dept中的不与emp中deptno相等的字段也会显示

> select -> e.ename,d.dname -> from -> emp e right join dept d -> on -> e.deptno=d.deptno;

以上right指的是把join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带查询关联查询左边的表

所以内连接两张表查询没有主次关系只要匹配就显示，外连接则是产生了主次关系

三张表，四张表怎么连接？

select...from a join b on a和b连接条件 join c on a和c连接条件 join d on a和d连接条件

案例：找出每个员工的部门名，以及工资等级，要求显示员工名，部门名，薪资，薪资等级

select -> e.ename,d.dname,e.sal,s.grade -> from -> emp e join dept d -> on -> e.deptno=d.deptno -> join salgrade s -> on -> e.sal between s.losal and s.hisal;

连接了三张表

十、子查询

select 语句中嵌套select语句，被嵌套的select语句被称为子查询

可以出现在 select..(select)from..(select)where..(select)

where语句中子查询

案例：找出比最低工资高的员工姓名和工资

> select ename,sal -> from emp -> where -> sal>(select min(sal) from emp);

from中的子查询

注意：from后面的子查询，可以将子查询的查询结果当成一张临时表

案例：找出每个岗位的平均工资的薪资等级

* select -> t.*,s.grade -> from -> (select job,avg(sal) as avgsal from emp group by job) t -> join -> salgrade s -> on -> t.avgsal between s.losal and s.hisal;

重点！！！！，注意from后的子查询中需要给avg(sal)起别名！不然在最后一条的时候，就会是t.avg(sal)，不能这样写，因为会被误以为avg(sal)为函数，所以需要给avg(sal)起别名！！！

select后的子查询(了解)

十一、union

合并查询结果集

> select ename,job from emp where job='manager' -> union -> select ename,job from emp where job='salesman';

以上结果与下面的一致，但是效率更高

select ename,job from emp where job='manager' or job='salesman';

union使用时要求两个结果集的列数相同，而且要求列和列的数据类型也要一致

limit

完整用法：limit startIndex，length 在order by之后执行

(startIndex为起始下标，length为长度，起始下标默认从0开始，和substr不同)

缺省用法：limit 5

将查询结果集的一部分取出来，通常使用在分页查询中

分页的作用是提高用户体验，因为一次全部查出来，用户体验差

案例：按照薪资降序，取出排名在前五名的员工

select ename,sal from emp order by sal desc limit 5;

每页显示pagesize条记录

第1页：limit 0,3

第2页：limit3,3

第3页：limit6,3

第4页：limit9,3

...

每页显示pagesize条记录

第pageNo页：limit(pageNo - 1)*pagesize,pagesize

关于查询语句DQL

select ... from ... where ... group by ... having ... order by ... limit ...

十一、创建表

建表语句：(属于DDL语句，包括create drop alter)

create table 表名(字段名1 数据类型,字段名2 数据类型);

表名：建议以t或者tb1开始

删除表 drop table (if exists)表名

十二、数据类型

常见：varchar char int bigint float double date datetime clob blob

varchar，最长255 可变长度字符串，根据实际数据长度动态分配空间优点：节省空间缺点：动态分配，速度慢

char，最长255 定长字符串优点：速度快缺点：使用不当会造成空间浪费

int，最长11 整数型

bigint 长整型等同于java中long

float 单精度浮点型数据

double 双精度浮点型数据

date 短日期类型

datetime 长日期类型

clob 字符大对象，最多存储4G的字符串，长度超过255的要采取此来存储

blob 二进制大对象，专门存储图片声音视频等流媒体数据，需要使用IO流

示范

create table t_student( no int, name varchar(32), sex char(1), age int(3), emall varchar(225) )

十三、插入数据(DML)

语法格式：insert into 表名(字段名1,字段名2,字段名3,..) values(值1,值2,值3)；

注意：字段名和值要一一对应，什么是一一对应？

数量要对应，数据类型要对应

范例：

insert into t_student(no,name,sex,age,email) values(2,'lisi','f',20,'lisi@123.com');

注意：insert语句只要执行成功，那么必然会多一条记录

default可以设定默认值，建表时可以使用

插入也可以省略前面的字段名，insert into student values() 但是这样后面的字段所有值都要写上

insert 插入日期

format：数字格式化

格式：format(数字,'格式')

str_to_date：将字符串中varchar类型转换成date类型

date_formate：将date类型转换成具有一定格式的varchar字符串类型

create table t_user( id int, name VARCHAR(32), birth date );

创建以上表，如果要插入数据

insert into t_user(id,name,birth) values(1,'zhangsan',STR_TO_DATE('01-10-1990','%d-%m-%Y'));

因为birth格式为date，所有插入的类型也必须为date

日期格式：%Y 年 %m月 %d日 %h 时 %i 分 %s 秒

如果插入的时间格式为'1990-10-01'这种样子，mysql会自动做类型转换，不需要str_to_date

查询时可用date_format语句：select id,name,DATE_FORMAT(birth,'%Y/%m/%d') from t_user;

如果直接查询，则是date的默认格式比如 1990-10-01

date和datetime两个类型的区别

date为短日期，只有年月日

datetime为长日期，有年月日时分秒

mysql短日期默认格式：%Y-%m-%d

mysql长日期默认格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'lili','1990-10-01','2022-11-30 00:01:28');

以上为案例，birth为date形式，create_time为datetime形式

now()函数可以获取系统当前时间，是datetime类型的，比如以上可以给create_time 插入now()

修改update(DML)

语法格式：

update 表名 set 字段名1-值1 ,字段名2-值2,字段名3-值3...where 条件

注意：如果没有条件限制会导致数据全部更新

update t_user set name = 'jack',birth = '2000-10-11' where id=2;

删除数据 delete (DML)

语法格式

delete from 表名 where 条件;

注意：如果没有条件，整张表数据都会删除

十四、补充知识

insert一次插入多条记录

insert into t_user(id,name,birth,create_time) values(1,'zs','1980-10-11',now())

(2,'lisi','1981-10-03',now())

快速建表

create table emp2 as select * from emp;

用查询结果建表

将查询结果插入到一张表中(很少用)

insert into 表查询语句

快速删除表中数据

delete语句删除数据的原理：表中数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放缺点：删除效率低优点：支持回滚rollback，后悔了可以恢复数据

truncate语句删除原理：删除效率高，表被一次截断，物理删除缺点：不支持回滚优点：快速

用法：truncate table (属于DDL操作)

十五、约束

约束是为了保证，表中数据有效

常见约束：

非空约束：not null

唯一性约束：unique

主键约束：primary key

外键约束：foreign key

检查约束：check(mysql不支持，oracle支持)

非空约束not null约束的字段不能为null，需要加一个default默认值

唯一性约束

name varcahr(32) unique 表示name唯一不可重复

联合唯一：

CREATE TABLE t_vip( id int, name varchar(255), email varchar(255), unique(name,email) );

以上就是联合name和email唯一，同时约束没有出现在列的后面，这种叫表级约束

主键约束(非常重要！！！)

字段被not null和unique约束之后自动成为主键(只有mysql这样，oracle不是，用的比较少)

相关术语

主键约束：一种约束

主键字段：该字段添加了主键约束

主键值：主键字段中的每一个值

主键值是每一行记录的唯一标识，身份证号

主键特征：not null + unique

使用：id int primary key 此时为列级约束也可以加到最后 primary key(id) 现在叫表级约束

主键约束一个表中只能有一个

主键值建议使用:int bigint char等类型不建议使用varchar，因为一般都是定长的

主键值可以用auto_increment生成，从一开始，以一递增

id int primary key auto_increment

外键约束(非常重要！！！)

删除外键约束：alter table 从表名 drop foreign key 外键约束名

foreign key,简称FK

涉及术语

外键约束：一种约束

外键字段：该字段加上外键约束

外键值：外键字段当中的每一个值

t_student是字表，t_class是父表，因为t_student里的班级号cno引用了t_class中的cno，所以需要对cno进行约束，如果不约束，可能会导致数据无效，比如可能t_student里出现一个cno为102的，但是t_class中不存在102的cno

删除表时：要先删除子表才能删除父表，因为子表在引用父表内字段时不能删除

创建表时：先创建父，再创建子

删除数据的顺序：先删子，再删父

插入数据的顺序：先插入父，再插入子

CREATE table t_class( classno int primary key, name varchar(255) );

CREATE TABLE t_student( no int primary key auto_increment, name varchar(255), cno int, foreign key(cno) references t_class(classno) );

还可以在foreign key() references ___()后添加 on update cascade 和 on delete set null

前者指的是级联修改，即两表连接的字段修改一个另一个也会变；后者指将删除改为变成null