最近在找工作，遇到2个面试数据库方面的笔试，当时回答的不理想，整理

1. 多对多表的设计
        场景：客户订购图书。  1个客户可以订购多本图书，1本图书可以被多个客户订购，我的解决方法：除了设计客户表和图书表外，再设计一个订单表
            具体设计：
            ####booksell.sql
            CREATE TABLE bookuser (
                        userID int(16) NOT NULL PRIMARY KEY,
                        username varchar(50) NOT NULL,
                        password varchar(50) NOT NULL,
                        email varchar(50) NOT NULL,
                        addr varchar(50)
            )
            CREATE TABLE book (
                        ISBN int(16) NOT NULL PRIMARY KEY,
                        bookname varchar(50) NOT NULL,
                        author varchar(50),
                        publisher varchar(50)
            )

            CREATE TABLE order(
                        ###
                        OrderID int(16) NOT NULL PRIMARY KEY,
                        userID int(16) NOT NULL,
                        ISBN varchar(50) NOT NULL,
                        Quantity int(16)
             )
            



2.复杂查询
     1).个人书签的表设计

                ##bookmarks.sql
                CREATE TABLE user (
                        username varchar(16) not null primary key,
                        passwd char(40) NOT NULL,
                        email varchar(100) NOT NULL
                );

                CREATE TABLE bookmark (
                        username varchar(16) not null,
                        bm_URL varchar(255) not null,
                        index(username),
                        index(bm_URL),
                        primary key(username, bm_URL)
                );

     2).问题：查找与给定用户至少有一个相同书签的其他用户
                    SELECT distinct(b2.username) FROM bookmark b1, bookmark b2
                        WHRER b1.username='".$valid_user."'
                        AND b1.username != b2.username
                        AND b1.bm_URL = b2.bm_URL

                    以上用户作为子查询进一步查询出这些有相同标签用户的其他书签
                    SELECT bm_URL FROM bookmark WHERE username in (
                            ###上段代码重复
                        SELECT distinct(b2.username) FROM bookmark b1, bookmark b2
                            WHRER b1.username='".$valid_user."'
                            AND b1.username != b2.username
                            AND b1.bm_URL = b2.bm_URL
                        )
                    更近一步：如果用户已经有了这些书签，就不必再将该书签推荐给他，在数量上做了一定的限制为8
                    SELECT bm_URL FROM bookmark WHERE username in (
                           ###重复上段代码 
                          SELECT distinct(b2.username) FROM bookmark b1, bookmark b2
                            WHRER b1.username='".$valid_user."'
                            AND b1.username != b2.username
                            AND b1.bm_URL = b2.bm_URL
                         AND bm_URL not in (
                            ###不重复自己已有的书签
                               SELECT bm_URL FROM bookmark WHERE username='".$valid_username."')
                         GROUP BY bm_url
                         HAVING count(bm_url) <8;