day12--Database (Mysq)

1. Introduction to databases

What is a database?(https://www.cnblogs.com/alex3714/articles/5950372.html)

A Database is a warehouse that organizes, stores, and manages data according to its structure.
Each database has one or more different API s for creating, accessing, managing, searching, and replicating stored data.
We can also store data in files, but reading and writing data in files is relatively slow.
So now we use relational database management systems (RDBMS) to store and manage large amounts of data.The so-called relational database is a database based on the relational model, which processes the data in the database with the help of mathematical concepts and methods such as set algebra.
RDBMS is the feature of Relational Database Management System:
1. Data appears in tabular form
2. Record names per behavior
3. The data field for each column that corresponds to the record name
4. Many rows and columns form a form
5. Several forms form a database

The following figure is a database

RDBMS terminology

Before we start learning about MySQL databases, let's look at some terms for RDBMS:

Database: A database is a collection of related tables;

Datasheet: A table is a matrix of data.A table in a database looks like a simple spreadsheet;

Column: A column (data element) contains the same data, such as postal code data;

Line: A row (=tuple, or record) is a set of related data, such as a user-subscribed data

Redundancy: Stores twice as much data. Redundancy can make your system faster.(The more normalized the table, the more relationships between tables; join queries may often be required between multiple tables; and join operations can slow down queries.For example, student information is stored in the student table, and department information is stored in the Department table.Associate with the Department table through the dept_id field in the student table.If you want to query the name of a department in which a student resides, you must look up the number (dept_id) of the Department in which the student resides from the student table, and then use that number to department to find the name of the department.Join queries can waste a lot of time if this is often needed.So you can add a redundant field, dept_name, to the student table to store the name of the Department in which the student resides.This saves you from having to connect every time.)

Primary key: The primary key is unique.A data table can contain only one primary key.You can use the primary key to query data;

Foreign keys: Foreign keys are used to associate two tables;

Composite keys: Composite keys (composite keys) use multiple columns as an index key and are generally used for composite indexes.

Index: Use an index to quickly access specific information in a database table.An index is a structure that sorts the values of one or more columns in a database table.A catalogue similar to a book;

Referential Integrity: Referential Integrity requires that references to non-existent entities not be allowed in a relationship.Integrity and entity integrity are the integrity constraints that the relationship model must satisfy in order to ensure data consistency.

Mysql Database

Mysql is the most popular relational database management system, and MySQL is one of the best RDBMS(Relational Database Management System) applications for WEB applications.Developed by Swedish MySQL AB and currently owned by Oracle.MySQL is an associated database management system that increases speed and flexibility by keeping data in different tables instead of all in one large warehouse.

Mysql is open source, so you don't have to pay extra fees;

2.Mysql supports large databases.Can handle large databases with tens of millions of records;

3.MySQL uses the standard form of SQL data language;

Mysql can be allowed on multiple systems and supports multiple languages.These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, Tcl, etc.

5.Mysql has good support for PHP, which is currently the most popular Web development language;

MySQL supports large databases, data warehouses with 50 million records, 32-bit system tables can support up to 4GB, 64-bit systems can support up to 8TB;

7.Mysql is customizable and uses the GPL protocol, so you can modify the source code to develop your own Mysql system.

2. Installation and use of MYSQ database

* Install Mysql on Linux/UNIX

RPM packages are recommended for installing Mysql on Linux platforms, and MySQL AB provides download addresses for the following RPM packages:

1.MySQL - MySQL Server.You need this option unless you only want to connect to a MySQL server running on another machine;

2.MySQL-client - MySQL client program for connecting to and operating Mysql servers;

3.MySQL-devel-library and include files, if you want to compile other MySQL clients, such as Perl modules, you need to install the RPM package;

4.MySQL-shared - This package contains shared libraries (libmysqlclient.so*) that some languages and applications need to load dynamically and uses MySQL;

5. Benchmarking and performance testing tools for MySQL-bench-MySQL database servers.

The following example of installing Mysql RMP is on a SuSE Linux system, although this installation step is also appropriate for other RPM-enabled Linux systems, such as Centos.

The installation steps are as follows:

* Log on to your Linux system using root users.

Download the Mysql RPM package at: MySQL Download.

* Perform the Mysql installation with the following commands, rpm packages downloaded for you:

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

The above process of installing the mysql server creates a mysql user and creates a mysql configuration file, my.cnf.

You can find all MySQL-related binaries in / usr/bin and / usr/sbin.All tables and databases will be created in the / var/lib/mysql directory.

Following are some installation procedures for mysql optional packages that you can install as needed:

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm　

* Install Mysql on Window s

* Installing Mysql on Window s is relatively easy, you only need to load it MySQL Download Download the mysql installation package for the window version and unzip the installation package.

Double-click the setup.exe file. Next, all you need to do is install the default configuration and click next. By default, the installation information will be in the C:\mysql directory.

Next you can switch to the C:\mysql\bin directory at the command prompt by typing the cmd command= in the search box with Start= and type a command:

    mysqld.exe --console

The above commands will output some mysql startup and InnoDB information if the installation is successful.

* Verify Mysql installation

After a successful installation of Mysql, some basic tables are initialized, and after the server starts, you can verify that Mysql is working properly through a simple test.

* Use the mysqladmin tool to get the server status:

Check the version of the server using the mysqladmin command and the binary on linux at / usr/bin on linux and on window s at C:\mysql\bin.

[root@host]# mysqladmin --version

The command on linux will output the following results based on your system information:

mysqladmin  Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

If you did not enter any information after executing the above command, your Mysql installation was not successful.

Using MySQL Client(Mysql client) to execute simple SQL commands

You can use the mysql command to connect to the mysql server in the MySQL Client(Mysql client). By default, the password of the mysql server is empty, so no password is required for this example.

The commands are as follows:

[root@host]# mysql

The above command will output a MySQL > prompt when executed, which indicates that you have successfully connected to the Mysql server. You can execute the SQL command at the MySQL > prompt:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.13 sec)

