MySQL数据库基本操作整理

mysql -uroot -proot

数据库:

    查询所有    show databases;

    创建        create database 库名;

    查询详情    show create database 库名;

    指定字符集  create database 库名 character set utf8;

    删除        drop database 库名;

    使用        use 库名;

    导入数据库       source 路径;

数据表:

    查询所有    show tables;

    创建        create table t1(name varchar(10),age int);

    查询详情    show create table t1;

    指定引擎和字符集    create table t1(name varchar(10),age int)     engine=myisam/innodb      charset=utf8;

            ----------------innodb:支持数据库的复杂操作，包括外键、事务等

            ----------------myisam:只支持数据基础的增删改查操作

    查看表字段   desc 表名;

    删除表       drop table 表名;

    修改表名     rename table 原名 to 新名;

    修改表的引擎和字符集  alter table 表名 engine=myisam/innodb charset=utf8;

    添加表字段 

            --最后面: alter table 表名 add 字段名 字段类型;

            --最前面: alter table 表名 add 字段名 字段类型 first;

            --XX后面: alter table 表名 add 字段名 字段类型 after xxx;

    删除表字段   alter table 表名 drop 字段名;

    修改表字段的名字和类型     alter table 表名 change 原字段名 新字段名 新字段类型;

    修改表字段的类型和位置     alter table 表名 modify 字段名 字段类型 位置;

    删除并新建   truncate table 表名;

数据相关:

    保存数据    insert into 表名 values(字段值,字段值,...);

    指定字段插入  insert into 表名 (字段名) values (字段值);

    查询数据    select * from 表名 where 条件;

    修改数据    update 表名  set 字段名=字段值,字段名=字段值,... where 条件;

    删除数据    delete from 表名 where 条件;

    查询到的字段更名    select 原字段名 新字段名 from 表名;

    查询去重        select distinct 字段名 from 表名;

    in 查询某个字段的值为多个的时候      select * from where 字段名 in(值1,值2,...);

    查询某个字段的值不为**的时候      select * from where 字段名 not in(值1,值2,...);

    between查询在**之间(之内)     select * from where 字段名 between 值1 and 值2;

    查询不在**之间的(之外)     select * from where 字段名 not between 值1 and 值2;

    模糊查询:   like

            _:代表单个未知字符

            %:代表0个或者多个未知字符

    升序   select * from 表名 order by 字段 asc;

    降序   select * from 表名 order by 字段 desc;   

    分组   select 字段 from 表名 group by 字段；

    分页查询     select * from 表名 limit (页数-1)*每页数量,每页数量             -----limit 7,7 

    取余      mod(7,2)      -----7%2

    获取当前日期+时间      select now();

    获取当前日期      select curdate();

    获取当前时间      select curtime();

    从年月日时分秒中提取日期    select date(now());

    从年月日时分秒中提取时间    select time(now());

    从年与日时分秒中提取时间分量:

        年      select extract(year from now());      ------select extract(year from 字段) from 表名;

        月      select extract(month from now());

        日      select extract(day from now());

        时      select extract(hour from now());

        分      select extract(minute from now());

        秒      select extract(second from now());

    日期格式化:

        %Y 四位年 YYYY

        %y 两位年 yy

        %m 两位月

        %c 一位月

        %d 日

        %H 24小时

        %h 12小时

        %i 分

        %s 秒

                -------select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');

    非标准格式转换为标准格式

        str_to_date('非标准格式的时间',格式);      --------select str_to_date('2018年11月16日 15时49分08秒','%Y年%m月%d日 %H时%i分%s秒');

    ifnull(x,y)     ----update emp set comm = ifnull(comm,0);

主键：

    创建表的时候定义主键和自增 注释     create table 表名(字段名 字段类型 primary key auto_increment comment '内容',....);

    查看自动提交状态    show variables like '%autocommit%';

    设置自动提交的状态   set autocommit=0/1;

    开启事务    begin;

    提交事务    commit;

    保存回滚点   savepoint 名;

    回滚到回滚点  rollback to 名;

聚合函数:

    平均值     avg(字段名);       ------select avg(sal) from emp;

    最大值     max(字段名);       ------select max(sal) from emp;

    最小值     min(字段名);       ------select min(sal) from emp;

    求和       sum(字段名);       ------select sum(sal) from emp;

    统计数量    count(字段名);    ------select count(ename) from emp;

字符串:

    拼接  concat('xx','xx');

    获取长度    select char_length(字段) from emp;

    获取字符串在另外一个字符串中出现的位置     instr(str,substr);

    插入字符串   insert(str,start,length,newstr);

    转大写     upper(str);

    转小写     lower(str);

    去两端空白   trim(str);

    截取字符串 

        从左截取    left(str,num); 

        从右截取    right(str,num); 

        自定义截取   substring(str,start,len);

    重复    repeat(str,次数);

    替换    replace(str,要替换的,替换后的);

    反转    reverse(str); 

数学函数：

    向下取整    select floor(num);

    四舍五入    select round(num,m);    ------select round(23.4782,2)   23.48

    非四舍五入   select truncate(num,m)   -----select truncate(23.879,2)  23.87

    随机数     rand()  0-1

            获取3、4、5 随机数     select floor(rand()*3)+3;

关联查询：

    内连接     select from 字段 from 表1 join 表2 on 等值条件

    外连接     select from 字段 from 表1 left join 表2 on 等值条件

视图：

    创建视图    create view 视图名 as 子查询 with check option;

    创建或替换视图     create or replace view 视图名 as 子查询;

    删除视图        drop view 视图名;

外键：

    格式： constraint 约束名称 foreign key(外键字段名) references 依赖的表名(依赖的字段名)

索引：

    格式： create index 索引名 on 表名（字段名（字符长度））

    删除索引    drop index 索引名  on 表名；

注意：

    1、where后面不能写聚合函数，使用having

    2、如果创建视图的子查询中使用了别名，则对视图操作时只能使用别名

    3、not null  不能为空

    4、unique   不能重复

    5、primary key   主键，不能为空，不能重复

    6、auto_increment    自增

    7、显示一行  select depto,group_concat(ename) from emp group by deptno;

作者：米臭了
链接：https://www.jianshu.com/p/64c96471da30