MySQL心得2

1.创建数据库 使用create database或create schema（以后再介绍）命令可以创建数据库。 create database 库名 create database if not exists 库名（创建库并检验创建的库是否存在，不存在则建，存在就不建了） MySQL不允许两个数据库使用相同的名字，使用if

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’ //索引的路径