What Mysql needs to do after installation

After successful Mysql installation, the default root user password is empty. You can use the following command to create the root user password:

[root@host]# mysqladmin -u root password "new_password";

You can now connect to the Mysql server using the following commands:

[root@host]# mysql -u root -p
Enter password:*******

Note: When entering a password, the password will not be displayed, you can enter it correctly.

Start MySQL at Linux system startup

If you need to start the MySQL server at Linux system startup, you need to add the following commands to the / etc/rc.local file:

/etc/init.d/mysqld start

Similarly, you need to add the mysqld binary to the / etc/init.d/directory.

3. mysql management

Start and shut down MySQL server

First, we need the following commands to check whether the MySQL server is started:

ps -ef | grep mysqld

If MySql is started, the above command will output a list of MySQL processes. If MySQL is not started, you can use the following command to start the MySQL server:

root@host# cd /usr/bin
./mysqld_safe &

If you want to shut down the currently running MySQL server, you can execute the following commands:

 1 root@host# cd /usr/bin

 2 ./mysqladmin -u root -p shutdown

 3 Enter password: ****** 

MySQL User Settings

If you need to add MySQL users, you only need to add new users to the user table in the MySQL database.

The following is an example of adding a user with the user name guest and password guest123, and authorizing the user to perform SELECT, INSERT, and UPDATE operations:

 

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;                                    #Enter Database Directive
Database changed
 
mysql> INSERT INTO user
          (host, user, password,
           select_priv, insert_priv, update_priv)
           VALUES ('localhost', 'guest',
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

 

When adding users, be aware to use the PASSWORD() function provided by MySQL to encrypt passwords.You can see in the above example that the user password is encrypted as: 6f8c114b58f2ce9e.

Note: The password of the user table in MySQL 5.7 has been replaced with authentication_string.

Note: Be aware that the FLUSH PRIVILEGES statement needs to be executed.This command will reload the authorization form when executed.

If you do not use this command, you will not be able to connect to the mysql server using the newly created user unless you restart the mysql server.

mysql> use mysql;                                    #Enter mysql database
Database changed                                  
mysql> show tables;                                  #Tables in mysql database
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.01 sec)

There are databases and tables in the database. If there are tables in the database, the database is equivalent to a workbook and the tables are equivalent to a worksheet.

Desc table #Display information inside the sheet to view table data

mysql> desc servers;            #Displays the information in the worksheet servers, how many columns are there, and the names of the columns are as follows       
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

Fields in the database, Field, Type (field type), Null (empty), key (primary key), Default (default), Extra

Select * from table\G; view the data in the worksheet, \G is to have the data displayed in columns, too much data is displayed in confusion in the operating system

mysql> select * from user\G;                                #Display information in database tables
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: auth_socket
 authentication_string: 
      password_expired: N
 password_last_changed: 2017-09-23 11:09:00
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: Y
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2017-09-23 11:09:04
     password_lifetime: NULL
        account_locked: Y
*************************** 3. row ***************************
                  Host: localhost
                  User: mysql.sys
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2017-09-23 11:09:04
     password_lifetime: NULL
        account_locked: Y
*************************** 4. row ***************************
                  Host: localhost
                  User: debian-sys-maint
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *F9509400A27C5FF86A2809919526D0679EF9F15C
      password_expired: N
 password_last_changed: 2017-09-23 11:09:11
     password_lifetime: NULL
        account_locked: N
4 rows in set (0.01 sec)

MySQL User Settings

If you need to add MySQL users, you only need to add new users to the user table in the MySQL database.

The following is an example of adding a user with the username guest and password guest123, and authorizing the user to perform SELECT, INSERT, and UPDATE operations:

 

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
 
