MySQL's SQL use

MySQL's SQL use

Brief introduction
What is SQL?
SQL refers to Structured query language
SQL gives us the ability to access a database
SQL is an ANSI standard computer language
Remarks: ANSI, national standardization organization of the United States

First, SQL statements, syntax

1. Check: SELECT statement is used to select data from the table. The result is stored in a result table, called a result set. The SQL SELECT Syntax Select column from the Table_name;select * from Table_name;2, interpolate: INSERT INTO statement is used to insert a new row into the table. SQL INSERT syntax INSERT INTO TABLE_NAME values (value 1, value 2,...); INSERT INTO table_name (column 1, column 2,...) VALUES (value 1, value 2,...); 3. Change: The Update statement is used to modify the data in the table. SQL UPDATE Syntax update table_name SET column 1 = new value where column 2 = value # column 2 specifies who modified, column 1 specifies modified content 4, delete: Delete statement is used to remove rows from the table. SQL Delete syntax delete from table_name where column = value; # Deletes a row delete from table_name;     or delete * FROM table_name;      The #删除所有行 (the structure, properties, and indexes of the table are complete) SQL TRUNCATE TABLE syntax TRUNCATE TABLE table_name; # Delete all rows in the table like Delete, which is more efficient 5, the TOP:TOP clause is used to specify the number of records to return.     Column (s) = *sql top Syntax select top number|precent column (s) from table_name; The # column (s) represents all column names in the top number example: Select TOP 2 * FROM table_name; or select * FROM table_name limit 2;top precent Example: Select Top precent * FROM table_name; # 50% of records in Table 6, the like:like operator is used in the WHERE clause to search for the specified pattern in the column SQL like syntax select column (s) from table_name WHERE column like pattern; example: SELECT * FR OM table_name where column like ' A% ' (search for a table starting with a) | ' %B ' (inb End of) | ' %ab% ' (contains AB); 7, the in:in operator allows us to specify multiple values in the WHERE clause SQL in syntax SELECT * FROM table_name WHERE column in (value 1, value 2,...) ; 8, between: operator between ... and selects a range of data between two values. These values can be numeric, text, or date. SQL between syntax select * FROM table_name where column between value 1 and value 2; # Range header does not take tail 9, alias: Specify Alias for column name and table name. SQL alias Syntax table: SELECT * FROM table_name as alias_name; table alias example: select P. column 1,p. Column 2,p. Column 3 from table_name1 as p,table_name2 as PO wh ERE p. Column 1 = ' value 1 ' and p. column 2 = ' value 2 '; column: Select column as Alias_name from table_name; column alias example: Select column 1 as alias_name1, column 2 as alias_name2 from TABLE_NAME;10, join: Use to query data from these tables based on the relationship between the columns in two or more tables.  SQL join syntax select TB1. column 1,tb1. Column 2,tb2. Column from tb1 inner join TB2 on tb1. Column 3=tb2. Column 3 order by tb1. column 1; or select TB1. column 1,tb1. Column 2,tb2. Column From TB1,TB2 where tb1. Column 3=tb2. column 3; Note: Different SQL joins can use several other connections in addition to the INNER join (inner join) used in the example above. The following lists the types of joins that can be used, and the differences between them. JOIN: Returns the row left join if there is at least one match in the table: even if there is no match in the right table, all rows are returned from the table in the open join: Even if there is no match in the left table, all rows are returned from the right table full join: As long as there is a match in one of the tables, Returns the row INNER join keyword syntax: the INNER join keyword returns a row when there is at least one match in the table. SELECT * FROM tb1 INNER JOIN TB2On tb1. Column =tb2. Column; # INNER JOIN and join are the same LEFT JOIN keyword syntax: The LEFT JOIN keyword returns all rows from the table (TB1), even if there are no matching rows in the right table (TB2). SELECT * from tb1 left joins TB2 on tb1. Column =tb2. column; Right join keyword syntax: the correct join keyword will return all rows to the table (TB2), even if there are no matching rows in the table (TB1). SELECT * from tb1 right joins TB2 on tb1. Column =tb2. column; Full join keyword syntax: The full join keyword returns a row select * from the TB1 full join as long as there is a match in one of the tables TB2 on tb1. Column =TB2.11, Union: The result set used to merge two or more SELECT statements (the SELECT statement inside the Union must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same) the SQL Union and UNION ALL syntax select * FROM TABLE_NAME1 Union SELECT * FROM Table_name2;select * FROM TABLE_NAME1 UNION ALL SELECT * from table_name2; Note: By default, the union picks a different value, and if duplicate values are allowed, use UNION ALL (list all values) 12, select INTO: Select data from one table, and then insert the data into another table. Often used to create a backup copy of a table or to archive records. SQL SELECT INTO syntax select * into New_table_name [in externaldatabase] from Old_table_name;  # All Columns Insert new table Select Column into New_table_name [in externaldatabase] from Old_table_name; # Insert a column into a new table example: Back up a table select * into Tb1_backup from tb1; Copy table to another database select * into tb1 in ' Backup.mdb ' from TB1. Multi-table query is deposited into the new table select TB1. column 1,tb2. Column 2 to new_table_name from tb1 inner join TB2 on tb1. Column 3=tb2. column 3;13, CREATE database: for creating databases SQL CREATE DATABASE syntax create DATABASE_NAME;14, CREATE TABLE: used to create tables in databases SQL CREATE TABLE syntax CREATE TABLE table_name ( Column 1 data type, column 2 data type,...) Note: Common data types are int (size)-Integer, Decimal (size,d)-With decimal number, char (size)-fixed-length string, varchar (size)-variable long string data type subsequent additions to 15, create Index: Used to create an index in the table. Indexes enable database applications to find data faster without reading the entire table. Note: Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.   SQL CREATE INDEX syntax CREATE INDEX INDEX_NAME on table_name (column);      # Simple index, allow use of duplicate values CREATE INDEX index_name on table_name (column 1 desc, column 2); # The default is ascending, you want to add the reserved word desc after the column name in descending order, index more than one column can be comma separated SQL create unique index syntax # Unique indexes create unique-index index_name on Table_na    Me (column); # A unique index means that two rows cannot have the same index value 16, drop: Delete index, table, and database SQL drop syntax to drop the index: DROP index index_name on table_name; Delete table: Drop tables table_name Delete Library: Drop database database_name;17, ALTER TABLE: statements are used to add, modify, or delete columns in an existing table. SQL ALTER TABLE syntax add COLUMN: ALTER TABLE table_name ADD column data type; Delete column: ALTER TABLE table_name DROP column, change the data type in the column: ALTER TABLE table_name Modify the type that needs to be modified; methods that only modify the data type of the column: you can usually write ALTER TABLE name modify column column type For example: The type of column sname in the student table is char (20), now to be modified to varchar, the SQL statement is as follows ALTER TABLE student MO Dify column sname varchar (20); Methods to modify the data type of the name and column at the same time: can usually be written as ALTER TABLE name change column old column name new column type For example: Student the type of column sname in table is Cha R (20), now to be modified to stuname varchar, the SQL statement is as follows ALTER TABLE student change column sname stuname varchar; ALTER TABLE Table_ Name add < new column name > < data type > [constraint][flrst (add column to Front of table) | After (specify column) < columns that already exist >]; # ADD column ALTER TABLE TABLE_NAME < OLD column name > < new column name > < new data type >; # Modify Column name ALTER TABLE TABLE_NAME Modify < column name > < data type >; # Modify the data type of a column ALTER TABLE TABLE_NAME modify < column 1 (the column you want to change) > < data type > flrst| After < column 2>;  # Modify the position of the column ALTER TABLE table_name DROP < column >; # Delete columns ALTER TABLE < old table name > Rename to < new table name >; # Modify Table name 18, auto increment: Generates a unique number when a new record is inserted into a table you typically want to automatically create a value for the primary key field each time you insert a new record. You can create a auto-increment field in the table. SqlAuto-increment Syntax CREATE TABLE table_name (ID int NOT NULL auto_increment,name varchar (255) Not null,primary key (ID)); #id定义为auto_increment主键19, view: A view is a table that is visualized. A view contains rows and columns, like a real table comment: The design and structure of a database is not affected by functions, where, or join statements in the view. SQL CREATE VIEW syntax create VIEW view_name as select column (s) from table_name where condition; Comment: The view always displays the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements. Update view: Create or Replace View view_name as Select column (*) from table_name where condition; Delete view: Drop view view_name;

