mysql笔记

数据库就是一个文件系统,这个文件必须通过标准的sql来访问

常用的关系型数据库
关系型数据库存放的都是实体之间的关系

MySQL       ：免费的小型的数据库,现在被Oracle收购.
Oracle      ：Oracle公司收费的大型的数据库.
SQLServer   ：微软公司收费中型的数据库.
DB2         ：IBM公司收费的大型的数据库.
SyBase      ：SyBase公司收费的数据库.已经被淘汰.PowerDesigner数据建模的工具.
SQLite      ：小型的嵌入式的数据库.
***** Java程序中经常使用的数据库
MySQL
Oracle

MySQL数据类型
数值类型:
TINYINT 小整数值 (-128,127)
SMALLINT 大整数值
MEDIUMINT 大整数值
INT/INTEGER 大整数值
BINGINT 极大整数值
FLOAT 单精度浮点数值
DOUBLE 双精度浮点整数值
DECIMAL 小数值

日期和时间类型
DATE
TIME
DATETIME
TIMESTAMP 会使用系统时间作为默认值

字符串类型
CHAR 定长字符串
VARCHAR 变长字符串
TINYTEXT 短文本字符串
BLOB 二进制形式的长文本数据
TEXT 长文本数据
MEDIUMTEXT 中等长度文本数据
LONGBLOB 二进制形式的极大文本数据
LONGTEXT 极大文本数据

MySQL创建数据库
语法:
* create database 数据库名称 [character set 字符集 collate 字符集校对];
练习:
* 创建db1;
* create database db1;
* 创建一个带有字符集的数据库db2;
* create database db2 character set gbk;
* 创建一个带有字符集和校对规则的数据库db3;
* create database db3 character set utf8 collate utf8_bin;

查看数据库
语法:
* 查看数据库服务器中所有的数据库:
* show databases;
* 查看某个数据库的定义信息.
* show create database 数据库名;
* 查看当前正在使用的数据库信息.
* select database();

删除数据库
语法:
* 删除数据库:
* drop database 数据库名;

切换数据库
语法:
* use 数据库名称;

• Java类型: MySQL:
  byte/short/int/long tinyint/smallint/int/bigint
  String char/varchar
  * 区别?char是固定长度的字符串,varchar可变长度的字符串.
  * char(8) 和 varchar(8)
  * 如果插入一个字符串hello 插入到char 那么 插入hello .插入到varchar中 插入hello
  float float
  double double
  boolean bit
  Date date/time/datetime/timestamp
  * datetime和timestamp都是既有日期又有时间的日期类型
  * 区别? datetime需要使用外部传入的日期.如果没传这个值就是Null. timestamp会使用系统当前的时间作为这个值的默认值.
  文本文件 Text
  二级制文件 BLOB
  * Oralce使用CLOB/BLOB
  * MYSQL中除了字符串类型需要设置长度其他的类型都有默认长度.

约束：
单表约束：
* 主键约束：primary key (默认就是唯一非空的)
* 唯一约束：unique
* 非空约束：not null

创建一个表：
* 创建表之前先选择数据库:use 某个数据库;
create table employee(
eid int primary key auto_increment,
ename varchar(20) not null,
email varchar(30) unique,
birthday date,
job varchar(20),
resume text
);

查看数据库中有哪些表：
* show tables;
查看表结构:
* desc 表名;

表的删除:
* drop table 表名;
修改表添加列：
* alter table 表名 add 列名 类型(长度) 约束;
* alter table employee add image varchar(50);
修改表删除列：
* alter table 表名 drop 列名;
* alter table employee drop job;
修改表的列的类型长度及约束:
* alter table 表名 modify 列名 类型(长度) 约束;
* alter table employee modify image varchar(80) not null;
修改表的列名
* alter table 表名 change 旧列名 新列名 类型(长度) 约束;
* alter table employee change image eimage varchar(60);
修改表名
* rename table 旧表名 to 新表名;
* rename table employee to user;
修改表的字符集：
* alter table 表名character set 字符集;
* alter table user character set gbk;

create table 表名(列一,列二,列三);
CREATE TABLE test
(列一 INT PRIMARY KEY AUTO_INCREMENT, //主键 自增
列二 VARCHAR(20) NOT NULL ,
字符串 VARCHAR(20) NOT NULL ,
时间 DATE
);

sql语句分类
DDL:数据定义语言
* create,alter,drop…
DML:数据操纵语言
* update,insert,delete
DCL:数据控制语言
* grant,if..
DQL:数据查询语言
* select

CRUD操作
插入操作
注意 类型 长度 列的个数对应

insert into 表名(列1,列二,列三)values(值一,值二,值三);

