首页 > 数据库 >MySQL——SQL语句

MySQL——SQL语句

时间:2024-08-06 18:26:54浏览次数:19  
标签:语句 dept -- SQL 查询 表名 MySQL id SELECT

文章目录

什么是SQL?

Structured Query Language:结构化查询语言

其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。

SQL通用语法

  • SQL语句可以单行或多行书写,以分号结尾。

  • 可使用空格和缩进来增强语句的可读性。

  • MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。

  • 3种注释:

  • 单行注释:-- 注释内容 或 # 注释内容(mysql特有)

  • 多行注释: /* 注释 */

SQL分类

  • DDL(Data Definition Language)数据定义语言

    用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等

  • DML(Data Manipulation Language)数据操纵语言

    用来对数据库中表的数据进行增删改。关键字:insert.delete,update等

  • DQL(Data Query Language)数据查询语言

    用来查询数据库中表的记录(数据)。关键字:select,where等

  • DCL(Data Control Language)数据控制语言

    用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等

DDL:操作数据库、表

3.1 操作数据库:CRUD

  • C(Create):创建

    • 创建数据库

       create database 数据库名称;
      
    • 创建数据库,判断不存在,再创建:

       create database if not exists 数据库名称;
      
    • 创建数据库,并指定字符集:

       create database 数据库名称 character set 字符集名;
      
    • 练习:创建db4数据库,判断是否存在,并指定字符集为gbk

       create database if not exists db4 character set gbk; 
      
  • R(Retrieve):查询

    • 查询所有数据的名称:

      show databases;
      
    • 查询某个数据库的字符集:查询某个数据库的创建语句

      show create database 数据库名称; 
      
  • U(Update):修改

    • 修改数据库的字符集:

       alter database db4 character set utf8;
      
  • D(Delete):删除

    • 删除数据库:

      drop database 数据库名称;
      
    • 判断数据库存在,存在再删除:

      drop database if exists 数据库名称;
      
  • 使用数据库

    use 数据库名称;
    
  • 查询当前正在使用的数据库名称

    select database();
    

3.2 操作表 CRUD

  • C(Create):创建

    --  语法:
    create table 表名(
    
       列名1 数据类型1,
    
       列名2 数据类型2,
    
       ......
    
       列名3 数据类型n
    );
    
    --- 注意:最后一列,不需要加逗号
    
  • 数据类型:

    • int:整数类型

      age int,

    • double:小数类型

      score double(5,2)

    • date:日期,只包含年月日,yyyy-MM-dd

    • datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss

    • timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss

    • varchar:字符串

      name varchar(20):姓名最大20字符

  • 练习:创建表

    create table student(
         id int,
         name varchar(32),
         age int,
         score double(4,1),
         birthday date,
         insert_time timestamp
    );
    
  • 复制表

    -- 复制表的结构
    create table if not exists 表名 like 被复制的表名;
    -- 新建一个查询表的内容
    create table if not exists 表名 as select语句;
    
  • R(Retrieve):查询

    • 查询某个数据库中所有的表名称

      show tables;
      
    • 查询表结构

      desc 表名;
      show create table 表名;
      
  • U(Update):修改

    • 修改表名

      alter table 表名 rename to 新表名;
      rename table 表名 to 新表名;
      
    • 修改表的字符集

      alter table 表名 character set 字符集名称;
      
    • 查看某张表的字符集

      show create table 表名;
      
    • 添加一列

      alter table 表名 add 列名 数据类型;
      
    • 修改列名称 类型

      alter table 表名 change 列名 新列名 新数据类型;
      
      alter table 表名 modify 列名 新数据类型;
      
    • 删除列

       alter table 表名 drop 列名;
      
  • D(Delete):删除

    drop table 表名;
    
    drop table if exists 表名;
    

DML:增删改表中数据

1.添加数据:

  • 语法:

    insert into 表名(列名1,列名2,列名3,......,列名n) values(值1,值2,......值n);
    
    # insert select 将select查询出来的数据插入到目标表中
    insert into 表名 select语句;
    
  • 注意:

    1.列名和值要一一对应;

    2.如果表名后,不定义列名,则默认给所有列添加值

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

    3.除了数字类型,其他类型需要使用引号(单双都可以)引起来

