MySQL基础篇(DDL，DML，DQL，DCL详细讲解)

​​navicat是一套快速、可靠并价格相宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。​​它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。

​​http://www.navicat.com.cn/​​

pojie方式：https://www.jb51.net/database/710931.html

​​mysql可能是世界上最流行的开源数据库引擎，但是使用基于文本的工具和配置文件可能很难进行管理。​​SQLyog提供了完整的图形界面，即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定义工具和查询、友好的视觉界面、类似 Excel 的查询结果编辑界面等优点。

​​https://www.webyog.com/product/sqlyog​​

3. 使用客户端工具

1:创建一个数据库;选择编码为utf-8
2: 创建数据表
3: 往数据表里面存储数据

二、SQL语句基础

  结构化查询语言(Structured Query Language)简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存放数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

between and

in not in or

2.2.6 字段类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

# 高级函数
# case函数

select * from t_student ;
select 
   id,stuname,age
    ,case
       when age < 18 then '[0-18]'
         when  age BETWEEN 18 and 20 then '[18-20]'
         when  age BETWEEN 20 and 30 then '[20-30]'
         else '[30以上]'
    end
from

4.5.4 子查询

# 子查询 嵌套查询
    # 查询出班级为 java1班 的所有的学员信息
    select  t1.*
    from t_student t1
    where class_id in (
        select t_class.class_id from t_class where t_class.class_name = 'java1班'  or t_class.class_name = 'java2班'
    ) 
    # 如果在子查询中只有一条记录那么我们可以用=来替代in
        select  t1.*
    from t_student t1
    where class_id  = (
        select t_class.class_id from t_class where t_class.class_name = 'java1班' or t_class.class_name = 'java2班'
    )


    select  t1.*
    from t_student t1
    where EXISTS  # exists 存在于的含义  外表中的记录存在于子表中 就满足条件 否则就过滤掉
    (
        select t_class.class_id from t_class where t_class.class_name = 'java1班'  and t1.class_id = t_class.class_id
    )

4.6 综合案例

drop table student;
create table student (
id int(3) PRIMARY KEY ,
name varchar(20) not null,
sex varchar(4),
birth int(4),
department varchar(20),
address varchar(50));


# 创建score表。SQL代码如下：
drop table score;
create table score(
   id int(3)   PRIMARY KEY ,
   stu_id int(3)  not null,
   c_name varchar(20) ,
   grade int(3)
)

-- 向student表插入记录的INSERT语句如下：
insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区');
insert into student values(903,'张三','女',1990,'中文系','湖南省永州市');
insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市');
insert into student values(905,'王五','女',1991,'英语系','福建省厦门市');
insert into student values(906,'王六','男',1988,'计算机系','湖南省衡阳市');
-- 向score表插入记录的INSERT语句如下：
insert into score values(1,901,'计算机',98);
insert into score values(2,901,'英语',80);
insert into score values(3,902,'计算机',65);
insert into score values(4,902,'中文',88);
insert into score values(5,903,'中文',95);
insert into score values(6,904,'计算机',70);
insert into score values(7,904,'英语',92);
insert into score values(8,905,'英语',94);
insert into score values(9,906,'计算机',90);
insert into score values(10,906,'英语',85);

SELECT * from student;
select * from score;

1、查询student表的第2条到4条记录 
       
    select * from student LIMIT 1,3;
    
2、从student表查询所有学生的学号（id）、
           姓名（name）和院系（department）的信息
    
      select id '学号' ,name as '姓名' ,department 院系
      from student t
       
3、从student表中查询计算机系和英语系的学生的信息
   
    select  *
    from student t
    where t.department = '计算机系' or t.department='英语系'
    
    select  *
    from student t
    where t.department in ('计算机系','英语系')
        
4、从student表中查询年龄25~30岁的学生信息
   select *,EXTRACT(year from now()) ,EXTRACT(year from now())-birth age 
    from student where (EXTRACT(year from now()) - birth) BETWEEN 30 and 40;    
         
5、从student表中查询每个院系有多少人 
select t.department,count(1)
from student t
group by t.department
     
6、从score表中查询每个科目的最高分
select s.c_name,max(grade)
from score s 
group by  s.c_name
     
 
7、查询李四的考试科目（c_name）和考试成绩（grade）
     注意： '=' 只有在确定结果是一个的情况下使用，不确定的使用用 'in'
    
    select c_name,grade
    from score
    where stu_id in (
      select id from student where name = '李四'
    )
    
    # 通过exists
    select c_name ,grade
    from score s
    where EXISTS (
      select id from student where name = '李四' and student.id = s.stu_id
    )
    
    # 通过左连接来实现
    select  t1.*
    from score t1 RIGHT join ( select * from student where name = '李四') t2
    on t1.stu_id = t2.id ;
    
    select  t1.*,t2.*
    from score t1 RIGHT join student t2
    on t1.stu_id = t2.id 
    where t2.name = '李四'
      
8、用内连接的方式查询所有学生的信息和考试信息

    select  t1.*,t2.*
      from student t1 INNER JOIN score t2
      on t1.id = t2.stu_id
      
9、计算每个学生的总成绩
   select stu_id,sum(grade)
   from score 
   group by stu_id 
   
   
   select stu_id,(select name from student where id = stu_id) 姓名,sum(grade)
   from score 
   group by stu_id 
   
   select t1.name,sum(t2.grade)
   from student t1 INNER JOIN score t2
   on t1.id = t2.stu_id
   group by t1.name
       
         
10、计算每个考试科目的平均成绩

select c_name,TRUNCATE(avg(grade),2) 平均分
from score 
group by c_name
       

11、查询计算机成绩低于95的学生信息

 select *
from student
where id in (
   select stu_id from score where c_name = '计算机' and grade < 95
) 

 select *
from student
where EXISTS (
   select stu_id from score where c_name = '计算机' and grade < 95 and student.id = stu_id
)   

       
12、查询同时参加计算机和英语考试的学生的信息
     select * from score; 
       
      # 首先查询出 参加计算机的学员
      select * from score where c_name = '计算机'
      
      select * from score where c_name = '英语'
      
      select * from student where id in (
      select stu_id from score where  stu_id in (
         select stu_id from score where c_name = '计算机' )
         and c_name = '英语' )

13、将计算机考试成绩按从高到低进行排序
select *
from score 
where c_name = '计算机'
order by grade desc
    
        
14、从student表和score表中查询出学生的学号，
     然后合并查询结果 UNION与union all
       
       select id 
       from student 
       union
       select stu_id
       from score
       
       select id 
       from student 
       union all
       select stu_id
       from score
   
            
15、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
   select name 姓名, department 院系, c_name 考试科目 ,grade 成绩
    from student t1 left join score t2 on t1.id = t2.stu_id
    where t1.name like '张%' or t1.name like '王%'
    
    
    select name 姓名, department 院系, c_name 考试科目 ,grade 成绩
    from (select * from student where  name like '张%' or name like '王%') t1 left join score t2 on t1.id = t2.stu_id

16、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
    select name 姓名, (EXTRACT(year from now()) - birth) 年龄, department 院系, c_name 考试科目 ,grade 成绩
      from student t1 left join score t2
      on t1.id = t2.stu_id
      where address like '湖南%'

5.DCL

  数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke 等。

  DCL 语句主要是DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。下面
通过一个例子来简单说明一下.

mysql> grant select,insert on plf.* to 'plf'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


mysql> revoke insert on plf.* from 'plf'@'%';
Query OK, 0 rows affected (0.00 sec)