Index Unique Index
Unique: Constraint cannot be duplicated (can be empty) PS: Primary key: Unique and cannot be empty acceleration lookup
Supplement for many-to-many SQL statements: Add CREATE TABLE TB11 (ID int auto_increment PRIMARY key, name varchar (+), age int) Engine=innodb DEFA Ult Charset=utf8; Insert into TB12 (name,age) VALUES (' Alex ', 12); Insert into TB12 (name,age) VALUES (' Alex ', ' n '), (' root ', 18); Insert into TB12 (name,age) select Name,age from Tb11; #将tb11表中的name和age插入到tb表里删delete from Tb12;delete to tb12 where ID =2;delete from TB12 where ID!=2;delete from TB12 wher E ID!=2 and name= ' Alex '; change update tb12 set name= ' Alex ', where id>12 and name= ' xx '; update tb12 set name= ' Alex ', age =19 whe Re id>12 and Name= ' xx '; check select * from Tb12;select id,name to Tb12;select id,name from tb12 where ID >10 or name = ' xxxx '; Select Id,name as CNAME from TB12 where ID >10 or name = ' xxxx '; #给输出的结果, the column name is set alias. Select id,name,123 from tb12 where ID >10 or name = ' xxxx '; The result is followed by a list of two types of 123 MySQL that are not equal:
Select Id,name from tb12 where ID in (1,5,12), select Id,name from tb12 where ID not in (1,5,12), select Id,name from Tb12 w Here ID between 5 and 12; The query ID is between 5-12 and contains the 5 and MySQL wildcard characters starting with a: a%% represents an infinite number of content A_ _ represents a content select * from TB12 where name like "a%" select * from TB12 l Imit 0, 10; 0 is the starting position, and 10 is the starting position to display several. Sort: Order By default is the ascending sort select * from TB12 the ORDER by id DESC; From large to small select * from TB12 ORDER by ID ASC; From small to large select Prod_desc,prod_id,cost from PRODUCTS_TBL where cost<20 order by 1;1 is PROD_DESC2 is Prod_id3 is the cost can be replaced by numbers. SELECT * from Tb12 ORDER by age Desc,id desc; Press age from the big to the small row, if there are duplicates let repeat by ID from big to small row.
Group: Group Bygroup by before order by in where select Max (ID), part_id from Userinfo5 Group by part_id; Take Max (ID) and part_id two columns, GROUP by PART_ID, Max takes maximum, Min takes minimum count function select COUNT (ID), Part_id from userinfo5 GROUP by Part_ id; count number. Count is more readable: select COUNT (part_id) from Userinfo5 Group by part_id; Note: The results returned by COUNT (*) include duplicates and null. Count counts the number of rows and does not involve data types. The sum function Sum function can handle only numeric fields. The Select SUM (cost) from the PRODUCTS_TBL;&NBSP;AVG function calculates the average value and can only handle numeric fields select AVG (SALARY) from employee_pay_tbl; The Max function returns a maximum value in the record, NULL is not in the computed range min function minimum summary function and DISTINCT command are not usually used together. sql statement, if a group by group is required after the aggregation function must be used, such as: SELECT COUNT (ID), max (ID), part_id The Userinfo5 goup by part_id has a count (ID) >1; sql statement where the result of the aggregate function cannot be appended. Remove: SELECT distinct Prod_desc from candy_tbl; result de-weight list operation: SELECT * from Userinfo5, Department5select * from userinfo5,department5 where userinfo5.part_id = department5.id; show two table contents in one table select * From Userinfo5 LefT join department5 on userinfo5.part_id = Department5.id with the above effect, two tables are shown in one table, the second table shows the order condition according to userinfo5.part_id = The table on the left of Department5.idleft join is all displayed and the right side is not displayed. The table on the right join is all displayed and the left side is not all displayed. Do not display for null right can not use left, but the two table position Exchange, the effect can be the same. There must be a relationship between the table and the table, which can be linked to table queries, foreign keys, and so on. If you check multiple tables by connecting a table, note the problem of repeating the column name, you need to bring the table name. Column name this. Such as:
The difference between inner join and left right is that it hides a row when null appears. Select COUNT (id) from USERINFO5; Number of rowsMySQL logic operatorIs NULL vs. NULL comparison between includes boundary values, between 2 and 3, including 2 and 3IN such as where prod_id in ("13", "9", "5"); Like wildcard match,% match 0 one or more numbers or characters _ underscore represents a number or character exists is used to search for records in a specified table that meet certain criteria such as: Select Costfrom Products_tblwhere exists ( Select cost from products_tbl where cost > 100); Uniqueall let a value and a collection compare all to a value and any element of a collection, such as: Select *from products_tblwhere cost > any (select cost from PRODUCTS_TBL where cost < 100); Arithmetic operator + addition-subtraction * Multiplication/Division For example: select SALARY + BONUS from Employee_pay_tbl;select salary-bonus from Employee_pay_tbl;select SALAR Y * Ten from EMPLOYEE_PAY_TBL;
MySQL Knowledge point review (ii)
