Comprehensive Analysis of MYSQL Table 04/28 Update SLTechnology News&Howtos

Comprehensive Analysis of MYSQL Table

2025-04-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Shulou(Shulou.com)06/02 Report--

This article mainly introduces "Comprehensive Analysis of MYSQL". In daily operation, I believe many people have doubts about the comprehensive analysis of MYSQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "Comprehensive Analysis of MYSQL"! Next, please follow the editor to study!

1. Create Table 1.1.The basic syntax for creating tables CREATE TABLE tablename (column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints,... )

Column_name is the name of the column

Column_type is the data type of the column

Contraints is a constraint for this column

1.1.1. Create a simple table mysql > create table orders (ordername varchar (10), createtime date,ordermoney decimal (10), ordernumber int (2)); Query OK, 0 rows affected (0.23 sec) 1.1.2, view the creation table definition

Structured definition:

Table is defined in detail:

View the detailed table definition:

Mysql > show create table orders\ Gbomber * 1. Row * * Table: ordersCreate Table: CREATE TABLE `orders` (`ordername` varchar (10) DEFAULT NULL, `createtime` date DEFAULT NULL, `ordermoney` decimal (10pm 2) DEFAULT NULL, `ordernumber`int (2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec) ERROR:No query specified

From this you can see that the ENGINE (storage engine) of the table is InnoDB

The CHARSET character set is Latin1

The "\ G" option means that records can be arranged vertically by field, making it easier to display records with longer content.

2. Delete the table

Command:

DROP TABLE tablename

Delete orders:

Mysql > drop table orders->; Query OK, 0 rows affected (0.14 sec) 3, modify Table 3.1, modify Table Type commands ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

Example: modify the name field definition of table orders, change varchar (10) to varchar (20):

Example: change ordernumber to ordernumbers on table orders

Special note: both change and modify can modify the definition of the table, but the difference is that the column name needs to be written twice after change, which is inconvenient. But the advantage of change is that you can change column names, while modify cannot.

Add table field command ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]

Example: the new field username is added to the table orders, and the type is varchar (3):

Example: delete field username on table orders:

Example: the name of table orders is changed to goodsorders

Insert (insert), query (select), update (update), delete (delete)

Insert record command INSERT INTO tablename (field1,field2, … Fieldn) VALUES (value1,value2,... Valuesn)

Example: insert a record into the table goodsorders. The ordername is zhang,createtime: 2021-05-12. The ordermoney is 100.00mindermoney: 1.

Mysql > insert into goodsorders (ordername,createtime,ordermoney,ordernumbers) values ('zhang','2021-05-12 sec 100.000); Query OK, 1 row affected (0.03 sec)

It can also be omitted (field1,field2,... Fieldn) this part

Mysql > insert into goodsorders values ('zhang1','2021-05-12 recording FROM tablename 1001.00 WHERE CONDITION 11); Query OK, 1 row affected (0.05 sec) 4.2, View insert data Command 4.2.1, query all FROM tablename [WHERE CONDITION]

Example: view all inserted data in goodsorders

Mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | zhang | 2021 -05-12 | 100.00 | 1 | | zhang1 | 2021-05-12 | 1001.00 | 11 | +-+ 2 rows in set (1001.00 sec)

Where "*" means to select all the records.

4.2.2. Query does not repeat record command keyword distinct

Example: query non-repeating creation time (createtime) data in non-goodsorders

Mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | zhang | 2021 -03-11 | 50.00 | 1 | li | 2020-05-12 | 70.00 | 15 | li | 2020-03-12 | 70.00 | 15 | li | 2020-03-11 | 70.00 | 15 | li | 2021-03-11 | 70.00 | 15 | +- -+ 5 rows in set (0.00 sec) mysql > select distinct createtime from goodsorders +-+ | createtime | +-+ | 2021-03-11 | | 2020-05-12 | | 2020-03-12 | | 2020-03-11 | +-+ 4 rows in set (2020 sec)

From this we can see that a repeated piece of time data 2021-03-11 has been removed.

4.2.3. Multi-conditional query keywords

The condition after where is the'= 'comparison of a field, and you can also use >, =, select * from goodsorders where ordername='li'and createtime =' 2020-03-11' +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | li | 2020-03-11 | 70.00 | 15 | +-+ 1 row in set (0.00 sec) 4.2.4, Sort query named SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC | ASC] Field2 [DESC | ASC],. Fieldn [DESC | ASC]]

