LPI Linux Certification in a Nutshell Part 27

stop(){ #Stopdaemons.

iftest-f/var/run/sm-client.pid;then echo-n$"Shuttingdownsm-client:"

killprocsm-client RETVAL=$?

echo [$RETVAL-eq0]&&rm-f/var/run/sm-client.pid [$RETVAL-eq0]&&rm-f/var/lock/subsys/sm-client fi echo-n$"Shuttingdown$prog:"

killprocsendmail RETVAL=$?

echo [$RETVAL-eq0]&&rm-f/var/lock/subsys/sendmail return$RETVAL }

#Seehowwewerecalled.

case"$1"in start) start ;; stop) stop ;; reload) reload RETVAL=$?

;; restart) stop start RETVAL=$?

;; condrestart) if[-f/var/lock/subsys/sendmail];then stop start RETVAL=$?

fi ;; status) statussendmail RETVAL=$?

;; *) echo$"Usage:$0{start|stop|restart|condrestart|status}"

exit1 esac

exit$RETVALOn the ExamYou should be familiar with a script's general structure, as well as the use of shebang, test test, if if statements and their syntax (including the trailing statements and their syntax (including the trailing fi fi), return values, exit values, and so on.

Objective 3: SQL Data Management Up until this point, we have focused on data and code stored in text files, the standard method of data storage in the Unix world. This has worked well for many years. However, limitations of this format have required that Linux system administrators become familiar with basic database concepts. Specifically, the Structured Query Language (SQL) syntax that is shared among most database systems is an important tool to have in your sysadmin a.r.s.enal.

There are many SQL database options available in the Linux world. Arguably, the most popular are MySQL, PostgreSQL, and SQLite. Like the flamewars that often arise around the merits of various text editors (vi versus versus emacs emacs being the historical Unix equivalent of the Hatfields versus the McCoys), the choice of a SQL database tends to bring out very strong feelings in Linux users. Due to its popularity among many database-backed open source projects, MySQL tends to be the SQL database that is most often seen on Linux systems (although the smaller footprint and rich API set of SQLite are making it a more popular choice every day). PostgreSQL is often touted as the only "real" Relational Database Management System (RDBMS) in the list of popular databases, and although that technically may be true, the ubiquity of MySQL means that PostgreSQL, at least for now, will continue to play a supporting role in the Linux database world. being the historical Unix equivalent of the Hatfields versus the McCoys), the choice of a SQL database tends to bring out very strong feelings in Linux users. Due to its popularity among many database-backed open source projects, MySQL tends to be the SQL database that is most often seen on Linux systems (although the smaller footprint and rich API set of SQLite are making it a more popular choice every day). PostgreSQL is often touted as the only "real" Relational Database Management System (RDBMS) in the list of popular databases, and although that technically may be true, the ubiquity of MySQL means that PostgreSQL, at least for now, will continue to play a supporting role in the Linux database world.

For the sake of simplicity, this section will use MySQL as an example. However, the SQL commands given here should work across the majority of SQL databases.

Accessing a MySQL Server MySQL is popular enough that it is distributed by default with most modern Linux distributions. Like many client-server applications, MySQL is usually distributed as multiple packages. Most often this means there are separate packages for the server binaries and the client binaries, although there may also be a third package that includes "common" code. Be sure to familiarize yourself with your distribution's package management system so you can effectively determine what software is installed on your system. Refer to Chapter5 Chapter5 for more information on the common Linux package managers. for more information on the common Linux package managers.

a.s.suming that the MySQL server package is installed, the server is started the same way that most Linux services are started: #/etc/rc.d/init.d/mysqldstart StartingMySQL:[OK]

You should now have a running mysqld process, listening on TCP port 3306 by default. You can verify both of these with the ps ps and and netstat netstat commands, respectively: commands, respectively: #psaux|grep-imysqld root18650.00.246561132pts/0S22:200:00/bin/sh /usr/bin/mysqld_safe--datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock--log-error= /var/log/mysqld.log--pidfile= /var/run/mysqld/mysqld.pid mysql19890.33.616150819012pts/0Sl22:200:00 /usr/libexec/mysqld--basedir=/usr--datadir= /var/lib/mysql--user=mysql--pid-file= /var/run/mysqld/mysqld.pid--skipexternal-locking --socket=/var/lib/mysql/mysql.sock #netstat-anp|grep"LISTEN"|grep"mysqld"

