Mysql beginners use guide
First, connect mysql: format: mysql -h host address -u username -P user password
1, Example 1: Connect to MySQL on the unit. First open the DOS window, then enter the directory mysqlbin, type the command mysql -uroot -p, then prompt you to lose your password, if you just install MySQL, the superuser root is no password, so you can enter it directly. MySQL, MySQL prompts are: mysql>
2, Example 2: Connect to MySQL on the remote host. Suppose the IP of the remote host is: 110.110.110.110, the username is root, the password is ABCD123. Type the following command: mysql -h110.110.110.110 -uroot -PABCD123 (Note: u and root can no need to bind, other is also the same)
3, exit mysql command: exit (Enter)
Second, modify the password: format: mysqladmin -u username -P old password Password new password
1, Example 1: Add a password to the root AB12. First enter the directory mysqlbin under DOS, then type the following command mysqladmin -uroot -password AB12 Note: Because the root does not have a password, the -p old password can be omitted.
2, Example 2: Change the root password to DJG345. mysqladmin -uroot -Pab12 Password DJG345
Third, add new users: (note: BY "Password"
Example 1. Add a user TEST1 password to ABC, allowing him to log in on any host and have the permissions of all databases, insert, modify, delete. First, use the root user into MySQL, then type the following command: Grant SELECT, INSERT, Update, delete on *. * To test1 @ "%" Identified by "ABC"; but Example 1 Increased users is very dangerous, you I want someone to know the password of Test1, then you can log in to your MySQL database on any computer on the Internet and to see you for your data.
Example 2, add a user TEST2 password to ABC, let him log in in localhost, and can query, insert, modify, and delete the database MYDB (localhost refers to the host host, the host where the mysql database is located) In this way, users can use the password that knows Test2, he cannot access the database directly on the Internet, and can only access the web page on the MySQL host. Grant SELECT, INSERT, UPDATE, DELETE ON MYDB. * to Test2 @ localhost Identified by "abc"; if you don't want Test2 with password, you can fight a command to remove the password. Grant SELECT, INSERT, UPDATE, DELETE ON MYDB. * To Test2 @ localhost iDentified by ""
Fourth, display command
1. Display the database list: show Databases; two databases just started: mysql and test. The mysql library is very important that there is a MySQL system information, we change your password and add users, actually use this library to operate.
2, the data table in the display library: use mysql; // Open the library, learn FoxBase will not be unfamiliar with Show Tables;
3, display the structure of the data sheet: Describe table name;
4, build the library: CREATE DATABASE library name;
5, Jian Table: USE library name; CREATE TABLE table name (field setting list);
6, delete libraries and deletion: DROP DATABASE library name; Drop Table table name;
7. Record the table in the table: DELETE FROM table name;
8, the record in the table: SELECT * FROM table name;
V. An instance of building a library and construction form and inserting data
CREATE DATABASE SCHOOL; / / Establishment School Use School; // Open Library SCHOOL CREATE TABLE TEACHER / / Establishment TEACHER (ID INT (3) Auto_INCREMENT NOT NULL PRIMARY Key, // ID Settings to the main keyword, and automatically set value , That is, when you add, you don't have to write content name char (10) Not null, address varchar (50) default 'Shenzhen', // set the default to Shenzhen Year Date; //
/ / The following is inserted insert INTO TEACHER VALUES ('', 'Glchengang', 'Shenzhen No.1 ",' 1976-10-10 '); // ID No Write Insert Into Teacher Values (', 'Jack' , 'Shenzhen Yizhong', '1975-12-23');
6. Modify the database structure
1. Add a field ALTER TABLE DBNAME Add Column
2, modify the field ALTER TABLE DBNAME CHANGE
3, delete fields ALTER TABLE DBNAME DROP Column
Mysql Beginner User Guide 2
Welcome back to this tutorial! In the previous chapter, we have learned two software installed and configured PHP and MySQL. In this chapter, we will focus on working in the mysql database using the Structured Query Language (SQL).
Database
As I am in the previous chapter, PHP is a server-side scripting language. You can add instructions in your web page through this language so your web service software (probably Apache, Personal Web Server or anything else) Software) Execute it first before sending these pages in a browser that issues a request. In that simple example, I show how to insert the current date each time you accept the requested web page.
Everything is now clear, but if you add a database, you will really lead to us. A database server (we are mysql here) is a program that stores a lot of information in a certain format. With this program you can simply use scripting languages such as PHP to access data. For example, you can use PHP to get a joke list in the database and display it to your Web site. In this example, the joke is fully stored in the database. There are two benefits. First, you no longer need to write an HTML file for each of your jokes, you only need to write a PHP file to bring any jokes from the database and display it; second, you have to add a joke to your Web site, and only just Yes Add a joke into the database. PHP code can automatically display new jokes when new jokes are included in the list.
Now let's take a look at how the data is stored in the database. A database contains one or several data tables, each of which contains a list of things. For our joke database, we will need a data table named "Jokes" at first, and this data table contains a joke list. Each data table in the database contains one or several data columns (Column) or DILDs. Go back to our example, our "Jokes" data sheet may have two columns: jokes and the date of jokes to the database. The jokes stored in the data table are called a row. To understand all the terms mentioned here, you can look at this picture:
Please note that in the two data columns of Joke Text ("Joketext") and the Add Date ("Jokedate"), I also added a data column called "ID". The role of this data column is to assign a unique number to each joke so that we can easily review and distinguish these jokes.
Now review it, there are three data columns in the above data table, two rows. Each line contains a joke ID, its body and its added date. After mastering these basic terms, we will start using MySQL.
Log in Mysql
Operating the standard interface of the MySQL database is connected to the MySQL service software (installed in the first chapter) and simultaneously enter the command. To connect the server, we need to use the MySQL client program. If you install MySQL service software yourself, whether you are installed under Windows, or installing some UNIX version, you should have installed this client program in the same location on the installation service program. In Linux, this program is called mysql, and its location is default
/ usr / local / mysql / bin
table of Contents. Under Windows, this program is called mysql.exe, and its location is the c: mysqlin directory by default.
If you are not installing MySQL servers (for example, you are working on your web host provider), then there are two ways to connect to the MySQL server, the first method is to log in to your web host with Telnet On the server, 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). Both methods can work well, your web host may support one of them, or both support (you need to ask questions).
No matter which method you choose, no matter which system you use, you will eventually execute the mysql client program to connect your MySQL server in a command line, you need to enter the following command:
MySQL -H
"-P" parameter tells the program to prompt you to enter your password, which will display immediately after you enter the above command. If you are installing mySQL, your password is your password for the root selected in the first chapter. If you are using your web host provider's MySQL service, this should be the MySQL password they give you.
If you enter everything, the MySQL client program will connect to the mysql server and return to you a mysql command line:
MySQL>
Now, MySQL servers are actually connected to several databases (this makes a web host to set the same mysql server for several users). So your next step should be a job database. First, get a list of databases on the current server. Enter the following command (don't forget the semicolon!) And then return to the car.
mysql> show data;
MySQL displays a list of databases on your server. If this is a newly installed server (that is, this is you installed in the first chapter). This list will be like this:
-------- | Database | ---------- | MySQL | | TEST | ----------
2 rows in set (0.11 sec)
MySQL servers use the first database that is called MySQL to manage users and their passwords and permissions. Now we will not care about this database, in the later chapter, we will discuss it. The second called Test is a data module. You can delete this database, you will not use it in our tutorial (we will build some database yourself). Deleting something is called "Dropping" in MySQL, to delete the Test database, the correct command should be:
mysql> DROP DATABASE TEST;
If you entered this command and hit it, MySQL will delete this database and return Query OK. Note that information such as "this is determined" is not prompted. So you must be very careful in MySQL. Just like we have seen here, you can completely delete the database - contain all the information on it - just use only one command!
Let's take a look at the connection of the mysql command line before we enter the next step. As we have noted, all commands in MySQL must end with semicolons (;). If you have forgotten this semicolon, MySQL will think that you have not ended your command and will let you continue to enter this line:
Mysql> Show -> Databases;
When Mysql is waiting for the remainder in the command, the prompt will change from mysql> to ->. For a long command, this is useful, you can enter your command a few lines. If you find that your command is wrong in the middle, you can completely cancel the current command (the translator Note: means a command that has not been executed) and is from the head. To do this, you only need to enter C and press Enter: MySQL> DROP DATABASECMYSQL>
MySQL will completely ignore the command you just entered and return to the prompt waiting for your next command.
Finally, when you want to quit MySQL client programs, you only need to enter quit or exit (both commands are exactly the same). This is the only command that can be executed without the end of the semicolon.
Mysql> quitbye
What is SQL?
In this tutorial, we used to tell Mysql to do what is actually a part of the structured query language (SQL). The command in SQL is also called inquiry (in this tutorial, we will alternately use these two names).
SQL is a standard language that implements interaction with the vast majority of databases, so even if you no longer use MySQL, you will use Microsoft SQL Server, you will find that most commands are the same. You must understand the difference between SQL and MySQL. MySQL is the database service software you are using. SQL is a language you use to implement and interact with the database.
Create a database
If your web host provider has assigned a database that you work. You should wait patiently, wait for us to continue to discuss the following problems. If you are working on your MySQL server installed. Execute the following command, you can easily create a database:
Mysql> Create Database Jokes;
My database's name is Jokes, which is consistent with the example of our work. In fact, you can give your database from any name you like. However, if you work on the MySQL server of the web host provider, they may have established a database for you, you can't choose the name of the database.
Now we already have a database, we need to tell Mysql we want to use this database. Below this command should be less difficult:
Mysql> Use Jokes;
Now you can start using your database. Before you add a data table, this database will be empty, our first step should be to create a data sheet to save our jokes.
Create a data sheet
The SQL command we have encountered before, but because the data table is more flexible, it is much more complicated to establish their commands. The basic format of the establishment of a data table is this:
Mysql> Create Table
