MySQL additions and deletions and table copying and renaming

MySQL Add, delete, change, plug
Full table query table record
Format 1:select Field 1, ... field N from library name. Table name;

Format 2:select Field 1, ... field N from library name. Table name where conditional expression;

Precautions:
1. Use "*" to match all fields.
2. When specifying a table name, you can use the name of the library. Form of table name

例：mysql>create database ku;mysql>create table ku.lisi(>name char(10) not null,>gender  enum("boy","girl"),>age int(3) not null>);mysql>insert into ku.lisi   values("luozixu","boy"23);mysql>insert into ku.lisi   values("wang","girl",25);mysql>select * from ku.lisi;mysql>select name,age from ku.lisi where gender="girl";例：为指定字段显示别名,name显示为"姓名"、age显示为"年龄"mysql>select name AS "姓名"，age AS "年龄" from ku.lisi;

Query and condition matching
Use where sentence to guide
1. Applicable to updates, deletions, inquiries, etc.
2. Only qualifying records will be manipulated

Common match conditions
1. Check the numeric size of the field
2. Does the string match
3. Logic test, scope test

数值的比较=             等于>、>=         大于、大于或等于<、<=         小于、小于或等于!=            不等于BETWEEN..AND...  在...与...之间

Logical comparisons, multi-condition combinations
Or, and,! Logical OR, logical and logical, non-
In.., Notin. In.. In scope, not in. Within range
Is null the value of the field is null
Is the value of the NOT NULL field is not empty
DISTINCT does not display duplicate values

    运算操作+          加法-          减法*          乘法/          除法%          取余法（求模）

Using fuzzy queries
Basic usage:
-where field name like "wildcard string"
-Wildcard "_" matches a single character, "%" matches 0-n characters

例：列出name字段值以J开头或以Y结尾的记录mysql>select * from ku.lisi where  name  LIKE  "J"  OR name LIKE "Y";

Using regular expressions
Basic usage:
-where field name REGEXP ' Regular expression '

例：列出name字段值以J开头或以Y结尾的记录mysql>select * from  ku.lisi  where  name regexp ‘^J|Y$‘;

where sub-query
Use the WHERE subquery:
-Query criteria for the outer query as the inner query result

Format 1:select field Name list from library. Table A Where Condition (select field Name list from library. Table A);

Format 2:select field Name list from library. Table A Where Condition (select field Name list from library. Table A Where condition);

例：输出年龄小于平均年龄的学生的姓名和年龄mysql>select  name,age from  ku.lisi  where age < (select avg(age) from  

Format 3:select field Name list from library. Table A Where Condition (select field Name list from library. Table B);

Format 4:select field Name list from library. Table A Where Condition (select field Name list from library. Table B where Condition);

    常用的统计函数avg()         集合的平均数 sum()         对集合中的各参数求和min()         集合中的最小值max()         集合中的最大值count()       记录的个数例：输出ku库lisi表的记录个数，各个学生年龄总和计算各学生的平均年级、最大、最小的年龄。mysql>select count(*),sum(age) from ku.lisi;mysql>select avg(age),max(age) from ku.lisi;

Query result sorting/grouping
Basic usage:
-order by field name [ASC (Ascending)/desc (descending)]
-group by field name [having conditional expression]
-limit n,m limit display of record entries

例：查询ku库lisi表，按年龄排序，输出前2条结果。mysql>select * from  ku.lisi  order  by  age  limit  2;再输出降序排列的前2列结果mysql>select * from  ku.lisi  order  by  age  desc limit 2;例：查询ku库lisi表，按性别进行分组，分别统计boy、girl的人数。mysql>select gender AS "性别"，count(*) AS "人数" from ku.lisi  group by  gender;

Insert Inserts a record
Format 1: (Specify only the values of the partial fields)
Insert into library name. Table name (field 1, Field 2,: field N) VALUES (the value assigned To field 1, the value assigned To field 2, ...). The value assigned by the field N);

