mysql数据库学习（二）--表操作

一、表操作

以下内容都是自己学习的时候看过的一些知识，作为笔记记录一下吧，大部分都是所看文章的内容。

1.创建表

前面的基础篇笔记是相当于搭建了一个方便管理的文件夹树根，下面要学习的是一些关于表的知识，相当于树上的叶子吧！

create table 表名（
    列名    类型    是否可以为空，
    列名    类型    是否可以为空
）ENGINE=InnoDB DEFAULT CHARSET=utf8；

下面这两个变量是：
ENGINE ：存储引擎
charset：字符集

        是否可空，null表示空，非字符串
            not null    - 不可空
            null        - 可空

是否可以为空

        默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )

默认值

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null auto_increment,
                num int null,
                index(nid)
            )
            注意：1、对于自增列，必须是索引（含主键）。
                 2、对于自增可以设置步长和起始值
                     show session variables like 'auto_inc%';
                     set session auto_increment_increment=2;
                     set session auto_increment_offset=10;

                     shwo global  variables like 'auto_inc%';
                     set global auto_increment_increment=2;
                     set global auto_increment_offset=10;

自增

        主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )

主键

        外键，一个特殊的索引，只能是指定内容
            creat table color(
                nid int not null primary key,
                name char(16) not null
            )

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )

外键

2.删除表

drop table 表名

3.清空表

# 表还存在，表内容清空

delete from 表名   速度慢，能回滚。
truncate table 表名 速度快，不能回滚。
 

4.修改表

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型

添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;

添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称

修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

实例：

