与MySQL的零距离接触.docx

《与MySQL的零距离接触》笔记

目录：（按住Ctrl并单击可访问链接）

第一部份：：MySQL基础
MySQL概述 MySQL的安装与配置 MySQL目录结构 修改编码方式 启动/停止MySQL服务
登陆/退出MySQL 修改MySQL提示符 MySQL常用命令 语法规范 操作数据库
第二部份：：数据类型
整型 浮点型 日期时间型 字符型
第三部份：：数据表操作
打开数据库 创建数据表 查看数据表 记录的插入与查找 空值与非空 自动编号及主键约束
唯一约束 默认约束
第四部份：：外键约束
外键约束的要求解析 编程数据表的默认存储引擎 实例 外键约束的参照操作
第五部份：：修改数据表
删除约束 添加约束 删除单/多列 添加单/多列 修改列定义和更名数据表
第六部份：：数据表的记录操作
插入记录 单表记录更新UPDATE 单表删除DELETE
第七部份：：查询表达式
select直接调用函数 查询表达式select_expr 条件查询WHERE 查询结果分组GROUP BY
分组条件HAVING order by对结果排序 限制查询结果返回的数量LIMIT
第八部份：：子查询
导入数据 子查询基本概念 由比较运算符引发的子查询 由[NOT] IN/EXISTS引发的子查询
使用INSERT...SELECT插入记录时使用子查询 多表更新一步到位
第九部份：：连接JOIN
连接的语法结构 三种连接的演示 关于连接的几点说明 自身连接 单表删除多张表的操作
第十部份：：内置函数库
字符函数 数值运算符和函数 比较运算符和函数 日期时间函数 信息函数 聚合函数 加密函数
第十一部份：：自定义函数
自定义函数简介 创建不带参数的自定义函数 创建带有参数 创建具有复合结构函数体的
第十二部份：：MySQL存储过程
存储过程的简介 创建不带参数的存储过程 创建带有IN类型参数的存储过程 创建带有IN和OUT类型参数的存储过程 创建带有多个OUT类型参数的存储过程 存储过程与自定义函数的区别
第十三部份：：MySQL存储引擎
存储引擎的简介及相关知识点 各种存储引擎的特点 设置存储引擎
第十四部份：：MySQL图形化管理工具
phpMyAdmin MySQL Workbench Navicat for MySQL

-----课程来自《幕课网》@开然

2014.12.28
Ø MySQL基础
l MySQL概述：
MySQL由瑞典MySQL AB公司开发，目前属于Oracle公司。
MySQL是一个开源的关系型数据库管理系统（是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据）。
MySQL分为社区版和企业版。
MySQL默认的端口号：3306
MySQL中的超级用户名：root
l MySQL的安装与配置：
安装方式：MIS安装（windows Installer）、ZIP安装两种方式。
配置MySQL：
1、 运行MySQL配置向导文件：MIS方式安装的需配置。
2、 启动MySQL服务：开始à管理工具à服务(本地)à找到MySQL设置为自动，然后在计算机属性à高级系统设置à高级à环境变量à在系统变量找到pathà编辑，确定变量值后面已经启动了MySQL服务。
l MySQL目录结构：
/bin：存储可执行文件
/data：存储数据文件
/docs：文档
/include：存储包含的头文件
/lib：存储库文件
/share：错误消息和字符集文件
l 修改编码方式：
打开my.ini在[mysql]下添加或修改default-character-set=utf8，在[mysqld]下添加或修改character-set-server=utf8。
l 启动/停止MySQL服务：
方式一：服务（本地）里设置MySQL为启动/停止状态;
方式二：命令行输入：启动：net start mysql 停止：net stop mysql
l 登陆/退出MySQL：
cdm运行后输入mysql -uroot -p 回车，提示输入密码，输入密码回车就可以了，如果刚装好的mysql，超级用户root使没有密码的，所以直接回车就可以进入mysql中了。出现提示符：mysql> 则表示已经进入 MySQL。
在登陆MySQL后，输入以下三个命令均可以退出MySQL（含分号）：exit; quit; \q; 。
l 修改MySQL提示符：
方式一：通过参数指定：cdm运行登陆后输入命令：mysql –uroot –proot - -prompt \h 回车，提示符改为localhost，\h参数是服务器名称，指向localhost。
方式二： 通过prompt命令修改：同上，输入命令：prompt mysql> 回车，即可修改提示符为mysql。
对于提示符的参数，如右侧所示：
输入如下命令：prompt \u@\h \d> 回车后提示符变为：root@localhost (none)>
l MySQL常用命令：
1、 打印当前服务器版本：select version();
2、 打印当前日期时间：select now();
3、 打印当前用户:select user();
l 语法规范:
1、 关键字与函数名称全部大写;
2、 数据库名称、表名称、字段名称全部小写;
3、 SQL语句必须以分号结尾。
l 操作数据库
1、 列出服务器的数据库：mysql>SHOW DATABASES;
2、 创建一个数据库名为abccs：mysql> CREATE DATABASE abccs;
列出创建的数据库，查看其字符集：（如下图字符集为latin1）

