mysql根本-SQL

SQL:

1.SQL通用语法：

1）SQL语句可以单行 or 多行书写，以分号结尾
2）SQL语句可以使用空格/缩进来增强语句的可读性
3）MYSQL  数据库SQL语句不区分大小写。 关键字建议大写
4） 注释：
单行注释： -- 注释内容  or #注释内容（MYSQL特有）
 多行注释：  /* 注释内容*/

2.SQL语句分类：

1)DDL （Data Definition Language）:   数据定义语句：用来定义数据库对象 （数字库，表，字段）
2）DML（Mainpulation）： 数据操作语句，对数据进行增删改
3）DQL（Query）：数据查询语句，用来查询数据库中表的记录
4）DCL（Control）：数据控制语句，用来创建数据库的用户、控制数据库的访问权限

DDL：

1）DDL -- 数据库操作:

show databases； 查看当前数据库
create database [if not exists]数据库名 [default charset 字符集] [ collate 排序规则]；  创建数据库

drop  database [if exists]数据库名称；

use 数据库名； 使用
select database;  查看当前 数据库

2）DDL -- 操作数据库表结构:

show tables;  查看当前数据库中的使用表；
desc 表名； 查询表结构；
show create table 表名； 查询指定表的建表语句；

create table  表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
....
字段n字段n类型 [COMMENT 字段n注释]

)[COMMENT 表注释];

[] -- 可选参数

case: 

create table emp(
id int,
workno varchar(10) comment'编号',
name  varchar(10) comment'姓名' ,
gender char(1)  comment'性别' ,
age tinyint  unsigned comment'年龄' ,
idcard char(18)  comment'身份证号' ,
workaddr varchar(18) comment'工作地址',
entrydate date  comment'入职时间' 
)comment '员工信息表';
 

3）DDL - 表操作-- 数据类型；

定长字符串 char  不够空格补全   性能更高
变长字符串 varchar 不需填满 

常用：
用户名 usenamevarchar(50)
性别 gender char（1） 

DDL- 表操作-修改：

添加字段：
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

修改数据类型：
alter table 表名 modify 字段名 类型(长度) ;

修改字段名和字段类型:
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

删除字段：
alter table 表名 drop 字段名;

修改表名：
alter table 表名 rename to 新表名;

删除表：
drop table [if exists] 表名；

删除指定表,并且重新创建这张表（只删数据）：
truncate table 表名;

DML语句学习：

对数据库中的数据进行增删改.

1. 增加数据：insert

语法：
给指定字段增加数据 into 
 insert  into 表名（字段1，字段2） values（值1，值2）
给全部字段增加数据
insert  into 表名 values（值1，2）
批量输入：
insert  into 表名 values（a值1，2）,(b值1,2);

case:

先创建一张空表

create table emp(
id int,
workno varchar(10) comment'编号',
name  varchar(10) comment'姓名' ,
gender char(1)  comment'性别' ,
age tinyint  unsigned comment'年龄' ,
idcard char(18)  comment'身份证号' ,
workaddr varchar(18) comment'工作地址',
entrydate date  comment'入职时间' 
)comment '员工信息表';
 

再插入数据

insert into emp(id ,workno,name,gender,age,idcard,workaddr,entrydate)
 value (1,'1','美美','女',18,'123456789012345678','北京','2020-06-06'),
        (2,'2','大明','男',20,'122456789012345678','上海','2020-06-22'),
        (3,'3','不美','女',22,'123456789012345622','太古里','2020-06-23'),
    (4,'4','李娃','女',18,'123556789012345678','北京','2021-02-06'),
    (5,'5','李强','男',32,'123456789012345332','深圳','2020-03-06'),
    (6,'6','王刚','男',31,'133456789012345678','北京','2020-06-06'),
    (7,'7','小明','男',18,'123433789012345678','广州','2020-06-06'),
    (8,'8','李帅','男',21,'123488789012345678','南宁','2020-12-06');
 

2.修改：upadate set