2.删除数据:

  • 语法:

    delete from 表名 [where条件]
    
  • 注意:

    1.如果不加条件,则删除表中所有记录

    2.如果要删除所有记录

    delete from 表名; --不推荐使用。有多少条记录就会执行多少次删除操作

    truncate table 表名; --推荐使用,效率高 先删除表,然后再创建一张一样的表。

3.修改数据:

  • 语法:

    update 表名 set 列名1 = 值1,列名2 = 值2,......[where条件];
    
  • 注意:

    1.如果不加任何条件,则会将表中所有记录全部修改。

DQL:查询表中的记录

1.语法:

select
   字段列表
from
   表名列表
where
   条件列表
group by
   分组字段
having
   分组之后的条件
order by
   排序
limit
   分页限定

2.基础查询

  • 1.多个字段的查询

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

    注意:如果查询所有字段,则可以使用*来替代字段列表。

  • 2.去除重复:distinct

  • 3.计算列

    一般可以使用四则运算计算一些列值。(一般只会进行数值型的计算)

    null参与的运算,计算结果都为null

  • 4.起别名:

    as

    as 也可以省略

3.条件查询

  • 1.where子句后跟条件

  • 2.运算符

    >、<、<=、>=、=、<>、<=>
    
    BETWEEN...AND
    
    IN(集合) -- 集合可以写成子查询  但是结果应该只有一列
    
    LIKE:模糊查询
    	   _:单个任意字符
    	   %:多个任意字符
    IS NULL
    IS NOT NULL
    count(字段名) 时注意null的列
    注意 null 和 空字符串'' 的区别
    
    count(*) count(columns) count(1)
    数全部的  数字段 有null值时不数  数全部的
    
    and
    
    or
    
    not   
    
  • 示例

    SELECT * FROM student WHERE age > 20;
    
    SELECT * FROM student WHERE age >= 20;
    
    SELECT * FROM student WHERE age =20;
    
    SELECT * FROM student WHERE age != 20;
    
    SELECT * FROM student WHERE age <> 20;
     
    SELECT * FROM student WHERE age >=20 && age <=30;
    
    SELECT * FROM student WHERE age >=20 AND age <=30;
    
    SELECT * FROM student WHERE age BETWEEN 20 AND 30;
    
    SELECT * FROM student WHERE age=22 OR age=20 OR age=25;
    
    SELECT * FROM student WHERE age IN(22,18,55);
    
    SELECT * FROM student WHERE english IS NOT NULL;
    
    SELECT * FROM student WHERE NAME LIKE '马%';
    
    SELECT * FROM student WHERE NAME LIKE "_化%";
    
    SELECT * FROM student WHERE NAME LIKE "___";
    
    SELECT * FROM student WHERE NAME LIKE "%景%" 
    
    show variables like "%char%"
    

4.排序查询

  • 语法:order by 子句

    order by 排序字段1 排序方式1,排序字段2 排序方式2…

  • 排序方式:

    • ASC:升序,默认的

    • DESC:降序

  • 注意:

    如果又多个排序条件,则当前边的条件值一样时,才会判断第二条件

其他函数

  • 函数有很多,这里只列取一些常用到函数的实例
-- if(condition,true_act,false_act) 函数 case when 的写法
select *,IF(age > 22,"old",'young') as age_type  from students;
select *,IF(age > 22,
						if(age>23,'big old','little old'),
					'young') as age_type  from students;
select *,case when age=22 then "young"
				when age=23 then 'little old'
				when age=24 then 'big old'
				else 'little young' end as type
				from students;
-- 字符串函数
-- 字符串拼接
-- concat(*strs) 如果字符串中有null 结果就是null
select CONCAT(name,',',gender,',','a') from students;
select CONCAT(name,gender,'a',null) from students;
-- CONCAT_WS(separator,str1,str2,...) 中间有分隔符 他会过滤null
select CONCAT_WS(',',name,gender,'a',null,'b') from students;

