1. 常用命令
0)连接MySQL服务器
mysql -h 127.0.0.1 -P 3306 -u root -proot
mysql -h localhost -P 3306 -u root -proot
1)查看当前MySQL服务实例支持的字符集
show character set;
2)查看当前MySQL服务实例使用的字符集
show variables like 'character%'
3)查看当前MySQL服务实例支持的字符序
show variables like 'collation%'
4)修改MySQL默认的字符集
修改my.ini配置文件 (/etc/my.cnf)
1.1 数据库管理
1)创建数据库
create database choose;
2)查看数据库
show databases;
3)显示数据库结构
show create database choose;
4)选择当前操作的数据库
use choose;
5)删除数据库
drop database student;
1.2 表管理
1)查看MySQL服务实例支持的存储引擎show engines;
2)InnoDB存储引擎的特点:
- 支持外键(Foreign Key)
- 支持事务(Transaction)
如果某张表主要提供OLTP支持,需要执行大量的增、删、改操作(insert、delete、update语句),出于事务安全方面的考虑,InnoDB存储引擎是更好的选择。
3)MyISAM存储引擎的特点
- 具有检查和修复表的大多数工具
- 表可以被压缩
- 表最早支持全文索引
- 表不支持事务(Transaction)
- 表不支持外键(Foreign Key)
駋需要执行大量的select语句,出于性能方面的考虑,MyISAM存储引擎是更好的选择
4)“临时地”设置存储引擎
set default_storage_engine=MyISAM;
5)创建数据库表
use choose;
set default_storage_engine=InnoDB;
create table my_table(
today datatime,
name char(20)
);
6)显示当前数据库所有的表
show tables;
7)显示表结构
desc my_table;
8)显示表的详细信息
show create table my_table;
9)表记录的管理
use chooses;
insert into my_table values(now(),'a');
insert into my_table values(now(),'a');
insert into my_table values(now(),NULL);
insert into my_table values(now(),'');
select * from my_table;
10)删除表
drop table my_table;
11)表删除字段
alter table 表名 drop 字段名
12)表添加新字段
alter table 表名 add 新字段名 新数据类型 [新约束条件] [first|after旧字段名]
13)表修改字段名和数据类型
alter table 表名 change 旧字段名 新字段名 新数据类型
14)表仅修改字段数据类型
alter table 表名 modify 字段名 新数据类型
15)修改表名
rename table 旧表名 to 新表名
或 alter table 旧表名 rename 新表名
1.3 查看系统变量
1) 查看全局系统变量(393项)show global variables;
2) 查看当前会话相关的所有系统变量以及所有的全局系统变量(405项)
show session variables;
1.4 MySQL数据库备份和恢复
1)准备工作方法1: 停止MySQL服务
方法2:使用MySQL命令: flush tables with read lock;
2)备份文件的选取
- 备份整个数据库目录(MyISAM)
- 除MyISAM要求的之外,还需要备份ibdata1表空间文件以及重做日志文件ib_logfile0与ib_logfile1
- 将MySQL配置文件(例如my.ini配置文件)一并备份
3)数据库恢复
- 停止MySQL服务
- 把所有备份文件复制到新MySQL服务器对应的路径,即可恢复数据库中的数据
1.5 数据类型
1) 整数类型- tinyint (1字节)
- smallint (2字节)
- mediumint (3字节)
- int (4字节)
- bigint (8字节)
2) 小数类型
- decimal(length, precision)
- float (4字节)
- double (8字节)
3) 字符串类型
- char (字长字符串类型)
- varchar (变长字符串类型)
- text (文体类型) : tinytext, text, mediumtext, longtext
4) 日期类型
- date (表示日期,默认格式为:YYYY-MM-DD)
- time (表示时间,格式为:hh:mm:ss)
- year (表示年)
- datetime与timestamp是日期和时间的混合类型,其格式为:YYYY-MM-DD hh:mm:ss
5)复合类型
- enum (类似于单选按钮的功能,一个enum类型的数据最多可以包含65535个元素)
- set (类似于复选框功能,一个set类型的数据最多可以包含64个元素)
6) 二进制类型
- binary
- varbinary
- bit
- tinyblob
- blob
- mediumblob
- longblob
1.6 索引
1)索引的种类:- 主索引、聚簇索引
- 唯一性索引
- 普通索引
- 复合索引
- 全文索引(fulltext)
2)方法一:创建表的同时创建索引
create table 表名(
字段名1 数据类型[约束条件],
...
[其它约束条件],
...
[unique | fulltext] index [索引名] (字段名[(长度)] [asc| desc])
) engine=存储引擎类型 default charset=字符集类型
实例:
create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal(6,2),
publish_time date not null,
unique index isbn_unique(isbn),
index name_index (name(20)),
index complex_index (price,publish_time)
)engine=InnoDB default charset=utf8;
2)方法二:在已有的表上创建索引
create [unique | fulltext] index 索引名 on 表名 (字段名[(长度)] [asc | desc])
或
alter table 表名 add [unique | fulltext] index 索引名 (字段名[(长度)] [asc|desc])
3)删除索引
drop index 索引名 on 表名
2. 操作表记录
2.1 插入新记录
1) 插入一条新记录insert into 表名[(字段列表)] values(值列表)
2) 批量插入多条新记录
insert into 表名[(字段列表)] values
(值列表1),
(值列表1),
...
(值列表n);
3) 使用insert ...select插入结果
insert into 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
4) 使用replace插入新记录
- replace into 表名[(字段列表)] values (值列表)
- replace [into] 目标表名 [(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
- replace [into] 表名 set 字段1=值1, 字段2=值2
注:考虑到数据库移植,不建议使用replace
2.2 修改表记录
update 表名 set 字段名1=值1,字段名2=值2,...,字段名n=值n[where条件表达式]
2.3 删除表记录
delete from 表名 [where条件表达式]truncate [table] 表名 //完全清空一个表,不支持事务的回滚,且不会触发触发器程序的运行
3. 查询表记录
1) select语句select 字段列表 from 数据源
[where条件表达式]
[group by 分组字段 [having条件表达式]]
[order by 排序字段 [asc|desc]]
注:字段列表
*: 字段列表为数据源的全部字段
表名.*: 多表查询时,指定某个表的全部字段
字段列表:指定所需要显示的列
select 字段列表 from 数据源 limit [start,] length;
2) 使用where子句过滤结果集
- 常用的比较运算符有=(等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)、<>(不等于)、!=(不等于)、!<(不小于)、!>(不大于)
- is NULL:用于判断表达式的值是否为空值NULL(is [not] NULL)
- 逻辑运算符:and、or、!布尔表达式、[not] between 起始值 and 终止值
- [not] in (数学集合)
- 模糊查询:[not] like 模式 // %: 包含0个或多个字符组成的任意字符串 _: 任意一个字符
3)使用order by子句对结果集排序
order by 字段名1 [asc|desc] [...,字段名n [asc|desc]]
4)使用聚合函数汇总结果集
- 累加求合sum()函数
- 平均值avg()函数
- 统计记录的行数count()函数
- 最大值max()函数
- 最小值min()函数
- 以上函数都忽略NULL值
5)使用group by子句对记录分组统计
select 字段列表
from 数据源
where 条件表达式
group by 分组字段 having 条件表达式