tcp000.0.0.0:33060.0.0.0:*LISTEN 1989/mysqld By default, communication with the MySQL server takes place over the TCP port that the server listens on (normally tcp/3306). This communication can be either through the mysql mysql command-line client program, a programming language such as PHP or Perl, or a GUI application. A number of useful GUI applications are available as free downloads from command-line client program, a programming language such as PHP or Perl, or a GUI application. A number of useful GUI applications are available as free downloads from http://dev.mysql.com/downloads/gui-tools. This section will focus on using the mysql mysql command-line program, both interactively and in sh.e.l.l scripts. command-line program, both interactively and in sh.e.l.l scripts.

To access the MySQL server, use the mysql mysql command-line program: command-line program: #mysqlurootphlocalhost Enterpa.s.sword: WelcometotheMySQLmonitor.Commandsendwith;org.

YourMySQLconnectionidis14 Serverversion:5.0.45Sourcedistribution

Type'help;'or'h'forhelp.Type'c'toclearthebuffer.

mysql> The options pa.s.sed on the command line are: -u Username to connect as. This is not the same as the Linux username; MySQL uses its own username and pa.s.sword combinations. They are often similar to the Linux usernames (root, for example), but they bear no relation to them.

-p Prompt for the pa.s.sword. The pa.s.sword can also be given on the command line, but this is considered insecure and is not recommended.

-h What hostname (or IP address) to connect to. Useful if you are connecting to another server on your network. It is not recommended that you connect to MySQL servers over an unsecured network (such as the Internet) without using SSL or some other form of encryption. MySQL offers some basic SSL options, but that level of security is outside the scope of what will appear on the LPI 102 exam.

Now that we have successfully connected to our database, it's time to review basic database concepts.

Database Overview A full understanding of database management recommendations and design methodology is outside the scope of this book and outside the scope of the LPI 102 exam. However, you will need to know the basics of storing data in a MySQL table, managing that data (adding, updating, and deleting), and performing relatively complex queries on that data.

A MySQL server instance allows the creation and access of multiple databases simultaneously. The MySQL server actually creates a directory in the filesystem for each new database created. Each database may contain many tables, the layout of which is set upon initial table creation, but can be modified later. Although there are many options and pros and cons regarding database formats and storage engines, for the purposes of the LPI 102 exam we will a.s.sume the default storage engine (MyISAM) and concern ourselves more with table layout and querying. For more information on storage engines and other advanced MySQL topics, visit http://dev.mysql.com/doc/.

A table is made up of a number of columns, each given a certain datatype that defines what data may be stored in this column. Table13-2 Table13-2 describes some of the more common MySQL datatypes. describes some of the more common MySQL datatypes.

Table13-2.Common MySQL datatypes

Datatype Description INTEGER A normal-size integer. The signed range is 2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

FLOAT A floating-point number.

BOOLEAN Stored as a single character integer. A value of zero is considered false. Nonzero values are considered true.

DATE A date in the range of '1000-01-01' to '9999-12-31'. Dates are displayed as YYYY-MM-DD by default.

DATETIME A date and time combination in the range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

CHAR A fixed-length string in the range of 0255 characters.

VARCHAR A variable-length string. Before MySQL 5.0.3, the maximum length of a VARCHAR was 255 characters. Since 5.0.3, the maximum length is 65535.

BLOB A binary format with a maximum size of 65535 bytes.

TEXT A text format with a maximum size of 65535 characters.

Why are datatypes important? Wouldn't it be easier if we had only two datatypes, ASCII and binary? It is important to specify datatypes because MySQL queries are datatype-aware. For example, if I want to create a query that says, "Give me all the records earlier than a certain date," I can use the less-than operator (<) on="" a="" column="" that="" has="" a="" datatype="" of="" date.="" mysql="" will="" know="" that="" less-than="" in="" this="" case="" means="" "on="" a="" date="" earlier="" than="" the="" one="" given."="" this="" would="" be="" much="" more="" difficult="" to="" accomplish="" if="" we="" did="" not="" have="" strongly="" defined="">

