Use PHP and MySQL to build a database-driven website 3. In this chapter, we will focus on learning how to use Structured Query Language (SQL) to work in MySQL databases. (2002-08-2914: 11: 10) ------------------------------------------- in this chapter, we will focus on how to use Structured Query Language (SQL) to work in MySQL databases. (14:11:10) -------------------------------------------------------------------------------------- By Wing, source: Linuxaid Chapter 2: MySQL entry welcome back to this tutorial! In the previous chapter, we learned how to install and configure PHP and MySQL. In this chapter, we will focus on learning how to use Structured Query Language (SQL) to work in MySQL databases. As described in the previous chapter, PHP is a server-side scripting language. you can use this language to add commands on your Web page, in this way, your Web service software (probably Apache, Personal Web Server, or any other software) will first execute it before sending these pages to the requesting browser. In that simple example, I show how to insert the current date in the Web page that receives the request each time. It is clear, but adding a database in it will really interest us. A database server (MySQL here) is a program that stores a large amount of information in a certain format. through this program, you can easily access data using a script language like PHP. For example, you can use PHP to get a joke list in the database and display it to your Web site. Jokes are completely stored in the database. There are two benefits to doing so. First, you no longer need to write an HTML file for each joke. you only need to write a php file to extract any joke from the database and display it. second, to add a joke to your Web site, you just need to add the joke to the database. PHP code automatically displays new jokes when the new joke package is included in the list. Let's use this example to see how the data is stored in the database. A database contains one or more tables. each table contains a list of items. For our joke database, we may need a data table named "jokes" at the beginning, which contains a joke list. Each data table in the database contains one or more columns or fields ). Back in our example, our "jokes" data table may have two columns: the joke body and the date the joke was added to the database. Every joke stored in a data table is called a row. To understand all the terms mentioned here, you can see the figure below: in addition to the two data columns of joke text ("JokeText") and date ("JokeDate, I also added a data column named "ID. This data column is used to assign a unique number to each joke so that we can easily view and distinguish these jokes. For details, the data table above contains three data columns and two rows. Each line contains a joke ID, its body, and its addition date. After understanding these basic terms, we will start using MySQL. The standard interface for logging on to MySQL SQL database is to connect to the MySQL service software (installed in chapter 1) and enter commands at the same time. To connect to the server, we need to use the MySQL client program. If you have installed the MySQL service software on your own, whether installed in Windows or in some Unix versions, you should have installed the client in the same location where the service program is installed. In Linux, this program is called mysql, and its location is the/usr/local/mysql/bin directory by default. In Windows, this program is called mysql.exe, and its location is the C: mysqlin directory by default. Is a self-installed MySQL server (for example, you work on the MySQL server of your Web host provider), there are two ways to connect to the MySQL server, the first method is to use telnet to log on to the server of your Web host, and then run mysql there. The second method is from http://www.mysql.com/ (For Windows and for Linux can be obtained for free) download and install the MySQL client program. Both methods work well. Your Web host may support either or both of them (you need to know ). Which method is used? no matter which system you are using, you should execute the MySQL client program in a command line to connect to your MySQL server. you need to enter the following command: replace mysql-h-u-p with the host name or IP address of the computer on which your MySQL server is running. If you run the client program on the same computer that runs the service, you can directly use-h localhost instead of-h. It should be your MySQL User name. If you are a self-installed MySQL server, this should be root. If you are using your Web host provider's MySQL service, this should be the MySQL User they specified for you. The parameter tells the program that you are prompted to enter your password, which will be displayed immediately after you enter the command above. If you install MySQL on your own, your password is the root password you selected in chapter 1. If you are using your Web host provider's MySQL service, this should be the MySQL password they gave you. After all the commands are entered, the MySQL client will connect to the MySQL server and return a command line for MySQL: mysql> ySQL server is actually connected to several databases (this allows a Web host to set the same MySQL server for several users ). So your next step is to select a working database. First, obtain the database list on the current server. Enter the following command (do not forget the semicolon !), Then press Enter. Mysql> show databases; L will display the database list on your server. If this is a newly installed server (that is, you have installed it yourself in chapter 1 ). This list will look like this: + ---------- + | Database | + ---------- + | mysql | test | + ---------- + 2 rows in set (0.11 sec) L The Server uses the first database called mysql to manage users, passwords, and permissions. We will not care about this database for the time being. we will discuss it later. The second name is test, which is a data module. You can delete this database, which is not used in our tutorial (we will create some databases ourselves ). To delete something that is called "dropping" in MySQL, the correct command for deleting the test DATABASE should be: mysql> drop database test; enter this command and press enter, mySQL deletes the database and returns Query OK. Note that you will not be prompted for such information as "whether this is OK. Therefore, you must be very careful when entering commands in MySQL. As we can see here, you can completely delete the database-containing all its information-just use one command! Before entering the next step, let's take a look at the MySQL command line connection. As we have noticed, all commands in MySQL must end with a semicolon. If you forget this semicolon, MySQL will think that you have not entered your command, and will ask you to continue entering in this line: mysql> SHOW-> DATABASES; L when you enter the remaining part of the command, the prompt will change from mysql> to->. This is very useful for a long command. you can divide your command into several lines. If you find that your command has an error, you can completely cancel the current command (note: it refers to the command that has not been executed) and start from scratch. To do this, enter c and press enter: mysql> DROP DATABASEcmysql> L to ignore the command you just entered and return to the prompt to wait for your next command. To exit the MySQL client, you only need to enter quit or exit (the two commands are identical ). This is the only command that can be executed without the end of a semicolon. Mysql> quitBye what is SQL? The commands we use to tell MySQL what to do are actually part of a specification called Structured Query Language (SQL. SQL commands are also called queries (in this tutorial, we will use these two names alternately ). Standard language for interaction with the vast majority of databases, so even if you stop using MySQL and use Microsoft SQL Server in the future, you will find that the vast majority of commands are the same. You must understand the difference between SQL and MySQL. MySQL is the database service software you are using. SQL is the language you use to interact with databases. Creating a database Web host provider has assigned you a database for work. Wait patiently and we will continue to discuss the following issues with you. If you are working on your own MySQL server. Execute the following command to easily CREATE a DATABASE: mysql> create database jokes; the DATABASE name is jokes, which is consistent with our example. In fact, you can give your database any name you like. However, if you work on the MySQL server of the Web host provider, they may have already created a database for you, and you cannot select the database name. If you already have a database, we need to tell MySQL that we want to use it. The following command should not be difficult to remember: mysql> USE jokes; to start using your database. Before you add a data table, the database will be empty. our first step is to create a data table to save our jokes. The SQL commands used to create a data table are very simple, but because the data table is flexible, it is much more complicated to create them accordingly. The basic format for creating a data TABLE is as follows: mysql> create table (-> ,-> ,->...->); Return to the "Jokes" table in our example. This table has three data columns: ID (a number), JokeText (joke body), and JokeDate (date added ). The command for creating this TABLE should be as follows: mysql> create table Jokes (-> id int not null AUTO_INCREMENT primary key,-> JokeText TEXT, -> JokeDate date not null->); it looks complicated, right? Let's break it down: the first line is relatively simple; it indicates that we want to create a new data table named Jokes. The second row indicates that we need a data column named ID. the column type should be an integer (INT ). This row also defines other information about this data column. First, this row cannot be blank (not null ). Second, if you do not specify a value for a column, MySQL will select a value greater than the current maximum value (AUTO_INCREMENT ). Finally, this data column is the unique identifier of the data table. Therefore, all values in this data column should be unique (primary key ). The third row is very simple. This indicates that we need a data column named JokeText. the column type should be TEXT ). The fourth row defines our last column. the column name is JokeDate. the column type is DATE and cannot be NULL ). Note that when we enter SQL commands, the case sensitivity is completely free, but the MySQL service runs in a Unix-based system, because we must be consistent with the directory and file in the MySQL data directory, we must be case sensitive when encountering database and table names. Otherwise, MySQL is case-insensitive and has only one exception. The table names, column names, and other names that appear multiple times in the same command must be exactly the same in spelling.
In this chapter, we will focus on how to use Structured Query Language (SQL) to work in MySQL databases. (14:11:10 )-------------------------------------------...