show tables; #展示当前库下的所有表名
SELECT * from user_info; #查看表的信息
alter table user_info add class int(4) #为表添加一新的一列,默认添加到最后面。

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAbIAAACdCAIAAABenGocAAAVmElEQVR4nO2dPWsbSxuG9yed4hhi/ZF0cRHY/5A64CaFtn+btwzuAkZFMAEXAZ1CRkQYYjYYFzmJjcGYEDDhLeYttB/zuTtaz8czo/vChbwfmnnunbk1s7PSUzxG4urqipHk6uoqliYSqAnYif+9eLEPfwGULK7iEdsA9UQUBIDnEN2wwvwFULKI7UIAADdEN6wwfwGUhC0CkAkhjSM8sEUAwM7AFl1R/BcAkAWdccSuiBdCRmccLX779u3q/k8Gf9++fQvw8bIrcWtFUxNSp*p5KLoqBCpOYRMMWvQJbJE6KEsEWXQFbjANskTgpSgRbdAVsMQ6wReKkKBFs0RWwxTjAFomTokSwRVfAFuPQ16qeHxaH81o5YlFqNzsundXzw6JcKEeYaiVgUUWfUXjFS7PxrMaOtrgodVc+LrBF2CJjDLZIFNhiFGCLsMVB4tqiEf542OKOwBbHgC3CFgeBLUYFthiFhGzxw8vixZu3r4uiKIrir7eXzfZ/3v1VNLx8zx35vtn+8v2fq/fKWfcfXrZncRsD2WI9P2wL7xrEomw3bVtsPT/s9i7KwlPLESfRmsr0FfJaelN8X25Tlb5W3AGHh4dC3RZlcTifl+KZ5ihk9cWJOjEDFftnF9HhfN7Vk1embzqyGMZrKje85zNmi1KJnC3qaq5u08fnlLRssShefWit8MWbf/5c3V++efXu4/aA96+L4vV/uiP/fvfxvjXE/qzugO3pf67uL9/83flpEFvUDIz4vtjvbraGGa8JxYoGGcoWC37811tWb4tSZ+GP5o7aVtcURT0v23C6QziFqQ1dOIn4iOr5YcHboqCMGqBRDX3DeyaDtqg2qE5yXc3VSjmr5hBp2SLvZd3rPx/fvmg/PVTX071uB48dAQaMUssQ2ob4Qc7tU450jcYWJReOM4lu/5VHi8oBchXr+WE7BDFHoQw3ut3UXJGTyBiRxifkAE3nGhvesxiyRU1zEjTXXRqpXt47BUveFv9591dnavrBoMEW/27HmKH+FKG3DUDXh3tgi65tUR5m9fOyw3kdZCCyGzvbojbAAVv0cHEn2qLh0vRRCWeqW1ySuC1yBvfx7Qvb0eL9h5f9jcg//3m1PSuULdbzOTcVapstP8fpbyk2fTXaJJpvqj5LF26lcv9MtkVTFNyxcpGHZXlI6bYiY8x6Ei3cUNUEaDpX3/Ceyegkurtwc/7eorbmamfRdB/3JG6L95dv/m4nwq9e244W77ULNeFGi8rKgmHywN88Crnkwt8TnwccLZaKLkZbbAWTb752tmiKohf6sCwlPyG12LJF6J9c3ZUlF91BfICma+ph/WJkyaUvsWvp/O1jueZqZ9F0H9ckZItp/6X6gE6+pUsQW4JuMEpEs7qMMXz5zx2wxTjAFhsI3lZkjMkD6jAPCDwX2KIrYItxgC2ydlJG0RSN00+6nshgi+6ALcYBtkicFCWCLboCthgH2CJxUpQItugK2GIcYIvESVEi2KIrim8AgCzojCN2RZ4FCVtMa5A1gcfHx9hV0BC3VjQ1IUWKEqnGkVwUAxWGLbqEZsuALRInRYk648j7L4CSsMU4wBaJk6JE0Q0LtpgMNNs3bJE4KUoU3bBgi8lAs33DFomTh0TJRUGkwkVR6J2Rs8XuB4iqWTGr6D7kr4eI0BKwReLkIRGxKB4vzm5+Ci+UI2hUuPlK0z7Z4qIs4ocRt1bjpdfVrGka3Gb7jTLdb680x/Qnjfwmy+QTnwuN/qm2ClkQ80bG/ETx6/rr6eaRsad6uT49+1r/5vbd3Zwub38ZT5Vt8edmfX795LvCE+ib17gtpgkn9LZPlWVJyhYj1GqsdO63+RbdDvuNEtzHqTY5Tl3N9A1s8okOiN0/tddFK8iQSu6j+H173hjfU71cX2xEH9zRFhl7qpeCscaWvUH44M3dFhuM/TcgcWs1UrrwT13NZlW9y0YJwbw0TmZsXpNPdAGR/ikLrAoyqJLzKH5u1hd325dP9XJ9cfdUL7kR3862yNjdzemmryQR2aUZSTE2iU7PH2GLO5eu7Yv2G9W3FvPgCWEOhD35RBcQ6Z/ydVEFGVTJdRT84G5ri4z9vj0/a72ys8Xft+ed8fWvdbbYDz99VHgisi12zghb9AppW6yrmZATthuV2G0UkVrNQpkVmprU5BOdQKR/8lFrBRlUyXkU/FJJa4vbu43b7RNsUVx+ISI7RotxIG2LTLiJX5XtHtuN4grAwHBGblGTT3QPkf5Ja7QojOx6W2yWXzaPk2xRuL1IRPZd7i3CFt1B3RaFPepFt9845GcjIU8+0QVE+ufEmxstYUaLjLVT6U12o0W7B3Rgi25IxRa12+03MsaEdmPu5U5PdACR/mm8ddDvMKnEmBdbVO4ttvy6/np6tuZssTmyn2Kb7i2StUXr5xZhi26gbYuOn1rkjuIz5Vk0p8knPhsi/VNuFUZBlI2MsRAr0fzOp3q57laiG5c8W59vboZGi2RXovHlv/DgWy7EyUMin88tOoHqc4v4TnQUYIvEyUMin99ycQDdb7nAFqMAWyROHhIlFwWRCsMW4wBbJE4eEiUXBZEKwxbjAFskTh4SJRcFkQrDFuMAWyROHhIlFwWRCg9l/nsEAICwxLZExobzRIeshz+ICC0Rt1Y0NSFFHhIlFwWRCsMW4wBbJE4eEiUXBZEKwxbjAFskTh4SJRcFkQrDFuMAWyROHhIlFwWRCsMW4wBbJE4eEiUXBZEKT7NFCj+2YAsRoSVgi8TJQyJiUaST+c+0I1NbNOZIC4xQq65SoWplUbp9hjkbSTXHCDn8jD+IM/DmfnMlEumfMiM/YyQL6SOKvcj8Z9qRoy2OZZKLU6tF90PXwWo1Vrp9hjkbSfXHWLShoTffWsGe2aJF8kVk/nPBPtniWCa5kBgufyBhR0q3zzBnI6nhmPG8fQNvXlezWVX5FItI/xTQ51nkhZTbj/Mo9iXzn2lHZ4tywvLuh9KrUh64awbzwpFSJuJmm9fWzQs9nEkuLPrLH2G0qCvdPmeIjaT6Y/hmZQjZ+OaNRXq9gkT6p4AhP0GfiFb5qHEdxd5k/jPt0IwWxXG7Mouqq1KdjmmP5Ht/Xc383lLrhB7OkRYYzeXns+hFLd0+w5yNpKPHcFNlqxPVFz4g0j8FDHkW+U8YSRDXUexN5j/TDtkWjSmGhKTpynhRd6TUnD37UxKjRe8fDjuV/tzRonUCP3mbxYnc+Xtni0yXZ1H4VJGnG46j2J/Mf6Ydoi0OOFlrdnzfEu4gUbLFkCk1B+Evf/jln5HS7TPM2Ug6foyhBWhOlO/ojCyAPwMi/dNM86FBIU80Y9ll/jPt4GxRbcojZscN9rXjyjiT6OEcaYExLAQRKd0+w5yNpPoTay6P3y4n9uzjaLFFvAKhRov7k/nPtKOzxf6xNmnJRTY77pGqshwaLYrHhltyEcqNnMJQGMOqAkcv3T7DnI2kmmO4cd/A1R988/2zRUOeRe4i+n5ucV8y/5l2hPvyX7BJNCXwLRfi5CGRz+cWnZDVc4vPpK5m/OqN3+ksEaElYIvEyUMin99ycUBm33J5NnZTKCcQEVoCtkicPCRKLgoiFSYwifYMEaElYIvEyUOi5KIgUmHYYhxgi8TJQ6LkoiBSYdhiHGCLxMlDouSiIFJhZP4DABAitiUyNjxafMiCx8fH2FXQELdWNDUhRR4SJRcFbDEQNFsGbJE4eUiUXBSwxUDQbBmwReLkIVFyUcAWA0GzZcAWiZOHRMlFAVsMBM2WAVskTh4SJRcFbDEQNFsGbJE4eUhELIrvy7O6Fl7IwBYDIbaMk6PmO4cHx6toVXow1uroJHzpXeGCJlqh7DdKaI5ZHR/0v9tjjHvgzU+OfF5FYobS0qvWhC7IqGjpI4p/Ly9PV98fHu6+fF6fnl1++cHtu65PP9/8azxVtsV6tf50eccfAVsMBNcyVscHbWs6OQrmQRa1amoSrFJc6SdHnbX0xWuFst8ooT/m5GjU1IbefGsHe2aLY/Ktjg9EldxH8ePmU2N8d18+r5cr0Qd3tMWHh7svnwVjhS0Gom8ZQqNRWlBY9O3Vwio8lt4VrxXKfqOE4ZiTozH9B958dXxwcHzsUy2Ktig0j9XxgRy9KqnzKOrVenm9fXn35fN6eX335TM34tvZFh8eruvT1Xe+wiHtz8S4LXLXQvdhxQ3i20tyclQcHB8fKZMfbkLktUWL9C1DbDWhLEiPtr0Gq5LRlLcCaYWy36i+reYYfvJnsEfjmzcW6VWuFGxRFE4nh+so+MHd1hYfHn7cfDprvbKzxR83nzrj61/rbLEffjYVDml/JsZtkf98PzhoPqH6xn2kTsFOjgp1jsXPglbHB+Fu7nUtQ2pHhGyxtYhgo1dNb1kdH/AXTRHKfqPmjQeP4abKVieqL3xA0Ra5a8R3sm6f2n5cR8EvlbS2uL3buN0+wRbF5ZdkbLET/OTo4Hi1vRUlDOCV8SLfXNsjpSYc0JPSGi1GmUTLH1PPHS2K6yTjsvNGN3aiOHnZL1t8kGZcXPSGGxiOoxBGdr0tNssvq++TbFG4vZiOLTbWxhniSeuKfH8S7hpRtcWBOUhYRu7uBSxds1KiFcp+o8T4MYaoNSeq666+PkiI2mKP8KFhajdhRosPD+1UerU/o8Wttx00zXn7ul8f5O/3DNgigUm00CfjjhWlhSBel8CjRb2VaYWy32j1bqv2UPMYeeTN93G02CLGblTCgy0q9xZb/r28PD1bc7bYHNlPsU33FhO1ReGmhtB3uceojo6GRovisXGWXIQ6xHw650GslWbVKljp/UOLYg20QtlvlNAcwwU90BIG33z/bFF5arHfrBff/0o0v/Puy+d1txLduOTZ+tOqHhotJroS7YUok2hK4FsuxMlDIp/PLTph359b5OaK4ZYWHh6otm/YInHykMjnt1wcgG+52E6bnEOzfcMWiZOHRMlFsX+2GAmaLQO2SJw8JEouCthiIGi2DNgicfKQKLkoYIuBoNkyYIvEyUOi5KKgYovI/AcAoENsS2QMeaJjEbdWNDUhRR4SJRcFkQrDFuMAWyROHhIlFwWRCsMW4wBbJE4eEiUXBZEKwxbjAFskTh4SJRcFkQrDFuMAWyROHhIlFwWRCsMW4wBbJE4eEhGL4vHi7Oan8EI5gkaF980WF2Xz/cNZVUerEtNf/kUZqlpK6UrRdTVThRrZWJQLU4E62bttQ0FrTuQKHCrymRDpnxp63ZrgBwTxEcWv66+nm0fGnurl+vTsa/2b23d3c7q8/WU8VbbFn5v1+fWT7wpPYK9ssa5mbfdalB671G61atg27uC2uC22LEvZsRp1FqVGMtNGvaRa2RdlV95uJ3KF+4RI/5TRBT8giPsoft+eN8b3VC/XFxvRB3e0Rcae6qVgrERk3ydbrKtZ3/2Ef8IjX/66ms2qKkiH15QudS3hn7qazarasJEtyl5FQ/ccl323E/ki/UGkf4p0sgsMCOI8ip+b9cXd9uVTvVxf3D3VS27Et7MtMnZ3c7rpK0lEditblOdJfHsVmuusqkoSc1SOXmix+YQZdJgQL38jYrAq7WiLRbkwbOR7qql3jspuGi3qT+SnjB7tkUj/FGg/O8UuNiSI6yj4wd3WFhn7fXt+1nplZ4u/b8874+tf62yxH376qPBELGxR/YA32mLR9xUyxtgJLcVBxxa7mlCxxbqaCVPjolwYNoqdUlv5Edn5t93pRM+tjEj/FOBk1wavbnMdBb9U0tri9m7jdvsEWxSXX4jIbjNa3N7jle6yG0aLdbeVnC0SHS1y9aBii0xY6qhKrn7SRqEjdqM+cZ3ELHtdzUQvtT1xaJsjiPRPATFcG0EcRyGM7HpbbJZfNo+TbFG4vUhEdvt7i9ySQLq2qJsGRqGrFb+KOL4w67r0DrPBaOfGzUarAbhB9vFFr/HrtWe2KPaqCLZoGC0y1k6lN/szWqyrSrJAbozAzYHI2yLfteKOFQ2Xn9BocXB7v1E/WpTQyW71qaS9XnXN3QLdr0m03PEaKQcE8WCLyr3Fll/XX0/P1pwtNkf2U2zTvcUkbVHzpFR/R2lWlsmMFhl/Jyzm0zmMui3aP7XINw6zpqrs3FlD52quF3cz06dYRPqnwkBf1AjifyWa3/lUL9fdSnTjkmfr883N0Ggx3ZXopCEitAS+5UKcPCTy+dyiE/DcYiSICC0BWyROHhL5/JaLA/Atl2gQEVoCtkicPCRKLgoiFYYtxgG2SJw8JEouCiIVhi3GAbZInDwkSi4KIhWGLcYBtkicPCRKLgoiFUbmPwAAIWJbImMYLcYibq1oakKKPCRKLgoiFYYtxgG2SJw8JEouCiIVhi3GAbZInDwkSi4KIhWGLcYBtkicPCRKLgoiFYYtxgG2SJw8JEouCiIVtvy9RSq/+zABIkJLwBaJk4dExKLIKvNfTrZIMfNfmFR2ptIZY44y/w1LqhQxKfNfrfw+pSfBiPRPDUO/oSMr6SMKZP7bko0tEs38F15fSRNnmf/0YRiKmJr5T9zv6yIS6Z8yw6lwlL3uo0Dmv/ZlLrZINfNfmFR2ptK7OkzJ/KdPB6hn8AfAp6QM9Kobkf4polN48BI4jwKZ/0Rb7BKO9ZmN1LwF2mTqpoyAQmaQED+zTDOXS6hUdobSGyZm/tslD4RR8N0y/42+nRuI9E8Bbea/wUvgOgpk/pNSXEkp/fS2WFelOjPSZgTku4KS6cgpndBkM/91BEuYOGKL9pn/DOkAtegFn5j5z/tgn0j/FNBm/hu8BK6jQOY//SS6HaWbRoua8aLudKmL+LQo8qNFnkCVGrFFZp35T79Rv66lz2g6LfOf/1sgRPqngCnzn/a6MMacR4HMf1NskW/mwxkBY9kivcx/ImRsUdhjzPxnsdFYxHMy/wWQiUj/FLDK/CdoGma0yNgeZv4z2qKS/I87cCwjYIRJNNXMf4FS2RlK74uekvnP4h30B0zP/GdTlAOI9E8Rbea/HlUXD7aIzH8N+pR+uuR/3ANUoxkB+Yetwiy5CIXSyfwXKJWdofSGqZn/bJ9alIuYnvkv0ECfSP9U2OWpxRAr0fxOZP5zS5hJNCXwLRfi5CGRz+cWnZDwc4s+qKsZf6fI4ziJiNASsEXi5CGRz2+5OCDpb7n4IdTckYjQErBF4uQhUXJREKkwjUm0T4gILQFbJE4eEiUXBZEKwxbjAFskTh4SJRcFkQrDFuMAWyROHhIlFwWRCiPzHwCAELEtkbGB0SIAAOwnsEUAABCALQIAgABsEQAABGCLAAAgAFsEAAAB2CIAAAjAFgEAQAC2CAAAArBFAAAQgC0CAIAAbBEAAARgiwAAIABbBAAAAdgiAAAIwBYBAEAAtggAAAKwRQAAEIAtAgCAwP8Bv5NLqHWKf3QAAAAASUVORK5CYII=" alt="" />

