7.27数据库笔记(2)
2018-7-27 20:19:49
数据库感觉不难,就是在命令行里面敲好长好长的SQL语句有点长,敲错好可惜233333333
不过数据库记住语句,然后知道怎么各种关系也不难!明天继续学,空闲时候练练字看看书,
感觉很惬意!
Day 58 上节回顾： 1. 以ATM引出DBMS 2. MySQL - 服务端 - 客户端 3. 通信交流 - 授权 - SQL语句 - 数据库 create database db1 default charset=utf8; 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); insert into tb1(name,age) values('alex',18),('egon',19),('yuan',20); 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; #修改为自增从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'; 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; #这两句话等价 都是取前10行数据 select * from tb12 limit 10,10; select * from tb12 limit 20,10; # 从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; 大到小 # d开头就是大到小 select * from tb12 order by id asc; 小到大 # a开头就是小到大 select * from tb12 order by age desc,id desc; 取后10条数据 select * from tb12 order by id desc limit 10;
2018-7-28 10:40:28 继续补全一下笔记
说一下目前学数据库的感觉,,就是记住语句然后知道各种关系 理解,然后用语句表达一下就好了!比python简单点!
分组： 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;
作业练习：
http://www.cnblogs.com/wupeiqi/articles/5729934.html
10-15个完成
2018-7-28 10:41:40 下午再继续看视频 晚上做一下数据库练习!
