mysql 外键属于索引

转载

mob64ca1404476b 2024-11-05 16:11:19

文章标签 mysql 外键属于索引数据库 python 字段主键 文章分类 MySQL 数据库

主键
外键
alter修改表名或者修改数据表字段
自增起始值
自增步长
SqlServer：自增步长：
唯一索引
外键的变种

一对一
多对多

SQL语句数据行操作
exercise

主键

主键是能确定一条记录的唯一标识
一个表只能有一个主键, 主键可以由多列组成

#主键可以由多列组成
create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

上面的name和id字段组合起来就是你test表的复合主键
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好

外键

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

create table tb1用户表(
        id int not null auto_increment primary key,
        name char(10),
        department_id int,
        p_id int,
        constraint fk_1 foreign key (department_id,p_id) references tb2(tid,xid)
      )engine=innodb default charset=utf8;

alter修改表名或者修改数据表字段

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

#删除，添加或修改表字段

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

返回顶部

自增起始值

#查看表的字段
desc t10;

show create table t10;

show create table t10 \G;

#修改表的自增起始值为20
alter table t10 AUTO_INCREMENT=20;

自增步长

基于会话级别：
		show session variables like 'auto_inc%';	查看全局变量
            set session auto_increment_increment=2; 	设置会话步长
		# set session auto_increment_offset=10;
	基于全局级别：
		show global variables like 'auto_inc%';	    查看全局变量
            set global auto_increment_increment=2; 	    设置会话步长
		# set global auto_increment_offset=10;

返回顶部

SqlServer：自增步长：

基础表级别：
		CREATE TABLE `t5` (
		  `nid` int(11) NOT NULL AUTO_INCREMENT,
		  `pid` int(11) NOT NULL,
		  `num` int(11) DEFAULT NULL,
		  PRIMARY KEY (`nid`,`pid`)
		) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

		CREATE TABLE `t6` (
		  `nid` int(11) NOT NULL AUTO_INCREMENT,
		  `pid` int(11) NOT NULL,
		  `num` int(11) DEFAULT NULL,
		  PRIMARY KEY (`nid`,`pid`)
		) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

返回顶部

唯一索引

create table t1(
			id int ....,
			num int,
			xx int,
			unique 唯一索引名称 (列名,列名),
			constraint ....
		)
		#
		1   1   1
		2   1   2
		PS:
			唯一：约束不能重复（可以为空），加速查找

		PS: 主键：不能重复（不能为空）

返回顶部

外键的变种

一对一

create table userinfo1(
        id int auto_increment primary key,
        name char(10),
        gender char(10),
        email varchar(64)
      )engine=innodb default charset=utf8;

create table admin(
        id int not null auto_increment primary key,
        username varchar(64) not null,
        password VARCHAR(64) not null,
        user_id int not null,
        unique uq_u1 (user_id),
        CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
      )engine=innodb default charset=utf8;

返回顶部

多对多

create table userinfo2(
  id int auto_increment primary key,
  name char(10),
  gender char(10),
  email varchar(64)
)engine=innodb default charset=utf8;

create table host(
  id int auto_increment primary key,
  hostname char(64)
)engine=innodb default charset=utf8;


create table user2host(
  id int auto_increment primary key,
  userid int not null,
  hostid int not null,
  unique uq_user_host (userid,hostid),
  CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
  CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;

返回顶部

SQL语句数据行操作

create table tb12(
      id int auto_increment primary key,
      name varchar(32),
      age int
    )engine=innodb default charset=utf8;

  增
    insert into tb11(name,age) values('alex',12);

    insert into tb11(name,age) values('alex',12),('root',18);

    insert into tb12(name,age) select name,age from tb11;
  删
    delete from tb12;
    delete from tb12 where id !=2
    delete from tb12 where id =2
    delete from tb12 where id > 2
    delete from tb12 where id >=2
    delete from tb12 where id >=2 or name='alex'

  改
    update tb12 set name='alex' where id>12 and name='xx'
    update tb12 set name='alex',age=19 where id>12 and name='xx'
  查

    select * from tb12;

    select id,name from tb12;

    select id,name from tb12 where id > 10 or name ='xxx';

    select id,name as cname from tb12 where id > 10 or name ='xxx';

    select name,age,11 from tb12;

    其他：
      select * from tb12 where id != 1
      select * from tb12 where id in (1,5,12);
      select * from tb12 where id not in (1,5,12);
      select * from tb12 where id in (select id from tb11)
      select * from tb12 where id between 5 and 12;


      通配符：

      select * from tb12 where name like "a%"
      select * from tb12 where name like "a_"


      分页：

        select * from tb12 limit 10;

        select * from tb12 limit 0,10;
        select * from tb12 limit 10,10;
        select * from tb12 limit 20,10;

        select * from tb12 limit 10 offset 20;
        从第20行开始读取，读取10行；

        结合Python分页：
        # page = input('请输入要查看的页码')
        # page = int(page)
        # (page-1) * 10
        # select * from tb12 limit 0,10; 1
        # select * from tb12 limit 10,10;2


      排序：
        select * from tb12 order by id desc; 大到小
        select * from tb12 order by id asc;  小到大
         select * from tb12 order by age desc,id desc;

        取后10条数据
        select * from tb12 order by id desc limit 10;

      分组：

        select count(id),max(id),part_id from userinfo5 group by part_id;

        count
        max
        min
        sum
        avg

        **** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
        select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

        select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

      连表操作：

        select * from userinfo5,department5

        select * from userinfo5,department5 where userinfo5.part_id = department5.id

        select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
        select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
        # userinfo5左边全部显示

        # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
        # department5右边全部显示

        select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
        将出现null时一行隐藏

        select * from
          department5
        left join userinfo5 on userinfo5.part_id = department5.id
        left join userinfo6 on userinfo5.part_id = department5.id


        select
          score.sid,
          student.sid
          from
        score

          left join student on score.student_id = student.sid
          left join course on score.course_id = course.cid
          left join class on student.class_id = class.cid
          left join teacher on course.teacher_id=teacher.tid


    select count(id) from userinfo5;

返回顶部