MySQL数据库之操作类型

MySQL数据库操作

1. MySQL数据库基本操作

A.  数据类型

（1）整数类型

整数类型	字节数	无符号数取值	有符号数取值
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
INTEGER	4	0~4294967295	-2147483648~2147483647
BIGINT	8	18446744073709551615	+_9223372036854775808

 

（2）浮点数与定点数类型

整数类型	字节数
FLOAT	4
DOUBLE	8
DECIMAL(M,D)或DEC	M+2

 

（3）日期与实践类型

整数类型	字节数	格式
YEAR	1	0000
DATE	4	0000:00:00
TIME	3	00:00:00:
DATETIME	8	0000-00-00 00:00:00
TIMESTAMP	4	0000000000000

 

（4）字符串类型

类型	字节数
CHAR(n)	固定n字节
VARCHAR(n)	可变长，最大为n字节
TINYTEXT	255
TEXT	65535
MEDIUMTEXT	167772150
LONGTEXT	4294967295
ENUM	(‘值1’，’值2’，、、、，’值n’)
SET	(‘值1’，’值2’，、、、，’值n’)

 

（5）二进制类型

类型	字节数
BINARY(M)	字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)	允许长度为0~M的变长二进制字符串，字节数为值的长度加1
BIT(M)	M位二进制数据，M最大值为64
TINYBLOB	可变长的二进制数据，最多255字节
BLOB	可变长的二进制数据，最多（2^16-1）字节
MEDIUMBLOB	可变长的二进制数据，最多（2^24-1）字节
LONGBLOB	可变长的二进制数据，最多（2^32-1）字节

 

B．操作数据库

1)        创建：create database 表名 ;

2)        删除：drop database 表名 ;

3)        存储引擎：show engines ;show variables like ‘have%’ ;show variables like ‘storage_engine’;

提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。支持自动增长列AUTO_INCREMENT.

 

C操作表

-----------------------------------------------------创建表-----------------------------------------------------------------

1)        创建：

Create table 表名(属性名     数据类型         [完整性约束条件] ，

                                     属性名     数据类型         [完整性约束条件] ，

                                     。。。

                                     属性名     数据类型

                                     );

2)        完整性约束条件表：

约束条件	说明
PRIMARY KEY	标识该属性为表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为表的未见，是与之联系的某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREAMENT	标识该属性的值自动增加，这是MySQL的特色
DEFAULT	为该属性设置默认值

 

3)        设置主外键：a.单字段主键-----    属性名     数据类型         PRIMARY KEY

b.多字段主键-----    PRIMARY KEY(属性名1，属性名2，。。。，属性名n)

c.外键-----         CONSTRAINT   外键别名         FOREIGN KEY   （属性1.1，属性1.2，。。。，属性1.n）         REFERENCES    表名（属性2.1，属性2.2，。。。，属性2.n）

4)        设置表的非空约束：属性名         数据类型         NOT NULL

5)        设置表的唯一约束：属性名         数据类型         UNIQUE

6)        设置表的属性值自动增加：属性名     数据类型         AUTO_INCREMENT

7)        设置表的属性的默认值：属性名         数据类型         DEFAULT  默认值

8)        查看表：describe 表名 ;

9)        查看表结构：show create table 表名 ; show tables ;

-----------------------------------修改表---------------------------------------------------------------------------

10)    修改表名：alter table old表名 re表名 [to] new表名 ;

11)    修改字段的类型：alter table 表名 modify 属性名   数据类型

12)    修改字段名：alter table 表名 change 旧属性名      新属性名         新数据类型

13)    增加字段：alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first | after 属性名2]

14)    删除字段：alter table 表名 drop 属性名

15)    修改字段的排列位置：alter table 表名 modify 属性名1 数据类型 first | after 属性名2

16)    更改表的存储引擎：alter table 表名 engine=存储引擎名

17)    删除表的外键约束：alter table 表名 drop foreign key 外键名

18)    删除表：a.无关联的表-----drop table 表名

b.有关联的父表-----先删除子表的外键约束，在删除父表

 

 

 

D．索引

含义：索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。

优点：可以提高检索数据的速度。

缺点：创建和维护索引需要耗费时间，占用物理空间。

 

1)        创建索引：create table 表名（属性名数据类型 [完整性约束条件]，

属性名 数据类型 [完整性约束条件]，

。。。

