• 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

  • 相关阅读:
    KMP算法
    IEEE754 32位浮点数表示范围
    银行家算法
    字符串类
    栈类
    稀疏数组类
    队列类
    多维数组类
    单向链表类
    哈希表类
  • 原文地址:https://www.cnblogs.com/guantou1992/p/12779868.html
Copyright © 2020-2023  润新知