设为首页收藏本站
切换到窄版

大道论

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
大道论»论坛 › 大道论 › 大道论 › SQL分类及语法
返回列表 发新帖
查看: 117|回复: 2

SQL分类及语法

[复制链接]
记录集集

2

主题

2

帖子

6

积分

新手上路

Rank: 1

积分
6
发表于 2022-9-22 09:57:04 | 显示全部楼层 |阅读模式
ubuntu进入mysql终端命令：mysql -uroot -p1234
-p后数字为数据库密码
SQL通用语法


  • SQL语句可以单行或多行书写，以分号结尾
  • SQL语句可以使用空格/缩进来增强语句可读性
  • MySQL数据库SQL语句不区分大小写，关键字建议使用大写（默认大小写都可）
  • 注释

    • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
    • 多行：/* 注释内容 */


SQL分类

SQL语言是一种数据库查询语言和程序设计语言它是一种关系型数据库语言，主要用于管理数据库中的数据，如存取数据、查询数据、更新数据等。关系型数据库语言由4部分组成
（1）数据定义语言（DDL）
（2）数据操作语言（DML）
（3）数据查询语言（DQL）
（4）数据控制语言（DCL）
分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库访问权限

DDL-数据定义语言

DDL-数据库操作


  • 查询

    • 查询所有数据库

SHOW DATABASES;




    • 查询当前数据库

SELECT DATABASE();



  • 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
​
#[]中的内容为可选参数，本文章后面内容所有的[]都表示[]中的内容为可选参数
#if not exists-如果不存在则创建；字符集utf8存储长度3字节(不推荐使用) utf8mb4存储长度4字节



  • 删除
DROP DATABASE [IF EXISTS] 数据库名;
​
#if exists-如果存在则删除；



  • 使用
USE 数据库名;   

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 表注释];



  • 修改

    • 添加字段

ALTER TABLE 表名 ADD 字段 类型（长度） [COMMENT 注释] [约束];




    • 修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;




    • 修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束];




    • 删除字段

ALTER TABLE 表名 DROP 字段名;




    • 修改表名

ALTER TABLE 表名 RENAME TO 新表名; alter table 表名 rename to 新表名;



  • 删除

    • 删除表

DROP TABLE [IF EXISTS] 表名;



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

TURNCATE TABLE 表名; #在删除表时，表中的所有数据也会被删除


数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

  • 数值类型
类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述
TINYINT1  byte(-128，127)(0，255)小整数值
SMALLINT2  bytes(-32768，32767)(0，65535)大整数值
MEDIUMINT3  bytes(-8388608，8388607)(0，16777215)大整数值
INT或INTEGER4  bytes(-2147483648，2147483647)(0，4294967295)大整数值
BIGINT8  bytes(-2^63，2^63-1)(0，2^64-1)极大整数值
FLOAT4  bytes(-3.402823466 E+38，3.402823466351  E+38)0 和 (1.175494351  E-38，3.402823466 E+38)单精度浮点数值
DOUBLE8  bytes(-1.7976931348623157 E+308，1.7976931348623157 E+308)0 和  (2.2250738585072014 E-308，1.7976931348623157 E+308)双精度浮点数值
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)



  • 字符类型
类型大小描述
CHAR0-255 bytes定长字符串char(10) -> 性能好
VARCHAR0-65535 bytes变长字符串varchar(10) -> 性能较差
TINYBLOB0-255 bytes不超过255个字符的二进制数据
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据



  • 时间日期类型
类型大小范围格式描述
DATE31000-01-01 至  9999-12-31YYYY-MM-DD日期值
TIME3-838:59:59 至  838:59:59HH:MM:SS时间值或持续时间
YEAR11901 至 2155YYYY年份值
DATETIME81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳


DML-数据操作语言

DML是数据操作语言，用来对数据库表中的数据进行增删改

  • 添加数据 (INSERT)
  • 修改数据 (UPDATE)
  • 删除数据 (DELETE)

DML-添加字段


  • 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值1,值2,...);



  • 给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...);



  • 批量添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);


注意事项：

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

DML-修改数据

UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,...[WHERE 条件];注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据



