- 节回顾:
- 1. 以ATM引出DBMS
- 2. MySQL
- - 服务端
- - 客户端
- 3. 通信交流
- - 授权
- - SQL语句
- - 数据库
- create database db1; ?
- drop database db1;
-
- - 数据表
- 先创建tb2部门表
-
- 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;
- 创建外键可以()写多个,注意里面的多列是一个主键
-
- 补充:主键
- 一个表只能有一个主键
- 主键可以由多列组成
-
-
- 补充:外键 ?
- CREATE TABLE t5 (
- nid int(11) NOT NULL AUTO_INCREMENT,
- pid int(11) not NULL,
- num int(11),
- primary key(nid,pid)这两列组成一个主键,多列组成的主键不常用,但是要知道
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
-
- create table t6(
- id int auto_increment primary key,
- name char(10),
- id1 int,
- id2 int,
- CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
- )engine=innodb default charset=utf8;
-
-
-
- - 数据行
-
- insert into tb1(name,age) values(‘alex‘,18);
-
- delete from tb1;自增列计数不会清空
- truncate table tb1;自增列计数清空
- delete from tb1 where id > 10
-
-
- update tb1 set name=‘root‘ where id > 10
-
- select * from tb;
- select id,name from tb;
-
-
- 4 对于自增补充:
- desc t10;
-
- show create table t10;
-
- show create table t10 \G;
-
- alter table t10 AUTO_INCREMENT=20;修改表的自增来
-
-
- MySQL: 自增步长
- 基于会话级别:
- 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
-
-
- 今日内容:
- 0. 唯一索引(可以联合唯一索引)
-
- 语法
- create table t1(
- id int ....,
- num int,
- xx int,
- unique 唯一索引名称 (列名,列名),
- constraint ....
- )
- #
- 1 1 1
- 2 1 2
- 唯一索引的主要作用PS:
- 唯一:
- 约束不能重复(可以为空)
- PS: 主键不能重复(不能为空)
- 作用是加速查找,如果以后有用到唯一但是为不为空不限制 就可以考虑主键和约束
-
-
- 1. 外键的变种
-
- a. 用户表和部门表
-
- 用户:
- 1 alex 1
- 2 root 1
- 3 egon 2
- 4 laoyao 3
-
- 部门:
- 1 服务
- 2 保安
- 3 公关
- ===》 一对多
- b. 用户表和博客表
- 用户表:
- 1 alex
- 2 root
- 3 egon
- 4 laoyao
- 博客表:
- FK() + 唯一
- 1 /yuanchenqi/ 4
- 2 /alex3714/ 1
- 3 /asdfasdf/ 3
- 4 /ffffffff/ 2
-
- ===> 一对一
-
- 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;
-
-
-
-
- c. 用户表(百合网) 相亲记录表
-
- 示例1:
- 用户表
- 相亲表
-
- 示例2:
- 用户表
- 主机表
- 用户主机关系表
- ===》多对多
-
- 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;
-
-
- 2. 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‘; as起别名
-
- select name,age,11 from tb12;额外的常量列11
-
- 其他:
- 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) where后面的select只能写一列
- select * from tb12 where id between 5 and 12;
-
-
- 通配符:
-
- select * from tb12 where name like "a%" %表示任务多个字符(0个或多个)
- select * from tb12 where name like "a_" _代指一个字符
-
-
- 分页:
-
- select * from tb12 limit 10;
-
- select * from tb12 limit 0,10; 从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;