《mysql必知必会》笔记（三)

最新推荐文章于 2024-06-14 10:04:32 发布

dazhuangzhang

最新推荐文章于 2024-06-14 10:04:32 发布

阅读量887

点赞数

分类专栏： Mysql

Mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

十九：插入数据

1：insert语句用来将行插入数据表中，可以插入完整的行、行的一部分、插入多行、插入某些查询的结果。

2：不指定列名，可以这样插入：

insert into customers

values(null,

‘pep e. lapew’,

‘100 main street’,

‘los angeles’,

‘ca’,

‘90046’,

‘usa’,

null,

null);

这种形式中，每个列必须提供一个值，如果某个列没有值，则使用null。每个列按照他们在表中定义中出现的次序填充。因为cust_id可以自动增加，所以可以为。

这种形式虽然简单，但是不安全，并且高度依赖于表中列的定义次序，所以要避免使用。

3：更安全的使用insert的方法是：

insert into customers(cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email)

values(‘pope lapew’,

‘100 main street’,

‘los angels’,

‘ca’,

‘90046’,

‘usa’,

null,

null);

表名后面的括号中，明确给出了列名，在插入行时，mysql将用values列表中的相应值填入列表中的对应项。values必须以指定的次序匹配指定的列名。指定列名后，必须在values中给出每个列的值。

使用这种语法，还可以省略某些列，比如上例中cust_id就省略了。省略的列必须满足两个条件中的一个：该列定义时允许NULL值；在表定义中给出默认值。

4：insert操作可能很耗时，特别是有许多索引需要更新时，而且他还可能降低select的性能。如果一系列操作中，select操作很重要，则可以使用low_priority关键字，指示mysql降低insert语句的优先级，比如：insert low_priority into 表名…

5：如果需要插入多个行，可以使用多条insert语句。也可以使用一条insert语句，比如：

insert into customers(cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)

values(‘pop e lapew’,

‘100 main street’,

‘los angels’,

‘ca’,

‘90046’,

‘usa’),

(

‘m martian’,

‘42 galaxy way’,

‘new york’,

‘ny’,

‘11213’,

‘usa’);

这样处理，可以提高数据库处理的性能，使用一条insert语句进行多行插入比多条insert语句更快。

6：insert还可以将一条select语句的结果插入表中，也就是insert select语句，比如：

insert into customers(cust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)

select cust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

from custnew;

这个例子，使用insert select将custnew中所有数据导入到customers中。select语句从custnew中检索出需要插入的值，select中列出的每个列对应于customers表名后所跟的列表中的每个值。

此例中，insert和select语句使用了相同的列名。但是，不一定要求列名匹配，mysql不关心select返回的列名，他使用的是列的位置。因此select中的第一列，不管其列名，将用来填充表列中指定的第一个列，如此类推。

insert select中，select语句可包含where子句来过滤需要插入的数据。

二十：更新和删除数据

1：更改表中的数据，使用update语句，可以更新特定的行，也可以更新所有行。在使用update时一定要细心，因为稍不注意，省略了where子句，就会更新表中所有行。

2：比如:

update custnew

set cust_email = ‘hh@hh.com’

where cust_id = 10007;

update语句中where子句，告诉mysql更新哪一行。如果没有where子句，则mysql将会把表中所有行的cust_email值都改变，比如：

update custnew

set cust_email = ‘hh@hh.com’

结果如下：

3：如果需要更新多个列，则：

update custnew

set cust_name = ‘hh’,

cust_email = ‘hh@163.com’

where cust_id = 10007;

更新多个列，只需要单个set命令，每个key=value之间用逗号分隔。

4：如果用update语句更新多行，并且在更新过程中，其中一行或多行出现错误，则整个update操作将被取消，已经更新的行将恢复到它们原来的值。

如果需要即使发生错误，也继续更新，则可以使用ignore关键字，比如：update ignore customers…;

5：为了删除某个列的值，可设置它为NULL，比如：

update customers

set cust_email = NULL

where cust_id = 10005;

