Detailed explanation of common sql statements in MySQL database operation add, delete, change, query, comment, fuzzy query, multi table linkage, regular expression, etc

catalog

1. Single line comments in SQL statements

2. Multiline comment

3. Create a new database

4. Delete database

5. Create table

6. Delete data table in MySQL

7.MySQL insert data

8.MySQL query data

9.MySQL UPDATE records

10.MySQL DELETE statement delete records

11.MySQL LIKE clause fuzzy query

12.MySQL UNION operator union query

13.MySQL sorting

14.MySQL GROUP BY statement

15. Use multi table linkage query for MySQL connection

16.MySQL NULL value processing

17. REGEXP operator is used for regular expression matching in MySQL.

Two -- followed by a space

--Test notes

2. Multiline comment

/*
 multiline comment 
 gang
*/

3. Create a new database

CREATE DATABASE database name;

4. Delete database

Drop database < database name >;

5. Create table

Syntax:

CREATE TABLE table_name (column_name column_type);
CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Users of user table ID',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name of consignee',
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `sure_name` varchar(255) DEFAULT NULL,
  `postal_code` varchar(255) DEFAULT NULL COMMENT 'Postal Code',
  `country_code` varchar(10) DEFAULT NULL,
  `tel` varchar(30) NOT NULL DEFAULT '' COMMENT 'phone number',
  `province` varchar(255) NOT NULL COMMENT 'Province of administrative region table ID',
  `city` varchar(255) NOT NULL COMMENT 'City of administrative region table ID',
  `county` varchar(255) NOT NULL COMMENT 'Districts and counties in the list of administrative regions ID',
  `address_detail` varchar(255) NOT NULL DEFAULT '' COMMENT 'Detailed receiving address',
  `area_code` char(6) DEFAULT NULL COMMENT 'Area code',
  `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Default address or not',
  `add_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL COMMENT 'Update time',
  `deleted` tinyint(1) DEFAULT '0' COMMENT 'Logical deletion',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Receiving address table';

The above case shows:

  • If you don't want the field to be NULL, you can set the property of the field to NOT NULL. When you operate the database, if the data entered into the field is NULL, an error will be reported.
  • Auto increment defines the column as an auto increment attribute, which is generally used for the primary key. The value will be automatically increased by 1.
  • The PRIMARY KEY keyword is used to define columns as primary keys. You can use multiple columns to define the PRIMARY KEY, separated by commas.
  • ENGINE sets the storage ENGINE, and CHARSET sets the encoding.
  • COMMENT set COMMENT
  • int varchar datetime tinyint char is the database data type

6. Delete data table in MySQL

Syntax:

DROP TABLE table_name ;
-- delete
DROP TABLE `admin`;
-- Next, if this table exists, delete it
DROP TABLE IF EXISTS `address`;  

7.MySQL insert data

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
INSERT INTO `runoob_tbl` VALUES ('1', 'study PHP', 'Rookie tutorial', '2017-04-12'), ('2', 'study MySQL', 'Rookie tutorial', '2017-04-12'), ('3', 'study Java', 'RUNOOB.COM', '2015-05-01'), ('4', 'study Python', 'RUNOOB.COM', '2016-03-06'), ('5', 'study C', 'FK', '2017-04-05');

8.MySQL query data

The following is the general SELECT syntax for querying data in MySQL database:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
  • In the query statement, you can use one or more tables, which are separated by commas (,), and use the WHERE statement to set the query conditions.
  • The SELECT command can read one or more records.
  • You can use asterisk (*) instead of other fields, and the SELECT statement will return all field data of the table
  • You can use the WHERE statement to include any condition.
  • You can use the LIMIT property to set the number of records returned.
  • You can use OFFSET to specify the data OFFSET for the SELECT statement to start the query. The OFFSET is 0 by default.
