MySQL Advanced 2

Data type:

Difference between the width of a character type and the width of a numeric type
1, the width of the numeric type is the display width, only for the Select query display, and the amount of storage space occupied independent, can be used Zerofill to display the effect
2. The width of the character type exceeds the specified width and cannot be stored
CREATE TABLE T1 (
ID int (3) Zerofill,
Name Char (20)
);
INSERT into T1 VALUES (1, "Lucy"); # # Display effect is 001

Working with table fields

1、语法：alter table 表名 执行动作;2、添加字段(add)    1、添加到末尾        alter table 表名 add 字段名 数据类型;    2、添加到第一列        alter table 表名 add 字段名 数据类型 first;    3、添加到指定位置        alter table 表名 add 字段名 数据类型 after 字段名;3、删除字段(drop)    alter table 表名 drop 字段名;4、修改字段数据类型(modify)    alter table 表名 modify 字段名 新的数据类型;5、修改字段名(change)    alter table 表名 change 旧字段名 新字段名 数据类型;6、修改表名(rename)    alter table 表名 rename 新表名;

Management of Table Records

1、删除表记录    1、delete from 表名 where 条件;    2、注意        delete语句后如果不加where条件子句会将表中所有记录全部删除    3、示例        create table t4(        id int(3) zerofill,        name varchar(20),        sex enum("boy","girl")        );        insert into t4 values        (1,"Lucy","girl"),        (2,"Tom","boy"),        (3,"Bob","boy");        select * from t4;

Update table Records

    1、update 表名 set 字段名=值1,字段名=值2,...,where 条件;    2、注意        update语句后如果不加where条件会将表中所有的值修改    3、练习(见建表脚本MOSHOU.hero.txt)        1、查找所有蜀国人的信息            select * from hero where country="蜀国";        2、查找女英雄的姓名、性别和国家            select name,sex,country from hero where sex="女";        3、把曹操的国籍改为蜀国            update hero set country="蜀国" where name="曹操";        4、把魏延的性别改为 女 ,国籍改为 泰国        5、把id为2的记录的姓名改为司马懿,性别为男,国家为魏国        6、删除所有的泰国人        7、将表中所有的记录的国家改为吴国        8、删除所有英雄的信息

4, operator operation (with query, modify, delete)
1. Numerical Comparison & character comparison
1. Numeric comparison operators: =,! =, >, >=, <, <=
2. Character comparison operators: =,! =
3. Practice
1. Find out the hero's name and attack damage value above 150.
2, find out the defensive force value is not 66 of the hero information
2. Logical comparison
1, Operator: and (multiple conditions are met)
or (Multiple conditions can be satisfied)
2. Practice
1. Find out the Shu Hero's name, attack value and country of attack value greater than 200
2. Set the attack value of Heroes of Wu heroes to 110 to 100 and the defense value to 60
3. Find the hero information of Shu and Wei
3. In-range comparison
1, Operator: between and, in, not in
2. Grammar
Field name between value 1 and value 2
Field name in (value 1, value 2,..., value N)
Field name not in (value 1, value 2,..., value N)
3. Practice
1, find the attack value between 100-200 Shu hero information
2. Find the information of the heroine of the country other than Shu and Wu
3, find the number 1, 3 or 5 Shu Hero Shing's number, name, country
4, matching empty, non-empty
1, null: IS NULL
2, non-null: is NOT NULL
3. Practice
1. Find Shu Hero information for null (NULL) name
2. Find the Hero information named empty string ("")
4. Attention
1, NULL: null value, must be matched with IS or not
2, "": empty string, with = or! = to match
5. Fuzzy comparison
1. Grammar
Field name like expression
2. Expression
1. : Match a single character
2,%: match 0 to more characters
3. Practice
1. Select Id,name from Sanguo where nameis like "%_";

Matches at least two characters in a name.

        2、select id,name from sanguo where name like "%";            # 匹配除NULL之外的所有英雄信息        3、select id,name from sanguo where name like "___";            # 匹配名字中有三个字符的        4、select id,name from sanguo where name like "赵%";            # 匹配姓赵的英雄信息

SQL query

1. Summary (Order of execution) 3, select ...    Aggregate functions from ...    1. Where ...    2. Group BY ...    4, having ...    5. Order BY ... 6. Limit ...        2, order by 1, role: To sort the results of the query 2, sort by 1, ASC (default): Ascending 2, DESC: Descending 3, Syntax format order by field name sort by Type 4, exercise        1, the Hero information by the defensive value from low to high order select * from Sanguo ORDER by Fangyu ASC;        2, the Shu hero information by the attack value from high to low sort select * from Sanguo where country= "Shu" ORDER by Gongji DESC; 3, the hero of the two countries in the Wei Shu name three characters in ascending order of defensive values select * from Sanguo where country in ("Shu", "Wei") and sex= "male" and name    Like "___" order by Fangyu;                3, limit (always put in the last write of SQL statement) 1, the role: limit the number of display query records 2, usage 1, limit N--show a few records 2, limit M,n M--from the beginning of the first few records, N indicates that several # # m values are counted from 0, and 3 indicates that the fourth record begins with the limit 3,5: Display 4-8 records 3                , Exercise 1, find the name, attack value, and country--select name,gongji,country from Sanguo of the first three Shu heroes with a name that is not a null value Where, NAMe is not null and country= "Shu", ORDER by Gongji Desc, limit 3; 2. Find the second-lowest defensive value to the fourth-lowest Shu hero record, select * from Sanguo, where, countr Y= "Shu", ORDER by Fangyu ASC limit 1, 3;

