MySQL study Note 1

1. Data in relational databases is stored as tables, so the storage engine can also be called the table type (that is, the type of the storage and operation table ). Query supported storage engines in MySQL: show engines; showengines/g or show variables like 'have % '; supported data types in MySQL: numeric, String, date, and time. Numeric types are divided into integer and floating-point string types, including common TEXT string types (CHAR and VARCHAR), variable types (TEXT and BLOB), and special types (SET and ENUM) the MySQL operator "=" is used to determine whether numbers, strings, and expressions are equal. If they are equal, 1 is returned; otherwise, 0 is returned. NULL values cannot be determined by "=. "<>" And "! = "Is used to determine whether or not they are not equal. If they are not equal, 1 is returned. NULL values cannot be used for determination. To determine whether a value is null, use "<=>", is null, and is not null. "Between and" is used to determine whether the data is within a certain value range. "IN" is used to determine whether data exists IN a collection. "LIKE" is used to match strings. The expression is as follows:

X1 LIKE s1;

If x1 matches string s1, 1 is returned; otherwise, 0 is returned. "REGEXP" is also used to match strings, but it uses regular expressions for matching. The expression format is as follows:

X1 REGEXP 'matching Method'

If x1 matches, 1 is returned; otherwise, 0 is returned.

Tip: The REGEXP operator is used to match strings. The method is very simple. REGEXP operators are often used with "^", "$", and. "^" Is used to match the start part of the string, "$" is used to match the end part of the string, and "." is used to represent a character in the string. Create database for MySQL DATABASE operation: create database name; name can be up to 64 characters, alias can up to 256 characters by default: in Windows, the database name and table name are case-insensitive, while in linux, the database table name is case-sensitive. View DATABASE: show database; select DATABASE: use database Name; delete DATABASE: drop database name; MySQL Data Table operation select DATABASE: use database Name; create data table: CREATE [TEMPORARY] TABLE [if not exists] data TABLE name [(create_definition,...)] [table_options] [select_statement] basic format: create table Name (column name 1 attribute, column name 2 attribute ......) View the table structure SHOW [FULL] colums from data table name [FROM database name]; or: SHOW [FULL] colums from database name. data table describe data TABLE name; (view only one column: DESCRIBE data TABLE name column;) modify the TABLE structure Alter [IGNORE] TABLE data TABLE name alter_spec [, alter_spec]... note: If you use alter to modify a table column, you must delete all the data in the table before you can modify the table column. Rename table: rename table data TABLE name 1 To TABLE name 2. Note: This statement can be used To RENAME multiple data tables at the same time. Multiple tables are separated by commas. Delete TABLE: drop table data TABLE name. When deleting a TABLE that does not exist, an error occurs. IF you add the if exists keyword, no error occurs. Format: drop talbe if exists data table name; MySQL statement insert record insert into data table name (column_name, column_name2 ,...) values (value1, value2 ,...) note: multiple rows of records can be inserted at the same time in MySQL. The Value List of each row of records is separated by commas after the VALUES keyword, while only one row can be inserted in a standard SQL statement at a time. The query database record syntax is as follows:

Select [distinct] [concat (col 1, ":", col 2) as col] selection_list

From data table name

Where primary_constraint

Group by groupint_columns // how to group results

Order by sorting_cloumns // how to sort the results (ascending ASC, descending DESC)

Having secondary_constraint // The second condition that is met during Query

Limit count // limit the output query results. Use the distinct keyword to remove duplicate rows in the results. LIKE fuzzy query has two wildcard characters: "%" can match one or more characters, while "_" can only match one character. Example: Find the book whose second letter is "h"

Select * from tb_mrbook where bookname like ('_ H'); CONCAT joins multiple columns of LIMIT to LIMIT the number of query results. Use LIMIT to take values from the middle of the query results. First, define two parameters. Parameter 1 is the number of the record on the first day of reading (in the query result, the record number of the first result is 0 rather than 1 ); parameter 2 indicates the number of records to be searched.

Limit parameter 1, parameter 2; Modify Record UPDATE Syntax: update data table name set column_name = new_value, column_name2 = new_value ,...... Where condition DELETE record delete Syntax: DELETE from data table name where condition;

Note: If the where condition is not specified, all records will be deleted. If the condition is specified, all records will be deleted according to the specified condition.