mysql常用操作
mysql安装
sudo apt-get install mysql -server mysql -client
管理服务
# 启动
service mysql start
# 停止
service mysql stop
# 重启
service mysql restart
允许远程连接
# 找到mysql配置文件并修改 sudo vi /etc/mysql/mysql.conf.d/mysql.cnf 将bind-address=127.0.0.1 注释 #登录mysql,运行命令 grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option; flush privileges; # 重启mysql service mysql restart
数据
# 字段类型 在mysql中包含的数据类型很多,这里主要列出来常用的几种。 数字 : int, decimal 字符串:varchar ,text 日期:datetime 布尔:bit # 约束 主键 primary key 非空 not null 惟 unique 默认 default 外键 foreign key
使用命令连接操作
mysql -uroot -p 回车输入密码登录 #退出登录 quit或exit #查看当前版本 select version(); # 显示当前时间 select now();
数据库操作
# 创建数据库 create database 数据库名 charset=utf-8; # 删除数据库 drop database 数据库名; # 切换数据库 use 数据库名字 # 查看当前选择的数据库 select database();
表操作
# 查看当前数据库所有的表 show tables # 创建表 auto_increment 表示自动增长 create table 表名字( id int auto_increment primary key not null, name varchar(10) not null, age int(3), idDelete bit default 1); # 修改表 # add 增加 change修改 drop删除 alter table 表名 add|change|drop 列名 类型; #删除表 drop table 表名; # 查看表结构 desc 表名 #更改表名称 rename table 原表名 to 新表名 # 查看标的创建语句 show create table '表名';
数据操作
# 查询 select * from 表名 # 增加 # 全列插入 insert into 表名 values(......); # 缺省插入 insert into 表名(列1,...) values(值1,....) # 同时插入多条数据 insert into 表名 values(....),(...)....; # 修改 update 表名 set 列1=值1,.....where 条件 # 删除 delete from 表名 where 条件 # 逻辑删除,本质就是修改操作update update 表名 isdelete=1 where 条件;
备份与恢复
# 数据备份 # 进入管理员模式 sudo -s # 进入mysql目录 cd /var/lib/mysql # 运行mysqldump 命令 mysqldup -uroot -p 数据库名 > /desktop/备份文件.sql; # 按提示输入mysql 密码 # 数据恢复 mysql -uroot -p 数据库名 < /desktop/备份文件.sql;
条件
select * from 表名 where 条件; # 比较运算符 # 等于 = # 大于 > # 大于等于 >= # 小于 < # 小于等于<= # 不等于 != 或 <> # 查询编号大于3的学生 select * from 表名 where id>3; # 逻辑运算符 and 并 or 且 not 非 # 模糊查询 like % 表示任意多个字符 _表示一个任意字符 列 查询姓张的学生 select * from 表名 where 列 like '张%'; # 匹配两个字姓 张的同学 select * from 表名 where 列 like '张_'; # 范围查询 in 表示在一个非连续的范围内 select * from 表名 where id in(1,2,4,6); berween...and.....表示一个连续范围内 # 查询2至8的学生 select * from 表名 where id between 2 and 8; # 空判断 is null 判断空 not null 非空 # 优先级 # 小括号,not,你叫运算符,逻辑运算符 # and 比 or先运算,如果同时出现并希望先算or,需要结合()使用。
聚合
# 聚合提供了5个函数 count(*) # 表示计算总行数,括号中学星与列名,结果是相同的。 # 查询学生总数 select count(*) from 表名; max(列) # 表示求此列的最大值 # 查询女生编号最大值 select max(id) from 表名 where gender=0; min(列) # 表示求此列最小值 select min(id) from 表名 where gender=0; sum(列) # 求此列的和 avg(列) # 求此列的平均值 # 查询为删除女生的编号平均值 select avg(id) from 表名 where isdelete=0 and gender=0;
分组
# 按照字段分组,表示此字段相同的数据会被放到一个组中 # 分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中 # 可以对分组后的数据进行统计,做聚合运算 select 列1,列2,聚合... from 表名 group by 列1,列2,列3...; # 查询男女生总数 select gender as 性别,count(*) from students group by gender; # 查询各城市人数 select hometown as 家乡,count(*) from students group by hometown; # 分组后的数据筛选 # having后面的条件运算符与where的相同 select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,...聚合... # 查询男生人数 select gender as 性别,count(*) from students group by gender having gender=1; # 对比where与having # where是对from后面指定的表进行数据筛选,属于对原始数据的筛选 # having是对group by的结果进行筛选
排序
# 为了方便数据的查看,可以对数据进行排序。 # asc 从小到大 # desc 从大到小 select * from 表名 order by 列1 asc|desc,列2 asc|desc,....; 列: # 查询未删除男生学生信息,按学号降序 select * from students where gender=1 and isdelete=0 order by id desc; # 查询未删除科目信息,按名称升序 select * from subject where isdelete=0 order by stitle;
分页
# 当数据量过大时,在一页中查看数据是一件非常麻烦的事情。 # 语法 # start 开始 count 结束 select * from 表名 limit start,count #示例:分页 # 已知:每页显示m条数据,当前显示第n页 # 求总页数:此段逻辑后面会在python中实现 # 查询总条数p1 # 使用p1除以m得到p2 # 如果整除则p2为总数页 #如果不整除则p2+1为总页数 #求第n页的数据 select * from students where isdelete=0 limit (n-1)*m,m
外键
create table scores( id int primary key auto_increment, stuid int, subid int, score decimal(5,2), foreign key(stuid) references students(id), foreign key(subid) references subjects(id) );
连接
""" 连接查询分类如下: 表A inner join 表B:表A与表B匹配的行会出现在结果中 表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的 数据使用null填充 表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充 在查询或条件中推荐使用“表名.列名”的语法 如果多个表中列名不重复可以省略“表名.”部分 如果表的名称太长,可以在表名后面使用' as 简写名'或' 简写名',为表起个临时的简写名称 """ # 查询每个学生每个科目的分数 select students.sname,subjects.stitle,scores.score from scores inner join students on scores.stuid=students.id inner join subjects on scores.subid=subjects.id;
# 查询学生的姓名、平均分 select students.sname,avg(scores.score) from scores inner join students on scores.stuid=students.id group by students.sname; # 查询男生的姓名,总分。 select students.sname,avg(scores.score) from scores inner join students on scores.stuid=students.id where students.gender=1 group by students.sname; # 查询科目的名称、平均分 select subjects.stitle,avg(scores.score) from scores inner join subjects on scores.subid=subjects.id group by subjects.stitle; # 查询未删除科目的名称、最高分、平均分 select subjects.stitle,avg(scores.score),max(scores.score) from scores inner join subjects on scores.subid=subjects.id where subjects.isdelete=0 group by subjects.stitle;
视图
""" 对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情 解决:定义视图 视图本质就是对查询的一个封装 定义视图 """ create view stuscore as select students.*,scores.score from scores inner join students on scores.stuid=students.id; #视图的用途就是查询 select * from stuscore;
# 事物
# 要求表的类型必须是innodb或bdb类型,才可以对此表使用事务。
开启begin;
提交commit;
回滚rollback;