mysql> INSERT INTO user
          (host, user, password,
           select_priv, insert_priv, update_priv)
           VALUES ('localhost', 'guest',
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

 

When adding users, be aware to use the PASSWORD() function provided by MySQL to encrypt passwords.You can see in the above example that the user password is encrypted as: 6f8c114b58f2ce9e.

Note: The password of the user table in MySQL 5.7 has been replaced with authentication_string;

Note: Be aware that the FLUSH PRIVILEGES statement needs to be executed.This command will reload the authorization table when executed.

If you do not use this command, you will not be able to connect to the mysql server using the newly created user unless you restart the mysql server.

You can assign permissions to users when they are created. In the corresponding permissions column, set to'Y'in the insert statement. The list of user permissions is as follows:

    Select_priv，Insert_priv，Update_priv，Delete_priv，Create_priv，Drop_priv，Reload_priv，Shutdown_priv，Process_priv，

File_priv，Grant_priv；

Another way to add users is through the SQL GRANT command, which adds user Zara with password zara123 to the specified database TUTORIALS.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed
 
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

* etc/my.cnf file configuration

In general, you do not need to modify the configuration file, which is configured as follows by default:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
[mysql.server]
user=mysql
basedir=/var/lib
 
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Commands to manage MySQL

The following lists the commands commonly used in using Mysql databases:

1.USE Database Name: Select the Mysql database you want to operate on, and all Mysql commands will only target that database after using this command;

SHOW DATABASES: List the databases of the MySQL database management system;

SHOW TABLES: #Displays all tables of the specified database, you need to use the use command to select the database you want to operate on before using this command;

4.SHOW COLUMNS FROM data table: #Display the attributes, attribute types, primary key information, whether NULL, default values and other information of the data table;

5.create database testdb charset "utf8"; #create a database called testdb and make it support Chinese;

Drop database testdb; #delete database;

SHOW INDEX FROM data table: Displays detailed index information of the data table, including PRIMARY KEY (primary key);

Create a database using create database as follows:

mysql> create database oldboydb;                   #Create a database so that it cannot be written in Chinese by default. There is a character set latin1 in it which is Latin by default and does not support Chinese
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboydb;              #View information about the created database
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| oldboydb | CREATE DATABASE `oldboydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

When creating a database, set the string utf8 as follows:

mysql> create database oldboydb charset "utf8";                     #Default database table can be Chinese, utf8 level
Query OK, 1 row affected (0.04 sec)

mysql> show create database oldboydb;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| oldboydb | CREATE DATABASE `oldboydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

4. MySQL data types

The types of data fields defined in MySQL are important for optimizing your database.

MySQL supports many types and can be roughly divided into three categories: numeric, date/time, and string (character) types.

- Numeric type

 

MySQL supports all standard SQL numeric data types

These include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

The keyword INT is synonymous with INTEGER and the keyword DEC is synonymous with DECIMAL.

The BIT data type holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT as extensions to the SQL standard.The following table shows the storage and extent of each integer type required.

* Date and time type

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a valid range of values and a "zero" value, which is used when specifying values that are not represented by illegal MySQL.

The TIMESTAMP type has a unique automatic update feature that will be described later.

* String type

String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.This section describes how these types work and how to use them in queries.

 

type Size purpose
CHAR 0-255 Bytes Fixed-length string
VARCHAR 0-65535 Bytes Variable length string
TINYBLOB 0-255 Bytes A binary string of no more than 255 characters
TINYTEXT 0-255 Bytes Short text string
BLOB 0-65 535 bytes Long text data in binary form
TEXT 0-65 535 bytes Long Text Data
MEDIUMBLOB 0-16 777 215 bytes Medium-length text data in binary form
MEDIUMTEXT 0-16 777 215 bytes Medium-length text data
LONGBLOB 0-4 294 967 295 bytes Maximum text data in binary form
LONGTEXT 0-4 294 967 295 bytes Very large text data

 

CHAR and VARCHAR types are similar, but they are saved and retrieved in different ways.Their maximum length and whether trailing spaces are retained vary.There is no case conversion during storage or retrieval.

The BINARY and VARBINARY classes are similar to CHAR and VARCHAR except that they contain binary strings rather than non-binary strings.That is, they contain byte strings instead of character strings.This means they have no character set and sort and compare numeric values based on column value bytes.

BLOB is a binary large object that can hold a variable amount of data.There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.They differ only in the maximum length that can hold values.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.These correspond to four BLOB types with the same maximum length and storage requirements.

5. Common MySQL commands

MySQL Create Data Table

Grammar

    create table table_name(column_name column_type);

Create a student table

create table student(
   stu_id INT NOT NULL AUTO_INCREMENT,
   name CHAR(32) NOT NULL,
   age  INT NOT NULL,
   register_date DATE,
   PRIMARY KEY ( stu_id )
);

Create databases, database operations, transform databases for creation;

mysql> create table student(stu_id INT NOT NULL AUTO_INCREMENT,
    -> name char(32) not null,
    -> age int not null,
    -> register_date  date not null,
    -> primary key (stu_id));
Query OK, 0 rows affected (1.04 sec)

mysql> show tables
    -> ;
+--------------------+
| Tables_in_oldboydb |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

Display the created database information:

mysql> desc student;                      #Show information about the created student table
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| stu_id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name          | char(32) | NO   |     | NULL    |                |
| age           | int(11)  | NO   |     | NULL    |                |
| register_date | date     | NO   |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.10 sec)

Instance resolution:

1. If you don't want the field to be NULL, you can set the field's property to NOT NULL. When you operate a database, you will get an error if you enter NULL for the field's data.

2.AUTO_INCREMENT defines columns as self-increasing attributes, typically used for primary keys, with values automatically added by 1;

3. The PRIMARY KEY keyword is used to define a column as the primary key.You can use multiple columns to define the primary key, separated by commas.

MySQL Insert Data

Grammar

 

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

 

Insert data:

mysql> insert into student (name,age,register_date) values ("alex li",22,"2016-03-4")
    -> ;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex li |  22 | 2016-03-04    |
+--------+---------+-----+---------------+
1 row in set (0.00 sec)

Insert data into the stuedent table as follows:

mysql> insert into student (name,age,register_date) values ("wupeiqi",28,"2017-5-20");
Query OK, 1 row affected (0.22 sec)

mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  22 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
+--------+---------+-----+---------------+
2 rows in set (0.00 sec)

MySQL Query Data

Grammar

1 SELECT column_name,column_name
2 FROM table_name
3 [WHERE Clause]
4 [LIMIT M ][OFFSET N]     #Offset offset rows, starting with M rows, LIMIT shows the number N.

1. You can use one or more tables in a query statement, use commas (,) to split tables, and use WHERE statements to set query conditions;

2. The SELECT command can read one or more records;

3. You can use asterisks (*) instead of other fields, and the SELECT statement returns all the field data of the table.

4. You can use the WHERE statement to contain any conditions;

5. You can specify the data offset from which the SELECT statement begins the query through OFFSET.By default, the offset is 0;

6. You can use the LIMIT property to set the number of records returned;

mysql> select * from student limit 3 offset 2;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      3 | alex li |  24 | 2016-03-04    |
|      4 | alex li |  24 | 2016-03-01    |
|      5 | alex li |  24 | 2016-03-02    |
+--------+---------+-----+---------------+
3 rows in set (0.00 sec)
For example, with this SQL, limit is followed by three pieces of data, offset is followed by reading from Article 3
 
