Of Mysql database management -- SQL statement

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)

5, DCL statement

Tags: Operation & Maintenance Database MySQL SQL cloud computing

Posted on Fri, 22 Oct 2021 03:09:59 -0400 by Anidazen