Let's start with an example table and walk through the creation steps. For our example, we will create a database called community community. In this database, our first table will be families families, which will store names and birthdates for the members of a family: #mysqlurootphlocalhost WelcometotheMySQLmonitor.Commandsendwith;org.

YourMySQLconnectionidis53 Serverversion:5.0.45Sourcedistribution

Type'help;'or'h'forhelp.Type'c'toclearthebuffer.

mysql>createdatabasecommunity; QueryOK,1rowaffected(0.02sec)

mysql>usecommunity; Databasechanged mysql>CREATETABLEfamilies( ->idINTEGERUNSIGNEDNOTNULLAUTO_INCREMENT, ->father_nameVARCHAR(100), ->father_dobDATE, ->mother_nameVARCHAR(100), ->mother_dobDATE, ->number_of_childrenINTEGERUNSIGNED, ->child1_nameVARCHAR(100), ->child1_dobDATE, ->child2_nameVARCHAR(100), ->child2_dobDATE, ->child3_nameVARCHAR(100), ->child3_dobDATE, ->notesTEXT, ->PRIMARYKEY(id) ->); QueryOK,0rowsaffected(0.05sec) The CREATE TABLE CREATE TABLE command is used to create a table in an existing database. Each column of the table is named and given a datatype. In addition, options can be given to columns to change their behavior. For example, we gave the command is used to create a table in an existing database. Each column of the table is named and given a datatype. In addition, options can be given to columns to change their behavior. For example, we gave the id id column the option column the option AUTO_INCREMENT AUTO_INCREMENT. This means that MySQL will automatically increment this integer value for every row added to the table. This is a common practice and ensures that every row will be unique, because even if all the other columns have the same data, the id id field will always be different. This is enforced by the line "PRIMARY KEY (id)". By naming the field will always be different. This is enforced by the line "PRIMARY KEY (id)". By naming the id id field as the primary key, we're instructing MySQL to enforce the uniqueness of this value for each row. This means that if we tried to add a row of data that included an field as the primary key, we're instructing MySQL to enforce the uniqueness of this value for each row. This means that if we tried to add a row of data that included an id id value that already existed, MySQL would display an error and the data would not be added. value that already existed, MySQL would display an error and the data would not be added.

The other fields added are names, which we defined as a varchar with an upper limit of 100 characters (more than enough to hold a first name, middle name and last name), dates of birth (denoted by the DATE datatype), and a notes field, which we defined as type TEXT.

To add data to this table, we use the INSERT INSERT command: command: mysql>INSERTintofamilies ->(father_name,father_dob,mother_name,mother_dob, ->number_of_children,child1_name,child1_dob, ->child2_name,child2_dob,notes) ->VALUES ->("JoeSmith","1970-04-01","JanSmith","1970-05-10", ->"2","JimmySmith","2000-08-10","JaneySmith", ->"2002-12-12","Thisisthesmithfamily ->ofChicago,IL"); QueryOK,1rowaffected,0warnings(0.02sec) Notice that we did not insert data into every column of the table. By identifying the fields we wished to use and then the values for each, we're able to insert exactly the data we wish into the table. To view the data we just added, use the SELECT SELECT command: command: mysql>SELECTid,father_name,mother_name,number_of_childrenfromfamilies; +----+-------------+-------------+--------------------+ |id|father_name|mother_name|number_of_children| +----+-------------+-------------+--------------------+ |1|JoeSmith|JanSmith|2| +----+-------------+-------------+--------------------+ 1rowinset(0.00sec) In this case, we instructed the SELECT SELECT statement to show us only the columns statement to show us only the columns id id, father_name father_name, mother_name mother_name, and number_of_children number_of_children. If we had wished to see all of the columns, the command would have been SELECT * from families SELECT * from families.