mysql> select * from student limit 3 ,1;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      4 | alex li |  24 | 2016-03-01    |
+--------+---------+-----+---------------+
1 row in set (0.00 sec)
 
This SQL, followed by a limit, reads from Article 3, reading one piece of information.
mysql> select * from student where name = "alex";
+--------+------+-----+---------------+
| stu_id | name | age | register_date |
+--------+------+-----+---------------+
|      1 | alex |  22 | 2017-01-18    |
+--------+------+-----+---------------+
1 row in set (0.08 sec)

Select * from table (worksheet name) limit n offset m, limit the output first, then where to start the query, offset must be used with limit, but limit does not have to be with offset, as follows:

mysql> select * from student limit 5 offset 3;               #First determine the output, where to start the query
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      4 | wupeiqi |  28 | 2017-05-20    |
|      5 | chunyun |  26 | 2016-05-16    |
+--------+---------+-----+---------------+
2 rows in set (0.00 sec)

mysql> select * from student limit 2                        #limit can be used without offset;
    -> ;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  22 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
+--------+---------+-----+---------------+
2 rows in set (0.00 sec)
mysql> select * from student offset 2;                     #But offset must be used with limit;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line

MySQL where clause

Grammar

 

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

 

The following is a list of operators that can be used in a WHERE clause.

The examples in the table below assume that A is 10B and 20

Like fuzzy query; where name like "sheet%"

Operator describe Example
= Equal sign, checks if two values are equal, returns true if they are equal (A = B) returns false.
<>, != Not equal, detects if two values are equal, returns true if they are not equal (A!= B) Returns true.
> Greater than sign, detects if the value on the left is greater than the value on the right, returns true if the value on the left is greater than the value on the right (A > B) returns false.
< Less than sign, detects if the value on the left is less than the value on the right, returns true if the value on the left is less than the value on the right (A < B) returns true.
>= Greater than or equal to sign, detects if the value on the left is greater than or equal to the value on the right, returns true if the value on the left is greater than or equal to the value on the right (A >= B) returns false.
<= Less than or equal to sign, detects if the value on the left is less than or equal to the value on the right, and returns true if the value on the left is less than or equal to the value on the right (A <= B) Returns true.

Primary keys are very fast for conditional queries as WHERE clauses.

 

select * from student where register_date > '2016-03-04';
select * from student where register date like "2016-06%";                    #Fuzzy Query, Date June 2016

 

MySQL UPDATE Query

 

Grammar

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
 
  update student set age=22 ,name="Alex Li" where stu_id>3;

Modify the age of alex in table stundet as follows:

mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  22 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
|      3 | geng    |  27 | 2019-01-15    |
|      4 | wupeiqi |  28 | 2017-05-20    |
|      5 | chunyun |  26 | 2016-05-16    |
+--------+---------+-----+---------------+
5 rows in set (0.00 sec)

mysql> update student set age = 38 where name = "alex";     #Modify the age of alex for student s in the table to 38
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  38 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
|      3 | geng    |  27 | 2019-01-15    |
|      4 | wupeiqi |  28 | 2017-05-20    |
|      5 | chunyun |  26 | 2016-05-16    |
+--------+---------+-----+---------------+
5 rows in set (0.00 sec)

MySQL DELETE statement

Grammar

 

DELETE FROM table_name [WHERE Clause]<br><br>      delete from student where stu_id=5;

 

 

mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  38 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
|      3 | geng    |  27 | 2019-01-15    |
|      4 | wupeiqi |  28 | 2017-05-20    |
|      5 | chunyun |  26 | 2016-05-16    |
+--------+---------+-----+---------------+
5 rows in set (0.00 sec)

mysql> delect from student where name = "geng";                           #Delete rows named geng from the student table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near 'delect from student where name = "geng"' at line 1
mysql> delete from student where name = "geng";
Query OK, 1 row affected (0.05 sec)

mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name    | age | register_date |
+--------+---------+-----+---------------+
|      1 | alex    |  38 | 2017-01-18    |
|      2 | wupeiqi |  28 | 2017-05-20    |
|      4 | wupeiqi |  28 | 2017-05-20    |
|      5 | chunyun |  26 | 2016-05-16    |
+--------+---------+-----+---------------+
4 rows in set (0.00 sec)

MySQL LIKE clause

Grammar

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
 
select *from student where name binary like "%Li";
select *from student where name binary like  binary "%Li"; #Match only uppercase

MySQL Sorting

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
Use ASC or DESC keywords to set the query results to be in ascending or descending order.By default, it is sorted in ascending order.
select *from student where name like binary "%Li" order by stu_id desc;

MySQL GROUP BY statement

 

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

 

Example:

mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
| 1 | Xiaoming | 2016-04-22 15:25:33 | 1 |
| 2 | King | 2016-04-20 15:25:47 | 3 |
| 3 | Xiaoli | 2016-04-19 15:26:02 | 2 |
| 4 | King | 2016-04-07 15:26:14 | 4 |
| 5 | Xiaoming | 2016-04-11 15:26:40 | 4 |
| 6 | Xiaoming | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
 
Next, we use the GROUP BY statement to group the data tables by name and count how many records each person has:
mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
|Xiaoli| 1 |
|Xiaoming| 3 |
| Xiaowang | 2 |
+--------+----------+
3 rows in set (0.01 sec)
 