DML-删除数据

DELETE FROM 表名 [WHERE 条件]注意：

  • delete语句条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
  • delete语句不能删除某一个字段的值（可以使用update）



DQL-数据查询语言

DQL是数据查询语言，用来查询数据库中表的记录

查询关键字：select

DQL语法

#编写顺序
SELECT
        字段列表
FROM
        表名列表
WHERE
        条件列表
GROUP BY
        分组字段列表
HAVING
        分组后条件列表
ORDER BY
        排序字段列表
LIMIT
        分页参数接下来按照下列顺序一一学习DQL语法

  • 基本查询
  • 条件查询（WHERE）
  • 聚合函数（count、max、min、avg、sum）
  • 分组查询（GROUP BY）
  • 排序查询（ORDER BY）
  • 分页查询（LIMIT）

DQL-基本查询


  • 查询多个字段
SELECT 字段1,字段2,字段3...FROM 表名;
SELECT * FROM 表名;
示例：
#进入text数据库
mysql> use text;  
Database changed  

#创建员工表
mysql> 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 '员工表';  

    Query OK, 0 rows affected (0.12 sec)  

#给全部字段批量添加数据
mysql> insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
    -> VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
    ->  (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
    ->  (3, '3', '韦一笑', '女', 38, '123456789012345670', '上海', '2005-08-01'),
    ->  (4, '4', '赵敏', '女', 18, '123456789012345670', '北京', '2009-12-01'),
    ->  (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01'),
    ->  (6, '6', '杨逍', '男', 28, '12345678901234567X', '北京', '2006-01-01'),
    ->  (7, '7', '范瑶', '男', 40, '123456789012345670', '北京', '2005-05-01'),
    ->  (8, '8', '黛绮丝', '女', 38, '123456789012345670', '天津', '2015-05-01'),
    ->  (9, '9', '范凉凉', '女', 45, '123456789012345678', '北京', '2010-04-01'),
    ->  (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
    ->  (11, '11', '张士诚', '男', 55, '123456789012345670', '江苏', '2015-05-01'),
    ->  (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'),
    ->  (13, '13', '张三丰', '男', 88, '123456789012345678', '江苏', '2020-11-01'),
    ->  (14, '14', '灭绝', '女', 65, '123456789012345670', '西安', '2019-05-01'),
    ->  (15, '15', '胡青牛', '男', 70, '12345678901234567X', '西安', '2018-04-01'),
    ->  (16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

Query OK, 16 rows affected (0.04 sec) Records: 16  Duplicates: 0  Warnings: 0   


#查询id，姓名，年龄字段的数据
mysql> select id,name,age from emp;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | 柳岩      |   20 |
|    2 | 张无忌    |   18 |
|    3 | 韦一笑    |   38 |
|    4 | 赵敏      |   18 |
|    5 | 小昭      |   16 |
|    6 | 杨逍      |   28 |
|    7 | 范瑶      |   40 |
|    8 | 黛绮丝    |   38 |
|    9 | 范凉凉    |   45 |
|   10 | 陈友谅    |   53 |
|   11 | 张士诚    |   55 |
|   12 | 常遇春    |   32 |
|   13 | 张三丰    |   88 |
|   14 | 灭绝      |   65 |
|   15 | 胡青牛    |   70 |
|   16 | 周芷若    |   18 |
+------+-----------+------+
16 rows in set (0.00 sec)   



mysql> select * from emp;
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.00 sec)

  • 设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]...FROM 表名;   #AS可以省略



  • 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;


DQL-条件查询


  • 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表

  • 条件
比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<>或!=不等于
BETWEEN...AND在某个范围之内（含最小、最大值）
IN(...)在in之后的列表中的值，多选一
LIKE 占位符模糊匹配（_匹配单个字符，%匹配任意字符）
IS NULL是NULL
逻辑运算符功能
AND 或 &&并且（多个条件同时成立）
OR 或 ||或者（多个条件任意成立一个）
NOT 或 !非，不是



  • 示例
#查询年龄等于88的员工
mysql> select * from emp where age=88;
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
1 row in set (0.01 sec)   


#查询小于20的员工信息
mysql> select * from emp where age < 20;
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
4 rows in set (0.00 sec)   


#查询没有身份证号的员工信息
mysql> select * from emp where idcard is null;
+------+--------+-----------+--------+------+--------+-------------+------------+
| id   | workno | name      | gender | age  | idcard | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------+-------------+------------+
|   16 | 16     | 周芷若    | 女     |   18 | NULL   | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------+-------------+------------+
1 row in set (0.00 sec)   


#查询有身份证号的员工信息
mysql> select * from emp where idcard is not null;
+----+--------+--------+--------+-----+-------------------+-------------+------------+
| id | workno | name   | gender | age | idcard            | workaddress | entrydate  |
+----+--------+--------+--------+-----+-------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
15 rows in set (0.01 sec)   



#查询年龄在15岁（包含）到20岁（包含）之间的员工信息
mysql> select * from emp where age between 15 and 20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.00 sec)   


#查询年龄等于18或20或40的员工信息
mysql> select * from emp where age=18 or age=20 or age=40;  / select * from emp where in(18,20,40);
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.00 sec)   


#查询姓名为两个字的员工
mysql> select * from emp where name like '__';
+----+--------+-------+--------+------+--------------------+-------------+------------+
| id | workno | name  | gender | age  | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+------+--------------------+-------------+------------+
|  1 | 1      | 柳岩  | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|  4 | 4      | 赵敏  | 女     |   18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭  | 女     |   16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍  | 男     |   28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶  | 男     |   40 | 123456789012345670 | 北京        | 2005-05-01 |
| 14 | 14     | 灭绝  | 女     |   65 | 123456789012345670 | 西安        | 2019-05-01 |
+----+--------+-------+--------+------+--------------------+-------------+------------+
6 rows in set (0.01 sec)   



#查询身份证号最后一位是X的员工信息
mysql> select * from emp where idcard like '%X';
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
2 rows in set (0.00 sec)

DQL-聚合函数


  • 聚合函数指的是将一列数据作为整体，进行纵向计算。


  • 常见的聚合函数
函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和


  • 语法
    SELECT 聚合函数(字段列表) FROM 表名;


  • 示例
#查询年龄等于88的员工
mysql> select * from emp where age=88;
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
1 row in set (0.01 sec)   


#查询小于20的员工信息
mysql> select * from emp where age < 20;
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
4 rows in set (0.00 sec)   


#查询没有身份证号的员工信息
mysql> select * from emp where idcard is null;
+------+--------+-----------+--------+------+--------+-------------+------------+
| id   | workno | name      | gender | age  | idcard | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------+-------------+------------+
|   16 | 16     | 周芷若    | 女     |   18 | NULL   | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------+-------------+------------+
1 row in set (0.00 sec)   


#查询有身份证号的员工信息
mysql> select * from emp where idcard is not null;
+----+--------+--------+--------+-----+-------------------+-------------+------------+
| id | workno | name   | gender | age | idcard            | workaddress | entrydate  |
+----+--------+--------+--------+-----+-------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
15 rows in set (0.01 sec)   



#查询年龄在15岁（包含）到20岁（包含）之间的员工信息
mysql> select * from emp where age between 15 and 20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.00 sec)   


