mysql/nosql | shwy的吐槽圣地

分类目录

9月 14

记一次链接远程数据的问题。

本文由 shwy 发表于 2017年9月14日

mysql/nosql

原文，https://segmentfault.com/q/1010000011165688 提示的问题。

辅助文章： http://www.jb51.net/article/58096.htm

https://dev.mysql.com/doc/refman/5.6/en/connection-access.html

最后通过查询mysql->user表发现，就是用户名相同，host主机来源不同，所使用的密码也是不相同，这是个天坑

8月 26

MySQL中tinytext、text、mediumtext和longtext详解

本文由 shwy 发表于 2017年8月26日

mysql/nosql

一、字符串类型

类型	范围	说明
Char(N) [ binary]	N=1~255 个字节 binary ：分辨大小写	固定长度	std_name cahr(32) not null
VarChar(N) [binary]	N=1~255 个字节 binary ：分辨大小写	可变长度	std_address varchar(256)
TinyBlob	最大长度255个字节(2^8-1)	Blob (Binary large objects)储存二进位资料，且有分大小写	memo text not null
TinyText	最大长度255个字节(2^8-1)
Blob	最大长度65535个字节(2^16-1)
Text	最大长度65535个字节(2^16-1)
MediumBlob	最大长度 16777215 个字节(2^24-1)
MediumText	最大长度 16777215 个字节(2^24-1
LongBlob	最大长度4294967295个字节 (2^32-1)
LongText	最大长度4294967295个字节 (2^32-1)
Enum	集合最大数目为65535	列举(Enumeration)，Enum单选、Set复选	sex enum(1,0) habby set(‘玩电玩’,’睡觉’,’看电影’,’听音乐’)
Set	集合最大数目为64	列举(Enumeration)，Enum单选、Set复选	sex enum(1,0) habby set(‘玩电玩’,’睡觉’,’看电影’,’听音乐’)

辨別Null与Not Null ：Null为允许储存空值(Null)

二、数字/数值类型

类型	范围	说明	例如
TinyInt[M] [UNSIGNED]	-128~127 UNSIGNED ： 0~255		num tinyint unsigned
SmallInt[M] [UNSIGNED]	-32768~32767 UNSIGNED ：0~ 65535
MediumInt[M] [UNSIGNED]	-8388608~8388607 UNSIGNED ：0~16777215
Int[M] [UNSIGNED]	-2^31~2^31-1 UNSIGNED ： 0~2^32
BigInt[M] [UNSIGNED]	-2^63~2^63-1 UNSIGNED ： 0~2^64
Float [(M,D)]	-3.4E+38~3.4E+38( 约 )	注： M 为长度， D 为小数,Float 4 bytes,Double 8 bytes
Double [(M,D)]	-1.79E+308~1.79E+308( 约 )
Decimal [(M,D)]

辨別ZeroFill：当宣告关键字ZeroFill为自动填满0，如 000021

三、日期时间类型

类型	范围	说明
Date	日期(yyyy-mm-dd)
Time	时间(hh:mm:ss)
DateTime	日期与时间組合(yyyy-mm-dd hh:mm:ss)
TimeStamp	yyyymmddhhmmss
Year	年份yyyy

7月 04

远程连接mysql数据

本文由 shwy 发表于 2017年7月4日

mysql/nosql

看了网上很多方式都是复制粘贴的,步骤都漏了, 我这里补充一下

2、授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。

这里前提先 use user表. 才能执行,否则报错

Error (1133): Can’t find any matching row in the user table

GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’192.168.1.3’IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;
我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES
使修改生效，就可以了
第三种方法：
在安装mysql的机器上运行：
1、d:\mysql\bin\>mysql -h localhost -uroot

//这样应该可以进入MySQL服务器

2、use user;

//使用user表再继续操作

3、mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
//赋予任何主机访问数据的权限并使用root密码登录
4、mysql>FLUSH PRIVILEGES
//修改生效
5、mysql>EXIT
//退出MySQL服务器
这样就可以在其它任何的主机上以root身份登录啦。

2月 06

php操作MongoDB常用命令

本文由 shwy 发表于 2017年2月6日

mysql/nosql

转载
如：如果你想创建一个“myTest”的数据库，先运行use myTest命令，之后就做一些操作（如：db.createCollection(‘user’)）,这样就可以创建一个名叫“myTest”的数据库。

一、数据库常用命令
1、Help查看命令提示
复制代码代码如下:
help
db.help();
db.yourColl.help();
db.youColl.find().help();
rs.help();

2、切换/创建数据库
复制代码代码如下:
use yourDB; 当创建一个集合(table)的时候会自动创建当前数据库

3、查询所有数据库
复制代码代码如下:
show dbs;

4、删除当前使用数据库
复制代码代码如下:
db.dropDatabase();

5、从指定主机上克隆数据库
复制代码代码如下:
db.cloneDatabase(“127.0.0.1”); 将指定机器上的数据库的数据克隆到当前数据库

6、从指定的机器上复制指定数据库数据到某个数据库
复制代码代码如下:
db.copyDatabase(“mydb”, “temp”, “127.0.0.1”);将本机的mydb的数据复制到temp数据库中

7、修复当前数据库
复制代码代码如下:
db.repairDatabase();

8、查看当前使用的数据库
复制代码代码如下:
db.getName();
db; db和getName方法是一样的效果，都可以查询当前使用的数据库

9、显示当前db状态
复制代码代码如下:
db.stats();

10、当前db版本
复制代码代码如下:
db.version();

11、查看当前db的链接机器地址
复制代码代码如下:
db.getMongo();
二、Collection聚集集合
1、创建一个聚集集合（table）
复制代码代码如下:
db.createCollection(“collName”, {size: 20, capped: 5, max: 100});//创建成功会显示{“ok”:1}
//判断集合是否为定容量db.collName.isCapped();

2、得到指定名称的聚集集合（table）
复制代码代码如下:
db.getCollection(“account”);

3、得到当前db的所有聚集集合
复制代码代码如下:
db.getCollectionNames();

4、显示当前db所有聚集索引的状态
复制代码代码如下:
db.printCollectionStats();

三、用户相关
1、添加一个用户
复制代码代码如下:
db.addUser(“name”);
db.addUser(“userName”, “pwd123”, true); 添加用户、设置密码、是否只读

2、数据库认证、安全模式
复制代码代码如下:
db.auth(“userName”, “123123”);

3、显示当前所有用户
复制代码代码如下:
show users;

4、删除用户
复制代码代码如下:
db.removeUser(“userName”);

四、聚集集合查询
1、查询所有记录
复制代码代码如下:
db.userInfo.find();
相当于：select* from userInfo;

默认每页显示20条记录，当显示不下的情况下，可以用it迭代命令查询下一页数据。注意：键入it命令不能带“；”
但是你可以设置每页显示数据的大小，用DBQuery.shellBatchSize= 50;这样每页就显示50条记录了。
2、查询去掉后的当前聚集集合中的某列的重复数据
复制代码代码如下:
db.userInfo.distinct(“name”);
会过滤掉name中的相同数据
相当于：select distict name from userInfo;

3、查询age = 22的记录
复制代码代码如下:
db.userInfo.find({“age”: 22});
相当于： select * from userInfo where age = 22;

4、查询age > 22的记录
复制代码代码如下:
db.userInfo.find({age: {$gt: 22}});
相当于：select * from userInfo where age >22;

5、查询age < 22的记录复制代码代码如下: db.userInfo.find({age: {$lt: 22}}); 相当于：select * from userInfo where age <22; 6、查询age >= 25的记录
复制代码代码如下:
db.userInfo.find({age: {$gte: 25}});
相当于：select * from userInfo where age >= 25;

7、查询age <= 25的记录复制代码代码如下: db.userInfo.find({age: {$lte: 25}}); 8、查询age >= 23 并且 age <= 26 复制代码代码如下: db.userInfo.find({age: {$gte: 23, $lte: 26}}); 9、查询name中包含 mongo的数据复制代码代码如下: db.userInfo.find({name: /mongo/}); //相当于%% [code]select * from userInfo where name like ‘%mongo%'; 10、查询name中以mongo开头的复制代码代码如下: db.userInfo.find({name: /^mongo/}); select * from userInfo where name like ‘mongo%'; 11、查询指定列name、age数据复制代码代码如下: db.userInfo.find({}, {name: 1, age: 1}); 相当于：select name, age from userInfo; 当然name也可以用true或false,当用ture的情况下河name:1效果一样，如果用false就是排除name，显示name以外的列信息。 12、查询指定列name、age数据, age > 25
复制代码代码如下:
db.userInfo.find({age: {$gt: 25}}, {name: 1, age: 1});
相当于：select name, age from userInfo where age >25;

13、按照年龄排序
复制代码代码如下:
升序：db.userInfo.find().sort({age: 1});
降序：db.userInfo.find().sort({age: -1});

14、查询name = zhangsan, age = 22的数据
复制代码代码如下:
db.userInfo.find({name: ‘zhangsan’, age: 22});
相当于：select * from userInfo where name = ‘zhangsan’ and age = ‘22′;

15、查询前5条数据
复制代码代码如下:
db.userInfo.find().limit(5);
相当于：selecttop 5 * from userInfo;

16、查询10条以后的数据
复制代码代码如下:
db.userInfo.find().skip(10);
相当于：select * from userInfo where id not in (
selecttop 10 * from userInfo
);

17、查询在5-10之间的数据
复制代码代码如下:
db.userInfo.find().limit(10).skip(5);

可用于分页，limit是pageSize，skip是第几页*pageSize
18、or与查询
复制代码代码如下:
db.userInfo.find({$or: [{age: 22}, {age: 25}]});
相当于：select * from userInfo where age = 22 or age = 25;

19、查询第一条数据
复制代码代码如下:
db.userInfo.findOne();
相当于：selecttop 1 * from userInfo;
db.userInfo.find().limit(1);

20、查询某个结果集的记录条数
复制代码代码如下:
db.userInfo.find({age: {$gte: 25}}).count();
相当于：select count(*) from userInfo where age >= 20;

21、按照某列进行排序
复制代码代码如下:
db.userInfo.find({sex: {$exists: true}}).count();
相当于：select count(sex) from userInfo;

五、索引
1、创建索引
复制代码代码如下:
db.userInfo.ensureIndex({name: 1});
db.userInfo.ensureIndex({name: 1, ts: -1});

2、查询当前聚集集合所有索引
复制代码代码如下:
db.userInfo.getIndexes();

3、查看总索引记录大小
复制代码代码如下:
db.userInfo.totalIndexSize();

4、读取当前集合的所有index信息
复制代码代码如下:
db.users.reIndex();

5、删除指定索引
复制代码代码如下:
db.users.dropIndex(“name_1”);

6、删除所有索引索引
复制代码代码如下:
db.users.dropIndexes();

六、修改、添加、删除集合数据
1、添加
复制代码代码如下:
db.users.save({name: ‘zhangsan’, age: 25, sex: true});

添加的数据的数据列，没有固定，根据添加的数据为准
2、修改
复制代码代码如下:
db.users.update({age: 25}, {$set: {name: ‘changeName’}}, false, true);
相当于：update users set name = ‘changeName’ where age = 25;
db.users.update({name: ‘Lisi’}, {$inc: {age: 50}}, false, true);
相当于：update users set age = age + 50 where name = ‘Lisi’;
db.users.update({name: ‘Lisi’}, {$inc: {age: 50}, $set: {name: ‘hoho’}}, false, true);
相当于：update users set age = age + 50, name = ‘hoho’ where name = ‘Lisi’;

3、删除
复制代码代码如下:
db.users.remove({age: 132});

4、查询修改删除
复制代码代码如下:
db.users.findAndModify({
query: {age: {$gte: 25}},
sort: {age: -1},
update: {$set: {name: ‘a2’}, $inc: {age: 2}},
remove: true
});
db.runCommand({ findandmodify : “users”,
query: {age: {$gte: 25}},
sort: {age: -1},
update: {$set: {name: ‘a2’}, $inc: {age: 2}},
remove: true
});

update 或 remove 其中一个是必须的参数; 其他参数可选。
参数详解默认值
query 查询过滤条件 {}
sort 如果多个文档符合查询过滤条件，将以该参数指定的排列方式选择出排在首位的对象，该对象将被操作 {}
remove 若为true，被选中对象将在返回前被删除 N/A
update 一个修改器对象
N/A
new 若为true，将返回修改后的对象而不是原始对象。在删除操作中，该参数被忽略。 false
fields 参见Retrieving a Subset of Fields (1.5.0+)
All fields
upsert 创建新对象若查询结果为空。示例 (1.5.4+)
false
七、语句块操作
1、简单Hello World
复制代码代码如下:
print(“Hello World!”);

这种写法调用了print函数，和直接写入”Hello World!”的效果是一样的；
2、将一个对象转换成json
复制代码代码如下:
tojson(new Object());
tojson(new Object(‘a’));

3、循环添加数据
复制代码代码如下:
> for (var i = 0; i < 30; i++) { ... db.users.save({name: "u_" + i, age: 22 + i, sex: i % 2}); ... }; 这样就循环添加了30条数据，同样也可以省略括号的写法复制代码代码如下: > for (var i = 0; i < 30; i++) db.users.save({name: "u_" + i, age: 22 + i, sex: i % 2}); 也是可以的，当你用db.users.find()查询的时候，显示多条数据而无法一页显示的情况下，可以用it查看下一页的信息； 4、find 游标查询复制代码代码如下: >var cursor = db.users.find();
> while (cursor.hasNext()) {
printjson(cursor.next());
}

这样就查询所有的users信息，同样可以这样写
复制代码代码如下:
var cursor = db.users.find();
while (cursor.hasNext()) { printjson(cursor.next); }

同样可以省略{}号
5、forEach迭代循环
复制代码代码如下:
db.users.find().forEach(printjson);

forEach中必须传递一个函数来处理每条迭代的数据信息
6、将find游标当数组处理
复制代码代码如下:
var cursor = db.users.find();
cursor[4];

取得下标索引为4的那条数据
既然可以当做数组处理，那么就可以获得它的长度：cursor.length();或者cursor.count();
那样我们也可以用循环显示数据
复制代码代码如下:
for (var i = 0, len = c.length(); i < len; i++) printjson(c[i]); 7、将find游标转换成数组复制代码代码如下: > var arr = db.users.find().toArray();
> printjson(arr[2]);

用toArray方法将其转换为数组
8、定制我们自己的查询结果
只显示age <= 28的并且只显示age这列数据复制代码代码如下: db.users.find({age: {$lte: 28}}, {age: 1}).forEach(printjson); db.users.find({age: {$lte: 28}}, {age: true}).forEach(printjson); 排除age的列复制代码代码如下: db.users.find({age: {$lte: 28}}, {age: false}).forEach(printjson); 9、forEach传递函数显示信息复制代码代码如下: db.things.find({x:4}).forEach(function(x) {print(tojson(x));}); 八、其他 1、查询之前的错误信息复制代码代码如下: db.getPrevError(); 2、清除错误记录复制代码代码如下: db.resetError(); 查看聚集集合基本信息 1、查看帮助 db.yourColl.help(); 2、查询当前集合的数据条数 db.yourColl.count(); 3、查看数据空间大小 db.userInfo.dataSize(); 4、得到当前聚集集合所在的db db.userInfo.getDB(); 5、得到当前聚集的状态 db.userInfo.stats(); 6、得到聚集集合总大小 db.userInfo.totalSize(); 7、聚集集合储存空间大小 db.userInfo.storageSize(); 8、Shard版本信息 db.userInfo.getShardVersion() 9、聚集集合重命名 db.userInfo.renameCollection("users"); 将userInfo重命名为users 10、删除当前聚集集合 db.userInfo.drop(); 复制代码代码如下: show dbs:显示数据库列表 show collections：显示当前数据库中的集合（类似关系数据库中的表） show users：显示用户 use ：切换当前数据库，这和MS-SQL里面的意思一样
db.help()：显示数据库操作命令，里面有很多的命令
db.foo.help()：显示集合操作命令，同样有很多的命令，foo指的是当前数据库下，一个叫foo的集合，并非真正意义上的命令
db.foo.find()：对于当前数据库中的foo集合进行数据查找（由于没有条件，会列出所有数据）
db.foo.find( { a : 1 } )：对于当前数据库中的foo集合进行查找，条件是数据中有一个属性叫a，且a的值为1

2月 06

安装MongoDB插件

本文由 shwy 发表于 2017年2月6日

mysql/nosql

1.下载mongodb扩展
下载windows环境下php的mongodb扩展。
http://pecl.php.net/package/mongo
non thread safe (非线程安全)和thread safe（线程安全）区别就在于，
非线程安全一般搭配IIS环境使用，线程安全搭配apache使用。
2.安装mongodb扩展
下载好以后压缩包里有php_mongo.dll文件。将这个文件复制到“E:\web\php\ext”这个路径的文件夹下面。
打开php.ini配置文件，添加
extension=php_mongo.dll
3.让mongodb的扩展找到libsasl.dll依赖库
libsasl.dll是在php根目录下的一个文件夹，mongodb需要依赖这个dll。由于配置环境的时候没有添加php的环境变量，所以在使用php的mongodb扩展的时候，扩展无法找到libsasl.dll的位置导致mongodb的扩展是无法使用的。所以要把 E:\web\php\ 添加到电脑的系统变量里 PATH

4.测试是否安装成功
get_loaded_extensions() 显示安装的所有扩展

2月 06

Mongo控制台下增删改查

本文由 shwy 发表于 2017年2月6日

mysql/nosql

1.先进入 mongo/bin 下目录输入mongo命令切换到mongo工作台

2.show bds; 查看所有数据库
?
3.use dbs; 选择数据表,出现以下说明选择成功

4.show collections; 查看该表中的所有合集

5.db.collection.inset(); 向集合中插入数据

但是有一点不能通过这个方式同时插入两条数据
这里返回是还是 1 条
以上的执行结果会出现两次name=aaa

如果需要一次插入多条则可以使用数组的方式

6.db.collection.remove({‘name’:’1111′}) 删除 name=1111的数据

7.db.collection.update(query , update, , );
第一:查询的条件
第二:更新的字段
第三:如果不存在就插入
第四:是否允许修改多条
默认情况下如果更新不存在的文档(mysql里面的行) 则会报错

如果更新的时候发现存在要插入
这个时候就要用第三个字段了

结果

但是此时的更新会把所有 name=888 的age 全部更新.. 如下图

如果我只是想把这条数据的 age 更新就要加上 $set 关键字这样就能准确的更新想要的字段了

8.db.collection.find(); 查询所有

加上pretty() 参数会格式化列表

_id是系统新增数据的时候自动生成的查询的方式跟其它一样

2月 06

Mysql常用语句

本文由 shwy 发表于 2017年2月6日

mysql/nosql

MySQL数据库操作
1)显示当前存在的数据库
>SHOW DATABASES;
2)选择你所需要的数据库
>USE guest;
3)查看当前所选择的数据库
>SELECT DATABASE();
4)查看一张表的所有内容
>SELECT * FROM guest; //可以先通过SHOW TABLES;来查看有多少张表
5)根据数据库设置中文编码
>SET NAMES gbk; //set names utf8;
6)创建一个数据库
>CREATE DATABASE book;
7)在数据库里创建一张表
>CREATE TABLE users (
>username VARCHAR(20), //NOT NULL 设置不允许为空
>sex CHAR(1),
>birth DATETIME);
8)显示表的结构
>DESCRIBE users;
9)给表插入一条数据
>INSERT INTO users (username,sex,birth) VALUES (‘Lee’,’x’,NOW());
10)筛选指定的数据
> SELECT * FROM users WHERE username = ‘Lee’;
11)修改指定的数据
>UPDATE users SET sex = ‘男’ WHERE username=’Lee’;
12)删除指定的数据
> DELETE FROM users WHERE username=’Lee’;
13)按指定的数据排序
> SELECT * FROM users ORDER BY birth DESC; //正序
14)删除指定的表
>DROP TABLE users;
15)删除指定的数据库
>DROP DATABASE book;