Format 2: (Specify a value for each field)
Insert into library name. Table Name values (field 1 assigns the value, field 2 assigns the value, ....) The value assigned by the field N);

Precautions:
1. The field value should match the field type.
2. For a field of character type, enclose it in double or single quotation marks.
3. Field names can be omitted when assigning values to all fields in turn.
4. When assigning values to only a subset of fields, you must explicitly write out the corresponding field names.

例：1.创建库和表结构mysql>create database ku;mysql>create table ku.xi(>name char(10) not null,>gender  enum("boy","girl") default "girl",>age int(3) not null>);2.插入表记录mysql>insert into ku.xi(name,age) values("luozixu",23);mysql>insert into ku.xi    values("lisi","boy",25);3.查看表记录mysql>select * from   ku.xi;

Update record
Format 1: (Update all records in the table)
Update library name. Table name Set field 1 = Field 1 modified value, field 2 = Field 2 modified value, ... field n= field N modified value;

Format 2: (update only partial records that meet the criteria)
Update library name. Table name Set field 1 = Field 1 modified value, field 2 = Field 2 modified value, ... Field n= field n modifies the value of the Where condition expression;

Precautions:
1. The field value should match the field type.
2. For a field of character type, enclose it in double or single quotation marks.
3. All records are updated if they are not qualified with where.
4. When a condition is limited, only records with matching criteria are updated.

例：1.将ku库xi表中的所有人的age字段设置为10。mysql>update ku.xi  set  age=10;mysql>select * from   ku.xi;2.将性别为boy的人的age字段设置为20。mysql>update  ku.xi set age=20 where gender="boy";mysql>select * from   ku.xi;

Delete Deletes a table record
Format 1: (delete only records that match the criteria)
Delete from library name. Table name where conditional expression;

Format 2: (delete all table records from table)
Delete from library name. Table name;

例：1.删除ku库xi表中age字段小于10的表记录。mysql>delete from ku.xi  where age<=10;mysql>select * from   ku.xi;2.删除表所有的表记录mysql>delete from  ku.xi;mysql>select *  from   ku.xi;

Multi-Table query (also known as connection query)
1. Connect 2 or more than 2 tables, and select the data you want by connecting them to a certain condition.

2. When more than one table has a field with the same meaning (the field name can be different), you can concatenate multiple tables through that field.
Format 1:select field Name list from table A, table B;
Format 2:select field Name list from table A, table B where condition;

The result of the query above is called the Cartesian set, and the total entry showing the result of the query is (the record number of table B)

例：mysql>select  *  from ku.user,ku.user2;mysql>select  *  from ku.user,ku.user2  where  ku.user2.shell != ku.user.shell  and ku.usr.uid<100;

Inner JOIN query (only records that match the query criteria are displayed)
Format: Select field Name list from table A, table B;

Outer JOIN query
1. Left connection query (mainly left table record)
2. Right connection query (based on right table record)

Left Join answer Query
Format: Select field List from Table A LEFT JOIN table B on conditional expression;

Right connection query
Format: Select field Name list from table A right join table B on conditional expression;

Copying and renaming of tables
Copy the source table xxx as a new table yyy
Format: CREATE table library name. YYY SELECT * from library name. xxx;

例：mysql>create  table ku.yy  select  * from  mysql.user;

Copies the specified query results to the new table zzz
Format: CREATE TABLE zzz SQL query statement;

例：    mysql>create  table ku.zz  select  user,host,password  from  mysql.user;

Copy the table structure of the source table xxx to the new table BBB
Format: CREATE table library name. BBB SELECT * from library. XXX where false (incorrect record matching);

例：mysql>create table  ku.bb  select  * from  mysql.user  where  false;mysql>desc ku.bb;

Change the name of the source table BBB to www
Format: ALTER TABLE BBB Rename to www;

例：mysql>alter  table  ku.bb rename  to  ku.ww;

MySQL additions and deletions and table copying and renaming