-Query 
-- SELECT * FROM runoob_tbl;
-- SELECT * FROM runoob_tbl WHERE runoob_author='as';
--String comparison of WHERE clause in MySQL is case insensitive. You can use the BINARY keyword to set the string comparison of the WHERE clause to be case sensitive
-- SELECT * from runoob_tbl WHERE BINARY runoob_author='AS';

When paging the data, the offset is a multiple of the limit. Suppose that the display quantity of each page is 5, the data on the first page is 1-5, and the data on the second page is 1-5. Just set the offset to 5 and write a function in turn.

9.MySQL UPDATE records

The following is the general SQL syntax for the UPDATE command to modify MySQL data table data:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • You can update one or more fields at the same time.
  • You can specify any condition in the WHERE clause.
  • You can update data in a single table at the same time.
-- to update 
UPDATE runoob_tbl SET runoob_title='study BUKEKU' WHERE runoob_id=2;
-- The meaning of this sentence is runoob_id =2 When I change this runoob_title by'study BUKEKU'

10.MySQL DELETE statement delete records

The following is a general syntax for SQL DELETE statements to delete data from MySQL data tables:

DELETE FROM table_name [WHERE Clause]
  • If the WHERE clause is not specified, all records in the MySQL table will be deleted.

  • You can specify any condition in the WHERE clause
  • You can delete records in a single table at once.
-- delete
DELETE FROM runoob_tbl WHERE runoob_id=4;

11.MySQL LIKE clause fuzzy query

The following is a general syntax for SQL SELECT statements to read data from a data table using the LIKE clause:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
  • You can specify any condition in the WHERE clause.
  • You can use the LIKE clause in the WHERE clause.
  • You can use the LIKE clause instead of the equal sign =.
  • LIKE is usually used with%, similar to a metacharacter search.
  • You can use AND OR to specify one OR more conditions.
  • You can use the WHERE...LIKE clause in the DELETE or UPDATE commands to specify conditions.
/*
like Match / fuzzy match, will be used in combination with% and.

'%a'     //Data ending in a
'a%'     //Data starting with a
'%a%'    //Data with a
'_a_'    //Three digits and the middle letter is a
'_a'     //Two digit and ending with a
'a_'     //Two digit with a starting letter
*/

SELECT * from runoob_tbl  WHERE runoob_author LIKE 'a%';
-- Find the a At the beginning.

12.MySQL UNION operator union query

The MySQL UNION operator is used to connect the results of more than two SELECT statements into a result set. Multiple SELECT statements delete duplicate data.

grammar

MySQL UNION operator syntax format:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

parameter

  • Expression1, expression2,... Expression \ n: column to retrieve.

  • tables: the data table to retrieve.

  • WHERE conditions: optional, retrieve conditions.

  • DISTINCT: optional, delete duplicate data in result set. By default, the UNION operator has deleted duplicate data, so the DISTINCT modifier has no effect on the result.

  • ALL: optional, returns ALL result sets, including duplicate data.

user table:

user2 table:

SELECT name,age FROM user WHERE age>35
UNION
SELECT name,age FROM user2 WHERE age>40
ORDER BY age;

Results:

13.MySQL sorting

MySQL's ORDER BY clause sets which field you want to sort by, and then returns the search results.

The following is the SQL SELECT statement that uses the ORDER BY clause to sort the query data before returning the data:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][default ASC]], [field2...] [ASC [DESC][default ASC]]
  • You can use any field as the sorting condition to return the sorted query results.
  • You can set multiple fields to sort.
  • You can use the ASC or DESC keywords to set the query results in ascending or descending order. By default, it is arranged in ascending order.
  • You can add a WHERE...LIKE clause to set the condition.
-- sort  ASC Ascending order DESC Descending order
SELECT * FROM user WHERE age>19 ORDER BY age DESC;

14.MySQL GROUP BY statement