1.创建一个班级数据库school，里面包含一张班级表grade，包含编号(id)、姓名(name)、邮件(email)、评分(point)、注册日期(regdate)。
mysql>CREATE DATABASE school; //创建一个数据库
mysql> CREATE TABLE grade (
//UNSIGNED表示无符号，TINYINT(2) 无符号整数0-99，NOT NULL表示不能为空，AUTO_INCREMENT表示从1开始没增加一个字段，累计一位
-> id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> email VARCHAR(40),
-> point TINYINT(3) UNSIGNED NOT NULL,
-> regdate DATETIME NOT NULL,
-> PRIMARY KEY (id) //表示id为主键，让id值唯一，不得重复。
-> );

2.给这个班级表 grade新增5-10条学员记录
mysql> INSERT INTO grade (name,email,point,regdate) VALUES
(‘Lee’,’yc60.com@gmail.com’,95,NOW());

3.查看班级所有字段的记录，查看班级id,name,email的记录
mysql> SELECT * FROM grade;
mysql> SELECT id,name,email FROM grade;

WHERE表达式的常用运算符
MYSQL运算符含义
= 等于
< 小于 > 大于
<= 小于或等于 >= 大于或等于
!= 不等于
IS NOT NULL 具有一个值
IS NULL 没有值
BETWEEN 在范围内
NOT BETWEEN 不在范围内
IN 指定的范围
OR 两个条件语句之一为真
AND 两个条件语句都为真
NOT 条件语句不为真

