Shulou(Shulou.com)06/01 Report--

MySQL adds, deletes, modifies, inserts

full table lookup 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;

Notes:

1. Use "*" to match all fields.

2. When specifying a table name, you can use a library name. Form of table name

Example: 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"; e.g.: Display alias for specified field,name is displayed as "name", age is displayed as "age"mysql>select name AS "name", age AS "age" from ku.lisi;

Query and condition matching

Use WHERE sentences to guide

1. Apply to update, delete, query, etc.

2. Only qualified records will be operated

Common matching conditions

1. Check the numeric size of the field

2. Whether the string matches

3. logic test, range test

Comparison of values = Equal>,>= greater than, greater than or equal to select * from ku.lisi where name regexp '^J| Y$';

where subquery

Use the where subquery:

- Use inner query results as query criteria for outer queries

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 conditions);

mysql>select name,age from ku.lisi where age

< (select avg(age) from ku.lisi); 格式3：select 字段名列表 from 库.表A where 条件 (select 字段名列表 from 库.表B ); 格式4：select 字段名列表 from 库.表A where 条件 (select 字段名列表 from 库.表B where 条件); 常用的统计函数avg() 集合的平均数 sum() 对集合中的各参数求和min() 集合中的最小值max() 集合中的最大值count() 记录的个数例：输出ku库lisi表的记录个数，各个学生年龄总和计算各学生的平均年级、最大、最小的年龄。mysql>

select count(*),sum(age) from ku.lisi;mysql>select avg(age),max(age) from ku.lisi;

Sort/group query results

Basic Usage:

-ORDER BY field name [asc (ascending)/desc (descending)]

-GROUP BY field name [HAVING conditional expression]

-LIMIT N,M Limit the number of record entries displayed

Example: query ku lisi table, sort by age, output the first 2 results. mysql>select * from ku.lisi order by age limit 2; then output the first two columns of results arranged in descending order mysql>select * from ku.lisi order by age desc limit 2; example: query ku lisi table, group by gender, and count the number of boys and girls respectively. mysql>select gender AS "gender", count(*) AS "number" from ku.lisi group by gender;

insert insert record

Format 1:(Specify values for only some fields)

insert into library name. Table Name (Field 1, Field 2,.. Field N) values(values assigned to field 1, values assigned to field 2,... field N assigned value);

Format 2:(Specify the value of each field)

insert into library name. Table name values(values assigned to field 1, values assigned to field 2,... field N assigned value);

Notes:

1. The field value matches the field type.

2. For fields of character type, enclose them in double or single quotes.

3. Field names can be omitted when assigning values to all fields sequentially.

4. When assigning values to only a part of fields, the corresponding field names must be clearly written.

Example: 1. Create library and table structures 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. Insert table records mysql>insert into ku.xi(name,age) values("luozixu", 23);mysql>insert into ku.xi values("lisi","boy", 25);3. View table records mysql>select * from ku.xi;

update update record

Format 1:(update all records in the table)

Update library name. table name set field 1= value modified by field 1, field 2= value modified by field 2,... Field N= modified value of field N;

Format 2:(only update qualified partial records)

Update library name. table name set field 1= value modified by field 1, field 2= value modified by field 2,... Field N= modified value of field N where conditional expression;

Notes:

1. The field value matches the field type.

2. For fields of character type, enclose them in double or single quotes.

3. If not qualified by WHERE, all records are updated.

4. When limiting conditions, only records matching the conditions are updated.

Example: 1. Set the age field of all people in ku library xi table to 10. mysql>update ku.xi set age=10;mysql>select * from ku.xi;2. Set the age field for people with gender boy to 20. mysql>update ku.xi set age=20 where gender="boy";mysql>select * from ku.xi;

delete delete table record

Format 1:(Delete only eligible records)

delete from library name. table name where conditional expression;

Format 2:(delete all table records)

delete from library name. Table name;

Example: 1. Delete table records with age field less than 10 in ku library xi table. mysql>delete from ku.xi where ageselect * from ku.xi;2. delete all table records in the table mysql>delete from ku.xi;mysql>select * from ku.xi;

Multi-table queries (also known as join queries)

1. Connect two or more tables according to a certain condition and select the required data from them.

2. When there are fields with the same meaning (field names may be different) in multiple tables, multiple tables can be connected through this 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 query results in the above format are called Cartesian sets, and the total entries showing the query results are (records in table a * records in table b)

Example: mysql>select * from ku.user,ku.user2;mysql>select * from ku.user,ku.user2 where ku.user2.shell!= ku.user.shell and ku.usr.uidcreate table ku.yy select * from mysql.user;

Copies the specified query results to a new table zzz

Format: create table zzz SQL query statement;

Example: mysql>create table ku.zz select user,host,password from mysql.user;

Copy table structure of source table xxx to new table bbb

Format: create table library name.bbb select * from library.xxx where false(wrong record matching);

Example: 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;

Example: mysql>alter table ku.bb rename to ku.ww;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.