Notice that the id id column has a value of 1. We did not insert this value; it was set by the MySQL server when we inserted our first record. Subsequent inserts will continue to increment this number. Let's add one more row of data: column has a value of 1. We did not insert this value; it was set by the MySQL server when we inserted our first record. Subsequent inserts will continue to increment this number. Let's add one more row of data: mysql>INSERTintofamiliesset ->father_name="KenAnderson", ->father_dob="1971-06-06", ->mother_name="MaryAnderson", ->mother_dob="1971-01-29", ->number_of_children="3", ->child1_name="ShawnAnderson", ->child1_dob="1999-10-17", ->child2_name="KyleAnderson", ->child2_dob="2001-10-12", ->child3_name="LillieAnderson", ->child3_dob="2004-11-12", ->notes="ThisistheAndersonfamilyofOmaha,NE"; QueryOK,1rowaffected(0.02sec) In this example, we accomplished the same goal as our original INSERT INSERT statement, but we used an alternate syntax. statement, but we used an alternate syntax.

Now repeat our SELECT SELECT query to verify that the table contains two rows: query to verify that the table contains two rows: mysql>SELECTid,father_name,mother_name,number_of_childrenfromfamilies; +----+--------------+---------------+--------------------+ |id|father_name|mother_name|number_of_children| +----+--------------+---------------+--------------------+ |1|JoeSmith|JanSmith|2| |2|KenAnderson|MaryAnderson|3| +----+--------------+---------------+--------------------+ 2rowsinset(0.01sec) Now that we know how to add data to our table, the next step is modifying existing data. In our initial insert, we didn't capitalize the last name "smith" in the notes notes column. Use the column. Use the UPDATE UPDATE command with a command with a WHERE WHERE clause to correct this: clause to correct this: mysql>UPDATEfamiliesset ->notes="ThisistheSmithfamilyofChicago,IL"

->WHEREid="1"; QueryOK,1rowaffected(0.01sec) Rowsmatched:1Changed:1Warnings:0 The UPDATE UPDATE command is used to modify values in rows that already exist. In order to identify what rows to modify (a.s.suming you don't want to update all rows in the table), you need to give a command is used to modify values in rows that already exist. In order to identify what rows to modify (a.s.suming you don't want to update all rows in the table), you need to give a WHERE WHERE clause that uniquely identifies the rows you wish to modify. In this instance, we took advantage of the fact that the clause that uniquely identifies the rows you wish to modify. In this instance, we took advantage of the fact that the id id field is unique among rows to ensure that our modification affected only the row we wanted it to affect. Use the field is unique among rows to ensure that our modification affected only the row we wanted it to affect. Use the SELECT SELECT command again to verify our change took place: command again to verify our change took place: mysql>selectid,notesfromfamilies; +----+------------------------------------------+ |id|notes| +----+------------------------------------------+ |1|ThisistheSmithfamilyofChicago,IL| |2|ThisistheAndersonfamilyofOmaha,NE| +----+------------------------------------------+ 2rowsinset(0.00sec) To remove data from the table, the DELETE DELETE command is used. The syntax is similar to the command is used. The syntax is similar to the UPDATE UPDATE command: command: mysql>deletefromfamilieswhereid="2"; QueryOK,1rowaffected(0.03sec)

mysql>selectid,notesfromfamilies; +----+-----------------------------------------+ |id|notes| +----+-----------------------------------------+ |1|ThisistheSmithfamilyofChicago,IL| +----+-----------------------------------------+ 1rowinset(0.00sec) The SELECT SELECT command shows us that we're back to one record in the table. command shows us that we're back to one record in the table.

It is possible to alter the layout of a table after it has been created. You can either modify the datatype of an existing column or add/delete columns from the table. Be careful when you modify a datatype on a column that already contains data because you run the risk of losing your data! For example, if you change a column from varchar(255) varchar(255) to to char(1) char(1), you will lose all but the first character of any data you had in that column.