属性名 数据类型

[unique | fulltext | spatial] index | key

[别名] （属性名1 [(长度)] [asc | desc]）

）;

2)        在已经存在的表上创建索引：create [unique | fulltext | spatial] index 索引名

On 表名 （属性名 [（长度）] [asd |desc]）;

3)        用alter table语句来创建索引：alter table 表名 add  [unique | fulltext |spatial]

Index 索引名 （属性名 [（长度）] [asd |desc]）

4)        删除索引：drop index 索引名 on 表名；

 

 

 

E．视图

含义：视图是从一个或多个表中导出来的表，是一种虚拟存在的表。

优点：视图提供给用户看自己关心的数据而不用看到整个数据库表，使用户的操作更方便，而且可以保障数据库系统的安全性。

1)        创建视图：create [algorithm={undefined | merge | temptable}]

View 视图名 [（属性清单）] as select 语句

[with [cascaded | local ] check option ] ；

2)        查看视图：describe 视图名；show table status like ‘视图名’ ；show createview 视图名

Select * from information_schema.views ;

3)        修改视图：create or replace [algorithm={undefined | merge | temptable}]

View 视图名 [(属性清单)]    as select 语句

[with [cascaded | local ] check option] ;

4)        Alter语句修改视图：alter[algorithm={undefined | merge | temptable}]

View 视图名 [(属性清单)]    as select 语句

[with [cascaded | local ] check option] ;

5)        删除视图：drop view [if exists] 视图名列表 [restrict | cascade]

 

 

F．触发器

含义：触发器是由insert、update、delete等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。

优点：保证某些操作的一致性。

1)        创建只有一个执行语句的触发器：create trigger 触发器名 before | after 触发事件

On 表名 for each row 执行语句

2)        创建有多个执行语句的触发器：delimiter &&

create trigger 触发器名 before | after 触发事件

On 表名 for each row

Begin

         执行语句列表

End

&&

Delimiter ；

3)        查看触发器：show trigger ；

4)        在trigger表中查看触发器信息：select * from information_schema.triggers ;

5)        删除触发器：drop trigger 触发器名；

 

 

 

 

 

2.SQL语句

A．基本查询语句

1)        Select ：select 属性列表 from 表名和视图列表 【where 条件表达式1】

【group 属性名1 【having 条件表达式2】】

【order by 属性名2 【asc | desc 】】

2)        带in关键字的查询：【not】 in （元素1，元素2，。。。，元素n）

3)        带between and的查询范围：【not】between 取值1 and 取值2

4)        带like的字符匹配查询：【not】like ‘字符串’

5)        查询空值： is[not] null

6)        带and的多条件查询：条件表达式1 and 条件表达式2 【。。。and 条件表达式n】

7)        带or的多条件查询：条件表达式1 or 条件表达式2 【。。。or 条件表达式n】

8)        查询结果不重复：selectdistinct 属性名

9)        对查询结果排序：orderby 属性名 【asc | desc】

10)    分组查询：groupby 属性名 【having 条件表达式】【withrollup】

11)    使用函数的查询：select属性名n,函数（属性名）from 表名 group by 属性名

12)    使用limit限制查询结果的数量：limit 记录数

13)     使用集合函数查询：select函数（属性名）from 表名

14)     内连接查询：select属性名 from 表名 where 表1.属性=表2.属性

15)     外连接查询：select属性名 from 表1 left | right join 表2 on 表1.属性1=表2.属性2

16)    带in，比较运算符的关键字的子查询：select 属性名 from 表 where 属性名

 【in | 比较运算符 |exist】【any | all】

 (select 属性名 from 表)

17)    合并查询结果：select 语句1 union | union all

Select 语句2 union | union all

Select 语句n ;

18)    为表、字段取别名：表名表别名   ；   属性名 【as】 别名

19)     使用正则表达式查询：属性名 regexp ‘匹配方式’

正则表达式的模式字符	含义
^	匹配字符串开始的部分
$	匹配字符串结束的部分
.	代表字符串中的任意一个字符
[字符集合]	匹配“字符集合”中的任何一个字符
[^字符集合]	匹配除了“字符集合”以外的任何一个字符
S1 | S2 | S3	匹配S1、S2、和S3中的任意一个字符
*	代表多个该符号之前的字符，包括0和1个
+	代表多个该符号之前的字符，包括1个
字符串{N}	字符串出现N次
字符串{M,N}	字符串出现至少M次，最多N次