6：使用delete语句，可以删除表中特定行或所有行。使用delete语句需要小心，因为稍不注意，可能会删除表中所有行。

7：比如：

delete from custnew

where cust_id = 10007;

注意，如果没有where子句，则delete将删除表中的所有行。而且，delete子句删除的是整行，而不是某一列。

8：如果需要删除表中的所有行，不要使用delete，应该用truncate table语句，因为它更快。

9：使用update和delete时，一定注意不要遗忘了where子句，否则，将会影响到表中的所有行。

二十一：创建和操纵表

1：使用create table语句创建表，比如：

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL ,

cust_address char(50) NULL ,

cust_city char(50) NULL ,

cust_state char(5) NULL ,

cust_zip char(10) NULL ,

cust_country char(50) NULL ,

cust_contact char(50) NULL ,

cust_email char(255) NULL ,

PRIMARY KEY (cust_id)

)ENGINE=InnoDB;

表明紧跟在create table之后，实际的表定义在表名后面的圆括号之中。每个列以列名开始，后跟数据类型等。表的主键可以用primary key指定。

2：创建表时，指定的表必须不能存在，否则会出错。

3：如果某个列的定义中含有关键字NOT NULL，则将会阻止插入没有值的列，如果试图插入没有值的列，则返回错误。

NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。注意，NULL与空串不同。

4：如果需要指定多个列组成的主键，则语法如下：

create table orderitems

(

…

primary key (order_num,order_item)

);

5：使用auto_increment，可以在每当增加一行是自动增量。每个表只允许一个auto_increment列，而且它必须被索引。

在insert时，如果指定了值，则该值会覆盖自动生成的值。后续的增量将开始使用该手工插入的值。

last_insert_id()函数可以获得当前的增量值。

6：默认值使用default关键字指定。

7：创建表时，可以使用ENGINE=InnoDB;指定特定的引擎。

与其他的DBMS一样，MySQL有一个具体管理和处理数据的内部引擎，多数时候它隐藏在DBMS内，不需要过多的关注它。

但MySQL与其他的DBMS不一样，它具有多种引擎，它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能处理用户的请求。

为什么要发行多种引擎呢，因为他们具有各自不同的功能和特性，为了不同的任务选择正确的引擎能获得良好的功能和灵活性

当然你也可以省略这些数据库引擎，如果省略ENGINE=语句，则使用默认引擎(很可能为MyISAM)，多数sql语句都会默认使用它，但并不是所有的语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下几个需要知道的引擎

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

MEMORY 在功能上等同于MyISAM，但由于数据存储在内存，所以速度更快（特别适用于临时表）

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理.

8：更新表，使用ALTER TABLE语句，但是在理想状态下，当表中存储了数据后就不应该再去更新表了。所以在设计表时需要花费大量的时间去考虑，以便后期不做太大的改动。

比如：

添加一列 ALERT TABLE vendors ADD vend_phone CHAR(20);必须指明数据类型

删除一列 ALERT TABLE vendors DORP vend_phone;

9：删除表

DORP TABLE tablename；不可撤销，永久删除表

重命名表

RENAME TABLE table1 TO table2；多个表重命名逗号分隔开，比如：

rename table table1 to table11,

table2 to table12,

table3 to table13;

二十二：使用视图

1：mysql 5添加了对视图的支持。视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

2：比如下面的语句:

select cust_name, cust_contact from customers, orders, orderitems where

customers.cust_id= orders.cust_id and

orders.order_num= orderitems.order_num and

orderitems.prod_id= ‘TNT2’;

如果使用视图，可以将整个查询包装成一个名为productcustomers的虚拟表，则采用下面的查询即可得到相同的结果：

select cust_name, cust_contact from productcustomers where prod_id = ‘TNT2’;

这就是视图的作用，productcustomers是一个视图，作为视图，它不包含表中应该有的任何数据，它包含的是一个sql查询。

3：下面是视图的一些常见应用：

重用sql语句；

简化复杂的sql操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；

