SQL语句

  sql语句

　　参考链接 ： https://www.cnblogs.com/zhangpengnike/p/5545715.html  和 　sql语句大全 ： 参考链接  （推荐这个）

一、常用数据类型：

　　MySQL 中，有三种主要的类型：文本、数字 和 日期/时间类型。  https://www.w3school.com.cn/sql/sql_datatypes.asp 或 链接地址

  • Text 类型：

    • CHAR(size)        ：固定长度的数字、字符串都都可以用。如,手机号、地区代码、地区简称
    • VARCHAR(size)  ：可变长度
    • TEXT
    • BLOB
    • ENUM
    • 等等

  • 数字 类型：

    • int(size)
    • tinyint：【常用】比int更省字节
    • float
    • DOUBLE
    • decimal： 金额
    • money
    • 等等

  • 日期/时间 类型：

    • DATE()：日期。格式：YYYY-MM-DD
    • DATETIME()：日期和时间的组合。      格式：YYYY-MM-DD HH:MM:SS
    • TIMESTAMP()：带时区 的 日期时间。 格式：YYYY-MM-DD HH:MM:SS 【后端回去数据时就会显现】
      同事说（未验证）：sql语句运行更新时，TIMESTAMP，可以设置自动更新时间。而DATETIME不能自动更新（一般通过设置值更新）。
    • TIME()：时间。格式：HH:MM:SS
    • YEAR()：
    • 等等

二、基本sql语句： https://juejin.cn/post/6844903798578610183

  • sql一些语法说明：

    • 在标准 SQL 中，字符串使用的是单引号。 如果字符串本身也包括单引号，则使用两个单引号（注意，不是双引号，字符串中的双引号不需要另外转义）。
      但在其它的数据库中可能存在对 SQL 的扩展，比如在 MySQL 中允许使用单引号和双引号两种。

  • 库相关命令：

    • 创建数据库： 
      CREATE DATABASE datatype;
USE datatype;
    • 查看当前数据库所有的表（这个直接使用navicat的查看功能，也很方便的。选择查看信息，表相关的信息都可以看到） 
      SHOW TABLES;

      上面的命令虽然可以查询所有的表名，但是没什么用。我们更希望查询出 表名 和 注释。让我们知道每个表的作用。

      -- 查询  表名 和 表注释

SELECT table_name, table_comment
FROM information_schema.TABLES 
WHERE table_schema='jeecg-boot'

  • 表相关命令：

    • 创建数据表： https://www.w3school.com.cn/sql/sql_create.asp 
      -- 属性名 和 数据类型是必填，其它的非必填

CREATE TABLE 表名
( 
属性名 数据类型 [完整性约束条件] COMMENT '字段注释',
属性名 数据类型 [完整性约束条件] COMMENT '字段注释',
.
属性名 数据表格 [完整性约束条件]  COMMENT '字段注释'
) COMMENT = '表的注释';
      -- 示例： 创建测试表
CREATE TABLE 'test_number' 
(
'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'number' int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数字',
 PRIMARY KEY ('id')
)

      根据已有的表创建新表：

      --根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

      说明一点：真实的开发中，数据库 和 表  都是 先创建好的。一般会在 管上先把表创建好。

    • 修改表的信息：https://blog.csdn.net/spring_model/article/details/50435783 或 https://juejin.cn/post/6954305135740715022 
      -- 新增字段
-- 语法格式： ALTER TABLE 表名 ADD 字段名 数据类型
ALTER TABLE da_club_user_model ADD car_gear varchar(10)

-- 修改字段名
-- 语法格式：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
ALTER TABLE da_club_user_model CHANGE rzSpec preSpec VARCHAR(100)
    • 查看 字段注释 或 表结构信息 。下面两个命令，查询结果集是一样的。 
      show full fields from test1; -- 个人喜欢用这个命令，fields 比较符合 语义
-- 或 show full columns from test1;

  • 基础语句：增删改查

    • 新增数据 
      -- INSERT INTO <表名> (属性,...) VALUES (值,...);

INSERT INTO 
type_number(type,byte,range_singed,range_unsinged,info) 
VALUES
('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整数');

      你也可以一次，插入多条数据

      INSERT INTO 
