mysql基本语法

• mysql-sql 语句
• 字符集选utf-8
• 我需要学增删改查, 事物, 联合

---

启动数据库

<pre><code>
mysql -u root -p（root是用户名）
</code></pre>

查看数据库（所有）

<pre><code>
show databases
</code></pre>

进入数据库

<pre><code>
use one ;
</code></pre>

展示当前数据库的所有表

<pre><code>
show tables;
</code></pre>

创建个名为user的表的结构

<pre><code>
create table user(
id int,
name varchar(30),
pass varchar(30)
);（字符串长度最长是30）
</code></pre>

查看表结构

<pre><code>
desc user;
</code></pre>

查看数据从表里面

<pre><code>
select * from user;
</code></pre>

insert 增（我可以随意增加，插入数据到表中）

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

形成了下表

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

select 查（我可以随意查找 select from table where...）

<pre><code>
select * from user where id=2;
select * from user where pass=13;
</code></pre>

select like 子段（ 我可以随意选取子字段 ）

select * from user where name like '%carry%';//选取中间含有carry字段的数据
+------+----------+------+
| id   | name     | pass |
+------+----------+------+
|    5 | carryone | 123  |
|    2 | carry    | 571  |
+------+----------+------+

选取以one结束的字段

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

select order by 排序（我们可以随意排序数据）

<pre><code>
select * from user order by name; //默认是升序
select * from user order by id desc;//desc为降序排列
</code></pre>

delete 删 （我可以随意删除 delete from table where...）

<pre><code>
delete from user where name="yujie";
delete from user where id=3;
</code></pre>

update (我可以随意更改 update user set charct where ....)

<pre><code>
update user set name="billin" where id=1;
update user set id=5 where name="billin";
</code></pre>

inner/left/right join 联合，内联，左联，右联

 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的外键

链接：https://www.jianshu.com/p/30b139ab0c2b