MySQL programming on linux (2): Basic MySQL syntax operations, linuxmysql

MySQL programming on linux (2): Basic MySQL syntax operations, linuxmysql

[Copyright statement: respect originality. For reprinted content, Please retain the Source: blog.csdn.net/shallnet. this document is only intended for study and exchange purposes. Do not use it for commercial purposes]

Structured query language is a database query and programming language used to access data and query, update, and manage relational database systems. The SQL language consists of three parts: 1. The Data Definition Language (DDL) is used to define and manage objects, such as databases, data tables, and attempts. For example, create, drop, alter, and other statements. 2. Data Operation Language (DML), used to operate data in the database, such as select, insert, update, and delete. 3. Data Control Language (DCL), used to manage databases, including management permissions and data changes. For example, grant, revoke, commit, and rollback. Similar to advanced development languages, data in SQL also has different types. Different Data Types store different data. In SQL, data types are classified into five types: character type (storing a short string), text (character storing a large string), numeric (storing integers, real numbers, floating-point numbers), logical (I .e. Boolean, value 0 or 1), and date. Annotations can also be added to SQL statements. There are three types: "#" (similar to shell script annotations), "--", and C-like /*.... */multi-line comment. Database Operation: 1. create database: create database <database Name>. 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 a database: 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               |+--------------------+3 rows in set (0.00 sec)mysql> 
Table operations: a table is an important part of a database. A database usually consists 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. Each column includes the column name, data type, and other attributes, the row contains the records or data of a column. 1. create table <table Name> (<column Name> <data type> [<column-level integrity constraints>] [, <column Name> <data type> [<column-level integrity constraints>]... [, <Table-level integrity constraints>]) in actual use, only simple table creation languages are used, and only 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,    -> username char(32),    -> 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. If the table structure does not meet the requirements, use the alter table statement to modify the column attributes or even the table name. Modify the syntax as follows: alter table <table Name> [ADD <new COLUMN Name> <data type> [Integrity Constraint] [DROP <Integrity Constraint>] [alter column <COLUMN Name> <data type>]; for example, if the regusers table does not have the "Last Logon Time" attribute, you can modify the table to solve this problem. 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 a table. drop table <table Name>, <table Name>...; you can specify multiple tables and delete them at the same time. Record operations: the records in the database are the actual data information, and data storage is also reflected by data records. The SQL function is mainly reflected in the operations on these records. The premise for storing records is that you must have databases and data tables. you can insert, delete, and update a table. 1. insert a record and use the insert statement to insert a new data row to the specified data table. Syntax format: insert into <Table Name> [(<attribute column 1>, <belongs to the following 2>...)] values (<constant 1> [, <constant 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-12', '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 records. Use the update statement to update or modify records that meet the specified conditions. The update statement format is: update <Table Name> set <column name >=< expression> [, <column name >=< expression>]... [where <condition>]. For example, modify the records with the name of brooks in the regusers table to ben, as shown below:
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    | 1985-08-12 | 2014-01-17 |+--------+----------+------------+------------+3 rows in set (0.01 sec)mysql> 
5. Delete the record. Use the delete statement to delete records that meet the specified conditions in the data table. The syntax of the delete statement is: delete from <Table Name> [where <condition>]. For example, to delete a record whose userid is 10002 in a table is 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      | 1982-04-02 | 2014-04-30 |+--------+----------+------------+------------+2 rows in set (0.00 sec)mysql> 
Database query operations: SQL queries databases using select statements. Queries are the most frequently used operations on databases. Select can query several columns that meet the conditions in the table and sort the query results in a certain order.

Query the SQL statements of all records in the table tb_users 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 all users whose birthdate is less than 1983-1-1:

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 the date of birth, which is later than and sorted by the 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)

Only the user and the last logon time are displayed:

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)