II. SQL constraints (constraints)

SQL constraints: Constraints are used to restrict the type of data that is joined to a table. is a restriction that ensures the integrity and uniqueness of table data by restricting the data of the table's Rows or columns
You can specify constraints (through the CREATE TABLE statement) When you create the table, or you can (via the ALTER table statement) after the table is created.

1, NOT NULL constraint: Forces a column to not accept a null value, forcing the field to always contain a value. This means that if you do not add a value to the field, you cannot insert a new record or update the record. Example: CREATE TABLE table_name (ID int not null,name varchar (255) is not NULL); # Mandatory ID and name cannot be null 2, UNIQUE constraint: uniquely identifies each record in a database table, ensuring that a column of data in a table does not have the same value unique and PRIMARY KEY constraints are guarantees of uniqueness for a column or column collection. PRIMARY KEY has a UNIQUE constraint that is automatically defined. Each table can have multiple unique constraints, but there can be only one PRIMARY KEY constraint for each table: CREATE table when creating a UNIQUE constraint on the ID column, created table table_name (ID int not null,name VARC Har (255) NOT NULL, unique (ID)); Example 2: Define UNIQUECREATE Table table_name for multiple columns (ID int not null,name varchar (255) NOT NULL, Constra int Uc_personid Unique (id,name)) Example 3: Create a UNIQUE constraint with the ALTER TABLE table_name add unique (ID) and multiple: ALTER TABLE table_name Add constraint Uc_personid unique (id,name); Example 4: Undo Uniquealter Table table_name DROP INDEX UC_PERSONID;3, PRIMARY KEY Constraint: The PRIMARY KEY constraint uniquely identifies each record in a database table. The primary key must contain a unique value. Primary key columns cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key. Primary KEY = unique + NOT NULL example: CREATE table when creating a primary KEY constraint in the ID column the CREATE TABLE table_name (ID int not null,name varchar (25 5) Not NULL, PRIMARY KEY (ID)), Example 2: Define PRIMARY for multiple columns KEYCREATE table table_name (ID int not null,name varchar (255) NOT NULL, constraint Pk_personid PRIMARY KEY (id,name)) Example 3: Table created case Create PRIMARY KEY constraint ALTER TABLE table_name add PRIMARY KEY (ID); Multiple: ALTER TABLE TABLE_NAME ADD constraint Pk_personid PRIMARY KEY (id,name); Example 4: Undo Primary Keyalter Table table_name DROP INDEX UC_PERSONID;4, FOREIGN key constraint: FOREIGN key in one table points to Primar in another table The foreign key for the Y key is used to establish a link between two table data, which can be one or more columns. A table can have one or more foreign keys that correspond to referential integrity, a foreign key for a table can be null, and if not NULL, each foreign key value must be equal to a value from the primary key in another table. The FOREIGN KEY constraint is used to prevent actions that disrupt the connection between tables. The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to. Example: CREATE table when creating a FOREIGN KEY constraint in the ID column creates a table table_name1 (id int not null,name varchar (255) not null,id_p int, PRIMARY Key (ID), FOREIGN key (id_p) REFERENCES table_name2 (id_p)); Example 2: Define FOREIGN KEY CREATE TABLE table_name1 (id int not null,na) for multiple columns Me varchar (255) not null,id_p int, PRIMARY key (Id) constraint fk_pertb2 FOREIGN KEY (id_p) REFERENCES table_name2 (id_p)) Example 3 : Table created FOREIGN KEY constraint ALTER TABLE table_name ADD add FOREIGN key (id_p) REFERENCES table_name1 (id_p); multiple: ALTER TABLE TABLE_NAME ADD constraint Pk_personid PRIMARY key (Id,name); Example 4: Undo FOREIGN key ALTER TABLE table _name drop FOREIGN key fk_pertb2; foreign KEY constraint mysql> CREATE TABLE bookcategory (category_id int primary KEY, C Ategory varchar (), parent_id int);mysql> CREATE TABLE bookinfo (book_id int primary key, b ook_category_id int, constraint fk_cid foreign key (book_category_id) references bookcategory (category_id)); 5, CHE CK constraint: Used to limit the range of values in a column if you define a CHECK constraint on a single column, the column only allows a specific value. If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column. Example: CREATE table when creating a CHECK constraint on the ID column creates a table table_name (ID int not null,name varchar (255) not Null,check (id>0)); Example 2: For multiple columns The semantic CHECK Constraint CREATE TABLE table_name (ID int not null,name varchar (255) is not NULL, constraint Chk_tbname CHECK (id>0 and name = ' xxx '); Example 3: Create a CHECK constraint when the table is created ALTER TABLE table_name ADD constraint Chk_tbname CHECK (id_p>0 and name= ' xxx '); Example 4: Undo Checkalter Table table_name drop constraint chk_tbname;6, DEFAULT approx.Bundle: Used to insert a default value into a column if no other value is specified, the default value is added to all new records. Example: CREATE table when creating a DEFAULT constraint in the ID column, similar to GETDATE (), the default constraint can also be used to insert system values CREATE TABLE table_name (ID int not null,name varch AR (255) NOT null default ' Lxq ', timedate date default GETDATE ()); Example 2: Create a DEFAULT constraint in case the table is created ALTER TABLE TABLE_NAME ALTER name Set default ' LXQ2 '; Example 3: Undo Defaultalter Table table_name ALTER name DROP default;

