MySQL（转+整理）

 

MySql数据库的基本操作

 

 

一、字段数据类型：

1、布尔型  BOOL                   1字节

       0---false        非0---true

 

2、微整型    tinyint    1字节

      unsigned(无符号数)范围0到255

      有符号数范围为-128到127

 

3、小整型  smallint  2字节

      unsigned(无符号数)范围0到65535

      有符号数范围为-32768到32767

 

4、中整型  mediumint  3字节

      unsigned(无符号数)范围0到16777215

      有符号数范围为-8388608到8388607

 

5、整型  int  4字节

      unsigned(无符号数)范围0到4294967295

      有符号数范围为-2147483648到2147483647 

6、大整型  bigint  8字节

      unsigned(无符号数)范围0到18446744073709551615

      有符号数范围为-9223372036854775808到9223372036854775807

 

7、单精度类型  float  4字节    最多10位有效数字

      允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38

 

8、双精度类型  double  8字节    最多17位有效数字

     允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308

 

10、精确小数型  decimal(M,D)  M<=30

    M是精度的总数，D是小数点后面的位数。

 

11、日期型  date  3

     日期。支持的范围为‘1000-01-01’到‘9999-12-31’。

12、日期时间型  datetime  8字节

     日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

 

 

13、时间戳型  timestamp  4字节

     时间戳。范围是'1970-01-01 00:00:00'到2037年。

TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。

 

14、时间型  time  3字节

     时间。范围是'-838:59:59'到'838:59:59'。

 

15、年期型  year  1

      两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。 

16、字符类型  char(M)  M<=255    汉字与英文字符计算方法相同

     固定长度字符串，当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255个字符。

     注：当检索CHAR值时尾部空格被删除。

 

17、变长字符类型varchar(M)  M<=255    汉字与英文字符计算方法相同

      变长字符串。M表示最大列长度。

      注：当检索时不删除VARCHAR值的尾部空格。

 

18、二进制类型      BINARY(M)  M<=255

     固定长度二进制数，不足添0。

     注：当检索BINARY值时尾部0被删除。

 

19、二进制类型      VARBINARY(M)  M<=255

     变长二进制数。

     注：当检索时不删除尾部0。

 

20、微对象类型      TINYBLOB

     最大长度为255的二进制数。

21、微文本类型  TINYTEXT 

      最大长度为255的字符串。

 

22、对象类型       BLOB

      最大长度为65,535的二进制数。

 

23、文本类型      TEXT

       最大长度为65,535的字符串。

 

24、中对象类型      MEDIUMBLOB

       最大长度为16,777,215的二进制数。

 

25、中文本类型      MEDIUMTEXT

       最大长度为16,777,215的字符串。

 

26、长对象类型      LONGBLOB

       最大长度为4,294,967,295的二进制数。

 

27、长文本类型      LONGTEXT

       最大长度为4,294,967,295的字符串。

28、枚举类型  ENUM  

      格式：ENUM('value1','value2',...)

      只能有一个值的字符串，从值列‘value1’，‘value2’，...，NULL中或特殊 ‘’错误值中选出。ENUM列最多可以有65,535个截然不同的值。

 

29、集合类型       SET

       格式： SET('value1','value2',...)

       字符串对象可以有零个或多个值，每个值必须来自列值‘value1’，‘value2’，...SET列最多可以有64个成员。

 

