MySQ4e-01

MySQL P A R T 1 Chapter 01 Chapter 02 Chapter 03 Chapter 04 Chapter 05

01 Chapter MySQL (RDBMS, Relational Database Management System) MySQL SQL (SQL, Structured Query Language).,. MySQL.,. MySQL SQL. MySQL., 1.2 sampdb. 1.1 MySQL. MySQL., MySQL, 1.2...,,,,,,,,,.,,.,..,,..,

Chapter 01 3....,...,.,.,.... ( Publish or Perish, ).,...,... (,,,, ).,.., ( ).,.,.,.,.,

4 PART 1!.. MySQL?.,...,,..,, MySQL...,..., 6.,. SELECT last_name, first_name, last_visit FROM patient WHERE last_visit < DATE_SUB(CURDATE(),INTERVAL 6 MONTH);, (,.., ). (, )..,,.,... (,

Chapter 01 5, )....,,. MySQL...,..,.,,.....,,.,.,..., ( )...,.,.,.,...,

6 PART 1 MySQL., MySQL. MySQL? MySQL,. 1.2. MySQL.... ( )....,,.,,.,.......,.,,.,. 1.2.1.,.

Chapter 01 7.....,..., ( )..,.. (,,,, ).,...,.,.,,.,., ( ) (MySQL ),.,.,,.,.,.,

8 PART 1,..,,..,.,.!.,.?,.,..,..,., MySQL. MySQL, MySQL. Perl DBI(, DataBase Interface) Perl MySQL. Perl,., Perl RTF(Rich Text Format), RTF. PHP. PHP,. MySQL. PHP Apache ( ),. MySQL,.,..

Chapter 01 9,.., MySQL., MySQL. ( www.mysql.com ). (Perl, DBI, PHP, Apache),.. UNIX( BSD UNIX, Linux, Mac OS X UNIX ) Windows. Unix Windows. 1.2.2.,. MySQL... A, B, C, D, F.,...,., MySQL.,,,. 1.2.3,.. MySQL.

10 PART 1,..,,.,., -. 1.3.,.,,...,.,. 1.3.1, MySQL (RDBMS, Relational Database Management System).. (RDBMS DB )...... ( MS ),,,. (Relational R ) DBMS, ( )., DBMS. (,

Chapter 01 11., RDBMS.)...,., ([ 1.1] ). company,

12 PART 1,,,. ad,,, (hit). hit,.., company.,, hit.,., 7 14 Pickles,. 1. company (, Pickles ) (, 14 ). 2. ad,., 48 101. 3. ad, hit,. 48 101.?.,,..,????, 3, -, (, C.J. Date E.F. Codd ). 1.3.2 MySQL, SQL(Structured Query Language). SQL,. SQL,., SQL.,, MySQL., MySQL

Chapter 01 13, MySQL. CREATE TABLE company ( company_name CHAR(30), company_num INT, address CHAR(30), phone CHAR(12) ); SQL, SQL., CREATE TABLE.. 1.3.3 MySQL MySQL /,. mysqld.,.. MySQL. ( Linux RPM, RPM,.). mysql,., mysqldump, mysqladmin.., MySQL. C. C, Perl, PHP, Python, Java, Ruby.. (GUI), http://www.mysql.com /products/tools/. MySQL /.,.

14 PART 1 (concurrency control).,.,.,.. MySQL...,.?. MySQL,.. (Sally) ( ), (Phil)..,. mysqld /, MySQL, libmysqld, MySQL., (embedded). /.,.,. > TIP

Chapter 01 15 1.4. MySQL! MySQL.,,,,.. RDBMS SQL, SQL MySQL SQL., MySQL /,,. mysql, SQL,,. mysql MySQL,. mysqlimport mysqlshow. sampdb,., sampdb MySQL.. MySQL,. MySQL. 1.4.1 ( sampdb sampdb ).. A., sampdb..

16 PART 1 MySQL MySQL bin. A PATH. 1.4.2,. MySQL. MySQL.. MySQL MySQL..,.,. MySQL, A. (ISP, Internet Service Provider), MySQL. ISP MySQL, MySQL. MySQL, MySQL ( MySQL, ).,. MySQL,. MySQL root CREATE USER GRANT MySQL. MySQL,, sampadm secret ( ). % mysql -p -u root Enter password: ****** mysql> CREATE USER 'sampadm'@'localhost' IDENTIFIED BY 'secret'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT ALL ON sampdb.* TO 'sampadm'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql -p mysql root MySQL. ******

Chapter 01 17. MySQL root., password:., root. CREATE USER GRANT, MySQL, 12 MySQL. GRANT MySQL. sampadm secret sampdb. GRANT.. MySQL, localhost., asp.snake.net GRANT. Mysql> CREATE USER 'sampadm'@'asp.snake.net' IDENTIFIED BY 'secret' ; mysql> GRANT ALL ON sampdb.* TO 'sampadm'@'asp.snake.net' ; MySQL., sampadm, secret, sampdb MySQL,,. 1.4.3, (UNIX Windows DOS ) mysql.. % mysql options %. UNIX. $. Windows C:\>. mysql options,. % mysql -h host_name -p -u user_name mysql,.. -h host_name ( : --host=host_name). MySQL mysql,.

18 PART 1 -u user_name ( : --user=user_name) MySQL. UNIX MySQL,. mysql MySQL. Windows ODBC,. -u USER., sampadm set. C:\> set USER=sampadm USER,. -p ( : --password) mysql MySQL Enter password:.,. % mysql -h host_name -p -u user_name Enter password: Enter password:, ( ). MySQL UNIX Windows. -p, mysql. ( : --password=your_pass) -pyour_pass.,.,., -p. -h -u, h -u. MySQL sampadm secret. MySQL, -h mysql. % mysql -p -u sampadm Enter password: ******, mysql Enter password:, (secret ****** ).

Chapter 01 19, mysql mysql>.. % mysql -p -u sampadm Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13762 server version: 5.0.60-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>, -h. cobra.snake.net,. % mysql -h cobra.snake.net -p -u sampadm mysql, -h, -u, -p,. Mysqlshow MySQL. quit. mysql> quit Bye \q (UNIX ) Ctrl-D. MySQL (, ).,,., ( ) MySQL. mysql. 1.5 mysql... 1.4.4. mysql. mysql,. ( ; ). mysql.,

20 PART 1 mysql. mysql, mysql.. mysql> SELECT NOW(); +---------------------+ NOW() +---------------------+ 2008-03-21 10:51:23 +---------------------+ 1 row in set (0.00 sec) SQL \g( go ). mysql> SELECT NOW()\g +---------------------+ NOW() +---------------------+ 2008-03-21 10:51:28 +---------------------+ 1 row in set (0.00 sec) \G,. mysql> SELECT NOW(), USER(), VERSION()\G *************************** 1. row *************************** NOW(): 2008-03-21 10:51:34 USER(): sampadm@localhost VERSION(): 5.0.60-log 1 row in set (0.03 sec), \G \G. mysql SQL.,. mysql,. mysql> SELECT NOW(), -> USER(), -> VERSION() -> ; +---------------------+-------------------+------------+ NOW() USER() VERSION() +---------------------+-------------------+------------+ 2008-03-21 10:51:37 sampadm@localhost 5.0.60-log +---------------------+-------------------+------------+

Chapter 01 21, mysql> ->. mysql,., mysql.,, MySQL, mysql. (mysql. F MySQL )., \c.,. mysql> SELECT NOW(), -> VERSION(), -> \c mysql> mysql mysql>.,. mysql> SELECT NOW();SELECT USER();SELECT VERSION(); +---------------------+ NOW() +---------------------+ 2008-03-21 10:52:31 +---------------------+ +-------------------+ USER() +-------------------+ sampadm@localhost +-------------------+ +------------+ VERSION() +------------+ 5.0.60-log +------------+,,,.. SELECT USER(); select user(); SeLeCt UsEr();