Third, SQL functions

1. Date

函数及描述NOW()：返回当前的日期和时间CURDATE()：返回当前的日期CURTIME()：返回当前的时间DATE()：提取日期或日期/时间表达式的日期部分EXTRACT()：返回日期/时间按的单独部分DATE_ADD()：给日期添加指定的时间间隔DATE_SUB()：从日期减去指定的时间间隔DATEDIFF()：返回两个日期之间的天数DATE_FORMAT()：用不同的格式显示日期/时间

Iv. Types of numbers

There are three main types of MySQL: text, numbers, and date/time types
1. Text type

CHAR(size)：保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。VARCHAR(size)：保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。TINYTEXT：存放最大长度为 255 个字符的字符串。TEXT：存放最大长度为 65,535 个字符的字符串。BLOB：用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。MEDIUMTEXT：存放最大长度为 16,777,215 个字符的字符串。MEDIUMBLOB：用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。LONGTEXT：存放最大长度为 4,294,967,295 个字符的字符串。LONGBLOB：用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。ENUM(x,y,z,etc.)：允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X‘,‘Y‘,‘Z‘)SET：与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

2. Type of number

TINYINT(size)：-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。SMALLINT(size)：-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。MEDIUMINT(size)：-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。INT(size)：-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。BIGINT(size)：-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。FLOAT(size,d)：带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。DOUBLE(size,d)：带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。DECIMAL(size,d)：作为字符串存储的 DOUBLE 类型，允许固定的小数点。重点：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

3. Date type

DATE()：日期。格式：YYYY-MM-DD注释：支持的范围是从 ‘1000-01-01‘ 到 ‘9999-12-31‘DATETIME()：*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1000-01-01 00:00:00‘ 到 ‘9999-12-31 23:59:59‘TIMESTAMP()：*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00‘ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1970-01-01 00:00:01‘ UTC 到 ‘2038-01-09 03:14:07‘ UTCTIME()：时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59‘ 到 ‘838:59:59‘YEAR()：2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。重点：即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD

MySQL's SQL use