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的参数