Mysql 学习笔记

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

sql 的分类

DDL:数据定义语言
操作对象：数据库和表
关键词：create 。alter，drop
DML:数据操作语言
操作对象：记录
DQL:数据查询语言
DCL:数据控制语言
操作对象：用户，事物，权限

DDL

操作数据库：
	创建：
		create database 数据库名称；
	删除：
		drop database 数据库名称；
操作表：
	创建：
		create table user （
			id  int  primary  key  auto_increment,
			username varchar(20)
		）; 

DML

插入：
	insert into 表名 values （字段值，字段值...）；
	insert into 表名 （字段名1，。。。） values （字段值，...）；
修改：
	update 表名 set 字段名 =  字段值，[wehere 条件]；
删除：
	delete from 表名 [where条件]；
	例句：select * from user where id = 2；

DQL

查询：
	select ... from 表名  where  条件 group by  分组字段  having 条件 order by 排序字段  asd|desc;
	 查询所有： select * from 表名；
	 查询指定字段： select 字段，字段， from 表名；
	 去重操作： select distinct  字段，字段 from 表名；
	 别名：select 字段或表达式  （别名） from 表名；
	 	    select 字段或表达式  （'别名'） from 表名；
	 	    select 字段或表达式  （别  名） from 表名；错误，不能有空格
	 	    select 字段或表达式  （`别  名`） from 表名；用重音符包裹，正确
         例句：
         查询所有商品名称为十三香的商品所有信息：
         select *  from products where pname = ‘十三香’；
         查询价格大于60 的商品
         select *  from products where price>60；
         查询涨品种包换“新”的商品
         	格式：字段名 like “匹配规则”；
         		   匹配内容%
         		   		“新”         值为新
         		   		“%新”        值以新结尾
         		   		“新%”        值以新开始
         		   		“%新%”       值中包含新	         
       查询价格为 10 ，20 ，30的商品
       select * from products where price in（10,20,30）；
       价格在50 到 70 之内
       select * from products where price between 50 and 70；
       查询所有商品价格排序asc | desc
       select * from products order by price (desc);  默认asc升序

聚合函数

聚合函数会忽略null

sum，avg，max，min，count
	获取所有商品的价格总和：
	select  sum (price) from productes;
	平均值，保留两位小数
	select round（avg（price），2）from products；
	查询记录条数
	select count（*） from products；

分组

	根据cno字段分组，分组后统计商品个数
	select cno，count（*） from products group by cno；
	根据cno分组，分组统计每组商品的总数量，并且总数量大于200；
	select cno，sum（pnum）from products group by cno having sum（pnum）>200；

where是对分组前的数据过滤，having是对分组后的数据过滤
where后面不能使用聚合函数，having可以。

数据类型

java	mysql
byte	tinyint
short	smallint
int	int
long	bigint
char/string	varchar/char varchar可变长度，char固定长度
boolean	tinyint/int 代替
float/double	folat/double double(5,2) 小数长度为5，小数位站两位， 最大值999.99
data	date 日期
time	time 时间
timestamp	timestamp 时间戳，若给定的值为null，数据库会把当前的系统时间存放到数据库中
空	datetime 时间+日期
Clob	text 存放文本
blob	blob 二进制文件 电影音乐等

约束

为了保证数据的有效性和完整性

主键约束：primary key

	被修饰过的字段 唯一 非空
	一张表只能有一个主键，这个逐渐可以包含多个字段
	方式1：字段名称  字段类型  primary key
	方式2：所有的字段声明完成之后，primary key （字段一，字段2）
	方式3：alter table pk02 add primary key （字段）

唯一约束：unique

	被修饰过的字段唯一，用法与主键约束一样

非空约束：not null

	字段 not null 

外键约束：foreign key

	为保证数据的有效性和完整性，添加约束，外键约束
	再多标的一方添加外键约束
	alter table 多表名称 add foreign key （外键名称） references 一表名称（主键 ）

1.主表中不能删除从表中已引用的数据
2.从表中不能添加主表中不存在的数据

truncate

truncate 表名；
truncate 操作的是表，delect 操作的是记录。truncate 是干掉表，重新创建，新的记录从1开始，delete 内部维护了一个auto increment

多表查询

为保证数据的有效性和完整性，添加约束，外键约束
一对多的关系在约束中已经给出
多对多：在开发中我们一般引入一张中间表，在中间表中存放两张表的主键，一般还会将这两个中间表的主键设置成中间表的联合主键，将多对多拆分成两个一对一。
为了保证数据的有效性和完整性，在中间表上添加两个外键约束即可

alter  table 中间表 add foregin key（外键字段1）reference orders(id)；
alter  table 中间表 add foregin key（外键字段2）reference product(id)；

笛卡尔积

多张表无条件联合查询，没有任何意义

		select a.*,b.* from a,b;

内连接

格式1：显示内连接

select a.* ,b.* from  a [inner] join b on ab的连接条件
select user.*,orders.* from user JOIN orders ON user.id = orders.user_id;

格式2：隐式内连接

select a.* ,b.* from  a , b where ab的连接条件
select user.*,orders.* from user,orders where user.id=orders.user_id;

外链接

左连接：

select a.* ,b.* from a left [outer] join b on 连接条件
先展示join左边的 a 表的所有数据，根据条件关联查询 join 右边的表b ，符合条件则展示出来，不符合以 null 值展示。
select user.*,orders.* from user left join orders on user.id= orders.user_id;

右连接：

select orders.* ，user.*from user  right join orders on user.id = orders.user_id;
左右连接可以等价转换。

子查询

一个查询以来另一个查询

select * from orders where user_id = (select id from user where username = '张三');
select * from user where id in (select user_id from orders where price>300);
另一种方式：
是将一个查询的结果作为一张临时表
select user.*,tmp.* from user,(select * from orders where price >300) as tmp where user.id = tmp.user_id;