4.姓名等于’Lee’的学员，成绩大于90分的学员，邮件不为空的成员，70-90之间的成员
mysql> SELECT * FROM grade WHERE name=’Lee’;
mysql> SELECT * FROM grade WHERE point>90;
mysql> SELECT * FROM grade WHERE email IS NOT NULL;
mysql> SELECT * FROM grade WHERE point BETWEEN 70 AND 90;
mysql> SELECT * FROM grade WHERE point IN (95,82,78);

5.查找邮件使用163的学员，不包含yc60.com字符串的学员
mysql> SELECT * FROM grade WHERE email LIKE ‘%163.com’;
mysql> SELECT * FROM grade WHERE email NOT LIKE ‘%yc60.com%’;

6.按照学员注册日期的倒序排序，按照分数的正序排序
mysql> SELECT * FROM grade ORDER BY regdate DESC;
mysql> SELECT * FROM grade ORDER BY point ASC;

7.只显示前三条学员的数据，从第3条数据开始显示2条
mysql> SELECT * FROM grade LIMIT 3;
mysql> SELECT * FROM grade LIMIT 2,2;

8.修改姓名为’Lee’的电子邮件
mysql> UPDATE grade SET email=’yc60.com@163.com’ WHERE name=’Lee’;

9.删除编号为4的学员数据
mysql> DELETE FROM grade WHERE id=4;

