MySQL basic: MySQL data operation

Posted by juancuca on Sun, 23 Feb 2020 08:59:29 +0100

Catalog

Add record

# grammar
INSERT [INTO] tbl_name[(col_name,....)] VALUE|VALUES(values...);

# Do not specify field name
INSERT INTO tbl_name VALUE(value....);

# List specified fields
INSERT INTO tbl_name(Field name....) VALUE(value....);

# Insert.... set form
INSERT INTO tbl_name SET Field name=value,....;

# Insert.... select form
INSERT INTO tbl_name[(Field name....,)] SELECT [(Field name....,)] FROM tbl_name [WHERE condition];

# Add multiple records at a time
INSERT INTO tbl_name (Field name,....) VALUES(value....),(value....),....;

Update record

#If you do not specify a condition, the records of the entire table will be updated
 UPDATE tbl_name SET field name = value, field name = value,... [WHERE condition];

Delete record

# If no condition is specified, the records of the whole table will be deleted
# DELETE does not reset the value of auto increment when clearing the data table
DELETE FROM tnl_name [WHERE condition]; 

# Clear the data table completely, clear the data table and reset the value of auto increment
TRUNCATE TABLE tbl_name;

Query record

# grammar
SELECT select_exxpr,.... FROM tbl_name 
   [WHEREcondition] 
   [GROUP BY {col_name|position} HAVING Two screening] 
   [ORDER BY {col_name|position|expr}[ASC|DESC]] 
   [LIMIT Limit the display number of result sets] 

# Query all records
 SELECT * FROM tbl_name;  

# Information of the specified field in the query table (alias generally should not exceed three letters)
SELECT Field name AS alias,.... FROM tbl_name AS alias;

WHERE condition, select the qualified records

  • Comparison operators: > > = < < =! = < > and < = > can detect NULL values;
  • IS [NOT] NULL: check whether the value is NULL;
  • Specified range: [not] between;
  • Specify set: [not] in (value );
  • Logical operators: AND (logical AND) | OR (logical OR);
  • Match operator: [NOT] LIKE
    %: any string
    _: any character

GROUP BY

  • Divide the same value into the same group, and only one record in the group will be displayed in the final query result;

  • Group with group ABCD concat() can view the details of a field in a group;
    SELECT GROUP_CONCAT(col_name) FROM tbl_name GROUP BY col_name1;

  • Use with aggregate functions

  1. COUNT(): the total number of records in the statistics table;
    If the value in count (col name) field is NULL, it will not be counted. Write COUNT(*) to count NULL;
  2. MAX(): find the maximum value;
  3. MIN(): minimum value;
  4. AVG(): average value;
  5. WITH ROLLUP: a record will be added at the end of the record, which is the sum of all the records above.

multi-table query

#Internal connection form
 SELECT field name,.... from TBL ﹣ name1 inner join TBL ﹣ Name2 on condition;  

#Left outer connection form
 SELECT field name,.... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON condition; 
(display all records in the left table first, and then go to the right table to query the records that meet the conditions. If not, use NULL instead.)  

#Right outer connection form
 SELECT field name,.... FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON condition;
(display all the records in the right table first, and then go to the left table to query the records that meet the conditions. If not, use NULL instead.)

Create foreign keys

Note: only InnoDB storage engine supports foreign keys.

#Specify foreign key when creating table
 #The types of foreign key fields in the sub table and the main table fields should be similar; if they are numeric, they should be consistent and unsigned; if they are character, they should be consistent in types and lengths.
#If the foreign key field does not create an index, MySQL will automatically help us add the index.
#The foreign key Association of the child table must be the primary key of the primary table.
[CONSTRAINT foreign key name] foreign key (field name) REFERENCES main table (field name);

#Reference operation of foreign key constraint
 {DELETE | UPDATE} CASCADE: cascade operation, delete or update records from the main table, and then delete or update records from the sub table.
SET NULL: 
NO ACTION | RESTRICT: refuse to update or delete the primary table.

#Add foreign key dynamically  
ALTER TABLE tbl_name ADD [CONSTRAINT foreign key name] foreign key REFERENCE main table (primary key field);

Note: the records in the table before dynamic adding of foreign keys must be legal records without dirty data, otherwise, the foreign key cannot be added successfully.
  
#Dynamically delete foreign key
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;

ANY SOME ALL

Joint query

