复制成功
  • 图案背景
  • 纯色背景

笔记

  • 2019-11-16
    为大人带来形象的羊生肖故事来历 为孩子带去快乐的生肖图画故事阅读
    谈谈怎样学好数学_苏步青-中学生文库
kuo08099

上传于:2015-04-08

粉丝量:35

免责声明:多数资料为网络收集,版权属原作者所有,如有侵权请友情告知,我立即删除!



内苎古快三走势图—官方网址22270.COM—The Visibooks Guide To Mysql Basics

下载积分:850

内容提示: 12 11. CNR12. GETTING STARTED 13Start MySQL 1. Click the then Terminal Program (Konsole). button, then Run Programs, then Utilities, GETTING STARTED 14 2. When the Konsole window opens, it should look like this: Tip: In Linspire, the prompt is followed by a # Visilearn: ~# as you see above. # means you’re giving commands as the Root user. The default user in Linspire is the Root user. GETTING STARTED 15On other Linux distributions the terminal prom...

文档格式:PDF| 浏览次数:11| 上传日期:2015-04-08 10:22:22| 文档星级:
12 11. CNR12. GETTING STARTED 13Start MySQL 1. Click the then Terminal Program (Konsole). button, then Run Programs, then Utilities, GETTING STARTED 14 2. When the Konsole window opens, it should look like this: Tip: In Linspire, the prompt is followed by a # Visilearn: ~# as you see above. # means you’re giving commands as the Root user. The default user in Linspire is the Root user. GETTING STARTED 15On other Linux distributions the terminal prompt is followed by a $. $ means you’re giving Linux commands as a regular user. Giving the su command allows you to give commands as the “Super User,” or Root user, of the computer. If your terminal prompt is followed by a $, type su at the prompt. Then press the ENTER key on your keyboard. GETTING STARTED 16 At the Password prompt, type: Your Root user password Not this particular string, of course, but the actual Root password for the Linux computer. Then press the ENTER key. Notice the prompt has changed from [yourusername@localhost yourusername] $ to [root@localhost yourusername] # There’s now a # at the end of the prompt. This means you are now giving commands as the Root user. As the Root user, you can add/delete/modify any file on the computer. GETTING STARTED 173. Type: /etc/init. d/mysql start Then press ENTER. The window should look like this: This starts the MySQL server—the program mysql in the /etc/init. d/ directory. GETTING STARTED 18 Tip: If you are not sure whether or not the MySQL Server is running, type: /etc/init. d/mysql status If it’s running, the window will look like this: GETTING STARTED 19Tip: If you had to log in as the Super User earlier, type: exit Then press ENTER. The prompt has now changed to: [yourusername@localhost yourusername]$ Linux Root privileges were only needed to start MySQL, so you’ve logged out as the Linux computer’s Super (Root) User. GETTING STARTED 20 4. At the prompt, type: mysql –u root mysql Then press ENTER. The window should look like this, with a mysql> prompt: GETTING STARTED 21Here’s what this string of commands means: • mysql mysql –u root mysql This first mysql starts the MySQL client. MySQL is made up of two parts: the MySQL server program and a MySQL client program. The MySQL server program handles the storage of the data. The MySQL client program allows you to give commands to the MySQL server. You need both parts to make MySQL work. • -u root mysql –u root mysql The -u command tells the MySQL client that you want to log into the MySQL server as a particular user. root denotes the root user of the MySQL server. You’re not logging into the Linux computer as the Root user; you’re logging into the MySQL server as its root user. This gives you total control over the MySQL server. GETTING STARTED 22 • mysql mysql –u root mysql This last mysql refers to a database called mysql that you’ll use initially. This database is included by default in the MySQL server. The database mysql has several tables, including one that describes who can use the MySQL server. 5. Type: SET PASSWORD FOR root@localhost=PASSWORD(‘textbook’ ) ; Then press ENTER. The window should look like this: This string of commands sets the password for the root user on the MySQL server to textbook. GETTING STARTED 23Tip: Both the MySQL server and the Linux computer itself can have root users who can add/delete/modify anything. The passwords for each are independent, however. textbook is not the Root account password of your Linux computer. It’s the root password for the MySQL server. In the previous string of commands, you logged into the MySQL server as its root user, so the password textbook applies to the MySQL server. You can now give commands to add/delete/modify anything in the MySQL server, but not the Linux computer it runs on. GETTING STARTED 24 Create a new database 1. At the mysql> prompt, type: CREATE DATABASE us_presidents; Then press ENTER. GETTING STARTED 25The window should look like this: GETTING STARTED 26 Tip: Now that you’re logged into the MySQL server, you’re giving MySQL commands. Unlike Linux commands, MySQL commands need a semicolon (; ) on the end to execute. The CREATE DATABASE command created a database called us_presidents in the MySQL server. If ever you mistakenly end a command string with a character other than a semicolon… CREATE DATABASE us_presidents …then press ENTER, there is no way to “fix” that command. Just add a semicolon to the new line you are on: CREATE DATBASE us_presidents ; If the command is valid, it will execute. If there was an error in the command string and it’s invalid, adding a semicolon here will execute it and MySQL will give an error. GETTING STARTED 272. Type: SHOW DATABASES; then press ENTER. The window should look like this: This shows the databases on your MySQL server: mysql, test, and us_presidents. The mysql database is used by the MySQL server to store information about users, permissions, etc. The test database is often used as a workplace for MySQL users to test and try things – this is useful in a work environment where many people are working with critical information. GETTING STARTED 28 Tip: MySQL commands don’t have to be UPPER-CASE. In this book, commands are put in UPPER-CASE to make them easier to distinguish. If you’d typed the command in lower-case: show databases; that would have been fine. GETTING STARTED 29Create a table 1. Type: USE us_presidents; then press ENTER. The window should look like this: The USE command allows you to start using the database us_presidents. GETTING STARTED 30 Displaying text Sometimes a string of commands is too wide to fit on the pages of this book. In those cases, an arrow is added that tells you to continue typing in the same line. For instance, this command: rpm –i MySQL-3. 23. 51-1. i386. rpm MySQL-client-3. 23. 51-1. i386. rpm could be displayed this way: rpm –i MySQL-3. 23. 51-1. i386. rpm ►► MySQL-client-3. 23. 51-1. i386. rpm GETTING STARTED 312. Type: CREATE TABLE name ►► (id INT NOT NULL PRIMARY KEY ►► AUTO_INCREMENT, ►► first CHAR(25) , last CHAR(25) ) ; then press ENTER. The window should look like this: This string of commands is used to CREATE a TABLE called name with three fields: id, first, and last. GETTING STARTED 32 Here are the datatypes and properties for these fields: • INT CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25) , last CHAR(25) ) ; The INT datatype for the id field ensures it will contain only integers—numbers, not text. • NOT NULL CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25) , last CHAR(25) ) ; The NOT NULL property ensures the id field cannot be left blank. GETTING STARTED 33• PRIMARY KEY CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25) , last CHAR(25) ) ; The PRIMARY KEY property makes id the key field in the table. In any database table, one field should be the key field—a field that can contain no duplicates. In this table, name, the id field is the key field because it contains the PRIMARY KEY property. This means the name table can’t have two records with an id of 35. • AUTO_INCREMENT CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25) , last CHAR(25) ) ; The AUTO_INCREMENT property automatically assigns a value to the id field, increasing the previous id number by one for each new field. This ensures that the NOT NULL (can’t be blank) and the PRIMARY KEY (can’t have duplicates) properties of the id field are both satisfied. GETTING STARTED 34 • CHAR CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25) , last CHAR(25) ) ; The CHAR datatype for the first and last fields limits the length of entries to 25 characters each. In the us_presidents database, you’ve created a table called name that’s organized like this: Field Datatype Properties id INT primary key, not null, auto increment first CHAR(25) last CHAR(25) GETTING STARTED 35Create a record 1. Type: INSERT INTO name (id, first, last) ►► VALUES (NULL, ' George' , ' Washington' ) ; then press ENTER. The window should look like this: This command string creates the first record in the table name. It reads much like a sentence: INSERT INTO the table name (which has the fields id, first, and last) the corresponding VALUES NULL, George, and Washington. GETTING STARTED 36 Since the id field can’t be blank (it has a NOT NULL property), putting a NULL value in it forces MySQL to automatically number the record (because the id field also has the property AUTO_INCREMENT) . The data in the table name is now organized like this: Fields: id first last Record: 1 George Washington Tip: Text is enclosed within single quotes to let MySQL know that it’s just text, not a command. If the phrase ’ What is the first name of the president named Washington whose values kept him from cutting down the cherry tree?’ was not enclosed in single quotes, MySQL might interpret the words name and values as commands, and get confused. In these examples, single-quotes are used. Double-quotes perform the same function. GETTING STARTED 372. Type: INSERT INTO name (id, first, last) ►► VALUES ►► (NULL, ' John' , ' Adams' ) , ►► (NULL, ' Thomas' , ' Jefferson' ) , ►► (NULL, ' James' , ' Madison' ) ; then press ENTER. This adds three records to the table name: one record each for presidents John Adams, Thomas Jefferson, and James Madison. The data in the table name are now organized like this: Fields: idfirst last Records:1George Washington 2John Adams 3 ThomasJefferson 4JamesMadison GETTING STARTED 38 Run a query 1. Type: SELECT * FROM name; then press ENTER. The window should look like this: The SELECT command tells MySQL to perform a query. The asterisk (*) command tells MySQL to return everything (the asterisk means “everything” or “all”) that’s in the table name. GETTING STARTED 392. Type: SELECT first, last FROM name ►► ORDER BY last; then press ENTER. The window should look like this: This query is more precise than the previous one: it selects the fields first and last from the table name. ORDER BY puts the records in alphabetical order, based on the field last. In other words, it puts the presidents’ last names in alphabetical order. GETTING STARTED 40 3. Type: SELECT id, first, last FROM name ►► ORDER BY id; then press ENTER. The window should look like this: In this query, ORDER BY id places the records in numeric order, based on their id numbers. Tip: To arrange records in reverse numeric or reverse alphabetical order, add DESC on the end. For instance, type: SELECT first, last FROM name ORDER BY last DESC; The DESC option refers to the word “descending.” It tells MySQL to order things descending from high to low instead of the default: low to high. GETTING STARTED 414. Type: \q; then press ENTER. This closes your MySQL database connection. You are now logged out of the MySQL server: the mysql> prompt is gone. 5. Type: exit then press ENTER. The Konsole window should close. GETTING STARTED 42 Giving MySQL commands to a Web server MySQL’s client/server arrangement makes it well-suited to Web applications. With MySQL server running on a Web server, you can use a MySQL client to update/add/delete data remotely. This book assumes that you’ve installed MySQL on your desktop Linux computer. Both the MySQL client and server programs are on this computer, called localhost. To give commands to a MySQL server program running on a Linux Web server, just replace localhost with the IP address of the Web server, such as 10. 0. 1. 10 or the domain name of the Web server, such as mysql. domain. com Provided you have an Internet connection with the Web server, and the proper username/password to access it, your commands will work. GETTING STARTED 43Practice: Getting Started Task: A University has been giving computers to students without keeping track of who has what. Create a database for recording the computers given to students. 1. Open the Konsole window. 2. At the prompt, type: mysql –u root -p then press ENTER. Tip: The –p command tells MySQL to prompt the user for a password. 3. Type the password used to gain root access to the MySQL server: textbook then press ENTER. You are now logged in to the MySQL server. 4. Create a new database called hardware. GETTING STARTED 44 5. Create three new tables in the hardware database, organized in the format shown below: Table: student Field Datatype Properties id INT primary key, not null, auto increment first_name CHAR(15) last_name CHAR(25) Table: computer Table: history Field Datatype Properties id INT primary key, not null, auto increment date_added DATE student_id INT computer_id INT comments CHAR(50) Field Datatype Properties id INT primary key, not null, auto increment description CHAR(35) GETTING STARTED 456. Insert these data into the table student: id first_name last_name1 Jack Hanson 2 Lon James 3 Ken Jones 7. Insert these data into the table computer: id description 1 Apple iBook 2 Apple PowerBook3 Apple iMac 8. Insert these data into the table history: id date_added student_id computer_id comments 1 2002-01-08 1 2 Cool new laptop 2 2002-01-09 1 3 Workstation 3 2002-01-14 2 1 Wireless web Tip: When inserting dates, use the YYYY-MM-DD format, where Y's are the year, M's the month, and D's the day. If you don’t, MySQL will not properly store the information. Also, treat the date like text by surrounding it with quote marks. Otherwise, MySQL may think that 2002-01-08 is 2002 minus 1 minus 8, or 1993. GETTING STARTED 46 A command to insert a date would look like this: INSERT INTO birthdays ►► (name, birthday) ►► VALUES (' Kevin' , ' 1975-11-18' ) ; 9. Check your work by displaying the contents of the student table. Tip: Use a query to show everything in the table. When you’re done, the window should look like this: GETTING STARTED 4710. Display the contents of the computer table. The window should look like this: 11. Close the MySQL database connection. 12. Exit the Konsole window. GETTING STARTED 48 ADMINISTERING DATABASES 49Administering Databases In this section, you’ll learn how to: • Restart MySQL • Back up a database • Delete a table • Delete a database • Restore a database ADMINISTERING DATABASES 50 Restart MySQL If you’ve shut off your computer since the last exercise, you might need to restart MySQL. First, login to your Linux computer as the Root user. Then restart the MySQL server: 1. Open the Konsole window. Tip: If your terminal prompt is followed by a $, login as the Root user. Type su and press ENTER. Type your Root password and press ENTER again. 2. At the prompt, type: /etc/init. d/mysql start then press ENTER. Tip: If you had to login as the Root user in step 1, type: exit then press ENTER. ADMINISTERING DATABASES 51You’re now logged out of the Root account. Now you’ll have to establish a MySQL client connection to the MySQL server: 3. At the prompt, type: mysql –u root –p then press ENTER. ADMINISTERING DATABASES 52 4. Type the password used to gain root access to the MySQL server: textbook then press ENTER. The window should look like this: ADMINISTERING DATABASES 53Back up a database 1. Make sure the Konsole window is open. If it’s not, open it. 2. Make sure you’re logged out of the MySQL server. Tip: Give the \q; command. 3. At the prompt, type: pwd then press ENTER. ADMINISTERING DATABASES 54 4. The window should look something like this: The Linux command pwd is an acronym for print working directory. In other words, “print the path to the directory I’m working in.” This is the path to your current working directory on this computer: root. When you first open the Konsole window, Linux automatically goes to your home directory. (Linspire’s default user is the Root user, so the current working directory, root, is actually the root user’s home directory.) Each user on a Linux computer has his own home directory, which contains preferences and files unique to that user. ADMINISTERING DATABASES 55Tip: The Linux file system is structured like a pyramid, with the Root directory at the top. Starting from the Root directory, you can dig down into all the other directories, or folders, on the computer. ADMINISTERING DATABASES 56 5. Type: mkdir backups then press ENTER. mkdir is a Linux command to create a new directory, in this case a new directory within your home directory called backups. 6. Type: ls then press ENTER. The ls command lists all the items in the current directory: the backups, Desktop, My Computer, My Documents, and Network directories. ADMINISTERING DATABASES 577. Type: mysqldump –u root –p us_presidents > ►► . /backups/us_presidents. sql then press ENTER. Here’s an explanation of this command string: • mysqldump mysqldump –u root –p us_presidents > . /backups/us_presidents. sql The mysqldump command does exactly what it says – it connects to the MySQL server, selects a database, then dumps all the information from it into a text file. • -u root –p mysqldump –u root –p us_presidents > . /backups/us_presidents. sql The –u command tells mysqldump to use the MySQL root user account to connect to the MySQL server. The –p command tells MySQL to prompt the user for a password. ADMINISTERING DATABASES 58 • us_presidents mysqldump –u root –p us_presidents > . /backups/us_presidents. sql us_presidents is the name of the database you want to back up. • > mysqldump –u root –p us_presidents > . /backups/us_presidents. sql The > character is called a “pipe,” and is a Linux command. Pipe is an apt name for what > does: it pipes, or places, the information provided by mysqldump into a file. • . /backups/ mysqldump –u root –p us_presidents > . /backups/us_presidents. sql . /backups/ is the directory path to us_presidents. sql. Tip: The period in front of the slash (. /) represents the current directory you are working in. • us_presidents. sql mysqldump –u root –p us_presidents > . /backups/us_presidents. sql us_presidents. sql is the name of the file you’re piping the backup to. ADMINISTERING DATABASES 598. At the password prompt, type: textbook then press ENTER. The file us_presidents. sql has now been created in the backups directory. 9. Type: more . /backups/us_presidents. sql then press ENTER. This shows you the contents of us_presidents. sql: ADMINISTERING DATABASES 60 Tip: The more command shows you the contents of any text file. If the size of the file is larger than can fit in your window, you will be shown a percentage at the bottom of the page. Press the spacebar to continue scrolling down. ADMINISTERING DATABASES 61Delete a table 1. Type: mysql –u root –p us_presidents then press ENTER. 2. At the password prompt, type: textbook then press ENTER. The window should look like this: You’re now logged into the MySQL server with the root user account and password. You’re using the us_presidents database. ADMINISTERING DATABASES 62 3. At the mysql> prompt, type: DROP TABLE name; then press ENTER. 4. Type: SHOW TABLES; then press ENTER. The table name has been dropped, or deleted, from the us_presidents database: If you hadn’t made a backup of the us_presidents database and put it in your backups directory, the table name would be gone forever. ADMINISTERING DATABASES 63Delete a database 1. Type: DROP DATABASE us_presidents; then press ENTER. 2. Type: SHOW DATABASES; then press ENTER. The window should look like this: The database us_presidents has been dropped, or deleted. ADMINISTERING DATABASES 64 Restore a database 1. Type: CREATE DATABASE us_presidents; then press ENTER. The database has been restored, but is empty. There are no tables or data in it. 2. Type: \q; then press ENTER. This closes the MySQL client connection. You are closing the connection so you can use a Linux command line pipe ( > ) to restore the database. ADMINISTERING DATABASES 653. Type: mysql –u root –p us_presidents < . /backups/us_presidents. sql then press ENTER. This restores the data in the database us_presidents from the backup. This command string should look familiar: • mysql –u root –p mysql –u root –p us_presidents < . /backups/us_presidents. sql mysql –u root –p establishes a connection to the MySQL server using the MySQL client. The connection is made using the root user account and password. • us_presidents mysql –u root –p us_presidents < . /backups/us_presidents. sql us_presidents is the database you want to pipe data into. ADMINISTERING DATABASES 66 • < mysql –u root –p us_presidents < . /backups/us_presidents. sql Similar to the > pipe we used to backup the database, the < will read text from a file and pipe it into the MySQL server. • . /backups/us_presidents. sql mysql –u root –p us_presidents < . /backups/us_presidents. sql us_presidents. sql is the file in the backups directory that you backed up your us_presidents database to. Now you’re just reading it back into the us_presidents database on the MySQL server. 4. Type: textbook then press ENTER. 5. Type: mysql –u root –p then press ENTER. ADMINISTERING DATABASES 676. At the password prompt, type: textbook then press ENTER. You’ve reestablished a connection to MySQL Server. 7. Type: USE us_presidents; then press ENTER. ADMINISTERING DATABASES 68 8. Type: SHOW TABLES; then press ENTER. The window should look like this: The table name within the database us_presidents has been restored. 9. Type: exit then press ENTER. The MySQL server connection will close. ADMINISTERING DATABASES 69Practice: Administering Databases Task: The Dean of the college is concerned about losing the hardware database, because it would be difficult to reconstruct. Put the Dean's mind at ease: Back up the hardware database and duplicate it to a new database named hardware_duplicate. 1. Create a directory called db_backup in your home directory. 2. Backup the database hardware to the text file hardware. sql at . /db_backup/hardware. sql. 3. Connect to the MySQL database server and enter the MySQL root password to gain access to it. 4. Create a new database named hardware_duplicate. 5. Using the hardware. sql backup file, restore the hardware database to the new database hardware_duplicate. ADMINISTERING DATABASES 70 6. View the tables in the hardware_duplicate database to verify that the backup worked. You should see the tables student, computer, and history: 7. Close the MySQL database connection. 8. Exit the Konsole window. WORKING WITH TABLES 71Working with Tables In this section, you’ll learn how to: • Alter tables • Update records • Delete records WORKING WITH TABLES 72 Alter tables 1. Open the Konsole window. 2. Type: mysql –u root –p us_presidents then press ENTER. This command string establishes a connection to the MySQL server, specifically the database us_presidents. 3. At the password prompt, type: textbook then press ENTER. WORKING WITH TABLES 734. Type: ALTER TABLE name ADD COLUMN party CHAR(25) ; then press ENTER. This command string will add a field, or column, to the table name. MySQL refers to table fields as columns. These commands read pretty much like a sentence in English: ALTER the TABLE name by ADDing a COLUMN called party. Then make party a column that contains a maximum of 25 characters. Now the table name is organized like this, with a new field called party: Column Datatype Properties id INT primary key, not null, auto increment first CHAR(25) last CHAR(25) party CHAR(25) WORKING WITH TABLES 74 5. Type: SELECT * FROM name; then press ENTER. The window should look like this: id first last party1 George Washingtonnull 2 John Adams null 3 Thomas Jefferson null 4 James Madison null WORKING WITH TABLES 75Update records 1. Type: UPDATE name SET party=' Federalist' ►► WHERE (last=' Washington' OR last=' Adams' ) ; then press ENTER. The UPDATE command fills in the blank entries in the name table that were created when you added the party field. This string of commands reads like this: UPDATE the table name. SET the party field to “Federalist” WHERE the last name of the president is either “Washington” OR “Adams.” WORKING WITH TABLES 76 2. Type: SELECT * FROM name; then press ENTER. The window should look like this: id first last party 1 George Washington Federalist2 John Adams Federalist3 Thomas Jefferson 4 James Madison WORKING WITH TABLES 773. Type: UPDATE name SET ►► party=' Democratic Republican' ►► WHERE (last=' Jefferson' OR ►► last=' Madison' ) ; then press ENTER. This updates the party affiliations for Jefferson and Madison. WORKING WITH TABLES 78 4. Type: SELECT * FROM name; then press ENTER. The window should look like this: id first last party 1 George WashingtonFederalist 2 John Adams Federalist 3 Thomas Jefferson Democratic Republican 4 James Madison Democratic Republican WORKING WITH TABLES 79Delete records 1. Type: DELETE FROM name WHERE id>2; then press ENTER. The DELETE command deletes records that match the criteria you set. In this case, you told MySQL to DELETE from the table name any records WHERE the value for id is greater than 2. 2. Type: SELECT * FROM name; then press ENTER. The table should now hold only these records: id first last party 1 George Washington Federalist2 John Adams Federalist WORKING WITH TABLES 80 3. Type: \q; then press ENTER to close the MySQL database connection. 4. Type: exit then press ENTER to exit the Konsole window. WORKING WITH TABLES 81Practice: Working with Tables Task: All of the students in the hardware database are in different departments. Add a column to the students table to keep track of which department a student is in. 1. Open the Konsole window. 2. Type: mysql –u root –p hardware then press ENTER to connect to the MySQL database server, then the hardware database. 3. Type: textbook then press ENTER. 4. In the student table, add a column named department using the ALTER command. The column should hold up to 50 characters: char(50) . WORKING WITH TABLES 82 5. Now UPDATE the values in the new column: Specify that Jack, Lon and Ken be in the Computer Science Department. 6. Run a query that selects everything from the student table. It should look like this: id first_name last_name department 1 Jack Hanson Computer Science2 Lon James Computer Science3 Ken Jones Computer Science WORKING WITH TABLES 837. One of the computers, the Apple iMac, is not used any more, so DELETE it from the computer table. 8. Run a query that selects everything from the computer table. It should look like this: id description 1 Apple iBook 2 Apple PowerBook 9. Close the MySQL database connection. 10. Exit the Konsole window. WORKING WITH TABLES 84 RUNNING QUERIES 85Running Queries In this section, you’ll learn how to: • Sort query results • Add query criteria RUNNING QUERIES 86 Sort query results 1. On the launcher bar, click the button to open a browser window. 2. When the browser opens, go to: www.visibooks.com/books/mysql 3. Right-click the new_us_presidents.sql link. Then save the file in your home directory: RUNNING QUERIES 874. Open the Konsole window and type: mysql –u root –p us_presidents < . /new_us_presidents. sql then press ENTER. This command string pipes the data from the file you just downloaded (new_us_presidents. sql) into the database us_presidents. 5. Type your MySQL root password—textbook—then press ENTER to execute the command string. 6. Type: mysql –u root –p us_presidents then press ENTER. 7. Type your MySQL root password, then press ENTER. This will connect you to the us_presidents database on the MySQL server. RUNNING QUERIES 88 8. At the mysql> prompt, type: SHOW TABLES; then press ENTER. This will SHOW the TABLES in the us_presidents database: The new_us_presidents. sql file you piped in contained two new tables, name and quote. These are now in the us_presidents database. RUNNING QUERIES 899. Type: SELECT * FROM name; then press ENTER. The data in the names table should look like this: RUNNING QUERIES 90 10. Type: SELECT * FROM quote; then press ENTER. The data in the quotes table should look like this: RUNNING QUERIES 9111. Type: SELECT first, middle, last, party ►► FROM name ►► ORDER BY party, last, first; then press ENTER. The query results should look like this: This query lists the presidents' names and parties, then sorts them by party, last name, then first name. RUNNING QUERIES 92 12. Type: SELECT first, middle, last, age ►► FROM name ►► ORDER BY age; then press ENTER. The query results should look like this: This query lists the presidents in order, by their age when they took office. RUNNING QUERIES 9313. Type: SELECT COUNT(age) , AVG(age) ►► FROM name; then press ENTER. The query results should look like this: This query does two things: • COUNT the number of presidents in the name table. • Calculate the AVG (average) age of these presidents when they took office. RUNNING QUERIES 94 14. Type: SELECT party, COUNT(party) ►► FROM name GROUP BY party; then press ENTER. The query results should look like this: This query answers a simple question: how many presidents were in each of the different parties? If you look at a portion of the query… SELECT party, COUNT(party) FROM name GROUP BY party; …it lists the party for each president in the name table. RUNNING QUERIES 95Adding the other two parts… SELECT party, COUNT(party) FROM name GROUP BY party; …changes things. Instead of listing all 20 presidents, the list will now be GROUPed into sub lists of presidents of like parties, and then COUNTed. In the end, you see one row for each party – a total of 5 rows. Each row contains the party name and the number of presidents affiliated with that party. RUNNING QUERIES 96 Add query criteria Up to this point, you’ve only queried from one table. Now use multiple tables in a query: 1. Type: SELECT quote, last FROM quote, name ►► WHERE quote. name_id=name. id ►► ORDER BY last; then press ENTER. The query results should look like this: This query lists all of the quotes FROM the quote table, along with the last names of the presidents (pulled from the name table) who said them. RUNNING QUERIES 97Let's look at each portion of the query: • SELECT quote, last SELECT quote, last FROM quote, name ►► WHERE quote. name_id=name. id ►► ORDER BY last; This part looks the same as in previous queries, except the quote and last fields being queried are in different tables. • FROM quote, name SELECT quote, last FROM quote, name ►► WHERE quote. name_id=name. id ►► ORDER BY last; quote and name are the two tables you’re using in the query. The field quote is in the quote table; the field last is in the name table. • WHERE quote. name_id=name. id SELECT quote, last FROM quote, name ►► WHERE quote. name_id=name. id ►► ORDER BY last; The WHERE criterion links the quote and name tables together. This string tells the database that the name_id of a record in the quote table corresponds to a record with the same id in the name table. For instance, the president whose id is 1 delivered all quotes with an name_id of 1; the president whose id is 2 delivered quotes with name_id of 2, and so on. RUNNING QUERIES 98 • ...

关注我们

关注微信公众号

您选择了以下内容