工作中mysql常用的sql语句

￡神魔★判官ぃ 2023-06-12 11:25 19阅读 0赞

安装服务端: 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 … …  
库.表 : \* . \* 代表所有库的所有表