MySQL learning experience

Because I need to use MySQL in my work, I learned from the Internet and started to practice, and finally changed from a "database cainiao" to a "quasi-Prawns" Who Can skillfully operate MySQL ,:). My learning experience is as follows.

MySQL is a fully networked cross-platform relational database system, a real multi-user, multi-thread SQL database server, and a distributed database management system with a client/server architecture. It has the advantages of strong functionality, ease of use, convenient management, fast operation, strong security and reliability. You can use many languages to write programs that access the MySQL database, it is ideal for medium and small application systems. In addition to supporting standard ansi SQL statements, more importantly, it also supports multiple platforms, and on Unix systems, the software supports multi-threaded running mode, so as to achieve good performance. For users who do not use Unix, it can run as a system service on Windows NT, or as a common process on Windows 95/98. On Unix/Linux systems, MySQL supports multi-threaded running methods to achieve good performance, and it is open-source software.

MySQL is implemented in a Client/Server structure. It consists of a server daemon mysqld and many different client programs and libraries. MySQL has high execution performance and fast operation speed, it is very easy to use and is a very good database. The official pronunciation of MySQL is "My ESS que ell" (not my-sequel ).

1. Obtain MySQL

You must first download MySQL. The official website of MySQL is: http://www.mysql.com, the image in China is: http://www.freecode.net.cn/mirror/mysql/ or http://www2.linuxforum.net/#/mysql/, can download the stability of MySQL 3.22.32 (as of press date ). The version name is mysql-shareware-3.22.32-win, not only free but not called "30 Angel term ".

Ii. Install MySQL

(1) installation in Win98/winnt

To a directory, run the setup program and prompt the entire installation process. It is installed in C:/MySQL by default. If you want to install it in other directories, there are still some changes. As a beginner, you can skip this step and install it in the default directory. After installation, go to the C:/MySQL/bindirectory and run the mysqld-mongoware.exe file directly under Win98. Normally, there is no prompt.

1. Decompress the downloaded file to a temporary directory.
2. Click setup.exe to install it in C:/MySQL (default ).
3. Copy mysql_example.cnf to C:/My. CNF and change # basedir = D:/MySQL/in the file to basedir = C:/MySQL.
4. Go to the msdos directory c:/MySQL/bin.
5. Enter mysqld-policware-install to start the MySQL database service.
6. Mysqladmin-u root-P password: new password, which is used to change the password of the root user (the original default password is blank .)
7. Enter mysql-u root-P.
8. Then, enter the new password as prompted. Under normal circumstances, several lines of information will be prompted and the prompt symbol will appear:
Welcome to the MySQL monitor. commands end with; or/g.
Your MySQL connection ID is 6268 to server version: 3.22.32
Type 'help' for help.
Mysql>

By now, you have successfully installed MySQL.

(2) installation in Windows2000

1. Select the default directory c:/MySQL during installation.
2. Copy the C:/MySQL/my-example.cnf to C:/My. CNF, and copy C:/MySQL/lib/cygwinb19.dll to winnt/system32.
3. To start MySQL, follow these steps:
C:/MySQL/bin/mysqld-configurware -- install
Net start MySQL
This is easy to start.
4. Change the root password:
C:/MySQL/bin/MySQL
Mysql> Update user SET Password = PASSWORD ('your password') where user = 'root ';
Mysql> quit
C:/MySQL/bin/mysqladmin reload
Run the C:/MySQL/bin/mysqlshow command to check the vulnerability. It should be shown here:
+ ----------- +
| Databases |
+ ----------- +
| MySQL |
| Test |
+ ----------- +
This step proves no problem.
Again:
C:/MySQL/bin/mysqlshow -- user = root -- Password = your password MySQL
It should be shown here:
Database: MySQL
+ -------------- +
| Tables |
+ -------------- +
| Columns_priv |
| DB |
| Host |
| Tables_priv |
| User |
+ -------------- +
Everything is done!

5. C:/MySQL/bin/mysqladmin version status proc
You can see the following version information:
Mysqladmin ver 8.0 distrib 3.22.32, for Win95/Win98 on i586
Tcx datakonsult AB, by Monty