22 PART 1, SQL,,,.,.,. mysql.., myfile.sql,. (.) % mysql < myfile.sql. SQL.sql. mysql 1.4.7 sampdb. INSERT mysql.. mysql>,.,. (. mysql, ). 1.4.5 sampdb,,,.. 1. ( ). 2.. 3.,,,..,..,,,

Chapter 01 23,., mysql CREATE DATABASE. mysql> CREATE DATABASE sampdb; sampdb., ( )?..,. mysql> SELECT DATABASE(); +------------+ DATABASE() +------------+ NULL +------------+ NULL. sampdb, USE. mysql> USE sampdb; mysql> SELECT DATABASE(); +------------+ DATABASE() +------------+ sampdb +------------+ mysql.,. % mysql sampdb,.,., sampadm sampdb. % mysql -p -u sampadm sampdb MySQL,. % mysql -h cobra.snake.net -p -u sampadm sampdb, mysql sampdb. mysql USE sampdb mysql>.

24 PART 1 1.4.6 sampdb.,,., - (E-R),.,.,,,....,.. ( )..,. president,...,, first name last name.,. first name, last name. last name, first name.., last name,. last name., president last name first name. first name middle name. middle name,. Bush, George W.

Chapter 01 25 George W. Bush first name middle name,.. ( Jimmy Carter) Jr..?, James E. Carter, Jr., Carter, James E., Jr.. Jr. first name last name,.,.,.,.,.,.,.,... MySQL NULL,. member president., member. president., last_name, first_name, suffix.,. League ID,, ( MySQL, League., member )..,,. ( 1, 2, 3, 5 ),.,., NULL

26 PART 1,..... ( ). street, city, state, zip..,,.,., Zip, (province).,.,... ( ID.,.). CREATE TABLE,. CREATE TABLE tbl_name (column_specs); tbl_name. column_specs,.. 5. president CREATE TABLE. CREATE TABLE president ( last_name VARCHAR(15) NOT NULL, first_name VARCHAR(15) NOT NULL, suffix VARCHAR(5) NULL, city VARCHAR(20) NOT NULL, state VARCHAR(2) NOT NULL, birth DATE NOT NULL, death DATE NULL );

Chapter 01 27. sampdb create_president.sql., sampdb mysql. % mysql sampdb, CREATE TABLE, mysql.,.. president, sampdb create _presidnet.sql. sampdb,. sampdb. % mysql sampdb < create_president.sql mysql, (,, ). CREATE TABLE, ( ),. president, VARCHAR DATE. VARCHAR(n), n. n. state VARCHAR(2),.. DATE..,. MySQL CCYY-MM-DD, CC, YY, MM, DD,,,. ANSI SQL ( ISO 8601 )., July 18, 2005 MySQL 2005-07-18, 07-18- 2005 18-07-2005. president NULL( ) NOT NULL( ). NOT NULL. NULL, suffix( ) death( ). member, CREATE TABLE. CREATE TABLE member (

28 PART 1 ); member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (member_id), last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, suffix VARCHAR(5) NULL, expiration DATE NULL, email VARCHAR(100) NULL, street VARCHAR(50) NULL, city VARCHAR(50) NULL, state VARCHAR(2) NULL, zip VARCHAR(10) NULL, phone VARCHAR(20) NULL, interests VARCHAR(255) NULL mysql sampdb. Sampdb member CREATE TABLE create_member.sql.. % mysql sampdb < create_member.sql, member. member_id expiration. member_id. MySQL, AUTO_INCREMENT., member_id. INT ( ). UNSIGNED. NOT NULL (ID ). AUTO_INCREMENT MySQL.. AUTO_INCREMENT., member member_id ( NULL), MySQL. NULL. MySQL AUTO_INCREMENT ID. PRIMARY KEY member_id (unique).

Chapter 01 29 ID, ID., MySQL AUTO_INCREMENT (unique),. ( PRIMARY KEY NOT NULL. NOT NULL member_id MySQL.) AUTO_INCREMENT PRIMARY KEY, ID.. (AUTO_INCREMENT, 3 ). expiration DATE. NULL. NULL., expiration NULL,. MySQL,. mysql president. mysql> DESCRIBE president; +------------+-------------+------+------+---------+-------+ Field Type Null Key Default Extra +------------+-------------+------+------+---------+-------+ last_name varchar(15) NO first_name varchar(15) NO suffix varchar(5) YES NULL city varchar(20) NO state varchar(2) NO birth date NO death date YES NULL +------------+-------------+------+------+---------+-------+ DESCRIBE member, mysql member. DESCRIBE,. MySQL. INSERT LOAD DATA. DESCRIBE. DESC EXPLAIN SHOW.. DESCRIBE president; DESC president; EXPLAIN president; SHOW COLUMNS FROM president; SHOW FIELDS FROM president;

30 PART 1., SHOW LIKE. mysql> SHOW COLUMNS FROM president LIKE %name ; +------------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +------------+-------------+------+-----+---------+-------+ last_name varchar(15) NO first_name varchar(15) NO +------------+-------------+------+-----+---------+-------+ %, 1.4.9. SHOW FULL COLUMN SHOW COLUMN.,. MySQL SHOW. SHOW TABLES, sampdb,. mysql> SHOW TABLES; +------------------+ Tables_in_sampdb +------------------+ member president +------------------+ SHOW DATABASES. mysql> SHOW DATABASES; +--------------------+ Database +--------------------+ information_schema menagerie mysql sampdb test +--------------------+, information_schema sampdb.information _schema., sampdb. test MySQL. mysql MySQL. mysqlshow SHOW

Chapter 01 31. Mysqlshow username, password, hostname. mysql., mysqlshow. % mysqlshow +--------------------+ Databases +--------------------+ information_schema menagerie mysql sampdb test +--------------------+, mysqlshow. % mysqlshow sampdb Database: sampdb +-----------+ Tables +-----------+ member president +-----------+ mysqlshow, SHOW COLUMNS.,. [ 1.2]... ID ( ).

32 PART 1. 9 3, 6, 16, 23 9 9 10 1. score.?.,,,. [ 1.3] (MySQL CCYY-MM-DD ).,., [ 1.3],.. ( ),., [ 1.4] T Q.,.. 9 23 Q, 10 1 T..,.?. score,. grade_event ( ).,. score

Chapter 01 33 grade_event. [ 1.5] 9 23 score. grade_event,..,. score,.,.,,,???.... ([ 1.2] ).?., ( ).

34 PART 1. score.? T Q,.. grade_event.,,....,.,.,. [ 1.5] grade_event score grade_event.,., score grade_event, score grade_event..?.,.,.,.,,...?,.,. 1. grade_event., ID, event_id (, [ 1.2]

Chapter 01 35. ID. event ID, ). 2. score, event ID. [ 1.6]., event ID event, grade_event. grade_event event ID.,, ( )., [ 1.6]. score. [ 1.4], score. [ 1.6] score. event ID score?...,,. :? MySQL.,,., MySQL,.

36 PART 1.,.,.., (, ),,.,...,.., score, event ID.. event ID grade_event.,.. event ID. MySQL.,,., event ID MySQL., MySQL. 2008 9 23.. SELECT score.name, grade_event.date, score.score, grade_event.category FROM score, INNER JOIN grade_event ON score.event_id = grade_event.event_id WHERE grade_event.date = '2008-09-23'?,,, score grade_event ( ).. +--------+------------+-------+------+ name date score type +--------+----------- +-------+------+ Billy 2008-09-23 15 Q

