mysql数据库总结一【安装，配置，表操作，字段操作，增删改， 备份-恢复，select查询】

服务器端安装

  • 安装服务器端：在终端中输入如下命令，回车后，然后按照提示输入
sudo apt-get install mysql-server
  • 启动服务
sudo service mysql start
  • 查看进程中是否存在mysql服务
ps ajx|grep mysql
  • 停止服务
sudo service mysql stop
  • 重启服务
sudo service mysql restart

图形界面下载地址https://www.navicat.com.cn/　　

配置

路径

  • 主要配置项如下
bind-address表示服务器绑定的ip，默认为127.0.0.1

port表示端口，默认为3306

datadir表示数据库目录，默认为/var/lib/mysql

general_log_file表示普通日志，默认为/var/log/mysql/mysql.log

log_error表示错误日志，默认为/var/log/mysql/error.log

# 检测中心  查看访问数据库的记录
general_log_file = /var/log/mysql/mysql.log
general_log = 1

命令行操作数库

  • 连接数据库
mysql -uroot -p
  • 退出登录
quit 和 exit 或 ctrl+d
  • 登录成功后，输入如下命令查看效果
查看版本：select version();
显示当前时间：select now();

修改输入提示符

prompt python>
# \D 完整日期
# \d 数据库名称
# \u 使用用户
# \h主机名称

数据库

  • 查看所有数据库
show databases;
  • 使用数据库
use 数据库名;
  • 查看当前使用的数据库
select database();
  • 创建数据库
create database 数据库名 charset=utf8;
例：
create database python charset=utf8;
  • 删除数据库
drop database 数据库名;
例：
drop database python;

　　

数据表

  • 查看当前数据库中所有表
show tables;
  • 查看表结构
desc 表名;
  • 创建表
  • auto_increment表示自动增长
-- 例：创建班级表
create table classes(
	id int unsigned auto_increment primary key not null,
	name varchar(20)
)
-- 例：创建学生表
create table students(
    id int unsigned auto_increment primary key not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','第三','保密'),
    cls_id int unsigned default 0
);
  • 修改表-添加字段
  • alter table 表名 add 列名 类型;
例：
alter table students add birthday datetime;
  • 修改表-修改字段：重命名版
  • alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;
  • 修改表-修改字段：不重命名版
  • alter table 表名 modify 列名 类型及约束;
例：
alter table students modify birth date not null;
  • 修改表-删除字段
  • alter table 表名 drop 列名;
例：
alter table students drop birthday;
  • 删除表
  • drop table 表名;
例：
drop table students;
  • 查看表的创建语句
  • show create table 表名;
例：
show create table classes;

增删改查(curd)

数据准备

insert into students values
(0,'小铭',18,180.00,2,1),
(0,'小月月',18,180.00,2,2),
(0,'彭于晏',29,185.00,1,1),
(0,'刘德华',59,175.00,1,2),
(0,'芙蓉',38,160.00,2,1),
(0,'凤姐',28,150.00,4,2),
(0,'王祖贤',18,172.00,2,1),
(0,'周杰伦',36,NULL,1,1),
(0,'程坤',27,181.00,1,2),
(0,'刘亦菲',25,166.00,2,2),
(0,'金星',33,162.00,3,3),
(0,'静香',12,180.00,2,4),
(0,'周杰',34,176.00,2,5);

查询基本使用

  • 查询所有列
  • select * from students;
  • 查询指定列
  • select name from students;
  • 可以使用as为列或表指定别名
  • select name as '姓名' from students;

增加

说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准

  • 全列插入：值的顺序与表中字段的顺序对应
  • insert into students values(0,'小红',19,192.22,2,2);　　
  • 部分列插入：值的顺序与给出的列顺序对应
  • insert into students(name,age) values('小小',22);　　
  • 上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信
  • 全列多行插入：值的顺序与给出的列顺序对应
  • insert into students(name) values("lll"),('hhh');

修改

update students set name='哈哈' where id=20;   将id为20的name值改为'哈哈'

删除

  • 彻底删除
  • delete from students where id = 20;
  • 逻辑删除，本质就是修改操作
  • update students set isdelete=1 where name='大大';

查询指定字段

在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

select 列1,列2,... from 表名;

-- 表名.字段名
select students.id,students.name,sudents.gender from students;

-- 可以通过 as 给表起别名 
select s.id,s.name,s.gender from students as s;

-- 如果是单表查询 可以省略表明
select id,name,gender from student;

- 使用 as 起别
select id as 标号,name as 名字,gender as 性别 from students;

消除重复行

  • 在select后面列前使用distinct可以消除重复的行
select distinct 列1,... from 表名;
例：
select distinct gender from students;