# UNION 
SELECT field name,.... from TBL [name1 union SELECT field name,... From TBL [Name2]

# UNION ALL
 SELECT field name,.... from TBL \ name1 union all SELECT field name,... From TBL \ Name2

Note: UNION ALL is a simple merge. UNION will remove duplicate records from the table

Common mathematical functions of MySQL

# CEIL() is rounded; 
SELECT CEIL(2.3) => 3

# FLOOR() rounded down; 
SELECT FLOOR(2.3) => 2

# ROUND() rounded; 
SELECT ROUND(2.456,2) => 2.46 (Retain 2 Potential decimal)

# TRUNCATE() takes several decimal places; 
SELECT TRUNCATE(3.4567,2) => 3.45

# MOD() takes the remainder; 
SELECT MOD(5,2) => 1

# ABS() takes absolute value; 
SELECT ABS(-1) => 1

# POWER() power operation; 
SELECT POWER(2,3) => 8

# PI() PI; 
SELECT PI() => 3.141593

# Random number between RAND() or RAND(X) 0~1; 
SELECT RAND(1) => among 1 Yes, the same value is the same

# SIGN(X) gets the sign of the number; 
SELECT SIGN(-12) => -1 (Return when value is negative -1，Positive return 1，0 Return 0)

# EXP() calculates the x power of e; 
SELECT EXP(2) => E^2

Common string functions of MySQL

# CHAR_LENGTH() gets the number of characters in the string; 
SELECT CHAR_LENGTH('abc') => 3; 
SELECT CHAR_LENGTH('Hello') => 3;

# LENGTH() gets the length of the string;
SELECT LENGTH('Hello') => 6; A Chinese character in 3 Byte ( utf-8)

# CONCAT(s1,s2,....) merges strings into one string 
SELECT CONCAT('A','B','C'); => ABC

# CONCAT_WS(x,s1,s2,....) to specify a separator to splice strings
SELECT CONCAT_WS('-','A','B','C'); => A-B-C
//Note: if the concatenated string contains NULL，Splicing result return NULL

UPPER() | UCASE() Convert string to uppercase
LOWER() | LCASE() Convert string to lowercase

# REVERSE() reverses the string
SELECT REVERSE('ABC'); => CBA 

# LEFT() | RIGHT() returns the first or last characters of a string
SELECT LEFT('hello',2); => he
SELECT RIGHT('hello',3); => llo

# LPAD() | RPAD() fills the string left or right to the specified length
SELECT LPAD('ABC',5,'?'); => ??ABC Left fill 5 position
SELECT RPAD('ABC',5,'?'); => ABC?? Right fill to 5 position

# TRIM() | LTRIM() | RTRIM() remove spaces
SELECT CONCAT('*',TRIM(' ABC '),'*'),CONCAT('*',LTRIM(' ABC '),'*'),CONCAT('*',RTRIM(' ABC '),'*');

# REPEAT() the specified number of times to repeat a string
SELECT REPEAT('HELLO',3); => HELLOHELLOHELLO

# REPLACE() replaces the specified string
SELECT REPLACE('hello king','king','queen'); => hello queen

# SUBSTRING() intercepts a string (starting at 1)
SELECT SUBSTRING('abcdt',1,3); => abc

# STRCMP() compare string
SELECT STRCMP('B','A'); => 1

Date functions commonly used in MySQL

# Curdate(), the current date() returned
SELECT CURDATE(),CURRENT_DATE(); => 2018-12-02

# Curtime() returns the current time
SELECT CURTIME(),CURRENT_TIME(); => 21:16:56

# Now(), current'timestamp(), sysdate() returns the current date and time
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(); => 2018-12-02 21:19:20

# MONTH(), MONTHNAME() return the name of the month and month in the date
SELECT MONTH('2012-02-19'),MONTHNAME('2012-02-19'); => 2,February

# DAYNAME() returns the day of the week
SELECT DAYNAME('2012-02-19'); => Thursday

# DAYOFWEEK() returns the first day of the week
SELECT DAYOFWEEK('2012-02-19'); => 1

# WEEK() returns the week of the year
SELECT WEEK('2012-02-19'); => 8

# YEAR(),DAY(),HOUR(),MINUTE(),SECOND()
//Return to year, day, hour, minute and second

# DATEDIFF() calculates the number of days between two dates
SELECT DATEDIFF('2012-09-12','2012-09-18'); => -6

Other common functions

# Return version
VERSION() 

# Return the number of connections
CONNECTION_ID() 

# Return to current login user
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER() 

# Returns the value of auto increment generated by the previous insert operation
LAST_INSERT_ID() 

#  md5 encryption, 32-bit results
MD5(s)

# PASSWORD() encryption algorithm
SELECT * FROM mysql.user WHERE user='root'\G;
89 original articles published, 80 praised, 170000 visitors+
Private letter follow

Topics: MySQL

Hot Topics

©2024 Programmer Think