#查询年龄等于18或20或40的员工信息
mysql> select * from emp where age=18 or age=20 or age=40;  / select * from emp where in(18,20,40);
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.00 sec)   


#查询姓名为两个字的员工
mysql> select * from emp where name like '__';
+----+--------+-------+--------+------+--------------------+-------------+------------+
| id | workno | name  | gender | age  | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+------+--------------------+-------------+------------+
|  1 | 1      | 柳岩  | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|  4 | 4      | 赵敏  | 女     |   18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭  | 女     |   16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍  | 男     |   28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶  | 男     |   40 | 123456789012345670 | 北京        | 2005-05-01 |
| 14 | 14     | 灭绝  | 女     |   65 | 123456789012345670 | 西安        | 2019-05-01 |
+----+--------+-------+--------+------+--------------------+-------------+------------+
6 rows in set (0.01 sec)   



#查询身份证号最后一位是X的员工信息
mysql> select * from emp where idcard like '%X';
+----+--------+-------+--------+-----+--------------------+-------------+------------+
| id | workno | name  | gender | age | idcard             | workaddress | entrydate  |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
+----+--------+-------+--------+-----+--------------------+-------------+------------+
2 rows in set (0.00 sec)

DQL-分组查询


  • 语法
    SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];


  • where与having区别

    • 执行时机不同：where是分组之前进行过滤，不满足where条件，不分组参与；而having是分组之后对结果进行过滤
    • 判断条件不同：where不能对聚合函数进行判断，而having可以




  • 示例