备份

  • 运行mysqldump命令
mysqldump –uroot –p 数据库名 > python.sql;

# 按提示输入mysql的密码

恢复

  • 连接mysql，创建数据库
  • 退出连接，执行如下命令
mysql -uroot –p 数据库名 < python.sql

# 根据提示输入mysql密码

查询的完整格式

SELECT select_expr [,select|_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

条件查询

条件

  • 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
  • 语法如下：
select * from 表名 where 条件;
例：
select * from students where id =1;
  • where后面支持多种运算符，进行条件的处理
    • 比较运算符
    • 逻辑运算符
    • 模糊查询
    • 范围查询
    • 空判断

比较运算符

  • 等于: =
  • 大于: >
  • 大于等于: >=
  • 小于: <
  • 小于等于: <=
  • 不等于: != 或 <>
  • 例1：查询编号大于3的学生
select * from students where id > 3;
  • 例2：查询编号不大于4的学生
 select * from students where id <= 4;
  • 例3：查询姓名不是“黄蓉”的学生
select * from students where name != '黄蓉';
  • 例4：查询没被删除的学生
select * from students where isdelete=0;

逻辑运算符

  • and
  • or
  • not
  • 例5：查询编号大于3的女同学
select * from students where id > 3 and gender=2;
  • 例6：查询编号小于4或没被删除的学生
select * from students where id < 3 and isdelete = 0;

模糊查询

  • like
  • %表示任意多个任意字符
  • _表示一个任意字符
  • 例7：查询姓黄的学生
select * from students where name like '金%';
  • 例8：查询姓黄并且名字是两个字的学生
select * from students where name like '金_';
  • 例9：查询姓黄或叫靖的学生
select * from students where name like '%月' or name like '金%';

范围查询

  • in表示在一个非连续的范围内
  • 例10：查询编号是1或3或8的学生
select * from students where id in(1,3,8);
  • between ... and ...表示在一个连续的范围内
  • 例11：查询编号为3至8的学生
 select * from students where id between 3 and 8;
  • 例12：查询学生是3至8的男生
select * from students where id between 3 and 8 and gender=1;

空判断

  • 注意：null与''是不同的
  • 判空is null
  • 例13：查询没有填写身高的学生
select * from students where height is null;
  • 判非空is not null
  • 例14：查询填写了身高的学生
select * from students where height is not null;
  • 例15：查询填写了身高的男生
select * from students where height is not null and gender = 1;

优先级

  • 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
  • and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序

  • 为了方便查看数据，可以对数据进行排序
  • 语法：
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
  • 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
  • 默认按照列值从小到大排列
  • asc从小到大排列，即升序
  • desc从大到小排序，即降序
  • 例1：查询未删除男生信息，按学号降序
  • select * from students where isdelete = 0 and gender = 1 order by id desc;
  • 例2：查询未删除学生信息，按名称升序
select * from students where isdelete = 0 order by name asc;

聚合函数

  • 为了快速得到统计数据，经常会用到如下5个聚合函数
  • count(*)表示计算总行数，括号中写星与列名，结果是相同的
  • 聚合函数不能在 where 中使用
  • 例1：查询学生总数
select count(id) as "总数" from students;
  • max(列)表示求此列的最大值
  • 例2：查询女生的编号最大值
select max(id) from students where gender = 2;
  • min(列)表示求此列的最小值
  • 例3：查询未删除的学生最小编号
select min(id) from students where isdelete = 0;
  • sum(列)表示求此列的和
  • 例4：查询未删除男生的编号之和
select sum(id) from students where gender = 1 and isdelete =0;
  • avg(列)表示求此列的平均值
  • 例5：查询未删除女生的编号平均值
select avg(id) from students where isdelete = 0 and gender = 2;
  • 平均年龄保留两位数
select round(avg(age),2) from students;

分组

  • 按照字段分组，表示此字段相同的数据会被放到一个组中
  • 分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
  • 可以对分组后的数据进行统计，做聚合运算
  • 语法：
select 列1,列2,聚合... from 表名 group by 列1,列2...
  • 例1：查询男女生总数
select gender,count(*) from students where gender in(1,2) group by gender;
  • 例2：查询各种年龄的人数
select age,count(id) from students group by age;
  • 例3：多个里面的任意一个
select gender,any_value(name) from students group by gender;

分组后的数据筛选

  • 语法：
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
  • having后面的条件运算符与where的相同
  • 例3：查询男生总人数

方法一

select count(id) from students where gender = 1;

方法二

select gender,count(id) from students group by gender having gender = 1;

  • 例4：查询男生女生的总人数

select gender,count(id) from students group by gender having gender = 1 or gender = 2;

对比where与having

  • where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
  • having是对group by的结果进行筛选

获取部分行

  • 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
  • 语法
select * from 表名
limit start,count
  • 从start开始，获取count条数据
  • start索引从0开始
  • 例1：查询前3行男生信息
select * from students where gender = 1 limit 0,3;  # 0为分组后的索引

示例：分页

  • 已知：每页显示m条数据，当前显示第n页
  • 求总页数：此段逻辑后面会在python中实现
    • 查询总条数p1
    • 使用p1除以m得到p2
    • 如果整除则p2为总数页
    • 如果不整除则p2+1为总页数
  • 求第n页的数据
select * from students
where isdelete=0
limit (n-1)*m,m

　　

连接查询

  • 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

  • mysql支持三种类型的连接查询，分别为：

    • 内连接查询：查询的结果为两个表匹配到的数据
    • 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
    • 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

  • 语法

select * from 表1
inner或left或right join 表2 on 表1.列=表2.列
  • 例1：使用内连接查询班级表与学生表
  • 此处使用了as为表起别名，目的是编写简单
select * from students as s inner join classes as c on s.cls_id = c.id;
  • 例2：使用左连接查询班级表与学生表
select * from classes as c left join students as s on c.id=s.cls_id;
  • 例3：使用右连接查询班级表与学生表
select * from classes as c right join students as s on c.id=s.cls_id;
  • 例4：查询学生姓名及班级名称
select c.name as '班级',s.name as '姓名' from classes as c inner join students as s on c.id=s.cls_id;;

自关联

  • 设计省信息的表结构provinces
    • id
    • ptitle
  • 设计市信息的表结构citys
    • id
    • ctitle
    • proid
创建表
create table areas(
	aid int unsigned primary key auto_increment not null,
	atitle varchar(20),
	pid int
);

从sql文件中导入数据（注意在文件所在目录进入mysql）

source areas.sql;
  • 查询一共有多少个省
select * from areas where pid is null;
  • 例1：查询省的名称为“山西省”的所有城市
select c.aid,c.atitle from areas as c inner join areas as s on c.pid=s.aid where s.atitle='山西省';
  • 例2：查询市的名称为“山西省”的所有区县
select t.atitle as '市', x.atitle as '县' from (select c.aid,c.atitle from areas as s inner join areas as c on s.aid = c.pid where s.atitle='山西省') as t inner join areas as x on t.aid=x.pid;

子查询(sub query)

子查询

  • 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询

  • 主要查询的对象,第一条 select 语句

主查询和子查询的关系

  • 子查询是嵌入到主查询中
  • 子查询是辅助主查询的,要么充当条件,要么充当数据源
  • 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

  • 标量子查询: 子查询返回的结果是一个数据(一行一列)
  • 列子查询: 返回的结果是一列(一列多行)
  • 行子查询: 返回的结果是一行(一行多列)
  • 表级子查询: 返回的结果是多行多列

标量子查询

  1. 查询班级学生平均年龄
  2. 查询大于平均年龄的学生

查询班级学生的平均身高

select * from students where age > (select avg(age) from students);

列级子查询

  • 查询有学生的班级
    1. 找出学生表中所有的班级 id
    2. 找出班级表中对应的名字
select * from classes where id in (select cls_id from students);

行级子查询

  • 需求: 查找班级年龄最大,身高最高的学生
  • 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
select * from students where (height,age) = (select max(height),max(age) from students);

表级子查询

  • 查询学生与班级对应的信息
select c_name as '班级', s_name as '姓名' from (select s.name as s_name, c.name as c_name from students as s inner join classes as c on s.cls_id = c.id) as

说明：发现很多表级子查询的语句，都是可以使用连接查询实现的，此时推荐使用连接查询，因为连接查询的语句更简洁，逻辑更清晰

子查询中特定关键字使用

  • in 范围

    • 格式: 主查询 where 条件 in (列子查询)

  • any | some 任意一个

    • 格式: 主查询 where 列 = any (列子查询)
    • select * from students where cls_id = any((select id from classes));
select * from students where cls_id in (select id from classes); # 效果一样
select * from students where cls_id = some((select id from classes));  # 查询班级对应的学生
    • 在条件查询的结果中匹配任意一个即可,等价于 in

  • all

    • 格式: 主查询 where 列 = all(列子查询) : 等于里面所有
    • 格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有
    • select * from students where cls_id <> all(select id from classes);  # 查询哪些学生不在班级里面

完整的sql查询语句

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

　　

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

执行顺序为：

  • from 表名
  • where ....
  • group by ...
  • select distinct *
  • having ...
  • order by ...
  • limit start,count
posted @ 2017-08-27 22:13  凯哥吧  阅读(187)  评论(0)    收藏  举报