如何进行数据库DDL、DML操作及创建新用户表实例？

本文共计975个文字，预计阅读时间需要4分钟。

plaintext//mysqlddl操作创建数据库；mysqlshowdatabases；-------------------- | Database | -------------------- information_schema | aaz | admin-tp6 | cmf | easyadmin | heima | hm | lycms | mysql | newtp | nonecms | performance_schema | pyg | snake | sys

//mysqlddl实操createdropdatabse； mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |aaz| |admin-tp6| |cmf| |easyadmin| |heima| |hm| |lycms| |mysql| |newtp| |nonecms| |performance_schema| |pyg| |snake| |sys| |thinkcmf| |xhhds| |yzncms| +--------------------+ 18rowsinset(0.01sec) mysql>createdatabasewtes; QueryOK,1rowaffected(0.00sec) mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |aaz| |admin-tp6| |cmf| |easyadmin| |heima| |hm| |lycms| |mysql| |newtp| |nonecms| |performance_schema| |pyg| |snake| |sys| |thinkcmf| |wtes| |xhhds| |yzncms| +--------------------+ 19rowsinset(0.00sec) mysql>dropdatabasewtes; QueryOK,0rowsaffected(0.01sec) mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |aaz| |admin-tp6| |cmf| |easyadmin| |heima| |hm| |lycms| |mysql| |newtp| |nonecms| |performance_schema| |pyg| |snake| |sys| |thinkcmf| |xhhds| |yzncms| +--------------------+ 18rowsinset(0.00sec) mysql>uselycms Databasechanged mysql>showtables; +-----------------+ |Tables_in_lycms| +-----------------+ |v1_ad| |v1_banner| |v1_collect| |v1_column| |v1_email_log| |v1_kami| |v1_say| |v1_see_record| |v1_tort| |v1_user| |v1_user_info| |v1_vod| |v1_vod_actor| |v1_vod_director| |v1_vod_play| |v1_vod_type| +-----------------+ 16rowsinset(0.00sec) mysql>createtablev1_test(idint(10),namevarchar(8),sextinyint(1)); QueryOK,0rowsaffected(0.01sec) mysql>showtables; +-----------------+ |Tables_in_lycms| +-----------------+ |v1_ad| |v1_banner| |v1_collect| |v1_column| |v1_email_log| |v1_kami| |v1_say| |v1_see_record| |v1_test| |v1_tort| |v1_user| |v1_user_info| |v1_vod| |v1_vod_actor| |v1_vod_director| |v1_vod_play| |v1_vod_type| +-----------------+ 17rowsinset(0.00sec) mysql>descv1_test ->; +-------+------------+------+-----+---------+-------+ |Field|Type|Null|Key|Default|Extra| +-------+------------+------+-----+---------+-------+ |id|int(10)|YES||NULL|| |name|varchar(8)|YES||NULL|| |sex|tinyint(1)|YES||NULL|| +-------+------------+------+-----+---------+-------+ 3rowsinset(0.01sec) mysql>showcreatetablev1_test; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ |v1_test|CREATETABLE`v1_test`( `id`int(10)DEFAULTNULL, `name`varchar(8)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>altertablev1_testmodifynamevarchar(12); QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetablev1_test ->; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ |v1_test|CREATETABLE`v1_test`( `id`int(10)DEFAULTNULL, `name`varchar(12)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>altertablev1_testaddagetinyint; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetablev1_test; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |v1_test|CREATETABLE`v1_test`( `id`int(10)DEFAULTNULL, `name`varchar(12)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL, `age`tinyint(4)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>altertablev1_testnameunamevarchar(12); ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'nameunamevarchar(12)'atline1 mysql>altertablev1_testmodifynameunamevarchar(12); ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'unamevarchar(12)'atline1 mysql>altertablev1_testchangenameunamevarchar(12); QueryOK,0rowsaffected(0.00sec) Records:0Duplicates:0Warnings:0 mysql>altertablev1_testdropage; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetablev1_test; +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ |v1_test|CREATETABLE`v1_test`( `id`int(10)DEFAULTNULL, `uname`varchar(12)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>altertablev1_testaddagetinyintbeforesex; ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'beforesex'atline1 mysql>altertablev1_testaddagetinyintafteruname; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>altertablev1_testuidint(11)first; ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'uidint(11)first'atline1 mysql>altertablev1_testadduidint(11)first; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetablev1_test; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |v1_test|CREATETABLE`v1_test`( `uid`int(11)DEFAULTNULL, `id`int(10)DEFAULTNULL, `uname`varchar(12)DEFAULTNULL, `age`tinyint(4)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>altertablev1_testrenametest; QueryOK,0rowsaffected(0.00sec) mysql>showcreatetableest; ERROR1146(42S02):Table'lycms.est'doesn'texist mysql>showcreatetabletest; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |test|CREATETABLE`test`( `uid`int(11)DEFAULTNULL, `id`int(10)DEFAULTNULL, `uname`varchar(12)DEFAULTNULL, `age`tinyint(4)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8| +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>createindexid_indexontest(id); QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>createuniqueindexuid_oontest(uid); QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetabletest; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |test|CREATETABLE`test`( `uid`int(11)DEFAULTNULL, `id`int(10)DEFAULTNULL, `uname`varchar(12)DEFAULTNULL, `age`tinyint(4)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL, UNIQUEKEY`uid_o`(`uid`), KEY`id_index`(`id`) )ENGINE=MyISAMDEFAULTCHARSET=utf8| +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>dropindexid_indexontest; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetabletest; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |test|CREATETABLE`test`( `uid`int(11)DEFAULTNULL, `id`int(10)DEFAULTNULL, `uname`varchar(12)DEFAULTNULL, `age`tinyint(4)DEFAULTNULL, `sex`tinyint(1)DEFAULTNULL, UNIQUEKEY`uid_o`(`uid`) )ENGINE=MyISAMDEFAULTCHARSET=utf8| +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>showindexfromtest; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |test|0|uid_o|1|uid|A|NULL|NULL|NULL|YES|BTREE||| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1rowinset(0.00sec) mysql>showindexfromtestG; ***************************1.row*************************** Table:test Non_unique:0 Key_name:uid_o Seq_in_index:1 Column_name:uid Collation:A Cardinality:NULL Sub_part:NULL Packed:NULL Null:YES Index_type:BTREE Comment: Index_comment: 1rowinset(0.00sec) ERROR: Noqueryspecified mysql> //mysqlDML实操 mysql>insertintotest(uid,id,uname,age,sex)values(1,1,'yyds',11,1); QueryOK,1rowaffected(0.01sec) mysql>select*fromtest; +------+------+-------+------+------+ |uid|id|uname|age|sex| +------+------+-------+------+------+ |1|1|yyds|11|1| +------+------+-------+------+------+ 1rowinset(0.00sec) mysql>insertintotest(uid,id,uname,age,sex)values(2,2,'yyds',11,1); QueryOK,1rowaffected(0.00sec) mysql>select*fromtest; +------+------+-------+------+------+ |uid|id|uname|age|sex| +------+------+-------+------+------+ |1|1|yyds|11|1| |2|2|yyds|11|1| +------+------+-------+------+------+ 2rowsinset(0.00sec) mysql>deletefromtestwhereuid=2; QueryOK,1rowaffected(0.00sec) mysql>select*fromtest; +------+------+-------+------+------+ |uid|id|uname|age|sex| +------+------+-------+------+------+ |1|1|yyds|11|1| +------+------+-------+------+------+ 1rowinset(0.00sec) mysql>updatetestsetuname='xswl'whereid=1; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 mysql>select*fromtest; +------+------+-------+------+------+ |uid|id|uname|age|sex| +------+------+-------+------+------+ |1|1|xswl|11|1| +------+------+-------+------+------+ 1rowinset(0.00sec)

创建一张用户表

mysql>createtableuser( ->uidint(10)notnullauto_incrementcomment'用户ID', ->unamevarchar(10)notnull, ->pwdchar(32)notnull, ->sextinyint(1)notnull, ->agetinyintnotnull, ->telchar(11)notnull, ->create_timeint(10)notnull, ->update_timeint(10)notnull, ->PRIMARYKEY(`uid`), ->UNIQUEKEY(`uname`) ->)engine=innodbauto_increment=20001defaultcharset=utf8; QueryOK,0rowsaffected(0.02sec) mysql>showcreatetableuser; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |user|CREATETABLE`user`( `uid`int(10)NOTNULLAUTO_INCREMENTCOMMENT'用户ID', `uname`varchar(10)NOTNULL, `pwd`char(32)NOTNULL, `sex`tinyint(1)NOTNULL, `age`tinyint(4)NOTNULL, `tel`char(11)NOTNULL, `create_time`int(10)NOTNULL, `update_time`int(10)NOTNULL, PRIMARYKEY(`uid`), UNIQUEKEY`uname`(`uname`) )ENGINE=InnoDBAUTO_INCREMENT=20001DEFAULTCHARSET=utf8| +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) mysql>