1, Introduction to SQL language
SQL statements are used to maintain and manage the database, including data query, data update, access control, object management and other functions.
SQL language classification
- DDL: data definition language, which is used to create database objects, such as libraries, tables, indexes, etc.
- DML: data manipulation language, which is used to manage the data in the table and to insert, delete and modify the data in the database.
- DQL: data query language, which is used to find qualified data records from data tables.
- DCL: data control language, which is used to set or change database user or role permissions (data control statements, which are used to control the direct permission and access levels of different data segments. These statements define the access permissions and security levels of databases, tables, fields and users, such as COMMIT, ROLLBACK, GRANT, REVOKE...)
2, DDL statement
Data definition language is used to create database objects, such as libraries, tables, indexes, etc
Common commands:
create #establish drop #delete alter #modify
1. Create a new database
CREATE DATABASE <Database name>;
2. Create a new table
- create table name (field 1 data type, field 2 data type, [...], primary key);
- Primary keys generally select fields that can represent uniqueness. NULL values are not allowed. A table can only have one primary key
CREATE TABLE web_demo (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id)); # NOT NULL Null value is not allowed # name char(10) The maximum length of the name is 10 digits # decimal(5,2) The integer part has a maximum of five digits and two decimal places # DEFAULT '' The default value of password is blank # PRIMARY KEY The primary key generally selects fields that have no duplicates and are not null
3. Delete table
drop table <Database name>.<Table name>; #The database name is not required to enter the database
4. Delete database
drop database <Database name>;
3, DML statement
Data manipulation language is used to manage the data in the table, insert, delete and modify the data in the database
Common commands:
insert #insert update #Modify, update delete #delete
1. Insert data into the table
Format:
INSERT INTO Table name(Field 1,Field 2,...) VALUES(Value of field 1, value of field 2,...); INSERT INTO Table name VALUES(Value of field 1, value of field 2,...);
Example:
insert into web_demo (id,name,score,passwd) values(1,'zhangsan',70,PASSWORD('123456'));
insert into web_demo values(2,'lisi',85.5,PASSWORD('123456'));
------------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| game |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use game;
Database changed
mysql> show tables;
+----------------+
| Tables_in_game |
+----------------+
| web_demo |
+----------------+
1 row in set (0.00 sec)
mysql> select * from web_demo;
Empty set (0.00 sec)
mysql> insert into web_demo (id,name,score,passwd) values(1,'zhangsan',70,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from web_demo;
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 70.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> desc web_demo;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| passwd | char(48) | YES | | | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into web_demo values(2,'lisi',85.5,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from web_demo;
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 70.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | lisi | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)
PS:
select * from <tables>; #View all information in the table desc <tables>; #View the table structure attribute, the abbreviation of describe The password string is displayed in encrypted form, if not used PASSWORD()，Display in clear text during query desc Display field meaning: -------- Field: Field name type: data type Null: Allow null Key: Primary key Default: Default value Extra: Extended attributes (for example: Flag column (increment)/Step size) 1 (2)
2. Modify and update the data records in the data table
Format:
UPDATE Table name SET Field name 1=Field value 1[,Field name 2=Field value 2] [WHERE Conditional expression];
Example:
update web_demo set passwd=PASSWORD('') where name='zhangsan';
update web_demo set name='lisi',passwd='' where id=2;
-------------------------------
mysql> update web_demo set passwd=PASSWORD('') where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update web_demo set name='lisi',passwd='' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from web_demo;
+----+----------+-------+--------+
| id | name | score | passwd |
+----+----------+-------+--------+
| 1 | zhangsan | 70.00 | |
| 2 | lisi | 85.50 | |
+----+----------+-------+--------+
2 rows in set (0.00 sec)
3. Delete the specified data record in the data table
Format:
DELETE FROM Table name [WHERE Conditional expression];
Example:
delete from web_demo where id=2; ------------------ mysql> select * from web_demo; +----+----------+-------+--------+ | id | name | score | passwd | +----+----------+-------+--------+ | 1 | zhangsan | 70.00 | | | 2 | lisi | 85.50 | | +----+----------+-------+--------+ 2 rows in set (0.00 sec) mysql> delete from web_demo where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from web_demo; +----+----------+-------+--------+ | id | name | score | passwd | +----+----------+-------+--------+ | 1 | zhangsan | 70.00 | | +----+----------+-------+--------+ 1 row in set (0.00 sec)
4, DQL statement
Used to find qualified data records from the data table
Common commands:
select
Format:
SELECT Field name 1,Field name 2,[...] FROM Table name[WHERE Conditional expression]; --------------------------- select * from web_demo; select id,name,score from web_demo where id=2; select name from web_demo\G #Display vertically as a list select * from web_demo limit 2; #Only the first 2 rows are displayed select * from web_demo limit 2,3; #Displays the first 3 lines after line 2
Example:
mysql> select * from web_demo; +----+----------+-------+-------------------------------------------+ | id | name | score | passwd | +----+----------+-------+-------------------------------------------+ | 1 | zhangsan | 70.00 | | | 2 | lisi | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 3 | wangwu | 86.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 4 | xiaohong | 65.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 5 | xiaobai | 70.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 6 | xiaolan | 99.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +----+----------+-------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> select id,name,score from web_demo where id=2; +----+------+-------+ | id | name | score | +----+------+-------+ | 2 | lisi | 85.50 | +----+------+-------+ 1 row in set (0.00 sec) mysql> select name from web_demo\G; *************************** 1. row *************************** name: zhangsan *************************** 2. row *************************** name: lisi *************************** 3. row *************************** name: wangwu *************************** 4. row *************************** name: xiaohong *************************** 5. row *************************** name: xiaobai *************************** 6. row *************************** name: xiaolan 6 rows in set (0.00 sec) ERROR: No query specified mysql> select * from web_demo limit 2; +----+----------+-------+-------------------------------------------+ | id | name | score | passwd | +----+----------+-------+-------------------------------------------+ | 1 | zhangsan | 70.00 | | | 2 | lisi | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +----+----------+-------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from web_demo limit 2,3; +----+----------+-------+-------------------------------------------+ | id | name | score | passwd | +----+----------+-------+-------------------------------------------+ | 3 | wangwu | 86.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 4 | xiaohong | 65.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 5 | xiaobai | 70.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +----+----------+-------+-------------------------------------------+ 3 rows in set (0.00 sec)