1 / 12

MySQL

MySQL. Software Requirement. MySQL server 5.0 Download: http://dev.mysql.com/downloads/mysql/ (MySQL Community Server 5. up) Client side browser: http://www.webyog.com/en/ Aqua data studio (free for IU): http://iuware.iu.edu/title.aspx?id=585. How to start. Start your MySQL server

hchadwick
Télécharger la présentation

MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL

  2. Software Requirement • MySQL server 5.0 • Download: http://dev.mysql.com/downloads/mysql/ (MySQL Community Server 5. up) • Client side browser: • http://www.webyog.com/en/ • Aqua data studio (free for IU): http://iuware.iu.edu/title.aspx?id=585

  3. How to start • Start your MySQL server • Control panelAdministrative toolsservicesMySQL (click start) • Open SQL Yog

  4. Create Table Schema CREATE TABLE student ( student_id INT, name VARCHAR(25), major VARCHAR(15), gpa DECIMAL(6,3), PRIMARY KEY (student_id) ); CREATE TABLE course ( course_id VARCHAR(15), name VARCHAR(25), department_id VARCHAR(10), PRIMARY KEY (course_id) ); CREATE TABLE enroll ( student_id INT, course_id VARCHAR(15), grade CHAR(2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );

  5. Add instances to tables INSERT INTO student VALUES (101, 'Bill', 'CIS', 3.45); INSERT INTO student VALUES (102, 'Mary', 'CIS', 3.10); INSERT INTO student VALUES (103, 'Sue', 'MKT', 3.90); INSERT INTO course VALUES ('CIS3100', 'Database', 'CIS'); INSERT INTO course VALUES ('CIS3400', 'Network I', 'CIS'); INSERT INTO course VALUES ('CIS3500', 'Network II', 'CIS'); INSERT INTO course VALUES ('MKT3000', 'Advertizing', 'MKT'); INSERT INTO course VALUES ('MKT3200', 'Marketing I', 'MKT'); INSERT INTO course VALUES ('MKT4200', 'Marketing II', 'MKT'); INSERT INTO enroll VALUES (101, 'CIS3100', 'A'); INSERT INTO enroll VALUES (101, 'CIS3500', 'B+'); INSERT INTO enroll VALUES (102, 'CIS3100', 'A-'); INSERT INTO enroll VALUES (102, 'CIS3400', 'A'); INSERT INTO enroll VALUES (103, 'MKT3000', 'A'); INSERT INTO enroll VALUES (103, 'MKT3200', 'B'); INSERT INTO enroll VALUES (103, 'MKT4200', 'B+');

  6. Example

  7. SQL: Creating/Dropping table • Create Table CREATE TABLE student1 ( student_id INT, name VARCHAR(25), major VARCHAR(15), gpa DECIMAL(6,3), PRIMARY KEY (student_id) ); • Drop table DROP TABLE student1;

  8. Modifying table data INSERT INTO student VALUES (104, 'Ying', 'SLIS', 3.5); SELECT * FROM student; UPDATE student SET name=‘Ding’ WHERE student_id=104; DELETE FROM student WHERE student_id=104;

  9. Altering tables ALTER TABLE student ADD Available CHAR(1); ALTER TABLE student DROP Available;

  10. Queries SELECT * FROM course LIMIT 3; SELECT * FROM enroll WHERE grade=‘A’; SELECT * FROM student WHERE student.student_id=(SELECT enroll.student_id FROM enroll WHERE grade='A-'); SELECT * FROM student WHERE student.student_id IN(SELECT enroll.student_id FROM enroll WHERE grade='A'); SELECT student.name FROM student, enroll WHERE student.student_id=enroll.student_id AND enroll.grade=‘A’;

  11. Sorting and Grouping SELECT * FROM enroll ORDER BY grade, course_id; SELECT major, max(gpa) FROM student GROUP BY major HAVING max(gpa)>3.40; SELECT DISTINCT grade FROM enroll;

  12. Joining tables SELECT student.name, enroll.course_id, enroll.grade FROM student INNER JOIN enroll ON student.student_id=enroll.student_id; SELECT * FROM student LEFT JOIN enroll ON student.student_id=enroll.student_id; SELECT * FROM student RIGHT JOIN enroll ON student.student_id=enroll.student_id;

More Related