MySQL Learning basic grammar

--Mysql Database syntax

Create DATABASE Database name

CREATE TABLE table name (ID int,name varchar (20));

Copy tables and data the new table name after the CREATE table is copied select * from the old table name;

Duplicate table structure CREATE table after the new table name select * from where 1=0;

Creating an index create the index name on the table name (column name);

CREATE view name as select Column Name 1, column name 2, ...;

Delete table name of the drop tables table;

Shows the structure of the table describe table name;

Modify table name ALTER TABLE name rename the name of the tables to be modified;

Create column ALTER TABLE name add column name data type;

Delete Column ALTER TABLE name drop column name;

Modify the column name or data type ALTER TABLE name change column name new column name old data type;

Modify data type ALTER TABLE name modify column name new data type;

Delete Index drop the index name on table name; ALTER TABLE name DROP INDEX name;

Primary KEY constraints such as: The first method ID int primary key;
The second method is to write primary key (ID) at the end of the table content;
Unique constraints such as: ID int unique;
Same as primary key ID int unique NOT NULL;
Default constraints such as: Gender char (3) Default ' male ';
Non-null constraint ID int NOT null;

Foreign key and foreign KEY constraints first: Create a foreign key that has a relationship with another table

then: constraint foreign KEY constraint name foreign key (created foreign key name-column name) references another table name (column name-must have a PRIMARY KEY constraint)

difference: There is a foreign key to indicate that there is a relationship between the two tables, there are foreign keys do not necessarily have foreign key constraints, there must be foreign key constraints.
Delete data first delete from table data Delete from table name where column name = column name;
Delete Main Table data delete from table name where column name = column name;

--External write primary key, foreign key, FOREIGN KEY constraint


Add auto-grow (primary key only) ALTER TABLE list name 1 column name 1 data type auto_increment;
Change Auto-growth initial value ALTER TABLE name auto_increment=1000;
Delete auto grow ALTER TABLE name change column name 1 column name 1 data type;
Add PRIMARY KEY constraint first ALTER TABLE name ADD constraint primary key (column name);
The second ALTER TABLE name change column name 1 column name 1 data type primary key;
Delete primary KEY constraint (automatic growth must be deleted first) ALTER TABLE name drop PRIMARY key;
add unique constraint ALTER TABLE name add unique (column name 2);
delete unique constraint ALTER TABLE name DROP index column name 2;
add default constraint ALTER TABLE name change column name 2 column name 2 data type default ' male ';
DELETE default constraint ALTER TABLE name change column name 2 column name 2 data type;
add non-null constraint ALTER TABLE name change column name 2 column name 2 data type not NULL;
Delete non-null constraint ALTER TABLE name change column name 2 column name 2 data type;
add FOREIGN KEY constraint ALTER TABLE name ADD constraint foreign KEY constraint name foreign key (foreign key column name) references another table name (column name);
Delete foreign KEY constraint ALTER TABLE name drop FOREIGN key foreign KEY constraint name;

add data insert into table name (corresponding column: Comma separated) VALUES (data 1, data 2,*******);
shorthand: INSERT into table name values (data 1, data 2,****);
Modify Data Update table name set column name = new data where pk_id=2 (conditions of accession);
when handling null values: Update table name set column name = new data where column name is null;
Delete Data only: Update table name set column name = NULL where pk_id=2 (join condition);
Delete from table name where pk_id=2 (deleted condition) can be restored after deleting data deleted;
truncate table name;

--Query data syntax


query all information in the table select Column Name 1, column name 2 from table name;
select * from table name;
table alias Select table alias. ID as ' number ', table alias. Name as ' name ' from table name as ' table alias ';

column alias Select ID as ' number ', name as ' name ' from table name;
computed column Select Age as ' current age ', age+10 as ' 10 years old ' from table name;
display data in a column (string concatenation function) Select concat (Name, "", age) from table name;
Calculate Length function Select Length ("Content");
multiple columns to repeat select DISTINCT column name 1, column name 2 from table name;
Paging (limit) select * from table name limit 2; A parameter that represents the result of returning the specified number of rows starting from the first line.
select * FROM table name limit 2, 5, two parameters, indicating the result of returning the specified number of rows starting from the specified line;
Pagination formula (pages-1) * Number of bars per page showing data

between and (between what includes upper and lower bounds) select * from table name where column name between and 20; Check for people aged 18--20 (including 18)

In (Inside what) SELECT * from table name where column name in (17,20,25); Find people who are 17,20,25 years old

SELECT * FROM table name where age=17 or age=20 or age=20;

not in (what is not inside) SELECT * from table name where age!=15 and age!=30; query age is not 15 and 30 years old person;
select * FROM table name where age not in (15,30);
Like fuzzy query select * FROM table name where column name like ' Zhang _ '; query surname Zhang's (two words) classmate's information
select * FROM table name where column name like ' __ '; query for student's information with name two characters
select * FROM table name where column name like ' li% '; inquire about students ' information with the name of Li
select * FROM table name where column name like '% Zhao '; query name contains information about the students of Zhao Zi
query NULL is a SELECT * from table name where column name is null; query the information of the classmate whose name is null
sort ORDER BY select * FROM table name order BY age ASC; information about classmates from small to large
select * FROM table name ORDER BY age Desc; ages from big to small arrange classmates information
Multi-column sort select * from table name ORDER by age Desc,chengji desc; ages from large to small, grades from high to low;

execution steps: 1:from 2:where 3:select 4:order by

--functions and groupings

The count (*) statistic includes the null-worthy row SELECT COUNT (*) from the table name;
count (all column name) statistics do not include null for the row select COUNT (all column name) from table name;
count (distinct column name) statistics do not include null rows, and go to duplicate select COUNT (distinct column name) from table name;
sum (column name) sum of a single column select SUM (column name) from table name;
SUM (distinct column name) sums a single column (only once) select SUM (distinct column name) from table name;
avg (column name) to find the average of a column select AVG (column name) from table name;
Max (column name) find the maximum value for a column select MAX (column name) from table name;
min (column name) find the minimum value of a column select min (column name) from table name;
round (, 2) retains several decimals select round (AVG (column name 1), 2) from table name, indicating that two decimal places are reserved for column name 1 after averaging

Group by select Column Name 1, column name 2, aggregate function from table name where filter condition group By column name 1, column name 2;
Having and where difference where running before grouping, having run after grouping


SQL statement Execution order 1 from 2 where 3 Group by (Group) 4 Select (projection) 5 have 6 order by (sort).

string-To-integer: atoi (name to be modified);
Integer-To-string: itoa (new string name, name to be modified, 10);
float-to-string: GCVT (new string name, number of digits reserved, name to change);

MySQL Learning basic grammar