Server version 3.22.32-firmware ware-Debug
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 1 hour 29 min 30 sec

Threads: 1 questions: 72 slow queries: 0 opens: 16 flush tables: 1 open tables: 0 memory in use: 16423 K max memory used: 16490 K
Uptime: 5370 threads: 1 questions: 72 slow queries: 0 opens: 16 flush tables: 1 open tables: 0 memory in use: 16423 K max memory used: 16490 K
+ ---- + ------ + ----------- + ---- + --------- + ------ + ------- + ------------------ +
| ID | user | host | dB | command | time | state | info |
+ ---- + ------ + ----------- + ---- + --------- + ------ + ------- + ------------------ +
| 35 | ODBC | localhost | query | 0 | show processlist |
+ ---- + ------ + ----------- + ---- + --------- + ------ + ------- + ------------------ +
Then, use the show databases command to list the installed databases:
Mysql> show databases;
You can see:
+ ---------- +
| Database |
+ ---------- +
| MySQL |
| Test |
+ ---------- +
2 rows in SET (0.00 Sec)
If everything is normal, it means MySQL can work completely! To exit the program, enter exit.
Mysql> exit;
Bye

Iii. MySQL common sense

(1) Field Type

1. Int [(m)]
Normal size Integer type
2. Double [(m, D)] [zerofill]
Normal size (Double Precision) floating point numeric type
3. Date
Date type. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the date value in 'yyyy-MM-DD 'format, but allows you to assign the value to the date column using strings or numbers
4. Char (m)
Fixed-length string type. When stored, it always fills the Right to the specified length with spaces
5. Blob text
Blob or text type. The maximum length is 65535 (2 ^ 16-1) characters.
6. varchar
Variable-length string type, the most common type.

(2) Basic operations

