mysql基本語法

mysql -u root -p（root是使用者名稱）

show databases

use one ;

show tables;

create table user(
id int,
name  varchar(30),
pass varchar(30)
);（字串長度最長是30）

desc user;

select * from user;

insert into table(ct1,ct2,ct3) values(num,"str","str")
insert into user(id,name,pass) values(1,"leiwei","123")

mysql> select * from user;
+------+-----------+------+
| id   | name      | pass |
+------+-----------+------+
|    1 | leiwei    | 123  |
|    2 | yujie     | 13   |
|    3 | qiancheng | 456  |
+------+-----------+------+
3 rows in set (0.00 sec)

select * from user where id=2;
select * from user where pass=13;

select * from user where name like '%carry%';//選取中間含有carry欄位的資料
+------+----------+------+
| id   | name     | pass |
+------+----------+------+
|    5 | carryone | 123  |
|    2 | carry    | 571  |
+------+----------+------+

select * from user where name like '%one';//
+------+----------+------+
| id   | name     | pass |
+------+----------+------+
|    5 | carryone | 123  |
+------+----------+------+

select * from user order by name; //預設是升序 select * from user order by id desc;//desc為降序排列

delete from user where name="yujie";
delete from user where id=3;

update user set name="billin" where id=1;
update user set id=5 where name="billin";

select a.pass,b.money from user a left join customer b on a.name = b.name;//左連 select a.pass,a.name,b.money from user a right join customer b on a.id = b.id;//左聯 select a.pass,a.name,b.money from user a inner join customer b on a.name = b.name;//內聯

select gbl_products.productname, gbl_stocks.areaid, gbl_stocks.amounts, gbl_stocks.stocks_standard, gbl_product_barcodes.barcode, gbl_md5.image from gbl_products left join gbl_stocks on gbl_products.id = gbl_stocks.productid left join gbl_product_barcodes on gbl_products.id = gbl_product_barcodes.productid left join gbl_md5 on gbl_products.productimage = gbl_md5.md5 where gbl_stocks.amounts <= ( gbl_stocks.stocks_standard / 2

);

lala

1:from 後面跟一個表的名字，且這個表為主表

2: （on duplicate key update）檢視有沒有，沒有就插入，有就更新

insert into gbl_stocks (
gbl_stocks.productid,
gbl_stocks.areaid,
gbl_stocks.amounts,
gbl_stocks.stocks_standard
)values
(?,?,?,?) on duplicate key update gbl_stocks.amounts = gbl_stocks.amounts +values(gbl_stocks.amounts),
gbl_stocks.stocks_standard = 
`if` (
gbl_stocks.amounts +values(gbl_stocks.amounts) > gbl_stocks.stocks_standard,gbl_stocks.amounts +values(gbl_stocks.amounts),gbl_stocks.stocks_standard
)

values(gbl_stocks.amounts)中,括號裡面是一個可變化的動態的值，一般為讀取到的值

if(a> b,c,d);

意思為：

如果條件成立（a>b）,則返回c,反之返回d

主鍵是一個表中具有唯一標識性的欄位，其它的屬性欄位都根據主鍵來存在的，例如學號

mysql-外來鍵：表a和表b都有一個相同的欄位c，c是表a的主鍵，c不是表b 的主鍵，表b的欄位c相對於表a是表b的外來鍵

mysql基本語法

mysql基本語法

mysql基本語法1

mysql基本語法

相關推薦