1 2 3
4

SQL语句

SQL语句

分类：

  • DQL：数据查询语言，用于对数据进行查询
  • DML：数据操作语言，对数据进行增加、修改、删除
  • TPL：事务处理语言，对事务进行处理
  • DDL：数据定义语言，进行数据库、表的管理等
  • DCL：数据控制语言，进行授权与权限回收
  • CCL：指针控制语言，通过控制指针完成表的操作

数据库的增删改查是必须要掌握的。即为 insert、delete、update、select

数据库操作：

->查看所有数据库

show databases;

->查看该数据库建表语句

show create table 表名;

->创建数据库

create datebases 数据库名;
create datebases 数据库名 charset=utf8;

->删除数据库

drop datebase 数据库；

->使用数据库

use 数据库名；

->查看当前数据库

select database();

数据表操作

->查看当前数据库所有表

show tables;

->查看表结构

desc 表名;

->创建表格

语句1:
CREATE TABLE IF NOT EXISTS `test_tb`(
  `id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
  `title` VARCHAR(100) NOT NULL COMMENT '标题',
  `author` VARCHAR(40) NOT NULL COMMENT '作者',
  `cdate` DATE COMMENT '日期',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表格';

语句2:
CREATE TABLE `city` (
  `id` int NOT NULL COMMENT '编号',
  `name` varchar(100) DEFAULT NULL COMMENT '城市名称',
  `pid` varchar(4) DEFAULT NULL COMMENT '父ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='城市表格';
  • -- 如果你不想字段为NULL,可以设置字段的属性为 NOT NULL
  • -- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1
  • -- COMMENT对字段的注释
  • -- 创建外键，test_tb中的id类型一样要和目标表（goods表）中的id一致（可以没有外键）
  • -- 删除外键：alter table test_tb drop foreign key 外键名称
  • -- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
  • -- ENGINE 设置存储引擎，CHARSET 设置编码。

->修改表结构

->新增字段

alter table 表名 add 列名 类型; 
alter table test_tb add cast double default 0;

->修改字段

alter table 表名 change 原名 新名 类型及约束; 
alter table test_tb change cast num int default 1;

->修改字段类型

alter table 表名 modify 列名 类型及约束; 
alter table test_tb modify num double default 0;

->删除表格

drop tables 表名;

例如：

-- 新增字段
alter table employees add age int default null;

-- 新建表格
CREATE TABLE `new_employees` (
  `emp_no` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
   PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 写入数据
insert into new_employees(
  emp_no
  ,age
  ,birth_date
  ,first_name
  ,last_name
  ,gender
  ,hire_date
)
select
  emp_no
  ,2022 - left(birth_date,4) age
  ,birth_date
  ,first_name
  ,last_name
  ,gender
  ,hire_date
from employees

-- 删除表格
drop table employees;

增删改查

新增数据
insert into table_name (field1, field2,...)
values (value1, value2,...)
,(value1, value2,...)
,(value1, value2,...)
;

删除数据

delete from table_name where 删除的条件
-- 如果没有写where条件，表中的数据将被全部删掉
-- 清空表数据，表的结构、索引、触发器、约束等将被保留，后续仍然可以使用该表
truncate table table_name
-- 使用truncate table，消耗的资源更少，比delete from要快

修改（更新）数据

update table_name set field1=value1, field2=value2 where 更新的条件;
查询数据
-- 指定字段查询
select column_name1, column_name2 from table_name;
-- 可以使用一个或者多个表，表之间使用逗号(,)分割，不建议这么使用
-- 因为多个表格一起查询，遇到重名的字段，会很麻烦
select table_name1.column_name1, table_name1.column_name2,
table_name2.column_name1
from table_name1, table_name2;

-- 使用where语句来设定查询条件
select column_name1, column_name2 from table_name where 查询条件;
select column_name1, column_name2 from table_name where id >= 10;

-- 使用星号（*）来代替其他字段，select语句会返回表的所有字段数据
select * from table_name;
-- 使用 LIMIT 属性来设定返回的记录数
select * from table_name limit 10;
-- 通过as可以将字段或者表格起别名，在这个sql中，之后的语句都使用这个别名
select column_name1 as a, column_name2 as b FROM table_name as t;
select t.column_name1, t.column_name2 from table_name as t;
-- 数据去重
select distinct * from table_name;
select distinct column_name1, column_name2 from table_name;

条件

使用where子句对表中的数据筛选，满足where后面条件的数据会被查询出来

select * from table_name where 查询条件;
where后面支持多种判断，进行条件处理
  • 比较运算符
-- 等于
select * from table_name where id = 3;
-- 大于
select * from table_name where id = 3;
-- 大于等于
select * from table_name where id >= 3;
-- 小于
select * from table_name where id < 3;
-- 小于等于
select * from table_name where id <= 3;
-- 不等于
select * from table_name where id != 3;
select * from table_name where id <> 3;
  • 逻辑运算符
-- 与
select * from table_name where id > 3 and gender = '男';
-- 或
select * from table_name where id > 3 or gender = '男';
  • 模糊查询

模糊查询一定是配合like使用

-- 下划线 _ 匹配任意一个字符
select * from table_name where name like '周_';
-- % 匹配任意多个字符
select * from table_name where name like '%周';
  • 范围查询 in
-- 取id为1、4、10的人员数据
select * from table_name where id in (1,4,10);
select * from table_name where id=1 or id=4 or id=10;

-- between 取连续的数据
select * from table_name where id between 6 and 20;
select * from table_name where id >= 6 and id <=20;
-- 不同的数据库，sql中between的边界可能不同，有些是包头包尾，有些是包头去尾，或者是不包头也不
包尾
  • 判断空
-- NULL
select * from table_name where name is null;
-- 非空
select * from table_name where name is not null;

null不是 '' 。null是数据没有填，'' 表示空的字符串。不能使用 = NULL 或 != NULL 在列中查找 NULL 值

  • 优先级
-- 当无法判断条件运行的优先时，可以使用小括号
select * from table_name where id > 10 and name is null or gender = '男';
select * from table_name where id > 10 and (name is null or gender = '男');

UNION

DISTINCT: 删除结果集中重复的数据。UNION是默认删除重复数据，DISTINCT很少用到。

ALL: 返回所有结果集，包含重复数据。

注意： UNION关联的两张表不在乎字段的名称是否相同。但是要求对应字段的格式和类型一致，而且字 段的个数也要一致。

排序
-- 默认是从小到大排序。通过逗号隔开，允许多列进行排序的判断
-- desc 表示逆序，从大到小
select * from table_name order by 列1 asc|desc ,列2 asc|desc
select * from table_name order by id
select * from table_name order by id desc
聚合
-- 统计总数
select count(*) from table_name;
select count(0) from table_name;
-- 统计id大于3的人数
select count(0) from table_name where id >3;

-- 最大值
select max(id) from table_name;
-- 性别为女的最大ID
select max(id) from table_name where gender='女';

-- 最小值
select min(id) from table_name;
-- 性别为男的最小ID
select min(id) from table_name where gender='男';

-- 求和
select sum(age) from table_name;
-- 性别为男的年龄总值
select sum(age) from table_name where gender='男';

-- 平均值
select avg(age) from table_name;
-- 性别为男的年龄平均值
select avg(age) from table_name where gender='男';
select sum(age)/count(0) from table_name where gender='男';
分组：group by

将查询结果按照字段进行分组，字段值相同的为一组。可用于单个字段分组，也可用于多个字段分组。

-- 性别分组
select gender from table_name group by gender;

-- 利用分组去重
select id, name from table_name group by id, name;
-- 这里的去重是利用group by进行去重，它的效率会比distinct快，但是要求将进行去重的字段全部写入
分组内

-- 分组后的字段拼接
select gender, group_concat(name) from table_name group by gender;
select gender, concat(name) from table_name group by gender;

-- 分组后的聚合
-- 各个性别的人数
select gender, count(0) from table_name group by gender;
-- 各个性别的平均年龄
select gender, avg(age) from table_name group by gender;

-- 分组后的条件筛选
-- 各个性别的平均年龄大于10的数据
select gender, avg(age) from table_name group by gender having avg(age) >10;
-- 各个性别的平均年龄大于10的人数
select gender, count(0) from table_name group by gender having avg(age) >10;

-- 分组之后的总数统计
select gender, count(0) from table_name group by gender -- with rollup;
select gender, concat(name) from table_name group by gender -- with rollup;

-- 使用coalesce代替空值
select coalesce(gender, 'total'), count(0) num from table_name group by gender -
- with rollup;
注意

with rollup并非和coalesce()固定搭配，只是对已有数据进行总数统计。一般用在一层维度分级的统计。

posted @ 2023-05-29 23:18  XHkoko  阅读(57)  评论(0)    收藏  举报