1: display the database
Mysql> show databases;
2: currently selected database,
Mysql> select database ();
+ ------------ +
| Database () |
+ ------------ +
| Test |
+ ------------ +
3. Table information contained in the current database:
Mysql> show tables;
+ --------------------- +
| Tables in test |
+ --------------------- +
| Mytable1 |
| Mytable2 |
+ --------------------- +
4. Obtain the table structure
Mysql> DESC mytable1;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| FIELD | type | null | key | default | extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| S1 | varchar (20) | Yes | null |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
5. Create a table
A table is one of the most basic elements of a database. Tables and tables can be independent or associated with each other. The basic syntax for creating a table is as follows:
Create Table table_name
(Column_name datatype {identity | null | not null },
...)
The table_name and column_name parameters must meet the identifier requirements in the user database. The parameter ype is a standard SQL type or a type provided by the user database. You must use the non-null clause to input data for each field.
Create Table also has some other options, such as creating a temporary table and reading some fields from other tables using the select clause to form a new table. In addition, when creating a table, you can use the primary key, key, index, and other identifiers to set certain fields as primary keys or indexes. Note: List the complete Field List in a pair of parentheses. Field names are separated by commas. Enter a space after the comma (,) between field names. The last field name is not followed by a comma. All SQL statements end with a semicolon.
Example:
　　Mysql> Create Table guest (name varchar (10), sex varchar (2), age int (3), career varchar (10 ));
6. Create an index
Indexes are used to query databases. Generally, a database has a variety of index solutions, each of which is precise to a specific query class. Indexes can accelerate the database query process. The basic syntax for creating an index is as follows:
Create index index_name
On table_name (col_name [(length)],...)
Example:
　　Mysql> Create index number on guest (number (10 ));
7. Execute the query
Query is the most commonly used SQL command. The query database must rely on factors such as structure, index, and field type. Most databases contain an optimizer that converts user query statements into optional forms to improve query efficiency.
It is worth noting that MySQL does not support standard SQL 92 nested where clauses, that is, it only supports one where clause. The basic syntax is as follows:
Select [straight_join] [SQL _small_result] [SQL _big_result] [high_priority]
[Distinct | distinctrow | all]
Select_expression ,...
[Into {OUTFILE | dumpfile} 'file _ name' export_options]
[From table_references
[Where where_definition]
[Group by col_name,...]
[Having where_definition]
[Order by {unsigned_integer | col_name | formula} [ASC | DESC],...]
[Limit [offset,] rows]
[Procedure procedure_name]
Where the WHERE clause is the place where the definition is selected. where_definition can have different formats, but they all follow the following format:
Field name Operation expression
Field name operation field name
In the first form, the value of the field is compared with the expression. In the second form, the values of the two fields are compared. Depending on the data type, the following operations may be selected in search_condition:
= Check for Equality
! = Check whether the value is different

> (Or> =) check whether the left value is greater than (or greater than or equal to) the right value
<(Or <=) check whether the left value is less than (or less than or equal to) the right value
[Not] Between check whether the left value is within a certain range
[Not] in checks whether a member of a specific set is on the left.
[Not] Like: Check whether the left side is a substring on the right
Is [not] Null check whether the left side is null
Here, wildcard _ can be used to represent any character, and % represents any string. Use keywords, And You can generate complex words that use multiple standard sets of boolean expressions during runtime checks.
Example:
　　　Mysql> select t1.name, t2.salary from employee as T1, info as T2 where t1.name = t2.name;
Mysql> select College, region, seed from tournament
Order by region, seed;
Mysql> select col_name from tbl_name where col_name> 0;
8. Change the table structure
When using a database, you sometimes need to change its table structure, including changing the field name or even changing the relationship between different database fields. The alter command can be implemented. Its basic syntax is as follows:
Alter table table_name alter_spec [, alter_spec...]
Example:
　　Mysql> alter table dbname add column userid int (11) not null primary key auto_increment;
In this way, a field userid is added to the dbname table and the type is int (11 ).
9. Modify Table Data
When using a database, you often need to modify the data in its table, such as adding new data to the table, deleting the original data in the table, or changing the original data in the table. Their basic syntax is as follows:
Add data:
Insert [into] table_name [(column (s)]
Values (expression (s ))
Example:
　　　Mysql> insert into mydatabase values ('php', 'mysql', 'asp ', 'sqlserver', 'jsp ', 'oracle ');
10. Update table data

(1) modify a field at a time and pay attention to the syntax again. The text must be enclosed in quotation marks but not digits.
Mysql> Update table01 set field03 = 'new info' where field01 = 1;
Query OK, 1 row affected (0.00 Sec)
(2) Change multiple fields at a time, and remember to separate each updated field with a comma.
Mysql> Update table01 set field04 = 19991022, field05 = 062218 where field01 = 1;
Query OK, 1 row affected (0.00 Sec)
(3) Update multiple data at a time
Mysql> Update table01 set field05 = 152901 where field04> 19990101;
Query OK, 3 rows affected (0.00 Sec)
11. delete data
Mysql> Delete from table01 where field01 = 3;
Query OK, 1 row affected (0.00 Sec)
12. Import database tables
(1) create a. SQL File
(2) first generate a database such as auction. C:/MySQL/bin> mysqladmin-u root-P creat auction. A prompt is displayed, indicating that the password is entered and created successfully.
(3) import the auction. SQL File
C:/MySQL/bin> mysql-u root-P auction <auction. SQL.
With the preceding operations, you can create a database auction and a table auction.
13. MySQL database authorization
Mysql> grant select, insert, delete, create, drop
On *. * (or test. */user .*/..)
To username @ localhost
Identified by 'Password ';
For example, to create a new user account for database access, perform the following operations:
Mysql> grant usage
-> On test .*
-> To testuser @ localhost;
Query OK, 0 rows affected (0.15 Sec)
Then a new user named testuser is created. This user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that the user testuser can perform:
Mysql> grant select, insert, delete, update
-> On test .*
-> To testuser @ localhost;
Query OK, 0 rows affected (0.00 Sec)
This operation enables testuser to perform select, insert, delete, and update queries on the tables in each test database. Now we end the operation and exit the mysql client program:
Mysql> exit
Bye
14. Authorize the MySQL user password
The default username for MySQL database is "root" (SA for ms SQL Server is similar), and the password is empty by default. Enter
C:/MySQL/bin> "mysqladmin-u root-P password newpassword
After you press enter, you will be prompted to enter the original password. Because the original password is empty, press enter directly and change the password of the root user to "newpassword.

4. Install phpMyAdmin

The MySQL graphic interface manager phpMyAdmin is a set of web management interfaces written in php3 for MySQL database systems. It allows you to easily add and delete fields and data in the MySQL database on a graphical interface, and allows you to add, delete, and manage databases; phpMyAdmin can manage the entire MySQL Server (requires a Super User) or a single database. In addition, you can also use this graphical interface to learn the correct syntax of SQL until you are familiar with it. Wait until you have read the related sections in the MySQL manual. You can go to the http://www.phpwizard.net/phpMyAdmin/ to download the latest version.

First, extract the phpMyAdmin package to a directory.
1. Modify the config. Inc. php3 document.
Set the original settings as follows:
$ Login servers [1] ['host'] = ''; // MySQL hostname
$ Export servers [1] ['Port'] = ''; // MySQL port-leave blank for default port
$ Login servers [1] ['adv _ auth'] = false; // use advanced authentication?
$ Login servers [1] ['stduser'] = ''; // MySQL standard user (only needed with advanced auth)
$ Login servers [1] ['stdpass'] = ''; // MySQL standard password (only needed with advanced auth)
$ Login servers [1] ['user'] = ''; // MySQL user (only needed withbasic auth)
$ Login servers [1] ['Password'] = ''; // MySQL password (only needed with basic auth)
$ Incluservers [1] ['only _ db'] = ''; // If set to a DB-name, only this dB is accessible
$ Define servers [1] ['verbose '] = ''; // Verbose Name For this host-leave blank to show the hostname
:
:
Require ("English. Inc. php3 ");
Modify:
$ Login servers [1] ['host'] = 'mysql server hostname'; // enter the host name of your MySQL Server
$ Export servers [1] ['Port'] = ''; // enter the port used to connect to MySQL. If this parameter is not specified, the default port is used to connect to MySQL.
$ Login servers [1] ['adv _ auth'] = true; // if it is set to true, authentication is required before entering phpMyAdmin.
$ Login servers [1] ['stduser'] = 'root'; // account of the MySQL user
$ Login servers [1] ['stdpass'] = 'Password'; // password of the MySQL user
$ Login servers [1] ['user'] = 'root'; // MySQL account management
$ Login servers [1] ['Password'] = 'Password'; // MySQL Admin Password
$ Administrative servers [1] ['only _ db'] = ''; // specifies the name of the managed resource library. If this parameter is not specified, the entire server can be managed.
$ Define servers [1] ['verbose '] = ''; // specify the MySQL name. If this parameter is not specified, use the hostname of the system.
:
:
Require ("chinese_gb.inc.php3"); // change the language to Chinese.
Note:
(1) because of this management interface, you can use one interface to manage multiple MySQL servers, so you can find it in config. Inc. php3.
$ Incluservers [1]...
$ Incluservers [1]...
$ Incluservers [1]...
:
$ Incluservers [2]...
$ Incluservers [2]...
$ Incluservers [2]...
[1] indicates the first MySQL server, and [2] indicates the second MySQL server. If you want to manage more than three MySQL servers, follow the same instructions, add [4]... go on!
(2) If your MySQL server and HTTP server are the same, you can enter $ login servers [1] ['host'] = in localhost directly.
Finally, open the browser, enter http: // your url/phpMyAdmin, and you will see a small window for password verification. Enter your MySQL management account and password, you can see the phpMyAdmin management screen. With phpMyAdmin, you can manage all your databases graphically and conveniently.

V. Summary
Through the above learning and operations, you can finally freely operate the MySQL database, or use PHP or ASP to call MySQL. Finally, I would like to express my special thanks to those who have developed MySQL and contributed for free, as well as those who provide MySQL instructions online.