使用表的组成部分而不是整个表；

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据；

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图进行select操作，过滤和排序操作，将视图联接到其他视图或表，甚至能添加和更新数据。

重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此他们返回的数据是从其他表中检索出来的。

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。

4：视图的创建:

视图用create view语句来创建；

使用show create view viewname; 来查看创建视图的语句；

用drop删除视图，其语法为drop view viewname;

更新视图时，可以先用drop，再用create，也可以使用create or replace view；该语句的效果是：如果更新的视图不存在，则会创建一个视图；如果要更新的视图存在，则会替换原有视图。

5：上面的例子，使用视图的方法：

create view productcustomers as

select cust_name, cust_contact, prod_id

from customers, orders, orderitems

where customers.cust_id = orders.cust_id

and orderitems.order_num = orders.order_num;

productcustomers就是一个视图，可以使用下面的语句检索出订购了产品‘TNT2’的客户：

select cust_name, cust_contact fromproductcostomerswhere prod_id = ‘TNT2’;

6：可以用视图的方法，重新格式化检索出的数据，比如：

create view vendorlocations as

select concat(rtrim(vend_name), ‘(‘, rtrim(vend_country), ‘)’)

as vend_title

from vendors

order by vend_name;

select * from vendorlocation; 结果如下：

7：可以用视图过滤不想要的数据，比如可以过滤没有电子邮件的客户：

create view customeremaillist as

select cust_id, cust_name, cust_email

from customers

where cust_email is not null;

select * from customeremaillist; 结果如下：

8：可以将视图与计算字段结合，比如：

create view orderitemsexpanded as

select order_num,

prod_id,

quantity,

item_price,

quantity*item_price as expanded_price

from orderitems;

select * from orderitemsexpanded where order_num = 20005; 结果如下：

9：迄今为止，所有的视图都是和select语句使用的。然而，视图的数据是否能更新，这需要视情况而定。

通常，视图是可更新的，也就是可以对它们使用insert、update和delete。更新一个视图将更新其基表。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。如果mysql不能正确的确定被更新的基数据，则不允许更新。也就是说，如果视图定义中有以下操作，则不能进行视图的更新：分组；联接；子查询；并；聚集函数、distinct、导出列。

一般而言，应该将视图用于检索，而不是更新。

$(".MathJax").remove();

