Mysql数据类型简介

整数类型（分有符号和无符号类型：signed、unsigned）

TINYINT、SMALLINT、MEDIUMINT、INT、BIGIN

浮点数类型、定点数类型（分有符号和无符号类型：signed、unsigned）

FLOAT、DOUBBLE、DECIMAL(M, D)：M表示数据的总长度（但不包括小数点），D表示小数位。例如123.45可表示为decimal(5,2)，存入数据的时候，按四舍五入计算。
FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

日期与时间类型

YEAR、DATE、TIME、DATETIME、TIMESTAMP（另一种类型的时间，时间范围不一样）
DATA、TIME与时区无关，能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。
TIMESTAMP和时区有关，和 UNIX 时间戳相同，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。
应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

字符串类型

CHAR（固定长度字符串）、VARCHAR（可变长度字符串）、TEXT、ENUM（枚举类型）、SET（集合类型）
VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

二进制类型

BINARY(M)：字节数为M定长二进制字符串、VARBINARY(M)：变长、BIT(M)、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

数据库的基本操作

数据库（Database）：按照数据结构来组织、存储和管理数据的仓库。
实际开发用SQLyog图形界面进行数据库操作。
快捷键：
Mysql：键盘上下键可以切换已经使用过的命令。
SQLyog：选中root@localhost，按F5刷新。

显示所有数据库

SHOW DATABASES; //注意有分号结尾

创建数据库

1）CREATE DATABASE 数据库名; //一般数据库名的规范为 db_***，文件的扩展名为.sql
SQLyog：选中root@localhost，按F5刷新，发现新数据库创建成功。
2）创建数据库：选中root@localhost，右键 -> 创建数据库。

删除数据库

1）DROP DATABASE 数据库名
2）除数据库：选中需要删除的数据库，右键 -> 更多操作 -> 删除数据库。

使用数据库

在当前数据库下创建表等一系列操作需要先使用语句：
USE 数据库名

表的基本操作

先切换到需要进行操作的数据库下面：
USE 数据库名
属性（字段）的具体内容叫记录。其它的操作字符叫关键字。

创建表

表是数据库存储数据的基本单位，一个表包含若干字段或记录。
语法：

CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型 [完整性约束条件]);

约束条件说明
PRIMARY KEY 标识该属性为该表的主键，可以唯一标识对应的记录
FOREIGN KEY 标识该属性为该表的外键，与表的主键关联
NOT NULL 标识该属性不能为空
UNIQUE 标识该属性的值是唯一的
AUTO_INCREMENT 标识该属性的值自动加一（通常与主键一起使用）
DEFAULT 为该属性设置默认值
例：主表t_booktype（1对多）副表t_book

CREATE TABLE t_booktype(
	id INT PRIMARY KEY AUTO_INCREMENT,
	bookTypeName VARCHAR(20),
	bookTypeDesc VARCHAR(200)
);
INSERT INTO t_bookType (`id`, `bookTypeName`) VALUES ('1', '运动类');
INSERT INTO t_bookType (`id`, `bookTypeName`) VALUES ('2', '工科类');
INSERT INTO t_bookType (`id`, `bookTypeName`) VALUES ('3', '文科类');

CREATE TABLE t_book(
id INT PRIMARY KEY AUTO_INCREMENT,
bookName VARCHAR(20),
author VARCHAR(10),
price DECIMAL(6,2), //定位
bookTypeId INT, //这个属性与t_booktype的id关联。这是为了约束限制，确保数据一致性
CONSTRAINT `fk` FOREIGN KEY (`bookTypeId`) REFERENCES t_booktype (`id`)
); //注意是上句号，用于非值的标注，可以省略；但值的单引号不可省略
INSERT INTO t_book (`id`, `bookName`, `author`, `price`, `bookTypeId`) VALUES ('1', '数学', '张三', '50', '2');
INSERT INTO t_book (`id`, `bookName`, `author`, `price`, `bookTypeId`) VALUES ('2', '语文', '李四', '45', '3');

1）查看当前表中的数据
右键当前表 -> 更改表（改变表）
2）创建外键关联
外键（foreign key）：是为了约束属性，确保数据的一致性。
编辑 -> 新建架构设计器，将需要进行外键关联的表拖进来。将需要约束的source table中的属性拖到target table相应的属性中，建立外键关联。

查看表结构

1）查看表基本结构（表的基本结构，用SQLyog直接右键 -> 更改表查看结构）
DESCRIBE(或简写为DESC) 表名;
2）查看表详细结构（创建表的详细命令语句）
SHOW CREATE TABLE 表名;