Example: sort and display the records in the goodsorders table according to the creation time

Mysql > select * from goodsorders order by createtime +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | li | 2020-03-11 | 70.00 | 15 | li | 2020-03-12 | 70.00 | 15 | | li | 2020-05-12 | 70.00 | 15 | | zhang | 2021-03-11 | 50.00 | 1 | li | 2021-03-11 | 70.00 | 15 | +- -+ 5 rows in set (0.01sec) 4.2.5, Show part Not all, command SELECT. [LIMIT offset_start,row_count]

Offset_start represents the initial offset of the record

Row_count indicates the number of rows displayed

For example, 1: displays the first three records sorted by createtiem in the goodsorders table:

Mysql > select * from goodsorders order by createtime limit 3 +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | li | 2020-03-11 | 70.00 | 15 | li | 2020-03-12 | 70.00 | 15 | li | 2020-05-12 | 70.00 | 15 | +-+ 3 rows in set (0.00 sec)

For example, 2: if you want to display 3 pieces of data in the goodsorders table sorted by createtiem, starting with the second record:

Mysql > select * from goodsorders order by createtime limit 2 +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | li | 2020-05-12 | 70.00 | 15 | zhang | 2021-03-11 | 50.00 | 1 | | li | 2021-03-11 | 70.00 | 15 | +-+ 3 rows In set (0.00 sec) 4.2.6, Statistical data Aggregate instruction SELECT [field1,field2,... Fieldn] fun_name FROM tablename [WHERE where_contition] [GROUP BY field1,field2,. Fieldn [WITH ROLLUP]] [HAVING where_contition]

Parameter description:

1. Fun_name represents the aggregate operation to be done, that is, the aggregate function. The common ones are sum (summation), count (*) (number of records), max (maximum), and min (minimum).

2. The GROUP BY keyword indicates the fields to be classified and aggregated. For example, to count the number of employees by department, the department should be written after group by.

3. WITH ROLLUP is an optional syntax, indicating whether to re-summarize the results after classification and aggregation.

4. The HAVING keyword means to filter the classified results conditionally.

Note: the difference between having and where is that having conditionally filters the aggregated results, while where filters the records before aggregation. If logic allows, we use where to filter records as far as possible, so that because the result set is reduced, the efficiency of aggregation will be greatly improved. Finally, according to logic, we will see whether having is used for re-filtering.

Example 1: the total number of records in the query statistics goodsorders table

Mysql > select count (1) from goodsorders;+-+ | count (1) | +-+ | 5 | +-+ 1 row in set (0.00 sec)

Example 2: on this basis, group statistics are carried out according to the date of creation (createtime).

Mysql > select createtime,count (1) from goodsorders group by createtime +-+-+ | createtime | count (1) | +-+-+ | 2020-03-11 | 1 | | 2020-03-12 | 1 | 2020-05-12 | 1 | | 2021-03-11 | 2 | +- -+ 4 rows in set (0.00 sec)

Example 3: on this basis, we should not only group statistics according to the date of creation (cretetime), but also calculate the total.

Mysql > select createtime,count (1) from goodsorders group by createtime with rollup +-+-+ | createtime | count (1) | +-+-+ | 2020-03-11 | 1 | | 2020-03-12 | 1 | 2020-05-12 | 1 | | 2021-03-11 | 2 | NULL | 5 | +- -- +-+ 5 rows in set (0.02 sec)