Chapter 01 37 Missy 2008-09-23 14 Q Johnny 2008-09-23 17 Q Jenny 2008-09-23 19 Q +--------+------------+-------+------+?. [ 1.4]. event ID., MySQL.,.,,.,.?,., SQL.,. 1.5 mysql..,.. score, student ID ( Name ID )., student, name student_id ([ 1.7]).?. ID ( event ID ).,.

38 PART 1 SELECT student.name, grade_event.date, score.score, grade_event.category FROM grade_event INNER JOIN score INNER JOIN student ON grade_event.event_id = score.event_id AND score.student_id = student.student_id WHERE grade_event.date = '2008-09-23';,..,,.. [ 1.7] student..,.... ID ([ 1.8] ).. MySQL.,. student CREATE TABLE. CREATE TABLE student ( name VARCHAR(20) NOT NULL, sex ENUM ('F','M') NOT NULL, student_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (student_id) ) ENGINE=InnoDB; CREATE TABEL..

Chapter 01 39 mysql. % mysql sampdb < create_student.sql CREATE TABLE student, name, sex, student_id. name 20. name. first name last name. (?. ). sex. ENUM(, enumeration),., F M. ENUM. CHAR(1), ENUM., DESCRIBE. ENUM, MySQL. mysql> DESCRIBE student sex ; +-------+---------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+---------------+------+-----+---------+-------+ sex enum( F, M ) NO +-------+---------------+------+-----+---------+-------+ ENUM. ENUM(female, male ). student_id ID., ID. AUTO_INCREMENT ID, member member_id., ID, student_id AUTO_INCREMENT. NULL ID PRIMARY KEY. CREATE TABLE ENGINE., MySQL.. MySQL, 2.6.1. ENGINE, MySQL MyISAM.

40 PART 1 ISAM, MySQL., (president member) ENGINE MyISAM. (.), InnoDB. InnoDB. MySQL. Score. student ID grade event ID student, grade_event score., absence. student id student absence..,.. grade_event. CREATE TABLE grade_event ( date DATE NOT NULL, category ENUM('T','Q') NOT NULL, event_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (event_id) ) ENGINE = InnoDB;, mysql CREATE TABLE. % mysql sampdb < create_grade_event.sql Date MySQL DATE, CCYY-MM-DD ( ). category. student sex, type. T Q, (test) (quiz). event_id PRIMARY KEY AUTO_INCREMENT, student student_id. AUTO_INCREMENT, event ID. student student_id, ID ID. NOT NULL,.

Chapter 01 41 score. CREATE TABLE score ( student_id INT UNSIGNED NOT NULL, event_id INT UNSIGNED NOT NULL, score INT NOT NULL, PRIMARY KEY (event_id, student_id), INDEX (student_id), FOREIGN KEY (event_id) REFERENCES grade_event (event_id), FOREIGN KEY (student_id) REFERENCES student (student_id) ) ENGINE = InnoDB; FOREIGN KEY. mysql. % mysql sampdb < create_score.sql student_id event_id INT( ). student grade_event,. student_id event_id. PRIMARY KEY.. student_id event_id. score. event_id ( ), student_id ( ). ID FOREIGN KEY. FOREIGN KEY REFERENCE score. event_id grade_event event_id., score student_id student student_id. PRIMARY KEY score. FOREIGN KEY grade_event student ID., student_id? FOREIGN KEY,. event_id FOREIGN KEY, PRIMARY KEY. student_id FOREIGN KEY, student_id PRIMARY KEY., student_id. InnoDB,

42 PART 1 ( 2.14.1 ).. absence. CREATE TABLE absence ( student_id INT UNSIGNED NOT NULL, date DATE NOT NULL, PRIMARY KEY (student_id, date), PRIMARY KEY (student_id) REFERENCE student (student_id) ) ENGINE = InnoDB; mysql. % mysql sampdb < create_absence.sql student_id date NOT NULL.,.,! absence, student_id student student_id.. ID.,. score grade_event, student score., absence student, student absence.,. score grade_event, score, absence student. NOTE

Chapter 01 43 1.4.7,..,, 1.4.9, tbl_name. SELECT * FROM tbl_name;,. mysql> SELECT * FROM student; Empty set (0.00 sec), mysql,.. INSERT,, mysql INSERT LOAD DATA mysqlimport... 1.4.8 sampdb,,.,,. (,.) INSERT. INSERT SQL. INSERT.. INSERT INTO tbl_name VALUES(value1,value2,...); : mysql> INSERT INTO student VALUES('Kyle','M',NULL);

44 PART 1 mysql> INSERT INTO event VALUES('2008-09-03','Q',NULL); VALUES, ( CREATE TABLE )., DESCRIBE tbl_name. MySQL,. student event AUTO_INCREMENT NULL. AUTO_INCREMENT, MySQL. MySQL INSERT. INSERT INTO tbl_name VALUES(...),(...),... ; : mysql> INSERT INTO student VALUES('Avery','F',NULL),('Nathan','M',NULL); INSERT,... mysql> INSERT INTO student VALUES('Avery','F',NULL,'Nathan','M',NULL); ERROR 1136 (21S01): Column count doesn t match value count at row 1. INSERT INTO tbl_name (col_name1,col_name2,...) VALUES(value1,value2,...); : mysql> INSERT INTO member (last_name,first_name) VALUES('Stein','Waldo'); MySQL INSERT.,. mysql> INSERT INTO student (name,sex) VALUES('Abby','F'),('Joseph','M');., member_id student_id, MySQL NULL (member_id student_id AUTO_INCREMENT, NULL, ). SET. value( ) col_name=value

Chapter 01 45 INSERT INTO tbl_name SET col_name1=value1, col_name2=value2,... ; : mysql> INSERT INTO member SET last_name='stein',first_name='waldo' SET. INSERT. INSERT, score, absence. grade_event student ID. mysql> INSERT INTO score (event_id,student_id,score) VALUES(9999,9999,0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sampdb`.`score`, CONSTRAINT `score_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `grade_event` (`event_id`)) mysql> INSERT INTO absence SET student_id=9999, date='2008-09-16'; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sampdb`.`absence`, CONSTRAINT `absence_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)).., sampdb presidnet INSERT insert _president.sql.. % mysql sampdb < insert_president.sql mysql, SOURCE. mysql> SOURCE insert_president.sql; INSERT, LOAD DATA mysqlimport. LOAD DATA. mysql. mysql> LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member; member.txt, member. (member.txt sampdb.), LOAD DATA ( )..

46 PART 1. E SQL LOAD DATA. LOAD DATA LOCAL. LOCAL,, MySQL.. LOAD DATA LOCAL, LOCAL. ERROR 1148 (42000): The used command is not allowed with this MySQL version, --local-infile mysql.,. % mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;, LOCAL. 12. mysqlimport. mysqlimport, LOAD DATA. % mysqlimport --local sampdb member.txt mysql,., mysqlimport LOAD DATA member.txt member. mysqlimport (. )., mysqlimport member.txt president.txt member president. mysqlimport., member1.txt member2.txt member, mysqlimport member1 member2. member, member.1.txt member.2.txt member.txt1 member.txt2. 1.4.8, sampdb. sampdb mysql,. % mysql sampdb