--插入指定列的后面。
mysql> alter table user_info add firstname int(4) after name;

--插入名为qq列在第一。
mysql> alter table user_info add qq varchar(15) first;

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAocAAACYCAIAAAAZadTSAAAcLUlEQVR4nO2dz2vkOrbH64+awE39I7ObLAb8P8z6QTZvkdq/zSwv2Q2EWjyKC1k01CZN0UVDNw4hi3tvJwyEogkUw1v4LfxLkiVbdqTjc+TvhywqKrusc3SOvpYs26ui5nQ6nU6n79+/n3jw/fv3giVjXfR/v/yCP+tfpMgBAAC5rBqxKf+HKg8CVYYqAwBAJFaq2HBjRuntYawVs4sf278YMQMAAKLRVHlGqUuYRoTmrggX4BAAAHAxoMqzD6c++EfoSSesKsMBOAQAAFxAlaPDqjIcgEMAAMCFXZX/WTO7rH7w758MYFUZDsAhAADgwqnK5YeeYc3j4+P3f/+Hyd/j46NaN1ajsVGVMQxJkv6goq/PIPPWiqdPWJGGi8RZIa7CUoAqRweqbABVFnR0EaThInFWiKuwFKDK0YEqG0CVBR1dBGm4SJwV4iosBahydKDKBlBlQUcXQRouEmeFuApLAaocHaiyAVRZ0NFFkIaLxFkhrsJSgCpH5wOqnN9crlarVXZzc7m6vMkjVZAawaqcOxpim8VrH8Un+c3lKtt2tnDVSsOjijGtiEqUsCH3Rq8V28zW8vPCM1sTAKocncmq3PbBXt2uGKDKE48OVXYAVZ4FntmaAFDl6ExW5UndgqPj5oRgVXYxryo7UbeHKo8EqjwEz2xNAKhydKDKBlDlqUeHKtuBKs8Cz2xNgFCq/K+/rhT+8t//O1AeT5W32eqyqXabVtUl2tVqpfdT26o82xbFNis3UJKxLlpNz9BpqtweeJVt9bnsbLvN6grpZik7Xd7kpYE3HaPmc8WgQ8wJfKOanTrkN5fNt9tsFanj0mewLZUJ4BSfo6utr/rFHh6Xl1rdtGBQHOWwwvS+PkvOTL91eWgsury5aeppJo41vNxtGiz4x1tRHlFRZVvNu2V2+yJWGAQjiCr/66+r1V9/rQTyf/7WqK+rPLYqr1pVblIwq5Op7byVFC1TQO3Zqg20XmpadAcYK+vd7krLT6NSxvBINWp+V5R4qbLlIPY6VKU0o1XtsLo+U6mydirVKqYtPIaDwWWFLUgUD3MbuBnpr8mXkkKaZxxZYPNGyOD3t0IPqMbltpp3K0UyZQZVjkQIVf7175rcNv+6ygnHyjeXPUNENdlsn/Xz5smnycFVuXNqr9bLNWmZc3BFid9YuWOasw5dJwTGosrGScA8M9j1v87wGAqGXivcQcJNlBUXOS1ynMCqBrr2DRr8U6yoitrq25rGqFf0pCigytFYhiqbJ83eUhQiqmOqckmZpO3EXF9HPKsrSsZcV1ZMc9YBqhxale1BUu3GcOXCaFW2GtijyiTT9V6q7Giaov6/e44eLzWgypEIPoP99R9/sc5gq+XkqqxEuXIlckiKzBmtiV1RRFXOb27MuewRHTG9K0o8Z7A7ptnrUFYx6r1jQzPYak8Z8+jaZXTln8mq7LLCHiTlfll2SaJRY/CcwdYuptuzwLpvyOAfb8X2Rr2ubK15N1ks6RO1wiAkgVZ7/fr3ekbll3/8lzImdpVTz2C3Uz6XWeY7QNT2mx7ZUcfK7eyasRbEvNaac3BFiedYuWuafeZOvXBIudpLXY5zE3E4ZY6VM2uT28NjKBhcVliDpN6cnSjr8qDUvbPay7aRaqCrTQmWTjmtaCJdXTpg1rybLJb0iVphEI4Id0a5ZqqJZrAHqk3PB57tlSYJ3hmV7tENqGZzx+F0Ec/qOmDV0D6Iq7AUoMrRaSqDP+Ov6yueeQ5VrmB4SbkoCnM6gWZpfHgYNbQf4iosBahydGYXP7Z/XV/xzHOoclHPiHLUZOfcryRJLtg0tD/iKiwFPNsrOrOLH9u/rq945jlUmTlpuEicFeIqLAWoMi8WHug8zYcqMycNF4mzQlyFpQBV5sXCA52n+VBl5qThInFWiKuwFJyq/Pj4+Pj42MjboyiEVhsAAAQxh2alj1OVyzFoI2+Dw9N5OZ1O6r9yx8qGIUuDp/nz1oqnT1iRhovEWSGuwlKAKvNi4YHO03yoMnPScJE4K8RVWApQZV4sPNB5mg9VZk4aLhJnhbgKSwGqzIuFBzpP86HKzEnDReKsEFdhKUCVebHwQOdpPlSZOWm4SJwV4iosBRGqfPq8e/6hfehs0cbHNltlTbU3kp7tUxSJB/qodmQEVJk5abiImRVSszUBfFVZ6B9UOTg/n77dHU9Fcc73h7vdt/xd+e71+W7/8tO5q5nnP46H+6eztoVp/jZbrdZzt+K8tRo+er5ZV4+YVIr9C02atw1V27Q7DbyFaPKOH4VH1nSjwnSIu7Ao4lhBm60gDImr8hwu/RDcA/395b7K5HO+P3w+6ok9Ms+L4pzvtZ5CMb/s0rMsY6XKM9Rq6OjKu3i3zRf+hQb5Zm3ZXP/eqq2TdwzA3FljbRerQ/q8FN4KumwFIYEq84J5oP84Hj6/lh/P+f7w+fWc75Uz6NF5XhSvz3fH1uSu+U75IGTeWg0cXfsn36zXm3xMoYGmnRYhbV7zG2zHEDDJGtPBXYf0eim4FfTZCoIwoMrf//0fYw5q0nVl9SSrjI+ieH+539VB08TH+8t9EwHtZ1t8tKeBRdG5rrwt4p6bx4N3oFO2YwVUeaQqr7LtmMLuTyulppk9Zk/eMQRMssZsl65Der0U2ooZshUEYbQqN8I8RpXV9QJ1fJTXPMryCfGhr0GAKpNA2Y4VUOWBo+ebtTovvWrGZH6FOkbSbDtTsq6MmrxjEJhkjWq11SG9XgpuxQzZCoJAMlbWzrDa+KjWIBxPk+JDu8gBVaaAtB0roMrDR1fWD22y+hvfQn3xUc9gzkyoyTuGh0nW8Borz5GtIAjjVHnqnVH2s7aiqGdUjhgrV/AOdMp2rIAqjzm69eqtf2GfnA6YPHnHEDDJmolXFmpoxspFETFbQRBGqPIH7lc+fd51rnDU/Hz6drc7KPFRbdnOtLiucECVqaFsxwqosv/RreX+hUVRaGnjFpmgOwaASdY45+3bL1xeKoooqkydrSAIvqr8waeIdFYDql+e8/2hWQ1YhcvucH987jtrc64GhCpHhLAdK6DKQ0cPfLeyspWSPj7ZNHnHD8Mka8yocDqkU1gUBcUabPXLKNkKguClyl1JHv1sL33x3odJ9s457oYssh3xbC/mpOGimPcrB0FGtibAsCq7/sY+cbN+ykwAEn7KDH9DFtiOUGXmpOGimM/2CoCUbE0AOlWOSjLxkYwh0+BpPlSZOWm4SJwV4iosBagyL5IxZBo8zYcqMycNF4mzQlyFpQBV5kUyhkyDp/lQZeak4SJxVoirsBSgyrxIxpBp8DQfqsycNFwkzgpxFZaCU5UfPTgBAABYKnNoVvo4VXlwT4yVY5CMIdPgaf68teLpE1ak4SJxVoirsBSgyrxIxpBp8DQfqsycNFwkzgpxFZYCVJkXyRgyDZ7mQ5WZk4aLxFkhrsJSgCrzIhlDpsHTfKgyc9JwkTgrxFVYClBlXiRjyDR4mg9VZk4aLhJnhbgKS0GEKlvfKaZv0RcfHF5w4EvSgf7BdpwNqDJz0nARMyukZmsC0Kly/UTWc74/3O20p5y379+2Y8bHyCeyQpVDQtiO28zjFUcEaLVqKkVVK4+jWx3lX2hg2aZ90ZHlXUc+P77NYvqLadYMvLjLdGQMK+brdcF0qFS5fXvJOd8rL9wuGRkfI99eAlUOB1075pt13WzbrEcKCFBqtc2aUKKq1dDRrY7yLzSwb+ORQn0/XirRwlTZ6j69UPdSeCvm7HXBdIhUufOmz3O+V868RsfHqDd9QpWDQdeO2nt5Z35XtqNRiOJq4OhWR/kXGji2qV9a7qbnx/PNer3ZxHQWx6zRoiPfrMt/VEea8RPcill7XTAdGlVWT7Lq92+/v9zv6qBp4uP95b6JgPazLT70V4c28aFNtVWTRNtstd5sMnPayDKVpG2pZIwyNRe1d+Ee6HTtaOjAvCdW9kaZYaxsO7rVUf6F3Z+1bKNmlcNk549XCh21BTlmjanK7XxFO2zWfRnaCsJsBUGhUWV1vUAdH+U1j7J8QnzoaxAs8aHPGnXm8PJN1p0MtG6p9r75Zh33eiLvQKdrR2Mox06V882aala9/+hWR/kXWn64dxtlntprx+6HGHDMGjVCjH7FsTAhtBVz9LogBCSqrJ1htfFRrUE4nibFh3aRw4wPrZNQO4T2VLWwjJZtWxrdSWR9YB3olO3IeKwc/dRs1NE/OlbWl2gNu13V2aEdlf0Xp8qFMcXWTDo4FyYEtoK+1wWBmHOsXBT1jMox7Flbj5DWWqv2bdrlM6hyD4Tt6JgAnAW1UehXng0c3eoo/0KD4W0cCWDZ0byaNLD0+wPwzpqiOWfpn4qgGSsXRaReFwSDTJU7Vzhqfj59u9sdlPiotmxnWlxXOOzx0e1KBrRWmWqyjqoxg90wUzvOvVzPsQaNydGtjvIv9Pq1vN7UfYfTwI8vcaxco7cA1ViZNFtBSOZag61+ec73h2Y1YBUuu8P98bnvrM2xGrC9n9NY7WVqrXIvYZb1jZX1bZe92ouuHYtCdfuct0UVxgi+G1+zH93qKP9CA8s2yqi3J/h7f3x5qmy9W1lrxNj3K5NmKwgH/f3KQZjvzrlFz2An1I5jwLO9mJOGi2LerxwEGdmaAPTP9goA7VNm8s1aXTgWdzKVf6CLbcfpQJWZk4aLYj7bKwBSsjUBRDwHe5i48eE3gReEhQc6T/OhysxJw0XirBBXYSlAlXmRjCHT4Gk+VJk5abhInBXiKiwFqDIvkjFkGjzNhyozJw0XibNCXIWlAFXmRTKGTIOn+VBl5qThInFWiKuwFJyq/OjBCQAAwFKZQ7PS50Nj5Tc2nE6nuasQhmQMmQZP8+etFU+fsCINF4mzAqocCagyL5IxZBo8zYcqMycNF4mzAqocCagyL5IxZBo8zYcqMycNF4mzAqocCagyL5IxZBo8zYcqMycNF4mzAqocCagyL5IxZBo8zYcqMycNF4mzAqocCRGq/Pt+l+faB0t8UFUmLskYYkNqO0KVmZOGi5hZ4ZWtUURp8dCp8h9fv949/P729vrl0+Fu9/XLn8p3T/ndp+c/vOMjfzj89vXViI9RlWELf0MI2/H2qnrO6cX1Q1AbRuKo1dUt/dGbg2s+sTrKv9DAss3D9UX7piqn3T0/fnsVsxWZZk3rtcp0zY0dX8awIna2RhGlxUOlyn8+/1ZFwOuXT4f9gx4QI+Pj7e31yyctwpim5Xi4G0LXjg/XF3VndntFJoFW9FpVNSGrlHL026tG2drDWx3lX2hg3+b2alBT+368VKOFqfKQ+x6uL3QvhbcifrZGEaXFQ6TK+cNh/1R+fP3y6bB/ev3ySTnzGh0fb29P+d3D72p8+FeGM8wNoWtHrc/qdGC02BvFQ6kiHr05vNVR/oUGjm1ur4b83/PjD9cXF9fXMb3FMWu08Hi4vjCt77o0uBUE2RpFlBYPjSqrJ1llfLy9/fn8264OmiY+/nz+rYmA9rMtPtrTwCo+yg9KLtjOVZUppDolbq9WF9fXV52pN2U6LmqPosOxf2mha0ej06JSQDvWRiGrkvOcoHSQ1VH+hd2ftWyjzrw61Nn545VCR3UXx6wxVVl3nM0doa2gyNYoorR4aFRZXS9Qx0d5zaMsnxAf+hqEJqDV0/uLi+oEte1crroTgLdXq+4MnzoH93B9QXdlk2P/0jJHO769vbFS5VqhyMbulpB4uL5QY7bjKP9Cyw/3bqPMU3vt2P0QA45Zo7SR2sc033XjJ7QVFNkaRZQWD4kqa2dYbXxUaxAefp8UH9pFju7M5+3VxfVDeR1Omz7qjJbV7qLe0uhCCDWBY//SQNmOEsbKs8xgmyeJHx0r60u0ht2u6uzQjvrM1bJU+c2YblOsd1w9CGwFSbZGEaXFM+dYuQ4CZRnCh8dYtbIqenxbi7Lan2mXzKDKnhC2Y/8EIC0DV3YJj25ZpGV1lH+hwfA2DqstO3ZXHMc6j+GdNW/GOYsrbmjGym9vIbM1iigtHjJV7lzhqPnj69e73UGJj2rLdqbFdYXDrspvD9cXFxdVd1J+bpeGqhe7elQZM9h2KNtRkYR5R8rGTIwaFsRjZbuSWh3lX+j1aw/1pu4ZgoEfX+JYuUa33emJCKocPVujiNLimWsNtvrl65dPh2Y1YBUuu8NvD3nfWVvPGmz1io7Wdyr3D15d9Y2V9W2x2quBvh1pr+HaMc75Bu/bjXX09mZlvQZWR/kXGli2UYzuSYTeH1+eKnfuVm6L7c6PvwZb/TJMtkYRpcVDf79yEGjvV8YMdoPodpwKnu3FnDRcFPN+5SDgfmUi6J/tFYD4z/ZSZirplvW8vUnoX0S1YxigysxJw0Uxn+0VADzbiwwRz8EeJnxA+03aBSeN/mUyPM2HKjMnDReJswKqHAmoMi+SMWQaPM2HKjMnDReJswKqHAmoMi+SMWQaPM2HKjMnDReJswKqHAmoMi+SMWQaPM2HKjMnDReJswKqHAmnKj96cAIAALBU5tCs9PnQWDlKjSaRTHwkY8g0eJo/b614+oQVabhInBXiKiwFqDIvkjFkGjzNhyozJw0XibNCXIWlAFXmRTKGTIOn+VBl5qThInFWiKuwFKDKvEjGkGnwNB+qzJw0XCTOCnEVlgJUmRfJGDINnuZDlZmThovEWSGuwlIQocqnz7vnH9qHzhapxEcyhtiQ2o5QZeak4SJmVkjN1gSgU+WfT9/ujqeiOOf7w93uW/6ufPf6fLd/+enc1YyPH8fD/dNZ2yKV+OBvCGE7brPqmafrTR7UhpHYGmWbUVWrc/TOofPNuuuogcJVtnUd0Ob2pqzPaMuOygH7DvlB+GZN67fK+B6HxLACva5EqFT5/eW+ioBzvj98PuoBMTI+iuKc77UISyY+uBtC1475Zl337tssYo/uQbdRyr6VXJXLw2ZZZgpm5Z1tZnGZq9DuUqvbt1lzvHE7KgePCdOssRnf45DwVqDXlQmRKv84Hj6/lh/P+f7w+fWc75Uzr9HxURSvz3fHNiaSiQ/mhtC1Y75Zt72/9g89ZqPkm/V6syHRG8vRjZ5d+yffrNeb3FFYbLPWiw51GHb7uB3VQ8aDZdY0btfocUhwK9DrCoVGldWTrDI+iuL95X5XB00TH+8v900EtJ9t8dGeBhaFHh/mLJ3aX2jdxXqzyVjMkCrwDnTCdtR7L5ohlwu9UaoYIqvSSFVeZVtHoSoULnEYdLtrrGzfUZ2vjajOHLOmPnXTe5g+h4S2grTXBQGhUWV1vUAdH+U1j7J8QnzoaxAcY6xOiabKq7avYqPLvAOdrh2NZuSjyk1NuKhyvllr89KrbOso1DXBWvkBt6s/O2rHyEnGMWsUt1uN75aFtoKw1wVBIVFl7QyrjY9qDcLxNCk+tIscSnyU6yuMFS6OsXLelEKVPaBsR55jZaUeXFS50FZZbTKlfkahpgPNmFdfouV2e75Z61Luu2NfWSA4Zo1uro9DAltB2uuCkMw5Vi6KekblGPysTVmOA1UOBmE7OuZgZ6Gplbp+dnhJcuijN7j1zToxXRV6TT843D683m64vRamynqnMoMqz9DrgjCQqXLnCkfNz6dvd7uDEh/Vlu1Mi+sKh2MGe2MosDJEUGbgoMoTIGxHtWefd6TsaBRGY+Xe8rbQPlY2sLnd66TI2l55rlz+XtYMttnvVK7scUgEVSbLVhCSudZgq1+e8/2hWQ1YhcvucH987jtrc68G7Nwi2F5OW2cZxsofgbIdlaugc94WVXBXZf+7ldXccPu063Zlr759Le2lXMiO6SyuWdPTFVkcEn8Ntvpl6GwF4aC/XzkIyd45x92QRbYjnu3FnDRcFPN+5SDIyNYEoH+2VwASfsoMf0MW2I5QZeak4aKYz/YKgJRsTQARz8EeJpn4SMaQafA0H6rMnDRcJM4KcRWWAlSZF8kYMg2e5kOVmZOGi8RZIa7CUoAq8yIZQ6bB03yoMnPScJE4K8RVWApQZV4kY8g0eJoPVWZOGi4SZ4W4CkvBqcqPHpwAAAAslTk0K30wVuZFMoZMg6f589aKp09YkYaLxFkhrsJSgCrzIhlDpsHTfKgyc9JwkTgrxFVYClBlXiRjyDR4mg9VZk4aLhJnhbgKSwGqzItkDJkGT/OhysxJw0XirBBXYSlAlXmRjCHT4Gk+VJk5abhInBXiKiwFEapsfaeYvoX2fmUur5qYQNKBPqodGQFVZk4aLmJmhdRsTQD652Cf8/3hbqc95bx9/7YdMz56n8gKVY4LVTsWyit3Zm5QtVa5+o5lkndZdUKi8w5A/7dGOV4l1aFziPbtRz07mu2Vd95HHclhfLOm761RpidjWEGYrSAY9O+MOud75YXbJSPjo/ftJVDlmNC1o/J6Wte7gKlQG4U+vAyfrFZZptVB8U5bOf9CA8chBt/LPNheXi9pngjTrLE4ua8JwltBl60gJHO9X/mc75Uzr9Hx0fOmT6hyROjaUevEY/boHhiqTFyTbkhoIa79U78p3L/QgTuLHN8MtVdUv7HMGpuHe5sguBWEvS4ICY0qqydZ9fu331/ud3XQNPHx/nLfRED72RYf+qtDTVXeZNq8kdpLmJ+7b3hvdzdGJFXZJqLu8w50wnbUe/F5T7U6o1XC+evRqrzKtmMKHTgd7hor97dX5PbjmDX5Zq30Q+0sgrsJQltB2euCkNCosrpeoI6P8ppHWT4hPvQ1CLoqq2K83uQuVc43WXdezra71hXlm3XEC528A52uHY0ei40qtygzttRH7/TtyqRo+dm/0IHd4eovdL9xtlf0qQ6OWaN6WOuInE0Q2grKXheEhESVtTOsNj6qNQjH06T40C5yOGaw6zki11jZMlq27W50UTElgnWgU7Yj07GyClGlBlS5MOZx6m98C+1L6rq2dc5G9R172iv+9QeOWaN7UL+432mXoiiCW0Ha64KQzDlWLop6RuUYdqzsp8pqN9NuAFXugbAdifv1XrirsvZN10/+hc5DDK+3c7cXgZs4Zo1+1djmBLMJaMbKRRGj1wUhIVPlzhWOmp9P3+52ByU+qi3bmRbXFY5xqqzerZFt9Q2ViSXb7pjBrqBsR6Vnn3sBn1qrXLmey2IGe6jcv9C5gddJkau9KBqPZdYYnY5lNbZRFEGVybIVhGSuNdjql+d8f2hWA1bhsjvcH5/7ztq81mC356vNTPU6y9rrys2dg22hfXftLsPlrvaibEfV53PeFlW4VntRnSgMqXL4u5XNQ7Q33fa2h7W9SKY5uGbNmLuVKdZgq1+GzlYQDvr7lYMw651zy53BTqsdvcGzvZiThoti3q8cBBnZmgD0z/YKAPlTZvLNWr1MFnGYxD/QJbfjRKDKzEnDRTGf7RUAKdmaACKegz1M9PigmrlceKDzNB+qzJw0XCTOCnEVlgJUmRfJGDINnuZDlZmThovEWSGuwlKAKvMiGUOmwdN8qDJz0nCROCvEVVgKUGVeJGPINHiaD1VmThouEmeFuApLwanKjx6cAAAALJU5NCt9po+VAQAAABAWqDIAAADABagyAAAAwAWoMgAAAMAFqDIAAADABagyAAAAwAWoMgAAAMAFqDIAAADABagyAAAAwAWoMgAAAMAFqDIAAADABagyAAAAwAWoMgAAAMCF/wetEPTXqS4aNAAAAABJRU5ErkJggg==" alt="" />