The ALTER TABLE ALTER TABLE command is used to modify a table after it has been created. Let's add two new columns to our table to track the city and state where the families live: command is used to modify a table after it has been created. Let's add two new columns to our table to track the city and state where the families live: mysql>ALTERTABLEfamilies ->ADDCOLUMNcityVARCHAR(100)AFTERnotes, ->ADDCOLUMNstateCHAR(2)AFTERcity; QueryOK,1rowaffected(0.00sec) Records:1Duplicates:0Warnings:0 Reinsert our second family that we previously deleted: mysql>insertintofamiliessetfather_name="KenAnderson", ->father_dob="1971-06-06",mother_name="MaryAnderson", ->mother_dob="1971-01-29",number_of_children="3",child1_name= ->"ShawnAnderson",child1_dob="1999-10-17",child2_name= ->"KyleAnderson",child2_dob="2001-10-12",child3_name= ->"LillieAnderson",child3_dob="2004-11-12",notes= ->"ThisistheAndersonfamilyofOmaha,NE"; Use UPDATE UPDATE to add city and state values: to add city and state values: mysql>updatefamiliessetcity="Chicago",state="IL"whereid="1"; QueryOK,1rowaffected(0.01sec) Rowsmatched:1Changed:1Warnings:0

mysql>updatefamiliessetcity="Omaha",state="NE"whereid="3"; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0

mysql>selectid,city,state,notesfromfamilies; +----+---------+-------+------------------------------------------+ |id|city|state|notes| +----+---------+-------+------------------------------------------+ |1|Chicago|IL|ThisistheSmithfamilyofChicago,IL| |3|Omaha|NE|ThisistheAndersonfamilyofOmaha,NE| +----+---------+-------+------------------------------------------+ 2rowsinset(0.00sec) Notice that the Anderson family is now id id "3" instead of "2". ID 2 was removed by our "3" instead of "2". ID 2 was removed by our DELETE DELETE command. The autoupdate option of MySQL will never use the number 2 again in this column. command. The autoupdate option of MySQL will never use the number 2 again in this column.

Aggregate Functions Aggregate functions allow you to group queried data in meaningful ways. SQL databases are more than just simple data stores; the complex functionality of SQL allows you to extract meaningful data very easily.

A common aggregate function is GROUP BY GROUP BY. This function allows you to perform operations on groups of data. Let's add some more data to our example database and see what options GROUP BY GROUP BY gives us. gives us.

mysql>insertintofamiliessetfather_name="AdamWhite", ->father_dob="1969-06-08",mother_name="TinaWhite", ->mother_dob="1969-01-30",number_of_children="1", ->child1_name="EdWhite",child1_dob="1998-11-17", ->notes="ThisistheWhitefamilyofBellevue,NE", ->city="Bellevue",state="NE"; QueryOK,1rowaffected,0warnings(0.00sec) mysql>insertintofamiliessetfather_name="BillCarpenter", ->father_dob="1968-06-01",mother_name="LindaCarpenter", ->mother_dob="1970-02-30",number_of_children="4", ->child1_name="JoeCarpenter",child1_dob="1998-12-17", ->child2_name="BobCarpenter",child2_dob="1996-01-01", ->child3_name="LukeCarpenter",child3_dob="2004-08-08", ->notes="ThisistheCarpenterfamilyofLincoln,NE", ->city="Lincoln",state="NE"; QueryOK,1rowaffected,0warnings(0.00sec)

mysql>selectid,father_name,city,statefromfamilies; +----+----------------+----------+-------+ |id|father_name|city|state| +----+----------------+----------+-------+ |1|JoeSmith|Chicago|IL| |3|KenAnderson|Omaha|NE| |4|AdamWhite|Bellevue|NE| |5|BillCarpenter|Lincoln|NE| +----+----------------+----------+-------+ 4rowsinset(0.01sec)

mysql>selectcount(state),statefromfamiliesGROUPBYstate; +--------------+-------+ |count(state)|state| +--------------+-------+ |1|IL| |3|NE| +--------------+-------+ 2rowsinset(0.00sec) We've added two new families, so our table now contains four rows. The last query is an example of using the GROUP BY GROUP BY syntax. We asked MySQL, "How many different states are represented in our table?" You also could have achieved the same result with a combination of the syntax. We asked MySQL, "How many different states are represented in our table?" You also could have achieved the same result with a combination of the mysql mysql command-line program and some sh.e.l.l scripting knowledge: command-line program and some sh.e.l.l scripting knowledge: #echo"selectstatefromfamilies"| >mysqlsurootppa.s.swordhlocalhostcommunity| >sort|uniqc 1IL 3NE If you are more familiar with the Linux command-line text processing tools, you can oftentimes depend on them to pa.r.s.e the data that a SQL query will return. As you can see from this example, the mysql mysql command-line program can process queries on standard input and return results on standard output. The command-line program can process queries on standard input and return results on standard output. The -s -s option tells option tells mysql mysql to be "silent," meaning not to return any column names and only return data, which is usually what you want if you're going to be pa.s.sing the results to another program for processing. to be "silent," meaning not to return any column names and only return data, which is usually what you want if you're going to be pa.s.sing the results to another program for processing.