#根据性别分组，统计男性员工和女性员工的数量
mysql> select gender, count(*) from emp group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        8 |
| 男     |        8 |
+--------+----------+
2 rows in set (0.01 sec)   



#根据性别分组，统计男性员工和女性员工的平均年龄
mysql> select gender, avg(age) from emp group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 女     |  32.2500 |
| 男     |  48.0000 |
+--------+----------+
2 rows in set (0.00 sec)   



#查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
mysql> select workaddress, count(*) from emp where age<45 group by workaddress having count(*)>=3;
+-------------+----------+
| workaddress | count(*) |
+-------------+----------+
| 北京        |        7 |
+-------------+----------+
1 row in set (0.01 sec)


  • 注意

    • 执行顺序：where>聚合函数>having
    • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义



DQL-排序查询


  • 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

  • 排序方式

    • ASC：升序（默认值）
    • DESC：降序



  • 示例
#根据年龄，对公司员工进行升序排序
mysql> select * from emp order by age asc;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.00 sec)   


#根据年龄，对公司员工进行升序排序，年龄相同的再根据入职时间，对员工进行降序排序  
mysql> select * from emp order by age asc,entrydate desc;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.01 sec)

DQL-分页查询


  • 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

  • 注意：

    • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
    • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
    • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。



  • 示例
#查询第1页员工数据, 每页展示10条记录
mysql> select * from emp limit 0,10;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
|  1 | 1      | 柳岩   | 女     |  20 | 123456789012345678 | 北京        | 2000-01-01 |
|  2 | 2      | 张无忌 | 男     |  18 | 123456789012345670 | 北京        | 2005-09-01 |
|  3 | 3      | 韦一笑 | 女     |  38 | 123456789012345670 | 上海        | 2005-08-01 |
|  4 | 4      | 赵敏   | 女     |  18 | 123456789012345670 | 北京        | 2009-12-01 |
|  5 | 5      | 小昭   | 女     |  16 | 123456789012345678 | 上海        | 2007-07-01 |
|  6 | 6      | 杨逍   | 男     |  28 | 12345678901234567X | 北京        | 2006-01-01 |
|  7 | 7      | 范瑶   | 男     |  40 | 123456789012345670 | 北京        | 2005-05-01 |
|  8 | 8      | 黛绮丝 | 女     |  38 | 123456789012345670 | 天津        | 2015-05-01 |
|  9 | 9      | 范凉凉 | 女     |  45 | 123456789012345678 | 北京        | 2010-04-01 |
| 10 | 10     | 陈友谅 | 男     |  53 | 123456789012345670 | 上海        | 2011-01-01 |
+----+--------+--------+-------+------+--------------------+-------------+------------+
10 rows in set (0.00 sec)   



#查询第2页员工数据, 每页展示10条记录 mysql> select * from emp limit 10,10;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name   | gender | age | idcard             | workaddress | entrydate  |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 11 | 11     | 张士诚 | 男     |  55 | 123456789012345670 | 江苏        | 2015-05-01 |
| 12 | 12     | 常遇春 | 男     |  32 | 123456789012345670 | 北京        | 2004-02-01 |
| 13 | 13     | 张三丰 | 男     |  88 | 123456789012345678 | 江苏        | 2020-11-01 |
| 14 | 14     | 灭绝   | 女     |  65 | 123456789012345670 | 西安        | 2019-05-01 |
| 15 | 15     | 胡青牛 | 男     |  70 | 12345678901234567X | 西安        | 2018-04-01 |
| 16 | 16     | 周芷若 | 女     |  18 | NULL               | 北京        | 2012-06-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
6 rows in set (0.00 sec)
DQL-案例练习


  • 查询年龄为20，21，22，23岁的员工信息
