本篇一共10章,其中第九章存储引擎涉及 到代码方面的知识比较少,所以不做太多讲述
本篇谨献给MySQL初学者
第一章 初涉数据库
(一)概述
(二)MySQL的安装和配置
对于初学者来说,推荐安装mysql-workbench-oss-5.2.22-rc-win32.msi
下载路径 http://download.csdn.net/download/candy_moster/9901427
(三)启动和停止MySQL服务
以管理员方式运行cmd
重新启动 :net start mysql
如果服务已经启动,那么系统会报错
如果修改过配置文件,需要先停止服务,然后重启才能够生效。
关闭:net stop mysql
(四)登陆和退出
1 mysql -V 输出版本信息并退出
2 登录数据库
mysql -u root -p -P3306 -h127.0.0.1;
或者 mysql -u root -p
回车,输入密码
如果是连接本地服务器,并且用默认端口号,那么
只输入mysql -uroot -p即可
3 退出数据库,三种方法
exit;
quit;
\q;
4 其它信息
①-D:--database=mame打开指定数据库
②-delimiter=name 指定分隔符
③-h:-host=name服务器名称(默认本地服务器127.0.0.1)
④-p:--password[=name]密码
⑤-P:-port=#端口号(默认3306)
⑥-prompt=name 这只提示符
⑦-u:--user=name用户名
⑧-V:--version输出版本信息并且退出
(五)修改提示符
1 登陆上之后 用prompt命令
prompt mysql>;
2 几种常见提示符
\u;当前用户
\d;当前数据库
\h;服务器名称
\D;完整的日期
(六)MySQL常用命令以及语句规范
1 常用命令
SELECT VERSION();显示 当前服务器版本
SELECT NOW();显示当前时间
SELECT USER();显示当前用户
2 语句规范
关键字与函数名称全部大写
数据库名称,表名称,字段名称全部小写
SQL语句以分号结尾
(七)操作数据库
1 创建数据库
CREATE DATABASE db_name;
2查看当前服务器下面的数据库列表
SHOW DATABASES;
3 创建数据库时候加上的 条件语句,不会报错,但是会有警告
CREATE DATABASE IF NOT EXISTS t0;
4 查看 错误信息
SHOW WARNINGS;
5显示数据库创建的时候,所使用的指令是多少
SHOW CREATE DATABASE db_name;
6,创建一个数据库,编码方式为gbk;
CREATE DATABASE db_test CHARACTER SET gbk;
7,改变数据库编码方式
ALTER DATABASE db_test CHARACTER SET utf8;
8,删除数据库
DROP DATABASE db_test ;
第二章 数据类型与操作数据表
(一)内容回顾
1 MySQL基础,数据库的安装,配置,使用
2 MySQL默认的端口号3306,超级用户root,
3 创建数据库 CREATE DATABASE
修改数据库 ALTER DATABASE
删除数据库 DROP DATABASE
(二)数据类型之整型
TINYINT,//SMALLINT,//MEDIUMINT,//INT,//BIGINT
(三)数据类型之浮点型
FLOAT(M,D)
DOUBLE(M,D)
M,数字总位数,D,小数点后面的位数
(四)日期时间型
列类型 | 存储需求 |
YEAR | 1 |
TIME | 3 |
DATE | 3 |
DATETIME | 8 |
TIMESTAMP | 4 |
(五)数据类型之字符型
(六)创建数据表
数据表是数据库最重要的组成部分之一,是数据库其他对象的基础
1,打开数据库
USE 数据库名称
2,显示当前打开的数据库
SELECT DATABASE();
3,创建一个简单的数据库
CREATE TABLE td1(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);
(七)查看数据表
1 查看当前数据库的数据表,
SHOW TABLES;
2 查看其它数据库的数据表
SHOW TABLES FROM goods;
(八)查看数据表结构
SHOW COLUMNS FROM td1;
或者DESC td1;
(九)记录的插入和查找
INSERT tb_name VALUES(val....);
查找全部
SELECT * FROM db_name;
(十)空值与非空
NULL,字段值可以为空
NOT NUL,L字段值禁止为空
1 CREATE TABLE tt2(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);
(十一)自动编号
1 目的是为了保证某一条记录的唯一性
2 AUTO_INCREMENT自动编号必须与主键组合使用,必须定义为主键
3 AUTO_INCRE_MENT默认情况下,起始值为1 ,且 每次 自动增加为1
4示例
CREATE TABLE tt3(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL
);
(十二)初涉主键约束 PRIMARY KEY
1 ,每张表只能有一个主键
2 ,主键保证记录的唯一性
3 ,主键自动为NOT NULL
注意:AUTO_INCREMENT必须和主键一起使用,反过来说,主键不一定要和AUTO_INCEREMENT一起使用
4 ,代码同(十)里面的例子
(十三)初涉 唯一约束UNIQUE KEY
1 唯一约束可以保证记录的唯一性,不能重复插入
2 唯一约束的字段可以为空值
3 一张表可以由多个唯一约束
4 示例
CREATE TABLE tt5(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(30) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED
);
(十四)默认约束 DEFAULT
1默认值,当插入字段时,没有明确为字段赋值,则自动赋予默认值
2 示例
创建一张表
CREATE TABLE tt6(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
插入记录
INSERT tt6(username) VALUES('VIDAL');
查看
SELECT * FROM tt6;
(十五)总结
数据类型:字符型,整型,浮点型,日期时间型
数据表操作:插入记录,查找记录
记录操作:创建数据表,约束的使用
第三章:约束及修改数据表
(一)回顾和概述
1 数据类型和数据表操作
2 数据类型:整型,浮点型,字符型,日期时间型
3 数据表操作:如何创建数据表
PRIMARY KEY(主键约束)
UNIQUE KEY (唯一约束)
DEFAULT (默认约束)
NOT NULL(非空约束)
记录的插入和查找
4 本章重点:约束和数据表操作
(二)外键约束的要求解析
1 约束保证数据的完整性和一致性
2 约束分为表级约束和列级约束
3 约束类型包括
NOT NULL(非空约束)
PRIMARY KEY(主键约束)保持数据一致性,完整性,实现一对一或一对多关系
UNIQUE KEY(唯一约束)
DEFAULT (默认约束)
FROEIGN KEY(外键约束)
4 外键约束的要求
A 父表字表必须使用相同的存储引擎,而且禁止使用临时表。
B 数据表的存储引擎只能为InnoDB
C 外键列和参照列必须具有相似的数据类型。其最终数字的长度或是否有符号位必须相同;而字符的长度则可以不同
D外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引
5 编辑数据表的默认存储引擎
MySQL配置文件
Default-storage-engine=INNODB
6 通过下面的命令创建一张表通过创建时的命令查看存储引擎
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL
);
通过创建时的命令查看存储引擎
7现在有一张provinces表,以provinces表为父表创建字表,参照父表的id键,创建外键
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED ,
FOREIGN KEY(pid) REFERENCES provinces(id)
);
8 查看父表索引,字表索引
SHOW INDEX FROM provinces\G;
SHOW INDEX FROM users\G;
(三)外键约束的参照操作(2条代码,2个知识点)
外键约束的参照操作有下面四种,重点是前面两种
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表中删除或更新行,并设置子表中的外键列为NULL。如果使用改选项,必须保证子表列没有指定NOT NULL。
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准准SQL的关键字,在MySQL中与RESTRICT相同
1,再次参照provinces表创建一张子表,加入CASCADE字段
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces (id) ON DELETE CASCADE
);
2 父表和刚刚的字表如下存储些数据
父表
INSERT provinces(pname) VALUES('A'),('B'),('C');
子表
INSERT users1(username,pid) VALUES('TOM',3);
INSERT users1(username,pid) VALUES('JOHN',2);
INSERT users1(username,pid) VALUES('KAKA',1);
从父表中删除id为3的字段,再查看字表,发现字表对应的字段也消失了
DELETE FROM provinces WHERE id=3;
(四)标记约束和列级约束
1 对一个数据列建立 的约束,称为列级约束
2 对多个数据列建立的约束,称为表级约束
3列级约束既可以在列定义时声明,也可以在列定义后声明,表级约束只能在列定义后声明
(五)修改数据表-添加删除列(6条代码,6个知识点)
1 在表users1插入一列,不指定位置,于是自动就到了整个表的后面
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
2在表users1中 插入一个password字段,指定位置插入在username后面
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
3 在表users1,新增truename放在最前面
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
4 添加多列。在users1表中增加一个id_reclub列,一个id_nation列
ALTER TABLE users1 ADD(id_reclub SMALLINT,id_nation SMALLINT);
和添加单列的区别
区别1 添加单列的时候不需要加“()”
区别2 添加单列的时候能够指定位置关系,添加多列的时候不能指定位置关系
5 删除一列,在users1表中删除掉truename
ALTER TABLE users1 DROP truename;
6 删除多列。在users1表中删除password和age字段
ALTER TABLE users1 DROP password,DROP age;
7 拓展:试着删除一列的同时新增加一列
(六)修改数据表-添加约束(5条代码,5个知识点)
先创建一张表作为实验用
CREATE TABLE users2(
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED
);
1 增加id列,并设置为主键
ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
设置为主键
ALTER TABLE users2 ADD PRIMARY KEY(id);
或者
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
在后面 的这条添加约束的命令中,因为有了CONSTRANT关键字,所以可以给约束起名字
2 唯一约束和主键约束唯独不同的就是,唯一约束可以带有多个,而主键只能有一个
下面为username 添加唯一约束。
ALTER TABLE users2 ADD UNIQUE KEY(username);
3 如果我users2表中的id去参照provinces表中的id,那么,需要为uusers2添加一个外键约束
ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces(id);
4 默认约束:先增加一个字段作为测试
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
设置默认约束,默认值为15
ALTER TABLE users2 ALTER age SET DEFAULT 15;
5 删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;
6 思考,如何删除主键约束,唯一约束和外键约束
(七)修改数据表-删除约束(4条代码,4个知识点)
1 删除主键约束
ALTER TABLE users2 DROP PRIMARY KEY;
我们在删除主键约束的时候,没有加名字。是因为任何一张数据表有且只有一个主键,所以不需要加上名称
2 删除唯一约束
需要注意的是,我们删除的是某个字段上的唯一约束,而不是这个字段的名字,所以首先我们需要知道约束的名字
SHOW INDEX FROM users2\G;
那个Key_name就是索引
ALTER TABLE users2 DROP INDEX username;
3 删除外键约束
现在要进行数据表users2上的外键删除操作,那么一定要加上外键约束的名称
要知道外键约束的名称
SHOW CREATE TABLE users2;
由外键约束的名称删除外键
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
4 删除索引
ALTER TABLE users2 DROP INDEX pid;
(八)修改数据表-修改列定义和更名数据表(4条代码,4个知识点)
1 把users表中的id字段放在首位
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
2 下面对id的类型进行一下修改,从SMALLINT到TINYINT
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
注意在大类型改到小类型的时候有可能会造成数据的丢失
3下面准备修改pid的类型和名称,类型改为TINYINT,名称改为p_id
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
4 修改数据表的名字
ALTER TABLE users2 RENAME uusers2;
法二
RENAME TABLE uusers2 TO users2;(此法可以为多个数据表更名)
5 本节知识点总结
约束
按功能划分:NOT NULL,PRIMARY KEY ,UNIQUE KEY , DEFAULT ,FOREIGN KEY
按数据列的数目划分:表级约束,列级约束
修改数据表
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
(九)总结
本节知识点:约束,数据表
NOT NULL(非空约束)
PRIMARY KEY (主键约束)
功能 UNIQUE KEY(唯一约束)
约束 DEFAULT(默认约束)
FOREIGN KEY(外键约束)
数据列的数目 表级约束
列级约束
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
修改数据表 针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
第四章 操作数据库中的记录
(一) 回顾和概述
回顾:
本章重点:记录的操作
(二) 插入记录INSERT(4条代码,4个 知识点)
输入下面命令,创建一张新表,用作实验
CREATE TABLE nusers(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(200) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
1 赋值,INSERT插入字段
INSERT nusers VALUES (NULL,'VIDAL','23',23,1);
2 赋值为表达式
INSERT nusers VALUES (NULL,'SAM','23',3*7-5,1);
3,年龄这里赋值为DEFAULT
INSERT nusers VALUES(NULL,'RONALDO','7',DEFAULT,1);
4 一次性写入多条记录
INSERT nusers VALUES(DEFAULT,'INZAGHI','9',DEFAULT,1),(NULL,'KAKA','22',DEFAULT,1);
(三) 插入记录INSERT SET-SELECT(2条代码,2个知识点)
1 通过INSERT SET-SELECT插入一条,包括用户名和密码
INSERT nusers SET username='BAGGIO',password='10';
2 将查询结果插入到指定数据表,视频中没有太多讲解,第五章会遇到,须注意
INSERT [INTO] tdl_name[(col_name,...)]SELECT...
(四) 表单更新记录(3条代码,3个知识点)
在记录书写过程中,字段值可能是错误的,或者要更改字段值。需要用UPDATE语句
1 更新nusers表,年龄+5
UPDATE nusers SET age=age+5;
2 更新多列,age=age-id,sex=0
UPDATE nusers SET age=age-id,sex=0;
3 下面设置条件来进行改变。Id号为偶数的成员,年龄加上2岁
UPDATE nusers SET age=age+2 WHERE id%2=0;
(五) 表单删除记录DELETE (2条代码,2个知识点)
1 假如我想删除数据表中的第六条记录
DELETE FROM nusers WHERE id = 6;
2 如果现在重新插入一条记录,那么id号是多少?
INSERT nusers VALUES(NULL,'VIDAL','23',25,1);
(六) 查询表达式解析(4条代码,4个知识点)
1 查找数据表nusers中的前两列(id,username)
SELECT id,username FROM nusers;
2 在列的前面加上所属表的名字,可以用来表示所属表的字段
SELECT nusers.id,nusers.username FROM nusers;
3 不仅查表,而且起别名,以id,username字段为例
SELECT id AS userID,username AS uname FROM nusers;
4 书写不规范造成的错误
没加“,”,username 成为了id的别名
SELECT id username FROM nusers;
(七) 使用WHERE 语句进行条件查询
条件表达式
对记录进行过滤,如果没有指定WHERE语句,则显示所有记录。
在WHERE表达式 中,可以使用MySQL支持的函数或运算符
(八) group by 对查询结果进行分组(2条代码,2个知识点)
对查询结果进行分组
分组的时候,可以指定列的列名,列的位置
1按照性别分组
SELECT username FROM nusers GROUP BY sex;
2 按照位置
SELECT sex FROM nusers GROUP BY 1;
里面的‘1’是第一个字段出现的位置,不推荐
(九) having语句设置分组条件
Group分组的时候,还可以带分组的条件。可以全部记录都做分组,也可以只对某一部分记录进行分组
1 从nusers表中选出年龄大于20岁的
SELECT * FROM nusers HAVING age>20;
2 分组条件也 可以是函数
SELECT * FROM nusers HAVING count(id)>2;
(十) order by 语句对查询结果进行排序(2条代码,2个知识点)
Order by 可以按照某一个字段进行排序,也可以按照位置来排序。ASC为升序,DESC为降序
1 查询表nusers中的数据,按照id进行降序排列
SELECT * FROM nusers ORDER BY id DESC;
2 假如第一个字段不能排出想要的结果,那么再遵守第二个字段,甚至第三个字段
SELECT * FROM nusers ORDER BY age,id DESC;
(十一) limit语句限制查询数量(4条代码,4个知识点)
1 我只要nusers表中的前两条记录
SELECT * FROM nusers LIMIT 2;
2 要nusers表中第3,4条记录
SELECT * FROM nusers LIMIT 2,2;
3 先按照id号降序,从中找第3,4条记录
SELECT * FROM nusers ORDER BY id DESC LIMIT 2,2;
从结果中要明白,第几条记录,和id字段没有太大关系
4 新创建一张表做实验用
CREATE TABLE test(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
从nusers 表中选出年龄大于20的,将姓名插入到test表中
INSERT test(username) SELECT username FROM nusers WHERE age>=20;
(十二) 本节知识点
记录操作
INSERT
DELETE 单表删除,多表删除
UPDATE 单表更新,多表更新(第五章)
SELECT
第三章:约束及修改数据表
(一)回顾和概述
1 数据类型和数据表操作
2 数据类型:整型,浮点型,字符型,日期时间型
3 数据表操作:如何创建数据表
PRIMARY KEY(主键约束)
UNIQUE KEY (唯一约束)
DEFAULT (默认约束)
NOT NULL(非空约束)
记录的插入和查找
4 本章重点:约束和数据表操作
(二)外键约束的要求解析
1 约束保证数据的完整性和一致性
2 约束分为表级约束和列级约束
3 约束类型包括
NOT NULL(非空约束)
PRIMARY KEY(主键约束)保持数据一致性,完整性,实现一对一或一对多关系
UNIQUE KEY(唯一约束)
DEFAULT (默认约束)
FROEIGN KEY(外键约束)
4 外键约束的要求
A 父表字表必须使用相同的存储引擎,而且禁止使用临时表。
B 数据表的存储引擎只能为InnoDB
C 外键列和参照列必须具有相似的数据类型。其最终数字的长度或是否有符号位必须相同;而字符的长度则可以不同
D外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引
5 编辑数据表的默认存储引擎
MySQL配置文件
Default-storage-engine=INNODB
6 通过下面的命令创建一张表通过创建时的命令查看存储引擎
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL
);
通过创建时的命令查看存储引擎
7现在有一张provinces表,以provinces表为父表创建字表,参照父表的id键,创建外键
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED ,
FOREIGN KEY(pid) REFERENCES provinces(id)
);
8 查看父表索引,字表索引
SHOW INDEX FROM provinces\G;
SHOW INDEX FROM users\G;
(三)外键约束的参照操作(2条代码,2个知识点)
外键约束的参照操作有下面四种,重点是前面两种
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表中删除或更新行,并设置子表中的外键列为NULL。如果使用改选项,必须保证子表列没有指定NOT NULL。
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准准SQL的关键字,在MySQL中与RESTRICT相同
1,再次参照provinces表创建一张子表,加入CASCADE字段
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces (id) ON DELETE CASCADE
);
2 父表和刚刚的字表如下存储些数据
父表
INSERT provinces(pname) VALUES('A'),('B'),('C');
子表
INSERT users1(username,pid) VALUES('TOM',3);
INSERT users1(username,pid) VALUES('JOHN',2);
INSERT users1(username,pid) VALUES('KAKA',1);
从父表中删除id为3的字段,再查看字表,发现字表对应的字段也消失了
DELETE FROM provinces WHERE id=3;
(四)标记约束和列级约束
1 对一个数据列建立 的约束,称为列级约束
2 对多个数据列建立的约束,称为表级约束
3列级约束既可以在列定义时声明,也可以在列定义后声明,表级约束只能在列定义后声明
(五)修改数据表-添加删除列(6条代码,6个知识点)
1 在表users1插入一列,不指定位置,于是自动就到了整个表的后面
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
2在表users1中 插入一个password字段,指定位置插入在username后面
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
3 在表users1,新增truename放在最前面
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
4 添加多列。在users1表中增加一个id_reclub列,一个id_nation列
ALTER TABLE users1 ADD(id_reclub SMALLINT,id_nation SMALLINT);
和添加单列的区别
区别1 添加单列的时候不需要加“()”
区别2 添加单列的时候能够指定位置关系,添加多列的时候不能指定位置关系
5 删除一列,在users1表中删除掉truename
ALTER TABLE users1 DROP truename;
6 删除多列。在users1表中删除password和age字段
ALTER TABLE users1 DROP password,DROP age;
7 拓展:试着删除一列的同时新增加一列
(六)修改数据表-添加约束(5条代码,5个知识点)
先创建一张表作为实验用
CREATE TABLE users2(
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED
);
1 增加id列,并设置为主键
ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
设置为主键
ALTER TABLE users2 ADD PRIMARY KEY(id);
或者
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
在后面 的这条添加约束的命令中,因为有了CONSTRANT关键字,所以可以给约束起名字
2 唯一约束和主键约束唯独不同的就是,唯一约束可以带有多个,而主键只能有一个
下面为username 添加唯一约束。
ALTER TABLE users2 ADD UNIQUE KEY(username);
3 如果我users2表中的id去参照provinces表中的id,那么,需要为uusers2添加一个外键约束
ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces(id);
4 默认约束:先增加一个字段作为测试
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
设置默认约束,默认值为15
ALTER TABLE users2 ALTER age SET DEFAULT 15;
5 删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;
6 思考,如何删除主键约束,唯一约束和外键约束
(七)修改数据表-删除约束(4条代码,4个知识点)
1 删除主键约束
ALTER TABLE users2 DROP PRIMARY KEY;
我们在删除主键约束的时候,没有加名字。是因为任何一张数据表有且只有一个主键,所以不需要加上名称
2 删除唯一约束
需要注意的是,我们删除的是某个字段上的唯一约束,而不是这个字段的名字,所以首先我们需要知道约束的名字
SHOW INDEX FROM users2\G;
那个Key_name就是索引
ALTER TABLE users2 DROP INDEX username;
3 删除外键约束
现在要进行数据表users2上的外键删除操作,那么一定要加上外键约束的名称
要知道外键约束的名称
SHOW CREATE TABLE users2;
由外键约束的名称删除外键
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
4 删除索引
ALTER TABLE users2 DROP INDEX pid;
(八)修改数据表-修改列定义和更名数据表(4条代码,4个知识点)
1 把users表中的id字段放在首位
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
2 下面对id的类型进行一下修改,从SMALLINT到TINYINT
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
注意在大类型改到小类型的时候有可能会造成数据的丢失
3下面准备修改pid的类型和名称,类型改为TINYINT,名称改为p_id
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
4 修改数据表的名字
ALTER TABLE users2 RENAME uusers2;
法二
RENAME TABLE uusers2 TO users2;(此法可以为多个数据表更名)
5 本节知识点总结
约束
按功能划分:NOT NULL,PRIMARY KEY ,UNIQUE KEY , DEFAULT ,FOREIGN KEY
按数据列的数目划分:表级约束,列级约束
修改数据表
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
(九)总结
本节知识点:约束,数据表
NOT NULL(非空约束)
PRIMARY KEY (主键约束)
功能 UNIQUE KEY(唯一约束)
约束 DEFAULT(默认约束)
FOREIGN KEY(外键约束)
数据列的数目 表级约束
列级约束
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
修改数据表 针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
第四章 操作数据库中的记录
(一) 回顾和概述
回顾:
本章重点:记录的操作
(二) 插入记录INSERT(4条代码,4个 知识点)
输入下面命令,创建一张新表,用作实验
CREATE TABLE nusers(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(200) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
1 赋值,INSERT插入字段
INSERT nusers VALUES (NULL,'VIDAL','23',23,1);
2 赋值为表达式
INSERT nusers VALUES (NULL,'SAM','23',3*7-5,1);
3,年龄这里赋值为DEFAULT
INSERT nusers VALUES(NULL,'RONALDO','7',DEFAULT,1);
4 一次性写入多条记录
INSERT nusers VALUES(DEFAULT,'INZAGHI','9',DEFAULT,1),(NULL,'KAKA','22',DEFAULT,1);
(三) 插入记录INSERT SET-SELECT(2条代码,2个知识点)
1 通过INSERT SET-SELECT插入一条,包括用户名和密码
INSERT nusers SET username='BAGGIO',password='10';
2 将查询结果插入到指定数据表,视频中没有太多讲解,第五章会遇到,须注意
INSERT [INTO] tdl_name[(col_name,...)]SELECT...
(四) 表单更新记录(3条代码,3个知识点)
在记录书写过程中,字段值可能是错误的,或者要更改字段值。需要用UPDATE语句
1 更新nusers表,年龄+5
UPDATE nusers SET age=age+5;
2 更新多列,age=age-id,sex=0
UPDATE nusers SET age=age-id,sex=0;
3 下面设置条件来进行改变。Id号为偶数的成员,年龄加上2岁
UPDATE nusers SET age=age+2 WHERE id%2=0;
(五) 表单删除记录DELETE (2条代码,2个知识点)
1 假如我想删除数据表中的第六条记录
DELETE FROM nusers WHERE id = 6;
2 如果现在重新插入一条记录,那么id号是多少?
INSERT nusers VALUES(NULL,'VIDAL','23',25,1);
(六) 查询表达式解析(4条代码,4个知识点)
1 查找数据表nusers中的前两列(id,username)
SELECT id,username FROM nusers;
2 在列的前面加上所属表的名字,可以用来表示所属表的字段
SELECT nusers.id,nusers.username FROM nusers;
3 不仅查表,而且起别名,以id,username字段为例
SELECT id AS userID,username AS uname FROM nusers;
4 书写不规范造成的错误
没加“,”,username 成为了id的别名
SELECT id username FROM nusers;
(七) 使用WHERE 语句进行条件查询
条件表达式
对记录进行过滤,如果没有指定WHERE语句,则显示所有记录。
在WHERE表达式 中,可以使用MySQL支持的函数或运算符
(八) group by 对查询结果进行分组(2条代码,2个知识点)
对查询结果进行分组
分组的时候,可以指定列的列名,列的位置
1按照性别分组
SELECT username FROM nusers GROUP BY sex;
2 按照位置
SELECT sex FROM nusers GROUP BY 1;
里面的‘1’是第一个字段出现的位置,不推荐
(九) having语句设置分组条件
Group分组的时候,还可以带分组的条件。可以全部记录都做分组,也可以只对某一部分记录进行分组
1 从nusers表中选出年龄大于20岁的
SELECT * FROM nusers HAVING age>20;
2 分组条件也 可以是函数
SELECT * FROM nusers HAVING count(id)>2;
(十) order by 语句对查询结果进行排序(2条代码,2个知识点)
Order by 可以按照某一个字段进行排序,也可以按照位置来排序。ASC为升序,DESC为降序
1 查询表nusers中的数据,按照id进行降序排列
SELECT * FROM nusers ORDER BY id DESC;
2 假如第一个字段不能排出想要的结果,那么再遵守第二个字段,甚至第三个字段
SELECT * FROM nusers ORDER BY age,id DESC;
(十一) limit语句限制查询数量(4条代码,4个知识点)
1 我只要nusers表中的前两条记录
SELECT * FROM nusers LIMIT 2;
2 要nusers表中第3,4条记录
SELECT * FROM nusers LIMIT 2,2;
3 先按照id号降序,从中找第3,4条记录
SELECT * FROM nusers ORDER BY id DESC LIMIT 2,2;
从结果中要明白,第几条记录,和id字段没有太大关系
4 新创建一张表做实验用
CREATE TABLE test(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
从nusers 表中选出年龄大于20的,将姓名插入到test表中
INSERT test(username) SELECT username FROM nusers WHERE age>=20;
(十二) 本节知识点
记录操作
INSERT
DELETE 单表删除,多表删除
UPDATE 单表更新,多表更新(第五章)
SELECT第三章:约束及修改数据表
(一)回顾和概述
1 数据类型和数据表操作
2 数据类型:整型,浮点型,字符型,日期时间型
3 数据表操作:如何创建数据表
PRIMARY KEY(主键约束)
UNIQUE KEY (唯一约束)
DEFAULT (默认约束)
NOT NULL(非空约束)
记录的插入和查找
4 本章重点:约束和数据表操作
(二)外键约束的要求解析
1 约束保证数据的完整性和一致性
2 约束分为表级约束和列级约束
3 约束类型包括
NOT NULL(非空约束)
PRIMARY KEY(主键约束)保持数据一致性,完整性,实现一对一或一对多关系
UNIQUE KEY(唯一约束)
DEFAULT (默认约束)
FROEIGN KEY(外键约束)
4 外键约束的要求
A 父表字表必须使用相同的存储引擎,而且禁止使用临时表。
B 数据表的存储引擎只能为InnoDB
C 外键列和参照列必须具有相似的数据类型。其最终数字的长度或是否有符号位必须相同;而字符的长度则可以不同
D外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引
5 编辑数据表的默认存储引擎
MySQL配置文件
Default-storage-engine=INNODB
6 通过下面的命令创建一张表通过创建时的命令查看存储引擎
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL
);
通过创建时的命令查看存储引擎
7现在有一张provinces表,以provinces表为父表创建字表,参照父表的id键,创建外键
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED ,
FOREIGN KEY(pid) REFERENCES provinces(id)
);
8 查看父表索引,字表索引
SHOW INDEX FROM provinces\G;
SHOW INDEX FROM users\G;
(三)外键约束的参照操作(2条代码,2个知识点)
外键约束的参照操作有下面四种,重点是前面两种
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表中删除或更新行,并设置子表中的外键列为NULL。如果使用改选项,必须保证子表列没有指定NOT NULL。
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准准SQL的关键字,在MySQL中与RESTRICT相同
1,再次参照provinces表创建一张子表,加入CASCADE字段
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces (id) ON DELETE CASCADE
);
2 父表和刚刚的字表如下存储些数据
父表
INSERT provinces(pname) VALUES('A'),('B'),('C');
子表
INSERT users1(username,pid) VALUES('TOM',3);
INSERT users1(username,pid) VALUES('JOHN',2);
INSERT users1(username,pid) VALUES('KAKA',1);
从父表中删除id为3的字段,再查看字表,发现字表对应的字段也消失了
DELETE FROM provinces WHERE id=3;
(四)标记约束和列级约束
1 对一个数据列建立 的约束,称为列级约束
2 对多个数据列建立的约束,称为表级约束
3列级约束既可以在列定义时声明,也可以在列定义后声明,表级约束只能在列定义后声明
(五)修改数据表-添加删除列(6条代码,6个知识点)
1 在表users1插入一列,不指定位置,于是自动就到了整个表的后面
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
2在表users1中 插入一个password字段,指定位置插入在username后面
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
3 在表users1,新增truename放在最前面
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
4 添加多列。在users1表中增加一个id_reclub列,一个id_nation列
ALTER TABLE users1 ADD(id_reclub SMALLINT,id_nation SMALLINT);
和添加单列的区别
区别1 添加单列的时候不需要加“()”
区别2 添加单列的时候能够指定位置关系,添加多列的时候不能指定位置关系
5 删除一列,在users1表中删除掉truename
ALTER TABLE users1 DROP truename;
6 删除多列。在users1表中删除password和age字段
ALTER TABLE users1 DROP password,DROP age;
7 拓展:试着删除一列的同时新增加一列
(六)修改数据表-添加约束(5条代码,5个知识点)
先创建一张表作为实验用
CREATE TABLE users2(
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED
);
1 增加id列,并设置为主键
ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
设置为主键
ALTER TABLE users2 ADD PRIMARY KEY(id);
或者
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
在后面 的这条添加约束的命令中,因为有了CONSTRANT关键字,所以可以给约束起名字
2 唯一约束和主键约束唯独不同的就是,唯一约束可以带有多个,而主键只能有一个
下面为username 添加唯一约束。
ALTER TABLE users2 ADD UNIQUE KEY(username);
3 如果我users2表中的id去参照provinces表中的id,那么,需要为uusers2添加一个外键约束
ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces(id);
4 默认约束:先增加一个字段作为测试
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
设置默认约束,默认值为15
ALTER TABLE users2 ALTER age SET DEFAULT 15;
5 删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;
6 思考,如何删除主键约束,唯一约束和外键约束
(七)修改数据表-删除约束(4条代码,4个知识点)
1 删除主键约束
ALTER TABLE users2 DROP PRIMARY KEY;
我们在删除主键约束的时候,没有加名字。是因为任何一张数据表有且只有一个主键,所以不需要加上名称
2 删除唯一约束
需要注意的是,我们删除的是某个字段上的唯一约束,而不是这个字段的名字,所以首先我们需要知道约束的名字
SHOW INDEX FROM users2\G;
那个Key_name就是索引
ALTER TABLE users2 DROP INDEX username;
3 删除外键约束
现在要进行数据表users2上的外键删除操作,那么一定要加上外键约束的名称
要知道外键约束的名称
SHOW CREATE TABLE users2;
由外键约束的名称删除外键
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
4 删除索引
ALTER TABLE users2 DROP INDEX pid;
(八)修改数据表-修改列定义和更名数据表(4条代码,4个知识点)
1 把users表中的id字段放在首位
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
2 下面对id的类型进行一下修改,从SMALLINT到TINYINT
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
注意在大类型改到小类型的时候有可能会造成数据的丢失
3下面准备修改pid的类型和名称,类型改为TINYINT,名称改为p_id
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
4 修改数据表的名字
ALTER TABLE users2 RENAME uusers2;
法二
RENAME TABLE uusers2 TO users2;(此法可以为多个数据表更名)
5 本节知识点总结
约束
按功能划分:NOT NULL,PRIMARY KEY ,UNIQUE KEY , DEFAULT ,FOREIGN KEY
按数据列的数目划分:表级约束,列级约束
修改数据表
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
(九)总结
本节知识点:约束,数据表
NOT NULL(非空约束)
PRIMARY KEY (主键约束)
功能 UNIQUE KEY(唯一约束)
约束 DEFAULT(默认约束)
FOREIGN KEY(外键约束)
数据列的数目 表级约束
列级约束
针对字段的操作:添加/删除字段,修改列定义,修改列名称等
修改数据表 针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)
第四章 操作数据库中的记录
(一) 回顾和概述
回顾:
本章重点:记录的操作
(二) 插入记录INSERT(4条代码,4个 知识点)
输入下面命令,创建一张新表,用作实验
CREATE TABLE nusers(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(200) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
1 赋值,INSERT插入字段
INSERT nusers VALUES (NULL,'VIDAL','23',23,1);
2 赋值为表达式
INSERT nusers VALUES (NULL,'SAM','23',3*7-5,1);
3,年龄这里赋值为DEFAULT
INSERT nusers VALUES(NULL,'RONALDO','7',DEFAULT,1);
4 一次性写入多条记录
INSERT nusers VALUES(DEFAULT,'INZAGHI','9',DEFAULT,1),(NULL,'KAKA','22',DEFAULT,1);
(三) 插入记录INSERT SET-SELECT(2条代码,2个知识点)
1 通过INSERT SET-SELECT插入一条,包括用户名和密码
INSERT nusers SET username='BAGGIO',password='10';
2 将查询结果插入到指定数据表,视频中没有太多讲解,第五章会遇到,须注意
INSERT [INTO] tdl_name[(col_name,...)]SELECT...
(四) 表单更新记录(3条代码,3个知识点)
在记录书写过程中,字段值可能是错误的,或者要更改字段值。需要用UPDATE语句
1 更新nusers表,年龄+5
UPDATE nusers SET age=age+5;
2 更新多列,age=age-id,sex=0
UPDATE nusers SET age=age-id,sex=0;
3 下面设置条件来进行改变。Id号为偶数的成员,年龄加上2岁
UPDATE nusers SET age=age+2 WHERE id%2=0;
(五) 表单删除记录DELETE (2条代码,2个知识点)
1 假如我想删除数据表中的第六条记录
DELETE FROM nusers WHERE id = 6;
2 如果现在重新插入一条记录,那么id号是多少?
INSERT nusers VALUES(NULL,'VIDAL','23',25,1);
(六) 查询表达式解析(4条代码,4个知识点)
1 查找数据表nusers中的前两列(id,username)
SELECT id,username FROM nusers;
2 在列的前面加上所属表的名字,可以用来表示所属表的字段
SELECT nusers.id,nusers.username FROM nusers;
3 不仅查表,而且起别名,以id,username字段为例
SELECT id AS userID,username AS uname FROM nusers;
4 书写不规范造成的错误
没加“,”,username 成为了id的别名
SELECT id username FROM nusers;
(七) 使用WHERE 语句进行条件查询
条件表达式
对记录进行过滤,如果没有指定WHERE语句,则显示所有记录。
在WHERE表达式 中,可以使用MySQL支持的函数或运算符
(八) group by 对查询结果进行分组(2条代码,2个知识点)
对查询结果进行分组
分组的时候,可以指定列的列名,列的位置
1按照性别分组
SELECT username FROM nusers GROUP BY sex;
2 按照位置
SELECT sex FROM nusers GROUP BY 1;
里面的‘1’是第一个字段出现的位置,不推荐
(九) having语句设置分组条件
Group分组的时候,还可以带分组的条件。可以全部记录都做分组,也可以只对某一部分记录进行分组
1 从nusers表中选出年龄大于20岁的
SELECT * FROM nusers HAVING age>20;
2 分组条件也 可以是函数
SELECT * FROM nusers HAVING count(id)>2;
(十) order by 语句对查询结果进行排序(2条代码,2个知识点)
Order by 可以按照某一个字段进行排序,也可以按照位置来排序。ASC为升序,DESC为降序
1 查询表nusers中的数据,按照id进行降序排列
SELECT * FROM nusers ORDER BY id DESC;
2 假如第一个字段不能排出想要的结果,那么再遵守第二个字段,甚至第三个字段
SELECT * FROM nusers ORDER BY age,id DESC;
(十一) limit语句限制查询数量(4条代码,4个知识点)
1 我只要nusers表中的前两条记录
SELECT * FROM nusers LIMIT 2;
2 要nusers表中第3,4条记录
SELECT * FROM nusers LIMIT 2,2;
3 先按照id号降序,从中找第3,4条记录
SELECT * FROM nusers ORDER BY id DESC LIMIT 2,2;
从结果中要明白,第几条记录,和id字段没有太大关系
4 新创建一张表做实验用
CREATE TABLE test(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
从nusers 表中选出年龄大于20的,将姓名插入到test表中
INSERT test(username) SELECT username FROM nusers WHERE age>=20;
(十二) 本节知识点
记录操作
INSERT
DELETE 单表删除,多表删除
UPDATE 单表更新,多表更新(第五章)
SELECT
第五章 子查询与连接
(一)数据准备
创建表
CREATE TABLE tdb_goods(
goods_id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED DEFAULT 0.000,
is_show TINYINT(1) DEFAULT 1,
is_saleoff TINYINT(1) DEFAULT 0
);
插入数据
INSERT tdb_goods VALUES (NULL,'联想台式机','高科技产品','联想',5500,1,0);
INSERT tdb_goods VALUES (NULL,'三星笔记本','高科技产品','三星',7500,1,0);
INSERT tdb_goods VALUES (NULL,'三星便携式','高科技产品','三星',6500,1,0);
INSERT tdb_goods VALUES (NULL,'苹果笔记本','高科技产品','苹果',9000,1,0);
INSERT tdb_goods VALUES (NULL,'外星人笔记本','高科技产品','外星人',20000,1,0);
INSERT tdb_goods VALUES (NULL,'苹果笔记本','高科技产品','苹果',9000,1,0);
INSERT tdb_goods VALUES (NULL,'外星人笔记本','高科技产品','外星人',20000,1,0);
INSERT tdb_goods VALUES (NULL,'苹果笔记本','高科技产品','苹果',9000,1,0);
INSERT tdb_goods VALUES (NULL,'外星人笔记本','高科技产品','外星人',20000,1,0);
INSERT tdb_goods VALUES (NULL,'枝江大曲','酒','湖北枝江酒业',400,1,0);
INSERT tdb_goods VALUES (NULL,'人参','药材','长白山药业',40000,1,0);
INSERT tdb_goods VALUES (NULL,'十八街麻花','食品','天津特产',200,1,0);
INSERT tdb_goods VALUES (NULL,'耳朵眼炸糕','食品','天津特产',300,1,0);
(二)子查询(概念)
1 子查询是出现在其它SQL语句中的SQL子句
2 子查询 嵌套在查询内部,且必须是中出现在圆括号内
3 子查询可以包括多个关键字或条件,如DISTINCT,GROUP BY,ORDER BY LIMIT函数等
4 子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO
5,子查询可以返回标量,一行,一列或子查询
(三)由比较运算符引发的子查询,(5条代码,2个知识点,要知道如何嵌套SQL语句,还有修饰SOME ,ANY,ALL的用法)
本节讲述第一类,使用比较运算符的子查询
1,求平均值
SELECT AVG(goods_price) FROM tdb_goods;
2,四舍五入
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
3,查看哪些产品大于这个平均值
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>( SELECT ROUND(AVG(goods_price),2)FROM tdb_goods);
4,查询高科技产品的价格
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_cate='高科技产品';
5,查询哪些产品的价格 大于这些高科技产品
SELECT * FROM tdb_goods WHERE goods_price>ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='高科技产品');
在这里,引进了ALL,作为修饰
ALL,ANY,SOME都用来修饰比较运算符,SOME,ANY等价,跟在小于号后面,表示小于任意一条记录,ALL跟在小于号后面表示小于所有记录。跟在大于号后面SOME,ANY表示大于任意一条记录,ALL表示大于所有的记录
(四)第二种子查询,使用[NOT] IN EXITS引发的子查询(本节2条代码,知识点1个,重点在于理解)
ANY 和IN等效,!=ALL和NOT IN等效
1 查询高科技产品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate='高科技产品');
2 出去高科技产品的记录
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate='高科技产品');
(五)使用INSERT...SELECT插入记录(2条代码,2个知识点)
首先创建一个商品的分类表
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);
1,查找不同的分类,在查找的过程中按照不同的分类进行分组,这样就不会有重复的记录
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
2,查询结果放到新表里面
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
(六)多表更新(1条代码,1个知识点)
1,让tdb_goods表中,goods_cate字段,变成tdb_goods_cates字段cate_name对应的cate_id
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate = cate_id;
(七)多表更新之一步到位(4条代码,4个知识点)
1 创建一张品牌表,把所需要的字段插入进去
CREATE TABLE tdb_goods_brands
(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
2 把tdb_goods表中的brand_name字段变为tdb_goods_brands表中的brand_id
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name= b.brand_name
SET g.brand_name =b.brand_id;
3 修改字段类型
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
4 tdb_goods_brands表中插入记录
INSERT tdb_goods_brands(brand_name) VALUES('腾讯'),('华为');
(八)连接的语法结构
连接有固定的格式,熟悉常见的语法结构即可
(九)内连接INNER JOIN (1条代码,1个知识点)
连接类型分为三种:内连接,左外连接和右外连接
INNER JOIN,内连接,显示左表以及右表符合条件的记录
在MySQL 中,JOIN,CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN 左外连接
RIGHT [OUTER] JOIN 右外连接
连接条件:
使用ON关键字来设定连接条件,也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
1 从表tdb_goods中查询商品id(goods_id),商品名字(goods_name),类别名字(goods_cate)
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
(十) 外连接(2条代码,2个知识点 )
左连接,显示左表的全部记录以及右表是符合条件的记录,没有符合条件的记录,显示为NULL
右连接,显示右表全部记录以及左表的符合条件的记录,符合条件的记录,显示为NULL
1 ,通过左连接查找 goods_id,goods_name,cate_name
SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
2通过右连接查找 goods_id,goods_name,cate_name
SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
(十一),多表连接(1条代码,1个知识点)
注意,关键词的含义
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.cate_id=b.brand_id\G;
(十二)关于连接的几点说明
外连接
(十三 ),无线级分表设计(5条代码,2个知识点)
数据准备,创建一张表,插入数据
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑,办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超极本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
主要代码,1 查找子类,以及子类对应的父类
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p
-> ON s.parent_id = p.type_id;
2 ,查找父类,以及父类对应的子类
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id;
3 查找父类,以及父类下面子类的数目
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id GROUP BY p.type_name;
4按照顺序出现
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id ;
5显示子类的数目
SELECT p.type_id,p.type_name,COUNT(s.type_name)AS child_count FROM tdb_goods_types p LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id ;
(十四)多表删除,通过一张表来模拟多张表(3条代码,1个知识点)
主要代码
1,查找重复记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name;(重复的记录只显示一条,按照名字显示)
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>1;(分组的时候用HAVING加条件),这条语句的执行结果可以和后面的做子查询
2删除重复记录,保留id号比较小的那个,id号比较大的删除
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>1)AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id>t2.goods_id;
第六章 运算符和函数
(一)概述
1 字符函数
2 数值运算符和函数
3 比较运算符和函数
4 日期时间函数
5 信息函数
6 聚合函数
7 加密函数
(二)字符函数(30条代码,7个知识点)
函数名称 | 描述 |
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换成小写字母 |
UPPER() | 转换成大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
1 使用CONCAT()连接两个字符
SELECT CONCAT ('goods','mysql');
连接三个字符
SELECT CONCAT ('goods','-','mysql');
2 弄张表,做演示用
CREATE TABLE test(
first_name VARCHAR(20) DEFAULT NULL,
last_name VARCHAR(10) DEFAULT NULL
);
INSERT test VALUES('A','B'),('C','D'),('tom%','123'),('NULL','11');
3 将这两个字段合并在一起输出,AS起别名
SELECT CONCAT(first_name,last_name) AS fullname FROM test;
4 CONCAT_WS()需要至少三个参数
SELECT CONCAT_WS('|','A','B','C');
5 自动四舍五入
SELECT FORMAT(12580.561,2);
6 大写变小写
SELECT LOWER('MySQL');
7 小写变大写
SELECT UPPER('mysql');
8获取左侧N个字符
SELECT LEFT('MySQL',2);
9将获取到的字符转化为小写,需要嵌套
SELECT LOWER(LEFT('MySQL',2));
10 获取右侧N个字符
SELECT RIGHT('MySQL',2);
名称 | 描述 |
LENGTH() | 获取字符串长度 |
LTRIM() | 删除前导空格 |
RTRIM() | 删除后续空格 |
TRIM() | 删除前导和后续空格 |
SUBSTRING() | 字符串截取 |
[NOT] LIKE | 模式匹配 |
REPLACE() | 字符串替换 |
11 获取字符串的长度
SELECT LENGTH('MySQL');
12 如果由空格的话
SELECT LENGTH('My SQL');
13先删除前导空格,前导空格是第一个字符之前 的空格,后续空格,是最后一个字符之后的空格
SELECT LTRIM(' MySQL ');
验证一下长度
SELECT LTRIM(' MySQL ');
SELECT LENGTH(LTRIM(' MySQL '));
14去掉后续空格之后的长度
SELECT LENGTH(RTRIM(' MySQL '));
15 去掉所有空格之后的长度
SELECT LENGTH(TRIM(' MySQL '));
16 TRIM 还能删除某些特定的字符串比如:消除前导中的‘?’
SELECT TRIM(LEADING '?' FROM '??MySQL???');
17 消除后续中的‘?’
SELECT TRIM(TRAILING '?' FROM '??MySQL???');
18都删除
SELECT TRIM(BOTH'?' FROM '??MySQL???');
19,注意,中间的删不掉
SELECT TRIM(BOTH'?' FROM '??My????SQL???');
20 可以用字符串替换来去掉中间部分的‘?’,替换为空字符串
SELECT REPLACE('??My??SQL???','?','');
21 这种替换也可以是一对多
SELECT REPLACE('??My??SQL???','?','!#');
22 也可以是多对一
SELECT REPLACE('??My??SQL???','??','!');
23 字符串的截取--从第一位开始,截取两个
SELECT SUBSTRING('MySQL',1,2);
24 如果只说了截取几个,会从某一位取,一直取到结尾
SELECT SUBSTRING('MySQL',3);
25 如果是负值,那么就是倒序截取
SELECT SUBSTRING('MySQL',-2);
26 位置可以为负值,但是长度不能
SELECT SUBSTRING('MySQL',-2,-1);
27,LIKE ,常用。模糊查询,%在前,表示以某个字符开始,%在后,表示以某个字符结束,在中间,表示包括某个字符
SELECT 'MySQL' LIKE 'M%';
28 在表test中查找名字中包括O的记录
SELECT * FROM test WHERE first_name LIKE '%O%';
29 查找 包含%的用户,传统方法出错的
SELECT * FROM test WHERE first_name LIKE '%%%';
30这样,告诉字符串,1后面的不需要再进行通配符解析,直接默认为是标准的%
SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';
(三)数值运算符和函数(10条代码,10个知识点)
名称 | 描述 |
CEIL() | 进一取整 |
DIV() | 整数除法 |
FLOOR() | 舍一取整 |
MOD | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
1 简单的加减乘除,仅仅举一个例子
SELECT 3+4;
2 进一取整
SELECT CEIL(3.01);
3 向下取整
SELECT FLOOR(3.01);
4整数除法
SELECT 3/4;
SELECT 3 DIV 4;
5 MOD 取余数
SELECT 5 MOD 3;
6 对分数取余数
SELECT 5.2 MOD 3;
7 幂运算
SELECT POWER(2,3);
8 四舍五入,保留小数点后两位
SELECT ROUND(3.656,2);
9数字截取
SELECT TRUNCATE(125.89,1);
10 注意截断过程中的负值(意会)
SELECT TRUNCATE(125.89,-1);
(四)比较运算符和函数(5条代码,5个知识点)
名称 | 描述 |
[NOT] | []在范围之内 |
[NOT] | []在列出值范围内 |
IS [NOT] NULL | []为空 |
1 BETWEEN .. AND... 1 为TRUE ,0为FAUSE
SELECT 15 BETWEEN 1 AND 22;
2 NOT BETWEEN .. AND...
SELECT 15 NOT BETWEEN 1 AND 22;
3 IN ,NOT IN ,在列出的几个点之内
SELECT 10 IN (5,10,15,20);
4 严格的 IS [NOT ]NULL
SELECT NULL IS NULL;只有这个为真
SELECT 'NULL' IS NULL;假
SELECT 0 IS NULL;假
5 在test 表中查找first_name为NULL,或者不为NULL的字段(可惜实验结果和期望不对)
SELECT* FROM test WHERE first_name IS NULL;
SELECT* FROM test WHERE first_name IS NOT NULL;
(五)日期时间函数(6条代码,6个知识点)
名称 | 描述 |
NOW() | 当前日期和时间 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
1 NOW 打印当前日期和时间
SELECT NOW();
2 当前日期
SELECT CURDATE();
3 当前时间
SELECT CURTIME();
4 日期的变化
增加 年 SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);
月 SELECT DATE_ADD('2014-3-12',INTERVAL 1 WEEK);
日 SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
减少
SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);
5 日期时间的差值
SELECT DATEDIFF('2013-3-12','2014-3-12');
6 日期格式化
SELECT DATE_FORMAT('2014-3-2','%m%d%Y');
SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
(六)信息函数(6条代码,6个知识点)
名称 | 描述 |
CONNECTION_ID() | 连接ID |
DATEBASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID号 |
USER() | 当前用户 |
VERSION() | 版本信息 |
1 显示连接ID号
SELECT CONNECTION_ID();
2 显示当前数据库
SELECT DATABASE();
3 查询最后插入字段的Id
增加一列,包括ID, ALTER TABLE test ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;
插入记录 INSERT test(first_name,last_name) VALUES('11','22');
查询 SELECT LAST_INSERT_ID();
4 如果同时写入了多条记录,,会显示哪个id?
插入 INSERT test(first_name,last_name) VALUES('AA','BB'),('CC','DD');
查询SELECT LAST_INSERT_ID();
5 打印当前用户
SELECT USER();
6 打印版本信息
SELECT VERSION();
(七)聚合函数(5条代码,5个知识点)
名称 | 描述 |
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
1 求平均值
SELECT AVG(id) FROM test;
四舍五入,查询的结果再起个别名
SELECT ROUND(AVG(goods_price),3) AS avg_price FROM tdb_goods;
2 COUNT计数
SELECT COUNT(id) FROM test;
SELECT COUNT(id) AS counts FROM test;
3求最大值
SELECT MAX(id) FROM test;
SELECT MAX(id) AS counts FROM test;
4 求最小值
SELECT MIN(id) FROM test;
SELECT MIN(id) AS counts FROM test;
5 求和
SELECT SUM(id) FROM test;
SELECT SUM(id) AS counts FROM test;
(八) 加密函数(2条代码,2个知识点)
名称 | 描述 |
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
1 为WEB 界面加密,尽量用MD5()
SELECT MD5('admin');
2 修改 客户端自己密码的时候,用PASSWORD();
SELECT PASSWORD('admin');
SELECT PASSWORD=PASSWORD('123456'); // (附注:此法未成功,实验过程中报错)
第七章 自定义函数
(一)回顾和概述
字符函数
内置函数库 数值函数
日期时间函数
加密函数
(二)自定义函数简介
1 自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
2 自定义函数的两个必要条件:(1)参数(2)返回值
3 函数可以返回任意类型的值,同样可以接收这些类型的参数
4 创建自定义函数
CREATE FUNCTION function_name
RETURNS
routine_body
5 关于函数体
(1)函数体由合法的SQL语句构成
(2)函数体keys简单的SELECT或INSERT语句
(3)函数体如果为符合结构则使用BEGIN...END语句
(4)复合结构包括声明,循环,控制结构
(三)创建不带参数的自定义函数(2条代码,2个知识点)
1 创建
DATE_FORMAT()格式化
先做下验证
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H:%i分:%s秒');
将这个功能封装成函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日%H点:%i分:%s秒');
2 调用 函数f1();
SELECT f1();
(四)创建带参数的自定义函数(3条代码,3个知识点)
1 创建一个函数,计算两个数的平均值
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2)UNSIGNED
-> RETURN (num1+num2)/2;
2 调用函数f2(),求10,15的平均值
SELECT f2(10,15);
3 查看指定数据库下所有函数
公式:select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION'
在这里: select `name` from mysql.proc where db = 'goods1101' and `type` = 'FUNCTION';
(五)创建具有符合结构的自定义函数(3条代码,3个知识点)
先创建一张表,准备一些数据作为测试。
建表
CREATE TABLE testnow(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) DEFAULT NULL
);
插入数据
INSERT testnow VALUES(NULL,'John'),(NULL,'111');
1 DELIMITER 用于修改定界符。DELIMITER //表示,必须当出现//才代表结束
如果有多个语句需要执行,就要有begin,end构成聚合体
下面创建一个函数,可以直接把名字插入到testnow表中,并且返回新插入字段的id
DELIMITER //
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT testnow(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
创建完毕,改回定界符
DELIMITER ;
2 调用函数,把’TOM插入进去,并知悉其id号
SELECT adduser('TOM');
3 删除函数
公式: DROP FUNCTION [IF EXISTS] function_name;
在这里:DROP FUNCTION IF EXISTS f1;
第八章 存储过程
(一) 课程回顾
1 自定义函数:简称UDF;是对MySQL扩展的一种途径
2 创建自定义函数:CREATE FUNCTION......
3创建自定义函数的两个必要条件:
参数:可以有零个或多个
返回值:只能有一个返回值
具有复合结构等等函数体需要使用BEGIN...END来包含
(二) 存储过程简介
分析 执行 返回
SQL 命令 --->MySQL 存储引擎 ---------------> 语法正确--->可识别命令------> 执行结果------->客户端
2 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
3存储过程的优点:
A. 增强SQL语句的功能和灵活性
B. 实现较快的执行速度
C.减少网络流量
(三) 存储过程语法解法
1 参数
A IN,表示该参数的值必须在调用存储过程时指定
B OUT,表示改存储过程的值可以被存储过程改变,并且可以返回
C INOUT,表示该参数被调用时指定,并且可以被改变和返回
2 特性
A COMMENT:注释
B CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
C NO SQL:不包含SQL的语句
D READS SQL DATA:包含读数据的语句
E MODIFIES SQL DATA:包含写数据的语句
F SQL SECURITY{DEFINER|INVOKER}指定谁有权限来执行
3 过程体
A 过程体由合法的SQL语句构成
B 过程体可以是任意SQL语句
C 过程体如果为复合结构则使用BEGIN...END语句;
D 复合结构可以包含声明,循环,控制结构
(四)创建不带参数的存储过程(3条代码,3个知识点)
1 创建一个存储过程,打印当前数据库版本信息
CREATE PROCEDURE sp1() SELECT VERSION();
2 查看当前数据库下面所有的存储过程
公式: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE';
在这里: SELECT `name` FROM mysql.proc WHERE db = 'goods1101' and `type` = 'PROCEDURE';
3 调用
CALL sp1;
CALL sp1();
注意,如果有参数的话,小括号就不能省略
(五)创建带有IN类型 的存储过程(3条代码,3个知识点)
先准备一张表,实验用
CREATE TABLE users(
id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT(3) UNSIGNED NOT NULL DEFAULT 10,
sex TINYINT(1) NOT NULL DEFAULT 0
);
INSERT users VALUES(NULL,'John','123456',22,1);
INSERT users VALUES(NULL,'卡卡','123456',22,1);
INSERT users VALUES(NULL,'梅西','123456',22,1);
INSERT users VALUES(NULL,'舍甫琴科','123456',22,1);
INSERT users VALUES(NULL,'特雷泽盖','123456',22,1);
INSERT users VALUES(NULL,'因扎吉','123456',22,1);
INSERT users VALUES(NULL,'劳尔','123456',22,1);
INSERT users VALUES(NULL,'皮耶罗','123456',22,1);
INSERT users VALUES(NULL,'罗纳尔迪尼奥','123456',22,1);
INSERT users VALUES(NULL,'劳德鲁普','123456',20,1);
1 写一个存储过程,根据ID号删除记录
DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
END
//
改回标识符
DELIMITER ;
2 调用存储过程
CALL removeUserById(7);
3 删除存储过程
DROP PROCEDURE removeUserById;
(七)创建带有多个OUT类型,参数的存储过程
封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
新建一张表作为测试用
CREATE TABLE testseven(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
1 引进一个函数
ROW_COUNT(),被影响到的行数
插入数据
INSERT testseven(username) VALUES('A'),('B'),('C');
调用ROW_COUNT();
SELECT ROW_COUNT();
改变前两条记录的用户名
UPDATE testseven SET username =CONCAT(username,'--mysql')WHERE id<=2;
调用ROW_COUNT();
SELECT ROW_COUNT();
2封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED ,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED )
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id)FROM users INTO userCounts;
END
//
DELIMITER ;
3 调用
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
4 查看
SELECT @a,@b;
第六章 运算符和函数
(一)概述
1 字符函数
2 数值运算符和函数
3 比较运算符和函数
4 日期时间函数
5 信息函数
6 聚合函数
7 加密函数
(二)字符函数(30条代码,7个知识点)
函数名称 | 描述 |
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换成小写字母 |
UPPER() | 转换成大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
1 使用CONCAT()连接两个字符
SELECT CONCAT ('goods','mysql');
连接三个字符
SELECT CONCAT ('goods','-','mysql');
2 弄张表,做演示用
CREATE TABLE test(
first_name VARCHAR(20) DEFAULT NULL,
last_name VARCHAR(10) DEFAULT NULL
);
INSERT test VALUES('A','B'),('C','D'),('tom%','123'),('NULL','11');
3 将这两个字段合并在一起输出,AS起别名
SELECT CONCAT(first_name,last_name) AS fullname FROM test;
4 CONCAT_WS()需要至少三个参数
SELECT CONCAT_WS('|','A','B','C');
5 自动四舍五入
SELECT FORMAT(12580.561,2);
6 大写变小写
SELECT LOWER('MySQL');
7 小写变大写
SELECT UPPER('mysql');
8获取左侧N个字符
SELECT LEFT('MySQL',2);
9将获取到的字符转化为小写,需要嵌套
SELECT LOWER(LEFT('MySQL',2));
10 获取右侧N个字符
SELECT RIGHT('MySQL',2);
名称 | 描述 |
LENGTH() | 获取字符串长度 |
LTRIM() | 删除前导空格 |
RTRIM() | 删除后续空格 |
TRIM() | 删除前导和后续空格 |
SUBSTRING() | 字符串截取 |
[NOT] LIKE | 模式匹配 |
REPLACE() | 字符串替换 |
11 获取字符串的长度
SELECT LENGTH('MySQL');
12 如果由空格的话
SELECT LENGTH('My SQL');
13先删除前导空格,前导空格是第一个字符之前 的空格,后续空格,是最后一个字符之后的空格
SELECT LTRIM(' MySQL ');
验证一下长度
SELECT LTRIM(' MySQL ');
SELECT LENGTH(LTRIM(' MySQL '));
14去掉后续空格之后的长度
SELECT LENGTH(RTRIM(' MySQL '));
15 去掉所有空格之后的长度
SELECT LENGTH(TRIM(' MySQL '));
16 TRIM 还能删除某些特定的字符串比如:消除前导中的‘?’
SELECT TRIM(LEADING '?' FROM '??MySQL???');
17 消除后续中的‘?’
SELECT TRIM(TRAILING '?' FROM '??MySQL???');
18都删除
SELECT TRIM(BOTH'?' FROM '??MySQL???');
19,注意,中间的删不掉
SELECT TRIM(BOTH'?' FROM '??My????SQL???');
20 可以用字符串替换来去掉中间部分的‘?’,替换为空字符串
SELECT REPLACE('??My??SQL???','?','');
21 这种替换也可以是一对多
SELECT REPLACE('??My??SQL???','?','!#');
22 也可以是多对一
SELECT REPLACE('??My??SQL???','??','!');
23 字符串的截取--从第一位开始,截取两个
SELECT SUBSTRING('MySQL',1,2);
24 如果只说了截取几个,会从某一位取,一直取到结尾
SELECT SUBSTRING('MySQL',3);
25 如果是负值,那么就是倒序截取
SELECT SUBSTRING('MySQL',-2);
26 位置可以为负值,但是长度不能
SELECT SUBSTRING('MySQL',-2,-1);
27,LIKE ,常用。模糊查询,%在前,表示以某个字符开始,%在后,表示以某个字符结束,在中间,表示包括某个字符
SELECT 'MySQL' LIKE 'M%';
28 在表test中查找名字中包括O的记录
SELECT * FROM test WHERE first_name LIKE '%O%';
29 查找 包含%的用户,传统方法出错的
SELECT * FROM test WHERE first_name LIKE '%%%';
30这样,告诉字符串,1后面的不需要再进行通配符解析,直接默认为是标准的%
SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';
(三)数值运算符和函数(10条代码,10个知识点)
名称 | 描述 |
CEIL() | 进一取整 |
DIV() | 整数除法 |
FLOOR() | 舍一取整 |
MOD | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
1 简单的加减乘除,仅仅举一个例子
SELECT 3+4;
2 进一取整
SELECT CEIL(3.01);
3 向下取整
SELECT FLOOR(3.01);
4整数除法
SELECT 3/4;
SELECT 3 DIV 4;
5 MOD 取余数
SELECT 5 MOD 3;
6 对分数取余数
SELECT 5.2 MOD 3;
7 幂运算
SELECT POWER(2,3);
8 四舍五入,保留小数点后两位
SELECT ROUND(3.656,2);
9数字截取
SELECT TRUNCATE(125.89,1);
10 注意截断过程中的负值(意会)
SELECT TRUNCATE(125.89,-1);
(四)比较运算符和函数(5条代码,5个知识点)
名称 | 描述 |
[NOT] | []在范围之内 |
[NOT] | []在列出值范围内 |
IS [NOT] NULL | []为空 |
1 BETWEEN .. AND... 1 为TRUE ,0为FAUSE
SELECT 15 BETWEEN 1 AND 22;
2 NOT BETWEEN .. AND...
SELECT 15 NOT BETWEEN 1 AND 22;
3 IN ,NOT IN ,在列出的几个点之内
SELECT 10 IN (5,10,15,20);
4 严格的 IS [NOT ]NULL
SELECT NULL IS NULL;只有这个为真
SELECT 'NULL' IS NULL;假
SELECT 0 IS NULL;假
5 在test 表中查找first_name为NULL,或者不为NULL的字段(可惜实验结果和期望不对)
SELECT* FROM test WHERE first_name IS NULL;
SELECT* FROM test WHERE first_name IS NOT NULL;
(五)日期时间函数(6条代码,6个知识点)
名称 | 描述 |
NOW() | 当前日期和时间 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
1 NOW 打印当前日期和时间
SELECT NOW();
2 当前日期
SELECT CURDATE();
3 当前时间
SELECT CURTIME();
4 日期的变化
增加 年 SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);
月 SELECT DATE_ADD('2014-3-12',INTERVAL 1 WEEK);
日 SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
减少
SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);
5 日期时间的差值
SELECT DATEDIFF('2013-3-12','2014-3-12');
6 日期格式化
SELECT DATE_FORMAT('2014-3-2','%m%d%Y');
SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
(六)信息函数(6条代码,6个知识点)
名称 | 描述 |
CONNECTION_ID() | 连接ID |
DATEBASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID号 |
USER() | 当前用户 |
VERSION() | 版本信息 |
1 显示连接ID号
SELECT CONNECTION_ID();
2 显示当前数据库
SELECT DATABASE();
3 查询最后插入字段的Id
增加一列,包括ID, ALTER TABLE test ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;
插入记录 INSERT test(first_name,last_name) VALUES('11','22');
查询 SELECT LAST_INSERT_ID();
4 如果同时写入了多条记录,,会显示哪个id?
插入 INSERT test(first_name,last_name) VALUES('AA','BB'),('CC','DD');
查询SELECT LAST_INSERT_ID();
5 打印当前用户
SELECT USER();
6 打印版本信息
SELECT VERSION();
(七)聚合函数(5条代码,5个知识点)
名称 | 描述 |
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
1 求平均值
SELECT AVG(id) FROM test;
四舍五入,查询的结果再起个别名
SELECT ROUND(AVG(goods_price),3) AS avg_price FROM tdb_goods;
2 COUNT计数
SELECT COUNT(id) FROM test;
SELECT COUNT(id) AS counts FROM test;
3求最大值
SELECT MAX(id) FROM test;
SELECT MAX(id) AS counts FROM test;
4 求最小值
SELECT MIN(id) FROM test;
SELECT MIN(id) AS counts FROM test;
5 求和
SELECT SUM(id) FROM test;
SELECT SUM(id) AS counts FROM test;
(八) 加密函数(2条代码,2个知识点)
名称 | 描述 |
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
1 为WEB 界面加密,尽量用MD5()
SELECT MD5('admin');
2 修改 客户端自己密码的时候,用PASSWORD();
SELECT PASSWORD('admin');
SELECT PASSWORD=PASSWORD('123456'); // (附注:此法未成功,实验过程中报错)
第七章 自定义函数
(一)回顾和概述
字符函数
内置函数库 数值函数
日期时间函数
加密函数
(二)自定义函数简介
1 自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
2 自定义函数的两个必要条件:(1)参数(2)返回值
3 函数可以返回任意类型的值,同样可以接收这些类型的参数
4 创建自定义函数
CREATE FUNCTION function_name
RETURNS
routine_body
5 关于函数体
(1)函数体由合法的SQL语句构成
(2)函数体keys简单的SELECT或INSERT语句
(3)函数体如果为符合结构则使用BEGIN...END语句
(4)复合结构包括声明,循环,控制结构
(三)创建不带参数的自定义函数(2条代码,2个知识点)
1 创建
DATE_FORMAT()格式化
先做下验证
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H:%i分:%s秒');
将这个功能封装成函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日%H点:%i分:%s秒');
2 调用 函数f1();
SELECT f1();
(四)创建带参数的自定义函数(3条代码,3个知识点)
1 创建一个函数,计算两个数的平均值
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2)UNSIGNED
-> RETURN (num1+num2)/2;
2 调用函数f2(),求10,15的平均值
SELECT f2(10,15);
3 查看指定数据库下所有函数
公式:select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION'
在这里: select `name` from mysql.proc where db = 'goods1101' and `type` = 'FUNCTION';
(五)创建具有符合结构的自定义函数(3条代码,3个知识点)
先创建一张表,准备一些数据作为测试。
建表
CREATE TABLE testnow(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) DEFAULT NULL
);
插入数据
INSERT testnow VALUES(NULL,'John'),(NULL,'111');
1 DELIMITER 用于修改定界符。DELIMITER //表示,必须当出现//才代表结束
如果有多个语句需要执行,就要有begin,end构成聚合体
下面创建一个函数,可以直接把名字插入到testnow表中,并且返回新插入字段的id
DELIMITER //
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT testnow(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
创建完毕,改回定界符
DELIMITER ;
2 调用函数,把’TOM插入进去,并知悉其id号
SELECT adduser('TOM');
3 删除函数
公式: DROP FUNCTION [IF EXISTS] function_name;
在这里:DROP FUNCTION IF EXISTS f1;
第八章 存储过程
(一) 课程回顾
1 自定义函数:简称UDF;是对MySQL扩展的一种途径
2 创建自定义函数:CREATE FUNCTION......
3创建自定义函数的两个必要条件:
参数:可以有零个或多个
返回值:只能有一个返回值
具有复合结构等等函数体需要使用BEGIN...END来包含
(二) 存储过程简介
分析
返回 执行
2 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
3存储过程的优点:
A. 增强SQL语句的功能和灵活性
B. 实现较快的执行速度
C.减少网络流量
(三) 存储过程语法解法
1 参数
A IN,表示该参数的值必须在调用存储过程时指定
B OUT,表示改存储过程的值可以被存储过程改变,并且可以返回
C INOUT,表示该参数被调用时指定,并且可以被改变和返回
2 特性
A COMMENT:注释
B CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
C NO SQL:不包含SQL的语句
D READS SQL DATA:包含读数据的语句
E MODIFIES SQL DATA:包含写数据的语句
F SQL SECURITY{DEFINER|INVOKER}指定谁有权限来执行
3 过程体
A 过程体由合法的SQL语句构成
B 过程体可以是任意SQL语句
C 过程体如果为复合结构则使用BEGIN...END语句;
D 复合结构可以包含声明,循环,控制结构
(四)创建不带参数的存储过程(3条代码,3个知识点)
1 创建一个存储过程,打印当前数据库版本信息
CREATE PROCEDURE sp1() SELECT VERSION();
2 查看当前数据库下面所有的存储过程
公式: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE';
在这里: SELECT `name` FROM mysql.proc WHERE db = 'goods1101' and `type` = 'PROCEDURE';
3 调用
CALL sp1;
CALL sp1();
注意,如果有参数的话,小括号就不能省略
(五)创建带有IN类型 的存储过程(3条代码,3个知识点)
先准备一张表,实验用
CREATE TABLE users(
id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT(3) UNSIGNED NOT NULL DEFAULT 10,
sex TINYINT(1) NOT NULL DEFAULT 0
);
INSERT users VALUES(NULL,'John','123456',22,1);
INSERT users VALUES(NULL,'卡卡','123456',22,1);
INSERT users VALUES(NULL,'梅西','123456',22,1);
INSERT users VALUES(NULL,'舍甫琴科','123456',22,1);
INSERT users VALUES(NULL,'特雷泽盖','123456',22,1);
INSERT users VALUES(NULL,'因扎吉','123456',22,1);
INSERT users VALUES(NULL,'劳尔','123456',22,1);
INSERT users VALUES(NULL,'皮耶罗','123456',22,1);
INSERT users VALUES(NULL,'罗纳尔迪尼奥','123456',22,1);
INSERT users VALUES(NULL,'劳德鲁普','123456',20,1);
1 写一个存储过程,根据ID号删除记录
DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
END
//
改回标识符
DELIMITER ;
2 调用存储过程
CALL removeUserById(7);
3 删除存储过程
DROP PROCEDURE removeUserById;
(七)创建带有多个OUT类型,参数的存储过程
封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
新建一张表作为测试用
CREATE TABLE testseven(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
1 引进一个函数
ROW_COUNT(),被影响到的行数
插入数据
INSERT testseven(username) VALUES('A'),('B'),('C');
调用ROW_COUNT();
SELECT ROW_COUNT();
改变前两条记录的用户名
UPDATE testseven SET username =CONCAT(username,'--mysql')WHERE id<=2;
调用ROW_COUNT();
SELECT ROW_COUNT();
2封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED ,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED )
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id)FROM users INTO userCounts;
END
//
DELIMITER ;
3 调用
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
4 查看
SELECT @a,@b;第六章 运算符和函数
(一)概述
1 字符函数
2 数值运算符和函数
3 比较运算符和函数
4 日期时间函数
5 信息函数
6 聚合函数
7 加密函数
(二)字符函数(30条代码,7个知识点)
函数名称 | 描述 |
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换成小写字母 |
UPPER() | 转换成大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
1 使用CONCAT()连接两个字符
SELECT CONCAT ('goods','mysql');
连接三个字符
SELECT CONCAT ('goods','-','mysql');
2 弄张表,做演示用
CREATE TABLE test(
first_name VARCHAR(20) DEFAULT NULL,
last_name VARCHAR(10) DEFAULT NULL
);
INSERT test VALUES('A','B'),('C','D'),('tom%','123'),('NULL','11');
3 将这两个字段合并在一起输出,AS起别名
SELECT CONCAT(first_name,last_name) AS fullname FROM test;
4 CONCAT_WS()需要至少三个参数
SELECT CONCAT_WS('|','A','B','C');
5 自动四舍五入
SELECT FORMAT(12580.561,2);
6 大写变小写
SELECT LOWER('MySQL');
7 小写变大写
SELECT UPPER('mysql');
8获取左侧N个字符
SELECT LEFT('MySQL',2);
9将获取到的字符转化为小写,需要嵌套
SELECT LOWER(LEFT('MySQL',2));
10 获取右侧N个字符
SELECT RIGHT('MySQL',2);
名称 | 描述 |
LENGTH() | 获取字符串长度 |
LTRIM() | 删除前导空格 |
RTRIM() | 删除后续空格 |
TRIM() | 删除前导和后续空格 |
SUBSTRING() | 字符串截取 |
[NOT] LIKE | 模式匹配 |
REPLACE() | 字符串替换 |
11 获取字符串的长度
SELECT LENGTH('MySQL');
12 如果由空格的话
SELECT LENGTH('My SQL');
13先删除前导空格,前导空格是第一个字符之前 的空格,后续空格,是最后一个字符之后的空格
SELECT LTRIM(' MySQL ');
验证一下长度
SELECT LTRIM(' MySQL ');
SELECT LENGTH(LTRIM(' MySQL '));
14去掉后续空格之后的长度
SELECT LENGTH(RTRIM(' MySQL '));
15 去掉所有空格之后的长度
SELECT LENGTH(TRIM(' MySQL '));
16 TRIM 还能删除某些特定的字符串比如:消除前导中的‘?’
SELECT TRIM(LEADING '?' FROM '??MySQL???');
17 消除后续中的‘?’
SELECT TRIM(TRAILING '?' FROM '??MySQL???');
18都删除
SELECT TRIM(BOTH'?' FROM '??MySQL???');
19,注意,中间的删不掉
SELECT TRIM(BOTH'?' FROM '??My????SQL???');
20 可以用字符串替换来去掉中间部分的‘?’,替换为空字符串
SELECT REPLACE('??My??SQL???','?','');
21 这种替换也可以是一对多
SELECT REPLACE('??My??SQL???','?','!#');
22 也可以是多对一
SELECT REPLACE('??My??SQL???','??','!');
23 字符串的截取--从第一位开始,截取两个
SELECT SUBSTRING('MySQL',1,2);
24 如果只说了截取几个,会从某一位取,一直取到结尾
SELECT SUBSTRING('MySQL',3);
25 如果是负值,那么就是倒序截取
SELECT SUBSTRING('MySQL',-2);
26 位置可以为负值,但是长度不能
SELECT SUBSTRING('MySQL',-2,-1);
27,LIKE ,常用。模糊查询,%在前,表示以某个字符开始,%在后,表示以某个字符结束,在中间,表示包括某个字符
SELECT 'MySQL' LIKE 'M%';
28 在表test中查找名字中包括O的记录
SELECT * FROM test WHERE first_name LIKE '%O%';
29 查找 包含%的用户,传统方法出错的
SELECT * FROM test WHERE first_name LIKE '%%%';
30这样,告诉字符串,1后面的不需要再进行通配符解析,直接默认为是标准的%
SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';
(三)数值运算符和函数(10条代码,10个知识点)
名称 | 描述 |
CEIL() | 进一取整 |
DIV() | 整数除法 |
FLOOR() | 舍一取整 |
MOD | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
1 简单的加减乘除,仅仅举一个例子
SELECT 3+4;
2 进一取整
SELECT CEIL(3.01);
3 向下取整
SELECT FLOOR(3.01);
4整数除法
SELECT 3/4;
SELECT 3 DIV 4;
5 MOD 取余数
SELECT 5 MOD 3;
6 对分数取余数
SELECT 5.2 MOD 3;
7 幂运算
SELECT POWER(2,3);
8 四舍五入,保留小数点后两位
SELECT ROUND(3.656,2);
9数字截取
SELECT TRUNCATE(125.89,1);
10 注意截断过程中的负值(意会)
SELECT TRUNCATE(125.89,-1);
(四)比较运算符和函数(5条代码,5个知识点)
名称 | 描述 |
[NOT] | []在范围之内 |
[NOT] | []在列出值范围内 |
IS [NOT] NULL | []为空 |
1 BETWEEN .. AND... 1 为TRUE ,0为FAUSE
SELECT 15 BETWEEN 1 AND 22;
2 NOT BETWEEN .. AND...
SELECT 15 NOT BETWEEN 1 AND 22;
3 IN ,NOT IN ,在列出的几个点之内
SELECT 10 IN (5,10,15,20);
4 严格的 IS [NOT ]NULL
SELECT NULL IS NULL;只有这个为真
SELECT 'NULL' IS NULL;假
SELECT 0 IS NULL;假
5 在test 表中查找first_name为NULL,或者不为NULL的字段(可惜实验结果和期望不对)
SELECT* FROM test WHERE first_name IS NULL;
SELECT* FROM test WHERE first_name IS NOT NULL;
(五)日期时间函数(6条代码,6个知识点)
名称 | 描述 |
NOW() | 当前日期和时间 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
1 NOW 打印当前日期和时间
SELECT NOW();
2 当前日期
SELECT CURDATE();
3 当前时间
SELECT CURTIME();
4 日期的变化
增加 年 SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);
月 SELECT DATE_ADD('2014-3-12',INTERVAL 1 WEEK);
日 SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
减少
SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);
5 日期时间的差值
SELECT DATEDIFF('2013-3-12','2014-3-12');
6 日期格式化
SELECT DATE_FORMAT('2014-3-2','%m%d%Y');
SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
(六)信息函数(6条代码,6个知识点)
名称 | 描述 |
CONNECTION_ID() | 连接ID |
DATEBASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID号 |
USER() | 当前用户 |
VERSION() | 版本信息 |
1 显示连接ID号
SELECT CONNECTION_ID();
2 显示当前数据库
SELECT DATABASE();
3 查询最后插入字段的Id
增加一列,包括ID, ALTER TABLE test ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;
插入记录 INSERT test(first_name,last_name) VALUES('11','22');
查询 SELECT LAST_INSERT_ID();
4 如果同时写入了多条记录,,会显示哪个id?
插入 INSERT test(first_name,last_name) VALUES('AA','BB'),('CC','DD');
查询SELECT LAST_INSERT_ID();
5 打印当前用户
SELECT USER();
6 打印版本信息
SELECT VERSION();
(七)聚合函数(5条代码,5个知识点)
名称 | 描述 |
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
1 求平均值
SELECT AVG(id) FROM test;
四舍五入,查询的结果再起个别名
SELECT ROUND(AVG(goods_price),3) AS avg_price FROM tdb_goods;
2 COUNT计数
SELECT COUNT(id) FROM test;
SELECT COUNT(id) AS counts FROM test;
3求最大值
SELECT MAX(id) FROM test;
SELECT MAX(id) AS counts FROM test;
4 求最小值
SELECT MIN(id) FROM test;
SELECT MIN(id) AS counts FROM test;
5 求和
SELECT SUM(id) FROM test;
SELECT SUM(id) AS counts FROM test;
(八) 加密函数(2条代码,2个知识点)
名称 | 描述 |
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
1 为WEB 界面加密,尽量用MD5()
SELECT MD5('admin');
2 修改 客户端自己密码的时候,用PASSWORD();
SELECT PASSWORD('admin');
SELECT PASSWORD=PASSWORD('123456'); // (附注:此法未成功,实验过程中报错)
第七章 自定义函数
(一)回顾和概述
字符函数
内置函数库 数值函数
日期时间函数
加密函数
(二)自定义函数简介
1 自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
2 自定义函数的两个必要条件:(1)参数(2)返回值
3 函数可以返回任意类型的值,同样可以接收这些类型的参数
4 创建自定义函数
CREATE FUNCTION function_name
RETURNS
routine_body
5 关于函数体
(1)函数体由合法的SQL语句构成
(2)函数体keys简单的SELECT或INSERT语句
(3)函数体如果为符合结构则使用BEGIN...END语句
(4)复合结构包括声明,循环,控制结构
(三)创建不带参数的自定义函数(2条代码,2个知识点)
1 创建
DATE_FORMAT()格式化
先做下验证
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H:%i分:%s秒');
将这个功能封装成函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日%H点:%i分:%s秒');
2 调用 函数f1();
SELECT f1();
(四)创建带参数的自定义函数(3条代码,3个知识点)
1 创建一个函数,计算两个数的平均值
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2)UNSIGNED
-> RETURN (num1+num2)/2;
2 调用函数f2(),求10,15的平均值
SELECT f2(10,15);
3 查看指定数据库下所有函数
公式:select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION'
在这里: select `name` from mysql.proc where db = 'goods1101' and `type` = 'FUNCTION';
(五)创建具有符合结构的自定义函数(3条代码,3个知识点)
先创建一张表,准备一些数据作为测试。
建表
CREATE TABLE testnow(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) DEFAULT NULL
);
插入数据
INSERT testnow VALUES(NULL,'John'),(NULL,'111');
1 DELIMITER 用于修改定界符。DELIMITER //表示,必须当出现//才代表结束
如果有多个语句需要执行,就要有begin,end构成聚合体
下面创建一个函数,可以直接把名字插入到testnow表中,并且返回新插入字段的id
DELIMITER //
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT testnow(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
创建完毕,改回定界符
DELIMITER ;
2 调用函数,把’TOM插入进去,并知悉其id号
SELECT adduser('TOM');
3 删除函数
公式: DROP FUNCTION [IF EXISTS] function_name;
在这里:DROP FUNCTION IF EXISTS f1;
第八章 存储过程
(一) 课程回顾
1 自定义函数:简称UDF;是对MySQL扩展的一种途径
2 创建自定义函数:CREATE FUNCTION......
3创建自定义函数的两个必要条件:
参数:可以有零个或多个
返回值:只能有一个返回值
具有复合结构等等函数体需要使用BEGIN...END来包含
(二) 存储过程简介
分析
返回 执行
2 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
3存储过程的优点:
A. 增强SQL语句的功能和灵活性
B. 实现较快的执行速度
C.减少网络流量
(三) 存储过程语法解法
1 参数
A IN,表示该参数的值必须在调用存储过程时指定
B OUT,表示改存储过程的值可以被存储过程改变,并且可以返回
C INOUT,表示该参数被调用时指定,并且可以被改变和返回
2 特性
A COMMENT:注释
B CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
C NO SQL:不包含SQL的语句
D READS SQL DATA:包含读数据的语句
E MODIFIES SQL DATA:包含写数据的语句
F SQL SECURITY{DEFINER|INVOKER}指定谁有权限来执行
3 过程体
A 过程体由合法的SQL语句构成
B 过程体可以是任意SQL语句
C 过程体如果为复合结构则使用BEGIN...END语句;
D 复合结构可以包含声明,循环,控制结构
(四)创建不带参数的存储过程(3条代码,3个知识点)
1 创建一个存储过程,打印当前数据库版本信息
CREATE PROCEDURE sp1() SELECT VERSION();
2 查看当前数据库下面所有的存储过程
公式: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE';
在这里: SELECT `name` FROM mysql.proc WHERE db = 'goods1101' and `type` = 'PROCEDURE';
3 调用
CALL sp1;
CALL sp1();
注意,如果有参数的话,小括号就不能省略
(五)创建带有IN类型 的存储过程(3条代码,3个知识点)
先准备一张表,实验用
CREATE TABLE users(
id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT(3) UNSIGNED NOT NULL DEFAULT 10,
sex TINYINT(1) NOT NULL DEFAULT 0
);
INSERT users VALUES(NULL,'John','123456',22,1);
INSERT users VALUES(NULL,'卡卡','123456',22,1);
INSERT users VALUES(NULL,'梅西','123456',22,1);
INSERT users VALUES(NULL,'舍甫琴科','123456',22,1);
INSERT users VALUES(NULL,'特雷泽盖','123456',22,1);
INSERT users VALUES(NULL,'因扎吉','123456',22,1);
INSERT users VALUES(NULL,'劳尔','123456',22,1);
INSERT users VALUES(NULL,'皮耶罗','123456',22,1);
INSERT users VALUES(NULL,'罗纳尔迪尼奥','123456',22,1);
INSERT users VALUES(NULL,'劳德鲁普','123456',20,1);
1 写一个存储过程,根据ID号删除记录
DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
END
//
改回标识符
DELIMITER ;
2 调用存储过程
CALL removeUserById(7);
3 删除存储过程
DROP PROCEDURE removeUserById;
(七)创建带有多个OUT类型,参数的存储过程
封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
新建一张表作为测试用
CREATE TABLE testseven(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
1 引进一个函数
ROW_COUNT(),被影响到的行数
插入数据
INSERT testseven(username) VALUES('A'),('B'),('C');
调用ROW_COUNT();
SELECT ROW_COUNT();
改变前两条记录的用户名
UPDATE testseven SET username =CONCAT(username,'--mysql')WHERE id<=2;
调用ROW_COUNT();
SELECT ROW_COUNT();
2封装一个函数:根据年龄来删除用户,但是可以返回被删除的用户数和剩余的用户数
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED ,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED )
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id)FROM users INTO userCounts;
END
//
DELIMITER ;
3 调用
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
4 查看
SELECT @a,@b;
(九)MySQL存储引擎
(十)图形化管理工具
三种:PHPMyAdmin
Navicat
MySQL Workbench
推荐使用Navacat,