(function(){ var btnReadmore =

("#btn-readmore");
if(btnReadmore.length>0){
var winH = ("#btn-readmore"); if(btnReadmore.length>0){ var winH = $("#btn-readmore"); if(btnReadmore.length>0){ var winH =$ (window).height(); var articleBox =

("div.article_content");
            var artH = articleBox.height();
            if(artH > winH*2){
                articleBox.css({
                    'height':winH*2+'px',
                    'overflow':'hidden'
                })
                btnReadmore.click(function(){
                    articleBox.removeAttr("style"); ("div.article_content");            var artH = articleBox.height();            if(artH > winH*2){                articleBox.css({                    'height':winH*2+'px',                    'overflow':'hidden'                })                btnReadmore.click(function(){                    articleBox.removeAttr("style"); $("div.article_content"); var artH = articleBox.height(); if(artH > winH*2){ articleBox.css({ 'height':winH*2+'px', 'overflow':'hidden' }) btnReadmore.click(function(){ articleBox.removeAttr("style");$ (this).parent().remove(); }) }else{ btnReadmore.parent().remove(); } } })()

    <div class="p4course_target"><div style="" id="_cd9mpy79628"><iframe id="iframeu3501897_0" name="iframeu3501897_0" src="https://pos.baidu.com/pcam?conwid=900&amp;conhei=104&amp;rdid=3501897&amp;dc=3&amp;di=u3501897&amp;dri=0&amp;dis=0&amp;dai=2&amp;ps=12775x66&amp;enu=encoding&amp;dcb=___adblockplus&amp;dtm=HTML_POST&amp;dvi=0.0&amp;dci=-1&amp;dpt=none&amp;tsr=0&amp;tpr=1535681741658&amp;ti=%E3%80%8Amysql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A%E3%80%8B%E7%AC%94%E8%AE%B0%EF%BC%88%E4%B8%89%EF%BC%89%20-%20CSDN%E5%8D%9A%E5%AE%A2&amp;ari=2&amp;dbv=2&amp;drs=4&amp;pcs=885x823&amp;pss=1226x13838&amp;cfv=0&amp;cpl=3&amp;chi=1&amp;cce=true&amp;cec=UTF-8&amp;tlm=1535681765&amp;prot=2&amp;rw=845&amp;ltu=https%3A%2F%2Fblog.csdn.net%2Fgqtcgq%2Farticle%2Fdetails%2F40614423&amp;ltr=https%3A%2F%2Fblog.csdn.net%2Fgqtcgq%2Farticle%2Flist%2F10%3Ft%3D1&amp;ecd=1&amp;uc=1366x728&amp;pis=-1x-1&amp;sr=1366x768&amp;tcn=1535681766&amp;qn=b48479f2e0708983&amp;tt=1535681720205.45561.45562.45567" width="900" height="104" align="center,center" vspace="0" hspace="0" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" style="border:0;vertical-align:bottom;margin:0;width:900px;height:104px" allowtransparency="true"></iframe></div></div><script>window.p4sdk_enable_courseBox=true</script>        <a id="commentBox"></a>

    <form id="commentform">
        <input type="hidden" id="comment_replyId">
        <textarea class="comment-content" name="comment_content" id="comment_content" placeholder="想对作者说点什么"></textarea>
        <div class="opt-box"> <!-- d-flex -->
            <div id="ubbtools" class="add_code">
                <a href="#insertcode" code="code" target="_self"><i class="icon iconfont icon-daima"></i></a>
            </div>
            <input type="hidden" id="comment_replyId" name="comment_replyId">
            <input type="hidden" id="comment_userId" name="comment_userId" value="">
            <input type="hidden" id="commentId" name="commentId" value="">
            <div style="display: none;" class="csdn-tracking-statistics tracking-click" data-mod="popu_384"><a href="#" target="_blank" class="comment_area_btn">发表评论</a></div>
            <div class="dropdown" id="myDrap">
                <a class="dropdown-face d-flex align-items-center" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
                <div class="txt-selected text-truncate">添加代码片</div>
                <svg class="icon d-block" aria-hidden="true">
                    <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#csdnc-triangledown"></use>
                </svg>
                </a>
                <ul class="dropdown-menu" id="commentCode" aria-labelledby="drop4">
                    <li><a data-code="html">HTML/XML</a></li>
                    <li><a data-code="objc">objective-c</a></li>
                    <li><a data-code="ruby">Ruby</a></li>
                    <li><a data-code="php">PHP</a></li>
                    <li><a data-code="csharp">C</a></li>
                    <li><a data-code="cpp">C++</a></li>
                    <li><a data-code="javascript">JavaScript</a></li>
                    <li><a data-code="python">Python</a></li>
                    <li><a data-code="java">Java</a></li>
                    <li><a data-code="css">CSS</a></li>
                    <li><a data-code="sql">SQL</a></li>
                    <li><a data-code="plain">其它</a></li>
                </ul>
            </div>  
            <div class="right-box">
                <span id="tip_comment" class="tip">还能输入<em>1000</em>个字符</span>
                <input type="submit" class="btn btn-sm btn-red btn-comment" value="发表评论">
            </div>
        </div>
    </form>
</div>
        <div class="comment-list-container">
    <a id="comments"></a>
    <div class="comment-list-box">
    </div>
    <div id="commentPage" class="pagination-box d-none"></div>

</div>

查看表的字段结构

DESCRIBE categorys

这是查看表结构的一种简写(和上面的一样)

SHOW STATUS

显示服务器的…

        </div>
                </a>
</div>
                                <div class="recommend-item-box recommend-ad-box clearfix">
                <script type="text/javascript" src="//rabc1.iteye.com/production/source/pc3553.js?pkcgstj=jm"></script>
            </div>

                <div class="recommend-item-box recommend-box-ident type_blog clearfix" data-track-view="{&quot;mod&quot;:&quot;popu_387&quot;,&quot;con&quot;:&quot;,https://blog.csdn.net/sinat_28978689/article/details/56304150,BlogCommendFromGuangxin_2,index_2&quot;}" data-track-click="{&quot;mod&quot;:&quot;popu_387&quot;,&quot;con&quot;:&quot;,https://blog.csdn.net/sinat_28978689/article/details/56304150,BlogCommendFromGuangxin_2,index_2&quot;}" data-flg="true">
    <a href="https://blog.csdn.net/sinat_28978689/article/details/56304150" target="_blank" title="《<em>MySQL</em><em>必知</em><em>必会</em>》全书总结">
        <div class="content" style="width: 702px;">
            <h4 class="text-truncate oneline" style="width: 573px;">
                    《<em>MySQL</em><em>必知</em><em>必会</em>》全书总结              </h4>
            <div class="info-box d-flex align-content-center">
                <p class="avatar">
                        <img src="https://avatar.csdn.net/B/A/8/3_sinat_28978689.jpg" alt="sinat_28978689" class="avatar-pic">
                        <span class="namebox" style="left: -56.5px;">
                            <span class="name">sinat_28978689</span>
                            <span class="triangle"></span>
                        </span>
                </p>
                <p class="date-and-readNum">
                    <span class="date hover-show">02-21</span>
                    <span class="read-num hover-hide">
                        <svg class="icon csdnc-yuedushu" aria-hidden="true">
                            <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#csdnc-yuedushu"></use>
                        </svg>
                        5154</span>
                    </p>
                </div>
                <p class="content oneline" style="width: 702px;">
                        知识点回顾                   </p>
        </div>
                </a>
</div>
                    <div class="recommend-item-box recommend-box-ident type_blog clearfix" data-track-view="{&quot;mod&quot;:&quot;popu_387&quot;,&quot;con&quot;:&quot;,https://blog.csdn.net/anxixiaomu/article/details/79192412,BlogCommendFromGuangxin_3,index_3&quot;}" data-track-click="{&quot;mod&quot;:&quot;popu_387&quot;,&quot;con&quot;:&quot;,https://blog.csdn.net/anxixiaomu/article/details/79192412,BlogCommendFromGuangxin_3,index_3&quot;}" data-flg="true">
    <a href="https://blog.csdn.net/anxixiaomu/article/details/79192412" target="_blank" title="<em>MySql</em><em>必知</em><em>必会</em><em>笔记</em>">
        <div class="content" style="width: 702px;">
            <h4 class="text-truncate oneline" style="width: 582px;">
                    <em>MySql</em><em>必知</em><em>必会</em><em>笔记</em>             </h4>
            <div class="info-box d-flex align-content-center">
                <p class="avatar">
                        <img src="https://avatar.csdn.net/8/C/E/3_anxixiaomu.jpg" alt="anxixiaomu" class="avatar-pic">
                        <span class="namebox" style="left: -40px;">
                            <span class="name">anxixiaomu</span>
                            <span class="triangle"></span>
                        </span>
                </p>
                <p class="date-and-readNum">
                    <span class="date hover-show">01-29</span>
                    <span class="read-num hover-hide">
                        <svg class="icon csdnc-yuedushu" aria-hidden="true">
                            <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#csdnc-yuedushu"></use>
                        </svg>
                        182</span>
                    </p>
                </div>
                <p class="content oneline" style="width: 702px;">
                        1.限制条件

select pro_name
from products
limit 5
limit 5 指示MySQL返回不多于5行
select pro_name
from produ…

        <div class="recommend-loading-box">
            <img src="https://csdnimg.cn/release/phoenix/images/feedLoading.gif">
        </div>
        <div class="recommend-end-box">
            <p class="text-center">没有更多推荐了，<a href="https://blog.csdn.net/" class="c-blue c-blue-hover c-blue-focus">返回首页</a></p>
        </div>
    </div>
</main>