Build table: (NOT NULL, auto_increment, unique, primary key)Create Database Balfish;
Use Balfish;
CREATE TABLE Mytable1 (
ID int NOT NULL,
Name varchar (20),
Grade int
);
INSERT into mytable1 values (' 1 ', ' Yang ', 97);
INSERT into mytable1 values (' 2 ', ' Li ', 94);
INSERT into Mytable1 values (' 3 ', ' Chen ', 88);
INSERT into mytable1 values (' 4 ', ' Zhang ', 57);
INSERT into Mytable1 values (' 5 ', ' Dai ', 90); CREATE TABLE Mytable2 (
ID int NOT NULL,
Name varchar (20),
Job varchar (20)
);
INSERT into mytable2 values (' 1 ', ' Yang ', ' worker ');
INSERT into mytable2 values (' 2 ', ' Li ', ' farmer ');
INSERT into mytable2 values (' 3 ', ' Shi ', ' doctor ');
Select:SELECT * from Mytable1 ORDER BY grade desc limit 0, 2; Inner JOIN (1) SELECT * from TB1,TB2 where Tb1.name=tb2.name, (2) SELECT * from tb1 inner join TB2 on Tb1.name=tb2.name; Outerselect * from Mytable1 tb1 left joins Mytable2 TB2 on Tb1.name=tb2.name;
InsertInsert more than one at a time. Insert INTO city (Id,city_name) VALUES (1, ' Beijing '), (2, ' Shanghai '); When there is a uniqueness constraint in the Insert table, the method 1:insert into Tbl_name (A, B, c) VALUES (?,?,?) on duplicate key update c=values (c); method 2:insert Ignore I Nto tbl_name (A,b,c) values (a);
UpdateUpdate product set amount=150 where Id=1;update product_details set weight=38,exist=1 where name= ' Jim '; update tbl_name set b=b+1 where name= ' AAA ';
DeleteDELETE from orderlog where user = ' Sean ' and ID between 20000 and 40000;
truncate– Features
is a DDL operation and cannot be rolled back immediately after execution
Same as delete from TB, delete full table data, keep empty table
Drop permission Required
Syntax: TRUNCATE TABLE product;
CREATE TABLE
Tables (1) CREATE TABLE T1 select * from product; Create a new table with the same structure as the original table field, remove all constraints, and insert the result data of the original list select into the new table (2) CREATE table t2 like product; Create a new empty table that exactly matches the original table structure, containing all constraints
ALTER TABLEField action: ALTER TABLE ADD/MODIFY/DROP column ... alter TABLE t2 add column contact varchar (50);
ALTER TABLE t2 Modify column Contact varchar (500);
ALTER TABLE T2 drop column contact; Index operation: ALTER TABLE T2 add index Idx_orderno (OrderNo);
ALTER TABLE T2 DROP INDEX Idx_orderno;
ALTER TABLE T2 add primary key (ID);
ALTER TABLE T2 add unique index uniq_version (version);
drop table– Features
• Delete Table operations, clear all data, delete table definition files
• Do not roll back
• Syntax: drop table t2;
Storage Engine:–myisam
Transaction not supported
Table-Level Locks
Only indexes can be cached
Table files may become corrupted after a bulk update operation
–innodb (currently used on-line engine)
Support Transactions
Row-level locking for improved concurrency
Cache data and indexes in buffer pool
does not damage
IndexAn index is a data structure that the storage engine uses to quickly locate data • Index scanning
Primary key----query directly on clustered B+tree
The secondary index----queries the primary key on secondary b+tree, and then uses the primary key in clustered B+tree
MySQL Specific statement Example
