草庐IT

SQL之建表

安利“一个处女座的程序员” 2024-01-21 原文

1、知识点介绍：

表的创建、修改与删除：

  • 1.1 直接创建表：

  • CREATE TABLE

    [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过

    (column_name1 data_type1 -- 列名和类型必选

      [ PRIMARY KEY -- 可选的约束，主键

       | FOREIGN KEY -- 外键，引用其他表的键值

       | AUTO_INCREMENT -- 自增ID

       | COMMENT comment -- 列注释（评论）

       | DEFAULT default_value -- 默认值

       | UNIQUE -- 唯一性约束，不允许两条记录该列值相同

       | NOT NULL -- 该列非空

      ], ...

    ) [CHARACTER SET charset] -- 字符集编码

    [COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

  • 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

  • 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

  • 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

1

2

3

4

5

6

7

8

{ ADD COLUMN <列名> <类型>  -- 增加列

 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型

 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值

 | MODIFY COLUMN <列名> <类型> -- 修改列类型

 | DROP COLUMN <列名> -- 删除列

 | RENAME TO <新表名> -- 修改表名

 | CHARACTER SET <字符集名> -- 修改字符集

 | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

  • 3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

细节剖析：

  • 自增ID：AUTO_INCREMENT；
  • 设置主键：PRIMARY KEY；
  • 唯一性约束：UNIQUE
  • 非空约束：NOT NULL
  • 设置默认值：DEFAULT 0
  • 当前时间戳：CURRENT_TIMESTAMP
  • 评论/注释：COMMENT
  • 如果该表已创建过，正常返回：IF NOT EXISTS

2、案例

（1）现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。

原来的用户信息表：

FiledTypeNullKeyDefaultExtraComment
idint(11)NOPRI(NULL)auto_increment自增ID
uidint(11)NOUNI(NULL)用户ID
nick_namevarchar(64)YES(NULL)昵称
achievementint(11)YES0成就值
levelint(11)YES(NULL)用户等级
jobvarchar(32)YES(NULL)职业方向
register_timedatetimeYESCURRENT_TIMESTAMP注册时间

作为数据分析师，请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。

--T1:
CREATE TABLE IF NOT EXISTS user_info_vip
(id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
 uid int UNIQUE NOT NULL COMMENT '用户ID',
 nick_name	varchar(64) COMMENT '昵称',
 achievement int DEFAULT 0 COMMENT '成就值',
 `level` int COMMENT '用户等级',
 job varchar(32) COMMENT '职业方向',
 register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间')
 CHARACTER SET utf8 COLLATE utf8_general_ci;

--T2:
create table user_info_vip(
id int(11) not null primary key auto_increment comment '自增ID',
uid int(11) not null unique key comment '用户ID',
nick_name varchar(64) comment '昵称',
achievement int(11) default 0 comment '成就值',
level int(11) comment '用户等级',
job varchar(32) comment '职业方向',
register_time datetime default current_timestamp comment '注册时间'
) default charset=utf8

你应该返回的输出如下表格所示，请写出建表语句将表格中所有限制和说明记录到表里。

FiledTypeNullKeyDefaultExtraComment
idint(11)NOPRIauto_increment自增ID
uidint(11)NOUNI用户ID
nick_namevarchar(64)YES昵称
achievementint(11)YES0成就值
levelint(11)YES用户等级
jobvarchar(32)YES职业方向
register_timedatetimeYESCURRENT_TIMESTAMP注册时间

备注：
1.后台会通过 SHOW FULL FIELDS FROM user_info_vip 语句，来对比输出结果

2.如果该表已经被其他分析师创建过了，正常返回即可。

SQL建表tdmargin-lefttext-align数据库

有关SQL之建表的更多相关文章

  1. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  2. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  3. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  4. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  5. ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2

    我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。 最佳答案

  6. sql - Rails:使用 Postgres 创建对象时重复 ActiveRecord::RecordNotUnique？ - 2

    我正在使用Rails4应用程序，它需要创建大量对象以响应来自另一个系统的事件。当我调用create!时，主键列上出现非常频繁的ActiveRecord::RecordNotUnique错误(由PG::UniqueViolation引起)我的模型之一。我在SO上找到了其他答案，建议挽救异常并调用retry:beginTableName.create!(data:'here')rescueActiveRecord::RecordNotUnique=>eife.message.include?'_pkey'#Onlyretryprimarykeyviolationslog.warn"Retr

  7. ruby-on-rails - Ruby 和 SQL 中的重复业务逻辑 - 2

    我有一个PORO(普通旧Ruby对象)来处理一些业务逻辑。它接收一个ActiveRecord对象并对其进行分类。为了简单起见，以下面为例:classClassificatorSTATES={1=>"Positive",2=>"Neutral",3=>"Negative"}definitializer(item)@item=itemenddefnameSTATES.fetch(state_id)endprivatedefstate_idreturn1if@item.value>0return2if@item.value==0return3if@item.value但是，我还想根据这些st

  8. sql - ActiveRecord 的意外行为包括 - 2

    我正在使用ARincludes在对象User和Building之间执行LEFTOUTERJOIN的方法，其中User可能有也可能没有Building关联:users=User.includes(:building).references(:buildings)因为我正在使用references，任何关联的Building对象都将被预先加载。我的期望是我随后能够遍历用户列表，并检查用户是否有与其关联的建筑物而不会触发额外的查询，但实际上每当我尝试访问建筑物属性时我都会看到对于没有建筑物的用户，AR会进行另一个SQL调用以尝试检索该建筑物(尽管在后续尝试中它只会返回nil)。这些查询显然是

  9. sql - 如何查询具有 3 个标签的事件？ - 2

    我有以下模型:activity.rbtag.rbtagging.rb标签是事件和标签的连接模型。我想搜索具有2个或更多标签的事件。我如何在Rails中执行此操作？例如:我有tag1=Christmas，tag2=Florida，tag3=John如果存在，我想找到tag1、tag2和tag3存在的Activity。[编辑]我最终做了什么:tags=[tag1,tag2,tag3]activities=[]tags.eachdo|tag|activities如果任何组值的大小等于tags.size，则该事件包含所有标签。 最佳答案 如

  10. ruby-on-rails - 如何(大量)减少 Rails 应用程序中的 SQL 查询数量？ - 2

    在我的Rails应用程序中，我有users，它可以有许多invoices，而invoices又可以有许多payments。现在在dashboardView中，我想总结一个user曾经收到的所有payments，按年、季度或月。付款也分割为毛额、Netty和税额。user.rb:classUser:items).allpayments_with_invoice.select{|x|range.cover?x.date}.sum(&:"#{kind}_amount")endend发票.rb:classInvoicepayment.rb:classPaymentdashboards_cont

随机推荐