MySQL Technical insider's routine use

MySQL: Terminating the statement method:

1. At the end of the statement, enter a semicolon (;) to indicate the end of the statement

2. Using the \g (meaning go)

\G Displays the results in a vertical fashion, one value per line

Database: A database that contains tables, inserts, retrieves, modifies, or deletes data from a table

Creating a new database: Create database sampdb; (starting from a small letter here is the name of the databases you fetch)

Select the current database and set it as the default database: ①use sampdb; ②select DATABASE ();

Creating tables: Create TABLE Tbl_name (colum_spaces);

The data type Varchar:varchar (n) indicates that the column can hold a variable-length string, but its length has an upper limit and the maximum character is n characters

Auto_increment: Automatically generate index numbers

PRIMARY KEY (member_id): Indicates that the MEMBER_ID column creates an index, requiring that all values in the column must be unique to speed up the lookup of the table

ENUM (' F ', ' M '); Limit the possible values of a table column to a limited set

When a table is created, the engine is added at the end of it, indicating that the name is specified for the storage engines that should be used by MySQL (the storage engine is the processor used to manage some kind of table), such as: Engine=innodb;

Insert a row value into the table: INSERT into tbl_name values (valu1,value2 ...); Eg:insert into student VALUES (' Lymle ', ' M ', NULL);

You can also insert multiple rows at one time in a table: Eg:insert into student VALUES (' Avery ', ' F ', null), (' Nathan ', ' M ', null);

Created rows, specifying a few columns to assign the initial value: Format: INSERT into Tbl_name (col_name1,col_name2 ...)   VALUES (Value2,value2 ...); Eg:insert into member (Last_name,first_name) VALUES (' Stein ', ' Waddo ');

You can insert more than one value value list at a time: INSERT into student (Name,sex) VALUES (' Abby ', ' F '), (' Jo ', ' M ');

Specify a number of columns to assign the initial value, the second form: INSERT into Tbl_name SET col_name1=value,col_name2=value2 ...; Eg:insert into member SET last_name= ' Stein ', First_name= ' Waldo ';

Read the prepared statements from the file (unlike every input line, the file is the statement all in advance, the final one-time input), if MySQL is already running, you can execute the following format, SOURCE file name; Eg:source Insert_president.sql Here the file name to take, at the end of the SQL is convenient to find the representative inside the SQL statement is stored.

Read the original data reproduced in the file: format: LOAD data LOCAL INFILE ' file name ' into

Retrieving information: SELECT (retrieve) from (table or tables) WHERE (coditions, that data must satisfy); Where uppercase letters are keywords, in parentheses are replaceable words

Expression of the WHERE clause: allows the use of arithmetic operators, comparison operators, and logical operators.

When a column is compared to a large number of values, that is, it is easier to use in () to filter out the value that satisfies the condition from the selected column (only one of the conditions is satisfied) Eg:select Last_name,first_name from President WHERE State In (' VA ', ' MA ');

Null means no value or unknown value, to determine whether a value is null, only is NULL or is not NULL, and cannot be used =,<, or <> (which does not mean) Eg:select last_name,first_name from   President WHERE death is NULL; <=> the result of comparing NULL with Null,null with NULL is 1

Sort Query Results: Add an ORDER BY clause after the query statement, by default ascending, followed by the keyword ASC (ascending) or desc (descending or reverse order) Eg:select Last.name,state from President Order by State DESC;

When sorting multiple columns, each column can be individually ascending or descending: Eg:select last_name,state from President ORDER by state Esc,last_name ASC;

When sorting, ascending: null appears at the beginning, and when descending, NULL appears at the end. To disobey from this order, you can use the if () function, the first parameter to calculate the value of the ground expression, the result is true, return the value of the second parameter, if the result is false, then return the value of the third parameter, sort the default to put the real before the false, the setting can be placed in front of the pass condition set to True

Limit query result output lines: At the end of the limit eg:select Bir from the pre ORDER by birth limit 5; List the top 5 presidents by date of birth

By using LIMIT to extract part of a contiguous row from the middle of the query result, you need to specify two values, ① the number of rows to skip from the beginning of the query result ② the number of rows to return Eg:select birth from President ORDER by birth DESC LIMIT 10, 5; Skip the first 10 lines and take 5 consecutive lines

Connect the query results, using the keyword connect eg:select connect (first_name, ", last_name) Connect (city,", state) from President; Connect the first and last names with a space to join the columns in the table

Assign an alias to the output column (when the name of the output column is long), format: As name if the output column alias contains spaces, the alias must be enclosed in quotation marks Eg:select connect (first_name, ", last_name) as name, connect ( City, ", state) as birthplace from President;

When providing aliases for columns, you can omit the keyword as, eg:select 1 one,2 two,3 three;

Retrieves a specific year, month, day, using the years (), month (), DayOfMonth () function, Eg:select Last_name,birth from President WHERE MONTH (birth) =3 and DayOfMonth (birth) =29;//found March 29-born president

Query today, use the Curdate () function

Query time interval for two days: ① when two dates are known, use the Timestanpdiff () function, format: Timestampdiff (INTERVAL,DATETIME_EXPRE1,DATETIME_EXPR2), and two are the points of comparison. , minus before the interval, where interval (interval units) must be one of the following: Frac_second indicates the interval is milliseconds, SECOND seconds, minute minutes, HOUR hours day, WEEK weeks, month months, QUARTE R quarter, year years Eg:select Last_name,expiration from member WHERE Timestampdiff (Day, Curdate (), expiration) <60;

② known a date and time interval, calculate another date, key functions: Data_add () and Data_sub () eg:select data_add (' 1970-1-1 ', INTERVAL year); Where the interval is the interval, the clause can be searched for during the 10-year period

MySQL Technical insider's routine use