凉冰的手记
凉冰
2023-06-30
目录

SQL语句

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

4、 SQL语句分类

# DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

# 数据库操作

  1. 查询数据库
    • show databases; —— 查询所有数据库的名称
    • select database() ; ——查询当前使用的数据库
  2. 创建数据库
    • create database db1; ——db1指的是数据库名称
    • create database if not exists db1;
  3. 删除数据库
    • drop database db2; ——db2指的是数据库名称
    • drop database if exists db2;
  4. 切换数据库
    • use db1;——使用数据库db1

# 表操作

  1. 查询

    • show tables;—— 查询当前数据库下所有表的名称
    • desc t1; ——t1是表名称 查询表结构

  2. 创建

    建表语句

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

    常用类型

    int     4bytes        整型            --id int
double  8bytes        双精度浮点数值    --score double(5,2)  --总长度5位，小数点后面长度2位

char    0-255bytes    定长字符串       --name char(10)      
--总长度10位，空白地方补0;存储性能高，浪费空间
varchar 0-65535bytes  变长字符串       --name varchar(10)   
--总长度为name的实际长度;存储性能低，节约空间

date  3bytes   日期值
time  3bytes   时间值
datetime  8bytes  混合日期和时间值

  3. 删除（删除表时，数据会一块删除）

    • drop table t2;——t2指的表名
    • drop table if exists t2;

  4. 修改

    • alter table t1 rename to t2; --修改表名 #t1为原表名，t2为要修改的表名。
    • alter table t1 add row1 int; --添加一列 #row1为添加的列名，int为数据类型。
    • alter table t1 modify row1 char;--修改数据类型 #char为要修改的数据类型。
    • alter table t1 change row1 row2 varchar;
    • --修改列名和和数据类型 #row1为原列名，row2为新列名，varchar为要修改的数据类型
    • alter table t1 drop row2; --删除列 #row2为要删除的列

# DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进 行增、删、改操作。

  • 添加数据（INSERT）
  • 修改数据（UPDATE）
  • 删除数据（DELETE）

  1. 添加数据（INSERT）

    • 指定列添加

      • INSERT into stu(id,NAME) VALUES(1,"diana");

    • 给全部列添加

      INSERT into stu(id,NAME,sex,birthday,score,address,phone,state) VALUES(1,"张三",'男','1999-07-14',88.88,'123@qq.com','17854201283',0);

INSERT into stu VALUES(1,"张三",'男','1999-07-14',88.88,'123@qq.com','17854201283',0);

    • 批量添加

      • 批量添加某几列

        • INSERT into stu(id,NAME) VALUES(2,"张三"),(3,"张三"),(4,"张三");

      • 批量添加全部

        • INSERT into stu 
VALUES(2,"张三",'男','1999-07-14',88.88,'123@qq.com','17854201283',0),
(3,"张三",'男','1999-07-14',88.88,'123@qq.com','17854201283',0),
(4,"张三",'男','1999-07-14',88.88,'123@qq.com','17854201283',0);

  2. 修改数据（UPDATE）

    按照条件(where )修改数据 --->若不加限制where,则会修改全部相关数据

    UPDATE stu set sex='女' where name='张三';
UPDATE stu set birthday='2000-01-01',score=99.99 where name='张三';

  3. 删除数据（DELETE）

    按照条件(where )删除数据 --->若不加限制where,则会删除表中的全部数据

    DELETE FROM stu where name='da';

# DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

# 基本语法

# 基础查询

select name,age from stu; -- 查询两列

SELECT address from stu;  -- 有重复
SELECT DISTINCT address from stu ; -- DISTINCT，去除重复记录

SELECT name,math as 数学成绩,english as 英语成绩 from stu; -- as，起别名 as可省略

SELECT * from stu;  --查询全部，建议全写上
select id,name,age,sex,birthday,math,english,address from stu;

# 条件查询(where)

  • 精准查询

    SELECT *from stu where age>20; -- 查询年龄大于20岁的学员信息

select *from stu where age>=21; -- 查询年龄大于等于21岁的学员信息

SELECT *from stu where age>=20 AND age<=30; -- 查询年龄大于等于20，且小于等于30的信息
SELECT *from stu where age BETWEEN 20 AND 30; -- 查询年龄大于等于20，且小于等于30的信息

