1. Introduction to MySQL
MySQL is a relational database management system developed by Swedish MySQL lab company, which is a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of web application, MySQL is one of the best RDBMS(Relational Database Management System) application software.
MySQL is a relational database management system. Relational database saves data in different tables instead of putting all data in a large warehouse, which increases speed and flexibility.
The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual authorization policy, which is divided into community version and commercial version. Due to its small size, fast speed and low overall cost of ownership, especially the open source code, MySQL is generally selected as the website database for the development of small and medium-sized websites.
MySQL is a safe, cross platform and efficient database system, which is closely combined with PHP, Java and other mainstream programming languages. The database system was developed, released and supported by MySQL AB company in Sweden. It was established in 1995 by David Axmark and Michael Monty Widenius, the initial developers of MySQL.
The symbol of MySQL is a dolphin named Sakila, which represents the speed, ability, accuracy and excellent essence of MySQL database.
Mysql database can be called one of the fastest running SQL language databases at present. In addition to many functions that other databases do not have, MySQL database is a completely free product. Users can download MySQL database directly through the network without paying any fees.
2. MySQL features
1. Powerful
MySQL provides a variety of database storage engines. Each engine has its own advantages and is suitable for different applications. Users can choose the most appropriate engine to get the highest performance, and can handle high-intensity search Web sites with more than hundreds of millions of visits every day. MySQL 5 supports transactions, views, stored procedures, triggers, etc.
2. Support cross platform
MySQL supports at least 20 development platforms, including Linux, Windows, FreeBSD, IBM AIX, AIX, FreeBSD, etc. This makes the program written on any platform can be transplanted without any modification.
3. Fast running speed
High speed is a significant feature of MySQL. In mysql, the extremely fast B-tree disk table (MyISAM) and index compression are used; By using optimized single scan multi connection, the connection can be realized very quickly; SQL functions are implemented using highly optimized class libraries and run very fast.
4. Support object-oriented
PHP supports mixed programming. Programming methods can be divided into three ways: pure object-oriented, pure process-oriented, and the mixture of face sentence object and process-oriented.
5. High safety
Flexible and secure authority and password system, allowing basic host authentication. When connecting to the server, all password transmissions are encrypted to ensure the security of the password.
6. Low cost
MySQL database is a completely free product. Users can download it directly through the network.
7. Support various development languages
MySQL supports various popular programming languages and provides them with many API functions, including PHP, ASP.NET, Java, Eiffel, Python, Ruby, Tcl, C, C + +, Perl, etc.
8. Large database storage capacity
The maximum effective table size of MySQL database is usually determined by the file size limit of the operating system, not by the internal limit of MySQL. The InnoDB storage engine saves InnoDB tables in a table space, which can be created by several files. The maximum capacity of the table space is 64TB, which can easily handle large databases with tens of millions of records.
9. Support powerful built-in functions
PHP provides a large number of built-in functions, covering almost all functions in Web application development. It has built-in functions such as database connection and file upload. MySQL supports a large number of extension libraries, such as MySQL Li, which can facilitate the rapid development of Web applications.
Cooperation between PHP and MySQL
Multiple databases can be created in the same MySQL database server. If each database is regarded as a "warehouse", the content data in the website is stored in this warehouse. The access and maintenance of data in the database are managed by the database management system software.
The same database management system can establish databases for different websites, but in order to make the data in the website easy to maintain, backup and transplant, it is best to create a database for a website (sub database and sub table are used in case of large amount of data). The relationship between database, database management system and PHP application is shown in the figure below.
3. The important position of database in Web Development
In the final analysis, dynamic websites operate on data. When we browse the web page, we will find that the content of the web page will often change, while the main structure framework of the page has not changed. News is a typical example. This is because we store the news in the database. When users browse, the program will read the corresponding news from the database according to the news number requested by the user, and then respond to the user in a specific format.
The development of Web system is basically inseparable from the database, because everything must be stored in the database. The so-called dynamic website is a system based on database development. The most important thing is data management. In other words, when we develop, we are writing programs around the database. Therefore, as a Web programmer, only by mastering a database can we carry out software development.
Development process: store the content of the website in MySQL database; Then use PHP to obtain these contents through SQL query and output them to the browser in HTML format. Or save the data output by the user in the form in the MySQL database by executing SQL query in the PHP program. You can also accept other related operations of users on the web page in the PHP script, and then manage the website content stored in the database through SQL query.
PHP can use almost all existing databases. Compared with other large databases such as Oracle, DB2 and SQL Server, MySQL has its own shortcomings, such as small scale and limited functions (the functions and efficiency of MySQL Cluster are relatively poor), but this does not reduce its popularity. For ordinary individual users or small and medium-sized enterprises, MySQL provides more than enough functions, and because MySQL is an open source software, it can greatly reduce the total cost of ownership.
At present, the popular website architectures on the Internet are LAMP (Linux + Apache + MySQL + PHP/Perl/Python) and LNMP (Linux + Nginx + MySQL + PHP/Perl/Python), that is, Linux is used as the operating system, Apache and Nginx are used as the Web server, MySQL is used as the database, and PHP is used as the server-side script interpreter. Because these four software are free or open source software, a stable and free website system can be established without spending a penny (excluding labor cost).
4. Install MySQL
4.1 first, you need to download the MySQL package
This is the download link of the official website
Here I download version 5.7.34. You can download the corresponding version according to your needs.
// It is recommended that you put the downloaded packages in a unified place (convenient for management) [root@master ~]# yum -y install wget / / if there is no wget command, you need to install it first [root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_ 64.tar.gz - P / usr / local / / because the MySQL package has 634MB, you need to wait patiently for download- P download to the specified directory ## 4.2 unzip the downloaded package [root@master local]# pwd /usr/local [root@master local]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
4.3 resolving mysql dependencies
[root@master local]# yum -y install gcc gcc-c++ make zlib zlib-devel pcre pcre-devel openssl openssl-devel ncurses-compat-libs perl ncurses-devel cmake
4.4 creating MySQL users
[root@master local]# useradd -r -M -s /sbin/nologin mysql
To facilitate the following operations, create a soft link for MySQL
[root@master local]# ln -s mysql-5.7.34-linux-glibc2.12-x86_64 mysql
4.5 modify the owner and group of mysql directory
[root@master local]# chown -R mysql.mysql mysql
4.6 setting environment variables
After setting the environment variable, you can mysql Command into database [root@master local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@master local]# source /etc/profile.d/mysql.sh
4.7 configuring MySQL library files
[root@master local]# vim /etc/ld.so.conf.d/mysql.conf [root@master local]# cat /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/bin [root@master local]# ldconfig
4.8 create a MySQL database directory and modify the owner and group
[root@master local]# mkdir -p /opt/data [root@master local]# chown -R mysql.mysql /opt/data
4.9 generating mysql configuration files
[root@master local]# cat /etc/my.conf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve
4.9 configuring service startup script
[root@master ~]# vim /usr/local/mysql/support-files/mysql.server 46 basedir=/usr/local/mysql 47 datadir=/opt/data //Add this path after these two lines
4.10 initializing the database
After this command initializes the database, the database will not set the password. You need to enter the database to set the password [root@master local]# mysqld --initialize--insecure --user mysql --datadir /opt/data/ This command will generate a random password after initializing the database. You need to enter the random password to enter the database, and then modify the password you want [root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
4.11 configure system services to manage mysql
[root@master system]# pwd /usr/lib/systemd/system [root@master system]# cp sshd.service mysql.service [root@master system]# cat mysql.service [Unit] Description=mysql server daemon After=network.target [Service] Type=forking ExecStart=/usr/local/mysql/support-files/mysql.server start ExecStop=/usr/local/mysql/support-files/mysql.server stop ExecReload=/bin/kill -HUP $MAINPID [Install] WantedBy=multi-user.target [root@master ~]# systemctl daemon-reload [root@master ~]# systemctl enable --now mysql.service Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /usr/lib/systemd/system/mysql.service.
4.12 use the mysql command to enter the database and set the password
[root@master ~]# mysql
mysql> set password = password('New password');
5. Examples of adding, deleting, querying and modifying MySQL:
- Create a database named after you, and create a table student, which contains three fields (id, name, age). The table structure is as follows:
mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
- Check the contents of the newly created table (with a select statement)
- Insert data into the newly created student table (with insert statement), and the results should be as follows:
+----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+
- The age of modified lisi is 50
- Sort in descending order by age field
- Query the youngest 3 students in the student table and skip the first 2
- Query the oldest 4 students in the student table
- Query the record named zhangshan in the student table
- Query the records in the student table whose name is zhangshan and whose age is greater than 20 years old
- Query the student table for records aged between 23 and 30
- Modify wangwu's age to 100
- Delete the records in the student whose name is zhangshan and whose age is less than or equal to 20
First question mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.04 sec) mysql> create database wzw; //Create a library named wzw Query OK, 1 row affected (0.04 sec) mysql> show databases; //View which tables are in the database +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | wzw | +--------------------+ 5 rows in set (0.00 sec) mysql> use wzw; //Enter wzw this library Database changed mysql> create table student (id int not null auto_increment primary key,name varchar(100)not null,age tinyint); //Create student table Query OK, 0 rows affected (0.09 sec) mysql> desc student; //View table structure +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) The first question is finished
Second question mysql> select * from student; //View student content Empty set (0.00 sec) complete
Question 3
mysql> insert student (name,age) values ('tom',20),('jerry',23),('wangqing',)25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',null),('chenshuo',)10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20); //Because we set the automatic growth of id, there is no id(name,age) written here
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
complete
Question 4 mysql> update student set age=50 where id=7; //Modify the specified content according to the id location, or modify it according to the name. For example: update student set age=18 where name="wnagwu" this is to change the age of wangwu to 18. Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec) complete
Question 5 mysql> select * from student order by age desc; //Sort by age field in descending order. Adding desc means descending order, and sorting without desc means ascending order +----+-------------+------+ | id | name | age | +----+-------------+------+ | 7 | lisi | 50 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 3 | wangqing | 25 | | 2 | jerry | 23 | | 1 | tom | 20 | | 6 | zhangshan | 20 | | 11 | qiuxiaotian | 20 | | 10 | qiuyi | 15 | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | +----+-------------+------+ 11 rows in set (0.00 sec) complete
Question 6 mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec) mysql> select * from student order by age limit 2,3; //Skip the first 2 years in the table, and then display 3 +----+-------------+------+ | id | name | age | +----+-------------+------+ | 10 | qiuyi | 15 | | 1 | tom | 20 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 3 rows in set (0.00 sec) complete
Question 7
mysql> select * from student
-> ;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql> select * from student order by age desc limit 4; //First, sort the age field in descending order to get the four oldest students
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
complete
Question 8
mysql> select * from student where name="zhangshan"
-> ; //Locate by name
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
complete
Question 9 mysql> select * from student where name="zhangshan" and age > 20; //First find the one whose name is zhangshan, and then find the one who is older than 20 +----+-----------+------+ | id | name | age | +----+-----------+------+ | 5 | zhangshan | 26 | +----+-----------+------+ 1 row in set (0.00 sec) complete
Question 10 Both of the following methods are OK. The first method is recommended mysql> select * from student where age between 23 and 30; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | +----+-----------+------+ 4 rows in set (0.00 sec) mysql> select * from student where age >=23 and age <= 30; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | +----+-----------+------+ 4 rows in set (0.00 sec) complete
Question 11 mysql> update student set age=100 where name="wangwu"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec) complete
Question 12 mysql> delete from student where name="zhangshan" and age <= 20; Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 10 rows in set (0.00 sec) complete
Delete commands include delete and truncate
Let's talk about the difference between the two
| Statement type | characteristic |
|---|---|
| delete | DELETE when deleting table contents, only the contents will be deleted, but the table structure will be preserved; DELETE statement Deletes one line at a time and records one entry for each deleted line in the transaction log; Data can be recovered by rolling back the transaction log; Very space consuming |
| truncate | All data in the table is deleted and cannot be recovered. The table structure, constraints and indexes remain unchanged. The newly added row count value is reset to the initial value. The execution speed is faster than DELETE, and less system and transaction log resources are used. DELETE data by releasing the data page used to store table data, and only record the release of the page in the transaction log. For tables referenced by foreign key constraints, TRUNCATE TABLE cannot be used to DELETE data, and cannot be used for tables added to indexed views. |