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()固定搭配,只是对已有数据进行总数统计。一般用在一层维度分级的统计。
标签:语句,name,--,SQL,table,where,id,select From: https://www.cnblogs.com/xhboo/p/17442002.html