SELECT *from stu where  birthday BETWEEN '1999-01-01' AND '1999-04-01'; -- 查询日期（date类型）在01-01 到04-01之间  --date类型的数据也可以比较

SELECT *from stu where age =16; -- 查询等于16   -- 一个等号就是等于，不同于一般的编程语言

SELECT *from stu where age !=16; -- 查询不等于16
SELECT *from stu where age <>16; -- 查询不等于16

SELECT *from stu WHERE age=18 or age=16 or age=30;-- 查询等于16 或者等于18 或者等于30
SELECT *from stu WHERE age in (16,18,30);-- 查询等于16 或者等于18 或者等于30

SELECT *from stu WHERE english is null; -- 查询是否为null
SELECT *from stu WHERE english is not null; -- 查询是否为null

  • 模糊查询(like)

    通配符:
	_ :代表单个任意字符
	% :代表任意个数字符

SELECT *from stu WHERE name like '凯%'; -- 查询姓 凯 的学员信息
SELECT *from stu WHERE name like '_波%'; -- 查询第二个是 波 的学员信息
SELECT *from stu WHERE name like '%爱%'; -- 查询名字中含 爱 的学员信息
SELECT *from stu WHERE name like '[张李王]三'; -- 查询张三，李三，王三
SELECT *from stu WHERE name like '[^张李王]三'; -- 查询不是张三，李三，王三的 赵三等
select * from emp where name like '__';   #J. 查询姓名为两个字的员工信息
select * from emp where idcard like '%X'; #K. 查询身份证号最后一位是X的员工信息

# 聚合函数

聚合函数: 将一列数据当作一个整体，进行纵向计算。 null值不参与所有聚合函数的运算

count(age)     统计数量(一般选用不为null的列)
                -- 主键
                -- *
max(age)       最大值
min(age)       最小值
sum(age)       求和
avg(age)       求平均

SELECT COUNT(id) from stu ; -- 统计共有多少个  --要统计的列中最好不要有null
SELECT COUNT(*) from stu ;  -- 统计共有多少个  --只要某一行不全为null，就能完成统计
SELECT max(math) from stu;  -- 查询数学成绩的最高分
SELECT min(math) from stu;  -- 查询数学成绩的最低分
SELECT sum(math) from stu;  -- 查询数学成绩的总分
SELECT avg(math) from stu;  -- 查询数学成绩的平均分

# 分组查询(group by)(having)

查询字段为聚合函数和分组字段，其他字段没有意义 执行顺序 where>聚合函数>having

  • 所有where不能对聚合函数进行判断，having可以
  • where是分组之前进行限定，having是分组之后进行限定
 -- 查询男同学和女同学的各自数学平均分
SELECT sex,avg(math) from stu GROUP BY sex;

-- 查询男同学和女同学的各自英语平均分，以及各自人数
SELECT sex,avg(english),count(*) from stu GROUP BY sex;

-- 查询男同学和女同学的各自数学平均分，以及各自人数，要求分数低于70的不参与分组
SELECT sex,avg(math),count(*) from stu where math>=70 GROUP BY sex;

 -- 查询男同学和女同学的各自数学平均分，以及各自人数，(分组前--where)要求分数低于70的不参与分组，且分组之后(--having)人数大于2
SELECT sex,avg(english),count(*) from stu where math>=70  GROUP BY sex having count(*)>2;

# 排序查询

排序方式:
	ASC, 升序排列 --默认值
	DESC, 降序排列

SELECT * from stu order by age; -- 按年龄升序排列
SELECT * from stu order by age desc; -- 按年龄降序排列
SELECT * from stu order by math desc,english asc;-- 先按数学成绩降序，若数学成绩一样，在按照英语成绩升序

注:如果有多个排序条件,当前边的条件值一样时，才会根据第二个条件排序。

# 分页查询

limit 起始索引，查询条目数;  --起始索引默认从0开始

select * from stu LIMIT 0,3; -- 从0开始查询3条数据

-- 起始索引=（当前页码-1）*每页显示的条数
select * from stu LIMIT 0,3; -- 每页显示3条数据，查询第一页
select * from stu LIMIT 3,3; -- 每页显示3条数据，查询第二页
select * from stu LIMIT 6,3;-- 每页显示3条数据，查询第三页