Aggregation functions

        1, Category 1, AVG (field name): Find the average value of the field 2, Sum (field name): Ask for fields and 3, max (field name): Find the maximum of the field 4, MI N (field name): The minimum value of the field is 5, count (field name): Count the number of records in the field 2, Exercise 1, the strongest attack value is how many select Max (Gongji)            From Sanguo;                2, Statistics table in the Id,name field has a single proud few records select COUNT (ID), count (name) from Sanguo; # # NULL NULL is not counted, empty string "" will be counted 3, calculate Shu Hero's total attack, select SUM (Gongji) from Sanguo-&gt ;            where country= "Shu"; 4. Count the number of heroes in Shu Heroes with an attack value greater than 200, select COUNT (*) from Sanguo, where    Country= "Shu" and gongji>200; 5, GROUP by 1, role: to the results of the query Group 2, Exercise 1, Query Sanguo table in a total of several countries select country from Sanguo Gro            up by country;            2. Calculate the average attack power of all countries select COUNTRY,AVG (Gongji) from Sanguo Group by country; 3. Find the country name and number of heroes in the top two heroes in all countries Select CoUntry,count (*) from Sanguo, group by country, order by COUNT (*) desc        , limit 2; 3, note 1, the field name after the group by must be the field name after Select 2, if the field name after the select is inconsistent with the field name after the group by, you must aggregate the field value (aggregate function) 6, Having a statement 1, the role: to further filter the results of the query 2, Exercise 1, find out the average damage greater than 105 countries of the top two, showing the country name and average attack, select count Ry,avg (Gongji) from Sanguo, group by country, having AVG (Gongji) >105, O        Rder by AVG (GONGJI) desc-a limit 2; 3, note 1, having a statement is commonly used in conjunction with the group BY statement to filter the recordset returned by the group BY Statement 2, the existence of a having statement compensates for the lack of a WHERE clause cannot be used in conjunction with an aggregate function, where                Only the fields that actually exist in the table can be manipulated, and the having action is that the aggregation function produces the display column 7, distinct 1, function: Do not display the repeating value of the field 2, Exercise 1, Sanguo table in which several countries                Select distinct country from Sanguo;            # Multiple fields are separated by commas 2, calculate Shu altogether how many Heroes select COUNT (distinct name) from Sanguo where country= "Shu";  3. Attention              1, distinct processing is distinct and from all fields, all field values must be all the same to go to the weight 2, distinct can not do any field aggregation 8, query table records when doing mathematical operations 1, operator + 、-、 *,/,% 2, Exercise 1, query display when all heroes attack all the damage multiplied by the select id,name,country,gongji*            Ten as Xgj from Sanguo; 2, when the query shows all the hero's defensive force +5 select id,name,country,fangyu+5 from Sanguo;

Constraints

1、作用    为了保证数据的完整性、一致性、有效性的规则    可以限制无效的数据插入到数据表中2、约束分类    1、默认约束(default)        1、作用 ：在插入记录时,如果不给该字段赋值,则使用默认值        2、格式 ：字段名 数据类型 default 值    2、非空约束(not null)        1、作用 ：不允许该字段的值有NULL记录        2、格式 ：字段名 数据类型 not null

Index

1、定义    对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用BTREE方式)2、优点    可以加快数据的检索速度3、缺点    1、当你对表中的数据进行增加、修改和删除的时候,索引也要动态维护,降低了数据的维护速度    2、索引需要占用物理空间4、索引类型    1、普通索引(index)        1、使用规则            1、一个表中可以有多个index字段            2、字段的值可以有重复,且可以为NULL值            3、经常把做查询条件的字段设置为index字段            4、index字段的key标志是MUL        2、创建index            1、创建表时创建                index(字段名1),index(字段名2)            2、在已有表中创建index                create index 索引名 on 表名(字段名);            3、注意                索引名一般和字段名相同        3、查看普通索引            1、desc 表名;  -->查看key标志为MUL            2、show index from 表名;        4、删除普通索引            drop index 索引名 on 表名;            注意：                删除索引只能一个一个删除    2、唯一索引    3、主键索引    4、外键索引

MySQL Advanced 2