Chapter 01 47 mysql> source create_member.sql; mysql> source create_president.sql; mysql> source insert_member.sql; mysql> source insert_president.sql; mysql> DROP TABLE IF EXISTS absence, score, grade_event, student; mysql> source create_student.sql; mysql> source create_grade_event.sql; mysql> source create_score.sql; mysql> source create_absence.sql; mysql> source insert_student.sql; mysql> source insert_grade_event.sql; mysql> source insert_score.sql; mysql> source insert_absence.sql; ( ), UNIX. % sh init_all_tables.sh sampdb Windows. C:\> init_all_tables.bat sampdb. 1.4.9,. SELECT,.. SELECT * FROM president;. SELECT birth FROM president WHERE last_name = 'Eisenhower'; SELECT ( ),., SELECT., (, ). SELECT. SELECT what to retrieve FROM table or tables WHERE conditions that data must satisfy; SELECT,. (FROM WHERE), GROUP BY, ORDER BY,

48 PART 1 LIMIT. SQL. SELECT. FROM,.,., FROM. mysql> SELECT 2+2, 'Hello, world', VERSION(); +-----+--------------+----------------+ 2+2 Hello, world VERSION() +-----+--------------+----------------+ 4 Hello, world 5.0.60-log +-----+--------------+----------------+ FROM,. SELECT *,. student. mysql> SELECT * FROM student; +-----------+-----+------------+ name sex student_id +-----------+-----+------------+ Megan F 1 Joseph M 2 Kyle M 3 Katie F 4... MySQL. DESCRIBE student ( )..,. mysql> SELECT name FROM student; +-----------+ name +-----------+ Megan Joseph Kyle Katie.... SELECT * FROM student,.

Chapter 01 49 mysql> SELECT name, sex, student_id FROM student; +-----------+-----+------------+ name sex student_id +-----------+-----+------------+ Megan F 1 Joseph M 2 Kyle M 3 Katie F 4.... SELECT name, student_id FROM student; SELECT student_id, name FROM student;,,. MySQL. join. 1.4.9. MySQL,. SELECT name, student_id FROM student; SELECT NAME, STUDENT_ID FROM student; SELECT name, student_id FROM student;,.. Windows, Windows. UNIX UNIX. HFS+ UFS Mac OS X, HFS+, UFS. MySQL, 11.2.5. SELECT, WHERE.,.,. mysql> SELECT * FROM score WHERE score > 95; +------------+----------+-------+ student_id event_id score +------------+----------+-------+

50 PART 1 5 3 97 18 3 96 1 6 100 5 6 97 11 6 98 16 6 98 +------------+----------+-------+.. mysql> SELECT last_name, first_name FROM president -> WHERE last_name='roosevelt'; +-----------+-------------+ last_name first_name +-----------+-------------+ Roosevelt Theodore Roosevelt Franklin D. +-----------+-------------+ mysql> SELECT last_name, first_name FROM president -> WHERE last_name='roosevelt'; +-----------+-------------+ last_name first_name +-----------+-------------+ Roosevelt Theodore Roosevelt Franklin D. +-----------+-------------+. mysql> SELECT last_name, first_name, birth FROM president -> WHERE birth < '1750-1-1'; +------------+------------+------------+ last_name first_name birth +------------+------------+------------+ Washington George 1732-02-22 Adams John 1735-10-30 Jefferson Thomas 1743-04-13 +------------+------------+------------+. mysql> SELECT last_name, first_name, birth, state FROM president -> WHERE birth < '1750-1-1' AND (state='va' OR state='ma'); +------------+------------+------------+-------+ last_name first_name birth state +------------+------------+------------+-------+ Washington George 1732-02-22 VA Adams John 1735-10-30 MA Jefferson Thomas 1743-04-13 VA +------------+------------+------------+-------+

Chapter 01 51 WHERE ([ 1.1]), ([ 1.2]), ([ 1.3])..,,. MySQL,. C., ~ (and) AND.. ~ (and).,. mysql> SELECT last_name, first_name, state FROM president -> WHERE state='va' AND state='ma'; Empty set (0.36 sec)

52 PART 1,.?,. ~ (and), SQL OR. mysql> SELECT last_name, first_name, state FROM president -> WHERE state='va' OR state='ma'; +------------+-------------+-------+ last_name first_name state +------------+-------------+-------+ Washington George VA Adams John MA Jefferson Thomas VA Madison James VA Monroe James VA Adams John Quincy MA Harrison William H. VA Tyler John VA Taylor Zachary VA Wilson Woodrow VA Kennedy John F. MA Bush George H.W. MA +------------+-------------+-------+ SQL,. SQL,.,,. IN( ). IN( ). SELECT last_name, first_name, state FROM president WHERE state IN( VA, MA );

Chapter 01 53 IN( ). NULL., NULL. NULL. mysql> SELECT NULL < 0, NULL = 0, NULL <> 0, NULL > 0; +----------+----------+-----------+----------+ NULL < 0 NULL = 0 NULL <> 0 NULL > 0 +----------+----------+-----------+----------+ NULL NULL NULL NULL +----------+----------+-----------+----------+,, NULL. mysql> SELECT NULL = NULL, NULL <> NULL; +-------------+--------------+ NULL = NULL NULL <> NULL +-------------+--------------+ NULL NULL +-------------+--------------+ NULL., =, <>,!= IS NULL IS NOT NULL., president NULL. mysql> SELECT last_name, first_name FROM president WHERE death IS NULL; +-----------+-------------+ last_name first_name +-----------+-------------+ Carter James E. Bush George H.W. Clinton William J. Bush George W. +-----------+-------------+ NULL IS NOT NULL.. mysql> SELECT last_name, first_name, suffix -> FROM president WHERE suffix IS NOT NULL; +-----------+------------+--------+ last_name first_name suffix +-----------+------------+--------+

54 PART 1 Carter James E. Jr. +-----------+------------+--------+ MySQL <=> NULL NULL true.. SELECT last_name, first_name FROM president WHERE death <=> NULL; SELECT last_name, first_name, suffix FROM president WHERE NOT (suffix <=> NULL); SELECT * FROM tbl_name, MySQL.,..,,. (, MySQL.).,,., ORDER BY. last name. mysql> SELECT last_name, first_name FROM president -> ORDER BY last_name; +------------+---------------+ last_name first_name +------------+---------------+ Adams John Adams John Quincy Arthur Chester A. Buchanan James... ORDER BY. ASC DESC ORDER BY., ( ) DESC. mysql> SELECT last_name, first_name FROM president -> ORDER BY last_name DESC; +------------+---------------+ last_name first_name +------------+---------------+ Wilson Woodrow

Chapter 01 55 Washington George Van Buren Martin Tyler John...,. president, (state), (state) last name. mysql> SELECT last_name, first_name, state FROM president -> ORDER BY state DESC, last_name ASC; +------------+---------------+-------+ last_name first_name state +------------+---------------+-------+ Arthur Chester A. VT Coolidge Calvin VT Harrison William H. VA Jefferson Thomas VA Madison James VA Monroe James VA Taylor Zachary VA Tyler John VA Washington George VA Wilson Woodrow VA Eisenhower Dwight D. TX Johnson Lyndon B. TX... NULL. NULL, NULL NULL., ( NULL ).,. mysql> SELECT last_name, first_name, death FROM president -> ORDER BY IF(death IS NULL,0,1), death DESC; +------------+---------------+------------+ last_name first_name death +------------+---------------+------------+ Clinton William J. NULL Bush George H.W. NULL Carter James E. NULL Bush George W. NULL Ford Gerald R. 2006-12-26 Reagan Ronald W. 2004-06-05 Nixon Richard M. 1994-04-22 Johnson Lyndon B. 1973-01-22

