这是一步步从头学习MySQL的笔记历程，本人在学习之前只接触过少量SQL Server。
第一天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（一）
第三天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（三）
接下来每天都将更新~相信你通过笔记，一定能从零学会MySQL~
废话不多说，开始今天的学习压榨~！
昨天学习了SQL的历史，和MySQL的基本定义，今天继续，开始学习相关的表的概念。在这之前，先补充一点昨天的东西。
数据库对象（逻辑视角）
表：二维关系 表其实就是由行和列组成的二维关系，（一个基表）
索引，index：加速查询的数据结构，他也需要用到存储空间。快速找到某些具有特殊特点的指针。索引有索引类型的概念，不同的索引能适用于不同的查询类型，常用的比如Btree索引……
视图：一个存储下来的SELECT语句，检索成表的形式,（一个虚表）
触发器：trigger，让用户对数据库中的表发生数据修改的时候，同时触发的额外的数据库的操作。（比如用户修改记录的时候，系统会自动触发将用户的动作记录到日志信息）。具体实现什么操作，要管理员去定义。它通常是一个代码段，属于SQL变成的范畴。触发器在记录日志的时候非常有用，而且是将日志记录带某个表中。
存储过程：Storage procedure：一个正常的执行的数据段，需要用CALL来定义，来执行，过程是没有什么特定的返回值的。尤其是返回特定数据集。基于存储过程开发的程序可能适用性比较差。
存储函数：Storage function：在select语句中进行使用，可以返回表。他们可以实现代码重用，极大增加效率、以及增强数据库的安全程度。他们两个一般都是放在服务器端的，远程传输的只是CALL或者其他的命令~。
事件调度器：event sheduler：主要实现定期任务定期计划的，相当于Linux的Crontab。它也是一段代码，属于开发范畴。包括存储过程，存储函数，事件调度器他们都严重依赖与RDB编程接口。
用户：属于数据库对象。验证用户账号，权限分派。一个用户登录系统以后，主要是看你具有哪些访问权限的。
然后我们开始今天学习的正题：
如何去创建表：
表其实是由行和列组成的，表其实就是数据文件，一个表可以对应在多个文件上。对于MyInnsa这种类型来说，一个表通常对应3个文件。表空间通常是比表大，比数据库小的物理单位。
创建格式：
CREATE [TEMPORART] TABLE [IF NOT EXISTS] tbl_name (字段create_definition) [字段属性] [表的选项] [分区选项]
创建表方式1：
最简单的：CREATE TABLE table_name (…)
每一个表必须属于一个数据库，所以创建表的时候必须是在一个数据库当中创建的。如果你没有设定默认数据库的话：必须使用db_name.tab_name来指定数据库。
创建一个表的多行标准格式：
CREATE TABLE employee (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
UNIQUE(id),
INDEX (last_name,first_name)
);
创建表的时候可以指定使用什么存储引擎：
ENGINE=engine_name
CREATE TABEL users (id INT) ENGINE=InnoDB;
ALTER TABLE users ENGINE=MYISAM;
查看当前数据库使用引擎
SHOW TABLE STATUS
查看当前表引擎
SHOW TABLE STATUS LIKE ‘zones’\G
比如： CREATE TABLE tb1 (
-> id tinyint not null auto_increment primary key,
-> name varchar(30));
查看引擎，SHOW TABLE STATUS LIKE ‘tb1’\G
发现默认是InnoDB
比如： CREATE TABLE tb1 (
-> id tinyint not null auto_increment primary key,
-> name varchar(30)) ENGINE=MyISAM;
则创建了一个以MyISAM为引擎的表。
表选项：table_option:
常用的选项：
COLLATE = charset_name：指定专门的排序规则
AUTO_INCREMENT = value：指定自动增长的起始值
MAX_ROWS = value：最大行数
MIN_ROWS = value：最小行数
创建表方法2：从另一个表中选择数据并创建新表
CREATE TABLE [IF NOT EXISTS] tbl_name
-> [(create_definition,…)]
-> [table_options]
-> [partition_options]
-> select_statement
select_statement：我们完全可以从一个表中选择一些数据创建出另一个表。
比如：我想从role表中选择出里面包含user这个关键字的行并创建成新行：
CREATE TBALE role2 SELECT * FROM role WHERE name LIKE ‘%user’；
创建表方法3：以某个表为标准创建新表，只复制结构，不复制数据
CREATE TABLE [IF NOT EXISTS] tbl_name
[LIKE old_tbbl_name]
CREATE TABLE role3 LIKE role;
创建表方法4：创建表的同时创建索引：
索引的问题非常关键，所以一定要会在表创建的时候添加索引。
CONSTRAINT UNIQUE唯一键索引
SPATIAL空间索引
FULLTEXT全文索引
CHECK检查性约束
FOREIGN KEY外键索引
索引类型：
BTREE：平衡树索引。
HASH：索引
使用 USE BTREE|HASH 来指定索引类型
格式：
比如：创建一个users 有id,name,age,gender,score，并且经常性的会以成绩为区间进行检查，我们期望在score上进行索引
CREATE TABLE users (
-> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(30) NOT NULL,
-> Age TINYINT UNSIGNED NOT NULL,
-> Gender ENUM(‘F’,’M’) NOT NULL DEFAULT ‘M’,
-> Score FLOAT,
-> INDEX index_score(score)); ##指定索引，默认索引类型（BTREE类型）
-> UNIQUE(Name)); ##要求Name必须唯一
查看当前表的索引：
SHOW INDEXES FROM tbl_name
直接在创建列的时候就加入索引：
CREATE TABLE users (
-> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(30) UNIQUE NOT NULL, #直接指定这个字段唯一。
-> Age TINYINT UNSIGNED NOT NULL,
-> Gender ENUM(‘F’,’M’) NOT NULL DEFAULT ‘M’,
-> Score FLOAT,
-> INDEX index_score(score)); ##指定索引，默认索引类型（BTREE类型）
如何给自己的数据库设定默认引擎
方法1：
启动Mysql的时候，指定引擎
--default-storage-engine = engine_name
或者写入配置文件。my.cnf
方法2：
启动Mysql之后，不想重启系统的情况下，设定全局变量中storage_engine
SET GLOBAL storage_engine = engine_name
也可以设置会话级别的变量：
SET SESSION storage_engine = engin_name;
SET storage_engine = engine_name;
如何去表中查询数据：SELECT命令
最简单的使用方法：
SELECT column1, col2 FROM table_name
SELECT 字段 FROM 表：显示表中的选定字段
其中字段名可以使用“*”号来做通配，表示查询表中的所有字段，并显示字段中的值。只显示特定字段的方式叫做投影。
注意：字段的次序特别关键，你给定的次序就是到时候显示的次序，字段名并不区分大小写。
SELECT name,rid FROM role
和
SELECT rid,name FORM role
的结果是不一样的。
在查询的时候显示某些符合特定条件的行。
WHERE 字句
WHERE可以设定某个字段做逻辑比较(LIKE,BETWEEN AND)
WHERE column ….
指定某个字段符合什么样的条件：
>
< ,
= ,
LIKE ,
BETWEEN …AND… ,
IN,
IS NULL,
IS NOT NULL,
REGEXP(正则表达式)或者写成 RLIKE
1.比如查看rid大于等于2的role表中的行
SELECT * FORM role WHERE rid >= 2;
2.所有weight不等于0的行
SELECT * FROM role WHERE weight <> 0;
SELECT * FROM role WHERE weight != 0;
3.使用LIKE的时候，的通配符
%：通配所有字符
_：通配任意单个字符
查询所有中间包含了user的
SELECT * FROM role WHERE name LIKE “%user%”;
4.同时使用多个条件取他们交集。
name像user 并且weight不等于0的
SELECT * FROM role WHERE name LIKE ‘%user%’ and weight <> 0;
指定rid大于1且小于2的
SELECT * FROM role WHERE rid BETWEEN 1 AND 2;
5.IN，表示在某个特定集合之中出现过：
rid当中有1或者为2或者4或者5
SELECT * FROM role WHERE rid IN (1,2,4,5)
WHERE还可以使用算数运算比如：
+ , - , * , /
6.查看role表中rid值加1大于3的
SELECT * FROM role WHERE rid + 1 > 3
WHERE还可以使用逻辑操作符：
AND(&&), OR(||) , NOT(!)
所以当SELECT和WHERE字句同时使用的时候，就显示了一片内容。
当过滤结果之后，我们想对结果进行排序的方式：
ORDER BY clause
ASC(升序排序，默认)
DESC(降序排序)
1.以rid为基准按升序排序
SET * FROM role ORDER BY rid
2.按降序排序
SET * FROM role ORDER BY rid DESC;
检索的结果非常多，只想看特定的行：
LIMIT num
1.只看前2两行
SELECT * FROM role LIMIT 2;
2.跳过前2行之后，显示跳过之后的1行
SELECT * FROM role LIMIT 2,1;
3.只显示一个zone字段的前100行
SELECT zone FROM zones LIMIT 100;
distinct：消除重复的行
先消除所有的重复的行，再显示前100
SELECT DISTINCT zone FROM dnsrecords LIMIT 100;
先显示前100，再消去重复的行。【11：04】
CREATE TABLE test SELECT DISTINCT zone FROM dnsrecords
SELECT DISTINCT * FROM test
GROUP BY：实现以某个字段为标准，以相同的字段为标准，对其进行分组
分组的时候可以使用聚合函数
CONUT（*）：表示做计数的,对应有多少行并计算行数
按照域名进行分组,并使用COUNT(*)函数来进行计数
SELECT zone, COUNT(*) FROM mytb GROUP BY zone
对于GROUP BY的结果，是不能使用WHERE字句的，所以我们使用另一个字句：HAVING字句
AS:修改显示的结果的值的名字
修改并不对表做修改，仅仅只对显示做修改
表中的字段名和显示的不一样
SELECT zone AS domain FROM mytb
这样我们就可以把刚才GROUP BY的显示成numrecords
SELECT zone, COUNT(*) AS numrecords FROM mytb GROUP BY zone
HAVING字句：对结果做过滤
HAVING xxx
对检索出来的zong的值将其分组，并统计数目并将统计数目的值的名字修改为numrecords并对这个值进行过滤查看所有大于等于10的结果：
SELECT zone, COUNT(*) AS numrecords FROM mytb GROUP BY zone HAVING numrecords >= 10;
在Mysql中定义变量：
SET @num=10
这样一来，我们的刚才的那条定义就可以改为变量：
SELECT zone, COUNT(*) AS numrecords FROM mytb GROUP BY zone HAVING numrecords >=@num;
mysql练习1：
1、创建表mytb,比照mytb.jpg图片中的字段的定义；
答案：
mysql> CREATE TABLE mytb (
-> ID BIGINT(20) NOT NULL DEFAULT '0',
-> UserID BIGINT(20) NOT NULL,
-> ZoneID BIGINT(20) NOT NULL,
-> Zone VARCHAR(255) NOT NULL,
-> Host VARCHAR(255) NOT NULL,
-> Type ENUM('A','SOA','NS','MX','CNAME','PTR','TXT','AAAA') NOT NULL,
-> Mx_priority INT(10),
-> Data VARCHAR(255) NOT NULL,
-> TTL INT(10) NOT NULL DEFAULT '600',
-> View VARCHAR(20) NOT NULL DEFAULT 'DEF',
-> Active ENUM('Y','N') NOT NULL DEFAULT 'Y',
-> DomainLevel INT(11) NOT NULL DEFAULT '1',
-> Standby VARCHAR(255) NOT NULL,
-> CheckHostID BIGINT(20) NOT NULL DEFAULT '0',
-> LsFensheng TINYINT(1) NOT NULL DEFAULT '0',
-> );
2、将table.sql中的数据导入至此表；使用如下命令(假设新建的表名为mytb，原数据文件为/tmp/table.sql)：
mysql> LOAD DATA INFILE '/tmp/table.sql' INTO TABLE mytb;
练习2：
1、显示mytb表中Host为www的所有行；
mysql> SELECT * FROM mytb WHERE Host LIKE "www";
2、显示mytb表中Zone、Host、Type和Data字段；
mysql> SELECT Zone,Host,Type,Data FROM mytb;
3、将第2题中的Data字段显示时的Data改为显示Value；
mysql> SELECT Zone,Host,Type,Data AS Value FROM mytb;
4、显示mytb表中Host为ftp的所有行的Zone、Host、Type和Data字段；
mysql> SELECT Zone,Host,Type,Data FROM mytb WHERE Host LIKE "ftp";
5、显示mytb表中Zone为以.org结尾或以.net结尾的所有行；
mysql> SELECT * FROM mytb WHERE Zone LIKE "%.org" || Zone LIKE "%.net";
6、显示mytb表中Host字段的值为mail,@,*或oa的所有行；
mysql> SELECT * FROM mytb WHERE Host IN ('mail','@','*','oa');
7、以Host字段为准对mytb表中的数据进行分组，并对分组后的结果进行行数统计；而后显示统计结果中行数大于10的组；
mysql> SELECT *,COUNT(*) AS Times FROM mytb GROUP BY Host HAVING Times > 10;
8、显示mytb表中Mx_priority不为空的行，并对其结果以zone字段为准进行降序排序；
mysql> SELECT * FROM mytb WHERE Mx_priority IS NOT NULL ORDER BY Zone DESC;
9、将第1题中的结果限定为只显示第50行后100行；
mysql> SELECT * FROM mytb WHERE Host LIKE "www" LIMIT 50,100;
10、显示mytb表中存在的所有域名；
mysql> SELECT DISTINCT Zone FROM mytb;
11、显示mytb表中存在的所有记录类型；
mysql> SELECT DISTINCT View FROM mytb;
OK,经过中午的吃饭休息~我们下午继续开始！
如何去修改表结构:ALTER TABLE
格式：ALTER TABLE tbl_name
1.添加一个新字段：
ADD col_name col_def
ADD [默认情况下如果不说明添加的是什么，则为字段] 字段名 字段定义
我们先创建一个武侠表，方便之后的操作
CREATE DATABASE wuxia;
CREATE TABLE knight (
-> UID TINYINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
-> Name VARCHAR(10) NOT NULL,
-> Alias VARCHAR(30),
-> Age TINYINT,
-> Gender ENUM(‘F’,’M’),
-> CouseID TINYINT
-> );
使用DESC knight来看这个表
添加一个字段：我们武侠的师傅，这个师傅的值定义为ID号，因为他的师傅肯定会在武侠这个表中出现
ALTER TABLE knight ADD Master TINYINT UNSIGNED;
则这个叫Master的字段就添加到了最后一个字段。
AFTER col_name:在现有的某个字段的后面添加
FIRST:添加为第一个字段
2.修改一个字段：
CHANGE old_col_name new_col_name column_definition：
要指定原有名字，新名字不带改字段而且改名字
也可以指定FIRST AFTER col_name
MODIFY col_name column_definition
只改变某一个字段的定义，或者位置
后面可以跟FIRST AFTER col_name
比如修改刚才创建表中的Gender让其具有非空并且默认为M的属性：
ALTER TABLE knight MODIFY Gender ENUM(‘F’,’M’) NOT NULL DEFAULT ‘M’;
比如修改Master的名字为Tutor，并将字段放在Age之后
ALTER TABLE knight CHANGE Master Tutor TINYINT UNSIGNED AFTER Age;
3.给表添加索引
格式：ADD (INDEX|KEY) [index_name] [index_type] (index_col_name……);
比如给我们刚才的knight表添加索引为Age
ALTER TABLE knight ADD INDEX (Age)
4.删除的方法
DROP col_name ：删除字段
DROP PRIMARY KEY：删除主键
DROP (INDEX|KEY) index_name：删除索引
5.给表重命名：
RENAME TO new_col_name
比如修改knight为knights
ALTER TABLE knight RENAME TO knights;
不使用ALTER命令对其进行重命名
RENAME TABLE
RENAME TABLE knights TO knight
尽量不要重名，重命名的机制是先将源表复制，然后删除原表，并将原表放入新名字表中。
如何删除表
DROP TABLE [IF EXISTS] table_name
如何向表中插入数据(DML)：INSERT INTO
1：单一的插入方式
格式：INSERT INTO table_name (col1,col2) VALUES (val1,val2)
INSERT INTO 表名 (表字段名1，字段名2) 值 (字段值1，字段值2)
比如：往表中插入用户名：杨过
INSERT INTO knight (Name) VALUE (‘Yang Guo’);
我们查看一下：SELECT * FROM knight;
如果我们插入一个东西之后，要求必须不为空而且没有默认的字段没有定义的话，它会报错：
比如：
INSERT INTO knight (Gender) VALUE (‘F’)
所以我们先给他删掉：
DELETE FROM knight WHERE Gender=’F’;
2.SET 直接设置
INSERT INTO table_name SET col1=VAR1 col2=VAR2
INSERT INTO knight SET Name=’Wei Xiaobao’
我们发现，当我们删除过一个之后，下次再加入的数据将不会从删除的那个ID的号开始，而是从那个ID之后的号开始。
这时候我们就需要 LAST_INSERT_ID();这个内置函数，用于记录上次我们插入的新行的ID是几。
SELECT LAST_INSERT_ID();来显示
所以我们设定新值从来几来默认。
ALTER TABLE knight AUTO_INCREMENT=2;
此时：
INSERT INTO knight SET Name=’Wei Xiaobao’;
3.一次性不用指定字段，按照字段的排序，直接插入值
比如：
INSERT INTO knight VALUE(3,’Hong Qigong’,’Jiuzhishengai’,67,NULL,’M’,NULL);
4.一次插入多行：
INSERT INTO knight (Name) VALUES (‘Qiao Feng’),(‘Duan Yu’),(‘Chen Jinnan’);
这样则实现了一次性插入Name为A,B,C的三行。
5.REPLACE命令插入数据。
在数据进行插入的时候，只要发现数据一样，可以将原有数据替换的。而使用的方法，和INSERT一样
如何修改数据：UPDATE
格式：UPDATE table_name SET col1=val1,…… [WHERE clause] LINIT n
一般来讲，UPDATE一定要加上WHERE字句，如果不加的话，则是设定表中每一行的那个字段全部修改。
UPDATE knight SET Age=21;
所以，我们修改的时候要这样修改：给UID为2的人将年龄更改为25
UPDATE knight SET Age=25 WHERE UID=2;
LIMIT n：只修改前几行
UPDATE knight SET Age=19 LIMIT 2;
只修改前2行
如何删除数据：DELETE
格式：DELETE FROM table [WHERE clause]；
删除数据就直接删除了，所以需要指定WHERE字句。不指定条件的话，那整个数据就没有了
比如：删除knight表中UID=5的行
DELETE FROM knight WHERE UID=5
直接清空所有数据并将ID号设为最初的计数：
TRUNCATE TABLE table_name
如何多表查询
多表查询的原理：join,连接，基于某种方式，先将多张表组合连接起来，然后再让连接后的表，去响应我们的查询条件
我们先定义两个表，他们的列名及属性分别是：
表中的内容则为：
1.交叉连接：相当于笛卡尔乘积，A表的每一行，分别给B表的每一行做连接。所以当A表10行，B表20行的时候，连接起来一种200行。
SELECT * FROM knight,juexue;
2.内连接：也叫做对称连接，必须要基于某个等值条件，在A表中和B表中都出现的并且值相等的。才将其连接，并只显示其拥有相同值的。
2.1.我们以knight表和juexue表中让CouseID以及CID进行对应，并显示所有符合条件的。
SELECT * FROM knight,juexue WHERE knight.CouseID = juexue.CID;
2.2.当我们需要只显示两个表中的不同的列的时候，则详细制定显示内容，并将表合并并取出对称连接中能够匹配的值，进行查找
SELECT Name,Cname FROM knight,juexue WHERE knight.CouseID = juexue.CID;
2.3.当我们只需要显示武功的创始人对应的武侠中的名字时使用：
SELECT Cname, Name AS Author FROM juexue,knight WHERE juexue.Author = knight.UID;
其实我们可以给表取别名的：
SELECT Cname, Name AS Author FROM juexue AS j,knight AS k WHERE j.Author = k.UID;
当我们定义显示表中字段的时候，如果两个表的字段名相同，则必须在字段前加表名，比如
SELECT juexue.Cname, knight.Name AS Author FROM juexue,knight WHERE juexue.Author = knight.UID;
3.外连接：
3.1左外连接：LEFT JOIN … ON：左表中有的值但是右表中没有，能显示，但是定义为空
SELECT k.Name,j.Cname FROM knight AS k LEFT JOIN juexue AS j ON k.CouseID = j.CID;
3.2右外连接：RIGHT JOIN … ON：左表中没有但是右表中有，则能够显示右表中的每一项，左表中没有的则显示为NULL
SELECT j.Cname,k.Name FROM knight AS k RIGHT JOIN juexue AS j ON j.Author = k.UID;
3.3全外连接：FULL JOIN … ON:不论左表和右表是否有对应，都显示，有对应的则显示其对应，如果没有对应则显示为NULL。
好吧，MySQL其实不支持全外连接，但是SQL Server是支持的，在这里仅仅只是提一下。
4.自连接：由于自己的表中出现了需要去查找自己表中其他人的定义，所以当读取这个列的时候，就需要将其转换成自己表中的其他的对应的项，这就是自连接。
比如我们去定义knight表中的人对应的师傅，将其师傅的名字对应其人物的编号：
SELECT k1.Name,k2.Name AS Tutor FROM knight AS k1, knight AS k2 WHERE k1.Tutor = k2.UID
则符合条件的只有一个，杨过的师傅是小龙女。
5.UNION联合（Mysql特有的支持的连接）：可以实现将两张表的查询结果合成一个。而且这些结果可以完全来自两张不同的表。
我们单独的去分别查看两张表的内容:
SELECT UID AS ID, Name AS NAME FROM knight;
SELECT CID AS ID, Cname AS NAME FROM juexue;
我们可以看出这两个命令显示了不同的表的不同的内容，于是我们可以用UNION简单的将其其组合起来：
SELECT UID AS ID, Name AS NAME FROM knight
-> UNION
-> SELECT CID AS ID, Cname AS NAME FROM juexue;
2011.09.02，18:35，更新下午学习笔记。
从学习Mysql开始，两天笔记量：
关于MySQL的学习，没有结束，明天继续！压榨自己的学习能力！超越自己的学习极限。
本文出自 “For The Dream” 博客，请务必保留此出处http://foreveryan.blog.51cto.com/3508502/657640
转载于:https://blog.51cto.com/feilong0663/1384303
1528
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