2月 06

Mysql 主键ID

本文由 shwy 发表于 2017年2月6日

mysql/nosql

利用Mysql自带的一个函数语句 “SHOW TABLE STATUS” ，可以获取当前数据库里的所有表的属性等信息，通常用来做数据库的优化和维护。如果需要指定某一张表的话，可以通过两种方法指定。
一种是网上给出来的：
show table status like ‘TableName ‘
另外就是常见的where语句
show table status where Name =’ TableName ‘

得到的结果集里面有一列AUTO_INCREMENT，是一个Int值变量，获取之后就是下一个要插入的ID了。

2月 06

SQL的主键和外键约束

本文由 shwy 发表于 2017年2月6日

mysql/nosql

SQL的主键和外键的作用：

外键取值规则：空值或参照的主键值。

(1)插入非空值时，如果主键表中没有这个值，则不能插入。

(2)更新时，不能改为主键表中没有的值。

(3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

(4)更新主键记录时，同样有级联更新和拒绝执行的选择。

简而言之，SQL的主键和外键就是起约束作用。

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。

比如：

学生表(学号，姓名，性别，班级)

其中每个学生的学号是唯一的，学号就是一个主键；

课程表(课程编号,课程名,学分)

其中课程编号是唯一的,课程编号就是一个主键；

成绩表(学号,课程号,成绩)

成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以，学号和课程号的属性组是一个主键。

成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键；同理，成绩表中的课程号是课程表的外键。

定义主键和外键主要是为了维护关系数据库的完整性，总结一下：

一、主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

二、主键、外键和索引的区别

定义：

主键–唯一标识一条记录，不能有重复的，不允许为空

外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引–该字段没有重复值，但可以有一个空值

作用：

主键–用来保证数据完整性

外键–用来和其他表建立联系用的

索引–是提高查询排序的速度

个数：

主键–主键只能有一个

外键–一个表可以有多个外键

索引–一个表可以有多个唯一索引

创建SQL的主键和外键约束的方法：

create table Student –建表格式:create table 自定义的表名

( –字段名一般为有一定意义的英文

StudentName nvarchar(15), — 格式：字段名类型()括号里面的是允许输入的长度

StudentAge int, –int型的后面不需要接长度

StudentSex nvarchar(2) –最后一个字段后面不要逗号

)

–在创建表时就可以对字段加上约束:

create table Student

(

StudentNo int PRIMARY KEY IDENTITY(1,1), –加主键约束,还有标识列属性（两者构成实体完整性）

StudentName nvarchar(15) not null, –加非空约束,不加”not null” 默认为：可以为空

StudentSchool text(20) FOREIGN KEY REFERENCES SchoolTable(SchoolName), –加外键约束,格式:FOREIGN KEY REFERENCES 关联的表名(字段名)

StudentAge int DEFAULT ((0)), –加默认值约束

StudentSex nvarchar(2) CHECK(StudentSex=N’男’ or StudentSex=N’女’) –加检查约束,格式:check (条件表达式)

)

–如果在表创建好了以后再加约束，则格式分别为：

— 主键:

alter table 表名

add constraint PK_字段名–“PK”为主键的缩写，字段名为要在其上创建主键的字段名,’PK_字段名’就为约束名

primary key (字段名) –字段名同上

–唯一约束:

alter table 表名

add constraint UQ_字段名

unique (字段名)

–外键约束：

alter table 表名

add constraint FK_字段名–“FK”为外键的缩写

foreign key (字段名) references 关联的表名(关联的字段名) –注意’关联的表名’和’关联的字段名’

alter table 表A add constraint FK_B foreign key (ticket_no) references 表B(ticket_no)

alter table 表A add constraint FK_C foreign key (person_no) references 表C(person_no)

alter table 成绩表 add constraint FK_StudentNo foreign key (StudentNo) references Student (StudentNo)

ON UPDATE CASCADE ON DELETE CASCADE

级联更新，级联删除，这样在删除主表Student时，成绩表中该学生的所有成绩都会删除。

–检查约束:

alter table 表名

add constraint CK_字段名

check (条件表达式) –条件表达式中的条件用关系运算符连接

–默认值约束:

alter table 表名

add constraint DF_字段名

default ‘默认值’ for 字段名–其中的’默认值’为你想要默认的值，注意’for’

–删除创建的约束:

alter table 表名

drop constraint 约束名–约束名为你前面创建的如：PK_字段这样的约束名

–注意：如果约束是在创建表的时候创建的，则不能用命令删除

–只能在’企业管理器’里面删除

— 获取SqlServer中表结构

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,

syscolumns.length

FROM syscolumns,systypes

WHERE syscolumns.xusertype = systypes.xusertype

AND syscolumns.id = OBJECT_ID(‘Student’)

— 单独查询表递增字段

SELECT [name] FROM syscolumns WHERE

id = OBJECT_ID(N’Student’) AND COLUMNPROPERTY(id,name,’IsIdentity’)=1

— 获取表主外键约束

EXEC sp_helpconstraint ‘StuResults’

— 查询表主键外键信息

SELECT sysobjects.id objectId,OBJECT_NAME(sysobjects.parent_obj) tableName,

sysobjects.name constraintName, sysobjects.xtype AS constraintType,

syscolumns.name AS columnName

FROM sysobjects INNER JOIN sysconstraints

ON sysobjects.xtype in(‘C’, ‘F’, ‘PK’, ‘UQ’, ‘D’)

AND sysobjects.id = sysconstraints.constid

LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id

WHERE OBJECT_NAME(sysobjects.parent_obj)=’StuResults’

2月 06

mysql之触发器trigger

本文由 shwy 发表于 2017年2月6日

mysql/nosql

mysql之触发器trigger
触发器(trigger)：监视某种情况，并触发某种操作。

触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)

