美文网首页
SQL语句之单表操作

SQL语句之单表操作

作者: 肥四_F4 | 来源:发表于2020-07-01 21:37 被阅读0次

DDL 应用

作用：数据定义语言
1.库定义

#创建库
CREATE DATABASE test CHARSET utf8mb4;
CREATE DATABASE wordpress;
#查询库
SHOW DATABASES;
SHOW CREATE DATABASE test;
SHOW CREATE DATABASE wordpress;
#修改库
ALTER DATABASE wordpress CHARSET utf8mb4;
#删除库
DROP DATABASE wordpress;

2.表定义

# 建表
USE test;
CREATE TABLE stu (
id     INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname  VARCHAR(64) NOT NULL COMMENT '姓名',
age    TINYINT  UNSIGNED  NOT NULL DEFAULT 0  COMMENT '年龄',
gender ENUM('m','f','n')  NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

# 查表
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;

# 修改表定义
-- 添加和删除字段
-- 1. 在表中添加telnum char(11) not null unique key comment '手机号'
ALTER TABLE stu ADD COLUMN  telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';

-- 2. 在sname后添加a列
ALTER TABLE stu ADD COLUMN  a INT NOT NULL COMMENT '测试列' AFTER sname;

-- 3. 在第一列前添加b列
ALTER TABLE stu ADD COLUMN  b INT NOT NULL COMMENT '测试列' FIRST ;

-- 4. 删除添加的a,b列
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;

-- 5. 修改数据类型 
ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '手机号';
ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '手机号';

-- 6. 修改列名及数据类型
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '手机号';

#删除表
drop table stu;

DDL语句开发规范

库 ： create database test charset utf8mb4;

  1. 库名要与业务有关
  2. 库名不是用大写字母、数字开头
    为了多平台兼容
  3. 不要使用内置关键字
  4. 建库的时候必须置顶字符集
  5. 生产中禁止使用删库操作

表

  1. 表名：与业务有关，例如：wp_users,不使用大写字母、数字开头，不要太长（16以下）
  2. 设置存储引擎、字符集、表注释
  3. 表名、列名要是用内置关键字
  4. 列名要有意义，长度（16以下）
  5. 数据类型：合适的、足够的、简短
  6. 每个表要有主键，一般是自增长、（无关列数字列）
  7. 每个列尽量是not null ，可以配合default
  8. 每个列要有注释
  9. 修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc

DCL 应用

grant
revoke

DML应用

做用：针对标的数据行增删改查
种类
1.insert 应用

insert 应用
USE test;
DESC student;
# 标准数据插入方式 
INSERT student(id,NAME,age,gender,intime)
VALUES(1,'zhangs',18,'M','2020-07-02 08:30:00');

SELECT * FROM student;

INSERT INTO 
    student(id,NAME,age,gender,intime)
VALUES
(2,'zhang1',18,'M','2020-07-01 08:30:00'),
(3,'zhang2',19,'F','2020-07-03 08:30:00'),
(4,'zhang3',17,'M','2020-07-05 08:30:00'),
(5,'zhang4',16,'F','2020-07-06 08:30:00'),
(6,'zhang5',15,'M','2020-07-07 08:30:00');

# 省略写法
INSERT INTO 
    student 
VALUES 
(7,'zhang6',19,'M','2020-06-07 08:30:00');

# 部分列录入
INSERT INTO 
student(NAME,intime)
VALUES('ma6',NOW());

SELECT * FROM student;

2.update 应用

UPDATE student SET NAME='马六' WHERE id=8;
  1. delete 应用
DELETE FROM student WHERE id=8 ;

彩蛋1： 伪删除的实现,使用update替代delete。

# 1. 添加一个状态列 state 
ALTER TABLE student ADD state TINYINT NOT NULL DEFAULT 1 ;

# 2. update 替代 delete 
UPDATE student SET state=0 WHERE id=9;

# 3. 查询语句修改为
SELECT * FROM student WHERE state=1;

彩蛋2： 以下三条语句的功能及区别？

drop table t1 ;      ---> 表定义+表数据（物理），全删除，磁盘空间立即删除
truncate table t1 ;  ---> 清空表数据（物理），立即释放磁盘空间。
delete from t1;      ---> 逐行删除表数据（逻辑，delete mark）。不会立即释放磁盘空间，会有碎片。

DQL 应用

1.select
作用获取用户表中的数据行
独立使用

# 查询系统变量（参数）
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
#替代方案： 
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';

# 查询用户变量
SET @oldguo=100;
SELECT @oldguo;

# 查询函数
SELECT VERSION();
SELECT USER();
SELECT NOW();
SELECT CONCAT("hello world")
SELECT CONCAT(USER ,"@",HOST) FROM mysql.user;

select + from + where 应用

-- 1. where 配合等值查询
-- 例子： 查询中国(CHN)所有的城市信息
SELECT * FROM city 
WHERE countrycode='CHN'; 
-- 例子： 查询美国(USA)所有的城市名和人口数
SELECT NAME,population FROM city 
WHERE countrycode='USA';

-- 2. where 配合不等值查询 （> < >= <= !=）
-- 例子： 查询世界上人口数据小于100人的城市信息
SELECT * FROM city 
WHERE population<100;

-- 3. where 配合 and  or ,between and ,in 使用 
-- 例子：查询中国，并且人口大于500w的城市信息
SELECT *  FROM city 
WHERE countrycode='CHN' AND population>5000000;

-- 例子： 查询中国或美国的城市信息
SELECT *  FROM city 
WHERE countrycode='CHN' OR countrycode='USA'; 
--- 等价写法
SELECT *  FROM city 
WHERE countrycode IN ('CHN','USA'); 


-- 例子： 查询人口数量在 100w-110w之间
SELECT * FROM city 
WHERE population >=1000000 AND population<=1100000;
--- 等价写法
SELECT * FROM city 
WHERE population BETWEEN 1000000 AND 1100000;

select + from + where + group by + 聚合函数 应用

-- 1. 需求 ：1000人在一个广场上，要求快速统计每个省的学生数量？
-- 1. 站队。分组
-- 2. 数数

-- 2. group by + 聚合函数的执行逻辑
-- 1. 按照group by的列进行排序+去重复
-- 2. 将其他的查询列进行聚合操作
-- 3. 1+2 显示给用户

-- 聚合函数种类：
/*
count() : 统计个数
sum()   : 求和
avg()   ：平均值
max()   ：最大值
min()   ：最小值
group_concat()：列转行：

*/

-- 例子1 ： 统计city表，每个国家的城市个数 
 
SELECT countrycode,COUNT(*)  
FROM city 
GROUP BY countrycode;

-- 例子2 ： 统计city表，中国 每个省的 城市个数 
SELECT district,COUNT(*)  
FROM city 
WHERE countrycode='CHN'
GROUP BY district;

-- 例子3 ： 统计city表，每个国家的总人口数
SELECT countrycode,SUM(population)  
FROM city 
GROUP BY countrycode;

-- 例子4 ： 统计city表，中国 每个省的总人口数
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN'
GROUP BY district;

-- 例子5 ： 统计city表，中国 每个省的 城市个数 ，所有城市名
SELECT district,COUNT(*) ,GROUP_CONCAT(NAME)
FROM city 
WHERE countrycode='CHN'
GROUP BY district;

select + from + where + group by + 聚合函数 + having 应用

-- 例子：  统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
having  SUM(population)>5000000;

select + from + where + group by + 聚合函数 + having +order by

-- 例子：  统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口排序输出。

SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
order by SUM(population);


SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
ORDER BY SUM(population) desc ;

select + from + where + group by + 聚合函数 + having +order by + limit

-- 例子：  统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口从大倒小排序输出
-- 只显示前5名。

SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
ORDER BY SUM(population) desc
limit 5 offset 0 ;

-- 只显示6-10名。
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5 offset 5;

相关文章

  • SQL语句之单表操作

    DDL 应用 作用：数据定义语言1.库定义 2.表定义 DDL语句开发规范 库 ： create database...

  • mysql数据库查询语句

    1.简单的查询基本表的SQL语句 (1)查询语句 (2)查询语句 Student表的删除SQL语句： 选课表的操作...

  • MyBatis注解模式

    MyBatis注解方式实现DAO接口，一般用于简单的sql语句，比如简单条件下的单表操作，复杂的sql使用该方式太...

  • 基于Mybatis的分库实践

    思路 通过Mybatis的Interceptor拦截执行的SQL语句，判断SQL语句操作的表是否需要进行分库，若需...

  • 数据库

    SQL语句增删改查 SQL表关系 SQLiteDatabase操作SQLite数据库 SQLiteOpenHelp...

  • 1.Web 基础知识大纲

    SQL命令行操作数据库操作数据表操作Where条件语句Order排序语句Union合并语句注释语句__注意 '--...

  • MySQL实战宝典 索引调优篇 12 JOIN连接：到底能不能写

    除了单表的查询SQL语句，还有两大类相对复杂的SQL，多表JOIN和子查询语句，这就要在多张表上创建索引，难度相对...

  • Spring Boot集成JPA访问数据库

    终于到了数据库操作部分了，通常我们对于数据库的操作无非是增删改查，对于单表操作而言，SQL语句大都是类似的，同时时...

  • oracle 多表查询优化

    ORACLE中的高速缓冲用于保存查询过的sql语句，单只对单表查询起作用 单表查询优化：写查询语句时尽量统一规范，...

  • 2019-03-27 常用管理SQL语句应用实践（3）

    SQL语句实践 1. DDL语句之管理表 1、建立表 执行student建表语句： 字段类型表得对应列的类型说明：...

网友评论

      本文标题：SQL语句之单表操作

      本文链接：https://www.haomeiwen.com/subject/cmkhqktx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|SQL语句之单表操作|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！