Abstract in this chapter, we will focus on learning how to work in a MySQL database using Structured Query Language (SQL). (2002-08-29 14:11:10)--------------------------------------------------------------------------------by Wing, Source: Linuxaid Chapter II: MySQL Introduction Welcome back to this tutorial! In the previous chapter, we learned to install and configure both PHP and MySQL software. In this chapter, we will focus on learning if you are working in a MySQL database using Structured Query Language (SQL). Database Getting started the previous chapter briefly explains that PHP is a server-side scripting language in which you can add directives to your Web pages so that your Web services software (possibly 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 in a Web page that accepts requests every time. It's clear, but if you add a database to it, it's really interesting. A database server (we're MySQL here) is a program that stores a lot of information in a format that you can use to easily access data using a scripting language like PHP. For example, you can use PHP to get a list of jokes in your database and display them to your Web site. The joke is completely stored in the database. There are two benefits to doing so. First of all, you no longer need to write an HTML file for each of your jokes, you just have to write a PHP file to draw any jokes from the database and show it, and secondly, to add jokes to your Web site, just add jokes to the database. The PHP code can automatically display new jokes when new jokes are included in the list. Let's look at how the data is stored in the database in this example. A database contains one or several data tables (table), each containing a list of things. For our joke database, we might need a data table called "jokes" in the first place, which 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" data table might have two columns: the body of the joke and the date the joke was added to the database. Every joke that is stored in a data table is called a line. To understand all the terms mentioned here, you can look at the following picture: In the joke body ("Joketext") and addDate ("Jokedate") in addition to these two data columns, I also added a data column called "ID". The purpose of this data column is to assign a unique number to each joke so that we can easily refer to and distinguish between these jokes. The data table above has three data columns and two rows. Each line contains the ID of a joke, its body, and the date it was added. Having mastered these basic terms, we will start using MySQL. The standard interface for logging into MySQL SQL database is to connect to the MySQL service software (installed in the first chapter) and enter the command at the same time. To connect to the server, we need to use the MySQL client program. If you installed the MySQL service software yourself, whether you installed it under Windows or in some UNIX versions, you should have installed the client program in the same location where the service was installed. 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 installed MySQL server (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 method is to use Telnet to log on to your web host server, and then run MySQL there. The second method is to download and install the MySQL client program from http://www.mysql.com/(for Windows and for Linux, which are available for free). Both of these methods work well, and your web host may support one or both of them (you need to be clear). Choose which method, regardless of which system you are using, you should end up in a command line to execute the MySQL client program to connect your MySQL server, you need to enter the following command: Mysql-h-u-p replace 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 is running the service, you can use-h localhost without using-H. Should be your MySQL user name. If you are installing your own MySQL server, this should be root. If you are using the MySQL service of your web hosting provider, this should be the MySQL user they specified for you. The parameter tells the program to prompt you for your password, which will be displayed immediately after you enter the above command. If you are installing MySQL yourself, your password is the root password you chose in the first chapter. If you are using your web hosting provider'sMySQL service, this should be the MySQL password they gave you. The MySQL client program connects to the MySQL server and returns you a MySQL command line: The mysql> 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 choose a working database. First, get a list of the databases on the current server. Enter the following command (do not forget the semicolon!) ), and then hit enter. Mysql> SHOW DATABASES; L will display the list of databases on your server. If this is a newly installed server (that is, you installed it yourself in the first chapter). This list will be like this: +----------+ | Database | +----------+ | MySQL | | Test | +----------+ 2 rows in Set (0.11 sec) L Server uses the first database called MySQL to manage users and their passwords and permissions. Now we don't care about this database for the time being, we'll discuss it in a later chapter. The second called test is a data module. You can delete this database and not use it in our tutorial (we will build some databases ourselves). Delete What in MySQL is called "dropping", to delete the test database, its correct command should be: mysql> DROP database test; After entering this command and hitting enter, MySQL will delete the database and return to query OK. Note that you will not be prompted for information such as "Whether 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--including all of its information--with just one command! Before we go to 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 you have not finished typing your command, and will let you continue typing in this next line: Mysql> SHOW, DATABASES; When you wait for the remainder of the command to be entered, the prompt changes from mysql> to. This is useful for a long command, and you can enter your command in a few lines. The way to find your command is wrong, you can completely cancel the current command (translator Note: is not yet executed command) and from the beginning. To complete this job, you only need to enter C and press backCar: 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 exactly the same). This is the only command that can be executed without ending with a semicolon. mysql> Quitbye what is SQL? The command we used to tell MySQL what to do in the process is actually part of a specification called Structured Query Language (SQL). The commands in SQL are also referred to as queries (in this tutorial, we will alternately use these two types of salutation). The standard language for interacting with the vast majority of databases, so even if you don't use MySQL in the future, instead of using Microsoft SQL Server, you'll find that most of the 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. Establish a database web hosting 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 install on your own. Execute the following command, and it's easy for you to build a database: mysql> create databases jokes; The name of the library is jokes, which is in line with the example of our work. 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 created a database for you and you will not 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 difficult to remember: mysql> use jokes; To start using your database. This database will be empty before you add a datasheet to it, and our first step should be to create a data table to save our jokes. The SQL commands that are encountered when building a data table are very simple, but because the data tables are flexible, it is much more complicated to build their commands accordingly. The basic format for creating a data table is this: mysql> CREATE TABLE ( , , ...); 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 added). The command to build this table should look like 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 data table named jokes. The second line shows that we need a data column called ID, and the type of this column should be an integer (INT). This line also defines some additional information for this data column. First, this line is not allowed to be empty (not null). Second, if you do not specify a value for a column, MySQL chooses to use a value larger than the current maximum value (auto_increment). Finally, this data column is also the unique identifier for this data table, so all values in this data column should be non-duplicated (PRIMARY KEY). The third line is very simple, which means we need a data column called Joketext, and the type of the column should be a literal (text). Row 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 null (NOT NULL). Note that when we enter the SQL command, the case is completely free, but the MySQL service runs under a unix-based system, because we have to match the directory and file under the MySQL data directory, and when we encounter the database name and table name we have to be case sensitive. Otherwise, MySQL is completely case insensitive, with one exception, table names, column names, and other names that appear more than once in the same command must be spelled exactly the same
http://www.bkjia.com/PHPjc/532244.html www.bkjia.com true http://www.bkjia.com/PHPjc/532244.html techarticle abstract in this chapter, we will focus on learning how to work in a MySQL database using Structured Query Language (SQL). (2002-08-29 14:11:10)-------------------------------------------...