插入中文字符集不统一会报错,修改字符集
show variables like’%character%’ 显示字符集

语法
* insert into 表名 (列名,列名,…) values (值1,值2,…); —插入指定列的值
* insert into 表名 values (值1,值2,…); —插入所有列的值

注意事项：
* 列名的个数与值的个数对应.
* 列的类型与值的类型对应.位置也要对应.
* 列的类型如果是字符串或者日期,写值的时候使用单引号将值引起来.
* 插入的值的最大长度不能超过列的最大长度.

插入记录：
* 插入某几列的值:
* insert into employee (eid,ename,email) values (null,’aaa’,’aaa@itcast.cn’);
* 插入所有列的值:
* insert into employee values (null,’bbb’,’bbb@itcast.cn’,’1990-09-01’,’HR’,’I am HR’);
插入中文：
insert into employee (eid,ename,email) values (null,’张三’,’aaa@163.cn’);
ERROR 1366 (HY000): Incorrect string value: ‘\xD5\xC5\xC8\xFD’ for column ‘ename’ at row 1
* 插入中文问题的解决：
* show variables like ‘%character%’;
* * 找到MYSQL的安装路径/my.ini文件：
*

修改操作

update 表名 set 列名=值 , 列名=值 where 条件

注意事项:
列名和值得类型一致 不能超过长度

【修改记录】
语法：
* update 表 set 列名=值,列名=值 [where 条件];
注意事项：
* 列名和值类型也要一致.
* 值不能超过列的最大长度.
* 值是字符串或日期,需要使用单引号.
练习：
* 修改employee表中所有记录的job为WORKER
* update employee set job=’WORKER’;
* 修改employee表将name为aaa的邮箱改为aaa@163.com
* update employee set email = ‘aaa@163.com’ where ename = ‘aaa’;
* 修改employee表将name为bbb的邮箱改为bbb@163.com同时修改job为HR
* update employee set email = ‘bbb@163.com’ , job=’HR’ where ename=’bbb’;

删除操作

delete from 表 where 条件

注意事项
删除表中的一行记录 不是一列
不加条件就是删除全部记录
语法：
* delete from 表 [where 条件];
注意事项：
* 删除表中的一行记录,不能删除某列值
* 如果没有条件删除表中的所有列.
练习：
* 删除id为8的记录:
* delete from employee where eid = 8;
* 删除所有记录:
* delete from employee;

truncate table 表名
delete form 表名
都是删除所有记录 , truncate是将整个表删除掉再新建一张表,delete是一条一条进行删除的 , 表中记录很多用truncate效率很高,
事物管理上只能作用于DML语句上 , delete是可以通过事物找回 , truncate属于DDL不行

查询操作

select * from 表民 where 条件
select (distinct) from 表民 where 条件 去掉重复值 要所有字段都重复
select 列一 ,列二 from 表 where 条件
select 列一 命名一,列二 as 命名二 from 表 where 条件

where语句后面可以加:
= , 大于 , 小于 , like , in

查询语句：
* select [distinct] *|列名 from 表 [where 条件];
* 查询所有记录:
* select * from exam;
查询这个班级人的姓名和英语成绩:
* select name,english from exam;
查询英语成绩,将重复英语成绩去掉:
* select distinct english from exam;
查询李四的学生成绩:
select * from exam where name=’李四’;
查询名称叫李四并且英语成绩大于90的
select * from exam where name=’李四’ and english >90;
将成绩+10分进行显示:
select name ,english+10,chinese+10 ,math+10 from exam;
显示这个人的名称和对应总成绩的分数:
select name,english+chinese+math from exam;
使用as起别名,as可以省略.
select name , english+chinese+math as sum from exam;

【条件查询】
where语句后面可以加：
条件的关键字：
= , > , >= , <, <= , <>

like中可以使用占位符: _ 和 % ：下划线匹配一个字符, %:可以匹配任意多个字符.
* like ‘张％’; like ‘张_’; like ‘%明’; like ‘%明%’;

in 后跟着一组值.
* id in (1,2,3)

and or not

【排序查询】
order by 对数据进行排序.默认升序. （asc升序,desc降序）
* 查询所有学生的信息,并且按语文成绩进行排序.
* select * from exam order by chinese;
* 查询所有学生的信息,并且按语文成绩进行降序排序.
* select * from exam order by chinese desc;
* 查询学生的信息,按照英语成绩降序排序，如果英语成绩相同,按照语文降序.
* select * from exam order by english desc, chinese desc;
* 查询姓李的学生的信息,同时按照英语升序排序.
* select * from exam where name like ‘李%’ order by english asc;

