Mysql（2）查询实例

一：MySQL简介与安装

    MySQL是一种中型、易用的关系型数据库管理系统(DBMS,Database Management System)。

    安装步骤：

    第一步：  sudo apt-get install mysql-server

    第二步：  sudo apt install mysql-client

    第三步：  sudo apt install libmysqlclient-dev

    进入MySQL环境：

    mysql -u root -p

二：MySQL常用操作命令

    1. show databases;      # 显示所有数据库

    2. use 数据库名称;      # 进入（使用）某个数据库

    3. show tables;        # 显示当前数据库中所有的表

    4. desc 表名称;        # 查看表的结构

三：基本SQL语句

    SQL：Structured Query Language，结构化查询语言，专门用来操作关系型数据库的

        语言。

    1. 创建数据库

      create database 数据库名 [default character set 'utf8'];

    2. 创建表

      create table 表名称(

          字段名  字段数据类型  约束,

          字段名  字段数据类型  约束,

          ......

          字段名  字段数据类型  约束

      );

    3. 插入记录

      方式一：

      insert into 表名称(字段名1,字段名2,...)values(值1,值2,...);

      方式二：

      insert into 表名称 values(值1,值2,...);

      方式三：

      insert into 表名称(字段名1,字段名2,...)values(值1,值2,...),(值1,值2,...),

        (值1,值2,...).....(值1,值2,...);

    4. 修改记录

      update 表名称 set 字段名=修改值 [,字段名=修改值,...] [where 条件];

    5. 删除记录

      delete from 表名称 [where 条件];

    6. 查询记录

      select 字段列表 from 表名称 [where 条件];

    7. 增加一个字段

      alter table 表名 add 新字段的名字  字段属性

      alter table students add sex char(10)

    8. 删除一个字段

      alter table 表名 change 老字段名 新字段名 字段属性;

      例如：

      alter table students change id id int(4) auto_increment;

    9. 修改某个字段 --- 修改属性

      alter table students change id id int(4);

    10. 修改某个字段 --- 删除主键

        alter table students drop primary key;

    11. 修改某个字段 --- 添加主键

        alter table students add primary key(id);

        alter table students change id id int(4) primary key;

四：限制查询、分页查询、排序查询、分组查询

    1.限制查询

      第一种情况：查询限定条数

      select 字段列表 from 表名称 [where 条件] limit 最多记录数;

      第二种情况：从指定偏移量查询限定条数

      select 字段列表 from 表名称 [where 条件] limit 偏移量,最多记录数;

    2.分页查询

      已知当前页为currentPage,每页最多显示的记录数为pageSize,则currentPage页

      显示的表中的记录为：

      select 字段列表 from 表名称 [where 条件]

        limit (currentPage-1)*pageSize,pageSize;

    3.排序查询

      select 字段列表 from 表名称 [where 条件] order by 字段名[desc] [,字段名...];

    4.MySQL中的聚合函数

      A:  max(字段名)  获取某字段中的最大值

      B:  min(字段名)  获取某字段中的最小值

      C：  sum(字段名)  计算某字段值的和

      D：  avg(字段名)  计算某字段的平均值

      E：  count(字段名) 计算某字段值中不为null的记录数

          count(*)  计算表中的总记录数

    5.分组查询

      select 字段列表 from 表名称 [where 条件]

          group by 分组字段列表 [having 对分组后的筛选条件];

      注意：select后的字段列表应该包含在group by后的分组字段列表中!

一：模糊查询

    模糊查询中的两个通配符：

    1.  %  代表任意多个任意字符

    2.  _  代表任意一个字符

    模糊查询的SQL语法：

    select 字段列表 from 表名称 where 字段名 like 匹配条件;

二：外键(foreign key)

    外键用来约束子表的记录与父表对应。

    constraint 约束名 foreign key(用来关联父表的字段)

              references 父表名(父表中用来关联子表的字段，一般为主键);

三：多表查询

    1. 等值连接

      select 字段列表 from 表A,表B,... where 连接条件 [其他过滤条件];

    2. 内连接

      select 字段列表 from 表A inner join 表B on 连接条件 [其他过滤条件];

    3. 外连接

      左外连接：

      select 字段列表 from 表A left join 表B on 连接条件 [其他过滤条件];

      注意：左外连接的查询结果不但包括了符合连接条件的查询记录，而且还包括了

            左表中不符合连接条件的记录。

      右外连接：

      select 字段列表 from 表A right join 表B on 连接条件 [其他过滤条件];

      注意：右外连接的查询结果不但包括了符合连接条件的查询记录，而且还包括了

            右表中不符合连接条件的记录。

例子1：（多表查询）

1.使用“等值连接”查询学生姓名、学生成绩、学校名称。

  select student.name,student.score,school.schoolname 

      from school,student where student.school_id=school.schoolid;

2.使用“等值连接”查询出“张红”的学生姓名、学生成绩、学校名称。

  select student.name,student.score,school.schoolname 

      from school,student where student.school_id=school.schoolid

        and student.name='张红';

3.使用“内连接”查询学生姓名、学生成绩、学校名称。

  select student.name,student.score,school.schoolname 

      from school inner join student on student.school_id=school.schoolid;

4.使用“内连接”查询出“张红”的学生姓名、学生成绩、学校名称。

  select student.name,student.score,school.schoolname 

      from school inner join student on student.school_id=school.schoolid

      and student.name='张红';

5.先插入一条学校记录，再使用“左外连接”查询学生姓名、学生成绩、学校名称,

  要求显示所有学校的记录。

  insert into school(schoolname,note)values('东北师范',null);

  select student.name,student.score,school.schoolname 

      from school left join student on student.school_id=school.schoolid;

