· 9 years ago · Jul 21, 2017, 08:50 AM
1CREATE TABLE IF NOT EXISTS `teledata_staff` (
2 `staff_id` int(11) NOT NULL,
3 `last_name` varchar(30) DEFAULT NULL,
4 `suffix` varchar(3) DEFAULT NULL,
5 `name_title` varchar(10) DEFAULT NULL,
6 `first_name` varchar(20) DEFAULT NULL,
7 `middle` varchar(20) DEFAULT NULL,
8 `empl_id` varchar(32) DEFAULT NULL,
9 `dept_id` int(11) DEFAULT NULL,
10 `job_position` varchar(50) DEFAULT NULL,
11 `bldg_id` int(11) DEFAULT NULL,
12 `room` varchar(50) DEFAULT NULL,
13 `phone` varchar(50) DEFAULT NULL,
14 `email` varchar(20) DEFAULT NULL,
15 `email_machine` varchar(50) DEFAULT NULL,
16 `postal` varchar(10) DEFAULT NULL,
17 `last_updated` datetime DEFAULT NULL,
18 `listed` varchar(1) DEFAULT NULL,
19 `cellphone` varchar(50) DEFAULT NULL,
20 PRIMARY KEY (`staff_id`)
21) ENGINE=MyISAM DEFAULT CHARSET=latin1;
22
23
24-- View creation
25-- Staff View
26CREATE VIEW staff AS
27SELECT
28 teledata_staff.staff_id,
29 teledata_staff.last_name,
30 teledata_staff.first_name,
31 teledata_staff.middle,
32 teledata_staff.name_title,
33 teledata_staff.suffix,
34 teledata_staff.job_position,
35 teledata_staff.room,
36 teledata_staff.phone,
37 CONCAT(teledata_staff.email, teledata_staff.email_machine) AS email,
38 teledata_department.name AS department,
39 teledata_department.dept_id,
40 teledata_organization.name AS organization,
41 teledata_organization.org_id,
42 teledata_ucfbldg.name AS building,
43 teledata_ucfbldg.bldg_id
44FROM
45 teledata_staff,
46 teledata_department,
47 teledata_organization,
48 teledata_ucfbldg
49WHERE
50 teledata_staff.dept_id = teledata_department.dept_id AND
51 teledata_staff.bldg_id = teledata_ucfbldg.bldg_id AND
52 teledata_department.org_id = teledata_organization.org_id
53;
54
55
56
57-- Why come no match score?
58SELECT
59 MATCH(staff.first_name, staff.last_name) AGAINST ('Tom') as score,
60 CONCAT(IFNULL(staff.first_name, ''), ' ', IFNULL(staff.last_name, '')) AS name,
61 staff.email
62FROM staff
63WHERE staff.first_name = 'Tom'
64
65score name email
660 Tom Carbone tcarbone@fiea.ucf.edu
670 Tom Hall tvhall@mail.ucf.edu
680 Tom Hope thope@mail.ucf.edu
690 Tom Campana tcampana@mail.ucf.edu
700 Tom Hurter thurter@mail.ucf.edu
710 Tom Logsdon tlogsdon@mail.ucf.edu
720 Tom Mullin tmullin@mail.ucf.edu
730 Tom O'Neal oneal@mail.ucf.edu
740 Tom O'Neal NULL
750 Tom O'Neal oneal@mail.ucf.edu
760 Tom O'Neal oneal@mail.ucf.edu
770 Tom Ehren tehren@mail.ucf.edu
780 Tom O'Neal NULL
790 Tom O'Neal NULL
800 Tom Nelson tanelson@mail.ucf.edu
810 Tom Owens jtowens@mail.ucf.edu
820 Tom Owens jtowens@mail.ucf.edu
830 Tom Snyder tsnyder@athletics.ucf.edu
840 Tom O'Neal oneal@mail.ucf.edu
850 Tom Owens jtowens@mail.ucf.edu
860 Tom O'Neal oneal@mail.ucf.edu