mysql回顾&多表查询

MySQL基础与多表查询

最新推荐文章于 2025-03-22 03:32:32 发布

原创最新推荐文章于 2025-03-22 03:32:32 发布 · 置顶 · 184 阅读

0 ·

CC 4.0 BY-SA版权

JavaWeb 专栏收录该内容

12 篇文章

订阅专栏

mysql回顾&多表查询

一、mysql基础知识（了解）

1.数据库
本质上就是一个文件系统.通过标准的sql语句对数据进行curd操作

2.增删改查操作
C:create 创建
U:update 修改
R:read 读|检索查询
D:delete 删除

3.数据库类型
软件名       厂商特点
mysql oracle       开源的数据库
oracle oracle       大型的收费的数据库
DB2 IBM       大型的收费的数据库
sqlserver   微软中大型的收费的数据库
sybase       sybase(powerdesigner)
注：前三种较常用

4.SQL（结构化查询语句）
作用:管理数据库
分类:（常见面试题）★
   DDL:数据定义语言
       操作对象:数据库和表
       关键词:create alter drop
   DML:数据操作语言
       操作对象:记录
   DQL:数据查询语言(非官方)
   DCL:数据控制语言
       操作对象:用户事务权限

5.启动数据库方式
方式一：
右键我的电脑-管理-服务和应用程序-服务-找到mysql，右键，启动
方式二：
打开cmd-输入services.msc，确定-找到mysql，右键，启动
方式三：
打开cmd-输入cmd，确定-输入net start mysql,回车

6.登录数据库命令行
mysql -uroot -p密码

7.mysql注释方式
方式一：--空格
方式二：#

8.数据类型(了解)
   java mysql
   byte tinyint
   short smallint
   int int(★)
   long bigint
   char/String      varchar（size)(★)|char
varchar:可变长度 mysql的方言 varchar(20): 存放abc 只会占用三个
char:固定长度 char(20) 存放abc 占用20个
   boolean           tinyint|int 代替
   float|double float|double
注意:double(5,2):该小数长度为5个,小数占2个最大值:999.99

   java.sql.Date date 日期
   java.sql.Time   time 时间
java.sql.Timestamp   timestamp(★) 时间戳
若给定值为null,数据库会把当前的系统时间存放到数据库中
datetime(★) 日期+时间

   java.sql.Clob(长文本)   mysql的方言(text) 例如：存小说
   java.sql.Blob(二进制)   blob 例如：存图片

9.约束
（1）作用:为了保证数据的有效性和完整性
（2）mysql中常用的约束:主键约束(primary key) 唯一约束(unique)
非空约束(not null) 外键约束(foreign key)
   a.主键约束:被修饰过的字段唯一非空
   注:一张表只能有一个主键,这个主键可以包含多个字段
       方式1:建表的同时添加约束（只能给一个字段添加主键）
       格式: 字段名称字段类型 primary key
       方式2:建表的同时在约束区域添加约束（所有的字段声明完成之后,就是约束区域）
           格式: primary key(字段1,字段2)
           例如：
               create table pk01(
                   id int,
                   username varchar(20),
                   primary key (id)
               );
       方式3:建表之后,通过修改表结构添加约束
       例如：
               create table pk02(
                   id int,
                   username varchar(20)
               );
           格式：
           alter table pk02 add primary key(字段名1,字段名2..);
           例如：
           alter table pk02 add primary key(id,username);
   b.唯一约束(了解):被修饰过的字段唯一,对null不起作用
       方式1:建表的同时添加约束
       格式: 字段名称字段类型 unique
       方式2:建表的同时在约束区域添加约束（所有的字段声明完成之后,就是约束区域）
           格式: unique(字段1,字段2...)
       方式3:建表之后,通过修改表结构添加约束
           alter table 表名 add unique(字段1,字段2);-- 添加的联合唯一
   c.非空约束(了解):被修饰过的字段非空
       方式:
           create table nn(
               id int not null,
               username varchar(20) not null
           );

10.truncate 清空表 ★
   格式:
       truncate 表名;
   和delete from 区别:（常见面试题）★
       delete属于DML语句 truncate属于DDL语句
       delete逐条删除   truncate干掉表,重新创建一张空表

11.ER图（可以描述实体与实体之间的关系）
   实体用矩形表示
   属性用椭圆表示
   关系用菱形表示

二、sql分类

1.DDL:数据定义语言
操作对象:数据库和表
关键词:create alter drop truncate
（1）操作数据库:
   创建数据库:create database 数据库名称;
   删除数据库:drop database 数据库名称;
   常用命令:
   查看所有的数据库: show databases;
