1 1.连接数据库代码: 2 >mysql -h 主机的ip地址 -u 登入数据库用户名 -p 登入密码 3  4 2.显示数据库 5 >show databases; 6  7 3.连接数据库 8 >use dbname; 9 10 4.显示当前连接的数据库11 >select database();12 13 5.显示表结构14 >desc tablename15 16 17 操作表:18     1.修改表名19     >alter table table_old rename table_new;20 21     2.删除表22     >drop table tabelname;23 24     3.导入数据库25     >source 文件全路径26 27     4.修改字段长度28     >alter table table_name modify column_name varchar(50) ;29         4.1.修改字段名称30     >alter table keyword_info change enabled cate_num varchar(50);31         4.2.新增表字段名称32     >alter table keyword_info add enabled varchar(50)33 34     5.修改字段类型35     >alter table talbe_name modify column_name int(12);36 37     6.查询建表语句38     >show create table table_name;39 40     7.查询字段去重和统计不重复字段41     >select distinct(column_name) from table_name;42     >select count(DISTINCT column_name) from table_name43 44     8.删除表字段内容45     >delete from tablename where  column_name in (‘xx‘,‘xx‘,‘xx‘);46     >delete from keyword_info where keyword in(‘设计‘,‘新品‘,‘潮牌‘,‘高贵‘);47 48     9.查询重复字段49     >select column_name,count(1) as count from user_table group by column_name having count>1; 50 51     10.删除完全重复字段(如果有10000条重复数据我不是要手动执行10000次,通过函数实现循环)52     >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 153 54     11.更新表字段内容55     >update table_name SET column_name=新值 WHERE column_name=老值56 57     12.查询表插入新表58     >INSERT INTO talbe1 (cloumn1)  SELECT cloum1_like FROM table2 LIMIT 1;59     >INSERT INTO keywords_search_info (nick)  SELECT shop_name FROM search LIMIT 1;60 61     13.给已有表增加主键62         1.字段必须不为空63     >alter table mytable_1 alter column name char(1) not null64         2.删除原来主键65     >alter table mytable_1 drop constraint PK_mytable_1（主键名字）66         3.添加新主键67     >alter table mytable_1 add constraint PK_mytable_1 primary key(id,name)68         4.例子69     >alter table keyword_info add constraint keyword primary key(keyword);70 71     14.多表关联(on 后面紧跟主表字段)72     >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=‘1‘ limit 10,5;73 74 高级函数:75     1.取整76     >round(x,d)77 78     2.求平均79     >avg(x)80 81 一个简单的存储过程:82 83 //创建一个存储过程84 CREATE PROCEDURE p14()85 86 BEGIN    87   DECLARE v INT;    88   SET v = 0;    89   WHILE v < 30 DO    90     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;  91     SET v = v + 1;    92   END WHILE;    93 END;94 95 96 CALL p14();//执行这个存储过程97 98 drop procedure if exists p4//删除存储过程