MySQL 外键 表的查询

Posted on

自增补充

这是查看怎么创建的表， \G示旋转90度显示表的内容
表的自增的关键是 AUTO_INCREMENT=3，在表中添加数据后，这个会自动改变，通过alert可以改变这个默认值

1
2
3
4
5
6
7
8
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处添加

1
alter table t10 AUTO_INCREMENT=20;

自增步长

mysql是的默认步长是基于会话session的，sqlserver是基于表的。
查看全局变量，其中默认是1

1
2
3
4
5
6
7
8
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)

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

1
2
set session auto_increment_increment=2; 	设置会话步长
set session auto_increment_offset=10; 设置开始的位置

基于全局的级别的，可以修改全部的会话

1
2
3
show global variables like 'auto_inc%';	    查看全局变量
set global auto_increment_increment=2; 	    设置会话步长
set global auto_increment_offset=10;

唯一索引

unique

1
2
3
4
5
6
7
create table t1(
			id int ....,
			num int,
			xx int,
			unique 唯一索引名称 (列名,列名),
			constraint ....
		)

这了唯一的意思是：

  • 约束不能重复（可以为空）
  • 主键不能重复（不能为空）

作用是加速查找

外键的变种

  • 单列
  • 联合

关联一对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 外键关联多个列
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;

多对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 多对多
-- 用户表 
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语句数据行操作补充

增
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 增加单条数据
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，所以要添加一个长度。这个是根据需求

删
1
2
3
4
5
6
7
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'
改
1
2
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;

  • 通配符
    通配符的意识替换的意思
    %能够替换多个字符
    _只能替换一个字符
1
2
select * from tb12 where name like "a%"
select * from tb12 where name like "aa_"
  • 分页
1
2
3
4
select * from tb12 limit 10;					
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;

后期的Python应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 第一页1 
# select * from tb12 limit 10,10;第二页2
```                   
- 排序
```sql
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
7
8
9
10
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时一行隐藏

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
```					
```sql
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 |
+----+-------+------+------+

隐藏空行

1
2
3
4
5
6
7
8
9
10
11
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

© 2018 Peter's Blog Center All Rights Reserved.
Theme by hiero