【Mysql 基础部分：增删改查】简单理解

一、常见的数据库管理系统

oracle数据库，Mysql数据库（免费的），DB2,sybase，Access数据库等

• oracle数据库是美国甲骨文公司所开发的，但一般用于银行，金融等部门，软件自身是免费的，但数据库用于商业用途，就需要购买相应产品的License,这是一笔不小的数目，就连腾讯，阿里巴巴这样的大公司都开发自己的数据库！
• 2009年 oracle公司，以74亿美元的价格收购了sun公司，可见oracle甲骨文公司的雄厚资本；之前IBM公司也想吞并sun公司，但失败了，我们学java所用的eclipse可视化工具就是IBM公司开发的，eclipse单词的意思就是：日食，但吞并sun公司失败！
• mysql数据库是我们最常用的，但它也难逃被oracle收购的命运

二、sql、DB、DBMS分别是什么

• sql是结构化查询语言，标注的sql语言适用于所有数据库产品，也就是说sql适用于mysql和oracle，但可以在mysql上运行的代码不一定可以在oracle上运行，反之也是一样的
• DB：Database的简称，数据库在硬盘上以文件方式存在，可以看作一个仓库，不过是存储数据的仓库
• DBMS：数据库管理系统，DBMS通过sql语句来操作DB中的数据，从而完成需求

三、SQL语句包括增删查

• DQL（查询语句）：凡是select语句都是DQL
• DML：包括inster，delete，updata等对表中数据进行增删改
• DDL；create,drop,alter等对表结构进行增删改
• TCL：commit提交事务，rollback回滚事务等

四、操作数据库

• 进入mysql的两种方式

1、格式：mysql -u帐号　-p密码
2、mysql -u帐号　-p；按enter会提示你输入密码(Enter pssword:)，此时你写的密码就会显示为***这样。

• 创建数据库

create database 数据库名；

• 查看存在的数据库

show databases；

• 使用数据库

use 数据库名

• 删除数据库

drop database 数据库名；

五、创建、修改、删除表

• 创建表

create table 表名（ 属性名 数据类型 [完整性约束条件]);

• 查看表结构

• describle 表名；（可简写：desc 表名）；
• show create tables 表名；

• 修改表

• 改表名：alter table 表名 rename 新表名；
• 修改表字段数据类型：alter table 表名 modify 属性名 数据类型；
• 修改字段名：alter table 表名 change 旧属性名 新属性名 新数据类型
• 增加总段：alter table 表名 add 属性名1 数据类型 [约束条件] [first| after 属性名2]；
• 删除字段：alter table 表名 drop 属性名；
• 删除表的外键：alter table 表名 drop foreign key 外键别名；

五、数据类型

（一）、整数类型

我们知道：1个字节= 8位二进制数。 每个类型的取值范围也就能够知道，比如 TINYINT占用1个字节，也就是8位，2的8次方减1等于255，也就是说如果代表没符号的整数，该取值范围为0到255，如果是有符号的，最高位为符号号位，也就是2的7次方减1，也就是127，取值范围为-128~127， 为什么需要减1，这个问题就需要考虑临界值的问题了

（二）、浮点数类型和定点数类型

1、什么时候使用FLOAT、DOUBLE、DECIMAL
答：对精度要求比较高的时候，比如货币、科学数据等，使用DECIMAL的类型比较好。其他的时候，看你要存放的数据的大小而定了，一般使用DOUBLE。并且在使用浮点数时需要注意，尽量避免做浮点数的比较，比如加、减，谁大谁小，这样的操作，会引起精度缺失。相信在一些程序语言中，遇到过float精度丢失的问题。
2、 解释M,D的意思：
M:数值的总位数。就是看数字总个数，比如，3.1415，M就是5
D:小数点后面保留的位数。 比如上面的3.1415 ，D就是4。

（三）、日期与时间类型

（四）、字符串类型