修改表（ALTER只是对属性的修改，对具体的行上面的数据没有帮助）

1）修改表名：ALTER 旧表名 RENAME 新表名;
2）修改属性（字段）：ALTER TABLE 表名 CHANGE 旧属性新属性新数据类型;
3）增加字段：ALTER TABLE 表名 ADD 属性1 数据类型 [完整性约束] [FIRST或AFTER 属性2]; //在第一个位置添加或在某个属性后添加。而用工具添加都是在当前行之前添加
4）删除字段：ALTER TABLE 表名 DROP 属性名;

删除表

DROP TABLE 表名;
对于有外键关联的主表和副表，不能直接删除主表，需要先删除副表才能删除主表。

查询数据

query /‘kwɪərɪ/查询
先创建一个表，并插入一些字段和属性。

CREATE TABLE t_student(
	id INT, //注意此处没有默认加1，因此第二项的id应当手动设置为2。注意不可单独设置AUTO_INCREMENT关键字，因为它必须与PRIMARY KEY同时使用。
	stuName VARCHAR(60),
	age INT,
	sex VARCHAR(30),
	gradeName VARCHAR(60)
);
INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES (‘1’, ‘张三’, ‘23’, ‘男’, ‘一年级’); //注意关键字使用上句号，而字符串是用单引号。由于Word的格式问题，可能造成在Mysql命令窗口中单引号格式错误，最好重新在命令窗口修改。
INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES (‘2’, ‘李四’, ‘23’, ‘男’, ‘二年级’);

出错：1366 incorrect String
解决：此处是编码错误，这是由于我安装Mysql时将其默认编码设置为了uft-8编码，但不知为什么Mysql依然不支持中文字符集？SHOW FULL COLUMNS FROM t_student; 查询当前表所有字符的属性。
方法1）我们将所有的中文换成英文，就不会出错了。
方法2）找到Mysql的安装目录下的my.ini文件，修改default-character-set=GBK，但是需要注意，这里设成GBK之后在页面里面也需要设成GBK，否则会出现编码不一致，会出现乱码。
查询只需要在SQLyog的查询（query）框内，输入以下要查询的代码，里面的查询语句可以不用删除，需要让哪行执行，只需要将光标放在该命令行或选中某些命令行，点查询按钮（或F6快捷键）。

插入数据

建立了表结构后，需要通过Mysql输入插入数据的命令，或在SQLyog查询界面直接输入命令。注意：属性是用上句号括起来的；而属性的值是用单引号括起来的（因为它是字符串）。

INSERT INTO 表名 (`属性1`,`属性2`,`属性3`) VALUES (‘值1’, ‘值2’, ‘值3’);
如
INSERT INTO t_student (`id`,`stuName`,`age`) VALUES (‘1’, ‘张三’, ‘20’);
INSERT INTO t_student (`id`,`stuName`,`age`) VALUES (‘2’, ‘李四’, ‘21’);

单表查询

1）查询所有属性（字段）
SELECT * FROM 表名;
2）查询指定字段
SELECT 字段1, 字段2, … FROM 表名;
3）where条件查询
SELECT字段1, 字段2, … FROM 表名 WHERE 条件表达式; //表达式比如只需要查询id=2的字段属性，或age>8的字段属性。
SELECT 字段1, 字段2, … FROM 表名 WHERE 条件表达式1 AND条件表达式2;
SELECT 字段1, 字段2, … FROM 表名 WHERE 条件表达式1 OR条件表达式2;
4）带IN关键字查询
SELECT 字段1, 字段2, … FROM 表名 WHERE 字段 [NOT] IN (元素1, 元素2, 元素3);
5）带between and的范围查询
SELECT 字段1, 字段2, … FROM 表名 WHERE 字段 [NOT] BETWEEN 取值1 AND 取值2;
6）模糊查询
SELECT 字段1, 字段2, … FROM 表名 WHERE 字段 LIKE ‘元素字符串%’; //%可加在字符串的任意位置，它表示查询任意字符
SELECT 字段1, 字段2, … FROM 表名 WHERE 字段 LIKE ‘元素字符串_’; //_表示查询单个字符
7）空值查询
SELECT 字段1, 字段2, … FROM 表名 WHERE 字段 IS [NOT] NULL;
8）distinct去重复查询 //distinct明显的，不同的
SELECT DISTINCT 字段 FROM 表名;
9）对查询结果排序
SELECT 字段1, 字段2 FROM 表名 ORDER BY 属性名 [ASC|DESC]; //默认是升序ascent|descent
10） group by分组查询
SELECT * FROM 表名GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP];
（1）单独使用没意义；
（2）与GROUP_CONCAT()一起使用； //concat合并
注意格式是[属性],函数格式，属性与函数之间是逗号连接。
//查每个年级所有学生姓名
//关键：年级,GROUP_CONCAT(学生姓名)
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY stuName;
（3）与聚合函数COUNT()一起使用；
//查询每个年级的学生总数
//关键：年级,COUNT(学生姓名)
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
（4）与HAVING一起使用（对查询结果的筛选）；
（5）与WITH ROLLUP一起使用（对上面查询的结果最后加入一个总和行。如果是int类型，直接求和；如果是文本内容，它将筛选后的文本内容全部列出）。 //rollup归纳
11） LIMIT分页查询
SELECT 字段1, 字段2 FROM 表名 LIMIT 初始位置, 记录数;
//10条数据分两页进行查询，每页5条数据
SELECT * FROM t_student LIMIT 0, 5; //初始位置从0开始
SELECT * FROM t_student LIMIT 5, 5; //初始位置从5开始