语法：

create trigger triggerName

after/before insert/update/delete on 表名

for each row #这句话在mysql是固定的

begin

sql语句;

end;

注：各自颜色对应上面的四要素。

首先我们来创建两张表：

#商品表

create table g

(

　　id int primary key auto_increment,

　　name varchar(20),

　　num int

);

#订单表

create table o

(

　　oid int primary key auto_increment,

　 gid int,

much int

);

insert into g(name,num) values(‘商品1’,10),(‘商品2’,10),(‘商品3’,10);

如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事

1.往订单表插入一条记录

insert into o(gid,much) values(1,3);

2.更新商品表商品1的剩余数量

update g set num=num-3 where id=1;

现在，我们来创建一个触发器：

需要先执行该语句：delimiter $(意思是告诉mysql语句的结尾换成以$结束)

create trigger tg1
after insert on o
for each row
begin
update g set num=num-3 where id=1;
end$

这时候我们只要执行：

insert into o(gid,much) values(1,3)$

会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。

我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。

对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

所以现在我们可以这样来改我们的触发器

create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
end$

第二个触发器创建完毕，我们先把第一个触发器删掉

drop trigger tg1$

再来测试一下，插入一条订单记录：insert into o(gid,much) values(2,3)$

执行完发现商品2的数量变为7了，现在就对了。

现在还存在两种情况：

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

2.当用户修改一个订单的数量时，我们触发器修改怎么写?

我们先分析一下第一种情况：

监视地点：o表

监视事件：delete

触发时间：after

触发事件：update

对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

那我们的触发器就该这样写：

create trigger tg3

after delete on o

for each row

begin

update g set num = num + old.much where id = old.gid;(注意这边的变化)

end$

创建完毕。

再执行delete from o where oid = 2$

会发现商品2的数量又变为10了。

第二种情况：

监视地点：o表

监视事件：update

触发时间：after

触发事件：update

对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；

修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。

那我们的触发器就该这样写：

create trigger tg4

after update on o

for each row

begin

update g set num = num+old.much-new.much where id = old/new.gid;

end$

先把旧的数量恢复再减去新的数量就是修改后的数量了。

我们来测试下：先把商品表和订单表的数据都清掉，易于测试。

假设我们往商品表插入三个商品，数量都是10，

买3个商品1：insert into o(gid,much) values(1,3)$

这时候商品1的数量变为7；

我们再修改插入的订单记录: update o set much = 5 where oid = 1$

我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。