1、 CHAR和VARCHAR 的区别
CHAR(M)： 为固定长度字符串，固定长度的意思就是M的值为多少，那么该M的值就是其实际存储空间的值，就算插入的数据少于M位，其存储空间还是那么大，多余的用空格补齐。在输出时，空格将被删除不输出。M最大为255，比如char(4)，如果插入abc，则存储的值为’abc ‘后面多了一个空格，输出还是‘abc’， 存储空间还是占4个字节。M最大为255
VARCHAR(M): 长度可变的字符串，跟CHAR相反，会根据实际的大小值来确定存储空间的大小，比如 VARCHAR(4),插入’ab’，则存储空间为3字节，看上面图就知道VARCHAR会多一个字节用来存储长度，M最大为65535.
注意：字符串跟数值类型不一样，M为多大，就最多能插入多少字符，超过了M，就会报错
2、 ENUM
枚举，格式：字段名 ENUM(‘值1’,‘值2’,‘值3’…,‘值n’);只能取列中的一个元素
3、 SET
格式：字段名 SET(‘值1’,‘值2’,‘值3’,‘值4’…,‘值n’)；可以取列中1到多个值

（五）、二进制类型

六、查找数据

（一）、单表查询（常用的）

• 我就不列举底下语句的用法，都相对简单，多练就熟悉了

1、[not] in (元素1，元素2，…元素n);
2、 between 取值1 and 取值2（说明取值1和取值2范围都是闭区间）
3、 [not] like ‘字符串’；（其中%代表任意长度；_代表单个字符）
4、 查询空值：is [not] null
5、 带and和or的多字段查询
6、 查询结果不重复：select distanct 属性名 from 表名
7、 对查询结果排序：order by 属性名 【desc| asc】

（1）、 分组查询

语法：group by 属性名 【having 表达式】
1、 group by单独使用只能查询出每个分组的一条记录，这个特性可以用来查询不重复数据
2、 group by 一般和集合函数一起使用

（2）、集合函数（分组函数）

count（）、sum()、avg()、max()、min（）等
注意：
1、所有的分组都是针对某一组数据进行操作的，分组函数自动忽略空值：null
2、分组函数不能直接使用在where语句当中，因为group by 在where语句执行结束后才会执行，也就是说只有先分组，才可以进行分组查询，

（3）、having和where的区别

where作用于表/视图，是表/视图的查询条件，having作用于分组后的记录，用于选择满足条件的分组，

（4）、limit 限制查询结果

用法：limit 初始位置，记录数

（二）、多表查询（连接查询）

• 为了对比演示内外查询结果：创建员工表和部门表
  部门表：
  select * from department;
  ±-----±-------±---------+
  | did | dname | func |
  ±-----±-------±---------+
  | 1001 | 科研部 | 研发产品 |
  | 1002 | 生产部 | 生产产品 |
  | 1003 | 销售部 | 策划销售 |
  ±-----±-------±---------+
  员工表：
  select * from employee;
  ±----±-----±-----±-----+
  | num | did | name | sex |
  ±----±-----±-----±-----+
  | 1 | 1001 | 张三 | m |
  | 2 | 1001 | 李四 | w |
  | 3 | 1002 | 王五 | w |
  | 4 | 1004 | Aric | w |
  ±----±-----±-----±-----+

（一）、内连接查询

• 查询前提：两张表必须存在意义相同的字段，也可以是父表的主健和子表的外键
• 底下我们用：EX 表示意义相同的字段

用法：select 表一.属性名，表二.属性名 … from 表一，表二 where 表一.EX = 表二.EX

用上面的员工表和部门表运行测试：（意义相同字段did）

mysql> select a.name,b.dname from employee a,department b where a.did = b.did;
±-----±-------+
| name | dname |
±-----±-------+
| 张三 | 科研部 |
| 李四 | 科研部 |
| 王五 | 生产部 |
±-----±-------+
查询结果：我们查询出的是 特定字段值相同的元素，即就是did两个表都有的信息