56 PART 1... Jefferson Thomas 1826-07-04 Adams John 1826-07-04 Washington George 1799-12-14 +------------+---------------+------------+ IF()., IF() NULL 0 NULL 1. NULL NULL., LIMIT, ORDER BY. MySQL n. 5. mysql> SELECT last_name, first_name, birth FROM president -> ORDER BY birth LIMIT 5; +------------+------------+------------+ last_name first_name birth +------------+------------+------------+ Washington George 1732-02-22 Adams John 1735-10-30 Jefferson Thomas 1743-04-13 Madison James 1751-03-16 Monroe James 1758-04-28 +------------+------------+------------+, ORDER BY birth DESC, 5. mysql> SELECT last_name, first_name, birth FROM president -> ORDER BY birth DESC LIMIT 5; +-----------+-------------+------------+ last_name first_name birth +-----------+-------------+------------+ Clinton William J. 1946-08-19 Bush George W. 1946-07-06 Carter James E. 1924-10-01 Bush George H.W. 1924-06-12 Kennedy John F. 1917-05-29 +-----------+-------------+------------+ LIMIT,.,.,

Chapter 01 57. 10 5. mysql> SELECT last_name, first_name, birth FROM president -> ORDER BY birth DESC LIMIT 10, 5; +-----------+-------------+------------+ last_name first_name birth +-----------+-------------+------------+ Truman Harry S. 1884-05-08 Roosevelt Franklin D. 1882-01-30 Hoover Herbert C. 1874-08-10 Coolidge Calvin 1872-07-04 Harding Warren G. 1865-11-02 +-----------+-------------+------------+ president, ORDER BY RAND( ) LIMIT. mysql> SELECT last_name, first_name FROM president -> ORDER BY RAND() LIMIT 1; +-----------+------------+ last_name first_name +-----------+------------+ Johnson Lyndon B. +-----------+------------+ mysql> SELECT last_name, first_name FROM president -> ORDER BY RAND() LIMIT 3; +-----------+-------------+ last_name first_name +-----------+-------------+ Harding Warren G. Bush George H.W. Jefferson Thomas +-----------+-------------+. MySQL.. ( ). mysql> SELECT 17, FORMAT(SQRT(25+13),3); +----+-------------------------+ 17 FORMAT(SQRT(25+13),3) +----+-------------------------+ 17 6.164 +----+-------------------------+

58 PART 1. mysql> SELECT CONCAT(first_name,,last_name),CONCAT(city,,,state) -> FROM president; +----------------------------------+-------------------------+ CONCAT(first_name,,last_name) CONCAT(city,,,state) +----------------------------------+-------------------------+ George Washington Wakefield, VA John Adams Braintree, MA Thomas Jefferson Albemarle County, VA James Madison Port Conway, VA... first name last name,.,.,., AS name. (column alias).,. mysql> SELECT CONCAT(first_name,' ',last_name) AS Name, -> CONCAT(city,', ',state) AS Birthplace -> FROM president; +-----------------------+-------------------------+ Name Birthplace +-----------------------+-------------------------+ George Washington Wakefield, VA John Adams Braintree, MA Thomas Jefferson Albemarle County, VA James Madison Port Conway, VA...,. mysql> SELECT CONCAT(first_name,' ',last_name) AS 'President Name', -> CONCAT(city,', ',state) AS 'Place of Birth' -> FROM president; +-----------------------+-------------------------+ President Name Place of Birth +-----------------------+-------------------------+ George Washington Wakefield, VA John Adams Braintree, MA Thomas Jefferson Albemarle County, VA James Madison Port Conway, VA...

Chapter 01 59 AS. mysql> SELECT 1, 2 AS two, 3 three; +---+-----+-------+ 1 two three +---+-----+-------+ 1 2 3 +---+-----+-------+ AS.,., first_name last_name. mysql> SELECT first_name last_name FROM president; +---------------+ last_name +---------------+ George John Thomas James...., first_column, last_name.,. MySQL MySQL. 2008 7 27 2008-07-27., 07-27-2008 27-07-2008.. ( ),, ( )., DATE.

60 PART 1 mysql> SELECT * FROM event WHERE date = '2008-10-01'; +------------+----------+----------+ date category event_id +------------+----------+----------+ 2008-10-01 T 6 +------------+----------+----------+ mysql> SELECT last_name, first_name, death -> FROM president -> WHERE death >= '1970-01-01' AND death < '1980-01-01'; +-----------+------------+------------+ last_name first_name death +-----------+------------+------------+ Truman Harry S. 1972-12-26 Johnson Lyndon B. 1973-01-22 +-----------+------------+------------+, YEAR(), MONTH(), DAYOFMONTH()., 3 (3 ). mysql> SELECT last_name, first_name, birth -> FROM president WHERE MONTH(birth) = 3; +-----------+------------+------------+ last_name first_name birth +-----------+------------+------------+ Madison James 1751-03-16 Jackson Andrew 1767-03-15 Tyler John 1790-03-29 Cleveland Grover 1837-03-18 +-----------+------------+------------+. mysql> SELECT last_name, first_name, birth -> FROM president WHERE MONTHNAME(birth) = 'March'; +-----------+------------+------------+ last_name first_name birth +-----------+------------+------------+ Madison James 1751-03-16 Jackson Andrew 1767-03-15 Tyler John 1790-03-29 Cleveland Grover 1837-03-18 +-----------+------------+------------+, MONTH( ) DAYOFMONTH( ). mysql> SELECT last_name, first_name, birth -> FROM president WHERE MONTH(birth) = 3 AND DAYOFMONTH(birth) = 29;

Chapter 01 61 +-----------+------------+------------+ last_name first_name birth +-----------+------------+------------+ Tyler John 1790-03-29 +-----------+------------+------------+ /.,,., CURDATE( ),. SELECT last_name, first_name, birth FROM president WHERE MONTH(birth) = MONTH(CURDATE()) AND DAYOFMONTH(birth) = DAYOFMONTH(CURDATE());,.,,. TIMESTAMPDIFF(). mysql> SELECT last_name, first_name, birth, death, -> TIMESTAMPDIFF(YEAR, birth, death) AS age -> FROM president WHERE death IS NOT NULL -> ORDER BY age DESC LIMIT 5; +-----------+------------+------------+------------+------+ last_name first_name birth death age +-----------+------------+------------+------------+------+ Reagan Ronald W. 1911-02-06 2004-06-05 93 Ford Gerald R. 1913-07-14 2006-12-26 93 Adams John 1735-10-30 1826-07-04 90 Hoover Herbert C. 1874-08-10 1964-10-20 90 Truman Harry S. 1884-05-08 1972-12-26 88 +-----------+------------+------------+------------+------+ TO_DAYS()..,.,,. 60. SELECT last_name, first_name, expiration FROM member WHERE (TO_DAYS(expiration) - TO_DAYS(CURDATE())) < 60; TIMESTAMPDIFF(). SELECT last_name, first_name, expiration FROM member WHERE TIMESTAMPDIFF(DAY, CURDATE(), expiration) < 60;, DATE_ADD() DATE_SUB().

62 PART 1,. mysql> SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR); +----------------------------------------+ DATE_ADD('1970-1-1', INTERVAL 10 YEAR) +----------------------------------------+ 1980-01-01 +----------------------------------------+ mysql> SELECT DATE_SUB('1970-1-1', INTERVAL 10 YEAR); +----------------------------------------+ DATE_SUB('1970-1-1', INTERVAL 10 YEAR) +----------------------------------------+ 1960-01-01 +----------------------------------------+ 1970,.,. mysql> SELECT last_name, first_name, death -> FROM president -> WHERE death >= '1970-1-1' -> AND death < DATE_ADD('1970-1-1', INTERVAL 10 YEAR); +-----------+------------+------------+ last_name first_name death +-----------+------------+------------+ Truman Harry S. 1972-12-26 Johnson Lyndon B. 1973-01-22 +-----------+------------+------------+ DATE_ADD( ). SELECT last_name, first_name, expiration FROM member WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 60 DAY); expiration 5.. SELECT last_name, first_name, last_visit FROM patient WHERE last_visit < DATE_SUB(CURDATE(),INTERVAL 6 MONTH);.? MySQL,. (LIKE NOT LIKE),

