mysql笔记（2）

目录

• 外键的变种：
  • 情况一：一对多
  • 情况二：一对一
  • 情况三：多对多
• sql语句行操作补充：
  • 查询:

索引：

1. 普通索引：
   由关键字key或者index定义的索引；任务是加快对数据的访问速度。所以一般只给最常调用或带有排序条件的数据创建索引。只要有可能，就应该选择一个数据最整齐紧凑的数据列（例如整型类）来创建索引
2. 唯一索引：
   和普通索引允许被索引的数据包含重复值不同。被唯一索引的数据列只能包含彼此各不相同的值。创建的方法是unique 唯一索引名称 (列名,列名)。唯一索引可以保证数据记录的唯一性
3. 主索引：
   必须为主键字段创建一个索引，这个索引就是“主索引”
4. 外键索引：
5. 复合索引

外键的变种：

情况一：一对多

e：
用户表和部门表
用户：

id  name    department
1   alex       1
2   root       1
3   egon       2
4   laoyao     3

部门：

id  department
1   服务
2   保安
3   公关

一个部门中可能有好几个人，但是一个人只能属于一个部门

情况二：一对一

e：
用户表和博客表
用户表：

1 alex
2 root
3 egon
4 laoyao

博客：

1    /yuanchenqi/   4
2    /alex3714/     1
3    /asdfasdf/     3
4    /ffffffff/     2

一个用户名只能对应一个博客，一个博客也只能被一个用户名持有

情况三：多对多

e：
主机资源分配表
用户：

+----+-------+
| id | name  |
+----+-------+
|  1 | scott |
|  2 | jerry |
|  3 | peter |
+----+-------+

主机：

+----+------+
| id | host |
+----+------+
|  1 | c1   |
|  2 | c2   |
|  3 | c3   |
|  4 | c4   |
|  5 | c5   |
+----+------+

主机和用户的对应关系：

+----+-----------+-----------+
| id | user_name | user_host |
+----+-----------+-----------+
|  1 |         1 |         1 |
|  2 |         1 |         2 |
|  3 |         2 |         5 |
|  4 |         3 |         1 |
+----+-----------+-----------+

这种情况就是，一个用户可以调用多台主机，一台主机也可以被多个用户调用，但是这两者之间的关系如果直接用“某个用户可以使用1.2.3号主机”来描述，在检索的时候，就需要对这个“1，2，3”进行拆分整理操作，比较麻烦。
而如果像上面表三那样的对应关系来检索，每次只要检索一条就可以确认一台主机和一个用户之间的关系。不需要做过多的操作，简单迅速。
而且上述的方法使用了我们在开头提到过的唯一索引，也就是说如果scott使用了c1这台电脑的信息，不会被重复录入表中。确保了数据的唯一性。

绑定双外键并且建立联合索引：

mysql> create table re_ui(
    -> id int not null auto_increment primary key,
    -> user_name int not null,
    -> constraint fk_re_user foreign key (user_name) references user_info(id),
    -> user_host int not null,
    -> constraint fk_re_host foreign key (user_host) references host_info(id),
    -> unique relation_uh(user_name,user_host))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)


mysql> desc re_ui;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| user_name | int(11) | NO   | MUL | NULL    |                |
| user_host | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

sql语句行操作补充：

• 创建表：

mysql> create table table_note(
    -> id int auto_increment primary key,
    -> name varchar(32),
    -> age int
    -> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.04 sec)

• 添加数据
  insert into table_note(name,age)values('scott',25),('jerry',24);
  通过别的表的进行添加数据：

mysql> insert into table_note(name,age)select name,age from tb_note;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from table_note;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | scott |   25 |
|  2 | jerry |   24 |
|  3 | peter |   22 |
|  4 | jeck  |   26 |
+----+-------+------+

• 删除数据

delete from tb12;
delete from tb12 where id >=2 or name='alex'

复杂删除的范例：

DELETE from h_ncourse WHERE h_ncourse.course_id in
//删除的条件在归纳获得的临时表中
( 
SELECT tb1.cid from 
(
SELECT h_course.teacher_id as tid,
h_course.id as cid
from 
h_course
)tb1
INNER JOIN
(
select h_teacher.id as tid
FROM h_teacher
WHERE h_teacher.tname="叶平"
)tb2
on tb1.tid=tb2.tid
)

• 修改数据

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;

• 通配符：
  使用like和多字符通配符%，单字符通配符_获取字符筛选结果：

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

• 分页：
  LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

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;
//offset就是起点，这种格式，第一个数字才是偏移量，第二个数字是起始位
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1 
# select * from tb12 limit 10,10;2

• 排序：
  通常用来和limit连用，获取“正数/倒数前n条记录”。
  从大到小和从小的记忆方法：d是“大”的首字母啊，所以从大“d”到小。

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;

• 分组：

1. GROUP BY 后面可以包含多个列，这就是嵌套。
2. 如果GROUP BY进行了嵌套，数据将在最后一个分组上进行汇总。
3. GROUP BY子句中列出来的每个列必须是检索列或有效的表达式（但不能是聚集函数），如果在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
4. 除了聚集语句外，SELECT语句中的每一个列都必须在GROUP BY子句中给出/SELECT子句中的列名必须为分组列或列函数。
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多个NULL，它们将作为一个分组返回。
6. GROUP BY子句必须在WHERE 子句之后，ORDER BY 子句之前

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;
//获取id数大于1的条目的part_id，id数，并且以part_id分组。
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

练习：

权限管理
------------------
权限表：
1   订单管理
2   用户管理
3   菜单管理
4   权限分配
5   Bug管理
-------------
用户表：
1   Alex
2   egon
------------------
用户权限关系表：
1    1
1    2
2    1
----------------

Python实现：
某个用户登录后，查看自己拥有所有权限