修改数据库，并设置字符集为utf8:

3、 选择所创建的数据库：mysql> USE abccs 回车出现Database changed，表示已经进入。
4、 删除一个数据库：mysql> DROP DATABASE abccs。
Ø 数据类型：
指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。数据类型有整型、浮点型、日期时间型、字符串。
l 整型：
计算机以二进制形式存储，符号位以0和1开头，1为有符号位。

l 浮点型：
float单精度，double双精度

l 日期时间型：

l 字符型：

Ø 数据表操作：
是数据库最重要的组成部份之一，是其它对象的基础。
l 打开数据库：如下左图
mysql> USE abc;
SELECT DATABASE(); à显示当前打开的数据库

l 创建数据表：如上右图
mysql> CREATE TABLE tb2(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED, à unsigned:无符号
-> salary FLOAT(8,2) UNSIGNED
-> );
l 查看数据表：
mysql> SHOW TABLES; à查看数据表
mysql> SHOW COLUMNS FROM tb1; à查看数据表结构,如右图

l 记录的插入与查找：
向数据表中写入记录（全部/部份）：
mysql>INSERT tb1 VALUES('Tom',25,6456.55);
mysql> INSERT tb1(username,salary) VALUES('John',8000.00);
查找全部的字符记录：
mysql> SELECT *FROM tb1;

l 空值与非空：
NULL，字段可以为空;NOT NULL，字段值禁止为空,如下例，在username输入时Null时会提示出错：
mysql> CREATE TABLE tb3(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
mysql> INSERT tb3 VALUES('Tony',null);
mysql> INSERT tb3 VALUES(NULL,'26');
ERROR 1048 (23000): Column 'username' cannot be null
l 自动编号及主键约束：
AURO_INCREMENT自动编号，且必须与主键组合使用，默认情况下，起始值为1，每次增量为1。
PRIMARY KEY主键约束，每张数据表只能存在一个主键，主键保户记录的唯一性，自动为NOT NULL，但主键不一定须与AURO_INCREMENT组合使用。
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, à将id定义为主键，并自动编号
-> username VARCHAR(30) NOT NULL
-> );
mysql> INSERT tb4(username) VALUES('Tom');
mysql> INSERT tb4(username) VALUES('John');
mysql> INSERT tb4(username) VALUES('Rose');
右图所示，实现自动编号。下面例子没有使用AURO_INCREMENT，当两个id号相同时，会报错：
mysql> CREATE TABLE tb5(
-> id SMALLINT KEY, à PRIMARY可以省略
-> username VARCHAR(30) NOT NULL
-> );