Use WITH ROLLUP
mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
|Xiaoli| 2 |
|Xiaoming| 7 |
| Xiaowang | 7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)
It records the number of NULL logins for everyone.<br>
We can use coalesce to set a name that can replace NUll, coalesce syntax:
Mysql> SELECT coalesce (name,'total'), SUM (singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

Group Statistics:

mysql> select name,count(name) as "Number of people" from student group by name;
+---------+--------+
| name    | Number of people   |
+---------+--------+
| alex    |      1 |
| chunyun |      1 |
| wupeiqi |      2 |
+---------+--------+
3 rows in set (0.00 sec)

MySQL ALTER command

The MySQL ALTER command is required when we need to modify the data table name or the data table fields.

Delete, add or modify table fields

 

alter table student drop register_date; #from student Table Delete register_date   field alter table student add phone int(11) not null; ＃Add to phone field

 

Delete the fields in the table as follows:

alter table student drop register_date; #from student Table Delete register_date

Delete the register_date field from the student table as follows:

mysql> alter table student drop register_date;    #Delete the register_date field from the student worksheet, first alter table, alter (change; modify) table (worksheet)
Query OK, 0 rows affected (1.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;                       
+--------+---------+-----+
| stu_id | name    | age |
+--------+---------+-----+
|      1 | alex    |  38 |
|      2 | wupeiqi |  28 |
|      4 | wupeiqi |  28 |
|      5 | chunyun |  26 |
+--------+---------+-----+
4 rows in set (0.00 sec)

Add fields to the worksheet, alter table table name add field name + attribute, as follows:

alter table student add phone int(11) not null; add phone field
mysql> alter table student add sex char(16) not null;       #Insert a sex field into the worksheet student, character type, not empty
Query OK, 0 rows affected (1.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+---------+-----+-----+
| stu_id | name    | age | sex |
+--------+---------+-----+-----+
|      1 | alex    |  38 |     |
|      2 | wupeiqi |  28 |     |
|      4 | wupeiqi |  28 |     |
|      5 | chunyun |  26 |     |
+--------+---------+-----+-----+
4 rows in set (0.00 sec)

Modify field type and name

If you need to modify the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command;

For example, by changing the type of field c from CHAR(1) to CHAR(10), you can execute the following commands:

 

mysql> alter table student change name Full name char(64) not null;       #alter table is a modification of the table, changing the name to Chinese, character type, not empty, the field must have these two attributes;
Query OK, 4 rows affected (1.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table student change age Age int not null;             #Field must have type and be empty
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student change gender Gender char(16) not null;
Query OK, 4 rows affected (1.44 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+---------+--------+--------+
| number   | Full name    | Age   | Gender   |
+--------+---------+--------+--------+
|      1 | alex    |     38 | female     |
|      2 | wupeiqi |     28 |        |
|      4 | wupeiqi |     28 |        |
|      5 | chunyun |     26 |        |
+--------+---------+--------+--------+
4 rows in set (0.05 sec)

 

change is a data type that modifies a field. It cannot modify the name of a field. change can modify the name and type of a field:

Modify the data type of the field with modify:

mysql> alter table student modify Telephone char(32) not null;      #Use modify to modify the length and empty of the phone field;
Query OK, 4 rows affected (1.42 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      4 | wupeiqi |     28 |        | 15111252591 |
|      5 | chunyun |     26 |        | 15111252591 |
+--------+---------+--------+--------+-------------+
4 rows in set (0.00 sec)

Modify field type and name

 

If you need to modify the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command:

For example, by changing the type of field c from CHAR(1) to CHAR(10), you can execute the following commands:

 

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

 

With the CHANGE clause, the grammar is very different.Following the CHANGE keyword is the field name you want to modify, then specify the new field name and type.Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
 
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Effect of ALTER TABLE on Null and Default Values

 

When you modify a field, you can specify whether to include only or whether to set a default value:

The following example specifies that field j is NOT NULL and the default value is 100.

 

1 mysql> ALTER TABLE testalter_tbl
2     -> MODIFY j BIGINT NOT NULL DEFAULT 100;

 

Change Table Name

 

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

 

Modify the name of the table in the database and change the name of the student table to message as follows:

mysql> alter table student rename to message;      #The method to modify the table name is alter table name rename to (rename) new_name;
Query OK, 0 rows affected (0.34 sec)

mysql> show tables;
+--------------------+
| Tables_in_oldboydb |
+--------------------+
| message            |
+--------------------+
1 row in set (0.01 sec)

mysql> select * from message;
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      4 | wupeiqi |     28 |        | 15111252591 |
|      5 | chunyun |     26 |        | 15111252591 |
+--------+---------+--------+--------+-------------+
4 rows in set (0.00 sec)

* About foreign keys

Foreign keys, a special index used for two key tables, can only be specified:

constraint foreign key name foreign key... Is only used when defining foreign keys. It is convenient to delete foreign keys.

    constriant FK_stu_id foreign key

        

Create a foreign key association. Below is an association with the student table above, where the stu_id is the primary key in the student and the foreign key in the course table.

 

mysql> create table course(id int not null,
　　 -> course_name char(32) not null,
　　 -> stu_id int,primary key(id),
    -> constraint FK_stu_id foreign key (stu_id) references student (number));

 

View the results of foreign key creation:

mysql> desc course;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(11)  | NO   | PRI | NULL    |       |
| course_name | char(32) | NO   |     | NULL    |       |
| stu_id      | int(11)  | YES  | MUL | NULL    |       |
+-------------+----------+------+-----+---------+-------+
3 rows in set (0.59 sec)

As you can see from the above, foreign keys are created successfully; what are the functions of foreign keys? Here's a try:

The following inserts data into the course table:

mysql> insert into course (course_name,stu_id) values ("math",1);        #Insert data into table course
Query OK, 1 row affected (0.30 sec)

mysql> select * from course;
+----+-------------+--------+
| id | course_name | stu_id |
+----+-------------+--------+
|  1 | English     |      2 |
|  2 | Chinese     |      3 |
|  3 | math        |      1 |
+----+-------------+--------+
3 rows in set (0.00 sec)

The stu_id inserted above exists in the student association table. Now insert a non-existent stu_id to see what happens:

mysql> insert into course (id,course_name,score,stu_id) values(2,"English",89,5);    #Create a record that does not exist in the student table and you can see that the error occurred

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`oldboydb`.`course`, 
CONSTRAINT `FK_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `student` (`number`))

You can see that foreign keys and primary keys are related, and they are related, and cannot be created if the associated table does not exist; only records that exist can be created.Referential integrity of the database.

In the course table below, delete the data as follows:

mysql> select * from course;
+----+-------------+-------+--------+
| id | course_name | score | stu_id |
+----+-------------+-------+--------+
|  1 | Chinese     |    69 |      2 |
|  2 | Englist     |    89 |      1 |
|  3 | France      |   100 |      3 |
+----+-------------+-------+--------+
3 rows in set (0.00 sec)

mysql> delete from course where stu_id = 3;     #It is clear that there is no problem deleting the record stu_id=3 from the course table.
Query OK, 1 row affected (0.06 sec)

mysql> select * from course;
+----+-------------+-------+--------+
| id | course_name | score | stu_id |
+----+-------------+-------+--------+
|  1 | Chinese     |    69 |      2 |
|  2 | Englist     |    89 |      1 |
+----+-------------+-------+--------+
2 rows in set (0.00 sec)

There is no problem deleting records in the course table. Here's how deleting data in the student table works:

mysql> select * from student;
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      3 | wupeiqi |     28 |        | 15111252591 |
|      4 | tom     |     46 | male     | 15111009430 |
+--------+---------+--------+--------+-------------+
4 rows in set (0.00 sec)

mysql> delete from student where number = 4;                       #Delete the record with number = 4 in the student table, which does not exist in the course r Association table, deletion is unaffected
Query OK, 1 row affected (0.06 sec)

mysql> delete from student where number = 3;
Query OK, 1 row affected (0.07 sec)

mysql> delete from student where number = 2;                      #Delete the record with number=2 in the student table, which is associated in course s, where stu_id=2 depends on record number=2 in the student table and cannot be deleted
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`oldboydb`.`course`，

 CONSTRAINT `FK_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `student` (`number`))

From the code above, you can see that to delete the data in the student, it is normal to delete if there are no dependencies on the associated table, and if there are dependencies that cannot be deleted, this is the benefit of the database, and the role of foreign keys is reflected here to ensure data consistency.

There are two main functions of foreign keys:

1. Let the database itself use foreign keys to ensure data integrity and consistency;

2. One is to increase the readability of ER diagrams.

constraint foreign key name foreign key... Is only used when foreign keys are defined to facilitate deletion of foreign keys

Primary and foreign keys are defined primarily to maintain the integrity of the relational database. To summarize:

1. The primary key is the only identification that can determine a record, for example, a record includes a positive identity number, a name, and an age. ID number It's the only way to be sure you're a person. Everything else might be duplicated, so, ID number Is the primary key.

Foreign keys are used to associate with another table.Is the field that determines the record of another table and is used to maintain data consistency.For example, if a field in table A is the primary key of table B, it can be the foreign key of table A.

In a table, the primary key can be either the primary key or the foreign key of the table, associating with other tables as follows:

 

mysql> create table course(
    -> id int not null,
    -> course_name char(64) not null,
    -> score int not null,
    -> primary key (id),
    -> constraint FK_id foreign key (id) references student(number));     #To create a coursetable that associates a student table with an id even though the primary key of the coursetable is also a foreign key, you must also require the id to be in the student table when you create it
Query OK, 0 rows affected (0.39 sec)

mysql> desc course;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(11)  | NO   | PRI | NULL    |       |
| course_name | char(64) | NO   |     | NULL    |       |
| score       | int(11)  | NO   |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into course (id,course_name,score) values (1,"Chinese",88);
Query OK, 1 row affected (0.12 sec)

mysql> insert into course (id,course_name,score) values (5,"Englist",60);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`oldboydb`.`course`, CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `student` (`number`))

 

A table has been created above to associate other tables, even if the primary key of the table is a foreign key.

MySQL NULL value processing

 

* We already know that MySQL uses the SQL SELECT command and the WHERE clause to read data from data tables, but when the query condition field provided is NULL, the command may not work properly.

MySQL provides three operators to handle this situation:

IS NULL: This operator returns true when the value of a column is NULL.

IS NOT NULL: The operator returns true when the column value is not NULL.

<=>: The comparison operator (unlike the = operator) returns true when the two values compared are NULL.

The conditional comparison operation on NULL is special.You cannot use= NULL or!= NULL to find NULL values in columns.

In MySQL, comparisons of NULL values to any other value (even NULL) always return false, that is, NULL = NULL returns false.

Processing NULL in MySQL uses IS NULL and IS NOT NULL operators.(

Mysql connection (left join, right join, inner join, full join)

 

We've learned that it's relatively easy to read data in one table, but in real applications it's often necessary to read data from multiple tables.

In this section we will show you how to query data in two or more tables using MySQL's JOIN.

You can use Mysql's JOIN to join multiple table queries in SELECT, UPDATE, and DELETE statements.

JOIN s are roughly divided into the following three categories according to their functions:

1.INNER JOIN (inner join, or equivalent join): Get records of field matching relationships in two tables;

2.LEFT JOIN: Get all records of the left table even if the right table does not have matching records;

3.RIGHT JOIN: Contrary to LEFT JOIN, it is used to get all records of the right table even if the left table does not have corresponding matching records.

Create two tables first, as follows:

 

mysql> select * from student;
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      3 | tom     |     36 | male     | 15111009430 |
|      4 | chunyun |     27 | male     | 15111009430 |
|      5 | marry   |     16 | female     | 15111009430 |
+--------+---------+--------+--------+-------------+
5 rows in set (0.00 sec)

mysql> select * from course;
+----+-------------+-------+
| id | course_name | score |
+----+-------------+-------+
|  1 | Chinese     |    88 |
|  2 | Englist     |    86 |
|  3 | math        |    77 |
|  7 | music       |    99 |
| 10 | natural     |   100 |
+----+-------------+-------+
5 rows in set (0.00 sec)

 

    Inner join ... on.....

 

mysql> select t2.id,t1.Full name,t1.Age,t2.score from student t1 inner join course t2 on t1.number = t2.id;   #The link is created between the two tables, Table 1 Links Table 2
+----+---------+--------+-------+
| id | Full name    | Age   | score |
+----+---------+--------+-------+
|  1 | alex    |     38 |    88 |
|  2 | wupeiqi |     28 |    86 |
|  3 | tom     |     36 |    77 |
+----+---------+--------+-------+
3 rows in set (0.04 sec)

 

(select t2.id,t1.Name, t1.Age, t2.score from student t1 inner join course t2 where t1.Number= t2.id, inner join is the information common to both tables of the link, as shown above.

This is simply showing the intersection of two tables

    Left join ...on...

A left join will give all rows in A, plus any common rows in B. (whichever table is on the left), line link;

 

Mysql> select t1.Number, t1.Name, age, score, gender from student t1 left join course t2 on t1.Number= t2.id; #Link data based on left table
+--------+---------+--------+-------+--------+
|Number|Name|Age|score|Gender|
+--------+---------+--------+-------+--------+
| 1 | Alex | 38 | 88 | female |
|      2 | wupeiqi |     28 |    86 |        |
| 3 | Tom | 36 | 77 | man |
| 4 | Chunyun | 27 | NULL | Male |
| 5 | marry | 16 | NULL | female |
+--------+---------+--------+-------+--------+
5 rows in set (0.00 sec)

 

The lift join...on...above links are based on the table on the left.

    Right join...on...

A right join will give all rows in B, plus any common rows in A. Take the table on the right as the standard. If the left side is not discarded, keep the right field as follows:

 

mysql> select id,Full name,Age,score,course_name,Gender from student t1 right join course t2 on t1.number=t2.id;    #right join...on...is based on the right table character
+----+---------+--------+-------+-------------+--------+
| id | Full name    | Age   | score | course_name | Gender   |
+----+---------+--------+-------+-------------+--------+
|  1 | alex    |     38 |    88 | Chinese     | female     |
|  2 | wupeiqi |     28 |    86 | Englist     |        |
|  3 | tom     |     36 |    77 | math        | male     |
|  7 | NULL    |   NULL |    99 | music       | NULL   |
| 10 | NULL    |   NULL |   100 | natural     | NULL   |
+----+---------+--------+-------+-------------+--------+
5 rows in set (0.00 sec)

 

The right join on is based on the table field on the right. If there is one on the left and one on the right is not deleted; if there is one on the right and one on the left, the value displayed by default is empty, leaving the field on the right.

    Full join...on...

    A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

 

mysql> select id,Full name,Age,score,course_name,Gender from student t1 full join course t2 on t1.number=t2.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'full join course t2 on t1.number=t2.id' at line 1

 

MySQL does not support full join...on...full link mode, MySQL does not support it.

mysql does not directly support full join,but it is always hard for us to:

mysql> select id,Full name,Age,score,course_name,Gender from student t1 right join course t2 on t1.number=t2.id union 
select id,Full name,Age,score,course_name,Gender from student t1 left join course t2 on t1.number=t2.id;
"""Full link format, using union Stitch the left and right links together. union Weight removal function, another stitching method,Stitch Down union and union all，Left and right stitching left join and right join"""
+------+---------+--------+-------+-------------+--------+
| id   | Full name    | Age   | score | course_name | Gender   |
+------+---------+--------+-------+-------------+--------+
|    1 | alex    |     38 |    88 | Chinese     | female     |
|    2 | wupeiqi |     28 |    86 | Englist     |        |
|    3 | tom     |     36 |    77 | math        | male     |
|    7 | NULL    |   NULL |    99 | music       | NULL   |
|   10 | NULL    |   NULL |   100 | natural     | NULL   |
| NULL | chunyun |     27 |  NULL | NULL        | male     |
| NULL | marry   |     16 |  NULL | NULL        | female     |
+------+---------+--------+-------+-------------+--------+
7 rows in set (0.21 sec)

6. Transactions

MySQL transactions are mainly used to process data with high workload and complexity.For example, in the personnel management system, if you delete a person, you need to delete the basic data of the person as well as the information related to the person, such as mailbox, articles, etc. so that these database operation statements constitute a transaction!

1. Transactions are supported only in databases or tables in MySQL that use the Innodb database engine;

2. Transaction can be used to maintain the integrity of the database and to ensure that batches of SQL statements are executed either completely or not at all.

3. Transactions are used to manage insert,update,delete statements.

In general, a transaction must satisfy four conditions (ACID): Atomicity, Consistency, Isolation, Durability

1. Atomicity of transactions: A set of transactions either succeeds or is withdrawn;

2. Stability: Transaction withdrawal with illegal data (foreign key constraints, etc.);

3. Isolation: Transactions run independently.The result of one transaction processing affects other transactions, and the other transactions are withdrawn.100% isolation of transactions at the expense of speed;

4. Reliability: After a software or hardware crash, InnoDB data table drivers will use log file refactoring to modify.Reliability and speed cannot be combined, and the innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.*

Use transactions in the Mysql console. (InnoDB supports transactional operations only)

1.mysql> begin;Start a transaction
 
2.mysql> insert into a (a) values(555);
 
3.mysql>rollback; rollback so the data will not be written

Transactional operations start a transaction, begin, then perform command operations, and rollbakc if you don't want to, as follows:

mysql> begin;                                                                           #Open a transaction
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student (number,Full name,Age,Gender) values (6,"geng",27,"male");                #Insert a piece of data
Query OK, 1 row affected (0.29 sec)

mysql> select * from student; 
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      3 | tom     |     36 | male     | 15111009430 |
|      4 | chunyun |     27 | male     | 15111009430 |
|      5 | marry   |     16 | female     | 15111009430 |
|      6 | geng    |     27 | male     | 15111009430 |
+--------+---------+--------+--------+-------------+
6 rows in set (0.00 sec)

mysql> rollback;                                                                         #Rollback, return to perform action
Query OK, 0 rows affected (0.22 sec)

mysql> select * from student;
+--------+---------+--------+--------+-------------+
| number   | Full name    | Age   | Gender   | Telephone        |
+--------+---------+--------+--------+-------------+
|      1 | alex    |     38 | female     | 15111252591 |
|      2 | wupeiqi |     28 |        | 15111252591 |
|      3 | tom     |     36 | male     | 15111009430 |
|      4 | chunyun |     27 | male     | 15111009430 |
|      5 | marry   |     16 | female     | 15111009430 |
+--------+---------+--------+--------+-------------+
5 rows in set (0.00 sec)

mysql> commit;                                                                           #If there is no problem with the transaction being executed, the transaction cannot be rolled back and ended using commin.
Query OK, 0 rows affected (0.00 sec)

Of course, if the above data is OK, enter commit to submit the command.

7. Index

MySQL index is very important for the efficient operation of MySQL. Indexing can greatly improve the speed of MySQL retrieval.(

For example, if a reasonably designed and indexed MySQL is a Lamborgini, then a human tricycle is a MySQL that does not design and use an index.

Index is a single column index and a combined index.Single-column indexes, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a composite index.A composite index, where a cable contains multiple columns.

When you create an index, you need to ensure that it is a condition (typically as a condition of a WHERE clause) that is applied to the SQL query statement.

Indexes are actually tables that hold primary keys and index fields and point to records in entity tables.

All of the above are about the benefits of using indexes, but too much use of indexes can lead to abuse.Indexes also have their drawbacks: although they greatly increase query speed, they also slow down table updates such as INSERT, UPDATE, and DELETE.When updating a table, MySQL not only saves the data, but also saves the index file.Index files that take up disk space for indexing.

There is only one primary key and there can be multiple indexes, so it is generally created as a primary key or as a composite primary key.Index is a hash form, first converted to numbers, then sorted, and then folded in half by a binary distribution or binary tree to improve the query speed. Making an index can provide efficiency. Hundreds of millions of data can be queried only a dozen times, if traversal is time consuming.

Common Index

Create Index

This is the most basic index, it has no restrictions.It is created in the following ways: 

CREATE INDEX indexName ON mytable(username(length));

 

Display how to create an index: show index from tablename;

 

mysql> show index from student;                                #Index in presentation table
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | number        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.48 sec)

 

Primary key in a table is index by default, and BTREE is binary tree. Binary tree method is used to create primary key index.

mysql> create index index_name on student (Full name(16));                         #Index the field name, create index indexname on tablename
Query OK, 0 rows affected (2.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | number        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | index_name |            1 | Full name        | A         |           5 |       16 | NULL   |      | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

In the case of CHAR, VARCHAR type, length can be less than the actual length of the field; in the case of BLOB and TEXT type, length must be specified

Modify table structure - create index

ALTER mytable ADD INDEX [indexName] ON (username(length))

Specify directly when creating tables

 

CREATE TABLE mytable( 
  
ID INT NOT NULL,  
  
username VARCHAR(16) NOT NULL, 
  
INDEX [indexName] (username(length)) 
  
);

 

* Delete the syntax of the index

 

DROP INDEX [indexName] ON mytable;                             mysql> drop index index_name on student;      #Method of Deleting Indexes from Tables

 

* Unique Index

This is similar to the previous normal index except that the values of the index column must be unique, but null values are allowed.If it is a composite index, the combination of column values must be unique.It is created in the following ways:

Create Index

 

Create Index
CREATE UNIQUE INDEX indexName ON mytable(username(length))
 
 
Modify table structure
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
 
 
Specify directly when creating tables
CREATE TABLE mytable( 
  
ID INT NOT NULL,  
  
username VARCHAR(16) NOT NULL, 
  
UNIQUE [indexName] (username(length)) 
  
);

 

Adding and deleting indexes using the ALTER command

 

There are four ways to add an index to a data table:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a primary key, which means the index value must be unique and cannot be NULL.
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): The value that this statement creates the index must be unique (NULL may occur multiple times except for NULL).
ALTER TABLE tbl_name ADD INDEX index_name (column_list): Add a normal index, and the index value can appear multiple times.
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This statement specifies that the index is FULLTEXT for full-text indexing.
 
 
The following example adds an index to a table.
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
You can also use the DROP clause in the ALTER command to delete the index.Try the following example to delete the index:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

 

Adding and deleting primary keys using the ALTER command

 

Primary keys can only work on one column. When adding a primary key index, you need to make sure that the primary key is not empty by default (NOT NULL).Examples are as follows:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
 
You can also delete the primary key using the ALTER command:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
You only need to specify PRIMARY KEY to delete the specification, but you must know the index name when deleting the index.

 

Display index information

 

mysql> SHOW INDEX FROM table_name\G

 

mysql exercise http://www.cnblogs.com/wupeiqi/articles/5729934.html

For more mysql knowledge, see http://www.cnblogs.com/wupeiqi/articles/5713323.html

Keywords: Python MySQL Database RPM SQL

Added by Acs on Wed, 22 May 2019 22:14:39 +0300

Popular Keywords

©2024 Programming VIP