mysql使用操作常用语句

一.对表的操作

补充:链接数据库

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;