Chapter 01 63. _, % ( ). W w last name. mysql> SELECT last_name, first_name FROM president -> WHERE last_name LIKE 'W%'; +------------+------------+ last_name first_name +------------+------------+ Washington George Wilson Woodrow +------------+------------+. LIKE,. mysql> SELECT last_name, first_name FROM president -> WHERE last_name = 'W%'; Empty set (0.00 sec) W% w%. last_name W w. mysql> SELECT last_name, first_name FROM president -> WHERE last_name LIKE '%W%'; +------------+------------+ last_name first_name +------------+------------+ Washington George Wilson Woodrow Eisenhower Dwight D. +------------+------------+ last name. mysql> SELECT last_name, first_name FROM president -> WHERE last_name LIKE ' '; +-----------+-------------+ last_name first_name +-----------+-------------+ Polk James K. Taft William H. Ford Gerald R Bush George H.W. Bush George W. +-----------+-------------+

64 PART 1 MySQL REXEXP, C 3.5.1. MySQL.,. Andrew Jackson.,,. mysql> SELECT @birth := birth FROM president -> WHERE last_name = 'Jackson' AND first_name = 'Andrew'; +-----------------+ @birth := birth +-----------------+ 1767-03-15 +-----------------+ mysql> SELECT last_name, first_name, birth FROM president -> WHERE birth < @birth ORDER BY birth; +------------+------------+------------+ last_name first_name birth +------------+------------+------------+ Washington George 1732-02-22 Adams John 1735-10-30 Jefferson Thomas 1743-04-13 Madison James 1751-03-16 Monroe James 1758-04-28 +------------+------------+------------+ @var_name, SELECT @var_name := value. Andrew Jackson @birth ( SELECT. ).,. (join),.. SET, = :=. mysql> SET @today = CURDATE(); mysql> SET @one_week_ago := DATE_SUB(@today(), INTERVAL 7 DAY); mysql> SELECT @today(), @one_week_ago;

Chapter 01 65 +------------+---------------+ @today @one_week_ago +------------+---------------+ 2008-03-21 2008-03-14 +------------+---------------+ MySQL.,, MySQL MySQL.,. DISTINCT.,,. mysql> SELECT DISTINCT state FROM president ORDER BY state; +-------+ state +-------+ AR CA CT GA IA IL KY MA MO NC NE NH NJ NY OH PA SC TX VA VT +-------+, COUNT( ). COUNT(*),. WHERE, COUNT(*). member.

66 PART 1 mysql> SELECT COUNT(*) FROM member; +----------+ COUNT(*) +----------+ 102 +----------+ WHERE, COUNT(*).. mysql> SELECT COUNT(*) FROM grade_event WHERE type = 'Q'; +----------+ COUNT(*) +----------+ 4 +----------+ COUNT(*)., COUNT(col_name) NULL.. mysql> SELECT COUNT(*),COUNT(email), COUNT(expiration) FROM member; +----------+--------------+-------------------+ COUNT(*) COUNT(email) COUNT(expiration) +----------+--------------+-------------------+ 102 80 96 +----------+--------------+-------------------+ member 102, 80 email. 6 (expiration NULL, 102 96 NULL 6 ). COUNT() DISTINCT NULL., (state),. mysql> SELECT COUNT(DISTINCT state) FROM president; +-----------------------+ COUNT(DISTINCT state) +-----------------------+ 20 +-----------------------+.,. mysql> SELECT COUNT(*) FROM student; +----------+ COUNT(*) +----------+

Chapter 01 67 31 +----------+,? (sex). mysql> SELECT COUNT(*) FROM student WHERE sex='f'; +----------+ COUNT(*) +----------+ 15 +----------+ mysql> SELECT COUNT(*) FROM student WHERE sex='m'; +----------+ COUNT(*) +----------+ 16 +----------+,. (state). (SELECT DISTINCT state FROM president), SELECT COUNT(*).., MySQL,.,. mysql> SELECT sex, COUNT(*) FROM student GROUP BY sex; +-----+----------+ sex COUNT(*) +-----+----------+ F 15 M 16 +-----+----------+. mysql> SELECT state, COUNT(*) FROM president GROUP BY state; +-------+----------+ state COUNT(*) +-------+----------+ AR 1 CA 1 CT 1 GA 1

68 PART 1 IA 1 IL 1 KY 1 MA 4 MO 1 NC 2 NE 1 NH 1 NJ 1 NY 4 OH 7 PA 1 SC 1 TX 2 VA 8 VT 2 +-------+----------+ GROUP BY. MySQL.. GROUP BY COUNT(*),..,.,..., MySQL GROUP BY, ORDER BY., (state), ORDER BY. mysql> SELECT state, COUNT(*) AS count FROM president -> GROUP BY state ORDER BY count DESC; +-------+-------+ state count +-------+-------+ VA 8 OH 7 MA 4 NY 4 NC 2 VT 2 TX 2

Chapter 01 69 SC 1 NH 1 PA 1 KY 1 NJ 1 IA 1 MO 1 CA 1 NE 1 GA 1 IL 1 AR 1 CT 1 +-------+-------+, ORDER BY.,,., COUNT(*) count. ORDER BY.. SELECT state, COUNT(*) FROM president GROUP BY state ORDER BY 2 DESC; MySQL..,,, ORDER BY,. ORDER BY SQL,.. GROUP BY, ORDER BY.. mysql> SELECT MONTH(birth) AS Month, MONTHNAME(birth) AS Name, -> COUNT(*) AS count -> FROM president GROUP BY Name ORDER BY Month; +-------+-----------+-------+ Month Name count +-------+-----------+-------+ 1 January 4 2 February 4

70 PART 1 3 March 4 4 April 4 5 May 2 6 June 1 7 July 4 8 August 4 9 September 1 10 October 6 11 November 5 12 December 3 +-------+-----------+-------+ COUNT() ORDER BY LIMIT president. mysql> SELECT state, COUNT(*) AS count FROM president -> GROUP BY state ORDER BY count DESC LIMIT 4; +-------+-------+ state count +-------+-------+ VA 8 OH 7 MA 4 NY 4 +-------+-------+ LIMIT COUNT() HAVING. HAVING WHERE. COUNT() WHERE. 2. mysql> SELECT state, COUNT(*) AS count FROM president -> GROUP BY state HAVING count > 1 ORDER BY count DESC; +-------+-------+ state count +-------+-------+ VA 8 OH 7 MA 4 NY 4 NC 2 VT 2 TX 2 +-------+-------+,. HAVING count =1.

Chapter 01 71 COUNT(). MIN(), MAX(), SUM(), AVG(),,,... ( ). mysql> SELECT -> event_id, -> MIN(score) AS minimum, -> MAX(score) AS maximum, -> MAX(score)-MIN(score)+1 AS span, -> SUM(score) AS total, -> AVG(score) AS average, -> COUNT(score) AS count -> FROM score -> GROUP BY event_id; +----------+---------+---------+-------+-------+---------+-------+ event_id minimum maximum span total average count +----------+---------+---------+-------+-------+---------+-------+ 1 9 20 12 439 15.1379 29 2 8 19 12 425 14.1667 30 3 60 97 38 2425 78.2258 31 4 7 20 14 379 14.0370 27 5 8 20 13 383 14.1852 27 6 62 100 39 2325 80.1724 29 +----------+---------+---------+-------+-------+---------+-------+, event_id., grade_event. 1.4.9., WITH ROLLUP... WITH ROLLUP. mysql> SELECT sex, COUNT(*) FROM student GROUP BY sex WITH ROLLUP; +-----+----------+ sex COUNT(*) +-----+----------+ F 15 M 16 NULL 31 +-----+----------+ NULL.

