day6-mysql多表查询
笛卡尔积
笛卡尔（Descartes）乘积又叫直积。假设集合A=a,b，集合B=0,1,2，则两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
学生表
选课表
两个表的笛卡尔积
数据库两个表连接在一起就会产生2个表的笛卡尔积
select * from student s1, course s2;
这个时候我们发现我们得到的数据是无意义的。那如果然我们的数据变得有意义呢？
我们则需要用我们两个边相关联的条件来限制数据的重复性。
内连接查询
select * from student s1, course s2 where s1.s_id=s2.s_id;
select * from student s1 inner join course s2 where s1.s_id=s2.s_id;
这个时候下图的灰色行代表被过滤掉的信息。
使用迪卡尔积查询出来的数据叫做内连接
外连接查询（outer join）
外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（left outer join）、右外连接（right outer join）和全外连接（full outer join）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
1. 左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
2. 右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
3. 全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。
左外连接（left outer join）
select * from student s left outer join select_course c on s.stu_id=c.stu_id;
右外连接（right outer join）
select * from student s right outer join select_course c on s.stu_id=c.stu_id;
全外连接（full outer join） mysql不支持全外连接
select * from student s full outer join select_course c on s.stu_id=c.stu_id;
sql查询的基本原理
第一、 单表查询：根据where条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据select的选择列选择相应的列进行返回最终结果。
第二、 两表连接查询：对两表求积（笛卡尔积）并用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
第三、 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
UNION
将多条查询语句的结果合并成一个结果
UNION 与 UNION ALL 的区别
当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。
作业
CREATE TABLE newsp_102_menu ( menu_id varchar(32) default '' NOT NULL, menu_name varchar(32) NOT NULL, menu_desc varchar(200) default NULL, menu_value varchar(200), father_menu varchar(30), PRIMARY KEY (menu_id) ); INSERT INTO newsp_102_menu VALUES ('M00','根菜单','根菜单','根菜单',''); INSERT INTO newsp_102_menu VALUES ('M0001','7系统管理','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000101','菜单管理','','','M0001'); INSERT INTO newsp_102_menu VALUES ('M00010101','菜单维护','','1_menu/menu/menu_list.php','M000101'); INSERT INTO newsp_102_menu VALUES ('M00010102','群管理','','1_menu/group/group_list.php','M000101'); INSERT INTO newsp_102_menu VALUES ('M00010103','菜单群设置','','1_menu/groupMenu/groupMenu_list.php','M000101'); INSERT INTO newsp_102_menu VALUES ('M00010104','人员群管理','','1_menu/groupUser/groupUser_list.php','M000101'); INSERT INTO newsp_102_menu VALUES ('M000102','参数管理','','','M0001'); INSERT INTO newsp_102_menu VALUES ('M00010201','类型参数','','1_menu/parameterEx/parameterEx_list.php','M000102'); INSERT INTO newsp_102_menu VALUES ('M000103','日志管理','','99_log/log/log_list.php','M0001'); INSERT INTO newsp_102_menu VALUES ('M00010301','日志查看','','99_log/log/log_list.php','M000103'); INSERT INTO newsp_102_menu VALUES ('M00010302','清理日志','','','M000103'); INSERT INTO newsp_102_menu VALUES ('M000104','流程管理','','','M0001'); INSERT INTO newsp_102_menu VALUES ('M00010401','流程查看','','5_flow/flow/flow_list.php','M000104'); INSERT INTO newsp_102_menu VALUES ('M000105','表建设','','framework/template/inner_frame.php?view_NP=table_list.view&','M0001'); INSERT INTO newsp_102_menu VALUES ('M000106','新增页面','','framework/template/inner_frame.php?view_NP=page_list.view&','M0001'); INSERT INTO newsp_102_menu VALUES ('M000107','维修信息','','framework/template/inner_frame.php?view_NP=repair_list.view&','M0001'); INSERT INTO newsp_102_menu VALUES ('M0002','2后台管理','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000201','人员管理','','','M0002'); INSERT INTO newsp_102_menu VALUES ('M00020101','人员信息维护','','2_user/user/zhuce_list.php','M000201'); INSERT INTO newsp_102_menu VALUES ('M000202','人员与菜单管理','','1_menu/menuUser/menuUser_list.php','M0002'); INSERT INTO newsp_102_menu VALUES ('M00020201','人员与菜单维护','','1_menu/menuUser/menuUser_list.php','M000202'); INSERT INTO newsp_102_menu VALUES ('M000203','部门管理','','','M0002'); INSERT INTO newsp_102_menu VALUES ('M00020301','部门信息维护','','2_user/dept/dept_list.php','M000203'); INSERT INTO newsp_102_menu VALUES ('M00020302','供应商维护','','2_user/provider/provider_list.php','M000203'); INSERT INTO newsp_102_menu VALUES ('M000204','公司信息','','','M0002'); INSERT INTO newsp_102_menu VALUES ('M00020401','公司基本信息','','4_corp/corp/corp_edit.php','M000204'); INSERT INTO newsp_102_menu VALUES ('M000205','分类管理','','','M0002'); INSERT INTO newsp_102_menu VALUES ('M00020501','分类设置','','3_kind/kind/kind_list.php','M000205'); INSERT INTO newsp_102_menu VALUES ('M0003','信息管理','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000301','新闻信息','','10_biginfo/news/news_list.php','M0003'); INSERT INTO newsp_102_menu VALUES ('M000302','服务信息','','10_biginfo/help/help_list.php','M0003'); INSERT INTO newsp_102_menu VALUES ('M000303','文字信息','','10_biginfo/article/article_list.php','M0003'); INSERT INTO newsp_102_menu VALUES ('M000304','商品查看','','6_project/view/view_list.php','M0003'); INSERT INTO newsp_102_menu VALUES ('M000305','商城商品设置','','','M0003'); INSERT INTO newsp_102_menu VALUES ('M00030501','商城查看设置','','framework/template/inner_frame.php?view_NP=view_edit_list.view&action_NP=view_edit_list_bf_NP.do&','M000305'); INSERT INTO newsp_102_menu VALUES ('M00030502','商品积分管理','','6_project/view/cent_list.php','M000305'); INSERT INTO newsp_102_menu VALUES ('M00030503','库存警告【新】','','6_project/view/warn_list.php','M000305'); INSERT INTO newsp_102_menu VALUES ('M00030504','文章查看设置','','framework/template/inner_frame.php?view_NP=article_view_edit_list.view&action_NP=article_view_edit_list_bf_NP.do&','M000305'); INSERT INTO newsp_102_menu VALUES ('M0004','5商品管理','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000401','商品信息','','','M0004'); INSERT INTO newsp_102_menu VALUES ('M00040101','商品品牌','','6_project\\brand\\brand_list.php','M000401'); INSERT INTO newsp_102_menu VALUES ('M00040102','商品分类','','6_project\\type\\type_list.php','M000401'); INSERT INTO newsp_102_menu VALUES ('M00040103','商品小分类','','6_project\\kind\\kind_list.php','M000401'); INSERT INTO newsp_102_menu VALUES ('M00040104','商品基本信息','','6_project\\project\\project_list.php','M000401'); INSERT INTO newsp_102_menu VALUES ('M00040105','商品详细信息','','6_project/project/projectPrice_list.php','M000401'); INSERT INTO newsp_102_menu VALUES ('M000402','进货','','','M0004'); INSERT INTO newsp_102_menu VALUES ('M00040201','进货信息录入','','7_stock/batch/batch_new.php','M000402'); INSERT INTO newsp_102_menu VALUES ('M00040202','进货查看','','7_stock/batch/batch_list.php','M000402'); INSERT INTO newsp_102_menu VALUES ('M00040203','进货审批','','7_stock/approve/approve_list.php','M000402'); INSERT INTO newsp_102_menu VALUES ('M00040204','进货归档查询','','7_stock/view/remain_select.php','M000402'); INSERT INTO newsp_102_menu VALUES ('M00040205','进货详细信息','','7_stock/view/detail_list.php','M000402'); INSERT INTO newsp_102_menu VALUES ('M000403','部份商品盘点','','','M0004'); INSERT INTO newsp_102_menu VALUES ('M00040301','盘点录入','','14_check/newCheck/batch_new.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040302','确定预盘点商品','','14_check/newCheck/check_list.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040303','盘点数量录入','','14_check/newCheck/check1_list.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040304','盘点审核','','14_check/newCheck/check2_list.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040305','归档信息','','14_check/newCheck/check3_list.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040306','盘点归档查询','','14_check/query/check_select.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M00040307','丢失商品统计','','14_check/query/lose_select.php','M000403'); INSERT INTO newsp_102_menu VALUES ('M000404','分销管理','','','M0004'); INSERT INTO newsp_102_menu VALUES ('M00040401','分销录入','','8_sell/batch/batch_new.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040402','分销查看','','8_sell/batch/batch_list.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040403','仓库确认','','8_sell/approve/approve_list.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040404','出货确认','','8_sell/approve1/approve_list.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040405','已收货确认','','8_sell/approve2/approve_list.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040406','分销归档查询','','8_sell/view/sell_select.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M00040407','全部收货确认','','8_sell/approve2/approve1_list.php','M000404'); INSERT INTO newsp_102_menu VALUES ('M000405','退货流程','','','M0004'); INSERT INTO newsp_102_menu VALUES ('M00040501','退货申请','','14_check/withdraw/withdraw_new.php','M000405'); INSERT INTO newsp_102_menu VALUES ('M00040502','我的退货申请','','14_check\\withdraw\\withdraw_list.php','M000405'); INSERT INTO newsp_102_menu VALUES ('M00040503','退货审批','','14_check/withdraw/withdraw1_list.php','M000405'); INSERT INTO newsp_102_menu VALUES ('M00040504','退货归档查询','','14_check/withdraw/withdraw2_list.php','M000405'); INSERT INTO newsp_102_menu VALUES ('M0005','8销售管理','','9_sale/sale/sale_new.php','M00'); INSERT INTO newsp_102_menu VALUES ('M000501','个人销售','','','M0005'); INSERT INTO newsp_102_menu VALUES ('M00050101','今天销售','','9_sale/unit/today_list.php','M000501'); INSERT INTO newsp_102_menu VALUES ('M00050102','历史销售','','9_sale/unit/history_list.php','M000501'); INSERT INTO newsp_102_menu VALUES ('M000502','部门销售','','','M0005'); INSERT INTO newsp_102_menu VALUES ('M00050201','今天销售','','9_sale\\dept\\today_list.php','M000502'); INSERT INTO newsp_102_menu VALUES ('M00050202','历史销售','','9_sale/dept/history_list.php','M000502'); INSERT INTO newsp_102_menu VALUES ('M000503','所有销售','','','M0005'); INSERT INTO newsp_102_menu VALUES ('M00050301','今天销售','','9_sale/all/today_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M00050302','历史销售','','9_sale/all/history_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M00050303','部门销售统计','','9_sale/all/history_stat_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M00050304','按日期统计【新】','','9_sale/all/date_stat_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M00050305','分销统计【新】','','8_sell/all/date_stat_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M00050306','按月统计【新】','','9_sale/all/month_stat_list.php','M000503'); INSERT INTO newsp_102_menu VALUES ('M000504','盘点【不建议使用】','','','M0005'); INSERT INTO newsp_102_menu VALUES ('M00050401','盘点录入','','14_check/batch/batch_new.php','M000504'); INSERT INTO newsp_102_menu VALUES ('M00050402','盘点对比','','14_check/query/lose_view.php','M000504'); INSERT INTO newsp_102_menu VALUES ('M00050403','盘点查看','','14_check/check/check_list.php','M000504'); INSERT INTO newsp_102_menu VALUES ('M00050404','完成盘点','','14_check/check/check1_list.php','M000504'); INSERT INTO newsp_102_menu VALUES ('M00050405','盘点结果查询','','14_check/query/result_list.php','M000504'); INSERT INTO newsp_102_menu VALUES ('M000505','库存管理','','','M0005'); INSERT INTO newsp_102_menu VALUES ('M00050501','仓库查看','','13_storage/query/storage_view.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050502','部门所有商品','','13_storage/query/all_view.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050503','分销查看','','13_storage/query/distract_view.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050504','部门存量查看','','13_storage/query/dept_view.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050505','部门调货查看','','','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050506','商品查询','','13_storage/query/project_select.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M00050507','库存清理','','13_storage/blankOut/blankOut_list.php','M000505'); INSERT INTO newsp_102_menu VALUES ('M0006','6我的空间','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000601','信息修改','','','M0006'); INSERT INTO newsp_102_menu VALUES ('M00060101','修改密码','','2_user/user/password_edit.php','M000601'); INSERT INTO newsp_102_menu VALUES ('M00060102','基本信息修改','','2_user/user/user_edit.php','M000601'); INSERT INTO newsp_102_menu VALUES ('M0007','1财务管理','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000701','流动资金管理','','','M0007'); INSERT INTO newsp_102_menu VALUES ('M00070101','资金收入','','framework/template/inner_frame.php?view_NP=fund_note_new.view&kind_type=FUND_IN&','M000701'); INSERT INTO newsp_102_menu VALUES ('M00070102','资金支出','','framework/template/inner_frame.php?view_NP=fund_note_new.view&kind_type=FUND_OUT&','M000701'); INSERT INTO newsp_102_menu VALUES ('M00070103','损益表','','framework/template/inner_frame.php?view_NP=fund_note_view.view&action_NP=fund_note_view_bf_NP.do&','M000701'); INSERT INTO newsp_102_menu VALUES ('M00070104','部门资金查询','','framework/template/inner_frame.php?view_NP=fund_note_list.view&','M000701'); INSERT INTO newsp_102_menu VALUES ('M000702','固定资产管理','','','M0007'); INSERT INTO newsp_102_menu VALUES ('M00070201','固定资产增加','','framework/template/inner_frame.php?view_NP=fixed_assets_new.view&','M000702'); INSERT INTO newsp_102_menu VALUES ('M00070202','固定减少 》流动资金','','framework/template/inner_frame.php?view_NP=fund_note_list.view&','M000702'); INSERT INTO newsp_102_menu VALUES ('M00070203','固定资产增加','','','M000702'); INSERT INTO newsp_102_menu VALUES ('M00070204','固定资产减少','','','M000702'); INSERT INTO newsp_102_menu VALUES ('M00070205','固定资产管理','','framework/template/inner_frame.php?view_NP=fixed_assets_list.view&','M000702'); INSERT INTO newsp_102_menu VALUES ('M000703','负债管理','','','M0007'); INSERT INTO newsp_102_menu VALUES ('M00070301','向其他人贷款','','framework/template/inner_frame.php?view_NP=owes_new.view&kind_type=FUND_IN&','M000703'); INSERT INTO newsp_102_menu VALUES ('M00070302','给其他人借款','','framework/template/inner_frame.php?view_NP=owes_new.view&kind_type=FUND_OUT&','M000703'); INSERT INTO newsp_102_menu VALUES ('M00070303','负债增加','','framework/template/inner_frame.php?view_NP=fixed_assets_new.view&','M000703'); INSERT INTO newsp_102_menu VALUES ('M00070304','负债减少','','','M000703'); INSERT INTO newsp_102_menu VALUES ('M00070305','负债综合信息','','framework/template/inner_frame.php?view_NP=owes_list.view& ','M000703'); INSERT INTO newsp_102_menu VALUES ('M000704','财务综合信息','','','M0007'); INSERT INTO newsp_102_menu VALUES ('M00070401','总账信息查询','acct_report.view',' framework/template/inner_frame.php?view_NP=acct_report.view& ','M000704'); INSERT INTO newsp_102_menu VALUES ('M00070402','月报表信息','','','M000704'); INSERT INTO newsp_102_menu VALUES ('M00070403','年报表信息','','','M000704'); INSERT INTO newsp_102_menu VALUES ('M00070404','自定义报表','','','M000704'); INSERT INTO newsp_102_menu VALUES ('M0008','4论坛','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000801','文章管理','','','M0008'); INSERT INTO newsp_102_menu VALUES ('M00080101','文章大类','','a1_bbs/articleType/bbs_article_type.php','M000801'); INSERT INTO newsp_102_menu VALUES ('M00080102','文章小类','','a1_bbs/articleKind/bbs_article_kind.php','M000801'); INSERT INTO newsp_102_menu VALUES ('M00080103','文章信息','','a1_bbs/article/article_list.php','M000801'); INSERT INTO newsp_102_menu VALUES ('M00080104','所有回复','','a1_bbs/repay/repay_list.php','M000801'); INSERT INTO newsp_102_menu VALUES ('M000802','会员管理','','','M0008'); INSERT INTO newsp_102_menu VALUES ('M00080201','会员信息','','a1_bbs/user/bbs_user_list.php','M000802'); INSERT INTO newsp_102_menu VALUES ('M000803','网站日志','','a1_bbs/log/bbs_log_list.php','M0008'); INSERT INTO newsp_102_menu VALUES ('M0009','3教学内容','','','M00'); INSERT INTO newsp_102_menu VALUES ('M000901','英语单词','','framework/template/inner_frame.php?view_NP=add_word.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000902','单词库','','framework/template/inner_frame.php?view_NP=word_list.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000903','增加我的单词','','framework/template/inner_frame.php?view_NP=add_myword.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000904','我的单词库','','framework/template/inner_frame.php?view_NP=myword_list.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000905','单词测试','','framework/template/inner_frame.php?view_NP=test.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000906','单词统计','','framework/template/inner_frame.php?view_NP=stat_list.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000907','给组增加单词','english','framework/template/inner_frame.php?view_NP=group_word_list.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000908','组测试','','framework/template/inner_frame.php?view_NP=group_test.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000909','建立测试','','framework/template/inner_frame.php?view_NP=add_test.view&','M0009'); INSERT INTO newsp_102_menu VALUES ('M000910','知识点','','','M0009'); INSERT INTO newsp_102_menu VALUES ('M00091001','js初级','','framework/template/inner_frame.php?view_NP=ken_list.view&','M000910'); INSERT INTO newsp_102_menu VALUES ('M0010','知识点','','','M00'); INSERT INTO newsp_102_menu VALUES ('M001001','管理知识点','','103_ken/ken/kind_list.php','M0010'); INSERT INTO newsp_102_menu VALUES ('M001002','js初级','','framework/template/inner_frame.php?view_NP=ken_list.view&','M0010'); CREATE TABLE newsp_201_dept ( dept_id varchar(10) NOT NULL, dept_name varchar(30) default NULL, dept_addr varchar(200) default NULL, dept_desc varchar(200) default NULL, dept_type int(11) default NULL, user_id varchar(30) default NULL, tel varchar(15) default NULL, mobile varchar(11) default NULL, images varchar(50) default NULL, is_go int(11), PRIMARY KEY(dept_id) ); INSERT INTO newsp_201_dept VALUES ('DPT0000000','仓库','系统所有,不容修改',NULL,0,'Jimmy',NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000001','总部','总部地址',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000002','超音速旌旗店','107国道武陵新村师院后门斜对面',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000003','超音速数码坚强店','赣南师范学院黄金校区北区坚强便利专柜',NULL,0,'admin',NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000004','超音速数码师院本部店','赣南师范学院本部中国连通体验店内',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000005','超音速数码理工本部店','江西理工大学本部中国移动体验店内',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000006','超音速数码理工应科院店','江西理工大学应用科学学院食堂对面',NULL,0,'admin',NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000007','江西理工大学西校区店','江西理工大学西校区中国联通体验店内',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000008','超音速环境工程学院店','江西环境工程学院商业街3号店面',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000009','超音速数码医学院店','赣南医学院移动营业厅内',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000010','超音速数码赣州卫校店','赣州卫校内',NULL,0,NULL,NULL,NULL,NULL,0); INSERT INTO newsp_201_dept VALUES ('DPT0000011','分销客户','县','2323',0,'feng.kejun','222','222','jpg20090608133644.jpg',1); INSERT INTO newsp_201_dept VALUES ('DPT0000012','english','教室',NULL,0,NULL,NULL,NULL,NULL,0); CREATE TABLE newsp_201_dept_menu ( dept_id varchar(30) NOT NULL, menu_id varchar(255) NOT NULL, desc1 varchar(255) default NULL, PRIMARY KEY (dept_id,menu_id) ); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000702',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070201',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070202',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070203',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070204',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070205',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000703',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070301',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070302',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070303',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070304',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070305',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000704',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M00070401',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M0009',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000901',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000902',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000903',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000904',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000905',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000001','M000906',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000002','M000702',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000002','M00070201',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000002','M00070202',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000003','M00070203',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000003','M00070204',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000004','M00070205',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000004','M000703',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000004','M00070301',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000005','M00070302',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000005','M00070303',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000005','M00070304',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000005','M00070305',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000005','M000704',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M0004',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000404',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00040401',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00040402',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000405',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00040501',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00040502',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00040504',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M0005',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000501',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00070401',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M0009',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000901',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000902',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000903',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000904',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000905',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000906',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000907',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000908',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000909',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M000910',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M00091001',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M0010',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M001001',NULL); INSERT INTO newsp_201_dept_menu VALUES ('DPT0000006','M001002',NULL); CREATE TABLE newsp_201_group ( group_id varchar(10) NOT NULL, group_name varchar(50) NOT NULL, group_desc varchar(200) default NULL, PRIMARY KEY (group_id) ); INSERT INTO newsp_201_group VALUES ('GRP0000000','系统管理','系统管理'); INSERT INTO newsp_201_group VALUES ('GRP0000009','营业员','营业员'); INSERT INTO newsp_201_group VALUES ('GRP0000010','店长','店长'); INSERT INTO newsp_201_group VALUES ('GRP0000011','总监','总监'); INSERT INTO newsp_201_group VALUES ('GRP0000012','财务','财务'); INSERT INTO newsp_201_group VALUES ('GRP0000013','文员','文员'); INSERT INTO newsp_201_group VALUES ('GRP0000014','仓管','仓管'); INSERT INTO newsp_201_group VALUES ('GRP0000015','总经理','总经理'); INSERT INTO newsp_201_group VALUES ('GRP0000016','系统管理','系统管理'); INSERT INTO newsp_201_group VALUES ('GRP0000017','网络管理员','负责网路资料的上传及更新'); INSERT INTO newsp_201_group VALUES ('GRP0000018','分销商','分销商'); INSERT INTO newsp_201_group VALUES ('GRP0000019','英语学习','英语学习'); CREATE TABLE newsp_201_group_menu ( group_id varchar(10) NOT NULL, menu_id varchar(30) NOT NULL, PRIMARY KEY (group_id,menu_id) ); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00010102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00010103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00010104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00010201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000106'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000107'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0002'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000203'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000204'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000205'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00020501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0003'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040203'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040204'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040205'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040403'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040406'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00040407'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00050507'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000701'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000702'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070203'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070204'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070205'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000703'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000704'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00070401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0009'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000901'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000902'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000903'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000904'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000905'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000906'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000907'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000908'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000909'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M000910'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M00091001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M0010'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M001001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000000','M001002'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00040401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00040501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00040502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00040504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000009','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00040504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M000701'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00070101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00070103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000010','M00070104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M0002'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00040501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00040502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00040504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000011','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000701'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M000702'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000012','M00070202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00040405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00050401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000013','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M0002'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00020101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040403'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040406'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00040503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000701'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00070101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00070103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M000702'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00070201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000014','M00070202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00010102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00010103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00010104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0002'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00020101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000203'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00020301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0003'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040203'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040204'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000403'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040402'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040403'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040404'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040406'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040407'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000405'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00040504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050306'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050502'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050505'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050506'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00050507'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0007'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000701'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000702'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M00070202'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M0008'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000015','M000802'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M0001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010201'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00010401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000106'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000107'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M0003'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000305'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00030501'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00030504'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M0005'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000503'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00050302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M0006'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000601'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00060101'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M00060102'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M0009'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000016','M000901'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M0001'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000103'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M00010301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M00010302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M0003'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000301'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000302'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000303'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000304'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M0004'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000401'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M00040104'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M00040105'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M0008'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000802'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000017','M000803'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M00'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M0009'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000901'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000902'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000903'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000904'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000905'); INSERT INTO newsp_201_group_menu VALUES ('GRP0000019','M000906'); CREATE TABLE newsp_201_user ( user_id varchar(30) NOT NULL, user_name varchar(100) default NULL, password varchar(300) default NULL, dept_id varchar(10) default NULL, add_time date, sex varchar(2) default '', email varchar(100) default NULL, userfrom varchar(10) default NULL, city varchar(14) default NULL, address varchar(100) default NULL, zip varchar(6) default NULL, tel_1 varchar(14) default NULL, edu varchar(10) default NULL, hangye varchar(20) default NULL, zhuanye varchar(20) default NULL, class varchar(4) default NULL, techang varchar(150) default NULL, intro varchar(250) default NULL, regyear varchar(12) default NULL, birth_day date default NULL, regtime date, photo varchar(100) default NULL, reg_ip varchar(100) default NULL, PRIMARY KEY (user_id) ); INSERT INTO newsp_201_user VALUES ('1002','1002','f98c40098db141038426f268802cf4962a39837b1dc8822619271dc32efd9343','DPT0000012',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('admin','admin',NULL,'DPT0000012',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('an.wanquan','安万全','f98c40098db141038426f268802cf4962a39837b1dc8822619271dc32efd9343','DPT0000012',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,'',NULL,'13667971896',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('chen.yao','陈瑶','be1e8f364a923059e5c25bbe3c2e12ae2682730f8d3004de4e1c5c49ca5ad394','DPT0000003',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,'赣南师范学院黄金校区坚强便利内',NULL,'15870048741',NULL,'销售.宣传.维护',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('feng.kejun','冯科军','f98c40098db141038426f268802cf4962a39837b1dc8822619271dc32efd9343','DPT0000012',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男','','陕西','西安','赣州市环城路12号新水晶宫楼上','','13207979899','','船长兼水手','','','','',NULL,NULL,NULL,'',NULL); INSERT INTO newsp_201_user VALUES ('feng.kejuncg','冯科军cg','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000000',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('feng.kejunjq',' 冯科军jq','5fa62fa21d8204b0ac6e7711fbf50ef451d6ab12e1370c89b78229b480f5aad3','DPT0000003',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('feng.kejunld','冯科军LD','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000002',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('feng.yunzheng','冯运政','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000001',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('feng.yunzheng01','冯运政FX','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000011',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('he.jinlan','何锦兰','9ec47f70a81ebd75f9cffeffa86497450091525f79ea8954c1c3febd51dd0687','DPT0000003',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,'赣南师范学院黄金校区坚强便利二楼左边',NULL,'15083797298',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('Jimmy','赵佳','0383d87f2da2c6b674eb6b4aaf8258356de04f1ad506e1823a71b4e07c7b5235','DPT0000001',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('lai.liping','赖立平','8ac99a5ae9e8601ffaebc7aff984fbfc94761ef234f92dc68a24ee262791c043','DPT0000005',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,'江西理工大学动感地带体验店超音速数码',NULL,'13707070442',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('lan.runhong','蓝润红','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000011',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('li.gongxiqu','理工西区','bbc7267af43eae431e670cebfeae95fa2819feef5d1d887cacefad11aa008d66','DPT0000007',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('newsp.admin','系统管理员','aeeeed3d492f82c54d5d5a67c2c38fd3940ff85b15bea085330cf05c1ebc34cb','DPT0000001',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男','','陕西','西安','深圳市','','13528820913','','系统维护','','','','',NULL,NULL,NULL,'',NULL); INSERT INTO newsp_201_user VALUES ('qin.yuanjin','覃远金','5ebda79658b598204abd4dd18d0dbbe3eaeefd7cc9e2c88f05cdd5f7c960edad','DPT0000001',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('qin.yuanjinhj','覃远金HJ','f98c40098db141038426f268802cf4962a39837b1dc8822619271dc32efd9343','DPT0000008',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('teng.feng','藤飞','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000005',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('wang.xiaokang','王小康','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000001',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('wang.xiaokangld','王小康ld','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000002',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('wen.wei','文伟','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000004',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('xiao.qian','肖茜','4fdbf6926d0f2526d78ad2596a587f84ea8bf223557b15203dcf94e096f628d8','DPT0000003',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('yin.xia','尹霞','ce2e98ecd6fbedbe473c9600c3a3f9189fe864ad60d6521a6c43e925f7a2c175','DPT0000003',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('yu.waide','虞外德','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000006',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO newsp_201_user VALUES ('zhang.yunyang','张云杨','cdd7e692858094371afa6affc351d71030aa93a45a1ffe132b3b97282b6a49b8','DPT0000005',DATE_FORMAT('2003/01/28 12:23:38','%Y-%m-%d-%T'),'男',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); CREATE TABLE newsp_201_group_user ( group_id varchar(10) NOT NULL, user_id varchar(30) NOT NULL, PRIMARY KEY (group_id,user_id) ); INSERT INTO newsp_201_group_user VALUES ('GRP0000000','admin'); INSERT INTO newsp_201_group_user VALUES ('GRP0000000','newsp.admin'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','an.wanquan'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','chen.yao'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','feng.kejun'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','feng.kejun01'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','feng.kejun02'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','feng.kejunjq'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','feng.kejunld'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','li.gongxiqu'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','lv.chongyang'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','nie.yunyuan'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','qin.yuanjinhj'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','xiao.qian'); INSERT INTO newsp_201_group_user VALUES ('GRP0000009','xiao.xiao'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','feng.kejun01'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','feng.yunzheng01'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','he.jinlan'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','jiang.qinghua'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','lai.liping'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','lai.yunping'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','nie.yunyuan'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','tan.yuanjin01'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','wang.xiaokang01'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','wang.xiaokangld'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','wen.wei'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','yu.waide'); INSERT INTO newsp_201_group_user VALUES ('GRP0000010','zhang.lifeng'); INSERT INTO newsp_201_group_user VALUES ('GRP0000011','wang.xiaokang'); INSERT INTO newsp_201_group_user VALUES ('GRP0000012','qin.yuanjin'); INSERT INTO newsp_201_group_user VALUES ('GRP0000013','luo.liping'); INSERT INTO newsp_201_group_user VALUES ('GRP0000014','feng.kejuncg'); INSERT INTO newsp_201_group_user VALUES ('GRP0000014','feng.yunzheng'); INSERT INTO newsp_201_group_user VALUES ('GRP0000015','feng.kejun'); INSERT INTO newsp_201_group_user VALUES ('GRP0000016','admin'); INSERT INTO newsp_201_group_user VALUES ('GRP0000017','Jimmy'); INSERT INTO newsp_201_group_user VALUES ('GRP0000019','1002'); INSERT INTO newsp_201_group_user VALUES ('GRP0000019','admin'); CREATE TABLE newsp_201_user_menu ( menu_id varchar(30) NOT NULL, user_id varchar(30) NOT NULL, PRIMARY KEY (menu_id,user_id) ); INSERT INTO newsp_201_user_menu VALUES ('M00','qin.yuanjin'); INSERT INTO newsp_201_user_menu VALUES ('M0001','an.wanquan'); INSERT INTO newsp_201_user_menu VALUES ('M000101','an.wanquan'); INSERT INTO newsp_201_user_menu VALUES ('M00010101','an.wanquan'); INSERT INTO newsp_201_user_menu VALUES ('M00010102','an.wanquan'); INSERT INTO newsp_201_user_menu VALUES ('M000504','luo.liping'); INSERT INTO newsp_201_user_menu VALUES ('M00050401','luo.liping'); INSERT INTO newsp_201_user_menu VALUES ('M00050403','luo.liping'); INSERT INTO newsp_201_user_menu VALUES ('M000506','feng.kejun01'); select * from newsp_102_menu; select * from newsp_201_dept; select * FROM newsp_201_dept_menu; select * from newsp_201_group; select * from newsp_201_group_menu; select * from newsp_201_group_user; select * from newsp_201_user; select * from newsp_201_user_menu;
1. 查询菜单表中第1到30行的记录。 2. 查询菜单表中menu_value没有值(即值为null或者空的)的第1到30行的记录 3. 查询菜单表中指定father_menu并且menu_value不为空的记录。 4. 查询菜单名有’管理’两个字的所有记录。 5. 查询菜单表中的所有father_menu,去掉重复的记录。 6. 查询在江西理工大学里面的所有部门。 7. 查询所有姓冯的用户，显示：用户id，姓名，加入时间。 8. 给定部门id，查询该部门的菜单信息，显示：菜单id，菜单名。 9. 显示内容：菜单id，菜单名，部门id，部门名称。条件：所有记录 10. 显示内容：部门号，部门名称。条件：该部门拥有菜单id为M0009，M000901至少其中一个的记录 11. 显示内容：用户名，部门名，条件：所有信息。要求：没有部门的不显示 12. 显示内容：用户名，部门名，条件：所有信息。要求：如果该用户没有部门则部门值为空。 13. 显示内容：部门名，部门长姓名；条件，所有信息。要求，没有部门长的显示为空。 14. 显示内容：组（岗位，群）名，菜单名，条件，所有信息。 15. 显示内容：组名，用户名，菜单名，条件：所有信息。 16. 显示内容：菜单id，菜单名称，条件：给定部门id，查询该部门的所有菜单 17. 显示内容：菜单id，菜单名称，条件：给定群（岗位）id，查询该群的所有菜单 18. 显示内容：菜单id，菜单名称，条件：给定用户名，查询该用户单独拥有的菜单信息。 19. 显示内容：菜单id，菜单名称，条件：给定用户名，查询该用户通过群（岗位），部门，自己单独拥有的所有菜单信息。Union 21. 显示内容：菜单id，菜单名称，条件：给定用户名，查询该用户通过群，部门，自己单独拥有的并且该菜单信息是菜单id为M0009的子菜单或者子孙菜单信息。 22. 显示内容：菜单id，菜单名，父菜单id，父菜单名，条件：给定用户名，查询该用户通过群，部门，自己单独拥有的并且父菜单id为M0009的菜单信息 23. 显示内容，群名，菜单名，条件：根据用户id查询该用户对应群的所有菜单信息。 24. 显示内容:用户名，条件：给定一个菜单id，查询拥有这个菜单的用户名（同个单独拥有，部门拥有，岗位拥有的都算） 25. 显示内容：菜单id，菜单名，父菜单id，父菜单名。条件：给定某个部门id，查询该部门长能看到的所有菜单。 26. 显示内容：菜单id，菜单名。条件：给定一个用户名，查询该用户通过岗位和部门都拥有的菜单。 27. 显示内容：菜单id，菜单名。条件：给定一个用户名,查询该用户通过岗位和部门拥有的不相同菜单。 28. 显示内容：用户名，条件：查询拥有两个岗位上的用户以名。 29. 显示内容：用户名：条件：查询部门长不是属于本部门的用户名。 30. 显示内容：用户名，条件：这个用户是部门长，但是不是他本部门的部门长，自己拥有两个岗位以上的用户名。 31. 显示内容：菜单名，条件：给定两个用户名，查询他们都拥有的菜单名。 32. 显示内容：用户名，条件：给定两个岗位，查询同时在这两个岗位的用户名。 33. 显示内容：菜单数，条件：给定一个用户名，查询这个用户一共拥有多少个菜单功能。 34. 显示内容：用户名，条件：显示同时拥有以下菜单的用户名（M0001，M0002，M0007，M0009，M0005） 35. 显示：用户名，部门名， 条件：用户加入时间为2009年的，要求：如果该用户没有部门，此列显示空即可。