type_number(type,byte,range_singed,range_unsinged,info) 
VALUES
('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整数'),
('SMALLINT',2,'-2¹⁶ ~ 2¹⁶-1','0 ~ 2¹⁶-1','小整数'),
('MEDIUMINT',3,'-2²⁴ ~ 2²⁴-1','0 ~ 2²⁴-1','中等整数'),
('INT',4,'-2³² ~ 2³²-1','0 ~ 2³²-1','标准整数'),
('BIGINT',8,'-2⁶⁴ ~ 2⁶⁴-1','0 ~ 2⁶⁴-1','大整数');
    • 删除数据 
      -- DELETE FROM <表名> WHERE 条件;

DELETE FROM type_number 
WHERE type = 'TINYINT';
    • 更新数据 
      -- UPDATE <表名> SET 属性 = 值,... WHERE 条件;

UPDATE type_number 
SET 
info='微型整数'
WHERE type = 'TINYINT';
    • 查询数据 
      -- SELECT 属性,... FROM <表名> WHERE 条件;

SELECT 
type,range_unsinged 
FROM type_number 
WHERE byte>=4;

  • 查询 关键字

    • as 关键字：为列设置别名，即查询结果的字段名改变了。 
      SELECT COUNT(*) as total FROM users  -- 给 COUNT(*)设置别名，total

      也可以给普通字段设置别名

      SELECT name as userName, email as el FROM users

三、where子句限定方式：     https://blog.csdn.net/yuanlingGeGe/article/details/89503508

  • 比较运算符：where 子句中，通过 比较 某个字段是否符合条件，进而查询。 
    select * from students where age>18;
  • IS NULL 运算符：专门用来判断是否为 NULL 的，sql中无法用比较运算符判断字段值是否为NULL。
  • IN 操作符：IN 操作符允许我们在 WHERE 子句中规定多个值。https://www.w3school.com.cn/sql/sql_in.asp  
    SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
  • 模糊查询：即 关键字搜索
    • like： like关键字表示模糊查询
    • rlike：使用正则匹配，进行模糊查询
    • 模糊查询中的模糊表示语法： 
      -- 模糊语法：
–- % 替换1个或者多个
–- _ 替换1个
–- 查询姓名中 以 “小” 开始的名字

-- 查询姓名中 有"小" 所有的名字
select name from students where name like "%小%";

-- 查询有3个字的名字
select name from students where name like "___";

-- 查询至少有2个字的名字
select name from students where name like "__%";
  • 使用正则条件查询： 
    SELECT 
    productname
FROM
    products
WHERE
    productname REGEXP '^(A|B|C)'
  • 逻辑运算符：通过使用逻辑运算符，可以将多个查询条件进行组合。
    • NOT 运算符
    • AND 运算符和 OR 运算符 
      SELECT product_name, purchase_price
FROM Product
WHERE product_type = ' 厨房用具 '
AND sale_price >= 3000;

四、子查询：https://www.w3cschool.cn/sql/zjdc1oz3.html

子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。
  • 使用子查询必须遵循以下几个规则
    • 子查询必须 括在圆括号中。
    • 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
    • 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
    • 回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
  • 子查询的功能：https://www.jianshu.com/p/0b48c6e0a472
    • 使用子查询进行过滤：回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。用于 WHERE 子句的 IN 操作符中进行过滤。
      主要在多表联查中过滤数据
      SELECT
    cust_id 
FROM
    Orders 