-- 字符串切分
-- SUBSTR(str FROM pos FOR len) , SUBSTR(str,pos),SUBSTR(str,pos,len)
select *,SUBSTR(name,2) from students;
select *,SUBSTR(name,1,2) from students;
select *,SUBSTR(name from 1 for 2) from students;
select *,SUBSTRING(name,1,2) from students;

-- STRCMP 字符串对比
select *,STRCMP(SUBSTR(name,1,1),'施') from students;

-- 数字类型的函数
select *,ABS(age),COS(age),LENGTH(clazz) from students;

5.聚合函数

将一列数据作为一个整体,进行纵向的计算

注意:聚合函数的计算,排除null值(可使用ifnull函数)

  • count:计算个数

    一般选择非空的列:主键

    例:SELECT COUNT(NAME) FROM student;

  • max:计算最大值

    例:SELECT MAX(math) FROM student;

  • min:计算最小值

    例:SELECT MIN(math) FROM student;

  • sum:计算和

    例:SELECT SUM(english) FROM student;

  • avg:计算平均值

    例:SELECT AVG(math) FROM student;

6.分组查询

  • 语法:group by 分组字段

  • 注意:

    1.分组之后查询的字段:分组字段、聚合函数

    2.where和having的区别?

    ​ where在分组之前进行限定,如果不满足条件,则不参与分组。

    ​ having在分组之后进行限定,如果不满足结果,则不会被查询出来

    ​ where后不可以跟聚合函数,having可以进行聚合函数的判断

  • 例:

    • 按照性别分组,分别查询男、女同学的平均分

      SELECT sex,AVG(math) FROM student GROUP BY sex;
      
    • 按照性别分组,分别查询男、女同学的平均分,人数

      SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
      
    • 按照性别分组,分别查询男、女同学的平均分,人数 要求:分数不低于70分的人,不参与分组。

      SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70  GROUP BY sex;
      
    • 按照性别分组,分别查询男、女同学的平均分,人数 要求:分数不低于70分的人,不参与分组,分组之后,人数大于2人。

      SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70  GROUP BY sex HAVING COUNT(id)>2;
      

7.分页查询:

  • 语法:limit 开始的索引,每页查询的条数

  • 公式:开始的索引 = (当前的页码 -1) * 每页显示的条数

  • 例:

    • 每页显示3条记录

      SELECT * FROM student LIMIT 0,3; --第一页
      
      SELECT * FROM student LIMIT 3,3; --第二页
      
      SELECT * FROM student LIMIT 6,3; --第三页
      

DCL:管理用户,授权

1.管理用户

  • 添加用户:

    • 关闭密码复杂验证

      set global validate_password_policy=0;
      set global validate_password_length=1;
      
    • 语法:

      CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
      
    • 例子:

      CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';
      
      CREATE USER 'lisi'@'%' IDENTIFIED BY '123';
      
  • 删除用户:

    • 语法:

      DROP USER '用户名'@'主机名';
      
    • 例子:

      DROP USER 'zhangsan'@'localhost';
      
  • 修改用户密码:

    • 语法:

      -- 老版本的写法 
      -- UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
      
      SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
      
    • 例子:

      set password for 'lisi'@'%' = password('234567');
      
  • 查询用户:

    • 1.切换到mysql数据库

      USE mysql;
      
    • 2.查询user表

      select * from user;
      

      通配符: % 表示可以在任意主机使用用户登录数据库

2.权限管理:

  • 查询权限:

    • 语法:

       SHOW GRANTS FOR '用户名'@'主机名';
      
       SHOW GRANTS FOR 'lisi'@'%';
      
  • 授予权限:

    • 语法:

      with grant option 表示带上授权的功能

      grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' [with grant option];
      
    • 例子:

      -- 将表db3.account的SELECT ,DELETE, UPDATE权限授予用户'lisi'@'%'
      GRANT SELECT ,DELETE, UPDATE  ON db3.account TO 'lisi'@'%';
      
      -- 给zhangsan用户所有权限  
      
      GRANT ALL  ON *.* TO 'zhangsan'@'localhost'
      
  • 撤销权限:

    • 语法:

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

      -- 将用户'lisi'@'%'对于表db3.account的更新权限撤销
      REVOKE UPDATE ON db3.account FROM 'lisi'@'%';
      
      -- 给lisi用户撤销所有权限
      REVOKE ALL ON *.* FROM 'lisi'@'%';
      

