MySQL操作数据库的命令
操作数据库
1.显示当前数据库
show databases;
2.创建数据库
create database database_name;
3.进入数据库
use database_name;
4.删除数据库
drop database database_name;
存储引擎
1.查看数据库支持的存储引擎
show engines;
show variables like ‘have%’;
2.查询默认存储引擎
show variables like ‘storage_engine%’;
3.修改默认存储引擎
#修改配置文件my.ini中的default-storage-engine
#重启MySQL服务
数据类型
1.整数类型
整数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
tinyint | 1 | 有符号:-27,无符号:0 | 有符号:27,无符号:28 |
smallint | 2 | 有符号:-215,无符号:0 | 有符号:215,无符号:216 |
mediumint | 3 | 有符号:-223,无符号:0 | 有符号:223,无符号:224 |
int 和 integer | 4 | 有符号:-231,无符号:0 | 有符号:231,无符号:232 |
bigint | 8 | 有符号:-263,无符号:0 | 有符号:263,无符号:264 |
2.浮点数类型、定点数类型、位类型
浮点数类型 | 字节 |
---|---|
float | 4 |
double | 8 |
定点数类型 | 字节 |
---|---|
dec(m,d)和decimal(m,d) | m+2 |
位类型 | 字节 |
---|---|
bit(M) | 1~8 |
3.时间和日期类型
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年的某个时刻 |
time | 3 | -835:59:59 | 835:59:59 |
year | 1 | 1901 | 2155 |
4.字符串类型
char系类类型 | 字节 | 描述 |
---|---|---|
char(M) | M | M为0~255之间的整数,适合存储固定长度的数据 |
varchar(M) | M | M为0~255之间的整数,适合存储长度不固定的数据 |
text系类类型 | 字节 | 描述 |
---|---|---|
tinytext | 0~28 | 值的长度为+2个字节 |
text | 0~216 | 值的长度为+2个字节 |
mediumtext | 0~224 | 值的长度为+3个字节 |
longtext | 0~232 | 值的长度为+4个字节 |
存储二进制数据(例如:图片、音乐、视频文件)
binary系类类型 | 字节 | 描述 |
---|---|---|
binary(M) | M | 允许长度0~M,适合存储固定长度的数据 |
varbinary(M) | M | 允许长度0~M,适合存储长度不固定的数据 |
blob系类类型 | 字节 |
---|---|
tinyblob | 0~28 |
blob | 0~216 |
mediumblob | 0~224 |
longblob | 0~232 |
操作表
- 查看当前数据库下的所有表
show tables;
-创建表
create table table_name(
属性名 数据类型,
.
.
);
- 查看表结构
desc table_name;
- 删除表
drop table table_name;
- 修改表名
alter table old_table_name rename new_table_name;
- 增加字段
alter table table_name add 属性名 属性类型;
- 删除字段
alter table table_name drop 属性名;
- 修改字段
alter table table_name modify 属性名 数据类型;
- 修改字段的名字
alter table table_name change 旧属性名 新属性名 数据类型;
表的约束
约束关键字 | 含义 |
---|---|
not null | 约束字段的值不能为空 |
default | 设置字段的默认值 |
unique key (UK) | 字段值是唯一 |
primary key (PK) | 字段为表的主键,作为在该表的唯一标识 |
auto_increment | 字段的值是自动递增 |
foreign key | 字段为表的外键 |
索引操作
目的:为了提高从表中检索数据的速度。
对象:数据库的表上。
实现:由表中的一个或多个字段生成的键组成,存储在数据结构(B-树(BTREE)或哈希表(HASH))中。
功能:提高数据库系统的查找速度。保证字段的唯一性,从而实现数据库表的完整性。
类型:Mysql支持六种索引:普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。
注意:InnDB和MyISAM存储引擎支持B-树类型索引,MEMORY存储引擎支持哈希类型索引。
普通索引
普通索引就是在创建索引时,不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上;
- 创建普通索引
- 创建表时添加索引
create table table_name(
属性名 数据类型,
…
index|key [索引名] (属性名1 [(长度)] [ASC | DESC])
)使用index或key参数来指定字段为索引,“索引名”参数用来指定创建索引名,“属性名1”参数用来指定索引所关联的字段名称
- 在已经存在的表上创建普通索引
create index 索引名 on 表名 (属性名 [(长度)] [asc|desc])
- alter table创建索引
alter table table_name add index|key 索引名 (属性名 [(长度)]) [asc|desc]
查看创建结果
show create table 表名;
唯一索引
唯一索引的值必须是唯一的。
- 创建表时创建唯一索引
>create table table_name(
属性名 数据类型,
…
unique index|key [索引名] (属性名1 [(长度)] asc|desc)
);
- 在已经创建的表上创建唯一索引
create unique index 索引名 on 表名 ( 属性名1 [(长度)] [asc| desc])
- 通过alter创建
alter table table_name add unique index|key 索引名(属性名 [(长度)] [asc|desc])
全文索引
全文索引主要关联在数据类型为char、varchar和 text的字段上,以便能更快速地查询数据量较大的字符串类型的字段。
- 创建
方法一:
create table table_name(
属性名 数据类型,
…
fulltext index|key [索引名] (属性名1 [(长度)] [asc| desc])
);
方法二:
create fulltext index 索引名 on 表名 (属性名 [(长度)] [asc|desc])
方法三:
alter table table_name add fulltext index|key [索引名] (属性名1 [(长度)] [asc| desc]);
多列索引
多列索引在创建时所关联的字段是多个字段,查询时只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。
- 创建
方法一:
create table table_name(
属性名 数据类型,
…
index|key [索引名] (属性名1 [(长度)] [asc| desc])
…
(属性名n [(长度)] [asc| desc])
);
方法二:
create index 索引名 on 表名 (属性名 [(长度)] [asc|desc],…,属性名n [(长度)] [asc|desc])
方法三:
alter table table_name add index|key [索引名] (属性名1 [(长度)] [asc| desc]
…
属性名n [(长度)] [asc| desc]
);
删除索引
drop index index_name on table_name;
操作视图
视图特点
- 视图的列来自不同的表,是表的抽象和在逻辑意义上建立的新关系。
- 视图是由基本表产生的虚表。
- 视图的建立和删除不影响基本表。
- 对视图内容的更新(添加、删除和修改)直接影响基本表。
- 当视图来自多个基本表时,不允许添加和删除数据。
创建视图
create view view_name as 查询语句;
查询视图
select * from view_name;
查看视图
//查询所有视图
进入数据库
show tables;
查询视图的详细信息
show table status [from db_name] [like ‘pattern’];//db_name 数据名,pattern是视图名
删除视图
drop view view_name , [view_name] …;
修改视图
create or replace view view_name as 查询语句;
aler view viewname as 查询语句;
触发器
触发触发器的表事件有:delete、insert、update
创建
create trigger trigger_name
before | after trigger_event
on table_name
for each row
begin
trigger_stmt
end
//trigger_name 触发器名称
//before | after 触发器执行时间
trigger_event 触发的事件delete、update、insert
for each row 表上任何一条记录上的操作都触发触发器
trigger_stmt 激活触发器后执行的语句
查看
show triggers;
删除
drop trigger trigger_name:
操作数据表
增加数据
insert into table_name[(field1,field2......)] values(value1,value2,.....) [,(value1,value2,....)];
//在插入的时候除了“自动增加”约束的字段不需要插入数据外,具有默认值约束的字段也可以不插入数据。
更新数据
update table_name set field1=value1,fielid2=value2,.... where condition;
//condition 为更新的约束条件
删除数据
delete from table_name where condition;
//condition为删除的约束条件
查询数据
简单查询
select [distinct] field1,field2,... from table_name [where search_condition] [group by group_by_expression]
[hiving search_condition] [order by order_expression [asc| desc]] [limit offset,row ]
-
distinct 去除指定字段的重复数据
-
search_condition:查询条件
- 多个查询条件可以用 and | or
- not 取反
not age<30; //age>=30;
- between…and… 和not between… and…
age between 12 and 20;
age not between 12 and 30;- is (not) null关键字
- in (v1,v2,…,vn)
- like
用于模糊查询,通配符有%、_、[ ]、[^]
%:后面可以跟零个或多个字符
_:匹配任意单个字符
[ ]:查询一定范围内的单个字符,包括两端数据
[^]:表示不在一定范围内的单个字符,包括两端数据
-
group_by_expression:分组条件
group by field1,field2,…; //根据自定字段分组
-
search_condition:聚合查询条件,只能与select一起使用
//例:相同年龄的人数大于2
hiving count(age)>2
- order_expression :排序条件
order by field1 [desc|asc] ,field2 [desc|asc] ,…
- limit offset,row:限制查询数量
offset :偏移位置
row:显示数量
外连接查询
select field1,field2,... from table_name1 left | right
join table_name2
on condition;
//field来源于table1和table2中,condition表示连接查询条件
合并查询
把多个查询结果合并在一起
select field1,field2,... from table_name1
union | union all
select field1,field2,... from table_name2
.....
常用函数
字符串函数
函数 | 功能 |
---|---|
concat(str1,str2,…,strn) | 连接字符串str1、str2、…、strn为一个完整字符串 |
insert(str,x,y,instr) | 将字符串str从第x位置开始,长度为y个字符长的字符替换为instr |
lower(str) | 将字符串str中所有字符变为小写 |
uppe(str) | 将字符串str中所有字符变成大写 |
left(str,x) | 返回str最左边的x个字符 |
right(str,x) | 返回str最右边的x个字符 |
lpad(str,n,pad) | 对str最左边填充n个字符的pad |
rpad(str,n,pad) | 对str最右边填充n个字符的pad |
ltrim(str) | 去掉str左边的空格 |
rtrim(str) | 去掉str右边的空格 |
replace(str,a,b) | 使用字符串b替换掉str中所有字符串a |
strcmp(str1,str2) | 比较str1和str2 |
trim(str) | 去掉str前后的空格 |
substring(str,x,y) | 返回str中从x开始,长为y的字符串 |
length(str) | 字符串字节长度;例: length(“mysql”) = 5 length(“你好”) = 4 |
char_length(str) | str中字符的个数 ;例:char_length(“mysql”)=5,char_length(“你好”) = 2 |
find_in_set(str1,str2) | 在str2中查找str1,返回位置 |
数值函数
函数 | 功能 |
---|---|
abs(x) | 返回数值x绝对值 |
ceil(x) | 返回>=x的最小整数值 |
floor(x) | 返回<=x的最大整数值 |
mod(x,y) | 返回 x/y 的余数 |
rand() | 返回0~1内的随机数 |
round(x,y) | 返回x四舍五入之后,有y位小数点的值 |
truncate() | 返回x截断为y位小数的值 |
日期和时间函数
函数 | 功能 |
---|---|
curdate() | 获取当前日期 |
curtime() | 获取当前时间 |
now(),current_timestamp(),localtime(),systemdate() | 获取当前日期和时间 |
unix_timestamp(date) | 获取日期date的unix时间戳 |
from_unixtime() | 获取unix时间戳的日期值 |
week(date) | 返回日期date为一年中的第几周 |
year(date) | 返回date的年份 |
hour(time) | |
minute(time) | |
monthname(date) | |
adddate(date,n) | 日期date加上n天后的日期 |
subdate(date,n) | 日期date减去n天后的日期 |
系统信息函数
函数 | 功能 |
---|---|
database() | 当前数据库名 |
user() | 当前用户 |
功能函数
函数 | 功能 |
---|---|
password(str) | 对str加密 |
format(x,n) | 将数字x格式化,保留n位小数 |
inet_aton(ip) | 将ip地址转换成数字 |
inet_ntoa(x) | 将数字转换成IP |
get_loct(name,time) | 创建一个持续时间为time名为name的锁 |
release_loct(name) | 为名为name的锁进行解锁 |
convert(x,type) | 将x变成type类型 |