二、字段相关关键词：

     1、primary key          主键字段

 

     2、auto_increment    自动增加字段

 

     3、null及not null        允许为空或不允许为空字段

 

     4、unsigned              无符号数

          unsigned zerofill   无符号零填充数

          on update current_timestamp   用当前时间戳做为字段的默认值

     5、default   ‘值’              指定默认值

 

     6、check(约束表达式)    为字段设置约束

 

     7、FOREIGN KEY (外键表字段) REFERENCES 主键表(主键字段)

          设置主外键关系。

 

 

 

 三、mysql查询语句：

 

     1、Create Table语句：

      格式：

          Create Table  表名

          (

             字段名  数据类型 [auto_increment]  [primary key] [null | not null] [default ‘默认值’][check(约束表达式)],

             …

             [,foreign key(字段)  references 主键表(主键字段) ]

          )

     2、Alter Table语句：

 

      格式1：

          Alter Table  表名

          ADD 字段名数据类型

 

      格式2：

          Alter Table  表名

          Drop column 字段名

 

 

      3、drop Table语句：

 

      格式：

          drop Table  表名

     4、insert into语句：

 

      格式：

          insert into 表名 [(字段列表)] values (值列表)[, (值列表)]

 

 

      5、update语句：

 

      格式：

          update 表名 set 字段1=值1[,字段2=值2] [where 条件]

 

      6、delete语句

      格式：

           delete from 表名 [where 条件]

 

       7、truncate语句

       格式：

            truncate 表名

 

       8、select语句及子查询

     

       格式：

       select *|字段列表  from 表列表

       [where 条件] 

       [group by 字段 [asc|desc]] 

       [having 条件]

       [order by 字段 [asc | desc]]

       [limit [开始记录序号,]操作记录数]

 

 

      1、字段别名：

          字段名  as 别名

 

       2、、表列表：

            1)、内联：         inner   join

            2)、左外联：     left [outer] join

            3)、右外联：     right [outer] join

            4)、交叉连联接：   cross join                 

      3、limit [开始记录序号,]操作记录数：

        limit子句的格式:只能放在select 语句结束

 

        格式1:

              limit  数字

              从第一条记录(索引号为0)开始并包括第一条记录，选取数字 指定的那么多条记录

 

        格式2:

              limit  数字1,数字2

              从数字1(索引号)对应的记录开始(包括该记录)，选取数字2 指定的那么多条记录

              如果数字1为0，则

              limit  0,数字2            等同于           limit  数字2

       

        如何实现分页？

       select语句 limit (PageNo-1)*PageSize,PageSize

 

   四 、统计函数：

        1)、count(*|字段名)

        统计记录总数

 

        2)、sum(字段名表达式)

        求和

 

        3)、avg(字段名表达式)

        求平均值

 

        4)、max(字段名表达式)

        求最大值

 

 

        5)、min(字段名表达式)

        求最小值

 

   五、视图管理：

        1)、创建视图

        格式：create view 视图名  as   select语句

 

        2)、修改视图

        格式：alter view 视图名  as   select语句

 

        3)、删除视图

        格式：drop view 视图名

 

     六、索引管理：

        1)、创建索引

        格式：create index 索引名 on 索引表(字段1 [asc|desc],…,字段n [asc|desc])

 

        2)、删除索引

        格式：drop index  索引名 on 索引表

 

    七、mysql中设定存取权限的SQL语句：

格式：GRANT  ALL|{SELECT,INSERT,UPDATE,DELETE}  ON 数据库.表  TO  用户名@"主机名"   IDENTIFIED  BY  "密码"

其中数据库或表名中可以使用*表示不限制数据库或表，主机名中可以使用%表示用户可以从哪些主机上连接数据库，并指定密码。

例：增加一个用户testuser授权其能够对testdata进行所有操作，并可以从任何主机连接数据库。

 

GRANT  ALL  ON  testdata.*  TO  testuser@"%" IDENTIFIED BY "12345678“

 

例：增加一个用户testuser授权其能够对testdata进行查询操作，并可以从任何主机连接数据库。

 

GRANT  SELECT  ON  testdata.* TO  testuser@"%" IDENTIFIED BY "12345678"

flush privileges;  //刷新MySql中的权限表

 

 

八、MySql中删除用户的SQL语句：

格式：DELETE  FROM  mysql.user  WHERE  user='用户名';

例：

     DELETE  FROM  mysql.user  WHERE User='testuser';

     flush privileges;  //刷新MySql中的权限表

 

 

九、修改用户密码的SQL语句：

格式：SET PASSWORD FOR 用户名=PASSWORD("新密码")

例:

SET PASSWORD FOR testuser= PASSWORD("123");

 

十、数据库的备份与恢复语句：

1)、数据库的备份：

格式：mysqldump --user=用户名 --password=密码数据库 >文件名.sql

例1：手工备份

例2：创建备份计划任务

    D:\WINNT\system32\CMD.EXE /c "mysqldump --user=root --password=123456 testdata >c:\test.sql"

   或

    下面这种备份可能存在执行权限不够的问题：

echo ` D:\\WINNT\\system32\\CMD.EXE /c "mysqldump --user=root --password=123456 testdata >c:\\test.sql"`;

 

2)、数据库的恢复：

 

格式：mysql --user=用户名 --password=密码数据库 <文件名.sql

mysql --user=root --password=123456 testdata <c:\test.sql

 

下面这种恢复可能存在执行权限不够的问题：

echo `D:\\WINNT\\system32\\CMD.EXE /c "mysql --user=root --password=123456 testdata <c:\\test.sql"`;