from w3cschool.com
1,modify the segment of table
alter table table_name add/drop var_name [var_type];
2,update
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
3,like==regexp
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
4,drop
DROP TABLE table_name ;
drop db_name;
5,create
mysql> CREATE TABLE tutorials_tbl(
-> tutorial_id INT NOT NULL AUTO_INCREMENT,
-> tutorial_title VARCHAR(100) NOT NULL,
-> tutorial_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( tutorial_id )
-> );
create db_name
6,delete
DELETE FROM table_name [WHERE Clause]
7,order by
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
8,INDEX
9,temp table
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
10,create clone table
mysql> SHOW CREATE TABLE tutorials_tbl \G;
mysql> CREATE TABLE `clone_tbl` (...);
mysql> INSERT INTO clone_tbl (tutorial_id,
-> tutorial_title,
-> tutorial_author,
-> submission_date)
-> SELECT tutorial_id,tutorial_title,
-> tutorial_author,submission_date,
-> FROM tutorials_tbl;
11,mysql info
SELECT VERSION( )
SELECT DATABASE( ) current dbname
SELECT USER( ) current user
SHOW STATUS
SHOW VARIABLES
12,AUTO_INCREMENT
reset table
insert table with id acsend
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
13,primary key,unique // The UNIQUE constraint uniquely identifies each record in a database table.
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name) //last_name not permit to be the same as first_name
);
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10)
UNIQUE (last_name, first_name) // INSERT IGNORE INTO person_tbl (last_name, first_name),insert record existed will not report err,either real insert it
);
14,export &LOAD
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
---
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a); //specialize the order
---
mysqlimport dbname tablename.txt
15,Function
SELECT AVG(Price) AS PriceAverage FROM Products;
SELECT COUNT(*) AS NumberOfOrders FROM Orders;
SELECT FIRST(column_name) FROM table_name;
SELECT LAST(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;
SELECT LCASE(CustomerName) AS Customer, City //extract characters from a text field
FROM Customers;
SELECT LEN(column_name) FROM table_name;
select round(2.555,2) from dual;
SELECT NOW() FROM dual;
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
FOREIGN KEY constraint is used to prevent actions that would destroy links between tables
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
to be added...