MySQL basic SQL statement

Game programming 2022-05-14 14:41:31 阅读数:635

mysqlbasicsqlstatement
mysql basic SQL sentence - The first 1 Zhang

This article mainly introduces Mysql Database sql Basic statements and functions , And databases 、 Common operation skills such as data sheet , Friends in need can refer to

mysql basic SQL sentence

1、 Common command
mysql -u root -p # Connect to database command
show databases; # List all databases
use test; # Get into test database
desc user;# View database table structure command
create database Database name ; # Create a database file
2、 Data types and constraints
effect : In order to store data more accurately , Ensure the validity of data , Data types and constraints need to be combined to limit the storage of data
data type :
integer :int
decimal :decimal
character string :varchar
Date time :datetime
constraint :
Primary key :primary key: The order of physical storage
Generally, when setting the primary key , Often use id As field name , General requirements for setting primary keys , Integers / Unsigned / Auto increment / Can't be empty
When there is a primary key , The physical order in the table is fixed , When the data is cleared , The order of new data will not change from 1 Start counting , Instead, continue recording from the next bit in the order in which the data was previously stored, which can ensure that the data is unique in order .
Non empty :not null : This field does not allow null tuning
only :unique: Duplicate... Is not allowed in this field
The default value is default : When this value is not filled in, the default value is used , If it is filled in, the filling shall prevail
Foreign keys : foreign key : Maintain the association between two tables
3、SQL Language
select * from users; ------------ Be careful :sql Statement is case insensitive , The end of the statement needs to be followed by a semicolon
create database foo; Create database foo
1) Database table creation statement :
drop table if exists goods; Delete the table if it exists
create table goods(
goodsName varchar(20),
price decimal(4,2)
)
2) Create database tables

 1 -- demand Create a product list Fields including id( Primary key ), Name of commodity Price Number company North Lake 2 drop table if exists goods; 3 create table goods ( 4 id int unsigned primary key auto_increment, 5 goodsName varchar(20), 6 price decimal(4,2), 7 num int, 8 company varchar(20), 9 remark varchar(30)10 );

3) insert data

-- The primary key column is self growing , Space occupation is required during insertion , Usually use 0 perhaps default perhaps null Come and take the place , After the insertion is successful, the actual -- Data shall prevail -- grammar : insert into table_name values (),();-- Number of inserted data Must match the number of fields , The data type should also correspond to -- Be careful : When inserting multiple data , Each piece of data is separated by commas insert into goods values(0,'laptop',4700,20,'HUAWEI','china'),(0,'pad',2500,10,'HUAWEI','china'),(0,'smartphone',4500,30,'HUAWEI','china');-- Insert specified field data insert into goods(goodsName) values('watch');

4) Data modification

-- Add watch Information about -- Change the data type of a field in a table When modifying data You must filter by criteria where , update To back up the data table alter table goods modify price decimal(6,2);update goods set price = 4700,num=29,company='HW',remark='USA' where id =4;

5) Delete a piece of data

-- Delete a piece of data -- grammar : delete from table_name where id = 3delete from goods where id=3;

6) Change table structure alter keyword alter table table_name add Field name type ; # Add field type
alter table table_name modify Field name type ;# Change field type

-- Add field type alter table goods add is_delete int;-- Change field type alter table goods modify is_delete varchar(20);

7) Three ways to delete data
-- delete from Table name ; Empty data , Just clear the data , Keep the table structure , But the primary key count will not be reset
-- truncate table Table name ; Clear table data ( Just clear the data , Retention structure ), And reset the primary key count
--drop table Table name ; Delete table , Including table structure and data
-- Delete speed drop > truncate > delete

mysql basic SQL sentence - The first 2 Zhang

4、 Inquire about SQL sentence ( a key )
1)-- The basic query : Query all 、 Query the specified field 、 names 、 duplicate removal

-- The basic query : Query all 、 Query the specified field 、 names 、 duplicate removal select * from goods;select goodsName,price from goods;select goodsName as g ,price as p from goods;-- duplicate removal According to the given fields , Merge the data with the same data in the field and keep one -- demand How many companies are there at present select distinct(company) from goods;

2)-- Conditions of the query : Comparison operations / Logical operators

