02. 基础篇 - SQL

SQL通用语法

SQL分类

DDL

DDL-数据库操作

查询

查询所有数据库

show databases;

查询当前数据库

select database();

创建

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

删除

drop database [if exists] 数据库名;

使用

use 数据库名;

DDL-表操作

查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建

create table 表名(
    字段1 字段1类型 [comment 字段1注释],
    ......
    字段n 字段n类型 [comment 字段n注释]
) [comment 表注释];

例子：创建如下表结构

create table text(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
)comment '用户表';

数据类型

例子：
age tinyint unsigned
score double(4,1) -- 4为精度，1为标度 100.0

 

 性能：char > varchar

案例

mysql> create table emp (
    -> id int comment '编号',
    -> workNumber varchar(10) comment '工号',
    -> workName varchar(10) comment '姓名',
    -> gender char(1) comment '性别',
    -> age tinyint unsigned comment '年龄',
    -> idCard char(18) comment '身份证号',
    -> entrydate date comment '入职时间'
    -> ) comment '员工表';

修改

添加字段

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

alter table emp add nickname varchar(20) comment '昵称';

修改数据类型

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

修改字段名和字段类型

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

alter table emp change nickname username varchar(30) comment '用户名';

删除字段

alter table 表名 drop 字段名;

alter table emp drop username;

修改表名

alter table 表名 rename to 新表名;

alter table emp rename to employee;

删除

删除表

drop table [if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;

注意：在删除表时，表中的全部数据也会被删除。

DML

介绍

添加数据

给指定字段添加数据

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

给全部字段添加数据

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

批量添加数据

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

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

修改数据

update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];

删除数据

delete from 表名 [where 条件];

DQL

介绍

语法

select 
        字段列表
from 
        表名列表
where 
        条件列表
group by 
        分组字段列表
having 
        分组后条件列表
order by 
        排序字段列表
LIMIT 
        分页参数

基本查询

查询多个字段

select 字段1,字段2,... from 表名;
select * from 表名;

设置别名

select 字段1 [as 别名1],[as 别名2]... from 表名;

去除重复记录

select distinct 字段列表 from 表名;

条件查询

语法

select 字段列表 from 表名 where 条件列表;

条件

-- 条件查询
-- 查询年龄等于 88 的员工信息
select * from employee where age = 88;

-- 查询年龄小于 20 的员工信息
select *from employee where age < 20;

-- 查询年龄小于等于 20 的员工信息
select *from employee where age <= 20;

-- 查询没有身份证号的的员工信息
select *from employee where idCard is null;

-- 查询有身份证号的的员工信息
select *from employee where idCard is not null;

-- 查询年龄不等于 88 的员工信息
select *from employee where age != 88;
select *from employee where age <> 88;

-- 查询年龄在[15,88]的员工信息
select *from employee where age >= 15 and age <= 20;
select *from employee where age >= 15 && age <= 20;
select *from employee where age between 15 and 20;

-- 查询性别为女年龄且小于25的员工信息
select *from employee where gender = '女' and age < 25;
select *from employee where gender = '女' && age < 25;

-- 查询年龄为 18或 28或 40 的员工信息
select *from employee where age = 18 or age = 28 or age = 40;
select *from employee where age = 18 || age = 28 || age = 40;
select *from employee where age in (18,28,40);

-- 查询姓名为2个字的员工信息
select *from employee where workName like '_ _';

-- 查询身份证号最后一位为X的员工信息
select *from employee where idCard like '%X';

聚合函数

语法

select 聚合函数(字段列表) from 表名;

-- 聚合函数
-- 统计该企业的员工数量
select count(*) as '员工数量' from employee;

-- 统计该企业的员工的平均年龄
select avg(age) as '员工平均年龄' from employee;

-- 统计该企业的员工的最大年龄
select max(age) as '员工最大年龄' from employee;

-- 统计该企业的员工的最小年龄
select min(age) as '员工最小年龄' from employee;

-- 统计该企业的员工的年龄之和
select sum(age) as '员工年龄之和' from employee;

注意：所有的null值不参与所有聚合函数运算。

分组查询

语法

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

-- 分组查询
-- 根据性别分组，统计男性员工和女性员工的数量
select gender as '性别',
       count(*) as'男性员工和女性员工的数量' from employee group by gender ;

-- 根据性别分组，统计男性员工和女性员工的平均年龄
select gender as '性别',
       avg(age) as '男性员工和女性员工的平均年龄' from employee group by gender;

-- 查询年龄小于45的员工 根据年龄分组，获取员工数量大于等于2的相同年龄
select age as '员工年龄',
       count(*) as '相同年龄员工总数' from employee where age < 45 group by age having count(*) >= 2;

排序查询

语法
 

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

-- 排序查询
-- 根据年龄对公司员工进行升序排序
select * from employee order by age asc;
select * from employee order by age;

-- 根据入职时间对公司员工进行降序排序
select * from employee order by entrydate desc;

-- 根据年龄对公司员工进行升序排序，年龄相同，再按入职时间进行降序排序
select * from employee order by age asc , entrydate desc;

分页查询

语法

select 字段列表 from 表名 limit 起始索引, 查询记录数;

-- 分页查询
-- 查询第1页员工数据，每页展示3条记录
select * from employee LIMIT 0, 3;
select * from employee LIMIT 3;

-- 查询第2页员工数据，每页展示3条记录
select * from employee LIMIT 3,3;

综合案例

 

-- 综合案例
-- 1
select * from employee where age in (20,21,22,23) and
                             gender = '女';

-- 2
select * from employee where gender = '男' and
                             age between 20 and 40 and
                             workName like '___';

-- 3
select gender as '性别', count(*) as '员工数' from employee where age > 60 group by gender ;

-- 4
select workName,age from employee where age <= 35 order by age asc, entrydate desc;

-- 5
select * from employee where gender = '男' and
                             age between 20 and 40
                             order by age asc ,entrydate asc
                             limit 0,5;

DQL执行顺序

总结

DCL

介绍

用户管理

查询用户

use mysql;
select * from user;

创建用户

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

例子

-- 创建用户test，且主机名为localhost访问 密码123456
create user 'test'@'localhost' identified by '123456';

-- 创建用户test1，且可以在任何主机访问数据库 密码123456
create user 'test1'@'%' identified by '123456';

修改用户密码

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

删除用户

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

权限控制

查询权限

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

授予权限

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

 例子

grant all on test.* to 'test'@'localhost';

撤销权限

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

总结