Another option MySQL gives you for outputting data is the ORDER BY ORDER BY function. This simply changes the sort order of the results. Let's sort our families by the father's date of birth, from youngest to oldest: function. This simply changes the sort order of the results. Let's sort our families by the father's date of birth, from youngest to oldest: mysql>selectid,father_name,father_dobfromfamiliesORDERBYfather_dobasc; +----+----------------+------------+ |id|father_name|father_dob| +----+----------------+------------+ |5|BillCarpenter|1968-06-01| |4|AdamWhite|1969-06-08| |1|JoeSmith|1970-04-01| |3|KenAnderson|1971-06-06| +----+----------------+------------+ 4rowsinset(0.00sec) MySQL understands that the father_dob father_dob column is type DATE and sorts accordingly. The modifiers to column is type DATE and sorts accordingly. The modifiers to ORDER BY ORDER BY can be can be asc asc (ascending) or (ascending) or desc desc (descending). (descending).

Mult.i.table Queries The final concept to describe in our basic SQL overview is that of JOIN JOIN. So far, we have concerned ourselves with querying only one table. This is fine for simple data storage requirements, but as data complexity grows, so does the need for multiple tables. As long as there is a relationship between data elements in the tables, the table values can be JOIN JOINed in a query.

To see an example of this, we need to create another table that has a relationship with the first table. We will use this second table to store pet information for each family.

mysql>CREATETABLEpets( ->idINTEGERUNSIGNEDNOTNULLAUTO_INCREMENT, ->family_idINTEGERUNSIGNEDNOTNULL, ->typeVARCHAR(45)NOTNULL, ->nameVARCHAR(45)NOTNULL, ->PRIMARYKEY(id) ->); QueryOK,0rowsaffected(0.02sec)

mysql>showtables; +---------------------+ |Tables_in_community| +---------------------+ |families| |pets| +---------------------+ 2rowsinset(0.00sec)

mysql>describepets; +-----------+------------------+------+-----+---------+----------------+ |Field|Type|Null|Key|Default|Extra| +-----------+------------------+------+-----+---------+----------------+ |id|int(10)unsigned|NO|PRI|NULL|auto_increment| |family_id|int(10)unsigned|NO|||| |type|varchar(45)|NO|||| |name|varchar(45)|NO|||| +-----------+------------------+------+-----+---------+----------------+ 4rowsinset(0.02sec)

mysql>insertintopets(family_id,type,name)VALUES("1","dog","Max"); QueryOK,1rowaffected(0.01sec)

mysql>insertintopets(family_id,type,name)VALUES("3","cat","Paws"); QueryOK,1rowaffected(0.01sec)

mysql>insertintopets(family_id,type,name)VALUES("4","cat","m.u.f.fy"); QueryOK,1rowaffected(0.01sec)