多表查询

  • 准备sql:

    -- 创建部门表
    CREATE TABLE dept(
       id INT PRIMARY KEY auto_increment,
       NAME VARCHAR(20)
    );
    -- 插入数据
    INSERT INTO dept(NAME) VALUES ('开发部'),('市场部'),('财务部'); 
    
    -- 创建员工表
    CREATE TABLE emp(
       id INT PRIMARY KEY auto_increment,
       NAME VARCHAR(10),
       gender CHAR(1),  -- 性别
       salary DOUBLE,  -- 工资
       join_date DATE, -- 入职日期
       dept_id INT,
       FOREIGN KEY(dept_id) REFERENCES dept(id)  -- 外键,关联部门表的主键
    );
    -- 插入数据
    INSERT INTO emp(NAME,gender,salary,join_date,dept_id) 
    VALUES 	('孙悟空','男',7200,'2013-02-24',1),
     		('猪八戒','男',3600,'2010-12-02',2),
     		('唐僧','男',9000,'2008-08-08',2),
     		('白骨精','女',5000,'2015-10-07',3),
     		('蜘蛛精','女',4500,'2011-03-14',1);
    
  • 笛卡尔积:

    有两个集合A,B 取这两个集合的所有组成情况。

    例如:

    A:(a,b,c)

    B:(1,2,3)

    A与B作笛卡尔积—> a,1 a,2 a,3 b,1 b,2 b,3 c,1 c,2 c,3

