鶴建立表主鍵定義

mysql>createtablet5(namechar(3)notnull,enum('boy','girl')default'boy'notnull,likesset('a','b','c')default'a,b'notnull,ageint(3)notnulldefaultQueryOK,0rowsaffected(0.03sec)mysql>showcreatetablet5;mysql>desc |Field| |Null|Key|Default|Extra |name| | | |enum('boy','girl')| | |likes| | | | | | | mysql>createtablet6(idint(3)notnullauto_incrementprimarykey,namechar(3)notnull,ageint(3)notnulldefault20,index(name));QueryOK,0rowsaffected(0.01mysql>desc |Field| |Null|Key|Default| | |int(3)| |PRI| |auto_increment|name|char(3)| |MUL| | |int(3)| | mysql>insertintot6(name)values('prj');mysql>insertintot6(name)values('yzj');mysql>select*fromt6; |id|name|age |1|prj|20|2|yzj|20 mysql>insertintot6(id,name,age)values('2','jfd','22');ERROR1062(23000):Duplicateentry'2'forkey'PRIMARY'mysql>insertintot6(id,name,age)values('4','jfd','22');QueryOK,1rowaffected(0.00sec)mysql>select*from |id|name|age |1|prj|20|2|yzj|20|4|jfd|22 2mysql>createtablet7(hostchar(60)notnull,portchar(5)notnull,allowenum('y','n'),primarykey(host,port));QueryOK,0rowsaffected(0.09mysql>insertintot10(host,port,allow)ERROR1146(42S02):Table'test.t10'doesn'tmysql>insertintot7(host,port,allow)values('','80','y');QueryOK,1rowaffected(0.00sec)mysql>select*from | |port|allow || | 1rowinset(0.01mysql>insertintot7(host,port,allow)values('','80','y');ERROR1062(23000):Duplicateentry'-80'forkeyAltertableadd，modify，dropAltertablechangeAltertableaddservernamechar(10)notnull,addidint(3auto_incrementprimaryAltertablet7addservernamechar(10)notmysql>Altertablet7addservernamechar(10)notnull;QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>select*fromt7; | |port|allow|servername | | | | | | || | 3rowsinset(0.00mysql>desc-> | | |Null|Key|Default|Extra | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |servername| | | mysql>Altertablet7addidchar(3)notnullfirst;QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:mysql>desc | | |Null|Key|Default|Extra | | | | | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |servername| | | 5rowsinset(0.01mysql>Altertablet7addlimitlinkint(3)notnullafterallow;QueryOK,3rowsaffected(0.03sec)Records:3Duplicates:0Warnings:mysql>desc | | |Null|Key|Default|Extra | | | | | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |limitlink| | | |servername| | | 6rowsinset(0.00t7limitlinkchar310mysql>altertablet7modifylimitlinkchar(10);QueryOK,3rowsaffected(0.04sec)Records:3Duplicates:0Warnings:mysql>desc | | |Null|Key|Default|Extra | | | | | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |limitlink| |YES | |servername| | | mysql>altertablet7changeservernamesnchar(10);QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:mysql>desc-> | | |Null|Key|Default|Extra | | | | | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |limitlink| |YES | | | |YES | mysql>altertablet7dropid;QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:mysql>desc | | |Null|Key|Default|Extra | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |limitlink| |YES | | | |YES | 5rowsinset(0.01mysql>altert7renameERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear't7renamett77'atline1mysql>altertablet7renamett77;QueryOK,0rowsaffected(0.00sec)mysql>descERROR1146(42S02):Table'test.t7'doesn'texistmysql>desctt77; | | |Null|Key|Default|Extra | | | |PRI| | | | |PRI| | |enum('y','n')|YES | |limitlink| |YES | | | |YES | 5rowsinset(0.00mysql>select*from | |port|allow|limitlink| | | | | | | | | || | | 3rowsinset(0.00講tt77成表mysql>createtablet77select*fromtt77;QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:t77mysql>select*from | |port|allow|limitlink| | | | | | | | | || | | 3rowsinset(0.00sec)將已有的表到一個新表，只要表結構，不要表內容選擇個要求為非空的字段，selectmysql>createtablettt7select*fromtt77wherehostisnull;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:mysql>select*fromttt7;Emptyset(0.00sec)mysql>desc | | |Null|Key|Default|Extra | | | | | | | | | |enum('y','n')|YES | |limitlink| |YES | | | |YES | 5rowsinset(0.01將已有表到一個新表，只要某幾個字段mysql>createtablet7partselecthost,portfromtt77;QueryOK,3rowsaffected(0.04sec)Records:3Duplicates:0Warnings:mysql>select*from | |port | | | | || 3rowsinset(0.00mysql>desc |Field| |Null|Key|Default|Extra |host|char(60)| | |port|char(5)| | 2rowsinset(0.01notnull,agetinyint(3)unsignednotnulldefault18,enum('boy','girl')notnullbirthdaydatenotnull,qqchar(11)notnull);QueryOK,0rowsaffected(0.01mysql>desc | | |Null|Key|Default| | | | |PRI| |auto_increment| | | | | |tinyint(3)unsigned| | |enum('boy','girl')| | | | | | | | | | || | | |birthday| | | | | | | 9rowsinset(0.01mysql>altertablesinfoaddvarchar(50)notnull;QueryOK,0rowsaffected(0.00sec)Records:0Duplicates:0Warnings:mysql>desc | | |Null|Key|Default| | | | |PRI| |auto_increment| | | | | |tinyint(3)unsigned| | |enum('boy','girl')| | | | | | | | | | || | | |birthday| | | | | | | || | | 10rowsinset(0.00mysql>altertablesinfoaddlikesset('film','music','football','reading')default'film'after QueryOK,0rowsaffected(0.00sec)Records:0Duplicates:0Warnings:0mysql>desc +| | |Null|Key|Default|| +|id |int(3) |NO |PRI|NULL auto_increment|| | | | || |tinyint(3) | | | | | | || |set('film','music','football','reading')|YES | || | | | || | | | ||| | | ||birthday| | | || | | | ||| | | | +11rowsinset(0.00Createindexon表名（字段名Dropindexonmysql>createindexnameonsinfo(name);QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0mysql>altertablesinfomodifynamechar(20)notnull;QueryOK,2rowsaffected(0.01sec)Records:2Duplicates:0Warnings:mysql>desc +| | |Null|Key|Default|| +|id |int(3) |NO |PRI|NULL auto_increment|| | | |MUL| || |tinyint(3) | | | | | | || |set('film','music','football','reading')|YES | || | | | || | | | ||| | | ||birthday| | | || | | | ||| | | | +11rowsinset(0.00mysql>createtablecj(idint(3)primarykeynotnullauto_increment,numint(3)notnull,namechar(20)notnull,classchar(8)notnull, enum('boy','girl'),atinyint(3)unsigned,btinyint(3)unsigned,ctinyint(3)unsigned,dtinyint(3)QueryOK,0rowsaffected(0.00mysql>desc |Field| |Null|Key|Default| | | | |PRI| |auto_increment| | | | |name| | | |class| | | |enum('boy','girl')|YES | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | 9rowsinset(0.00mysql>altertablecjmodify enum('boy','girl')notnulldefault'boy';QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:mysql>desc |Field| |Null|Key|Default| | | | |PRI| |auto_increment| | | | |name| | | |class| | | |enum('boy','girl')| | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | | |tinyint(3)unsigned|YES | 9rowsinset(0.00mysql>insertintocj(num,name,class, )values(023,'liangx','class4','boy');QueryOK,1rowaffected(0.00sec)mysql>insertintocj )valuesQueryOK,1rowaffected(0.00mysql>insertintocj(num,name,class, )values(010,'lind','class4','girl');QueryOK,1rowaffected(0.00sec)mysql>insertintocj(num,name,class, )values(003,'lind','class4','girl');QueryOK,1rowaffected(0.00sec)mysql>select*from

|id|num| |class | | | |

|1|23| |class4|boy|NULL|NULL|NULL|NULL|2|12|liangxfd|class4|boy|NULL|NULL|NULL|NULL|3|10| |class4|girl|NULL|NULL|NULL|NULL|4 3| |class4|girl|NULL|NULL|NULL|NULL

4rowsinset(0.00mysql>insertintosinfo(name,age, QueryOK,1rowaffected,1warning(0.00mysql>insertintosinfo(name,age, QueryOK,1rowaffected,1warning(0.00mysql>insertintosinfo(name,age, QueryOK,1rowaffected,1warning(0.00mysql>insertintosinfo(name,age, QueryOK,1rowaffected,1warning(0.00Selectmysql>select*from |id|name|age |likes|class|rdate|| | |1|tzh119|boy||class22004|||2| 19|girl||||3|lin0120|boy||||4|lin0220|boy||||5|lin0220|girl||||6|lin0220|girl||||7|lin0223|girl||||8|lin0211|girl||||9|lin0250|girl|||78|girl|2001|| 10rowsinset(0.00mysql>select*fromsinfowhere |id|name|age| |likes|class|rdate||birthday |qq | ||8|lin02|11|girl|film|class4|2001|0000-00-00|++++++++++++1rowset(0.00mysql>select*fromsinfowhere |id|name|age |likes|class|rdate|| | | ++|9|lin0250|girl||class4|2001|0000-00-00||||10|lin0278|girl||class4|2001|0000-00-00|||++++++++++++2rowsset(0.00mysql>select*fromsinfo ="girl"and |id|name|age |likes|class|rdate|| || |2| |||5|lin0220|girl||class22001|0000-00-00||||6|lin0220|girl||class42001|0000-00-00||||7|lin0223|girl||class42001|0000-00-00||||8|lin0211|girl||class42001|0000-00-00|||+ +++ 5rowsinset(0.01mysql>select*fromsinfowhere =girlandage<30;ERROR1054(42S22):Unknowncolumn'girl'in'whereclause'mysql>select*fromsinfowhere ="girl"andage<30; |id|name|age |likes|class|rdate|| | |2| |19|girl|film|class4|2004 |class22001|0000-00-00||class4|class22001|0000-00-00||class42001|0000-00-00||class42001|0000-00-00||class42001|0000-00-00| |6|lin02|20|girl| |7|lin02|23|girl| |8|lin02|11|girl| 5rowsinset(0.01mysql>select*fromsinfo ="girl"or |id|name|age |likes|class|rdate|| || |1|tzh1|19|boy|film|class2|2004 |2| |19|girl|film|class4|2004 |4|lin02|20|boy| |5|lin02|20|girl| |6|lin02|20|girl| |7|lin0223|girl||||8|lin0211|girl||||9|lin0250|girl|||78|girl|2001|| 10rowsinset(0.00mysql>select*fromsinfowhereagebetween20and22 |id|name|age |likes|class|rdate|| | | |3|lin01|20|boy|film|class2|2001 |0000-00-00| |4|lin02|20|boy|film|class2|2001 |0000-00-00| |5|lin02|20|girl|film|class2|2001 |0000-00-00| |6|lin02|20|girl|film|class4|2001 |0000-00-00| 4rowsinset(0.00mysql>selectname,classfromsinfowherenamelike |name|class | |class4|lin01|class2|lin02|class2|lin02|class2|lin02|class4|lin02|class4|lin02|class4|lin02|class4|lin02|class4 9rowsinset(0.00mysql>select*fromsinfowherenamelike"l |id|name|age |likes|class|rdate|| | | |2|lin|19|girl|film|class4|2004 1rowinset(0.00mysql>select*fromsinfowherenamelike"l";Emptyset(0.00sec)mysql>select*fromsinfowherenamelike |id|name|age| |likes|class|rdate||birthday |qq | ++|3|lin0120|||class22001|0000-00-00||||4|lin0220|||class22001|0000-00-00||||5|lin0220|||class22001|0000-00-00||||6|lin0220|||class42001|0000-00-00||||7|lin0223|||class42001|0000-00-00||||8|lin0211|||class42001|0000-00-00||||9|lin02|50|girl|film|class4|2001 |0000-00-00| |10|lin02|78|girl|film|class4|2001 |0000-00-00| 8rowsinset(0.00GroupbyhavingOrderby字段名acs/desclimitLimitcongdijitianxianshiSelectname,max(gz)frommysql>updatecjseta=110,b=99,c=89,d=100wherename="liangx";QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:mysql>select*from

|id|num| |class | | | |

|1|2222| |class4|boy|110 99 89|100|2|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|3 10| |class4|girl|NULL|NULL|NULL|NULL|4 3| |class4|girl|NULL|NULL|NULL|NULL|5|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|6|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|7|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|8|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|9|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|10|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|11|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|12|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|13|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|14|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|15|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL

15rowsinset(0.00mysql>updatecjseta='255'where QueryOK,2rowsaffected(0.00sec)Rowsmatched:2Changed:2Warnings:mysql>select*from

|id|num| |class | | | |

|1|2222| |class4|boy|110 99 89|100|2|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|3 10| |class4|girl|255|NULL|NULL|NULL|4 3| |class4|girl|255|NULL|NULL|NULL|5|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|6|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|7|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|8|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|9|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|10|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|11|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|12|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|13|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|14|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL|15|212|liangxfd|class4|boy|NULL|NULL|NULL|NULL

15rowsinset(0.00mysql>createtablecj1select*fromcj;QueryOK,15rowsaffected(0.01sec)Records:15Duplicates:0Warnings:0mysql>deletefromQueryOK,15rowsaffected(0.00mysql>show |Tables_in_test | | | | | | | | | | | 11rowsinset(0.00mysql>select*fromcj;Emptyset(0.00sec)createtableuser(usernamevarchar(30)notnull,passwdchar(2)notnull,uidint(4)notnull,gidint(4)notnull,miaoshuvarchar(60),homedirvarchar(60),bashsvarchar(50)notnull,index(username));mysql>createtableuser(usernamevarchar(30)notnull,passwdchar(2)notnull,uidint(4)notnull,gidint(4)notnull,miaoshuvarchar(60),homedirvarchar(60),bashsvarchar(50)notnull,index(username));QueryOK,0rowsaffected(0.00mysql>desc-> | | |Null|Key|Default|Extra |username|varchar(30)| |MUL| | | | | | | | | | | | | | |varchar(60)|YES | | |varchar(60)|YES | | |varchar(50)| | "intotable表名fieldtermina lby'字段間隔符'linesterminatedby‘\n’"intotable表名fieldtermina lby'字段間隔符'linesterminatedby‘\n’Loaddatainfile文mysql>loaddatainfile'/usr/local/mysql/var/userlist/list.txt'intotableuserfieldsterminatedby':'linesterminatedby'\n';QueryOK,36rowsaffected(0.01Records:36Deleted:0Skipped:0Warnings:mysql>altertableuseraddidint(3)notnullprimarykeyauto_incrementfirst;QueryOK,36rowsaffected(0.04sec)Records:36Duplicates:0Warnings:mysql>select*from +

|id| |passwd| | || | +

|1| | 0 0|| | |2| | 1 1|| |/sbin/nologin|3| | 2 2|| |/sbin/nologin|4| | 3 4|| |/sbin/nologin|5| | 4 7|| |/sbin/nologin|6| | 5 0|| | |7| | 6 0|| |/sbin/shutdown|8| | 7 0|| | |9| | 8 12|| |/sbin/nologin|10| | 9 13|| |11| | 10 14|| |/sbin/nologin|12| | 11 0|| |/sbin/nologin|13| | 12 100|| |/sbin/nologin|14| | 13 30|| |/sbin/nologin|15| | 14 50|FTP| |/sbin/nologin|16| | 99 99|| |/sbin/nologin|17| | 28 28|NSCD| |/sbin/nologin|18| | 69 69|virtualconsolememory| |/sbin/nologin|19| | 32 32|PortmapperRPC| |/sbin/nologin|20| | 47 47| |/sbin/nologin|21| | 51 51| |/sbin/nologin|22|oprofile |x 16| 16|SpecialuseraccounttobeusedbyOProfile|/home/oprofile |/sbin/nologin||23| | 77 77| |/sbin/nologin|24| | 38 38| |/sbin/nologin|25| | 81 81|Systemmessage| |/sbin/nologin|26| | 70 |Avahi|/|/sbin/nologin|27| | 74 |||/sbin/nologin|28| | 29 |RPCService||/sbin/nologin|29| ||65534||AnonymousNFS||/sbin/nologin|30| | 68 |HAL|/|/sbin/nologin|31|avahi-autoipd| 100 |||/sbin/nologin|32| | 43 |XFont||/sbin/nologin|33| | 500 ||| |34| ||2525||||/sbin/nologin|35| ||2526|||| |36| ||2527|||| +

36rowsinset(0.00mysql>desc | | |Null|Key|Default| | | | |PRI| |auto_increment|username|varchar(30)| |MUL| | | | | | | | | | | | | | |varchar(60)|YES | | |varchar(60)|YES | |bashs|varchar(50)| | 8rowsinset(0.00mysql>selectid,bashsfromusergroupbybashs |id|bashs |10 |1| |35| |6| |8| |2|/sbin/nologin|7|/sbin/shutdown 7rowsinset(0.00mysql>selectid,bashsfromuserwherebashs |id|b