B．插入、更新、删除

1)        为所有字段插入：insert into 表名value(值1，值2，。。。，值n) ;

：insert into 表名(属性1，属性2，。。，属性n)

 value(值1，值2，。。。，值n) ;

2)        为表的指定字段插入：insert into 表名（属性1，属性2，。。，属性m） ;

3)        同时插入多条记录：insert into 表名【属性列表】

Value （取值列表1）（取值列表2）

。。。

（取值列表n） ;

4)        将查询结果插入表中：insert into 表名1 （属性列表1）

Select 属性列表 2 form 表名2 where 条件表达式

 

5)        更新数据：update 表名 set 属性名1 = 取值1，属性名2 = 取值2，

。。。，

属性名 n = 取值n,

Where 条件表达式；

6)        删除数据：delete from 表名【where 条件表达式】；

C．运算符

1)        MySQL的算数运算符

符号	表达式形式	作用
+	X1 + X2 +…+ Xn	加法运算
-	X1 - X2 -…- Xn	减法运算
*	X1 * X2 *…* Xn	乘法运算
/	X1 / X2 /…/ Xn	除法运算
DIV	X1 DIB X2	除法运算
%	X1 % X2	求余运算
MOD	MOD(x1,x2)	求余运算

 

2)        MySQL的比较运算符

符号	表达式形式	作用
=，！=，>,>=,<,<=	X1 符号 X2	判断X1是否符号X2
IS （NOT） NULL	X1 IS NULL	判断X1是否为空
BETWEEN AND	X1 between m and n
IN
LIKE
REGEXP	X1 REGEXP 正则表达式

 

 

3)        逻辑运算符：

符号	名称
&& 或者AND	与
|| 或者OR	或
！ 或者 NOT	非
XOR	异或

 

D．函数

1)        数学函数表：

函数	作用
ABS(x)	返回x的绝对值
CEIL(x),CEILING(x)	返回大于或等于x的最小整数
FLOOR(x)	返回小于或等于x的最大整数
RAND(),RAND(x)	返回0~1的随机数，x值相同时返回的随机数相同
SIGN(x)	返回x的符号，x是负数、0、正数分别返回-1、0、1
PI()	返回圆周率
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值
ROUND(x)	返回离x最近的整数
ROUND（x,y）	保留x小数点后y位的值，但截断时要进行四舍五入
POW(x,y)	返回x的y次方
SORT(x)	返回x的平方根
EXP(x)	返回e的x次方
MOD(x,y)	返回x除以y以后的余数
LOG(x)	返回自然对数

2)        字符串函数

 

3)        日期函数

4)        将日期格式化的函数

5)        系统信息函数

6)        加密函数：password(str)      ,encode(str,pswd_str)

7)        解密函数：decode(crypt_str,pswd_str)

8)        不同进制的数字进行转换的函数：ASCII(s),BIN(x),HEX(x),OCT(x),CONV(x,f1,f2)

9)        重复执行指定操作的函数：BENCHMARK(count,expr)

10)    改变字符集的函数：CONVERT(s USING cs)

11)    改变字段数据类型的函数：CAST(x AS type)

 

E．存储过程和函数

含义：将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。

优点：存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

1)        创建存储过程：create procedure sp_name (proc_parameter [,…] )

[characteristic…] routine_body

2)        创建存储函数：create function sp_name (func_parameter [,…] ) [characteristic …]

routine_body

3)        定义变量：declare var_name[,…] type 【default value】

4)        变量赋值：declare var_name = express 【，var_name = express】

5)        定义条件：declare condition_name condition for condition_value

Condition_value:

Sqlstate [value]sqlstate_value | mysql_error_code

6)        定义处理程序：declare handler_type Handler for condition_value [,…]sp_state

Handler_type:

Continue | exit| undo

Condition_value:

Sqlstate [value]sqlstate_value | condition_name | sqlwarning

Not found |sqlexception | mysql_error_code

7)        声明光标：declare cursor_name cursor for select_statement ;

8)        打开光标：open cursor_name

9)        使用光标：fetch cur_employee into var_name [,var_name…]

10)    关闭光标：close cursor_name

11)     If语句：if search_condition then statement_list

[elseif search_condition then statement_list]…

[else statement_list]

End if

12)    Case 语句：case

