Home  >  Article  >  Database  >  mysql check

mysql check

黄舟
黄舟Original
2017-01-16 13:14:201147browse

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 and2d90e2277c50a045dd3c6e6cd3ac1628=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 &#39;诺基亚%&#39;;

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

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

Replace Nokia with htc (change the real table content)

update goods 
set goods_name = concat(&#39;htc&#39;,substring(goods_name,4))
where goods_name like &#39;诺基亚%&#39; 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)!


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn