MySQL心得2--命令行方式建库和表_MySQL

bitsCN.com 1.创建数据库 使用create database或create schema命令可以创建数据库。 create database 库名 create database if not exists 库名（创建库并检验创建的库是否存在，不存在则建，存在就不建了） MySQL不允许两个数据库使用相同的名字，使用ifnot exists从句可以不显示错误信息 显示所有数据库： showdatabases; 显示创建数据库的定义信息： show create dabasese 库名 2. 创建了数据库之后使用USE命令可指定当前数据库。 语法格式：use db_name; 说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用create database语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。 注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。 mySQL语法中不区分大小写。创建数据库的必须是系统管理员，或者拥有用户级别的create权限。在安装MySQL的过程中已经创建了系统管理员，名为root，假设密码为root。 3.创建库的同时指定字符集,校对规则： create database mydb character set utf8 collate utf8-general_ci; character set：指定数据库字符集（Charset），collate：指定字符集的校对规则 4.修改数据库，只能改参数 数据库创建后，如果需要修改数据库的参数，可以使用alter database命令。 语法格式： alter {database | schema} [db_name] alter_specification [, alter_specification] ... 其中alter_specification： [default] character set charset_name | [default] collate collation_name 说明： alter database用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可以使用 alterdatabase。修改数据库的选项与创建数据库相同，功能不再重复说明。如果语句中数据库名称忽略，则修改当前（默认）数据库。 5.删除数据库 drop database 库名 drop database if exists 库名(避免删除不存在的数据库时出现的MySQL错误信息。) 注意：这个命令必须小心使用，因为它将删除指定的整个数据库，该数据库的所有表（包括其中的数据）也将永久删除。 6.创建表 创建表就是创建表的结构：包含哪些字段（属性），字段名，字段类型 create table 表名 (字段名1 类型, 字段名2 类型， 。。。。 ); 举例：创建一个学生表 use 库名（确定你的表建在哪个库中，把此库变为当前数据库）； create table xs ( id int, name char(10), sex char(2) ); Create [temporary] table [if not exists] tbl_name [ ( [column_definition] , ... | [index_definition] ) ] [table_option] [select_statement]; 说明： ● temporary：该关键字表示用create命令新建的表为临时表。不加该关键字创建的表通常称为持久表，在数据库中持久表一旦创建将一直存在，多个用户或者多个应用程序可以同时使用持久表。有时候需要临时存放数据，例如，临时存储复杂的select语句的结果。此后，可能要重复地使用这个结果，但这个结果又不需要永久保存。这时，可以使用临时表。用户可以像操作持久表一样操作临时表。只不过临时表的生命周期较短，而且只能对创建它的用户可见，当断开与该数据库的连接时，MySQL会自动删除它们。 ● 创建表时也可使用if not exists语句来判断创建的表是否已存在，避免出现错误。 ● 要创建的表的表名（tbl_name）必须符合标志符规则，如果有MySQL保留字必须用单引号括起来。 ● column_definition：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。 ● index_definition：表索引项定义，主要定义表的索引、主键、外键等，具体定义将在后面讨论。 ● table_option：用于描述表的选项。 ● select_statement：可以在create table语句的末尾添加一个select语句，在一个表的基础上创建表 7.修改表结构：增加字段 修改旧字段 删除字段 alter table 表名 add 字段名 类型（宽度） //增加字段 modify 旧字段 类型 //修改旧字段 drop 字段名 //删除字段 change 旧字段名 新字段内容 //改列名 order by col_name //排序 convert to character set charset_name [字符集名] //将字符集转换为二进制 [default]character set charset_name [字符集名] //修改默认字符集 举例：在学生表中增加birthday字段、修改name字段、修改字段name的名； alter table xs add birthday date; modify name varchar(20); change name sname varchar(20); 8.修改表名 rename table 旧名 to 新名 分类 数据类型 说明 数值类型 bit (M) tinint [unsigned] [zerofill] bool，boolean smallint [unsigned] [zerofill] int [unsigned] [zerofill] bigint [unsigned] [zerofill] float[(M,D)][unsigned][zerofill] double[(M,D)][unsigned][zerofill] 位类型。M指定位数，默认值1，范围1-64 带符号的范围是-128到127。无符号0到255。 使用0或1表示真或假 2的16次方 2的32次方 2的64次方 M指定显示长度，d指定小数位数 表示比float精度更大的小数 文本、二进制类型 char(size) char(20) varchar(size) varchar(20) blob longblob text(clob) longtext(longclob) 固定长度字符串 可变长度字符串 二进制数据 大文本 时间日期 date/datetime/TimeStamp 日期类型(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间 注意： 在字符数据类型和数值数据类型之后，MySQL允许指定一个数据类型选项用来改变数据类型的属性和功能。 对于字符数据类型，MySQL支持两种数据类型选项：characterset和collate。如果要区分字符的大小写情况，可以在字符类型后面加上bingary。 对于除bit以外的数值数据类型，MySQL允许添加一个或多个数据类型选项。unsigned：不允许负值。zerofill：当插入的值长度小于字段设定的长度时，剩余部分用0填补。 与int有关的类型与java中的bit、short、int、long分别对应。 varchar、bolb和text类是变长类型。每个类型的存储需求取决于列值的实际长度。 9. 列定义column_definition格式如下： col_name type [not null | null] [default default_value] [auto_increment] [unique [key] | [primary] key] [comment 'string'] [reference_definition] 说明： ● col_name：表中列的名字。列名必须符合标志符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字必须用单引号括起来。 ● type：列的数据类型，有的数据类型需要指明长度n，并用括号括起，MySQL支持的数据类型在附录C中介绍。 ● auto_increment：设置自增属性，只有整型列才能设置此属性。当插入null值或0到一个auto_increment列中时，列被设置为value+1，在这里value是此前表中该列的最大值。auto_increment顺序从1开始。每个表只能有一个auto_increment列，并且它必须被索引。 ● not null | null：指定该列是否允许为空。如果不指定，则默认为null。 ● default default_value：为列指定默认值，默认值必须为一个常数。其中，BLOB和TEXT列不能被赋予默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL值，默认值就是NULL。如果列被声明为NOT NULL，默认值取决于列类型： （1）对于没有声明auto_increment属性的数字类型，默认值是0。对于一个auto_increment列，默认值是在顺序中的下一个值。 （2）对于除timestamp以外的日期和时间类型，默认值是该类型适当的“零”值。对于表中第一个timestamp列，默认值是当前的日期和时间。 （3）对于除enum的字符串类型，默认值是空字符串。对于enum，默认值是第一个枚举值。 ● unique key | primary key：两者都表示字段中的值是唯一的。primary key表示设置为主键，一个表只能定义一个主键，主键一定要为notnull。 ● comment 'string'：对于列的描述，string是描述的内容。 ● reference_definition：指定参照的表和列。 10. (1).复制表 : create table 表名 like 表名1 语法格式： create [temporary] table[if not exists] tbl_name [ ( )like old_ name [ ] ] | [as (select_statement)] ; 说明： 使用LIKE关键字创建一个与old _name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。使用as关键字可以复制表的内容，但索引和完整性约束是不会复制的。select_statement表示一个表达式，例如，可以是一条select语句。 (2).删除表 : drop table 表名（这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除） (3).修改表名 : rename子句，语法格式： rename table old_name to new_name new_tbl_name是新表名。例如，将表a改名为b：alter table a rename to b ;除了上面的alter table命令，还可以直接用renametable语句来更改表的名字。 ● order by子句(以后介绍)：用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。在对表进行了大的改动后，通过使用此选项，可以提高查询效率。在有些情况下，如果表按列排序，对于MySQL来说，排序可能会更简单。 ● table_options：修改表选项，具体定义与create table语句中一样。 可以在一个alter table语句里写入多个add、alter、drop和chang子句，中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中，每个alter table语句中每个子句只允许使用一次。 11.说明：表中大多数的选项涉及的是表数据如何存储及存储在何处。多数情况下，不必指定表选项。engine选项是定义表的存储引擎，具体在附录E中介绍。 举例： USE mydb1; CREATE TABLE XS ( 学号 char(6) NOTNULL primary KEY, 姓名 char(8) NOTNULL , 专业名 char(10) NOT NULL , 性别 tinyint(1) NOT NULL default 1 ) engine=InnoDB; 说明：“primary KEY”表示将“学号”字段定义为主键。“default 1”表示“性别”的默认值为1。“engine=InnoDB”表示采用的存储引擎是InnoDB，InnoDB是MySQL在Windows平台默认的存储引擎，所以“engine=InnoDB”可以省略。 12.说明： ● ignore：是MySQL相对于标准SQL的扩展。若在修改后的新表中存在重复关键字，如果没有指定ignore，当重复关键字错误发生时操作失败。如果指定了ignore，则对于有重复关键字的行只使用第一行，其他有冲突的行被删除。 ● column_definition：定义列的数据类型和属性，具体内容在create table的语法中已做说明。 ● first| after col_name：表示在某列的前或后添加，不指定则添加到最后。 注意:若表中该列所存数据的数据类型与将要修改的列的类型冲突，则发生错误。例如，原来char类型的列要修改成int类型，而原来列值中有字符型数据“a”，则无法修改。 13. 1）. 空值(NULL)概念 空值通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出该列的具体值。 注意：表的关键字不允许为空值。空值不能与数值数据0或字符类型的空字符混为一谈。任意两个空值都不相等。 2）. 列的IDENTITY(标志）属性 对任何表都可创建包含系统所生成序号值的一个标志列，该序号值唯一标志表中的一列，可以作为键值。每个表只能有一个列设置为标志属性，该列只能是decimal、int、numeric、smallint、bigint 或tinyint 数据类型。定义标志属性时，可指定其种子（即起始）值、增量值，二者的默认值均为 1。系统自动更新标志列值，标志列不允许空值。 在下列情况下，MySQL隐含地改变在一个CREATE TABLE语句给出的一个列类型（这也可能在ALTER TABLE语句上出现）。 14.MySQL隐含地改变列类型： （1）长度小于4的varchar被改变为char。 （2）如果在一个表中的任何列有可变长度，结果使整个行是变长。 因此，如果一张表包含任何变长的列（varchar、text或Blob），所有大于3个字符的char列被改变为varchar列。这在任何方面都不影响用户如何使用列。在MySQL中这种改变可以节省空间并且使表操作更快捷。 （3）timestamp的显示尺寸必须是偶数且在2～14的范围内。如果指定0显示尺寸或比14大，尺寸被强制为14。从1～13范围内的奇数值尺寸被强制为下一个更大的偶数。 （4）不能在一个timestamp列里面存储一个NULL，将它设为NULL默认为当前的日期和时间。 如果想要知道MySQL是否使用了除指定的以外的一种列类型，在创建表之后，使用一个DESCRIBE语句即可。DESCRIBE语句在3.1.4节介绍。 15 type定义如下： 说明：在字符数据类型和数值数据类型之后，MySQL允许指定一个数据类型选项用来改变数据类型的属性和功能。 对于字符数据类型，MySQL支持两种数据类型选项：CHARACTERSET和COLLATE。如果要区分字符的大小写情况，可以在字符类型后面加上BINGARY。 对于除BIT以外的数值数据类型，MySQL允许添加一个或多个数据类型选项。UNSIGNED：不允许负值。ZEROFILL：当插入的值长度小于字段设定的长度时，剩余部分用0填补。 spatial_type是空间类型数据。 16. 表选项table_option定义如下： {engine | type} = engine_name //存储引擎 | auto_increment = value //初始值 | auto_increment = value //表的平均行长度 | [default] charcter set charset_name [collatecollation_name] //默认字符集和校对 | checksum = {0 | 1} //设置为1表示求校验和 | comment= 'string' //注释 | connection = 'connect_string' //连接字符串 | MAX_ROWS = value //行的最大数 | MIN_ROWS = value //列的最小数 | PACK_KEYS = {0 | 1 | DEFAULT} | password = 'string' //对.frm文件加密 | delay_key_write = {0 | 1} //对关键字的更新 |row_format={DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} //定义各行应如何储存 | union = (tbl_name[,tbl_name]...) //表示哪个表应该合并 | insert_method = { NO | FIRST | LAST}//是否执行INSERT语句 |data directory = 'absolute path todirectory' //数据文件的路径 | index directory = 'absolute path todirectory’ //索引的路径 作者 tianyazaiheruan bitsCN.com