72 PART 1 WITH ROLLUP.,. mysql> SELECT -> event_id, -> MIN(score) AS minimum, -> MAX(score) AS maximum, -> MAX(score)-MIN(score)+1 AS span, -> SUM(score) AS total, -> AVG(score) AS average, -> COUNT(score) AS count -> FROM score -> GROUP BY event_id -> WITH ROLLUP; +----------+---------+---------+------+-------+---------+-------+ event_id minimum maximum span total average count +----------+---------+---------+------+-------+---------+-------+ 1 9 20 12 439 15.1379 29 2 8 19 12 425 14.1667 30 3 60 97 38 2425 78.2258 31 4 7 20 14 379 14.0370 27 5 8 20 13 383 14.1852 27 6 62 100 39 2325 80.1724 29 NULL 7 100 94 6376 36.8555 173 +----------+---------+---------+------+-------+---------+-------+. WITH ROLLUP.. GROUP BY, WITH ROLLUP.,.. mysql> SELECT -> state AS State, -> AVG(TIMESTAMPDIFF(YEAR, birth, death)) AS Age -> FROM president WHERE death IS NOT NULL -> GROUP BY state ORDER BY Age; +-------+---------+ State Age +-------+---------+ KY 56.0000 VT 58.5000

Chapter 01 73 NC 59.5000 OH 62.2857 NH 64.0000 NY 69.0000 NJ 71.0000 TX 71.0000 MA 72.0000 VA 72.3750 PA 77.0000 SC 78.0000 CA 81.0000 MO 88.0000 IA 90.0000 NE 93.0000 IL 93.0000 +-------+---------+,, ( ),.,.?.,..,,... 3 2 15 14,?. MySQL., DBMS.., (join).. SELECT SELECT

74 PART 1. SELECT... 1.4.6,.. 3,.. mysql> SELECT student_id, date, score, category -> FROM grade_event INNER JOIN score -> ON grade_event.event_id = score.event_id -> WHERE date = 2008-09-23 ; +------------+------------+-------+----------+ student_id date score category +------------+------------+-------+----------+ 1 2008-09-23 15 Q 2 2008-09-23 12 Q 3 2008-09-23 11 Q 5 2008-09-23 13 Q 6 2008-09-23 18 Q... ( 2008-09-23 ) grade_event, event ID event ID (score). grade_event score, ID,,,.. FROM. FROM grade_event INNER JOIN score on grade_event score event_id. ON grade_event.event_id = score.event_id MySQL tbl_name.col_name grade_event.event_id event_id. event_id, event_id. (date, score, category),. (join), ( )..

Chapter 01 75 SELECT score.student_id, grade_event.date, score.score, grade_event.category FROM grade_event INNER JOIN score ON grade_event.event_id = score.event_id WHERE grade_event.date = '2008-09-23'; grade_event ID, ID score. student_id,. student student ID,. score student student_id,.. mysql> SELECT -> student.name, grade_event.date, score.score, grade_event.category -> FROM grade_event INNER JOIN score INNER JOIN student -> ON grade_event.event_id = score.event_id -> AND score.student_id = student.student_id -> WHERE grade_event.date = '2008-09-23'; +-----------+------------+-------+----------+ name date score category +-----------+------------+-------+----------+ Megan 2008-09-23 15 Q Joseph 2008-09-23 12 Q Kyle 2008-09-23 11 Q Abby 2008-09-23 13 Q Nathan 2008-09-23 18 Q.... student FROM, grade_event score student. student_id, (student_id) (score.student_id). score student student_id, score.student_id student.student_id. student ID score student ON. ON... score.student_id = student.student_id student ID (. student_id ).

76 PART 1,. student ID event ID. MySQL ID,.. absense student ID absence. (ID ), student_id absence student (join). ID. mysql> SELECT student.student_id, student.name, -> COUNT(absence.date) AS absences -> FROM student INNER JOIN absence -> ON student.student_id = absence.student_id -> GROUP BY student.student_id; +------------+-------+----------+ student_id name absences +------------+-------+----------+ 3 Kyle 1 5 Abby 1 10 Peter 2 17 Will 1 20 Avery 1 +------------+-------+----------+ NOTE.,?.... (join) LEFT JOIN. MySQL LEFT JOIN ( LEFT JOIN ). student, absense. FROM LEFT JOIN ( ), ON.

Chapter 01 77. mysql> SELECT student.student_id, student.name, -> COUNT(absence.date) AS absences -> FROM student LEFT JOIN absence -> ON student.student_id = absence.student_id -> GROUP BY student.student_id; +------------+-----------+----------+ student_id name absences +------------+-----------+----------+ 1 Megan 0 2 Joseph 0 3 Kyle 1 4 Katie 0 5 Abby 1 6 Nathan 0 7 Liesl 0... 1.4.9 score. event ID event, score grade_event ID.., event ID. mysql> SELECT -> grade_event.date,grade_event.category, -> MIN(score.score) AS minimum, -> MAX(score.score) AS maximum, -> MAX(score.score)-MIN(score.score)+1 AS span, -> SUM(score.score) AS total, -> AVG(score.score) AS average, -> COUNT(score.score) AS count -> FROM score INNER JOIN grade_event -> ON score.event_id = grade_event.event_id -> GROUP BY grade_event.date; +------------+----------+---------+---------+------+-------+---------+-------+ date category minimum maximum span total average count +------------+----------+---------+---------+------+-------+---------+-------+ 2008-09-03 Q 9 20 12 439 15.1379 29 2008-09-06 Q 8 19 12 425 14.1667 30 2008-09-09 T 60 97 38 2425 78.2258 31 2008-09-16 Q 7 20 14 379 14.0370 27 2008-09-23 Q 8 20 13 383 14.1852 27 2008-10-01 T 62 100 39 2325 80.1724 29 +------------+----------+---------+---------+------+-------+---------+-------+

78 PART 1,, COUNT() AVG(). event. mysql> SELECT grade_event.date, student.sex, -> COUNT(score.score) AS count, AVG(score.score) AS average -> FROM grade_event INNER JOIN score INNER JOIN student -> ON grade_event.event_id = score.event_id -> AND score.student_id = student.student_id -> GROUP BY grade_event.date, student.sex; +------------+-----+-------+---------+ date sex count average +------------+-----+-------+---------+ 2008-09-03 F 14 14.6429 2008-09-03 M 15 15.6000 2008-09-06 F 14 14.7143 2008-09-06 M 16 13.6875 2008-09-09 F 15 77.4000 2008-09-09 M 16 79.0000 2008-09-16 F 13 15.3077 2008-09-16 M 14 12.8571 2008-09-23 F 12 14.0833 2008-09-23 M 15 14.2667 2008-10-01 F 14 77.7857 2008-10-01 M 15 82.4000 +------------+-----+-------+---------+,.. SELECT student.student_id, student.name, SUM(score.score) AS total, COUNT(score.score) AS n FROM grade_event INNER JOIN score INNER JOIN student ON grade_event.event_id = score.event_id AND score.student_id = student.student_id GROUP BY score.student_id ORDER BY total;.,.,,. mysql> SELECT p1.last_name, p1.first_name, p1.city, p1.state -> FROM president AS p1 INNER JOIN president AS p2 -> ON p1.city = p2.city AND p1.state = p2.state -> WHERE (p1.last_name <> p2.last_name OR p1.first_name <> p2.first_name) -> ORDER BY state, city, last_name;