WHERE
    order_num IN ( SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01' );
    • 使用子查询创建计算字段：子查询的另一个使用场景为创建计算字段。 
      SELECT
    cust_name,
    cust_state,
    ( SELECT COUNT( * ) FROM Orders WHERE Orders.cust_id = Customers.cust_id ) AS orders 
FROM
    Customers;

      上述 SQL 对 Customers 表中每个顾客返回三列：cust_name cust_state 和 orders 。其中 orders 为子查询创建的计算字段。

    • 子查询计算：https://www.5axxw.com/questions/content/2dpoth   
      SELECT
  (SELECT SUM(`仓库变动`) FROM bank_and_security WHERE `业务名称` REGEXP '入库'
    AND `业务状态` REGEXP '成功') -
  (SELECT SUM(`库存变动`) FROM bank_and_security WHERE `业务名称` REGEXP '出库'
    AND `业务状态` REGEXP '成功')
AS '总数据'

五、对查询结果集处理：

  • 排序 ：根据条件查询出来的数据，再排序返回。 
    -- order by 字段
-- asc从小到大排列，即升序
-- desc从大到小排序，即降序

-- 查询年龄在18到34岁之间的男性，按照年龄从小到到排序
select * from students where (age between 18 and 34) and gender=1;
select * from students where (age between 18 and 34) and gender=1 order by age;
select * from students where (age between 18 and 34) and gender=1 order by age asc;
  • 聚合函数 ：总数、最值、求和、平均值、四舍五入
      • 总数
      • 最大值 / 最小值
      • 求和
      • 平均值
      • 四舍五入
  • 分组：分组的目的是，将数据进行一组一组分割处理。经常与聚合函数一起使用。 
    SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
  • 分页： 
    -- 查询前5个数据
select * from students limit 0,5;

-- 查询id6-10（包含）的书序
select * from students limit 5,5;


-- 每页显示2个，第1个页面
select * from students limit 0,2;

-- 每页显示2个，第2个页面
select * from students limit 2,2;

-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
-- 失败select * from students limit 2*(6-1),2;
-- 失败select * from students limit 10,2 order by age asc;
select * from students order by age asc limit 10,2;

六、多表查询    https://zhuanlan.zhihu.com/p/68136613

  • 内连接：
    这是最常用的，获取两个表中指定字段满足匹配关系的记录。 
    --查询每个学生的学号、姓名、籍贯、年龄、专业、班级
--涉及到student和major两张表，用共有字段“学号”为连接字段

--推荐写法：--省去了INNER，直接写JOIN，与INNER JOIN没有区别
SELECT A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
FROM student A
JOIN major B  
ON  A.学号=B.学号   -- 用共有字段 学号 来连接
  • 左连接：
    获取左表中的所有记录，即使在右表没有对应匹配的记录。 
    --左连接：显示左表student所有记录，如右表中没有与之
--匹配的项则以NULL值代替。
--【即以左边的记录条数要完成存在；右表没有对象的记录时，相应的字段值设为null】

SELECT A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
FROM student A LEFT JOIN major B  
ON  A.学号=B.学号
  • 右连接：
    用于获取右表中的所有记录，即使左表没有对应匹配的记录。 
    --右连接：显示右表major所有记录，如左表中没有与之
--匹配的项则以NULL值代替。

SELECT A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
FROM student A RIGHT JOIN major B  
ON  A.学号=B.学号
  • 完全连接：
    返回两个表中的所有行。 
    --完全连接：显示两张表的并集，如果其中一张表的记录
--在另一张表中没有匹配的行，则对应的数据项填充NULL

SELECT A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
FROM student A FULL JOIN major B  
ON  A.学号=B.学号

七、事务：

　　　https://juejin.cn/post/7085523252189069343?share_token=a6a624b6-7726-442a-b2a3-f0723bb66bf5#heading-11

事务的主要作用是保证一组数据库操作都成功执行，不然如果有一步数据操作出错了会导致不完整性。举个例子，当你删除一个用户时，同时需要用户的文章信息、登录数据等等时，要确保删除用户时的其他删除操作也必须成功。事务具有如下几个重要特征：

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert，update，delete 语句

使用用法主要有2步，一是使用BEGIN;开始一个事务；二是使用ROLLBACK;可以回滚事物或者使用COMMIT;进行事务确认。下面我们看具体的使用示例：

# 开始事务
begin;

# 一些数据库操作
# 此时所有数据操作并未被真正写入数据库
insert into books (bookname, type, author) values ('深入浅出Vue.js', 123, '刘博文');
insert into books (bookname, type, author) values ('new book', 123, 'make');
insert into books (bookname, type, author) values ('new book2', 123, 'make');

# 事物确认，此时所有数据才被全部写入
commit;

八、关联表的设计   https://blog.csdn.net/lilygg/article/details/114916481

  • 一对一关系：
    关联的表上增加一个 被关联表主键的字段就可以了。

  • 一对多关系：

  • 多对多关系：
    多对多关系，才需要第三张表，对这两张表进行关联

 

posted @ 2018-07-03 17:11  吴飞ff  阅读(256)  评论(0)  编辑  收藏  举报