The most important line, the number shown by null, is the total.

Example 4: grouping statistics according to the date of creation (createtime), and the quantity is greater than 1

Mysql > select createtime,count (1) from goodsorders group by createtime having count (1) > 1 row in set + | createtime | count (1) | +-+-+ | 0-03-11 | 2 | +-+-+ 1 row in set (sec)

Example 5: query the total, minimum and maximum amount of the order amount (ordermoney) in the goodsorders table

Mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | zhang | 2021 -03-11 | 50.00 | 1 | li | 2020-05-12 | 70.00 | 15 | li | 2020-03-12 | 70.00 | 15 | li | 2020-03-11 | 70.00 | 15 | li | 2021-03-11 | 70.00 | 15 | +- -+ 5 rows in set (0.00 sec) mysql > select sum (ordermoney) Max (ordermoney), min (ordermoney) from goodsorders +-+ | sum (ordermoney) | max (ordermoney) | min (ordermoney) | +- -+ | 330.00 | 70.00 | 50.00 | +-+ 1 row in set (0.02 sec) 4.2.7, Table join

1. Left join: contains all the records in the left table or even no records matching it in the right table; key instruction: left join

2. Right join: contains all records in the right table or even no matching records in the left table; associated instruction: right join

Example 1: now we create another user table (member), use goodorders to connect to the left, and query the associated user table information

Mysql > select * from member;+-+-+ | id | membername | +-+-+ | 15 | zhang | | 1 | li | | 13 | liss | +-+-+ 3 rows in set (0.00 sec) mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | memberid | +- -+-+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | li | 2020-05-12 | 70.00 | 15 | 1 | li | 2020-03-12 | 70.00 | 15 | 1 | li | 2020-03-11 | 70. 00 | 15 | 3 | li | 2021-03-11 | 70.00 | 15 | 1 | +-+ 5 rows in set (0.00 sec) mysql > select * from Goodsorders left join member on goodsorders.memberid = member.id +-+ | ordername | createtime | ordermoney | ordernumbers | memberid | id | membername | +-- -+-+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | 15 | zhang | | li | 2020-05-12 | 70.00 | 15 | 1 | 1 | li | | li | 2020-03-12 | 70.00 | 15 | 1 | 1 | li | li | 2021-03-11 | 70.00 | 15 | 1 | li | li | 2020-03-11 | 70.00 | 15 | 3 | | | NULL | NULL | +-+ 5 rows in set (0.00 sec) |

For example, the data in 2:member and goodsorders remains the same, so let's take a look at the query connected to the right and the result:

Mysql > select * from goodsorders right join member on goodsorders.memberid = member.id +-+ | ordername | createtime | ordermoney | ordernumbers | memberid | id | membername | +-- -+-+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | 15 | zhang | | li | 2020-05-12 | 70.00 | 15 | 1 | 1 | li | | li | 2020-03-12 | 70.00 | 15 | 1 | 1 | li | | li | 2021-03-11 | 70.00 | 15 | 1 | li | | NULL | 13 | Liss | +-+ 5 rows in set (0.00 sec)

A flip occurs here so that a piece of data in the left goodsorders table is empty.

4.2.8, subquery, related keywords

Mainly include in, not in, =,! =, exists, not exists and so on.

Example: query all users' records in the goodsorders table from the memeber table

