一.对表的操作
补充:链接数据库
mysql -h xx.xxx.xxx.xx -uroot -p --port=xxxx
1.修改mysql数据类型
alter table 表名 modify (字段 数据类型)；
alter table 表名 change 字段名 字段名 数据类型；
2.截断数据表
drop table 表名 eg: drop table text.db
3.添加字段
alter table `user_movement_log` Add column GatewayId int not null default 0 AFTER `Regionid`; (在哪个字段后面添加)
4.删除字段
alter table `user_movement_log` drop column Gatewayid;
5.调整字段顺序
ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID;
6.设置主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
7.增加一个新列
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';
8.删除列
alter table t2 drop column c;
9.重命名列
alter table t1 change a b integer;
10.改变列的类型
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';
11.重命名表名
alter table t1 rename t2;
12.加索引
- 1.加主键索引
alter table tablename add primary key(id);
- 2.加唯一索引
alter table tablename add unique emp_name2(cardnumber);
- 3.删除索引
alter table tablename drop index emp_name;
13.查看表是什么存储引擎
show create table 表名；
14.修改表的存储引擎的方式
alter table 表名 engine=innodb / myisam;
二.对于数据的操作
1、数据库基础知识
创建和查看数据库
创建：create database 数据库名称;
查询：show database;
show create database 数据库名称;
修改数据库
alter database 数据库名称 default character set 编码方式 collate 编码方式_bin;
删除数据库
drop database 数据库名称;
使用数据库
use 数据库名称;
2、数据表的基本操作
创建数据表
create table 表名(
字段名1 数据类型 [约束条件],
字段名2 数据类型 [约束条件],
...
)engine=InnoDB/MyISAM;
查看数据表
show tables;
show create table 表名;
describe 表名;
desc 表名;
修改数据表
修改表名：alter table 旧表名 rename [to] 新表名;
修改字段名：alter table 表名 change 旧字段名 新字段名 字段数据类型;
修改字段数据类型：alter table 表名 modify 字段名 数据类型;
添加字段：alter table 表名 add 字段名 字段类型 [约束条件] [first|after 已存在字段名];
删除字段：alter table 表名 drop 字段名;
修改字段的排列位置：alter table 表名 modify 字段名 数据类型 first/after 已存在字段名;
删除数据表
drop table 表名;
3、表的约束
主键约束（不能为空且不能重复）
单字段：字段名 数据类型 primary key
多字段：primary key(字段名1，字段名2...)
非空约束
字段名 数据类型 not null
唯一约束
字段名 数据类型 unique
默认约束
字段名 数据类型 default 默认值
4、设置表的字段值自动增加
字段名 数据类型 auto_increment
5、索引
索引概念：
普通索引
唯一索引：由unique定义
全文索引：由fulltext定义，作用于char、varchar、text类型的字段上，只有MyISAM储存引擎支持全文索引。
单列索引
多列索引
空间索引：由spatial定义，作用于geometry、point、linestring、polygon数据类型上，只能在储存引擎为MyISAM的表中。注：创建空间时，所在字段的值不可为空值。
索引作用：提高查询速度
创建索引
创建表的时候创建索引
create table 表名(
字段名1 数据类型 [约束条件],
字段名2 数据类型 [约束条件],
...
[unique/fulltext/spatial] index/key [别名] (字段名[长度] [asc/desc])
)engine=InnoDB/MyISAM;
使用create index
create [unique/fulltext/spatial] index 索引名 on 表名 (字段名[长度] [asc/desc]);
使用 alter table语句
alter table 表名 add [unique/fulltext/spatial] index 索引名 on 表名 (字段名[长度] [asc/desc]);
删除索引
drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
6.添加数据
为表中所有字段添加数据
insert into 表名 (字段名1,字段名2,字段名3....) values (值1,值2,值3...);
insert into 表名 values (值1,值2,值3...);
为表的指定字段添加数据
insert into 表名 (字段名2,字段名1,字段名3....) values (值2,值1,值3...);
insert into 表名 set 字段名1=值1,字段名2=值2,...;
同时添加多条记录
insert into 表名 (字段名1,字段名2,字段名3....) values (值1,值2,值3...),(值1,值2,值3...),(值1,值2,值3...)...;
7.更新数据
update 表名 set 字段名1=值1,字段名2=值2,... [where id...];
8.删除数据
delete from 表名 [where id...];
truncate [table] 表名;
三、单表查询
select [distinct] *|{字段名1，字段名2...}
from 表名
[where 条件表达式1(in (set),between and,is (not) null,and,or,like)]
[group by 字段名1,字段名2... [having条件表达式2]]
[order by 字段名1,字段名2... [asc|desc]]
[limit [offset,]记录数]
% _通配符
聚合函数：count(),sum(),max(),min(),avg();
函数（列表）:concat(),if(expr,v1,v2)
为表和字段取别名:[as]
四、多表操作
1、外键（表必须为Inodbx型，外键名不能加引号）
添加外键：
(1)foreign key(字段名) references 外表表名(外表字段名);
(2)alter table 表名 add constraint FK_ID foreign key(外键字段名) references 外表名(外表字段名);
[on delete cascade|set null|no action|restrict];
[on update cascade|set null|no action|restrict];
删除外键：
alter table 表名 foreign key 外键名;
2、连接查询
交叉连接：select * from 表名1 cross join 表名2;
select * from 表名1，表名2;
内连接：select 字段名1，字段名2... from 表名1 [inner] join 表名2 on 表名1.字段1=表名2.字段2;
外连接：select 字段名1，字段名2... from 表名1 left|right [outer] join 表名2 on表名1.字段1=表名2.字段2 where 条件;
3、子查询
select 字段名1，字段名2... from 表名1 where 字段名n in|exists(select...);
select 字段名1，字段名2... from 表名1 where 字段名n>|<all|any(select...);(exists关键字比in关键字运行效率高)
五、事务与储存过程
1、事务管理
开启、提交、回滚事务：start transaction;
commit;
rollback;
事务的四种隔离级别：read uncommited、read commited（大多数据库默认的事务隔离级别，如：oracle）、repeated read（mysql默认）、serializable;
修改事务隔离级别：set session transaction isolation level ...
查看事务隔离级别：select @@tx_isolation;
dirty read、non-repeatable read、phantom read
2、储存过程的创建
创建过程：create procedure procedure_name(proc_parameter);
[characteristics]routine_body(略)
设置结束符：delimiter(delimiter 和结束符之间必须要有一个空格)
变量
变量定义：declare var_name[,varname]...date_type[default value];(如果没有用default,则默认值为null)
变量赋值：set name1=value1[,name2=valu2...];
select 字段名1,字段名2...into 变量名1,变量名2...
定义条件和处理程序
定义条件：declare 条件名 condition for [condition_type];
condition_type:sqlstate_name,mysql_error_code
处理程序：declare handler_name hanlder for condtion_value[,...]sp_statement;
hanlder_type:
[continue|exit|undo](mysql不支持undo)
condition_value:
|sqlstate[value]
|condition_name
|sqlwarning
|not found
|sqlexception
|mysql_error_code
光标(游标)的使用
声明：declare cursor_name cursor for select_statement；(必须声明在声明变量、条件之前，处理程序之后)
打开：open cursor_name;
使用：fetch cursor_name into var_name1[var_name2...];
关闭：close cursor_name;(如果没有明确地关闭光标，他会在其声明的复合语句的末尾被关闭)
流程的使用
if语句：(判断变量是否为空 var_name is null)(mysql中海油一个if()函数，与这个不同)
if expr_condition then statement_list;
elseif expr_conditon then statement_list;
else statement_list;
end if;
case语句：(不可以 else null)
case case_expr
when case_value then statement_list;
when case_value then statement_list;
[else statement_list];
end case;
case
when case_expr then statement_list;
when case_expr then statement_list;
[else statement_list]
end case;
loop循环：
[loop label:]loop
statement_list;
end loop[loop label];
leave语句：(通常与begin...end,循环语句一起使用)
leave label
iterate语句：(只可以与loop、while、repeat语句一起用)
iterate label;
repeat语句：
[repeat_label:]repeat
statement_list;
until expr_condition;
end repeat[ repeat_label];
while语句：
[while_label:]while expr_condition
statement_list;
end while[ while_label];
3、储存过程的使用
调用:call sp_name(parameter[,...]);
查看
show procedure|function status[like'patten'];
show create procedure|function sp_name;
select * from information_schema.routines(information数据库下的routines表中储存了所有定义的储存过程)
where routine_name='' and routine_type='procedure'\G;
修改:alter [procedure|function] sp_name [characteristics...]
删除:drop procudure|function [if exits]sp_name;
六、视图
1、视图创建：
create [or replace] [algorithm={undefined|merge|temptable}]
view view_name [(column list)]
as select_statement
[with [cascaded|local] check option]
2、查看视图：
describe 视图名(desc 视图名);
show table status like '视图名';
show create view view_name;
3、修改视图：
create [or replace] [algorithm={undefined|merge|temptable}]
view view_name [(column list)]
as select_statement
[with [cascaded|local] check option]
alter [algorithm={undefined|merge|temptable}]
view view_name [(column list)]
as select_statement
[with [cascaded|local] check option]
4、更新视图：
update、insert、delete
一下情况不可以更新视图：
（1）视图中包含基本表中被定义为非空的列
（2）在定义的视图的select语句后的字段列表中使用了数学表达式。
（3）在定义视图的select语句后的字段列表中使用了聚合函数。
（4）在定义视图的select语句总使用了distinct、union、top、group by或having字句。
5、删除视图：
drop view [if exists]
view_name[,view_name...]
[restrict|cascade]
(删除视图必须必须拥有drop权限)
七、数据库的高级操作
1、数据备份与还原
备份：mysqldump -u root -p root db_name[,dtname,dtname...]>filename.sql;
mydqldump -u root -proot --database dbname[,dbname,...]>filename.sql;
mysqldump -u root -proot --all-databases>filename;
还原：mysql -u root -p root db_name<filename.sql;
source filename.sql;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