例子2：（限制，分页。。。。）

1.创建一个名为mydb的数据库

  create database mydb default character set 'utf8';

2.进入mydb

  use mydb;

13:55 2018/5/29

3.创建student表

  create table student(

    stuid  int  auto_increment primary key,

    name  varchar(20)  not null,

    age    int,

    score  float    not null,

    note    text

  );

4.向student表中插入一条记录

  insert into student(name,age,score,note)values('风清扬',20,93.5,'风清扬独孤九剑');

  插入成功之后，查询表的所有记录：

  select * from student;

  insert into student values(23,'令狐冲',18,65.5,'家住华山');

  insert into student(name,age,score)values('郭靖',25,62),('黄蓉',24,73.5),

  ('马云',40,79.5);

5.把学生表中所有的记录的成绩score改为60分

  update student set score=60;

6.将姓名为“令狐冲”的记录的成绩改为75分

  update student set score=75 where name='令狐冲';

7.删除学号为27的学生记录

  delete from student where stuid=27;

8.查询所有学生的姓名和成绩，并以别名的形式显示字段

  select name as 姓名,score as 成绩 from student;

9.查询出所有成绩在85到95之间的学生记录

  select * from student where score >=85 and score <= 95;

  或者

  select * from student where score between 85 and 95;

10.查询所有备注信息不为null的学生记录

  select * from student where note is not null;

11.查询学生表中的前三条记录

  select * from student limit 3;

12.查询成绩大于等于70分的学生中，前两条记录

  select * from student where score >= 70 limit 2;

13.查询学生表中第三条与第四条记录

  select * from student limit 2,2;

14.假如每页最多显示2条记录，查询第三页应该显示的记录

  select * from student limit 4,2;

15.按照成绩从高到低，将学生排序

  select * from student order by score desc;

16.按照成绩从高到低，将学生排序,如果成绩相等则再按照年龄降序排序

  select * from student order by score desc,age desc;

===========================以下为分组查询相应的SQL===========================

17.创建商品表product，并添加记录

  create table product(

      proid    int    auto_increment  primary key,

      proname  varchar(20)  not null,

      price    float  not null,

      type      varchar(20)  not null,

      note      text

  );

  insert into product(proname,price,type,note)values('锅巴',5,'零食',null),

    ('洗衣粉',8,'日用品',null),('可口可乐',12,'饮料','新品上市的Cola'),

    ('辣条',2,'零食',null),('脸盆',10,'日用品',null),('薯片',13,'零食',null),

    ('脉动',6,'饮料',null);

18.按照商品类型type对product表中的记录进行分组，求出每一组的平均价格

  select type as 类型,avg(price) as 平均价格 from product group by type;

19.按照商品类型type对product表中的记录进行分组，求出零食组的平均价格

  select type as 类型,avg(price) as 平均价格 from product

    group by type having type='零食';

例子三：（模糊查询）

创建worker表

create table worker(

  work_id  int  auto_increment primary key,

  name    varchar(20),

  job    varchar(20),

  salary  float

);

insert into worker(name,job,salary)values('张二强','修车',5000),

  ('王小刚','美容',3500),('李刚','经理',6000),('王娟','老板娘',7000);

1. 查询所有姓王的工人

  select * from worker where name like '王%';

2. 查询名字中带有“刚”的工人

  select * from worker where name like '%刚%';

3. 查询名字中第二个字是"刚"的工人

  select * from worker where name like '_刚%';

例子四：（外键）

学校表

create table school(

  schoolid    int  auto_increment primary key,

  schoolname  varchar(30)  not null,

  note      text

);

insert into school(schoolname,note)values('清华大学','清华大学很好')

  ,('北京大学','北大不错'),('交大','交大也很好');

学生表

create table student(

  stuid    int  auto_increment primary key,

  name    varchar(20) not null,

  score  double not null,

  sex    varchar(10),

  school_id int,

  constraint fk_student foreign key(school_id) references school(schoolid)

    on delete cascade on update cascade

);

insert into student(name,score,sex,school_id)values('张三',92,'男',1),

    ('张三丰',96,'男',1),('李娟',88,'女',2),('张红',86,'女',2),

    ('李芬',85,'女',3);

四：子查询（嵌套查询）

    在外部查询SQL语句中，可以包含其他内部查询的语句，这个内部查询（也叫嵌套查询）

    就是子查询；子查询需要使用括号括起来；子查询的结果一般作为外部查询语句的条件。

    子查询经常使用的关键字：

    1. in

      判断某字段的值是否在子查询结果集中

    2. all

      判断某字段的值是否满足操作符对子查询结果集所有值的比较

    3. any

      判断某字段的值是否满足操作符对子查询结果集任意一个值的比较

五：“一对多”与“多对多”关系表设计

    1. “一对多”关系表设计：

        在“多”的一方中，设置外键，关联“一”的一方。(eg:学校表("一")与学生表("多"))

    2. “多对多”关系表设计：

        “多对多”关系表的设计需要引入一个中间表，中间表负责维护“多对多”关系表。

        中间表至少需要包含两个字段，这两个字段分别用来关联这两个“多对多”的关系表。

        通常会将这两个字段设置成外键，关联两个“多”表的主键。

        可以通过将这两个字段设置成联合主键的方式，用来避免组合值重复。 

六.配置远程连接MySQL的权限

  1.sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

    对该文件进行编辑：注释掉 bind-address = 127.0.0.1

  2.进入MySQL环境,授予root用户所有权限

    grant all on *.* to 'root'@'%' identified by '你自己MySQL的root用户密码'

              with grant option;

  3.刷新权限

    flush privileges;

  4.退出MySQL环境，并重启mysql服务

    systemctl restart mysql