When when_valuethen statement_list

[When when_valuethen statement_list]…

[elsestatement_list]

End case

13)    Loop 语句：[begin_label:]loop

Set@count=@count + 1 ;

End loop add_num;

14)    Leave 语句：add_num:loop

Set@count=@count + 1 ;

         Leave add_num ;

End loop add_num;

15)    Repeat 语句：[begin_label:]repeat

Statement_list

Untilsearch_conditino

End repeat[end_label];

16)    While 语句：[begin_label:]while search_condition do

Statement_list

End while[end_label];

17)    调用存储过程和函数：call sp_name([parameter[…]]) ;

18)    查看存储过程和函数状态：show {procedure | function } status [like ‘pattern’] ;

19)    查看存储过程和函数定义：show create {procedure | function} sp_name ;

20)    查看存储过程和函数的信息：select * from information_schema.Routines where

Routine_name = ‘sp_name’;

21)    修改存储过程和函数：alter {procedure | function } sp_name [characteristic …]

Characteristic :

{contains sql |no sql | reads sql data | modifies sql data}

| sql security{definer | invoker}

|comment ‘string’

22)    删除存储过程和函数：drop {procedure | function } sp_name ;

 

 

 

3.MySQL数据库高级管理

A.用户管理

1)        登录和退出MySQL服务器：

Mysql –h hostname | hostIP –P port –uusername –p DatabaseName –e “sql语句” ;

2)        新建普通用户：create user user【identified by [password] ‘password’】

[,user[identified by [password] ‘password’]]…

Insert , grant

3)        删除普通用户：drop user user [,user]…,   delete from mysql.user where

host=’hostname’ and user=’username’

4)        Root用户修改自己的密码：mysqladmin–u username –p password

“new_password” ;

5)        修改mysql数据库下的user表：update mysql.user set password = password(“new_password”) whereuser=”root” and host=”localhost” ;

6)        使用set语句来修改root用户密码： set password = password

（”new_password”） ;

7)        Root用户修改普通用户密码：setpassword for ‘username’@’hostname’=password(“new_password”) ;

 

8)        Root用户密码丢失的解决办法：使用—skip—grant—tables选项启动MySQL服务                mysqld –skip-grant-tables      linux:mysqld_safe –skip-grant-tablesuser=mysql ;

B.  数据备份与还原

1)        备份一个数据库：mysqldump –u username –p dbname table1 table2 … > BackupName.sql

2)        备份多个数据库：mysqldump –u username –p --database dbname1 dbname2 > BackupName.sql

3)        备份所有数据库：mysqldump –u username –p –all –database > BackupName.sql

4)        直接复制整个数据库目录

5)        使用MysqL命令还原：mysql –u root –p [dbname] < bakup.sql

6)        直接复制到数据库目录：chown –R mysql.mysql dataDir

7)        相同版本的MySQL数据库之间的迁移：

Mysqldump –h name1 –u root –password=password1 –all –databases|

Mysql –h host2 –u root –password=password2

8)        不同版本的MySQL数据库之间的迁移

9)        表的导出和导入：select [列表名] form table [where 语句]

Into outfile ‘目标文件’[option] ;

10)    用mysqldump命令导出文本文件：mysqldump –u root –pPassword –T 目标目录 dbname

Dbname table [option] ;

11)    用load data [local] infile file into table table [option] ;

12)    用mysqllimport命令导入文本文件：mysqllimport –u root –pPassword [--local] dbname file

[option] ;

 

 

C.   MySQL日志

1)        启动和设置二进制日志：#my.cnf (liunx)或者my.ini （windows）

[mysqld] log-bin [=dir \ [filename]]

2)        查看二进制日志：mysqlbinlog filename.number

3)        删除二进制日志：(全部删)reset master

(根据编号)purge master logs to ‘filename.number’ ;

(根据时间) purge master logs to ‘yyyy-mm-dd hh:mm:ss’ ;

D．性能优化

1)        查看数据库的性能：show satus like ‘value’ ;

2)        分析查询语句：explain select 语句；

3)        优化数据库结构：

a)        将字段很多的表分解成多个表

b)        增加中间表

c)        增加冗余字段

4)        优化插入记录：

a)        禁用索引

b)        禁用唯一性检查

c)        优化insert语句

5)        分析表，检查表，优化表

6)        优化MySQL服务器

7)        优化MySQL的参数