MySQL Program code Build table (1)

First, create a table code format

CREATE table < table name >
(
< column name > < data type and length > [NOT NULL],
< column name > < data type and length;
...
< column name > < data type and length >
)

Cases:

#人员基本信息表
CREATE TABLE info//Create tables named info
(
Xuhao int PRIMARY Key auto_increment,//Build column named Xuhao, data type integer type, set as primary key and self-growing arrangement
Name varchar (50),//column named Name, data type is string type, length is 50 characters
Sex bit,//build column named Sex, data type Boolean
Minzu varchar (50),//Build column named Minzu, data type is string type, length is 50 characters
Foreign KEY (Minzu) references Nation (code),//Create a foreign key for the Minzu column, and use the Code column with the table name Nation as the constraint
#minzu varchar (references) Nation (code),//Build foreign key in another format, but after running, the outer key is empty
Shengri datetime,//Build column named Shengri, data type is time type
Height double (6,2)//Build column named height, data type is decimal type, total length is 6, leaving 2 digits after decimal point
);

Command statement:

Primary KEY Primary Key
Not NULL non-null
Foreign KEY (Nation) references Nation (Code) foreign key
Auto_increment Self-growth

Note: All symbols must be in the English state
Add a semicolon after each table is created
Do not add a comma after the last column in the table is finished

Second, delete the table code format

DROP table < table name >

Third, the operation of data in the table

Can be divided into increase, delete, change, check CRUD

1. Add Data
INSERT into < table name >[(column 1, column 2 ...) )] VALUES (< ' value 1 ' >[, ' value 2 ' ...])
Attention:
(1) Columns and values to match (quantity, type, sort)
(2) The column can be omitted, but the value must correspond exactly to the total number of columns and the order of the columns in the table
(3) Self-growth column name can be saved, but its value location cannot be saved, do not give explicit value to the self-increment column assignment '

Cases:

Insert into info (Name,minzu,shengri) VALUES ("', ' Zhang San ', ' n003 ', ' 1992-2-2 ')//Add the name Zhang San to the Info table, the Nation is n003, and the birthday is 1992-2-2" Increase the order of the self-growth columns, the remaining column is null

2. Delete data

Delete from Brand//Remove all data

Delete from car where code= ' c001 '//delete table data for rows in car code column c001
Delete from car where brand= ' b001 ' or brand= ' b004 '//delete table car data for b001 and b004 rows
Delete from car where brand= ' b001 ' | | Brand= ' b004 '//delete table The data of brand listed in car as b001 and b004 rows
Delete from car where brand= ' b007 ' && price>50//delete table The data of brand listed in car as b007 and price column is greater than 50 rows
Delete from car where brand= ' b007 ' and price>50//delete table The data of brand listed in car as b007 and price column is greater than 50 rows

3. Change data

Update < table name > Set < column = value >[, column = value ...] where .....

Cases:
Update info set sex= ' 1 ' where code= ' p003 '//change the data in the sex column of the code column in the info table to the row of p003 to 1
Update info set sex= ' 0 ', nation= ' n004 ', birthday= ' 1999-9-9 ' where code= ' p001 '//Change the sex column of the code column in the info table to 0,                                                                                                                       Nation column changed to N004,birthday column For 1999-9-9
Update car Set Price=price * 0.9 where price > 30//The data of price column greater than 30 in the car table is multiplied by 0.9
Update car set price =price * 0.95 where (brand= ' b006 ' | | brand= ' b005 ') &&price>30//The car table is listed as b006 and b005 and                                                                                                                                The price column of price column greater than 30 times the data 0.95

4. Finding data

SELECT * FROM table name
Select Column Name 1, column Name 2 ... from table name--Projection
SELECT * FROM table name where condition--filter

(1) Equivalence and non-equivalence
SELECT * from car where code= ' c001 '; Find data from the car table for rows with the code column c001
SELECT * FROM car where code! = ' c001 '; To find data for rows that are not c001 in the Code column from the car table
SELECT * from car where price > 30; Finding data for rows with a price column greater than 30 from the car table
--The following are the ranges
SELECT * from car where price >=30 && price <=50; Find data from the car table for rows with a price column greater than or equal to 30 and less than or equal to 50
SELECT * from car where price between and 50//Find data from the car table for rows with a price column greater than or equal to 30 and less than or equal to 50
SELECT * from car where brand= ' b002 ' | | brand= ' b004 ' | | Brand= ' b006 '//Find the data for brand listed as b002, b004, b006 rows from the car table
SELECT * FROM car where brand in (' b002 ', ' b004 ', ' b006 ')//Find data from the car table for rows that are listed as b002, b004, b006

SELECT * FROM car where brand isn't in (' b002 ', ' b004 ', ' b006 ')//Find data from the car table for rows not b002, b004, b006

(2) Fuzzy search
SELECT * from car where name ' BMW% '%--any number of any characters//find all data starting with "BMW" from the car table
SELECT * from car where name like '%5% '//from car table find all data in middle containing ' 5 '
SELECT * from car where name like '% type '//Find all data that ends with "type" from the car table
SELECT * from car where name like ' __5% '///from car table find the third digit is "5" all Data _ (space) denotes an arbitrary character

(3) Sort
SELECT * FROM table name where .... order BY column name [ASC/DESC], column name [Asc/desc] ....

SELECT * FROM car ORDER BY price DESC//The car table data is sorted in descending order of price column data
SELECT * FROM Car ORDER BY brand Desc,price ASC//The car table data is sorted in descending order of brand data, and price column data is sorted in ascending order

MySQL Program code Build table (1)