（二）、外连接查询

语法：select 属性列表 from 表一 left|right join表二 on 表一.EX = 表二.EX

（1）、左连接查询

用上面的员工表和部门表运行测试：（意义相同字段did）
mysql> select a.name,b.dname from employee a left join department b on a.did = b.did;
±-----±-------+
| name | dname |
±-----±-------+
| 张三 | 科研部 |
| 李四 | 科研部 |
| 王五 | 生产部 |
| Aric | NULL |
±-----±-------+
结果：我们看到左连接查询，会把左边这张表信息全部查出来，但did = 1004这个员工，右边表部门未登记，所以显示为空

（2）、右连接查询

用上面的员工表和部门表运行测试：（意义相同字段did）
select a.name,b.dname from employee a right join department b on a.did = b.did;
±-----±-------+
| name | dname |
±-----±-------+
| 张三 | 科研部 |
| 李四 | 科研部 |
| 王五 | 生产部 |
| NULL | 销售部 |
±-----±-------+
结果：和左连接查询相反，我们看到右连接查询，会把右边这张表信息全部查出来，但员工编号did = 1003，右边员工表查不到此人，所以显示为空

（三）、子查询

带in关键字的子查询
语法示例：select * from 表一 where did in （select did from 表二）；

带exists关键字的子查询：内层循环返回一个真假值（true/false）
语法示例：select * from 表一 where exists （select did from 表二）；

带any的子查询：满足其中一个条件就好
语法同上

带all的子查询：满足所有条件
语法同上

七、插入和删除数据

• 为表的所有字段插入数据

insert into 表名 values（值1，值2，。。。值n）；

• 插入数据时，若数据存在，也不要替换

insert [or] ingore into 表名 values（值1，值2，。。。值n)；

• 为表的指定字段插入数据

insert into 表名（属性1，属性2.。。。属性m） values（值1，值2，。。。值m）；
注意：如果某个字段没有设置默认值，而是非空约束，系统会出错

• 更新数据

update 表名 set 属性名1 = 取值1，属性名2 = 取值2，。。。where 条件表达式

*删除数据

delete from 表名【where 条件表达式】

八、存储引擎和字符编码

（一）、存储引擎的种类

1、MyISAM

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据

（3）MYI文件：存储索引

frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。

InnoDB
InnoDB是默认的数据库存储引擎，他的主要特点有：

（1）可以通过自动增长列，方法是auto_increment。

（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。

（3）使用的锁粒度为行级锁，可以支持更高的并发；

（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

（5）配合一些热备工具可以支持在线热备份；

（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
当然InnoDB的存储表和索引也有下面两种形式：

（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。

（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。

对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。

Memory
将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；

（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

（5）默认使用hash索引。

（6）如果一个内部表很大，会转化为磁盘表。

（二）、mysql编码问题

show variables like ‘character_set_database’; //查询mysql数据库默认编码
±-----------------------±-------+
| Variable_name | Value |
±-----------------------±-------+
| character_set_database | latin1 |
±-----------------------±-------+

（1） 给varchar/char 前面+n 只改变一个属性的编码形式。 create table test_char ( name
nvarchar(10) );

（2） 只改变一个属性的编码形式。
create table test_char ( name varchar(10) CHARACTER SET utf8 );
（3） alter table sex_tb1 modify Ssex enum(‘男’,‘女’) character set utf8;

（4） 改变整张表的编码形式： create table test_char3 ( name varchar(10)
)CHARSET=utf8;

改变整个数据库编码形式
改变mysql的配置文件  my.ini
这个配置文件是只在mysql 服务启动的时候会读取的，如果改变了mysql的配置文件一定要重启
服务之后改变好的配置才会起到作用。

有错误或者不足的地方望大家指出来，一起进步，谢谢！