#更改表名字，rename法。
--语法: rename table 原表名 to 新表名；
--更改user_infor表为user。
mysql> rename table user_info to user;

#删除表
--语法：drop table <表名>；
--删除表名为user表。
drop table user;

二、表内容操作

1、增

语法：insert into 表 (列名,列名...) values (值,值,值...)

# 插入单条数据
        insert into 表 (列名,列名...) values (值,值,值...)
# 插入多条数据
       insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
# 插入另一条语句的查询结果
        insert into 表 (列名,列名...) select 列名,列名... from 表

2、删

语法：delete from 表

delete from 表;
delete from 表 where id＝1;

3、改

语法：update 表 set name ＝ 'nick' where id>1

update 表 set name ＝ 'nick' where id>1

4、查

语法：select * from 表

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

# as 做别名

5、条件

语法：select * from 表 where id > 1

    select * from 表 where id > 1 and name != 'nick' and num = 12;    # 多个条件
    select * from 表 where id between 5 and 16;                       # id在5到16之间
    select * from 表 where id in (11,22,33);                          # id在元祖中
    select * from 表 where id not in (11,22,33);                      # id不在元祖中
    select * from 表 where id in (select nid from 表);                # id在查询结果中

6、通配符

语法：select * from 表 where name like '_n%'

    select * from 表 where name like 'ni%'  # ni开头的所有（多个字符串）
    select * from 表 where name like 's_'   # s开头的所有（一个字符）

7、限制

语法：select * from 表 limit 9,5;

    select * from 表 limit 5;            # 前5行
    select * from 表 limit 9,5;          # 从第9行开始的5行
    select * from 表 limit 5 offset 9    # 从第9行开始的5行

8、排序

语法：select * from 表 order by 列1 desc,列2 asc

    select * from 表 order by 列 asc             # 根据 “列” 从小到大排列
    select * from 表 order by 列 desc            # 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc   # 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

9、分组

语法：select num from 表 group by num

    select num from 表 group by num           # 根据num分组
    select num,nid from 表 group by num,nid   # 根据num和nid分组
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid  # 内置函数
    select num from 表 group by num having max(id) > 10    # 前面计算的结果交由后面处理

    注：group by 必须在where之后，order by之前

count(*)、count(1) # 表示个数
sum(score)     　  # 表示和
max(score)     　  # 表示最大数
min(score)     　  # 表示最小数

having            # 要用前面处理结果是用having。

10、连表

语法：inner join . on、left join . on、right join . on

    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid

    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid

    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid

    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid

11、组合

语法：union、union all

    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B

    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B