mysql| Data | Database summary
In this chapter, we will focus on how to work with the MySQL database using Structured Query Language (SQL).
(2002-08-29-14:11:10)
--------------------------------------------------------------------------------
By Wing, Origin: Linuxaid
Chapter II: Introduction to MySQL
Welcome back to this tutorial! In the previous chapter, we learned about installing and configuring both PHP and MySQL software. In this chapter, we will focus on working in the MySQL database using Structured Query Language (SQL).
Getting Started with databases
As the previous chapter briefly explains, PHP is a server-side scripting language that allows you to add instructions to your Web page so that your Web services software (possibly the apache,personal web Server, or any other software, executes the page before sending it to the requesting browser. In that simple example, I showed how to insert the current date each time on the Web page that accepts the request.
are clear, but if you add a database to it, it really interests us. A database server (where we are MySQL) is a program that stores a lot of information in a format that allows you to easily access data using scripting languages like PHP. For example, you can use PHP to get a list of jokes in your database and display them to your Web site.
, jokes are stored completely in the database. There are two advantages to doing so. First, you no longer need to write an HTML file for every joke you have, you just need to write a PHP file to draw any jokes from the database and show it, and secondly, add jokes to your Web site and just add jokes to the database. PHP code can automatically display new jokes when new jokes are included in the list.
Let's use this example to see how data is stored in a database. A database contains one or several data tables (table), and each data table contains a list of things. For our joke database, we might initially need a datasheet called "jokes" that contains a list of jokes. Each data table in the database contains one or several data columns (column) or Data fields (field). Back in our example, our "jokes" datasheet might have two columns: the body of the joke and the date the joke was added to the database. Each joke stored in the datasheet is called a row. To understand all the terms mentioned here, you can look at the following picture:
I added a data column called "ID" in addition to the two data columns of the joke body ("Joketext") and the Add date ("Jokedate"). The function of this data column is to assign a unique number to each joke so that we can easily refer to and distinguish between the jokes.
To learn, there are three columns and two rows in the data table above. Each row contains the ID of a joke, its body, and the date it was added. With these basic terms in hand, we'll start using MySQL.
Login to MySQL
The standard interface for SQL databases is to connect to the MySQL service software (installed in the first chapter) and enter commands at the same time. To connect to the server, we need to use the MySQL client program. If you installed your own MySQL service software, whether you installed it under Windows or installed it in some UNIX versions, you should have installed the client program in the same location as the installation service. In Linux, this program is called MySQL, and its location defaults to the/usr/local/mysql/bin directory. Under Windows, this program is called Mysql.exe, and its location defaults to the C:mysqlin directory.
Is your own MySQL server installed (for example, you are working on your web hosting provider's MySQL server), then there are two ways to connect to the MySQL server, the first way is to use Telnet to log on to your web host's server, and then run MySQL there. The second option is to download and install the MySQL client program from http://www.mysql.com/(for Windows and for Linux, for free). Both of these methods work well, and your web host may support one or both (you need to ask).
Choose which method, no matter what kind of system you are using, you should end up at a command line to execute MySQL client to connect to your MySQL server, you need to enter the following command:
Mysql-h-u-p
Replace your MySQL server with the host name or IP address of the computer on which it is running. If you run the client program on the same computer that is running the service, you can use the-H and direct-h localhost. Should be your MySQL username. If you are installing your own MySQL server, this should be root. If you are using the MySQL service for your web hosting provider, this should be the MySQL user they specify for you.
The parameter tells the program to prompt you to enter your password, this will appear immediately after you enter the above command. If you are installing MySQL yourself, your password is the root password you selected in the first chapter. If you are using the MySQL service for your web hosting provider, this should be the MySQL password they give you.
The MySQL client program connects to the MySQL server and returns you to a MySQL command line:
Mysql>
The Ysql server is actually connected to several databases (which allows a web host to set up the same MySQL server for several users). So your next step should be to select a working database. First, get a list of the databases on the current server. Enter the following command (don't forget the semicolon!) ), and then hit enter.
Mysql> show DATABASES;
L will show you the list of databases on the server. If this is a newly installed server (that is, this is the one you installed yourself in the first chapter). This list will be like this:
+----------+
| Database |
+----------+
| MySQL |
| Test |
+----------+
2 rows in Set (0.11 sec)
The L server uses the first database called MySQL to manage users and their passwords and permissions. Now we don't care about the database for the time being, and we'll discuss it later in the chapter. The second call test is a data module. You can delete this database and not use it in our tutorials (we'll build some databases ourselves). Delete Something in MySQL is called "dropping", to delete the test database, the correct command should be:
mysql> DROP DATABASE test;
Into this command, and hit enter, MySQL will delete this database and return to query OK. Note that you will not be prompted for information such as "This is OK". So you must be very careful in the MySQL input command. As we've seen here, you can completely delete the database--all the information contained therein--with just one command!
Before we go to the next step, let's look at the connection to the MySQL command line. As we have noted, all commands in MySQL must end with a semicolon (;). If you forget this semicolon, MySQL will think that you have not finished typing your command, and will allow you to continue typing in this next line:
Mysql> Show
-> DATABASES;
L When waiting for you to enter the remainder of the command, the prompt changes from mysql> to->. This is useful for a long command, and you can enter your command in several lines.
The way you find your command wrong, you can completely cancel the current command (translator: refers to the command not yet executed) and from the beginning. To complete this work, you only need to enter C and press ENTER:
Mysql> DROP databasecmysql>
L will completely ignore the command you just entered and return to the prompt to wait for your next command.
When you exit the MySQL client program, you only need to enter quit or exit (the two commands are identical). This is the only command that can be executed without a semicolon ending.
Mysql> Quitbye
What is SQL?
The commands we use to tell MySQL what to do in the process are actually part of a specification called Structured Query Language (SQL). The commands in SQL are also called queries (in this tutorial, we use these two names alternately).
Standard language for interacting with most databases, so even if you don't use MySQL in the future, instead of using Microsoft SQL Server, you'll find that most commands are the same. You have to understand the difference between SQL and MySQL. MySQL is the database service software you are using. SQL is the language you use to implement and interact with the database.
Create a database
The web host provider has assigned you a database to work with. You wait patiently, wait a moment, we will continue to discuss the following questions with you. If you are working on a MySQL server that you have installed on your own. By executing the following command, it is easy for you to create a database:
Mysql> CREATE DATABASE jokes;
The name of the library is jokes, which is in line with the example we are working on. In fact, you can give your database any name you like. However, if you are working on a web hosting provider's MySQL server, they may have set up a database for you and you won't be able to choose the name of the database.
Already have a database, we need to tell MySQL we want to use this database. The following command should not be too hard to remember:
Mysql> use jokes;
To start using your database. Before you add a datasheet to it, the database will be empty, and our first step should be to create a datasheet to save our jokes.
Create a data table
The SQL commands encountered are very simple, but because the data tables are flexible, the commands to build them are much more complicated. The basic format for creating a datasheet is this:
mysql> CREATE TABLE <table name;> (
-> <column 1 name;> <col. 1 type;> <col. 1 details;>,
-> <column 2 name;> <col. 2 type> <col. 2 details;>,
-> ...
->);
Now go back to our example "jokes" table. This table has three data columns: ID (a number), Joketext (the body of the joke), and jokedate (date of accession). The command to create this table should be this:
Mysql> CREATE TABLE Jokes (
-> ID INT not NULL auto_increment PRIMARY KEY,
-> Joketext TEXT,
-> jokedate DATE not NULL
->);
It looks complicated, doesn't it? Let's break it down:
The first line is relatively simple; it shows that we want to create a new datasheet named jokes.
The second line shows that we need a data column called ID, and that the type of the column should be an integer (INT). This line also defines some other information for this data column. First, this line is not allowed to be empty (not NULL). Second, if you don't specify a value for a column, MySQL chooses to use a value that is larger than the current maximum value (auto_increment). Finally, this data column is the only identifier for the data table, so all values in the data column should be PRIMARY (KEY).
The third line is very simple; This means that we need a data column called Joketext, and the type of this column should be a text.
Line four defines our last column, the column name is Jokedate, and the type of the column is the date type, and the column cannot be empty (not null).
Note that when we enter the SQL command, the capitalization is completely free, but the MySQL service running under a unix-based system, because we must conform to the directories and files in the MySQL data directory, must be case-sensitive when we encounter the database name and table name. Otherwise, MySQL is completely insensitive to case, with one exception, table names, column names, and other names that appear multiple times in the same command must be spelled exactly as they are.
We should also note that we specify a specified type for each column that we create. ID is an integral type, Joketext is a text type, and Jokedate is a date type. MySQL allows us to define a type for each column. This will not only help you organize your data, but you can use it to compare data (as we'll see below). To get a complete list of data types that MySQL supports, you can refer to the MySQL user manual.
In short, if you enter the above command correctly, MySQL returns to Query OK and builds your first datasheet for you. If there is something wrong with your input, MySQL will tell you that the name you typed has a problem and will give you a hint of where it doesn't understand.
For such a complex command, it is quite monotonous to end up with only query OK. Let's see if your new datasheet is properly established. Enter the following command:
Mysql> show TABLES;
The response should be this:
+-----------------+
| Tables in Jokes |
+-----------------+
| Jokes |
+-----------------+
1 row in Set
This is a list of all the tables in our database (which we named jokes earlier). This list contains only one datasheet: Jokes, this is the table we just built. It seems to be all right, let's take a closer look at this jokes table:
Mysql> DESCRIBE jokes;
+----------+---------+------+-----+------------+- -
| Field | Type | Null | Key | Default | ...
+----------+---------+------+-----+------------+- -
| ID | Int (11) | | PRI | 0 | ...
| Joketext | Text | YES | | NULL |
| Jokedate | Date | | | 0000-00-00 |
+----------+---------+------+-----+------------+- -
3 Rows in Set
A list of data columns is provided here. As we can see, this table has three columns, which are represented as three rows in the returned result table. Detailed information more difficult to understand, but you look carefully, you should still be able to understand its general meaning. Don't worry too much about the matter. We will continue to learn and we will add some jokes to the table.
We also need to know something else, such as deleting a table. This is as simple as deleting a database, and it is simply frightening. The commands are almost the same:
Mysql> DROP TABLE;
Adding data to the database
Our database and datasheet are all set up, and now we should add jokes to our database. The command to add data to the database is called insert. There are two basic forms of this command:
Mysql> INSERT into <table name;> SET
-> columnName1 = value1,
-> columnName2 = value2,
-> ...
->;
Mysql> INSERT into <table name;>
-> (columnName1, columnName2, ...)
-> VALUES (value1, value2, ...);
So to add jokes to our datasheet, we can select one of the following two commands:
Mysql> INSERT into Jokes SET
-> Joketext = "Why did chicken cross?" To the ' other side! ',
-> jokedate = "2000-04-01";
Mysql> INSERT into jokes
-> (Joketext, jokedate) VALUES (
-> "Why did the chicken cross the road?" Toget to the other side! ",
-> "2000-04-01"
->);
Note that in the second format of the Insert command, the order of the columns you give must be the same as the order in which you give the data.
Now that you know how to add to the table, let's look at how to display the contents of the table.
To display stored data
The command to display data stored in the tables in your database, called Select, is undoubtedly the most complex command in the SQL language. It is complicated because the main advantage of the database is that it can be used to give data in a mobile way. And mobility is achieved by this command.
The following command lists all the data stored in the table jokes:
Mysql> SELECT * from jokes;
This command means "pick everything from the jokes". If you try to enter this command, you will see this display:
+----+---------------------------------------
------------------------+------------+
| ID | Joketext
| Jokedate |
+----+---------------------------------------
------------------------+------------+
| 1 | Why did the chicken cross the road? To
Get to the other side! | 2000-04-01 |
+----+---------------------------------------
------------------------+------------+
1 row in Set (0.05 sec)
It seems a bit confusing, because the joketext is too long. For this reason, we may want to tell MySQL to omit the Joketext column, and this command will implement this function:
Mysql> SELECT ID, jokedate from jokes;
This time we stop telling it to "pick everything up" and we tell it what columns we are interested in showing. The result should be this:
+----+------------+
| ID | Jokedate |
+----+------------+
| 1 | 2000-04-01 |
+----+------------+
1 row in Set (0.00 sec)
But can we show a little bit of the content of the joke body? As a supplement to the columns we want to display, we can use "functions" to change the columns we want to display. There's a function called Left that lets us tell MySQL that when we display a data column, it displays a maximum of several characters. For example, if we want to display only the first 20 characters of the Joketext column:
Mysql> SELECT ID, left (joketext,20), jokedate from jokes;
+----+----------------------+------------+
| ID | Left (joketext,20) | Jokedate |
+----+----------------------+------------+
| 1 | Why did the Chicken | 2000-04-01 |
+----+----------------------+------------+
1 row in Set (0.05 sec)
Do you know if it's working? Another useful function is count, by which we can simply get the number of returned results. For example, if we want to find out how many jokes we have stored in our table, we can use the following command:
Mysql> SELECT COUNT (*) from jokes;
+----------+
| COUNT (*) |
+----------+
| 1 |
+----------+
1 row in Set (0.06 sec)
As we can see, there is only one joke in our table.
So far, all of our examples are for all the records in the table. As a complement to the Select command, we can use a "where clause" so that we can limit the results returned. Take a look at the following example:
Mysql> SELECT COUNT (*) from jokes
-> WHERE jokedate >= "2000-01-01";
This query will count all the dates "greater than or equal to" January 1, 2000, and for a date "greater than or equal to" means "on or after the day".
There is a special use to find a record that contains a paragraph of text. You can look at this query statement:
Mysql> SELECT Joketext from jokes
-> WHERE joketext like "%chicken%";
This query statement shows the body of all the Joketext columns that contain the word chicken jokes. The LIKE keyword tells MySQL that the specified column must match the given expression. Here, the expression we use is "%chicken%". The% here indicates that the word chicken can appear before or after any string.
Conditions can also be combined in a WHERE clause to make more complex queries. For example, we want to display the April 2000 knock-knock joke, we can use the following query statement:
Mysql> SELECT Joketext from Jokes WHERE
-> joketext like "%knock knock%" and
-> jokedate >= "2000-04-01" and
-> jokedate < "2000-05-01";
Add a few more jokes to the table and experiment with the SELECT statement. Familiarity with SELECT statements can help you learn this tutorial.
We can also do a lot of things with the SELECT statement, but we won't discuss it in detail here, and we'll talk about some of the other advanced features when we need it. If you're too curious to wait any longer, you can look at the MySQL user manual.
To modify stored data
Once you have entered some data into the database table, you may want to make some changes. such as correcting spelling errors and other data about jokes, all of these changes can be done with the update command. This command contains the basics of the Insert command (in terms of setting the numeric aspects of the column) and the Select command (in selecting the object to be changed). The basic format of the update command is this:
Mysql> UPDATE SET
-> =, ...
-> WHERE;
For example, if you want to change the date of the joke entered above, you need to enter the following command:
Mysql> UPDATE Jokes SET jokedate= "1990-04-01" WHERE id=1;
Here we use the ID column. Through it you can easily specify the jokes you want to change. The WHERE clause can also be used here, as in the Select command. The following command is to change the date of all the body's jokes that contain the word chicken:
Mysql> UPDATE Jokes SET jokedate= "1990-04-01"
-> WHERE joketext like "%chicken%";
deleting stored data
Deleting a content in SQL is scary and simple, and the following is the format of the command:
Mysql> DELETE from WHERE;
To delete all the jokes that contain chicken, you only need to enter the following command:
Mysql> DELETE from jokes WHERE joketext like "%chicken%";
The WHERE clause here is optional, but if you don't use it, you should know what you're doing, because you're actually emptying the datasheet. The following command clears the jokes datasheet:
Mysql> DELETE from jokes;
Conclusion
In fact, in addition to the basic commands we've seen here, MySQL database systems and SQL statements have many other things to learn, but the commands we mention are some of the most commonly used commands. So far, we're just working in a datasheet. To understand the true power of the relational database engine, you also need to learn how to use several tables simultaneously to describe the complex relationships that might exist between tables.
All of this and a few other things we'll talk about in the fourth chapter of our tutorial, in chapter fourth, we'll discuss the principles of database design and take a look at some of the more complex examples. Now we need to be able to manipulate MySQL using the command-line interface skillfully.
In chapter three, we will delve into the scripting language of PHP, and learn to use it to build a dynamic Web page.