要完成多表查询,需要消除无用的数据

  • 1.内连接查询:

    • 隐式内连接:使用where条件消除无用数据

      --  查询所有员工信息和对应的部门信息
      SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;
      
      -- 查询员工表的名称,性别。部门表的名称
      SELECT 	emp.`NAME`
      		,emp.gender
      		,dept.`NAME` 
      FROM emp,dept WHERE emp.dept_id=dept.id;
      
      -- 或者
      SELECT	t1.`NAME`
      		,t1.gender
      		,t2.`NAME`
      FROM emp t1,dept t2
      WHERE t1.dept_id=t2.id;
      
    • 显式内连接:

      --语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件
      
      SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
      
  • 2.外连接查询:

    • 左外连接:

      --语法:
      select 字段列表 from 表1 left [outer] join 表2 on 条件;
      
      --查询的是左表所有数据以及其交集部分。 
      
    • 右外连接:

      --语法:
      select 字段列表 from 表1 right [outer] join 表2 on 条件;
      
      --查询的是右表所有数据以及其交集部分。
      
  • 3.子查询:

    查询中嵌套查询,称嵌套查询为子查询。

    -- 查询工资最高的员工信息
    
    -- 1.查询最高的工资是多少 9000
    SELECT MAX(salary) FROM emp;
     
    -- 2.查询员工信息,并且工资等于9000的
    SELECT * FROM emp WHERE emp.salary=9000;
    
    -- 一条sql就完成这个操作
    SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
    

    子查询不同情况:

    • 子查询的结果是单行单列的:

      --子查询可以作为条件,使用运算符去判断。  运算符:> >= < <= =
      
      --查询员工工资小于平均工资的人
      SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
      
    • 子查询的结果是多行单列的:

      --子查询可以作为集合,使用 in、not int
      
      --查询财务部和市场部所有员工信息
      SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部';
      SELECT * FROM emp WHERE dept_id=3 OR dept_id=2;
      
      --使用子查询
      SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部');
      
    • 子查询的结果是多行多列的

      --子查询可以作为一张虚拟表参与查询
      --查询员工入职日期是2011-11-11日之后的员工信息和部门信息
      
      -- 子查询
      select * from dept t1 (select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;
      
      --普通内连接查询
      select * from emp t1,dept t2 where t1.dept_id = t2.id and t1.join_date > '2011-11-11'  
      
  • 表的拼接

    拼接时两张表的结构必须完全一致

    • union 对数据进行去重
    • union all 不去重

标签:语句,dept,--,SQL,查询,表名,MySQL,id,SELECT
From: https://blog.csdn.net/weixin_73504499/article/details/140956216

相关文章

  • 18.python语句
    if语句一、if语句的介绍1、if单分支2、if的多分支3、if的嵌套4、三目运算=================================二、实操1、if单分支格式:if条件:执行语句1else执行语句2案例1在if语句判断中:我们可以使用比较运算符、成员运算符、逻辑运算符等,<,==,!=,>=,<=、and......
  • 【BUUCTF】BabySQli
    【BUUCTF】BabySQli题目来源收录于:BUUCTFGXYCTF2019题目描述纯粹的SQL注入题随意传入name=abc&pw=a返回wronguser尝试传入name=1%27&pw=a发现闭合方式为',同时给出了一个可疑字符串把这段字符串丢进ChatGPT,告诉我们可能是Base32编码的,对其进行Base32解码得到......
  • 《PostgreSQL 数据库在国内的发展前景》
    从DB-engines这张2024年8月的最新排名图上可以看出,PostgreSQL数据库的发展趋势还是非常好的,在国内,PostgreSQL数据库也展现出令人振奋的发展前景,非常明显的一种表现就是腾讯云、人大金仓、阿里云、华为等众多厂商都有基于PostgreSQL数据库的产品,比如TDSQL-PG版、PolarDB-PG版、Kin......
  • python SQLite 访问组件
    importosimportsqlite3fromsqlite3importErrorfromqueueimportQueue,EmptyfromtypingimportList,Tuple,AnyclassSQLiteDB:default_db_path=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))default_db_file=default_db_path......
  • sqlserver跟踪sql导出
     具体操作:打开跟踪窗口,登录到数据库服务器,新增跟踪,配置跟踪属性,确定,业务数据操作,暂停跟踪,导出sql脚本  登录到数据库服务器 新增跟踪,配置跟踪属性      ......
  • MySQL主从复制
    介绍MySOL主从复制是一个异步的复制过程,底层是基于Mysq1数据库自带的二进制日志功能。就是一台或多台MySOL数据库(slave,即从库)从另一台MvSOL数据库(master,即主库)进行日志的复制然后再解析日志并应用到自身,最终实现从库的数据和主库的数据保持一致。MySOL主从复制是MySOL数......
  • 【MySQL的索引优化】
    一、索引的使用场景1、全值匹配通过主键索引查询代码解读mysql>explainselect*fromt_goodswhereid=1\G;***************************1.row***************************id:1select_type:SIMPLEtable:t_goodspartitions:N......
  • CVE-2023-0562银行储物柜管理系统登录页面sql注入漏洞靶场复现
    在PHPGurukul银行储物柜管理系统1.0中发现了一个漏洞。它被评定为临界状态。受此问题影响的是组件登录的文件index.php的一些未知功能。对参数username的操作会导致sql注入。攻击可能是远程发起的。该漏洞已被公开,并可能被利用。此漏洞的标识符是VDB-219716。抓登录包放salmap......
  • 基于Java+SpringBoot+Mysql实现的在线电影订票系统部分功能设计与实现五
    一、前言介绍:1.1项目摘要随着互联网技术的飞速发展和普及,人们的娱乐消费方式发生了巨大变化。在线娱乐平台,特别是电影订票系统,成为了现代人观影的主要选择之一。这一变化主要得益于以下几个方面的因素:生活节奏的加快:现代人的生活节奏越来越快,时间变得尤为宝贵。在线电影......
  • 基于Java+SpringBoot+Mysql实现的在线电影订票系统部分功能设计与实现六
    一、前言介绍:1.1项目摘要随着互联网技术的飞速发展和普及,人们的娱乐消费方式发生了巨大变化。在线娱乐平台,特别是电影订票系统,成为了现代人观影的主要选择之一。这一变化主要得益于以下几个方面的因素:生活节奏的加快:现代人的生活节奏越来越快,时间变得尤为宝贵。在线电影......