-- demand 4: The inquiry price is equal to 30 And from all the goods of pinduoduo -- Conditions of the query where Conditions select * from goods where price = 20; -- When the query has multiple conditions Use and or select * from goods where price= 20 and company = ' Spell the sunset ';-- Comparison operator The query is greater than 3000 The goods select * from goods where price > 3000;

3) Fuzzy query like % Match any number of characters
-- grammar where Field like '% Information %' _ Match a single character

-- Fuzzy query where Field like '% Information %' % Match any number of characters ,_ Match a single character -- Fuzzy query Pay attention to setting conditions , Avoid missing data select * from goods where remark like '%c%';

4) Range queries between Numbers and Numbers ; in Conditional query range Indicates that in a discontinuous range

select * from goods where price between 300 and 3000;select * from goods where price in (20,300,2500);

5) Empty judgment is null ; is not null;
-- Be careful null And '' It's different ,null It's empty ,'' It's an empty character

select * from goods where remark is null;select * from goods where price is not null;

6) Sort
-- demand 8 Query all product information , Sort according to the price from large to small , When the price is the same , Sort according to the number from less to more
-- Sort : order by Field name asc( Ascending )/desc( Descending )

-- Sort by price ascending order By default order by It is arranged in ascending order select * from goods order by price asc;-- In ascending order of price , If the price is the same, it shall be in accordance with num null select * from goods order by price asc,num desc;

7) Aggregate functions
-- demand 9: Check the information : Total number of commodity information ; The highest commodity price ; The lowest commodity price ; The average price of goods ;pad Total quantity

select count(*) from goods;select max(price) from goods;select avg(price) from goods;select min(price) from goods;select count(*) from goods where goodsName = 'pad';

8) grouping
-- demand 10: Check the quantity of goods in each company
-- grammar group by Field ; Data can be grouped according to the given field data
-- In general , Grouping needs to be used together with aggregation functions , The purpose is to make further statistics on the grouped data

-- demand 10: Check the quantity of goods in each company -- grammar group by Field ; Data can be grouped according to the given field data -- In general , Grouping needs to be used together with aggregation functions , The purpose is to make further statistics on the grouped data select * from goods;select company ,count(*) from goods group by company;-- After grouping, filter the conditions select company from goods group by company;-- Inquire about A company that doesn't have a sunset company select company from goods group by company having company != ' Spell the sunset ';-- Get the most expensive price of the company's goods select company , max(price) from goods group by company;

9) Paging query
-- demand 11: Query the... In the current table 5-10 All the data of the row
-- Pagination Use :limit Starting index , Number of data lines
-- Indexes : The index in the computer is from 0 At the beginning

