mysql check

Get how many rows there are in the table
Tips:

select count(*) from table_name;

Get the columns with cat_id=4 and cat_id=11
Use or select * from goods where cat_id=4 or cat_id=11 ;
Do not use or select * from goods where cat_id in(4,11);

Get the price>=100 and<=500

select * from goods where shop_price >= 100 and shop_price <= 500;
select * from goods where shop_price between 100 and 500;

Get the price< ;=100 and>=500

select * from goods where shop_price <=100 and shop_price >= 500;
select * from goods where shop_price not between 100 and 500;

in is the set of scatter points, between and is the interval

cat_id is not a column of 3 or 11

select * from goods where cat_id!=3 and cat_id!=11;
select * from goods where cat_id not in(3,11);

Calculate the discount value than the market price

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods ;

Find the local price that is more than 200 cheaper than the market price

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods where (market_price - shop_price) > 200;

(The chajia column is generated after where has been used)

Doubtful points note: where works on the data in the real table, and having can filter the where results

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods where chajia > 200;(错误的)

The same effect

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods having chajia>200;

Change [20,29] in the num column in the main table to 20 [30,39] to 30

update mian set num = floor(num/10)*10 where num between 20 and 39;

likeFuzzy query

Intercept the content after Nokia

select goods_id ,goods_name,substring(goods_name,4) from goods where goods_name like '诺基亚%';

Find the content starting with Nokia and replace it with htc (no change to the real table content)

select goods_id ,goods_name,concat('htc',substring(goods_name,4)) from goods where goods_name like '诺基亚%';

Replace Nokia with htc (change the real table content)

update goods 
set goods_name = concat('htc',substring(goods_name,4))
where goods_name like '诺基亚%' and cat_id=4;

The above is the content of mysql query. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!