Based on using

Library operation

To build libraries

CREATE DATABASE Specifies the DATABASE name.Copy the code

Delete library

DROP DATABASE Specifies the DATABASE name.Copy the code

Select database

show databases; Use the database name; // Select the databaseCopy the code

Table operation

Build table

1. You can set the property of a field to NOT NULL. If the field is entered as NULL during database operation, an error message is displayed. 2.AUTO_INCREMENT Defines an attribute that is incremented by a primary key. The value is automatically incremented by 1. 3. The PRIMARY KEY keyword is used to define the PRIMARY KEY of a column. You can define primary keys with multiple columns separated by commas. 4.ENGINE Sets the storage ENGINE. CREATE TABLE IF NOT EXISTS 'test_tab' (' test_id 'INT UNSIGNED AUTO_INCREMENT, `test_title` VARCHAR(100) NOT NULL, `test_author` VARCHAR(40) NOT NULL, `create_date` DATE, PRIMARY KEY ( `rtest_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;Copy the code

Update the table

ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD I INT; ALTER TABLE testalter_tbl MODIFY c CHAR(10); ALTER TABLE testalter_tbl CHANGE I j BIGINT; ALTER TABLE testalter_tbl RENAME TO ALTER_tbl; ALTER TABLE testalter_tbl RENAME TO alter_tbl; ALTER TABLE testalter_tbl ALTER I SET DEFAULT 1000; ALTER TABLE testALTER_tbl ALTER I DROP DEFAULT; // Delete field defaultsCopy the code

Delete table

DROP TABLE test_tab;
Copy the code

Query the table structure

desc tb1; Show COLUMNS from tb1; Show create table tb1; Look at the construction of tb1Copy the code

Query table

show tables; 
Copy the code

Data manipulation

Insert data

INSERT INTO table_name ( field1, field2,... fieldN ) VALUES ( value1, value2,... valueN );Copy the code

Delete the data

DELETE FROM table_name [WHERE Clause]
Copy the code

Update the data

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
Copy the code

Query data

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [OFFSET M]

example:
where cause1 = 1 and cause2 in(1, 2) the or cause3 like"%a%"
Copy the code

Advanced operation

Data manipulation

League table query

select column1, column2 from table1 UNION select column1, column2 from table2; SELECT statements within a UNION must have the same number of columns. Columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.Copy the code

The sorting

SELECT * from runoob_tbl ORDER BY submission_date ASC; SELECT * from runoob_tbl ORDER BY submission_date DESC; // in descending orderCopy the code

grouping

We can use COUNT, SUM, AVG, etc. on grouped columns. SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | COUNT (*) | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | small beautiful | 1 | | xiaoming | 3 | | wang | 2 | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + //WITH ROLLUP (SUM,AVG,COUNT...) SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | singin_count | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | small beautiful | 2 | | xiaoming 7 | | | | wang 7 | | NULL | 16 | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + / / we can use coalesce to set a name can replace NUll, coalesce grammar SELECT coalesce (name,'total'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, 'total') | singin_count | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | small beautiful | 2 | | xiaoming 7 | | | | wang 7 | | | | 16 total +--------------------------+--------------+Copy the code

League table

Syntable – inline
select * from T1 inner join T2 on T1.userid=T2.userid; / / inlineCopy the code

Linkage – left linkage
select * from T1 left outer join T2 on T1.userid=T2.userid; / / inlineCopy the code

Union table – right union
select * from T1 right outer join T2 on T1.userid=T2.userid; / / inlineCopy the code

Linkage form – Full linkage
select * from T1 full outer join T2 on T1.userid=T2.userid; / / inlineCopy the code

Adding indexes

ALTER TABLE is used to create a normal index, a UNIQUE index, or a PRIMARY KEY index. ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list) CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)Copy the code

Remove the index

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
Copy the code

View index

show index from tblname;
Copy the code