tips:
	limit是mysql数据库的方言
	rownumber 是oracle的方言
	top 是SQLServer的方言

# 案例

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

--  --数据插入
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

-- --条件查询
-- A.查询年龄等于 88 的员工
select * from emp where age = 88;
-- B. 查询年龄小于 20 的员工信息
select * from emp where age < 20;
-- C. 查询年龄小于等于 20 的员工信息
select * from emp where age <= 20;
-- D. 查询没有身份证号的员工信息
select * from emp where idcard is null;
-- E. 查询有身份证号的员工信息
select * from emp where idcard is not null;
-- F. 查询年龄不等于 88 的员工信息
select * from emp where age != 88;
-- G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
-- H. 查询性别为 女 且年龄小于 25岁的员工信息
select * from emp where gender = '女' and age < 25;
-- I. 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in (18, 20, 40);
-- J. 查询姓名为两个字的员工信息 _ %
select * from emp where name like '__';
-- K. 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';


-- --聚合函数
-- A. 统计该企业员工数量
select count(*) from emp; -- 16
select count(id) from emp; -- 16
select count(idcard) from emp; -- 15  不记录null值
-- B. 统计该企业员工的平均年龄
select avg(age) from emp;
-- C. 统计该企业员工的最大年龄
select max(age) from emp;
-- D. 统计该企业员工的最小年龄
select min(age) from emp;
-- E. 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';


-- -- 分组查询
-- A. 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender;
-- B. 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender;
-- C. 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
-- having 是分组以后在进行过滤
-- address_count 起别名
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
-- D. 统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) from emp  group by gender, workaddress;


-- -- 排序查询
-- A. 根据年龄对公司的员工进行升序排序
select * from emp order by age;
select * from emp order by age asc;  -- asc 升序
-- B. 根据入职时间, 对员工进行降序排序
select * from emp order by entrydate desc; -- desc 降序
-- C. 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age, entrydate desc;


-- -- 分页查询
-- A. 查询第1页员工数据, 每页展示10条记录
-- 0 是起始索引
-- 10 是每页限制条数
select * from emp limit 0,10;
-- B. 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数  （2-1）* 10 = 10
select * from emp limit 10,10;


-- -- 综合练习
-- 1). 查询年龄为20,21,22,23岁的女性员工信息。
select * from emp where age in (20, 21, 22, 23) and gender = '女';
-- 2). 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
select * from emp where (age between 20 and 40) and gender = '男' and name like '___';
-- 3). 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender, count(*) from emp where age < 60 group by gender;
-- 4). 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name, age from emp where age <= 35 order by age, entrydate desc;
-- 5). 查询性别为男，且年龄在20-40 岁(含)以内的前3个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and (age between 20 and 40) order by age, entrydate  limit 0, 3;

# DCL

# 用户管理

  1. 查询用户

    select * from mysql.user;

  2. 创建用户

    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'diana'@'localhost' IDENTIFIED BY '123456'; -- localhost 只能在本机访问
CREATE USER 'diana'@'%' IDENTIFIED BY '123456';  -- % 代表任意主机都可以访问

  3. 修改用户密码

    ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
ALTER USER 'diana'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

  4. 删除用户

    DROP USER '用户名'@'主机名';
DROP USER 'diana'@'localhost';

# 权限控制

  1. 查询权限

    SHOW GRANTS FOR '用户名'@'主机名';
show grants for 'diana'@'%';

  2. 授予权限

    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
grant all on db1.emp to 'diana'@'%';
grant all on db1.* to 'diana'@'%';  -- * 表示通配符；数据库名和表名 都可以使用通配符

  3. 撤销权限

    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
revoke all on db1.emp from 'diana'@'%';
#知识#数据库#MySQL
上次更新: 2023 07 3
登录注册案例
函数

← 函数→

最近更新
01
数据层解决方案
07-03
02
第三方技术整合
07-03
03
原理篇
07-03
更多文章>
Theme by Vdoing | Copyright © 2023-2023 by 凉冰
FIND YOU
G_E_M_ 邓紫棋