Chapter 01 79 +-----------+-------------+-----------+-------+ last_name first_name city state +-----------+-------------+-----------+-------+ Adams John Quincy Braintree MA Adams John Braintree MA +-----------+-------------+-----------+-------+., (p1 p2),.,. WHERE..., MONTH() DAYOFMONTH(). mysql> SELECT p1.last_name, p1.first_name, p1.birth -> FROM president AS p1 INNER JOIN president AS p2 -> WHERE MONTH(p1.birth) = MONTH(p2.birth) -> AND DAYOFMONTH(p1.birth) = DAYOFMONTH(p2.birth) -> AND (p1.last_name <> p2.last_name OR p1.first_name <> p2.first_name) -> ORDER BY p1.last_name; +------------+------------+------------+ last_name first_name birth +------------+------------+------------+ Harding Warren G. 1865-11-02 Polk James K. 1795-11-02 +------------+------------+------------+ MONTH() DAYOFMONTH() DAYOFYEAR(),. SELECT. 2.9..,. mysql> SELECT * FROM student -> WHERE student_id NOT IN (SELECT student_id FROM absence); +-----------+-----+------------+ name sex student_id

80 PART 1 +-----------+-----+------------+ Megan F 1 Joseph M 2 Katie F 4 Nathan M 6 Liesl F 7... SELECT absence student_id, SELECT ID student. 1.4.9 Andrew Jackson.,. mysql> SELECT last_name, first_name, birth FROM president -> WHERE birth < (SELECT birth FROM president -> WHERE last_name = 'Jackson' AND first_name = 'Andrew'); +------------+------------+------------+ last_name first_name birth +------------+------------+------------+ Washington George 1732-02-22 Adams John 1735-10-30 Jefferson Thomas 1743-04-13 Madison James 1751-03-16 Monroe James 1758-04-28 +------------+------------+------------+ SELECT Andrew Jackson, SELECT. 1.4.10. DELETE UPDATE.. DELETE. DELETE FROM tbl_name WHERE which rows to delete; WHERE,.., DELETE. DELETE FROM tbl_name;!, WHERE. SELECT WHERE

Chapter 01 81., president (Ohio),. mysql> DELETE FROM president WHERE state='oh'; Query OK, 7 rows affected (0.00 sec) DELETE, WHERE SELECT.. Teddy Roosevelt.? DELETE FROM president WHERE last_name='roosevelt';. Franklin Roosevelt., SELECT WHERE. mysql> SELECT last_name, first_name FROM president -> WHERE last_name='roosevelt'; +-----------+-------------+ last_name first_name +-----------+-------------+ Roosevelt Theodore Roosevelt Franklin D. +-----------+-------------+. mysql> SELECT last_name, first_name FROM president -> WHERE last_name='roosevelt' AND first_name='theodore'; +-----------+------------+ last_name first_name +-----------+------------+ Roosevelt Theodore +-----------+------------+ WHERE DELETE. mysql> DELETE FROM president -> WHERE last_name= Roosevelt AND first_name='theodore';,.?,.,. 1.5 mysql. UPDATE,. UPDATE tbl_name

82 PART 1 SET which columns to change WHERE which records to update WHERE DELETE.,., George. mysql> UPDATE student SET name='george';, WHERE. Historical League,. mysql> INSERT INTO member (last_name,first_name) -> VALUES('York','Jerome');,. WHERE UPDATE. mysql> UPDATE member -> SET expiration='2009-7-20' -> WHERE last_name='york' AND first_name='jerome';. UPDATE Jerome. mysql> UPDATE member -> SET email='jeromey@aol.com', street='123 Elm St', -> city='anytown', state='ny', zip='01003' -> WHERE last_name='york' AND first_name='jerome'; NULL ( NULL ). Jerome, Jerome NULL( ). mysql> UPDATE member -> SET expiration=null -> WHERE last_name='york' AND first_name='jerome' DELETE, UPDATE SELECT WHERE.,., sampdb.,. 1.4.8 sampdb.

Chapter 01 83 1.5 mysql.,. 1.5.1 mysql,,.. MySQL... mysql. MySQL. mysql. mysqlimport mysqlshow MySQL., mysql.. F.2.2. UNIX ~/.my.cnf (.my.cnf ). Windows C (C:\my.ini) MySQL my.ini.,.. [client] host=server_host user=your_name password=your_pass [client] client. MySQL. server_host, your_name, your_pass,,., cobra.snake.net, MySQL, sampadm, secret,.my.cnf.

84 PART 1 [client] host=cobra.snake.net user=sampadm password=secret [client],.., UNIX MySQL UNIX user. host, localhost., UNIX.. % chmod 600.my.cnf % chmod u=rw,go-rwx.my.cnf tcsh bash,.,., mysql,. %!my! my. ( Ctrl-P Ctrl-N).. tcsh bash,.,.,., csh tcsh, alias sampdb. alias sampdb 'mysql -h cobra.snake.net -p -u sampadm sampdb' bash.,. alias sampdb='mysql -h cobra.snake.net -p -u sampadm sampdb',.

Chapter 01 85 % sampdb % mysql -h cobra.snake.net -p -u sampadm sampdb,., (, tcsh.tcshrc, bash.bashrc.bash_profile ). Windows mysql (shortcut).. mysql. UNIX sampdb. #!/bin/sh exec mysql -h cobra.snake.net -p -u sampadm sampdb sampdb (chmod +x sampdb ), sampdb mysql. Windows (batch). smapdb.bat,. mysql -h cobra.snake.net -p -u sampadm sampdb DOS sampdb Windows. (shortcut),, mysql. 1.5.2 mysql,,. mysql,... mysql.. mysql (batch).

86 PART 1 mysql GNU Readline.,...,, ( ). [ 1.4].,. GNU http://www.gnu.org/manual/ bash (command editing). Windows, [ 1.5], mysql.

Chapter 01 87. mysql. mysql> SHOW COLUMNS FROM persident; president persident,. 1. Ctrl-B persident s. 2. er Delete, ( ). 3. re. 4..,. mysql,,.

88 PART 1,. 1. mysql. 2. (, UNIX vi, Windows gvim ). 3.,. mysql.,,. ( ). Unix mysql,.mysql_history., mysql,.mysql_history.mysql_history. mysql. mysql... member interests., (Great Depression),. SELECT last_name, first_name, email, interests FROM member WHERE interests LIKE %depression% ORDER BY last_name, first_name; interest.sql, mysql. % mysql sampdb < interests.sql, mysql. mysql, -t. % mysql -t sampdb < interests.sql,.

Chapter 01 89 % mysql -t sampdb < interests.sql > interests.out mysql, source. mysql> source interests.sql Thomas Jefferson, depression Jefferson mysql.... interests., interests.sh. #!/bin/sh # interests.sh - USHL if [ $# -ne 1 ]; then echo 'Please specify one keyword'; exit; fi mysql -t sampdb <<QUERY_INPUT SELECT last_name, first_name, email, interests FROM member WHERE interests LIKE '%$1%' ORDER BY last_name, first_name; QUERY_INPUT.. <<QUERY_INPUT QUERY_INPUT mysql., $1. ( $1, $2.).,. % chmod +x interests.sh,.. NOTE %./interests.sh "Jefferson';DROP DATABASE sampdb;"