使用聚合函数查询

聚合函数经常与GROUP BY一起以后使用（一起使用时，一般是根据属性分组,聚合函数的格式）。
1）COUNT()：用来统计记录的条数。
（1）单独使用：
//统计表中的所有数据
SELECT COUNT(*) AS total FROM t_student; //其中AS total是为count取了别名total
（2）与GROUP BY使用
//统计每个学生考了几门课程，统计相同名字出现的次数
SELECT stuName,COUNT(*) FROM t_student GROUP BY stuName;
2）SUN()：用来求和。
//求出张三的总分
SELECT stuName,SUM(score) FROM t_student WHERE stuName = “张三”;
//求每个学生的总分
SELECT stuName,SUM(score) FROM t_student GROUP BY stuName;
3）AVG()：求平均值的函数。
4）MAX()
//求出张三的最高分
SELECT stuName,MAX(score) FROM t_student WHERE stuName = “张三”;
//求每个学生某门课程的最高分
SELECT stuName,MAX (score) FROM t_student GROUP BY stuName;
5）MIN();

连接查询（笛卡尔积）

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。
SELECT *FROME t_book, t_booktype; //具体属性值进行的笛卡尔乘积，注意两个表之间用逗号给开。
1）内连接查询
内连接查询是一种常见的连接查询。
例：

USE DATABASE db_book;
CREATE TABLE t_booktype(
	id INT PRIMARY KEY AUTO_INCREMENT,
	bookTypeName VARCHAR(20),
	bookTypeDesc VARCHAR(200)
);
INSERT INTO t_bookType (id, bookTypeName) VALUES ('1', '运动类');
INSERT INTO t_bookType (id, bookTypeName) VALUES ('2', '工科类');
INSERT INTO t_bookType (id, bookTypeName) VALUES ('3', '文科类');

CREATE TABLE t_book(
id INT PRIMARY KEY AUTO_INCREMENT,
bookName VARCHAR(20),
author VARCHAR(10),
price DECIMAL(6,2), 
bookTypeId INT, 
CONSTRAINT fk FOREIGN KEY (bookTypeId) REFERENCES t_booktype (id)); 
INSERT INTO t_book (id, bookName, author, price, bookTypeId) VALUES ('1', '数学', '张三', '50', '2');
INSERT INTO t_book (id, bookName, author, price, bookTypeId) VALUES ('2', '语文', '李四', '45', '3');

CREATE TABLE t_pricelevel(
id INT,
priceLevel INT,
price FLOAT,
description VARCHAR(300));

//t_booktype和有外键的t_book，将t_book表的bookTypeId查出来
SELECT * TABLE t_book, t_booktype WHERE t_book.bookTypeId = t_booktype.id;
//或只查询需要的字段
SELECT tb.bookName, tb.author, tby.bookTypeName TABLE t_book tb, t_booktype tby WHERE tb.bookTypeId = tby.id; //提倡用别名代替表名，这样能更清楚地显示。t_book别名tb，t_booktype别名tby
2）外连接查询
可以查出某张表的全部信息。内连接只将符合要求的查询给列出，若想要即使在不符合要求时，也需要将所有的信息罗列出，就需要用外连接。
SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性1 = 表名2.属性2;
（1）左连接查询
可查询出”表名1”的所有记录，而”表名2”中只能查出匹配记录。
SELECT * FROM t_book tb LEFT JOIN t_booktype tby ON tb.bookTypeId = tby.id; //书名一定要列出，没找到bookType没关系，用NULL代替
（2）右连接查询
可查询出”表名2”的所有记录，而”表名1”中只能查出匹配记录。
3）多条件连接查询
SELECT tb.bookName, tb.author, tby.bookTypeName TABLE t_book tb, t_booktype tby WHERE tb.bookTypeId = tby.id AND tb.price > 70; //就是在原来的where语句之后跟多多个条件查询语句

