mysql introduction and installation
mysql introduction
mysql is a famous open source relational database. It was first opened by AB company, and later it came to Sun company. After Sun was acquired by Oracle, mysql was owned by Oracle. Since then, mysql has been commercialized, and the database named mariadb has been open-source as a branch of mysql, and then mysql has been published in community version and enterprise version. mysql's general history is like this, more detailed can be Baidu, there is not much expansion here.
For developers, database is an inextricable technology. All mysql, a well-known and frequently used open-source database, is of course necessary to learn.
mysql installation
mysql can be installed by compiling the source code, or by downloading the binary files directly on mysql's official website, and then installing. Of course, in 2019 when container technology is so hot, it is more suitable for developers to install and use mysql directly on docker.
First, prepare a CentOS7 virtual machine and install docker. Download mysql image directly:
docker pull mysql:5.7
If the installation is slow in China, you can use Alibaba cloud's image acceleration.
Then start mysql image
docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
The default login password of the root user of mysql database is 123456. This machine uses mysql command to log in. If it is not installed, you can use yum command to install:
yum install -y mysql
Log in with mysql command:
mysql -uroot -p123456 -h 127.0.0.1
SQL
SQL introduction
The full name of SQL is Structure Query Language, which is a database application language using relational model. SQL statements are mainly divided into three types:
- DDL (Data Definition Languages) statements: data definition languages, which define different database objects such as data segments, databases, tables, columns, indexes, etc. Common statement keywords include create, drop, alter, etc.
- DML (Data Manipulation Language) statement: data manipulation statement, used to add, delete, update and query database records, and check data integrity. Common statement keywords include insert, delete, update and select.
- DCL (Data Control Language) statement: a data control statement used to control the direct permission and access level statements of different data segments. These statements define database, table, field, user's access rights and security level. The main statement keywords include grant, revoke, etc.
Note: in the SQL statement, either or \ g is the end of the statement. \g indicates line display output.
DDL statement
DDL is to create, delete, modify and other operations for the objects inside the database, which is generally used by the database administrator (DBA). Let's look at listing common DDL statements.
Create database
The command used to create the database is:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
For example, create a database named test:
MySQL [(none)]> CREATE DATABASE test DEFAULT CHARSET utf8; Query OK, 1 row affected (0.00 sec)
Note: in mysql, sql is case insensitive, so the above command is equivalent to create database test default charset utf8;
Select database
After creating the database, you can operate on the corresponding database. The command is:
USE dbname
For example, use database test:
MySQL [(none)]> use test; Database changed MySQL [test]>
You can see that the content in [] becomes the name of the test database.
Delete database
To create a database, delete the database. The command is:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
For example, to view the test database:
MySQL [test]> drop database if exists test; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]>
Note: after the database is deleted, all the following table data will be deleted, so you need to check and back up before deleting.
View all databases
The order is:
SHOW DATABASES
MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
Create table
The syntax for creating a table in the database is as follows:
CREATE TABLE [IF NOT EXISTS] tablename ( column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, ... column_name_n column_type_n constraints );
For example, create a user table with the fields id, name, age.
MySQL [test]> create table if not exists user ( -> id int(11) primary key, -> name varchar(32) not null, -> age int(11) -> );
Note: you need to select a database before creating a table.
View all tables
Also use show
SHOW tables
For example: view all tables under the test database
MySQL [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.00 sec)
View table structure
The view table structure command is:
DESC tablename
For example, look at the structure of the user you created earlier.
MySQL [test]> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | NO | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
The command with similar functions is show create table tbname:
MySQL [test]> show create table user\G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(32) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
Delete table
The delete command of the table is as follows:
DROP TABLE [IF EXISTS] tablename
For example: delete the user table created before:
MySQL [test]> drop table if exists user; Query OK, 0 rows affected (0.00 sec)
Modify table
Modify the table using the keyword alter. It can modify the field type of the table or add or delete the table field.
1. Modify table type:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|ALTER col_name]
For example: modify the name field of the user table, and change varchar(32) to varchar(64)
MySQL [test]> alter table user modify name varchar(64); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
2. Add table field
ALTER TABLE tbname ADD [COLUMN] column_definition [FIRST|ALTER col_name]
For example: add a field named email in user, and the type is varchar(64)
MySQL [test]> alter table `user` add `email` varchar(64) not null; Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | age | int(11) | YES | | NULL | | | email | varchar(64) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3. Delete table fields
The syntax is as follows:
ALTER TABLE tablename DROP [COLUMN] col_name
For example: delete the email field in user
MySQL [test]> alter table `user` drop `email`; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
4. Field name change
The syntax is as follows:
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|ALTER col_name]
For example: change the name to username and the type to varchar(32):
MySQL [test]> alter table `user` change `name` `username` varchar(32); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | username | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Note: both change and modify can modify the table definition. The difference is that the column name needs to be written twice after change, which is inconvenient. But change can be renamed, and modify cannot.
5. Modify the field order.
The keyword ADD, CHNAGE and MODIFY of the modified field support the option first|after, which is used to MODIFY the field location.
For example: add a new field email, and put it before the username field:
MySQL [test]> alter table `user` add `email` varchar(64) after `id`; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | email | varchar(64) | YES | | NULL | | | username | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+-------------
For example: modify the field email after the username field:
MySQL [test]> alter table `user` modify `email` varchar(64) after `username`; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [test]> desc user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | username | varchar(32) | YES | | NULL | | | email | varchar(64) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Note: the keywords such as CHNAGE|FIRST|ALTER belong to the expansion of MySQL on standard SQL, and are not necessarily applicable to other databases.
6. Modify table name
The syntax is as follows:
ALTER TABLE tablename RENAME [TO] new_name
For example, change the table user to TB u user:
MySQL [test]> alter table `user` rename `tb_user`; Query OK, 0 rows affected (0.00 sec) MySQL [test]> desc tb_user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | username | varchar(32) | YES | | NULL | | | email | varchar(64) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
DML statement
DML statement is the operation statement of database table. It can insert, delete, update and select the contents of database table.
insert record
After the table is created, a record is inserted into the table. The syntax is as follows:
INSERT INTO tablename (field1,field2,field3,...,fieldn) VALUES (value1,value2,value3,...,valuen)
For example, insert a record in the user table with id 1, name xiaoming and age 20.
MySQL [test]> insert into `user` (`id`,`name`,`age`) values (1,'xiaoming',20); Query OK, 1 row affected (0.01 sec) MySQL [test]> select id, name, age from `user`; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | xiaoming | 20 | +----+----------+------+ 1 row in set (0.00 sec)
Note: if the initial value is set for the field type in the table, the value of the field can not be specified when inserting, so it will be inserted into the table with the default value.
select is the keyword of SQL query operation, which will be described later. insert statement supports inserting multiple records at the same time:
INSERT INTO tablename (field1,field2,field3,...,fieldn) VALUES (value1,value2,value3,...,valuen), (value1,value2,value3,...,valuen) ... (value1,value2,value3,...,valuen);
For example: insert multiple records in the user table at the same time:
MySQL [test]> insert into `user` (`id`,`name`,`age`) -> values -> (2,'John',40), -> (3,'Lisa',18); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL [test]> select id, name, age from `user`; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | xiaoming | 20 | | 2 | John | 40 | | 3 | Lisa | 18 | +----+----------+------+ 3 rows in set (0.00 sec)
Note: simultaneous insertion can save a lot of network overhead and improve the efficiency of insertion.
Update record
The record syntax of the update table is as follows:
UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]
For example: change John's age to 30:
MySQL [test]> update `user` set age=30 where name='John'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> select id, name, age from `user` where name='John'; +----+------+------+ | id | name | age | +----+------+------+ | 2 | John | 30 | +----+------+------+ 1 row in set (0.00 sec)
Note: when modifying, you need to use the keyword where to specify the conditions, otherwise all the specified fields of the records in the table will be modified.
UPDATE supports modifying records of multiple tables at the same time. The syntax is as follows:
UPDATE t1,t2,...tn SET t1.field1=value1,...,tn.fieldn=valuen [WHERE CONDITION]
Delete record
To delete a database table record, use the command delete. The syntax is as follows:
DELETE FROM tablename [WHERE CONDITION]
For example, to delete a record name d 'xiaoming':
MySQL [test]> delete from `user` where name='xiaoming'; Query OK, 1 row affected (0.00 sec) MySQL [test]> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 2 | John | 30 | | 3 | Lisa | 18 | +----+------+------+ 2 rows in set (0.00 sec)
Similarly, delete operation also supports multi table deletion. The syntax is as follows:
DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]
In order to simplify the SQL commands, you can add aliases to tables when you perform multi table operations. If you use an alias, you also need to represent the table with an alias before the field.
Note: you must also specify conditions when deleting. Without where conditions, all records in the table will be deleted.
Query record
The query record uses the command select, with the syntax as follows:
SELECT * FROM tablename [WHERE CONDITION]
For example: query all records of user table:
MySQL [test]> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 2 | John | 30 | | 3 | Lisa | 18 | +----+------+------+ 2 rows in set (0.00 sec)
*Indicates to output all fields, so the above select command is equivalent to select id, name, age from user. And the select command is also the most frequently used command, which supports various ways of query.
1. Query and de duplication.
Use the keyword distinct to de duplicate the value of a field. For example: field name to query again.
MySQL [test]> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | John | 40 | | 2 | John | 30 | | 3 | Lisa | 18 | | 4 | Lisa | 18 | +----+------+------+ 4 rows in set (0.00 sec) MySQL [test]> select distinct name from user; +------+ | name | +------+ | John | | Lisa | +------+ 2 rows in set (0.00 sec)
2. Condition query.
Use the where keyword to specify the query criteria. For example: query the record with name John and age 30:
MySQL [test]> select * from user where name='John' and age=30; +----+------+------+ | id | name | age | +----+------+------+ | 2 | John | 30 | +----+------+------+ 1 row in set (0.01 sec)
Use and to connect multiple query criteria, or to query records that meet one of the criteria. For example: query the record with name John or id=3:
MySQL [test]> select * from user where name='John' or id=3; +----+------+------+ | id | name | age | +----+------+------+ | 1 | John | 40 | | 2 | John | 30 | | 3 | Lisa | 18 | +----+------+------+ 3 rows in set (0.00 sec)
The condition after where is an expression, which supports various operators: >, <, > =, < =,! =.
3. ranking
Use the keyword ORDER BY to sort the query records. The syntax is as follows:
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC],...,fieldn [DESC|ASC]
DESC and ASC are the key words of sorting order. DESC tables are arranged in descending order according to the fields. ASC indicates ascending order. The default is ascending order. If there are multiple fields, sort the first field first, and then sort the second field if the fields are the same, and so on. For example: sort the records of user table by age field:
MySQL [test]> select * from user order by age desc; +----+------+------+ | id | name | age | +----+------+------+ | 1 | John | 40 | | 2 | John | 30 | | 3 | Lisa | 18 | | 4 | Lisa | 18 | +----+------+------+ 4 rows in set (0.00 sec) MySQL [test]> select * from user order by age asc; +----+------+------+ | id | name | age | +----+------+------+ | 3 | Lisa | 18 | | 4 | Lisa | 18 | | 2 | John | 30 | | 1 | John | 40 | +----+------+------+ 4 rows in set (0.01 sec)
4. Limits and offsets
Use the keywords OFFSET and LIMIT to LIMIT the number of query records and the range of queries already made. The syntax is as follows:
SELECT * FROM LIMIT N OFFSET M
For example: query the first record of user table:
MySQL [test]> select * from user limit 2; +----+------+------+ | id | name | age | +----+------+------+ | 1 | John | 40 | | 2 | John | 30 | +----+------+------+ 2 rows in set (0.00 sec)
For example, query the second record of the user table:
MySQL [test]> select * from user limit 1 offset 1; +----+------+------+ | id | name | age | +----+------+------+ | 2 | John | 30 | +----+------+------+ 1 row in set (0.00 sec)
In addition, OFFSET and LIMIT can be used together to realize paging effect of query: for example, query page 2 of user table, 5 records per page:
MySQL [test]> select * from user limit 5 offset 5;
Note: limit belongs to the syntax after MySQL extended SQL92, which is not necessarily common on other databases.