mysql 常用的查询语句

1.连接数据库代码:
>mysql -h 主机的ip地址 -u 登入数据库用户名 -p 登入密码

2.显示数据库
>show databases;

3.连接数据库
>use dbname;

4.显示当前连接的数据库
>select database();

5.显示表结构
>desc tablename


操作表:
    1.修改表名
    >alter table table_old rename table_new;

    2.删除表
    >drop table tabelname;

    3.导入数据库
    >source 文件全路径

    4.修改字段长度
    >alter table table_name modify column_name varchar(50) ;
        4.1.修改字段名称
    >alter table keyword_info change enabled cate_num varchar(50);
        4.2.新增表字段名称
    >alter table keyword_info add enabled varchar(50)

    5.修改字段类型
    >alter table talbe_name modify column_name int(12);

    6.查询建表语句
    >show create table table_name;

    7.查询字段去重和统计不重复字段
    >select distinct(column_name) from table_name;
    >select count(DISTINCT column_name) from table_name

    8.删除表字段内容
    >delete from tablename where  column_name in ('xx','xx','xx');
    >delete from keyword_info where keyword in('设计师款','秋冬新品','潮牌','春新品','性感诱惑','甜美清新','简约优雅','奢华高贵');

    9.查询重复字段
    >select column_name,count(1) as count from user_table group by column_name having count>1; 

    10.删除完全重复字段(如果有10000条重复数据我不是要手动执行10000次,通过函数实现循环)
    >DELETE from keyword_info WHERE keyword in (select keyword from (select keyword,count(1) as count from keyword_info GROUP BY keyword HAVING count>1) as b) LIMIT 1

    11.更新表字段内容
    >update table_name SET column_name=新值 WHERE column_name=老值

    12.查询表插入新表
    >INSERT INTO talbe1 (cloumn1)  SELECT cloum1_like FROM table2 LIMIT 1;
    >INSERT INTO keywords_search_info (nick)  SELECT shop_name FROM search LIMIT 1;

    13.给已有表增加主键
        1.字段必须不为空
    >alter table mytable_1 alter column name char(1) not null
        2.删除原来主键
    >alter table mytable_1 drop constraint PK_mytable_1（主键名字）
        3.添加新主键
    >alter table mytable_1 add constraint PK_mytable_1 primary key(id,name)
        4.例子
    >alter table keyword_info add constraint keyword primary key(keyword);

    14.多表关联(on 后面紧跟主表字段)
    >select distinct i.shopid from (item i left join task_info t on i.shopid=t.shop_id) left join shop s on i.shopid=s.shop_id where t.type='tmall' limit 10,5;

高级函数:
    1.取整
    >round(x,d)

    2.求平均
    >avg(x)
进程:
    1.查看运行线程(根据用户的权限输出)
    >show processlist
    
    2.开启查询事件探查器
    >set profiling=1(开启,退出mysql自动关闭)
    >show tables(示例:一条查询语句)
    >show profiles (会显示这条query的查询时间)
    >show profile cpu,block io for query 1; (query 后面跟的是事件查探的id)

一个简单的存储过程:

//创建一个存储过程
CREATE PROCEDURE p14()

BEGIN    
  DECLARE v INT;    
  SET v = 0;    
  WHILE v < 30 DO    
    DELETE from keyword_info WHERE keyword in (select keyword from (select keyword,count(1) as count from keyword_info GROUP BY keyword HAVING count>1) as b) LIMIT 1;  
    SET v = v + 1;    
  END WHILE;    
END;


CALL p14();//执行这个存储过程

drop procedure if exists p4//删除存储过程


10.10{
忘记mysql root 密码解决方法

    1.打开mysql配置文件my.ini
    
    2.找到[mysqld] 在下面加入一行
        skip-grant-tables (意思是跳过登入授权表,任何人都可以登录,保证安全的情况下)
    
    3.在cmd输入mysql
    >use mysql
    >UPDATE user SET authentication_string = PASSWORD('root') WHERE User = 'root';

    4.注释掉第二步的代码

    5.重新启动mysql 在cmd
        net stop mysql57
        net start mysql57

    6.使用用户root,密码root登录
}