mysql>insertintopets(family_id,type,name)VALUES("4","dog","Rover"); QueryOK,1rowaffected(0.00sec) The important column in this second table is family_id family_id. We need to ensure that as we add data to this table, we a.s.sociate the data with the correct ID from the families families table. This expresses the relationship between the families and their pets, and allows us to query against them. We have added four pets to our table: family ID #1 (the Smiths) have a dog named Max, family ID #3 (the Andersons) have a cat named Paws, and family ID #4 (the Whites) have a cat named m.u.f.fy and a dog named Rover. Here is a simple example of a join that queries values from both tables: table. This expresses the relationship between the families and their pets, and allows us to query against them. We have added four pets to our table: family ID #1 (the Smiths) have a dog named Max, family ID #3 (the Andersons) have a cat named Paws, and family ID #4 (the Whites) have a cat named m.u.f.fy and a dog named Rover. Here is a simple example of a join that queries values from both tables: mysql>selecta.id,a.father_name,a.mother_name,b.type,b.name fromfamiliesa,petsbwherea.id=b.family_id; +----+--------------+---------------+------+-------+ |id|father_name|mother_name|type|name| +----+--------------+---------------+------+-------+ |1|JoeSmith|JanSmith|dog|Max| |3|KenAnderson|MaryAnderson|cat|Paws| |4|AdamWhite|TinaWhite|cat|m.u.f.fy| |4|AdamWhite|TinaWhite|dog|Rover| +----+--------------+---------------+------+-------+ 4rowsinset(0.02sec) This syntax is slightly different from our previous SELECT SELECT statements. First, notice that we have to qualify the column names with a table identifier. In this case, we're using statements. First, notice that we have to qualify the column names with a table identifier. In this case, we're using a a and and b b. After the FROM FROM statement, we are aliasing the statement, we are aliasing the families families table as table as a a and the and the pets pets table as table as b b. This is common shorthand that makes our SQL statements shorter; otherwise, we'd have to use families.id families.id, families.father_name families.father_name, etc. Finally, the WHERE WHERE clause of clause of a.id = b.family_id a.id = b.family_id expresses the relationship between the tables. This ensures that we know which pet belongs to which family. As you can see, the expresses the relationship between the tables. This ensures that we know which pet belongs to which family. As you can see, the family_id family_id column is not a primary key, because it does not need to be unique. Families can have multiple pets, as the White family does in our example. column is not a primary key, because it does not need to be unique. Families can have multiple pets, as the White family does in our example.

Although this SELECT SELECT statement did not actually use the statement did not actually use the JOIN JOIN syntax, it is an example of the simplest kind of join. Notice that we are missing a family from our results, however: the Carpenter family does not have any pets, so our syntax, it is an example of the simplest kind of join. Notice that we are missing a family from our results, however: the Carpenter family does not have any pets, so our SELECT SELECT statement did not select them. If we want families to display in the output even if they do not have pets, we need to use the statement did not select them. If we want families to display in the output even if they do not have pets, we need to use the LEFT JOIN LEFT JOIN syntax. This is functionally very similar to the previous join of two tables, but it differs in two important ways: the syntax is quite a bit different, and because special consideration is given to the table on the LEFT, each item present in the left table will display in the results, syntax. This is functionally very similar to the previous join of two tables, but it differs in two important ways: the syntax is quite a bit different, and because special consideration is given to the table on the LEFT, each item present in the left table will display in the results, even if even if there is not a match with the other joined table. Compare the output of this there is not a match with the other joined table. Compare the output of this LEFT JOIN LEFT JOIN statement with the previous join output: statement with the previous join output: mysql>selectfamilies.id,families.father_name,families.mother_name, ->pets.type,pets.namefromfamilies ->LEFTJOINpetsonfamilies.id=pets.family_id; +----+----------------+-----------------+------+-------+ |id|father_name|mother_name|type|name| +----+----------------+-----------------+------+-------+ |1|JoeSmith|JanSmith|dog|Max| |3|KenAnderson|MaryAnderson|cat|Paws| |4|AdamWhite|TinaWhite|cat|m.u.f.fy| |4|AdamWhite|TinaWhite|dog|Rover| |5|BillCarpenter|LindaCarpenter|NULL|NULL| +----+----------------+-----------------+------+-------+ 5rowsinset(0.01sec) The Carpenter family now appears in the query results, but with NULL values for type type and and name name, since they did not have a corresponding record in the pets pets table. We used the more detailed table. We used the more detailed SELECT SELECT syntax in this example, keeping the full table names instead of aliasing them with syntax in this example, keeping the full table names instead of aliasing them with a a and and b b but the queries are the same; the aliasing is just for readability. but the queries are the same; the aliasing is just for readability.

On the ExamSQL can be a complicated subject, but the LPI 102 exam will test you only on the basic syntax of adding and querying data. Make sure you are familiar with the common elements of INSERT INSERT and and SELECT SELECT statements, and can describe the various datatypes available in MySQL. statements, and can describe the various datatypes available in MySQL.

Chapter14.The X Window System (Topic 106)