MySQL programming on Linux (ii): MySQL syntax base operation

"Copyright Notice: respect for the original, reproduced please retain the source: blog.csdn.net/shallnet, the article only for learning Exchange, do not use for commercial purposes"

SQL (Structured Query language) is a database query and programming language for accessing data and querying, updating, and managing relational database systems. The SQL language contains 3 parts: 1. Data definition Language (DDL), which defines and manages objects such as databases, data tables, and attempts. For example, create, drop, alter, and so on. 2. Data manipulation Language (DML), which is used to manipulate data in the database, such as SELECT, INSERT, UPDATE, Delete, and so on. 3. Data Control Language (DCL) for managing databases, including administrative permissions and data changes. such as Grant, REVOKE, commit, rollback, and so on. Similar to the Advanced development language, the data in SQL has different types, different data types hold different data, there are 5 types of data in sql: Character type (storing a short string), text type (storing large string of characters), numeric type (storing integer, real number, floating point), logical type (Boolean type, value 0 or 1), date type. Comments can also be added to the SQL statement in the following three ways: "#" (Comments like shell script), "--", and C-like/*....*/can be commented in multiple lines. Database operations: 1. Creating databases: Create Database < database name >. For example: Create DATABASE db_users;
mysql> CREATE DATABASE db_users; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | db_users           | | mysql              | | Test               |+--------------- -----+5 rows in Set (0.00 sec)
2. Delete databases: Drop database < database name >. For example:
mysql> drop Database db_users; Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | test               
Table operations: Tables are an important part of a database, and a database is usually made up of one or more data tables. All data or information in the database is stored in these data tables. Each table name is unique in the database, and the table consists of rows and columns, and each column column includes the column name, data type, and other properties, while rows contain records or data for a column. 1. Creating Tables: Create table < table names > (< column names > < data types > [< column-Level integrity constraints >][,< column names > < data types > [< column-Level integrity constraints ]] ... [,< table-level integrity constraints >]) In practice, only the simpler table creation language is used, only the surface and field definitions are required. For example:
mysql> use Db_users;database changedmysql> CREATE TABLE regusers (userid int auto_increment NOT NULL primary key,-&G T Username char (+), birth date); Query OK, 0 rows affected (0.01 sec) mysql> describe regusers;+----------+----------+------+-----+---------+-------- --------+| Field | Type | Null | Key | Default | Extra |+----------+----------+------+-----+---------+----------------+| UserID | Int (11) | NO | PRI | NULL | auto_increment | | Username | char (32) |     YES | |                NULL | || Birth | Date |     YES | |                NULL | |+----------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec) 
2. Modify the table, use the ALTER TABLE statement to modify the column properties, or even modify the table name when the table structure is found to not meet the requirements. Modify the syntax as follows: ALTER TABLE < table name >[add < new column name > < data type > [integrity constraint]][drop < integrity constraint >][alter column < name > < Data type >]; For example, there is no "last logon Time" attribute in the original Regusers table, you can resolve the problem by modifying the table. As follows:
mysql> ALTER TABLE regusers add Last_login date; Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0mysql> describe regusers;+------------+------ ----+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+----------+------+-----+---------+----------------+| UserID | Int (11) | NO | PRI | NULL | auto_increment | | Username | char (32) |     YES | |                NULL | || Birth | Date |     YES | |                NULL | || Last_login | Date |     YES | |                NULL | |+------------+----------+------+-----+---------+----------------+4 rows in Set (0.00 sec) Mysql> 
3. Delete tables, drop table < table name >,< table name ..... ; You can specify multiple tables and delete them at the same time. Record operation: The database record is the actual data information, the data storage is also through the data record to embody. The functions of SQL are mainly embodied in the operation of these records. The prerequisite for storing records is that you must have a database and a data table, which you can insert, delete, and update after you have a table. 1. Insert a record and insert a new row of data into the specified data table using the INSERT statement. The syntax format is as follows: INSERT into < table name > [(< attribute column 1>,< belongs to the following 2> ...)] VALUES (< constants 1> [,< constants 2>].); For example:
mysql> INSERT INTO Regusers (userid, username, birth, last_login) VALUES (10000, ' Allen ', ' 1981-1-1 ', ' 2014-2-2 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into regusers (username, birth, last_login) VALUES (' Brooks ', ' 1982-4- 2 ', ' 2014-4-30 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into regusers (username, birth, last_login) VALUES (' Curry ', ' 1985-8-1 2 ', ' 2014-1-17 '); Query OK, 1 row Affected (0.00 sec) mysql> Select * from regusers;+--------+----------+------------+------------+| UserID | Username | Birth |  Last_login |+--------+----------+------------+------------+| 10000 | Allen | 1981-01-01 |  2014-02-02 | | 10001 | Brooks | 1982-04-02 |  2014-04-30 | | 10002 | Curry | 1985-08-12 | 2014-01-17 |+--------+----------+------------+------------+3 rows in Set (0.00 sec) Mysql> 
4. Update the record and use the UPDATE statement to update or modify the records that meet the specified criteria. UPDATE statement format: Update < table name > set < column name >=< expression > [,< column name >=< expression;] ... [where < conditions >]; For example, the record of a record named Brooks in the Regusers table is modified to Ben, as follows:
mysql> Update regusers set username= ' Ben ' where username= ' Brooks '; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0mysql> select * from regusers;+------- -+----------+------------+------------+| UserID | Username | Birth      | Last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 | |  10001 | Ben      | 1982-04-02 | 2014-04-30 | |  10002 | Curry    
5. Delete the record. Use the DELETE statement to delete records in the data table that meet the specified criteria. The syntax for the DELETE statement is: Delete from < table name > [where < Condition >]; For example, delete the record with UserID 10002 in the table as follows:
Mysql> Delete from regusers where userid=10002; Query OK, 1 row affected (0.02 sec) mysql> select * from regusers;+--------+----------+------------+------------+| UserID | Username | Birth      | Last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 | |  10001 | Ben      
Database query operations: SQL uses the SELECT statement to implement a database query. Queries are the most frequently used operations of a database. Select can query the table for a number of columns that meet the criteria, and the query results can be sorted in a certain order.

Query table Tb_users all the records in SQL are as follows:

Mysql> SELECT * from tb_users;+--------+----------+------------+------------+| UserID | Username | Birth      | Last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 | |  10001 | Ben      | 1982-04-02 | 2014-04-30 | |  10002 | Curry    | 1985-08-12 | 2014-01-17 |+--------+----------+------------+------------+3 rows in Set (0.00 sec)

Query for all users who are less than 1983-1-1 in date of birth:

Mysql> SELECT * from tb_users where birth < ' 1983-1-1 '; +--------+----------+------------+------------+| UserID | Username | Birth      | Last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 | |  10001 | Ben      | 1982-04-02 | 2014-04-30 |+--------+----------+------------+------------+2 rows in Set (0.00 sec)

Query date of birth is greater than 1982-1-1 and is sorted by last logon time:

Mysql> SELECT * from tb_users where birth > ' 1982-1-1 ' ORDER by last_login;+--------+----------+------------+------ ------+| UserID | Username | Birth      | Last_login |+--------+----------+------------+------------+|  10002 | Curry |    1985-08-12 | 2014-01-17 | |  10001 | Ben      | 1982-04-02 | 2014-04-30 |+--------+----------+------------+------------+2 rows in Set (0.00 sec)

Show only users and their last logon times:

Mysql> Select Username,last_login from tb_users;+----------+------------+| Username | Last_login |+----------+------------+| Allen |    2014-02-02 | | ben      | 2014-04-30 | | Curry    | 2014-01-17 |+----------+------------+3 rows in Set (0.00 sec )

MySQL programming on Linux (ii): MySQL syntax base operation