工作中mysql常用的sql语句

安装服务端: sudo apt-get install mysql-server
安装客户端: sudo apt-get install mysql-client
配置文件：/etc/mysql
命令集： /usr/bin
数据库存储目录 ：/var/lib/mysql
查看MySQL状态: sudo /etc/init.d/mysql status
启动服务：sudo /etc/init.d/mysql start | stop | restart
mysql -h主机地址 -u用户名 -p密码
mysql -hlocalhost -uroot -p123456
本地连接可省略 -h 选项: mysql -uroot -p123456
常看已有库： show databases;
创建库： create database 库名 default charset utf8;
查看创建库的语句(字符集) : show create databse 库名;
查看当前所在库： select database();
切换库： use 库名；
删除库： drop database 库名；
创建表： create table class_table (id int primary key auto_increment,name varchar(32),age int,gender enum(‘w’,‘m’),score float default 0.0,price decimal(6,2),comment text);
查看数据表： show tables;
查看已有表字符集： show create table 表名；
查看表结构： desc 表名；
删除表： drop table 表名；
插入数据： insert into 表名 values(2,‘Alex’,28,90.5),(3,‘Ryan’,26,100.0)；
插入数据2： insert into 表名(字段名) values(对应字段值)；
查询： select * from 表名 where 条件；
条件注意： 不能直接写12<age<29,应该用 where between 12 and 10;
where age in (8,9);
where sex is null;
where sex=‘m’ and age>10;
where not sex=‘m’;
更新表记录： update 表名 set age=11 where name=‘Ryan’;
删除表记录： delete from 表名 where name=‘Ryan’;
添加字段： alter table 表名 add age int;
删除字段： alter table 表名 drop age;
修改字段类型： alter table 表名 modify age int not null;
修改字段名： alter table 表名 change 旧字段 新字段；
表重命名： alter table 表名 rename 新表名;
模糊查询： select * from 表名 where name like ‘_A%’；（%代表匹配任意多个字符,　_ 代表匹配任意一个字符）
排序： select * from 表名 where order by age desc limit 3; (ASC升序，DESC降序，limit查看多少)
联合查询： select * from class_table where age>13 UNION select * from class_table where gender is not null;
多表查询： select class_1.name,class_2.name from class_1,class_2 where class_1.age=class_2.age;
日期查询： select * from class_table where time<(now()-interval 1year); (查询一年以前的用户信息)
MySQL普通查询（执行顺序）
3、select …聚合函数 from 表名
1、where …
2、group by …
4、having …
5、order by …
6、limit …;
分组查询： select country,avg(attack) from sanguo group by country; (计算每个国家的平均攻击力)
select country,count(id) from sanguo where gender=‘m’ group by country order by count(id) DESC limit 2;(所有国家的男英雄中，英雄数量最多的前2名的国家名称及英雄数量)
分组聚合后筛选： select country,avg(attack) from sanguo group by country having avg(attack)>105 order by avg(attack) desc limit 2;(找出平均攻击力大于105的国家的前2名，显示国家名称和平均攻击力)
不显示字段重复值： select distinct country from sanguo;
嵌套查询： select name,attack from sanguo where attack<(select avg(attack) from sanguo);(把攻击值小于平均攻击值的英雄名字和攻击值显示出来)
select name,attack from sanguo where (country,attack) in (select country,max(attack) from sanguo group by country);(找出每个国家攻击力最高的英雄的名字和攻击值)
连接查询(内连接)： select province.pname,city.cname,country.coname from province inner join city on province.pid=city.cp_id inner join country on city.cid=county.copid;(显示省市县详细信息)
左连接（以左表为主显示，右表没有的显示null）
select province.pname,city.cname from provice left join city on province.pid=city.cp_id;
右连接（以右表为主显示，左表没有的显示null）
创建索引： create table 表名(name varchar(32),class varchar(32),index(name),unique(class));
已有表创建索引： create unique/index 索引名 on 表名(字段名)；
查看索引： desc 表名； MUL(普通索引)/UNI(唯一索引)
删除索引： drop index 索引名 on 表名；
创建主键： id int primary key auto_increment;
已有表添加主键： alter table 表名 add primary key(id);
已有表添加自增长： alter table 表名 modify id int auto_increment;
删除自增长： alter table 表名 modify id int;
删除主键索引： alter table 表名 drop primary key;(需要先去除自增长)
添加外键： foreign key(参考字段名) references 主表(被参考字段名) on delete cascade on update cascade); （删除更新主表，从表也会跟着操作）–restrict不做任何操作，set null主表删除，从表值为null
查看外键名： show create table 表名;
删除外键： alter table 表名 drop foreign key 外键名;
表的复制： create table 新表名 select * from 库名.被复制的表名；（表的复制不会把原表的键属性复制过来）
复制表结构： create table 表名 select 查询命令 where false;
创建表指定引擎： create table 表名 (…)engine=MyISAM,charset=utf8；
事务操作： (对查询和新建表，新建库是无效的)
开启事务： begin; 或者 start transaction;
终止事务： commit; 或者 rollback;
数据备份：
备份命令： mysqldump -u用户名 -p 源库名 > ~/xxx.sql
恢复命令： mysql -uroot -p 目标库名 < xxx.sql
python操作MySQL数据库：
db = pymysql.connect(参数列表)
host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8
数据库连接对象(db)的方法
db.close() 关闭连接
db.commit() 提交到数据库执行
db.rollback() 回滚
cur = db.cursor() 返回游标对象,用于执行具体SQL命令
游标对象(cur)的方法
cur.execute(sql命令,[列表]) 执行SQL命令　传入的是字符串的话，需要加单引号
cur.close() 关闭游标对象
cur.fetchone() 获取查询结果集的第一条数据 (1,100001,“河北省”)
cur.fetchmany(n) 获取n条 ((记录1),(记录2))
cur.fetchall() 获取所有记录

MySQL用户账户管理

开启MySQL远程连接
更改配置文件,重启服务!
sudo -i
/etc/mysql/mysql.conf.d/
vim mysqld.cnf
将bind-address = 127.0.0.1　注释掉，前面加个#号就可以了
重启服务sudo /etc/init.d/mysql restart

[mysql]
character_set_server = utf8 更改字符编码
添加授权用户
1、用root用户登录mysql
mysql -uroot -p123456
2、授权
grant 权限列表 on 库.表 to “用户名”@"%" identified by “密码” with grant option;(最后三个单词表示可以让授权用户继续往下授权)
eg:grant all privileges on db3.* to “yanxu”@"%"identified by “123456”;
３、刷新权限
flush privileges;

权限列表
all privileges (所有权限)、select 、insert … …
库.表 : * . * 代表所有库的所有表