子查询

1）带[NOT] IN关键字
一个查询语句的条件可能落在另一个SELECT语句的查询结果当中。
SELECT * FROM t_book bookTypeId IN (SELECT id FROM t_booktype);
2）带比较运算符
SELECT * FROM t_book WHERE price >= (SELECT * FROM t_priceLevel WHERE priceLevel = 1);
3）带exists关键字
如果子查询查询到记录，则进行外层查询，否则不执行外层查询。
//如果bookType存在则返回查询
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
4）带any关键字
any表示满足其中任一条件。
SELECT * FROM t_book WHERE price >= ANY (SELECT price FROM t_pricelevel);
5）带all关键字
all表示满足所有情况。

合并查询结果

union关键字，将所有查询结果合并到一起，然后去掉相同的记录。
union all关键字，则不会去掉系统记录。
SELECT id FROM t_book UNION SELECT id FROM t_bookType; //将两个表的id合并
SELECT id FROM t_book UNION ALL SELECT id FROM t_bookType; //将两个表的id合并

为表和字段取别名

格式：表名表别名
格式：属性名 [AS] 别名 //加上as能更清楚地区分表与属性。

插入、更新和删除数据

插入数据

1）INSERT INTO 表名 VALUES (值1, ..,值n);
2）INSERT INTO 表名 (属性1, 属性2,…) VALUES (取值1, 取值2);
3）同时插入多条数据
INSERT INTO 表名 [(属性列表)] VALUES (取值列表1), (取值列表2);
例：
INSERT INTO t_book (id, bookName, price, bookTypeId) VALUES ()
(‘1’, ‘数学’, ‘50’, ‘1’), (‘2’, ‘语文’, ‘45’, ‘3’);

更新（修改）数据

UPDATE 表名
SET 属性1 = 取值1, 属性2 = 取值2
WHERE 条件表达式;
UPDATE t_book SET bookName = “语文”, price = 60 WHERE id = 2;

删除数据

DELETE FROM 表名 [WHERE 条件表达式];

索引

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
优缺点：提高查询速度；创建和维护索引的时间增加了。
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
导入数据库：右击root@localhost –> 从SQL转储文件导入数据库。
SELECT COUNT(*) FROM t_book; //查询一共有多少条数据

按需添加索引

//若需要按bookName查询
SELECT * FROM t_book WHERE bookName = ‘语文’; //当有多条数据时，查询时间可能几百ms
我们可以对bookName属性添加索引：
右击需要添加索引的表 -> 管理索引(F7) , 对bookName添加索引。其中有全文索引（fulltext）、唯一性索引（unique）、主键（primary key，所有索引的主键只有一个）。

索引的分类

1）普通索引：可以创建在任何类型数据中；
2）唯一性索引（unique）：限制该索引的值必须是唯一的；（主键默认就是唯一性索引）
3）全文索引（fulltext）：只能创建在char，varchar，text类型的字段上。主要作用是提高查询较大字符串的速度，Mysql默认引擎不支持，只有MyISAM引擎支持；
1）单列索引：在单个字段上创建索引；
2）多列索引：在表的多个字段上创建一个索引；
3）空间索引（spatial）：只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。

创建索引

1）在创建表的时候创建索引
（1）单列索引：在单个字段上创建索引；

CREATE TABLE t_user1(
	id INT,
	userName VARCHAR(20),
	PASSWORD VARCHAR(20),
	INDEX index_userName(userName) // index_userName是自己设定的别名，可以不加
);

（2）多列索引：在表的多个字段上创建一个索引；

CREATE TABLE t_user1(
	id INT,
	userName VARCHAR(20),
	PASSWORD VARCHAR(20),
	INDEX index_userName_Password(username, PASSWORD) // index_userName是自己设定的别名，可以不加
);

2）在已经存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (属性名 [(长度)] [ASC|DESC]);
3）用alter语句创建索引
ALTER TABLE 表名 ADD INDEX 索引名
(属性名 [(长度)] [ASC|DESC]);

删除索引

1）DROP INDEX 索引名 ON 表名;
2）ALTER TABLE 表名 DROP INDEX 索引名;