MySQL学习笔记——基础篇（SQL语句、分组查询、分页查询、排序、基本函数、权限管理）

MySQL

1. 基础篇

1.1 数据库基础介绍

• 数据库：储存数据的仓库，Database（DB）
• 数据库管理系统：操纵和管理数据库的大型软件，Database Management System（DBMS）
  • Oracle，甲骨文研发，大型收费
  • MySQL，最终被甲骨文所收购，开源免费，中小型
  • SQLserver，微软研发，中型收费
  • PostgreSQL，免费，中小型
  • SQLlite，微型，安卓选择
• SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准，Structured Query Language（SQL）

1.2 关系型数据库

• 关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
• 特点：
  1. 使用表存储数据，格式统一，便于维护
  2. 使用SQL语言操作，标准统一，使用方便

1.3 数据模型

1.4 SQL

1.4.1 通用语法

1. SQL语句可以单行或多行书写，以分号结尾
2. MySql数据库的SQL语句不区分大小写，关键字建议大写
3. 注释：单行用**–或#（MySQL特有），多行用 //

1.4.2 SQL分类

• DDL：Data Definition Language，数据库定义语言，用来定义数据库对象（数据库，表，字段）
• DML：Data Manipulation Language，数据操作语言，进行增删改
• DQL：Data Query Language，数据查询语言，查询
• DCL：Data Control Language，数据控制语言，用来创建数据库用户、控制数据库的访问权限

1.4.3 DDL

1. DDL-数据库操作：

   • 查询

     #查询所有数据库
     show databases;
     #查询当前数据库
     select database();
     

   • 创建

     create database  dbname;
     #不报数据库已存在的错误
     create database if not exists dbname;
     #为数据库设置字符集
     create database dbname default charset utf8mb4;
     

   • 删除

     drop database if exists dbname;
     

   • 使用

     use dbname;
     

2. DDL-表操作

   • 查询

     #查询所有表结构
     show tables;
     #查询表结构
     DESC tbname;
     #查询指定表的建表语句
     show create table tbname;
     

   • 创建

     create table tbname(
     	name varchar(10) comment '名字',
     	id int comment '年龄'
     )comment '测试表';
     

   • 数据类型

     数值类型

     int，

     tinyint，tinyint unsigned，

     smallint，smallint unsigned，

     float，

     double，double（4,1）==>100.0

     字符类型

     char 定长字符串，没使用的用空格补位，性能高，eg：性别

     varchar 变长字符串，性能较差，eg：用户名

     text 长文本数据

     日期类型

     date YYYY-MM-DD 日期值

     time HH：MM：SS 时间或持续时间

     datetime YYYY-MM-DD MM：SS 混合日期时间

   • 修改

     #添加字段
     alter table tbname add ziduan type; 
     #修改字段数据类型
     alter table tbname modify  zidaun newtype;
     #修改字段名和数据类型
     alter table tbname change oldziduan newzidaun type;
     #删除字段
     alter table tbname drop ziduan;
     #修改表名
     alter table tbname rename to newtbname;
     

   • 删除

     drop table if exists tbname;
     #只删除数据，保留表名
     truncate table tbname;
     

1.4.4 DML

• 添加数据 insert

  #给指定字段添加数据
  insert into tbname(num1,num2) values(1,2);
  #给全部字段添加数据
  insert into tbname values(1,2);
  #批量添加数据
  insert into tbnaem values(1,2),(3,3);
  

  • 插入数据时，指定字段顺序要与值的顺序一一对应
  • 字符串和日期型数据要包含在引号中
  • 插入的数据大小，应该在字段规定的范围内

• 修改数据 update

  update tbname set name=xyh,id=00 where age=18;
  

• 删除数据 delete

  delete from tbname where id =00;
  

1.4.5 DQL

编写顺序：select from where group by having order by limit

执行顺序：from,where,group by,select,order by,limit

• 基本查询

  select name,id,gender from table where id=1;
  #设置别名
  select name as studentname,id as idfication from table;
  #去重
  select distinct name from table;
  

• 条件查询

  select name from table where ;
  

  >,<,>=,<=,=,!=,and,or,in,between,like,
  查询为空：is null
  不为空：is not null
  区间：age>=20 and age<=30
  	 age between 20 and 30(大小不能反)
  多选一：age in (20,12,21)
  模糊匹配：_匹配单个字符，%匹配任意字符
  	    name like '__',id like '%x'
  
  