Now import the following sql to your own database

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Number of logins',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', 'Xiaoming', '2016-04-22 15:25:33', '1'), ('2', 'Xiao Wang', '2016-04-20 15:25:47', '3'), ('3', 'Xiaoli', '2016-04-19 15:26:02', '2'), ('4', 'Xiao Wang', '2016-04-07 15:26:14', '4'), ('5', 'Xiaoming', '2016-04-11 15:26:40', '4'), ('6', 'Xiaoming', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

GROUP BY syntax

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
 SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

COUNT, SUM

/*
We can use coalesce to set a name that can replace NUll. Coalesce syntax:

select coalesce(a,b,c);
Parameter Description: if a==null, select b; if b==null, select c; if a!=null, select a; if a b c is null, return null (meaningless).
*/
SELECT coalesce(name, 'total'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
/*
+--------------------------+--------------+
| coalesce(name, 'Total ') | sin| count|
+--------------------------+--------------+
| Xiaoli 2|
| Xiaoming 7|
| Xiao Wang 7|
| Total | 16|
+--------------------------+--------------+
*/

15. Use of MySQL connection union multi table query

MySQL JOIN queries data in two or more tables.

You can use Mysql JOIN in SELECT, UPDATE and DELETE statements to JOIN multiple table queries.

The JOIN can be roughly divided into the following three categories according to its functions:

  • INNER JOIN (INNER JOIN or equivalent join): gets the record of the matching relationship between the fields in the two tables.
  • LEFT JOIN: gets all records in the left table, even if the right table does not have corresponding matching records.
  • RIGHT JOIN: opposite to LEFT JOIN, it is used to get all records of the right table, even if the left table does not have corresponding matching records.

Execute the following sql file to generate 2 tables

/*
 Navicat MySQL Data Transfer

 Source Server         : 127.0.0.1
 Source Server Version : 50621
 Source Host           : localhost
 Source Database       : RUNOOB

 Target Server Version : 50621
 File Encoding         : utf-8

 Date: 04/13/2017 14:25:12 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `runoob_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `runoob_tbl`;
CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `runoob_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `runoob_tbl` VALUES ('1', 'study PHP', 'Rookie tutorial', '2017-04-12'), ('2', 'study MySQL', 'Rookie tutorial', '2017-04-12'), ('3', 'study Java', 'RUNOOB.COM', '2015-05-01'), ('4', 'study Python', 'RUNOOB.COM', '2016-03-06'), ('5', 'study C', 'FK', '2017-04-05');
COMMIT;

-- ----------------------------
--  Table structure for `tcount_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `tcount_tbl`;
CREATE TABLE `tcount_tbl` (
  `runoob_author` varchar(255) NOT NULL DEFAULT '',
  `runoob_count` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `tcount_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `tcount_tbl` VALUES ('Rookie tutorial', '10'), ('RUNOOB.COM ', '20'), ('Google', '22');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Structure of two tables

runoob_tbl

tcount_tbl

JOIN intersection

implement

SELECT a.runoob_id,a.runoob_title, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

result

LEFT JOIN

Take all of the left table, and the ones that do not correspond to the right are null

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | Rookie tutorial    | 10             |
| 2           | Rookie tutorial    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+

RIGHT JOIN

Will read all of the right table, and the left table has no null s

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | Rookie tutorial    | 10             |
| 2           | Rookie tutorial    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+

16.MySQL NULL value processing

The following two statements are not valid

SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL;

To deal with this situation, MySQL provides three operators:

  • IS NULL: this operator returns true when the value of the column IS NULL.
  • IS NOT NULL: the operator returns true when the value of the column IS NOT NULL.
  • < = > comparison operator (different from = operator), returns true when two values of comparison are equal or both are NULL.

Write like this

SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;

17. REGEXP operator is used for regular expression matching in MySQL.

-- Regular keywords REGEXP
SELECT * FROM runoob_new WHERE runoob_author REGEXP '^R';

Tags: MySQL Database SQL encoding

Posted on Sat, 16 May 2020 03:43:33 -0700 by terandle