python之路 mysql 博客园_python之路-----MySql操作二

一.主键

1.每个 表只有一个主键

2.每个主键可以由多个列组成.(如果主键由多个组成,只要有一行列值不等即可)

CREATE TABLE NAME (

id INT auto_increment,

NAME CHAR (20),

sid int,

PRIMARY KEY (id, sid)

);

插入数据(1,'a',1)，主键值为：(1,1) ,插入(1,'b',2)，主键为(1,2)，两个主键值不完全相等即可

主键示例

二.唯一索引

唯一索引也是一种约束，约束内容为：

1.可以为空，但是不能重复

2.加快查询速度

CREATE TABLE name1 (

id INT auto_increment,

NAME CHAR (20),

UNIQUE qu_name (NAME),

INDEX (id)

)

唯一索引示例

唯一索引的值可以为多列，称为联合唯一索引

和主键的区别：

1.主键不允许为空，唯一索引可以为空

2.主键可在表外部被调用，唯一索引只能在表内使用

三.自增

1.修改自增列起始值

1.1.查看表数据

DESC hostinfo;

1.2.查看创建表时的命令:

show CREATE TABLE tablename

show CREATE TABLE tablename \G 竖着显示数据

CREATE TABLE `more2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pc_name` char(20) DEFAULT NULL,

`owner_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uq_more2` (`owner_id`),

CONSTRAINT `one2_more2` FOREIGN KEY (`owner_id`) REFERENCES `one2` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

1.3.通过修改表的auto_increment值来进行自增初始值的修改

ALTER TABLE tablename auto_increment=9;

这时候会从9开始自增,新插入的数据自增列值为9.

2.修改步长

mysql的步长单位和其他的数据库软件不同，部分数据库的步长为表级别的，可以针对表设置步长,而mysql的步长修改为会话级别的,一次连接修改的步长仅本次连接有效，如果新建连接，而步长依旧为1.

2.1 查看步长

show session variables like '%auto_inc%';     #查看session变量

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |   #步长

| auto_increment_offset | 1 |       #数量默认起始值

+--------------------------+-------+

2.2 修改session级别自增变量值

set session auto_increment_increment=3;   #设置步长为3

set session auto_increment_offset=5;           #设置自增列默认起始值为5

2.3 查看修改后的值

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 3 |

| auto_increment_offset | 5 |

+--------------------------+-------+

新建连接,查看自增起始值和步长

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

+--------------------------+-------+

我们发送，通过修改session变量，仅本次连接有效，如果要使修改的值永久有效，需要修改global变量

2.4查看global自增相关变量值

show global variables like '%auto_inc%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

+--------------------------+-------+

2.5 修改global变量

set global auto_increment_increment=2;  #修改自增步长

set global auto_increment_offset=2;      #修改自增列起始值

2.6 新建连接查看，配置依旧生效 。但是不建议使用该方法进行修改

四.外键

补充:当主键为多个时，外键可以关联多个

create table morekey(

id int auto_increment,

sid int,

name char(20),

PRIMARY KEY(id,sid)

);

CREATE table morekey1(

id1 INT,

sid1 INT,

name1 CHAR(20),

CONSTRAINT fk_moremore FOREIGN KEY(id1,sid1) REFERENCES morekey(id,sid)

)

关联多个外键

1.一对多

当我们对两个表通过外键建立约束关系时，此时表A的值对应应表B的多个值时,且B表的值仅对应A表的一个值，则该关系为一对多。例如部门表和员工表。一个员工仅对应一个部门,而一个部门却可以对应多个员工。

create table morekey(

id int auto_increment,

sid int,

name char(20),

PRIMARY KEY(id,sid)

);

CREATE table morekey1(

id1 INT,

sid1 INT,

name1 CHAR(20),

CONSTRAINT fk_moremore FOREIGN KEY(id1,sid1) REFERENCES morekey(id,sid)

)

一对多示例

2.一对一

当B表的值受A表的约束,且A表的值只能被B表使用一次，这时候A表和B表的值是一对一的。例如公司资产登记，一台pc只能归一个职工使用。或者是密码登陆管理，不允许用户名重复。

我们知道，唯一索引的作用就是对表内的值进行约束，使其在表内是唯一的，不允许重复。我们利用外键和唯一索引，即可完成一对一的表格设计

--pc编号

CREATE TABLE hostinfo(

id int auto_increment PRIMARY KEY,

host_name CHAR(20)

);--用户资产信息

CREATE TABLE employ(

id int auto_increment PRIMARY KEY,

name char(20),

pc_id int,

UNIQUE uq_hostinfo_employ (pc_id),

CONSTRAINT fk_hostinfo_employ FOREIGN KEY (pc_id) REFERENCES hostinfo(id)

);

一对一示例

3.多对多

当A表的值和B表的值互相对应多个时,即两表之间有着多对多的关系。例如运维人员对公司服务器的主机管理，一个人可登陆多台服务器，一台服务器也可多人登陆。

我们需要利用第三张表来记录A表和B表之间多对多的关系。

--主机信息表

CREATE TABLE pcinfo(

id int auto_increment PRIMARY KEY,

host_name char(20)

);--管理员信息表

CREATE TABLE admininfo(

id int auto_increment PRIMARY KEY,

name char(20)

);--管理员主机对应表

CREATE TABLE admin_pc(

id int auto_increment PRIMARY KEY,

pc_id int ,

user_id int,

UNIQUE uq_user_pc (pc_id,user_id),

CONSTRAINT fk_admin_pc FOREIGN KEY(pc_id) REFERENCES pcinfo(id),

CONSTRAINT fk_admin_user FOREIGN KEY(user_id) REFERENCES admininfo(id)

);

多对多示例

五.操作表内容高级运用补充

1.笛卡尔积

笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。例如A表中有三条数据,B表中有三条数据，则使用命令:

select * from A,B,则会产生九条结果。A表的每个值都会和B表的每个值产生一条对应的值。关系图如下：

2.临时表，将过滤出来的数据临时当成一张表使用

命令格式：(SELECT 列名 FROM 表) as B

应用

连表操作:

SELECT* FROM (SELECT sid FROM score) as B LEFT JOIN student on student.sid=B.sid;

把score表中过滤出来的数据和student表进行连表操作

3.insert插入过滤后的表内容

INSERT INTO class(caption) SELECT name FROM class2;

ps:需要注意的是，插入的列要跟表的列一致

4.条件语句

句型:case when 条件 then 条件为正返回 esle 条件为假返回 end

case when id<10 then 1 else 0 end; #如果id小于10，则返回1,否则返回0

5.利用常量来统计

select count(1) from tablename groub by id;

6.插入动态值

SELECT

student_id,

(select numfrom score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,

(select numfrom score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,

(select numfrom score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语from score as s1;

s1表的值可以再内循环中使用。类似于：

for student_id ins1:for id ins2:

s2=s1

7.三元运算

if(条件,为真返回，为假返回)

e.g:if (isnull(xx),0,1)  如果为null，则返回0，否则返回1