mysql查找所有外键_MySQL 外键 表的查询

自增补充

这是查看怎么创建的表， \G示旋转90度显示表的内容

表的自增的关键是** AUTO_INCREMENT=3**，在表中添加数据后，这个会自动改变，通过alert可以改变这个默认值

mysql> show create table t1 \G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

下一次添加的内容的id会从20处添加

alter table t10 AUTO_INCREMENT=20;

自增步长

mysql是的默认步长是基于会话session的，sqlserver是基于表的。

查看全局变量，其中默认是1

mysql> show session variables like 'auto_inc%';

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

| Variable_name | Value |

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

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

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

2 rows in set (0.00 sec)

设置步长基于会话步长，只能自该自己的会话

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;

唯一索引

unique

create table t1(

id int ....,

num int,

xx int,

unique 唯一索引名称 (列名,列名),

constraint ....

)

这了唯一的意思是：

约束不能重复(可以为空)

主键不能重复(不能为空)

作用是加速查找

外键的变种

单列

联合

关联一对多

create table userinfo(

id int auto_increment primary key,

username varchar,

usertype int,

)engine=innodb default charset=utf8;

create table admin(

id int auto_increment primary key,

user_id int,

passwprd varchar,

unique index(user_id),

constraint fk_key1 foreign key (user_id) references userinfo(id)

)engine=innodb default charset=utf8;

-- 外键关联多个列

create table t2(

nid int not null auto_increment,

pid int not null,

num int,

primary key(nid,pid)-- 这里的关联两个列的主键

)engine=innodb default charset=utf8;

create table t3(

id int auto_increment primary key,

name char,

id1 int,

id2 int,

constraint fk_t3_t2 foreign key (id1,id2) references t2(nid,pid)

)engine=innodb default charset=utf8;

多对多

-- 多对多

-- 用户表

create table userinfo(

id int auto_increment primary key,

username varchar,

gender int,

)engine=innodb default charset=utf8;

-- 主机表

create table computer(

id int auto_increment primary key,

name varchar,

)engine=innodb default charset=utf8;

-- 用户主机关系表

create table userandcom(

id int auto_increment primary key,

user_id int,

host_id int,

unique index(user_id,host_id),

constraint fk_key2 foreign key (user_id) references userinfo(id),

constraint fk_key3 foreign key (host_id) references computer(id)

)engine=innodb default charset=utf8;

SQL语句数据行操作补充

增

-- 增加单条数据

insert into t1 (name) values('ddd')；

增加多条数据

-- insert into t1 (name) values('ddd'),('eee');

-- 从一个表中添加另一个内容

insert into t4(name) select name from t1;

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

| id | name |

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

| NULL | aaa |

| NULL | aaa |

| NULL | ccc |

| NULL | ddd |

| NULL | eee |

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

这里出现null的原因是在创建表的时候没有添加自增和主键

在调试中发现char后面不加长度，默认的长度是1，所以要添加一个长度。这个是根据需求

删

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='a'

改

update tb12 set name='a' where id>12 and name='xx'

update tb12 set name='a',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 "aa_"

分页

select * from tb12 limit 10;

select * from tb12 limit 0,10;

select * from tb12 limit 10,10;

select * from tb12 limit 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; # 这是优先级 先按照age倒序，后按照id排序(ID中有相同的)

取后10条数据：先倒序后去取

select * from tb12 order by id desc limit 10;

mysql> select * from t5 order by name desc,id desc;

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

| id | name |

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

| 5 | eee |

| 4 | ddd |

| 3 | ccc |

| 2 | aaa |

| 1 | aaa |

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

5 rows in set (0.00 sec)

mysql> select * from t5 order by name desc,id asc;

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

| id | name |

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

| 5 | eee |

| 4 | ddd |

| 3 | ccc |

| 1 | aaa |

| 2 | aaa |

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

分组

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

- count

- max

- min

- sum

- avg

如果对于聚合函数结果进行二次筛选时？必须使用having ,不能使用where

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

连表操作

连表操作主要是把两张表显示在一张表上，主要用过join

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 userinfo5 right join department5 on userinfo5.part_id = department5.id

select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;这种就是变相的right

如果一张表显示全部，但是另一张表还有多的内容的时候，就会出现空null

inner join 将出现null时一行隐藏

select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id

mysql> select * from t1 left join t5 on t1.id=t5.id;

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

| id | name | id | name |

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

| 1 | aaa | 1 | aaa |

| 2 | aaa | 2 | aaa |

| 3 | ccc | 3 | ccc |

| 4 | ddd | 4 | ddd |

| 5 | eee | 5 | eee |

| 6 | hahah | NULL | NULL |

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

隐藏空行

mysql> select * from t1 inner join t5 on t1.id=t5.id;

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

| id | name | id | name |

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

| 1 | aaa | 1 | aaa |

| 2 | aaa | 2 | aaa |

| 3 | ccc | 3 | ccc |

| 4 | ddd | 4 | ddd |

| 5 | eee | 5 | eee |

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

5 rows in set (0.00 sec)

数据库的备份

数据库导出

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据(导入的时候会自动穿件表并把表的内容插入)

mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 仅仅结构

导入现有数据库数据：

mysql -uroot -p密码 数据库名称 < 文件路径

注意的是导入的时候不能用dump