mysql&多表查询知识点总结

DDL：数据定义语言
操作数据库：
启动服务：net start mysql
登录： mysql -h localhost -u root -p root
创建数据库：create database 数据库名称；
删除数据库：drop database 数据库名称；
常用的命令
查看所有的数据库：show databases;
操作表：
创建表：create table 表名(字段描述，字段描述）；
字段描述：字段名称 字段类型 [约束]
修改表：
格式：
alter table 表名…
修改表名：
alter table 旧表名 rename to 新表名；
添加字段：
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 表名；
常用的命令：
切换或者进入数据库 ：use 数据库名称；
查看当前数据库下的所有表 ：show tables;
查看表结构：desc 表名；
查看建表语句：show create table 表名；
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
DML：数据操作语言
操作对象：记录（行）
关键字：insert update delete
插入：
格式：insert into values (字段值1，字段值2,,,,,,,）；
注意：默认插入全部字段，必须保证values后面的内容和表结构顺序保持一致
字段类型为数字可以省略引号，引号建议使用单引号；
格式二：insert into 表名(字段名，字段名，)values （字段值，字段值，）；
修改：
update 表名 set 字段名=字段值，字段名1=字段值1….[where 条件];
update user set username=’jerry’ where username=’jack’;
删除：
格式：
delete from 表名[where 条件]
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
DQL：数据查询语言
操作对象：记录
关键词：select ….from 表名 where 条件 group by having 分组字段 order by排序字段 ase|desc
查看所有：select * from 表名；
查看指定字段：
select 字段名，字段名from 表名；
去重操作 distinct
select distinct 字段名，字段名 from 表名；
对结果进行运算，不影响数据库的值
select 表名+10 from 表名
可以起表名 字段名 [as] 别名 如果别名有空格记得加引号；
模糊匹配
格式：字段名 Like “匹配规则”；
匹配内容 %
“龙” 值为龙
“%龙” 值以龙结尾
“龙%” 值以龙开头
“%龙%” 值包含龙
匹配内容 “__”占两个位置
条件查询：
select * from 表名 where 字段名 =xx or 字段名=xx or 字段名=xx;
select * from 表名 where 字段名in(38,68,98);
DQL高级查询——————————————————-
排序查询：select * from 表名 order by 字段名； asc 升序 desc 降序
select * from 表名 where 条件 order by asc;(带条件）
聚合函数：
* sum()求和 avg()平均数 max() min() count()计数求和；
对一列进行计算 返回值是一个 ，特点忽略null值
select sum(字段名） from 表名；
select avg(字段名） from 表名；
round（值，保留小数位）四舍五入
select count(*) from 表名；

分组：使用group by 在where 后面 order by 前面
select cno,count(*) from 表名 group by con;
select cno,sum(字段名） from 表名 group by cno having sum(字段名)>200;
注意：
where 和having 的区别
1，where 是对分组前的数据进行过滤 having 是对分组后的数据进行过滤
2，where 后面不能使用聚合函数，having可以

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
数据数据类型：
java mysql
byte tinyint
short smallint
int int
long bigint
char/String varchar|char
varchar：可变长度
char：固定长度
boolean tinyint|int代替
float|double float|double
注意：double(5,2)：该小数长度为5，小数占两个 最大值999.99
double

java.sql.Date Date日期
java.sql.Time Time 时间
java.sql.Timestame timestamp 时间戳 若给定的时间为null会把数据库当前时间赋值
datetime 日期+时间

java.sql.clob(长文本） text
ajva.sql.Blob(二进制） blob

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
约束：
作用：为了保证数据的有效性和完整性
mysql中的常见约束：
主键约束：primary key
唯一约束：unique
非空约束：not null
外键约束：foreign key
主键约束：被主键约束修饰过的字段 唯一 非空
注意：一张表只能有一个主键，这个主键可以包含多个字段
方式1：在建表的时候添加约束 格式：字段名称 字段类型 primary key
方式2：在约束区域添加约束 格式 ：primary key
所有的字段都声明完成后，就是约束区域
格式:primary Key (字段名，字段名2）
方式3；建完表后，通过修改表结构来添加约束
alter table 表名 add primary key(字段名，字段名2）；
两个主键：联合主键，只有两个字段都一样才认为是一条记录

唯一约束：被修饰过的字段唯一，对null不起作用
方式1：在建表的时候添加约束 格式：字段名称 字段类型 unique
方式2：在约束区域添加约束 格式 ：unique
方式三：建表之后：alter table 表名 add unique（字段名，字段2）添加联合唯一
alter table 表名 add unique （字段1）；给一个添加唯一

非空约束：被修饰过的字段非空
格式 ：create table nn (id int not null) ;

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
truncate 清空表
格式：truncate 表名；删除表 ，重新创建一个新表
和delete from 区别 ：
delete 属于 DML（数据操作语言） 语句 truncate 属于DDL（数据定义语言）
delete 逐条删除 删除数据不会删除表 truncate 删除表，重新创建一个新表 id会重新开始

auto_increment 自增
要求：
被修饰的字段类型支持自增，一般为int
被修饰的字段必须是一个key 一般是primary key

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
多表操作，创建多表，尅表述表与表之间的关系
把网上商城里面用到的实体创建成表，并且将他们之间建立关系
技术分析：sql
…………………
网上商城的实体
用户 订单 商品 分类
常见的关系：
一对多：用户和订单 分类和商品
多对多：订单和商品 学生和课程
一对一：丈夫和妻子 国家领导人和国家
ER图可以描述实体和实体的关系
实体用矩形表示
属性用椭圆表述
关系用菱形表示

在开发中，我们将一对多的关系，一方我们称为一表或者主表，多方称为多表或者从表。
为了表示一对多的关系，一般会在多表的一方，会添加一个字段，字段名自定义（建议：主表名_id) 字段类型一般和主表的主键类型保持一致。我们称这个字段为外键。
例如：用户和订单
用户表
create table user (
id int primary key auto_increment,
username varchar(20)
);
订单表
create table orders(
oid int primary key auto_increment,
totaprice double ,
user_id int
);
ERR
为了保证数据的有效性和完整性，应该添加约束（外键约束）
在多表的一方，添加外键约束
格式：
alter table 多表名 add foreign key (外键名称） references 一表名（主键）；

如果里面有垃圾数据就不能添加外键，因为他的目的就是维护数据的有效性和完整性。

添加了外键以后有如下特点：
1.主表中不能删除从表中已经引用的数据
2.从表中不能添加主表中不存在的数据
当然也可以通过java来进行维护数据的有效性和完整性

开发中如何处理一对多？
在多表中添加一个外键，名称一般为主表的名称_id 字段类型一般和主表的主键的类型保持一致，为了保证数据的有效性和完整性需要在多表的外键上添加外键约约束。

、、、、、、、、、、、、、、、、、、、
多对多
商品和订单

多对多：在开中我们一般引入一个中间表，在中间表中存放两张表的主键，一般还会将这两个主键设置成中间表的联合主键。将多对多拆分成两个一对多。
为了保证数据的有效性和完整性，在中间表上添加两个外键约束即可，
创建商品表；
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);
创建中间表：
create table orderitem(
oid int ,
pid int
);
为中间表添加外键约束
alter table orderitem add foreign key(oid) references orders(oid);
alter table orderitem add foreign key(pid) references product(id);

在开发中处理多对多引入一张中间表，然后存放两张表的主键，一般会将这个字段设置成联合主键，这样就可以将多对多的关系拆分成两个一对多
为了保证数据的有效性和完整性
需要在中间表添加两个外键约束。

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
案例3多表查询
技术分析
内连接
外连接
子查询
///////////////////////////////////////////////////////////////
笛卡尔积：
多长表无条件的联合查询，没有任何意义
select a.,b. from a,b;
内连接：
格式1：
显示的内连接：select a.,b. from a [inner] join b on ab的连接条件
格式2：
隐式的内连接：select a.,b. from a,b where ab的连接条件
例如：
select user.,orders. from user,orders where user.id=orders.user_id;
select user.,orders. from user join orders on user.id=order.user_id;

外连接：意思先展示a表的所有数据，根据条件关联查询join右边的表，符合条件则展示出来。不符合就以null值展示。

左外连接：select a.,b. from a left [outer] join b on 连接条件
例如：查询所有用户的订单详情
select user.,orders. from user left join orders on user.id=user_id;
右外连：select a.,b. from a right [outer] join a on 连接条件
先展示join右边表的数据，根据条件关联查询左边的表的，符合条件则展示，不符合条件则以null展示。
例如：select orders.,user. from user right join orders on users.id=user_id;

子查询：一个查询依赖于另一个查询
查询用户为张三的订单详情
1.先查询张三的id
select id from user where username=”张三“；
2.再查询张三的订单
select * from orders where user_id=1；
子查询：两个合二为一
select * from orders where user_id=(select id from user where username=”张三“；);
查询订单价格大于三百的用户信息
1.查询出订单价格大于300的用户的id
select user_id from orders where price>300;
2.再通过订单id查询出用户的信息 select * from user where id in(3,3,5,,null);
整合：select * from user where id in(select user_id from orders where price>300);
查询出订单价格大于300的订单信息以相关的用户信息。
内连接：select orders.,user. from orders,user where user.id=orders.user_id and orders.price>300;

给表其别名；表 [as] 别名

一对一：
在开发中，例如person idcard
思路：将两个实体和二为一
思路2：在一个表上将这个表的主键设置成外键并且添加外键约束