（2）操作表:
   创建表:
   格式:
           create table 表名(字段描述,字段描述);
           字段描述:字段名称字段类型 [约束]
       例如：
           create table user(
               id int primary key auto_increment,-- 自增
               username varchar(20)
           );
   修改表:
       格式:
           alter table 表名 ....
       修改表名:
           alter table 旧表名 rename to 新表名;
           例如:
               alter table user1 rename to user10;
       添加字段:
           alter table 表名 add [column] 字段描述;
           例如:
               alter table user add password varchar(20);
       修改字段名:
           alter table 表名 change 字段名称新字段描述;
           例如:
               alter table user change password pwd varchar(20);
       修改字段描述:
           alter table 表名 modify 字段名称字段类型 [约束];
           例如:
               alter table user modify pwd int;
       删除字段:
           alter table 表名 drop 字段名;
           例如:
               alter table user drop pwd;
   删除表
       格式:
           drop table 表名;
           例如：
       drop table user;
   常用命令:
       切换或者进入数据库: use 数据库名称;
       查看当前数据库下所有表: show tables;
       查看表结构:desc 表名;
       查看建表语句:show create table 表名;

2.DML:数据操作语言
操作对象:记录(行)
关键词:insert update delete
（1）插入:
       格式1:（插入所有的字段）
           insert into 表名 values(字段值1,字段值2...,字段值n);
       例如:
           insert into user values(1,'tom');
       格式2:（插入指定的字段）
           insert into 表名(字段名,字段名1...) values(字段值,字段值1...);
       例如:
           insert into user (username,id) values('jack',4);
（2）更新:
       格式:
           update 表名 set 字段名=字段值,字段名1=字段值1... [where 条件];
       例如:
           update user set username='jerry' where username='jack';
（3）删除:
       格式:
           delete from 表名 [where 条件];
       例如:
           delete from user where id = '2';

3.DQL:数据查询语言
关键词:select
（1）格式:
       select ... from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 ase|desc
（2）执行顺序:
   a.确定数据来自哪张表 from
   b.是否需要筛选 where
   c.是否需要分组 group by
   d.分组后是否需要筛选 having
   e.是否需要排序 order by
   f.确定显示那些数据. select
（3）查询分类（了解）
a.简单查询
       去重操作 distinct
           格式:
           select distinct 字段名1,字段名2 from 表名
           例如：
           select distinct price from products;
       给列起别名
           格式:
           字段名 [as] 别名
           例如：
           select price+10 新价格 from products;
   b.条件查询（where 条件）
   模糊匹配
           格式: 字段名 like "匹配规则";
               匹配内容 %
                   "龙"   值为龙
                   "%龙"   值以"龙"结尾
                   "龙%"   值以"龙"开头
                   "%龙%"    值包含"龙"
               匹配个数 "__" 占两个位置
           例如：
           select * from products where pname like "%新%";
       where后的条件写法
           * > ,<,=,>=,<=,<>,!=
           * like 使用占位符 _ 和 % _代表一个字符 %代表任意个字符.
           例如：select * from product where pname like '%新%';
           * in在某个范围中获得值.
           例如：select * from product where pid in (2,5,8);
           * between 较小值 and 较大值
           例如：select * from products where price between 50 and 70;
   c.排序查询（order by 排序字段 ase|desc）
   asc-升序,desc-降序
   d.聚合函数：对一列进行计算返回值是一个,忽略null值
   * sum(),avg(),max(),min(),count();
       获得所有商品的价格的总和：
           select sum(price) from products;
       获得商品表中价格的平均数：
           select avg(price) from products;
           -- round(值,保留小数位)
           select round(avg(price),2) from products;
       获得商品表中有多少条记录：
           select count(*) from products;
   e.分组（group by 分组字段）
   where和having区别:
           where 是对分组前的数据进行过滤 ;having 是对分组后的数据进行过滤
           where 后面不能使用聚合函数,having可以

三、多表创建及描述关系

常见关系:
   一对多. 例如：用户和订单分类和商品
   多对多. 例如：订单和商品学生和课程
   一对一. 例如：丈夫和妻子

1.一对多
（1）在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表
（2）为了保证数据的有效性和完整性,添加约束(外键约束).
   在多表的一方添加外键约束(不是必须的,也可以通过java程序来控制)
       格式:
           alter table 多表名称 add foreign key(外键名称) references 一表名称(主键名称);
       例如:
           alter table orders add foreign key(user_id) references user(id);
（3）添加了外键约束之后有如下特点:★
   a.主表中不能删除从表中已引用的数据
   b.从表中不能添加主表中不存在的数据
（4）开发中处理一对多:★
   在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
   为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.

2.多对多
开发中处理多对多:★
   引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了。
   为了保证数据的有效性和完整性，需要在中间表上添加两个外键约束(不是必须的,也可以通过java程序来控制)

3.一对一（了解）
   在开发中,例如:person idcard
       思路1:将两个实体合二为一(字段比较少)
       思路2:在一个表上将这个表的主键设置成外键且添加外键约束.

四、多表查询

1.内连接 ★
   格式1:显式内连接
       select a.* ,b.* from a join b on ab的连接条件;
   格式2:隐式内连接
       select a.*,b.* from a,b where ab的连接条件;

2.外连接 ★
   左外连接格式:★
       select a.*,b.* from a left join b on 连接条件.
       展示a表所有数据,根据条件关联查询b表,满足条件展示,不满足以null值展示

3.子查询 ★
一个查询依赖另一个查询.
   给表起别名:
       表 [as] 别名