-- demand 11: Query the... In the current table 5-10 All the data of the row -- Pagination Use :limit Starting index , Number of data lines -- Indexes : The index in the computer is from 0 At the beginning select * from goods;select * from goods limit 2,4; # Start with the second line Inquire about 4 Row data select * from goods limit 0,1; # Get the first row of data -- Paging query : The formula limit (n-1)*m,m; m Ask for the data to be displayed for each page ,n To display page n Pages of data -- The essence of this paging Namely limit The starting index of , Display row number select * from goods limit 6,2; # Each page shows 2 That's ok , According to the first 4 Page data 
-- demand Create a product list Fields including id( Primary key ), Name of commodity Price Number company North Lake drop table if exists goods;create table goods ( id int unsigned primary key auto_increment, goodsName varchar(20), price decimal(6,2), num int, company varchar(20), remark varchar(30));-- The primary key column is self growing , Space occupation is required during insertion , Usually use 0 perhaps default perhaps null Come and take the place , After the insertion is successful, the actual -- Data shall prevail -- grammar : insert into table_name values (),();-- Number of inserted data Must match the number of fields , The data type should also correspond to -- Be careful : When inserting multiple data , Each piece of data is separated by commas insert into goods values(0,'laptop',4700,20,'HUAWEI','china'),(0,'pad',200,10,' Spell the sunset ','china'),(0,' garlic ',20,10,' Spell the sunset ','china'),(0,' Spoon ',20,10,' TaoBao ','china'),(0,' Gym shoes ',300,10,' Goudong ','china'),(0,' Treadmill ',1500,10,' Goudong ','china'),(0,'pad',2500,10,'HUAWEI','china'),(0,'smartphone',4500,30,'HUAWEI','china');-- Insert specified field data insert into goods(goodsName) values('watch');-- demand 2 Modify product data , Modify product data 1 strip , Delete 1 Item data -- modify -- First query all the data , Easy to observe the data content select * from goods;show variables like '%char%';set character_set_database=utf8;set character_set_filesystem=utf8;show create table goods;default-character-set= utf8;alter table goods default character set utf8 collate utf8_general_ci;-- Add watch Information about -- Change the data type of a field in a table alter table goods modify price decimal(6,2);update goods set price = 4700,num=29,company='HP',remark='USA' where id =4;rollback;-- Delete a piece of data -- grammar : delete from table_name where id = 3delete from goods where id=3;-- Add field type alter table goods add is_delete int;-- Change field type alter table goods modify is_delete varchar(20);-- The basic query : Query all 、 Query the specified field 、 names 、 duplicate removal select * from goods;select goodsName,price from goods;select goodsName as g ,price as p from goods;-- duplicate removal According to the given fields , Merge the data with the same data in the field and keep one -- demand How many companies are there at present select distinct(company) from goods;-- demand 4: The inquiry price is equal to 30 And from all the goods of pinduoduo -- Conditions of the query where Conditions select * from goods where price = 20; -- When the query has multiple conditions Use and or select * from goods where price= 20 and company = ' Spell the sunset ';-- Comparison operator The query is greater than 3000 The goods select * from goods where price > 3000;-- Fuzzy query where Field like '% Information %' % Match any number of characters ,_ Match a single character -- Fuzzy query Pay attention to setting conditions , Avoid missing data select * from goods where remark like '%c%';select * from goods where price between 300 and 3000;select * from goods where price in (20,300,2500);select * from goods where remark is null;select * from goods where price is not null;-- Sort by price ascending order By default order by It is arranged in ascending order select * from goods order by price asc;-- In ascending order of price , If the price is the same, it shall be in accordance with num null select * from goods order by price asc,num desc;-- demand 9: Check the information : Total number of commodity information ; The highest commodity price ; The lowest commodity price ; The average price of goods ;pad Total quantity select count(*) from goods;select max(price) from goods;select avg(price) from goods;select min(price) from goods;select count(*) from goods where goodsName = 'pad';-- demand 10: Check the quantity of goods in each company -- grammar group by Field ; Data can be grouped according to the given field data -- In general , Grouping needs to be used together with aggregation functions , The purpose is to make further statistics on the grouped data select * from goods;select company ,count(*) from goods group by company;-- After grouping, filter the conditions select company from goods group by company;-- Inquire about A company that doesn't have a sunset company select company from goods group by company having company != ' Spell the sunset ';-- Get the most expensive price of the company's goods select company , max(price) from goods group by company;-- demand 11: Query the... In the current table 5-10 All the data of the row -- Pagination Use :limit Starting index , Number of data lines -- Indexes : The index in the computer is from 0 At the beginning select * from goods;select * from goods limit 2,4; # Start with the second line Inquire about 4 Row data select * from goods limit 0,1; # Get the first row of data -- Paging query : The formula limit (n-1)*m,m; m Ask for the data to be displayed for each page ,n To display page n Pages of data -- The essence of this paging Namely limit The starting index of , Display row number select * from goods limit 6,2; # Each page shows 2 That's ok , According to the first 4 Page data 

At the end

This is a super comprehensive resource compiled by me and my friends , There's always one for you , The way to get my keyword “ Information ” You can get it for free !

mysql basic SQL sentence - The first 3 Zhang
mysql basic SQL sentence - The first 4 Zhang

This post ends here , Last , I hope the friends who read this post can gain something . Welcome to leave a message , Or follow my column and communicate with me .
author : Software testing Van Gogh

Game programming , A game development favorite ~

If the picture is not displayed for a long time , Please use Chrome Kernel browser .

版权声明:本文为[Game programming]所创,转载请带上原文链接,感谢。 https://javamana.com/2022/134/202205141435170770.html