- 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
);
实现的效果
切记
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