语法：
update 表名 set 字段1=值1，字段2=值2，...,[while 条件]
注意： 如果不带where条件就需要去更新整张表(会触发警报，选执行即可）

3.删除：delete from

delete from 表名 [where 条件];

注意： 没有where 修饰的时候就是删除整张表的意思。
 

DQL ：

查询数据

查询关键字： select

DQL 基本查询：

1.查询多个字段：
select  字段1,2,3 from 表名;
select *from 表名;

2.设置别名：
select  字段1 [(as) 别名 ] ，字段2[(as) 别名 ] ... from 表名;

3. 去除重复记录:
select disintct  字段列表 from 字段名;

编写顺序：
select
   字段列表
from
   表名列表
where
   条件列表
grop by
   分组字段列表
having
  分组后条件列表
order by
  排序字段列表
limit 
  分页参数

case:
查询指定字段 age name workno；
select age,name,workno from emp;
查询所有字段返回
select id,workno,name,gender,age,idcard,workaddr,entrydate from  emp;
或者使用通配符代替： select * from emp;
、
查询所有员工的工作地址起别名：
select workaddr (as) '工作地址' from emp;
不重复查询所有员工的工作地址：
distinct 关键字
select distinct  emp.workaddr '工作地址' from emp;

DQL 的条件查询-- where:

1.语法： select 字段列表 from where 条件列表;
2. 条件：
比较运算符：
等于 =
不等于 !=   ,   <> 
在某个范围内(含有最小，最大值) between ... and ..
在IN之后的列表中的值，多选一。
模糊匹配  like  占位符 （_ 匹配单个字符， % 匹配任意个字符）;
是NULLL:   is NULL

逻辑运算符:
and  && 
or  ||
not !

case :
 查询有身份证的员工：
select  *from emp where idcard is not null ;
查询年龄为18 22 31 的员工：
select  *from emp where age =18 or age =31 || age =22 ;
select  *from emp where age =18 or age =31 || age =22 ;
查询姓名为两个字的员工:
select  *from emp where name like '__';
 
查询身份证号最后一位为8的员工信息:
like ‘%8’

 

DQL--聚合函数:

概念:  将一列数据作为一个整体进行纵向计算
常见聚合函数：
count： 统计数量
max\min: 最大\小值
avg: 平均值
sum: 求和

语法： select  聚合函数(字段列表) form 表名；
 
注意： 衣所有的null不参与计算.

case:
统计北京地区所有的员工的年龄之和:
select sum(age) from emp where workaddr='北京';

DQL--分组查询（group by）: 

语法:
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；

where 和having 区别:
 
1.执行时机不同：where是分组前过滤，不满足where 的条件，不参与分组。 having 是分组后对结果进行过滤。
2.判断条件不同： where 不能对聚合函数进行判断，而having可以。

case：
1.统计男性员工和女性员工的数 量:
select gender, count(*) from emp group by  gender;

2. 分别求出女性，男性员工的平均‌年龄：
select gender, avg(age) from emp group by  gender;

3.查询年龄小于等于 30 的员工，并且根据工作地址分组，给出工作地址数量大于等于2的工作地址

select workaddr, count(workaddr) from emp  where age <=30   group by workaddr having count(*)>=2

4. 查询年龄 大于等于20的员工，并且根据工作地址分组，给出工作地址大于等于2的 地址。
select workaddr, count(*) from emp where  age >=20 group by workaddr having count(*)>=2;

注意： 
1.执行顺序： where > 聚合 函数>having 
2 . 分组后查询的字段一班为聚合函数字段和分组字段，查询其他字段无任何意义

DQL--排序查询(order by):

语法： select 字段列表 from 表名 order by 字段1， 排序方式1，字段2，排序方式2；

排序方式: asc : 升序  （默认值）
               desc： 降序 

注意： 如果进行多字段排序，只有当第一字段相同的时候才会按照第二字段排序。

case： 
先给员工按照年龄升序排序，if 相同按入职时间进行降序排序：
select  * from emp order by  age asc,entrydate desc;

DQL--分页查询（limit）：

语法： select 字段列表 from 表名 limit 起始索引, 查询记录数；

注意： 1. 起始索引从0 开始，起始索引=（查询页码-1） * 每页显示的记录数
            2. 分页查询是数据库的方言，不同数据库有不同的实现，比如mysql 和umlt。
            3.若查询第一页数据，起始索引可以省略，直接写为 limit 10；

case：
查询第二页的员工数据，显示5条记录： （2-1）*5 =5
select * from emp limit 5,5;

查找18,22,31岁的女性员工：

select * from emp where age in(18,31,22) and gender='女';

查找性别为男，而且年龄在20-40岁（包含20,40）的名字个数为2的男性员工。

select * from emp where age between 20 and 40 and gender='男' and name like '__';

统计年龄小于60的男女人数：
select gender, count(*) from emp where age <60 group by gender;

查询年龄小于30岁的员工的姓名和年龄，并按年龄升序排序，if 年龄相同就按照入职时间降序排序
select name, age from emp where age <30 order by age, entrydate desc;

查询年龄在18-30 的2个女性员工，并按年龄升序排序，if年龄同按入职时间升序

select * from  emp where age between 18 and 30 and gender='女' order by  age ,emp.entrydate limit 2;

DQL编写顺序：
select
   字段列表   4
from
   表名列表   1
where    
   条件列表   2
grop by        
   分组字段列表  3
having         
  分组后条件列表  
order by 5
  排序字段列表
limit 6
  分页参数

DQL执行顺序：

from
   表名列表   1
where    
   条件列表   2

grop by        
   分组字段列表  3

having         
  分组后条件列表  

select
   字段列表   4

order by 5
  排序字段列表

limit 6
  分页参数

 

DCL:

-管理数据库的用户，控制数据库的权限：

、
用户管理:

1.查询用户:

use mysql;
select *  from user;

2.创建用户：

create user '用户名'@'主机名' identified by '密码';

case:
创建用户 king，使其可以在任意主机上访问该数据库，密码123456

create user 'king'@'%' identified by '123456';

3,.修改用户密码：

alter  user '用户名'@'主机名' identified with mysql_native_password by '新密码';

4.删除用户:

 drop user '用户名'@'主机名';

注意：
主机名 可以使用 % 进行通配
这类sql开发人员不经常使用，主要是dba-数据库管理员，运维使用。

权限控制：

权限列表包括：
all -- 所有权限： 
数据 --- select -- 查找权限； insert; update; delete; 
表alter--修改表; drop; create;

语法：

1.查询 权限：

show grants for ‘用户名’@'主机名';

2.授予权限；

grant 权限列表 on 数据库名.表名 to 用户名’@'主机名';

3.撤销权限：

revoke 权限列表 on 数据库名.表名 from 用户名’@'主机名';