select * from emp where age in(20,21,22,23);

  • 查询性别为男，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工
select * from emp where gender='男' && age between 20 and 40 && name like '___';

  • 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender,count(*) from emp where age<60 group by gender;

  • 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按 入职时间降序排序
select name,age from emp where age<=35 order by age asc,entrydate desc;

  • 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序， 年龄相同按入职时间升序排序
select * from emp where gender='男' && age between 20 and 40 order by age asc,entrydate desc limit 0,5;
DQL-执行顺序

#编写顺序
SELECT
        字段列表        #4
FROM
        表名列表        #1
WHERE
        条件列表        #2
GROUP BY
        分组字段列表    #3
HAVING
        分组后条件列表  
ORDER BY
        排序字段列表    #5
LIMIT
        分页参数        #6
#执行顺序
FROM
        表名列表
WHERE
        条件列表
GROUP BY
        分组字段列表
HAVING
        分组后条件列表
SELECT
        字段列表
ORDER BY
        排序字段列表
LIMIT
        分页参数

DCL-数据控制语言

DCL是用来管理数据库用户、控制数据库访问权限

DCL-用户管理


  • 查询用户
USE mysql; SELECT * FROM user;

  • 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  • 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

  • 删除用户
DROP USER'用户名'@'主机名';
示例
#进入mysql数据库
use mysql;


#查询user用户表
select * from user;


#创建用户dany，只能够在当前主机localhost访问，密码123456
create user 'dany'@'localhost' identified by '123456';


#创建用户xiangwan，能够在任意主机访问该数据库，密码123456
create user 'xiangwan'@'%' identified by '123456';    #%代表任意主机都可以访问


#修改用户xiangwan的访问密码为1234
alter user 'xiangwan'@'localhost' identified with mysql_native_password by '1234';


#删除dany@localhost用户
drop user 'dany'@'localhost';
注意：

  • 主机名可以使用%通配
  • 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator）使用

DCL-权限控制


  • 常用权限
权限说明
ALL, ALL PRIVILEGES所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表


  • 其他权限，可参考https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html


  • 常用语法
     查询权限
SHOW GRANTS FOR '用户名'@'主机名';     授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';     撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

  • 注意：

    • 多个权限之间，使用逗号分隔
    • 授权时，数据库名和表名可以使用*进行通配，代表所有




  • 示例
#查询权限
show grants for 'xiangwan'@'%';  

+--------------------------------------+
| Grants for xiangwan@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `xiangwan`@`%` |
+--------------------------------------+
1 row in set (0.01 sec)   



#给主机xiangwan@% 授予 text1数据库中所有表 的所有权限
grant all on text1.* to 'xiangwan'@'%';   



#再次查询可以发现 主机xiangwan@% 中权限增加
show grants for 'xiangwan'@'%';
+-------------------------------------------------------+
| Grants for xiangwan@%                                 |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `xiangwan`@`%`                  |
| GRANT ALL PRIVILEGES ON `text1`.* TO `xiangwan`@`%`   |
+-------------------------------------------------------+
2 rows in set (0.01 sec)   



#撤销权限
revoke all on ldntext.* from 'xiangwan'@'%';   



#再次查询
show grants for 'xiangwan'@'%';  
+--------------------------------------+
| Grants for xiangwan@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `xiangwan`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)
回复

使用道具 举报

小短腿的黑猫

2

主题

3

帖子

7

积分

新手上路

Rank: 1

积分
7
发表于 2022-9-22 09:57:50 | 显示全部楼层
[赞同][赞同][赞同]
回复

使用道具 举报

曾海春

1

主题

4

帖子

8

积分

新手上路

Rank: 1

积分
8
发表于 昨天 02:44 | 显示全部楼层
学习下
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|大道论

GMT+8, 2025-4-7 01:35 , Processed in 0.102825 second(s), 22 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表