• 聚合函数

  #count,统计非null字段的数量
  select count(*) form tbname;
  select count(age) form tbname;
  #max
  select max(age) from tbname;
  #min
  select min(age) from tbname;
  #avg
  select avg(age) from tbname;
  #sum
  select sum(age) from tbname;
  

• 分组查询

  where：分组前过滤数据,在聚合函数之前执行,不可以使用聚合函数

  having：分组之后过滤，可以使用聚合函数

  #通过性别分组
  select gneder,count(*) from tbname group by gender;
  #查询年龄小于35岁的员工，然后根据工作地址分组，获取员工数量大于等于3的工作地址
  select workplace,count(workplace) addcount from tbname where age<35 group by workplace having addcount>=3;
  

• 排序查询

  ASC：升序,默认。

  DESC：降序

  #升序排序
  select * from tbname order by age asc
  #先年龄升序排序，年龄相同，根据入职时间降序排序
  select * from tbname order by age,time desc;
  

• 分页查询

  *起始索引从0开始，起始索引=（查询页码-1）每页显示记录数

  limit放在sql语句的最后

  #查询第一页，每页展示10条记录
  select * from tbname limit 0,10;
  #查询第二页，每页展示10条记录
  select * from tbname limit 10,10;
  

• 案例

  #查询年龄为19，32，20的女性
  select * from tbname where age in (19,32,20) and gender='女'
  #查询性别为男，并且年龄在20-40（含）姓名为3个字的员工
  select * from tbname where age between 20 and 40 and name like '___';
  #统计员工表中年龄小于50岁的，男性员工和女性员工的人数
  select gender,count(*) from where age<=60 group by gender;
  #查询男性，年龄20-40（含）之间的前五的员工的信息，对查询结果按年龄升序排序，年龄相同按入职时间降序排序
  select * form tbname where gender='男' and age between 20 and 40 order by age asc,time desc limit 5;
  

1.4.6 DCL

• 查询用户

  use mysql;
  select * from user; 
  

• 创建用户

  #本机访问
  create user 'xyh'@'localhost' 
  identified by '1234';
  #任意主机访问
  create user 'xyh'@'%' 
  identified by '12345';
  #修改密码
  alter user 'xyh'@'%' 
  identified with mysql_native_password by '12343';
  

• 删除用户

  drop user 'xyh'@'localhost';
  

• 权限控制

  #查询权限
  show grants for 'xyh'@'hostname';
  #赋予权限
  grant all on dbname.tbname to 'xyh'@'hostname';
  grant all on *.* to 'root'@'hostname';
  #撤销权限
  revoke all on dbname.tbname from 'xyh'@'hostname';
  

1.5函数

• 字符串函数

  #字符串拼接：concat(s1,s2)
  select concat('hello','mysql');
  #转小写：lower(s1),转大写：upper(s2)
  #左填充：lpad('01',5,'-')  ---01
  #去头尾空格：trim(' hello wolrd ')
  #截取：substring('hello',2,3)    ell
  
  #由于需求变更，需要将学号不足8位的补足0
  update tbname set id=lpad(id,8,'0'); 
  

• 数值函数

  #向上取整：ceil(1.5);
  #向下取整：floor
  #取余：mod
  #rand()
  #四舍五入：round(2.435,2)    2.44
  
  #生成一个6位随机验证码
  select lpad(floor(rand()*1000000),0);
  

• 日期函数

  #返回当前日期：curdate()
  #curtime()
  #日期+时间：now();
  #year,month,day
  #时间间隔：date_add(now(),interval 70 day);
  #时间差：datediff ('2001-1-12','2001-1-2');
  
  #查询员工入职天数并排序
  select name,datediff(curdate(),entrydate) as 'entrydays' order by desc;
  

• 流程函数

  #if(value,t,f)
  select if (true,'ok','false');
  ok
  #ifnull(value1,value2)
  select ifnull(null,'default');
  default
  #case when then else end
  #查询员工的姓名和工作地址，上海--》一线城市，其他--》二线城市
  select name,
  	   case workadd when '上海' then '一线城市' else '二线城市' from tbname;