【聚合函数】
sum()
count()
max()
min()
avg()
* 查询每个学生总成绩:
* select name,(english+chinese+math) from exam;
* 统计所有学生的总分:
* select sum(english+chinese+math) from exam; – ifnull(english,0)
* select sum(english)+sum(chinese)+sum(math) from exam;
* 统计学生的个数:
* select count(*) from exam;
* 统计英语成绩的最高分:
* select max(english) from exam;
* 统计语文成绩的最低分:
* select min(chinese) from exam;
* 统计英语成绩平均分:
* select avg(english) from exam;

【分组】
group by
创建一个订单详情的表:
* 统计订单中的每类商品所购买的个数：
* SELECT product,COUNT(*) FROM orderitem GROUP BY product;
* 统计订单中的每类商品所花的金额:
* SELECT product,SUM(price) FROM orderitem GROUP BY product;
* 统计订单中的每类商品所花总金额大于2000信息.
* SELECT product,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price) > 2000;

• 统计订单中名称有电子的商品并且所花金额大于1500同时按照价格降序排序：

  • SELECT product,SUM(price) FROM orderitem WHERE product LIKE ‘电%’ GROUP BY product HAVING SUM(price) > 1500 ORDER BY SUM(price) DESC;

  给orders表中的cid添加外键约束.

• alter table orders add foreign key (cid) references customer(cid);

【多表的查询的SQL】
多表的查询的方式：
* 交叉连接：
* select * from A,B; — 获得的是两个表的笛卡尔积.
* 内连接: inner join – inner 可以省略
* 显式内连接：select * from A inner join B on 条件;
* SELECT * FROM customer c INNER JOIN orders o ON c.cid = o.cid;
* 隐式内连接：select * from A,B where 条件;
* SELECT * FROM customer c ,orders o WHERE c.cid = o.cid;
* 外连接：outer join – outer 可以省略
* 左外连接：left outer join – select * from A left outer join B on 条件;
* SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.cid = o.cid;
* 右外连接：right outer join – select * from A right outer join B on 条件;
* SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.cid = o.cid;

【多表查询的子查询】
一个SQL语句查询的过程中需要依赖另一个查询语句.
SELECT * FROM customer c,orders o WHERE c.cid = o.cid AND c.cid IN (SELECT cid FROM orders WHERE addr LIKE ‘海淀%’);

顺序:
* Select … From … Where … Group by … Having … Order by …;

MySql优化

表的优化和列类型选择

1:定长和变长分离
int占4个字节 char(4)占四个字符 time
核心表而且常用字段 建成定长 放在一张表

2:常用字段和不常用要分离
例如用户表 个人常用信息在一张表 个人评价等不常用信息单独放一张表

3:在一对多,需要关联统计的字段上 , 添加冗余字段
比如说论坛的今日发帖数量 , 栏目表和帖子表 , 在栏目表多加一个字段 , 添加一篇帖子
统计发帖数量

在计算机的优化中 , 要么是用时间换空间 , 要么是用空间换时间

列类型的选择

1:字段类型优先级:整形 > date,time > enum,char > varchar >bolb,text

列的特点分析:
整形 : 定长,没有国家之分,没有字符集的差异
tinyint 和 char char要考虑字符集和校对规则 整形则不用
time也是定长 , 但是要考虑时区
enum(‘男’,’女’) 就只能接受男和女 内部转成数字来存 多了转换过程
char 定长
varchar 变长

字段长度够用就行 , 不要慷慨
原因 : 大字段浪费内存 , 影响速度

3:尽量不要用null
原因 :ｎｕｌｌ不利于索引，要用特殊的字节来标注
在磁盘上占据的空间其实更大　mysql5.5做了优化,大小区分不再明显
另外 null值也不便于查询
where 列名=null
where 列名!=null 都查不到值
where 列名 is null 或者 is not null 才可以查询

enum 类型多一个转换过程

索引优化策略
索引类型 :
1 : btree索引
2 : hash索引

索引是一种可以高效查询的数据结构
索引指向磁盘信息
news表里面有三个文件 myisam引擎
new.frm 结构
news.myd 数据
news,myi 树

hash索引
只能在memory表里使用(内存引擎) 内存里面 关机丢失
不管多少数据 理论上一下就可以找到

哈希: 坏处,地址冲突 拉链算法解决
无法对范围查询进行优化
无法利用前缀索引
排序也无法优化

btree索引的常见误区

1:在where条件常用的列上都加上索引
因为索引是独立的 , 同时只能用上1个

实际使用中使用的联合索引

2 在多列建立索引后 , 查询哪个索引都将发生作用
btree索引发挥作用的左前缀原则
索引也能提高排序速度和分组统计速度