Mysql > select * from member;+-+-+ | id | membername | +-+-+ | 15 | zhang | | 1 | li | | 13 | liss | +-+-+ 3 rows in set (0.00 sec) mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | memberid | +- -+-+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | li | 2020-05-12 | 70.00 | 15 | 1 | li | 2020-03-12 | 70.00 | 15 | 1 | li | 2020-03-11 | 70. 00 | 15 | 3 | li | 2021-03-11 | 70.00 | 15 | 1 | +-+ 5 rows in set (0.00 sec) mysql > select * from goodsorders where memberid in (select id from member) +-+ | ordername | createtime | ordermoney | ordernumbers | memberid | +- -+-+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | li | 2020-05-12 | 70.00 | 15 | 1 | li | 2020-03-12 | 70.00 | 15 | 1 | li | 2021-03-11 | 70. 00 | 15 | 1 | +-+ 4 rows in set (0.05sec) 4.2.9, Record association Instruction SELECT * FROM t1UNION | UNION ALLSELECT * FROM T2. UNION | UNION ALLSELECT * FROM tn

The main differences between UNION and UNION ALL:

UNION ALL is to merge result sets directly together.

UNION is the result of DISTINCT after UNION ALL and removal of duplicate records.

Example 1: display the collection of user numbers id (memberid) in the member table and the goodsorders table

Mysql > select memberid from goodsorders union all select id from member;+-+ | memberid | +-+ | 15 | 1 | 1 | 3 | 1 | 15 | 1 | 13 | +-+ 8 rows in set (0.00 sec)

Example 2: if you want the above result to be displayed after removing the duplicate record

Mysql > select memberid from goodsorders union select id from member;+-+ | memberid | +-+ | 15 | 1 | 3 | 13 | +-+ 4 rows in set (0.00 sec) 4.3, update record command UPDATE tablename SET field1=value1,field2.=value2, … Fieldn=valuen [WHERE CONDITION]

Example: change the order amount (ordermoney) with ordername of zhang in table goodsorders to 50

Mysql > update goodsorders set ordermoney=50.00 where ordername='zhang';Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from goodsorders +-+ | ordername | createtime | ordermoney | ordernumbers | +-+ | zhang | 2021 -05-12 | 50.00 | 1 | | zhang1 | 2021-05-12 | 1001.00 | 11 | +-+ 2 rows in set (0.00 sec)

When updating, if you encounter error code 1175:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences

Solution:

1. Query the advanced row body:

Show variables like 'SQL_SAFE_UPDATES'

2. Execute the following sql to turn off safe-updates mode:

SET SQL_SAFE_UPDATES = 0

SET SQL_SAFE_UPDATES = false;4.4, delete record named DELETE FROM tablename [WHERE CONDITION]

Example: delete all records in table goodsorders whose ordername is zhang1

Example: empty all the data in the table

Mysql > select * from varc;+-+-+ | v | c | +-+ | abc | abc | +-+-+ 1 row in set (0.03 sec) mysql > truncate table varc;Query OK, 0 rows affected (0.25 sec) mysql > select * from varc;Empty set (0.00 sec) 5, DCL statement

The DCL statement is mainly used to manage the permissions of operands in the database system.

5.1 create a database user

Example: create a database user user1 with an initial password of 123 and have SELECT/INSERT permissions on all tables in the ordermanage database:

Mysql > grant select,insert on ordermanage.* to 'user1'@'localhost' identified by' 123 question OK, 0 rows affected, 1 warning (0.06 sec) mysql > exitByeC:\ Program Files\ MySQL\ MySQL Server 5.7\ bin > mysql-uuser1-p123mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 82Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show databases;+-+ | Database | +-+ | information_schema | | ordermanage | +-+ 2 rows in set (0.00 sec)

On this basis, the insert permission of this user (user1) is withdrawn.

Mysql > revoke insert on ordermanage.* from 'user1'@'localhost';Query OK, 0 rows affected (0.02 sec) mysql > exitByeC:\ Program Files\ MySQL\ MySQL Server 5.7\ bin > mysql-uuser1-p123mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 84Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > use ordermanage;Database changedmysql > insert into member values ('11th); ERROR 1142 (42000): INSERT command denied to user' user1'@'localhost' for table 'member'mysql >

From this, we can see that the insert permission is insufficient and the insertion failed.

At this point, the study of "Comprehensive Analysis of MYSQL Table" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.