mysql> INSERT tb5(username) VALUES('Tom');
mysql> INSERT tb5 VALUES(4,'John');
mysql> INSERT tb5 VALUES(22,'Rose');
mysql> INSERT tb5 VALUES(22,'Hany');
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY' àid号重叠，报错
l 唯一约束：
UNIQUE KEY唯一约束，可以保证记录的唯一性，其字符段可以为空值NULL，每张数据表可以存在多个唯一约束。对比PRIMARY KEY只有一个约束，UNIQUE KEY可以有多个。
mysql> CREATE TABLE tb6(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED);
mysql> INSERT tb6(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT tb6(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username' à提示错误
mysql> insert tb6(username,age) values('Hany',22);
Query OK, 1 row affected (0.09 sec)
mysql> insert tb6(username,age) values('baby',12);
Query OK, 1 row affected (0.17 sec)
l 默认约束：
DEFAULT当插入记录时没有明确为字段赋值，则自动赋予默认值。
mysql> create table tb7(
-> id smallint unsigned auto_increment primary key,
-> user varchar(20) not null unique key,
-> sex enum('1','2','3') default '3');
mysql> insert tb7(user) values('Tom');

由上图可见，只输入一个user值为Tom时，系统默认其set为3。
Ø 外键约束：
约束保证数据的完整性和一致性，针对字段的数量把约束分为表级约束（针对一个字段时）和列级约束（两个或两个以上的字段）。约束类型包含以下5种：NOT NULL非空约束、PRIMARY KRY主键约束，UNIQUE KEY唯一约束、DEFAULT默认约束、FOREIGN KEY外键约束。
对一个数据列建立的约束，称为列级约束;对多个数据列建立的约束，称为表级约束。列级约束既可以在列定义时声明，也可以在列定义后声明。表级约束只能在列定义后声明。实际项目列级约束使用较多。
l 外键约束的要求解析
外键约束，保持数据一致性、完整性，实现一对一或一对多关系。外键约束的要求：
1、 父表（子表所参照的表）和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表;
2、 数据表的存储引擎只能为InnoDB;
3、 外键列（曾经加入FOREIGN关键词的那一表）和参照列（外键列所参照的那一列）必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同;
4、 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
l 编程数据表的默认存储引擎：
对于MySQL数据库，如果你要使用事务以及行级锁就必须使用INNODB引擎。如果你要使用全文索引，那必须使用MYISAM。 INNODB的实用性，安全性，稳定性更高但是效率比MYISAM稍差，但是有的功能是MYISAM没有的。修改MySQL的引擎为INNODB，可以使用外键，事务等功能，性能高。
修改my.ini，在[mysqld]下加上：default-storage-engine=INNODB
代码
mysql>CREATE TABLE provinces (
->id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
-> pname varchar(20) NOT NULL）

如右图所示，修改后引擎变为INNODB。

2014.12.29
l 实例：
创建一个user子表，其pid外键列参照刚才的provinces表（此称为父表）中的id列：
mysql> create table user(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid bigint, à数据类型不同，不能创建：ERROR 1215 (HY000): Cannot add foreign key constraint
-> pid smallint, à没有符号位，不能创建：ERROR 1215 (HY000): Cannot add foreign key constraint
-> pid smallint unsigned, à与参与列中数据类型相同、一样有符号位才能创建
-> foreign key(pid) references provinces(id)
-> );
由于已经修改过引擎，并且参考列在创建主键时自动生成索引，外键列的pid没有创建过索引，也会自动生成索引，如下图，出现两个索引，分别是id和pid。
查找索引命令：mysql> show indexes from user\G; à参数\G以树状形式显示(大写)

2015.1.1
l 外键约束的参照操作
1、 CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
创建一个user1表其pid外键列参照刚才的provinces(此处更该为pro)表的id列，并且使用cascade命令操作：
mysql> create table user1(
-> id smallint unsigned auto_increment primary key,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key(pid) references pro(id) on delete cascade);
其次，先往pro表添加记录，再往user1表中添加记录：
mysql> insert pro(pname) values('A');
mysql> insert pro(pname) values('B');
mysql> insert pro(pname) values('C');
mysql> insert user1(username,pid) values('Tom',3);
mysql> insert user1(username,pid) values('John',7); à输入pid为7时，父表不存在，报错，但id号还是自增，如右图id为2的记录为空：
mysql> insert user1(username,pid) values('John',1);
mysql> insert user1(username,pid) values('Rose',3);
然后对表进行删除操作，须添加条件，删除命令执行后，子表和父表的记录均同时被删除，其最后结果如下图所示：
mysql> delete from pro where id=3;

2、 SET NULL：从父表删除或更新行，并设置子表中的外键列为null。如果使用该选项，必须保证子表列没有指定not null。
3、 RESTRICT：拒绝对父表的删除或更新作。
4、 NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。
Ø 修改数据表：
l 添加单/多列：
如上面user1表，单列添加age和 password，多列添加truename和emaill。其命令如下：
mysql> alter table user1 add age tinyint unsigned not null default 10; à添加默认约束default
mysql>alter table user1 add password varchar(32) not null after username; à after在**后面插入数据
mysql> alter table user1 add (truename varchar(12) not null,emaill varchar(64) default null); à多列添加只能从数据表的后面堆，不能使用after、first命令。

l 删除单/多列：
将上表的进行单列删除truename，和多列删除password,emaill，命令如下：
mysql> alter table user1 drop truename;
mysql> alter table user1 drop password, drop emaill; à执行多条命令可以使用逗号隔开
mysql> alter table user1 drop age,add sex enum('boy','gril'); à删除的同时也可以添加，执行不同命令

l 添加约束：
先添加一个简单的新表，如右图：
mysql> create table user2(
-> username varchar(10) not null,
-> pid smallint unsigned);
-> alter table user2 add id smallint unsigned not null;
1、添加主键约束：
mysql> alter table user2 add constraint PK_user2_id primary key(id); à constraint指定一个名字（接后面）。
2、添加唯一约束：
mysql> alter table user2 add unique(username);
3、添加外键约束：
mysql> alter table user2 add foreign key(pid) references pro(id);
4、添加默认约束：
mysql> alter table user2 add age tinyint unsigned not null;
mysql> alter table user2 alter age set default 15;
mysql> alter table user2 add emill varchar(64) default 'abc@';

l 删除约束：
1、删除主键约束：
mysql> alter table user2 drop primary key; à主键只的一个，无须添加名字
2、删除唯一约束：
mysql> show indexes from user2\G; à先查看表中唯一约束，有两个：1.row，2.row
mysql> alter table user2 drop index username; à唯一约束有多个，须添加名字。删除后2.row没有了。
3、删除外键约束：
mysql> show create table user2; à查看系统默认给外键起的别名CONSTRAINT `user2_ibfk_1`
mysql> alter table user2 drop foreign key user2_ibfk_1;
4、删除默认约束：
mysql> alter table user2 alter emill drop default;
最后结果如下：

l 修改列定义和更名数据表：
1、修改列定义：
mysql> alter table user2 modify id samllint unsigned not null first; à将id的位置更换到顶端
mysql> alter table user2 modify id tinyint unsigned not null; à将id的数据类型更换为tinyint
2、修改列名称：
mysql> alter table user2 change pid p_id tinyint unsigned not null; à将pid名称该为p_id并将其数据类型更为tinyint。
3、数据表更名：建议不随意使用，特别是引用了表名的情况下。
方法一：mysql> alter table user2 rename user3;
方法二：mysql> rename table user3 to user2;
最后结果：

Ø 数据表的记录操作
l 插入记录:
新建一个新表：
mysql> create table user3(
-> id smallint unsigned primary key auto_increment,
-> username varchar(20) not null,
-> password varchar(60) not null,
-> age tinyint unsigned not null default 0,
-> sex boolean);
1、插入数据INSERT values：
mysql> insert user3 values(null,'Tom','123',25,1); à使用null，可以让id实现自增
mysql> insert user3 values(null,'John','345',24,0);
mysql> insert user3 values(default,'Hany','466',26,0); à使用default，也可以让id实现自增
mysql> insert user3 values(default,'Jack','466',3*7+3,1); à可以使用表达式3*7+3
mysql> insert user3 values(default,'Shen','768',default,1); à可以对age使用默认值
mysql> insert user3 values(default,'Wang','566',20,0),(default,'Baly','458',12*2,1); à一次输入多行
mysql> insert user3 values(null,'Biao',md5('123'),default,1); àmd5即密码123以哈希值的形式出现
2、插入记录INSERT SET:
与第一种方法的区别在于，此方法可以使用子查询，但只能一次性插入一条记录。
如上例继续添加一条记录：
mysql> insert user3 set username='Ben',password='879'; à默认值可以不写，如下面的id为9的记录
最后结果：

3、插入记录SELECT：
此方法可以将查询结果插入到指定数据表。
先建立一个空表：
mysql> create table user4(
-> id tinyint unsigned primary key auto_increment,
-> username varchar(20));
将上面的user3表中的部分数据插入到user4表中：
mysql> insert user4(username) select username from user3 where age>22;
à将user4表中年龄大于22岁的username添加到user3表中的username，如上图所示：
l 单表记录更新UPDATE：
mysql> update user3 set age=age+2; à将age都加2
mysql> update user3 set age=age+id,sex=0; à将age的值加上id的值，并且sex全部更为0
mysql> update user3 set age=age+2 where id%2=0; à将id号为偶数的age都加上2
结果如下：

l 单表删除DELETE：
mysql> delete from user3 where id=6; à删除id号为6的记录
mysql> insert user3 values(null,'Test','123',33,1); à重新插入的记录，不会顶替id为6的记录。

Ø 查询表达式：
l select直接调用函数：
如右所示：
mysql> select version();
mysql> select now();
l 查询表达式select_expr：
每一个表达式表示想要的一列，必须有至少一个。多个列之间以英文逗号分隔。星号 * 表示所有列。tb1_name.*可以表示命名表的所有列。查询表达式可以使用[AS]alias_name为其赋予别名。别名可用于GROUP BY，ORDRE BY或HAVING子句。
mysql> select id,username,age from user3; à只查询user3表中id,username,age三个记录
mysql> select username,id from user3; à将查询表的顺序可以任意调换
mysql> select user3.id,user3.username from user3; à tb1_name.*可以表示命名表的列

mysql> select id as userId,username as Uname from user3; à使用别名的形式查询，建议加上as
mysql> select id username from user3; à不加as，此处会将id的别名该为username，而不是查询出两列记录

l 条件查询WHERE：
对记录进行过滤，如果没有指定WHERE语句，则显示所有记录。在WHERE表达式中，可以使用MySQL支持的函数或运算符。（后面详解）
l 查询结果分组GROUP BY：
mysql> select age from user3 group by age; à指定表名称age里面的值分组，有相同的则被合并
mysql> select password from user3 group by 1;à指定表位置password以1开头的（不建议使用）
l 分组条件HAVING：
mysql> select username,password from user3 group by password having password>400;
à查找username，password两条记录中，以password的值分组，并只列出 password大于400的值。其结果如右所示：
mysql> select sex from user3 group by 1 having count(id) >=3;
à可以使用count函数或表达式使用

l order by对结果排序：
mysql> select * from user3 order by id desc; àdesc以id号倒序形式排列
mysql> select * from user3 order by age,id desc; à先以age排序，遇到age相同时再以id倒序

l 限制查询结果返回的数量LIMIT：
mysql> select * from user3 limit 5; à对查询的结果只取前5个
mysql> select * from user3 limit 1,4; à对查询的结果从第2开始取4条记录。（默认从0开始）
mysql> select * from user3 order by id desc limit 3,3; à以id进行倒序，再将结果从第4列开始取3条记录

Ø 子查询
l 导入数据
mysql> show variables like "%char%"; à查看当前编码
mysql>SET NAMES latin1 à统一指定编码，设置为 latin1
如下图所示：

然后导入数据，建立一个tdb_goods表。【数据链接点此】
2015.1.2
l 子查询基本概念：
子查询SubQuery是指出现在其它SQL语句内的SELECT子句。
例如：SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2) à其中SELECT * FROM t1称为外层查询/声明，col2 FROM t2则为子查询。
子查询指嵌套在查询内部，且必须始终出现在圆括号内。而事实上它有可能在子查询内部再嵌套子查询。
子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。
子查询的外层查询可以是SELECT、INSERT、UPDATE、SET或DO。
l 由比较运算符引发的子查询
使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>
语法结构：operand comparison_operator subquery
例子：不使用子查询方式：
mysql> select round(avg(goods_price),2) as avg_price from tdb_goods; à求所有电脑产品的平均价格四舍五入,并且保留两位小数：AVG,MAX,MIN、COUNT、SUM为聚合函数
mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price>5397.77 order by goods_price desc; à查询所有价格大于平均价格的商品，并且按价格降序排序
对上面例子使用子查询来实现以上功能：
mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price>(
select round(avg(goods_price),2) from tdb_goods) order by goods_price desc;

对于返回结果有多个时，须使用关键字：ANY，SOME和ALL。ANY 和SOME符合返回结果集其中的一个即可，等价于 IN，而ALL是要符合全部。

例子：查询价格大于或等于"超级本"价格的商品，并且按价格降序排列：
mysql> SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本'; à“超记本”的商品价格
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods
-> WHERE goods_price >= (SELECT goods_price FROM tdb_goods WHERE goods_ cate = '超级本')
à 此处直接写 >= 时报错： Subquery returns more than 1 row，由于超级本的价格返回多个结果，取大于等于超级本的价格系统不知道是4999、4299还是7999，所以报错
->WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
à = ANY 或 = SOME 等价于 IN，此得=ANY即返回