精华内容
下载资源
问答
  • sql建表语句

    千次阅读 2018-12-26 15:34:34
    select * from t_goods where goodsname like '%肉%' drop table t_shopping create table t_shopping ( shoppingguid uniqueidentifier not null default newid(), goodsguid uniqueidentifier not null default...
    use master
    go
    drop database mydata
    go
    create database mydata2
    go
    use mydata
    go
    create table t_user
    (
    	userguid uniqueidentifier not null default newid(),
    	username nvarchar(max) not null default N'',
    	userpassword nvarchar(max) not null default N'',
    	createdatetime smalldatetime not null default getdate(),
    	lastalterdatetime smalldatetime not null default getdate(),
    	a1 bit not null default 0,
    	a2 bit not null default 0,
    	a3 bit not null default 0,
    	a4 bit not null default 0,
    	b1 bit not null default 0, 
    	b2 bit not null default 0,
    	b3 bit not null default 0,
    	b4 bit not null default 0,
    	c1 bit not null default 0,
    	c2 bit not null default 0,
    	c3 bit not null default 0,
    	c4 bit not null default 0,
    	d1 bit not null default 0,
    	d2 bit not null default 0,
    	d3 bit not null default 0,
    	d4 bit not null default 0,
    	primary key (userguid),
    )
    insert into t_user (username, userpassword) values (N'张三', N'zs123')
    insert into t_user (username, userpassword) values (N'里斯', N'as645df')
    insert into t_user (username, userpassword) values (N'aaa', N'asdsdf')
    insert into t_user (username, userpassword) values (N'a', N'b')
    select * from t_user where username = 'AAA'
    select * from t_user
    
    create table t_admin
    (
    	adminguid uniqueidentifier not null default newid(),
    	adminname nvarchar(max) not null default N'',
    	adminpassword nvarchar(max) not null default N'',
    	createdatetime smalldatetime not null default getdate(),
    	lastaltertime smalldatetime not null default getdate(),
    	primary key (adminguid)
    )
    insert into t_admin (adminname, adminpassword) values (N'admin', N'admin')
    select * from t_admin
    
    drop table t_goods
    create table t_goods(
    	goodsguid uniqueidentifier not null default newid(),
    	goodsname nvarchar(50) not null default N'',
    	goodscode nvarchar(50) not null default N'',
    	goodsunit nvarchar(10) not null default N'',
    	goodscate nvarchar(10) not null default N'',
    	goodsprice money not null default 0,
    	goodsinserttime datetime not null default getdate(),
    	goodsupdatetime datetime not null default getdate(),
    	primary key (goodsguid)
    )
    
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('猪肉', '001', '斤', '肉类', 8.35);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('羊肉', '002', '斤', '肉类', 28.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('牛肉', '003', '斤', '肉类', 22);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('驴肉', '004', '斤', '肉类', 32);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('狗肉', '005', '斤', '肉类', 40);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('猫肉', '006', '斤', '肉类', 10);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('兔肉', '007', '斤', '肉类', 15);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('鸡肉', '008', '斤', '肉类', 5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('鸭肉', '009', '斤', '肉类', 8);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('鹅肉', '010', '斤', '肉类', 10);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('菠菜', '011', '斤', '蔬菜类', 5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('白菜', '012', '斤', '蔬菜类', 5.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('芹菜', '013', '斤', '蔬菜类', 6.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('油菜', '014', '斤', '蔬菜类', 0.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('韭菜', '015', '斤', '蔬菜类', 9.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('包菜', '016', '斤', '蔬菜类', 3.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('生菜', '017', '斤', '蔬菜类', 5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('萝卜', '018', '斤', '蔬菜类', 0.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('茭瓜', '019', '斤', '蔬菜类', 1.5);
    insert into t_goods (goodsname,goodscode,goodsunit,goodscate,goodsPrice) values ('冬瓜', '020', '斤', '蔬菜类', 2.5);
    select * from t_goods where goodsname like '%肉%'
    
    drop table t_shopping
    create table t_shopping
    (
    	shoppingguid uniqueidentifier not null default newid(),
    	goodsguid uniqueidentifier not null default newid(),
    	goodsname nvarchar(50) not null default N'',
    	goodscode nvarchar(50) not null default N'',
    	goodsunit nvarchar(10) not null default N'',
    	goodscate nvarchar(10) not null default N'',
    	goodsprice money not null default 0,
    	shoppingshuliang decimal(12,4) not null default 0, 
    	goodsinserttime datetime not null default getdate(),
    	goodsupdatetime datetime not null default getdate(),
    	primary key(shoppingguid)
    )
    select * from t_shopping
    
    drop table t_jiaohuo
    create table t_jiaohuo
    (
    	jiaohuoguid uniqueidentifier not null default newid(),
    	jiaohuoshuliang decimal(12,2) not null default 0,
    	jiaohuoinserttime datetime not null default getdate(),
    	jiaohuoupdatetime datetime not null default getdate(),
    	goodsguid uniqueidentifier not null default newid(),
    	primary key (jiaohuoguid),
    	FOREIGN KEY (goodsguid) REFERENCES t_goods(goodsguid)
    )
    select * from  t_jiaohuo
    
    drop view v_goods_jiaohuo;
    create view v_goods_jiaohuo as select goodsname 产品名称, t_goods.goodscode 产品代码, goodsunit 计算单位, goodscate 分类, goodsprice 单价, jiaohuoshuliang 叫货数量, goodsprice * jiaohuoshuliang 小计, jiaohuoupdatetime 最后修改时间, jiaohuoguid from t_goods, t_jiaohuo where t_goods.goodsguid=t_jiaohuo.goodsguid
    select * from t_goods order by goodsupdatetime desc
    select * from v_goods_jiaohuo
    
    select goodsname, t_goods.goodscode, goodsunit, goodscate, goodsprice, jiaohuoshuliang, goodsprice * jiaohuoshuliang total, jiaohuoupdatetime, jiaohuoguid from t_goods, t_jiaohuo where t_goods.goodsguid=t_jiaohuo.goodsguid
    
    select sum(total) from (select goodsname, t_goods.goodscode, goodsunit, goodscate, goodsprice, jiaohuoshuliang, goodsprice * jiaohuoshuliang total, jiaohuoupdatetime, jiaohuoguid from t_goods, t_jiaohuo where t_goods.goodsguid=t_jiaohuo.goodsguid and goodscate = '肉类')t
    
    展开全文
  • HiveSQL建表-查询

    千次阅读 2020-08-19 10:21:46
    化的数据文件映射为一张数据库,并提供完整的SQL查询功能,可以将SQL语句转换为MapReduce任务进行运行,通过自己的SQL 去查询分析需 要的内容,这套SQL 简称Hive SQL,使不熟悉mapreduce 的用户很方便的利用SQL ...

    Hive 是基于Hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能,可以将SQL语句转换为MapReduce任务进行运行,通过自己的SQL 去查询分析需要的内容,这套SQL 简称Hive SQL,使不熟悉mapreduce 的用户很方便的利用SQL 语言查询,汇总,分析数据。而mapreduce开发人员可以把己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。
          它与关系型数据库的SQL 略有不同,但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机online)事务处理,也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。

    HIVE的特点:可伸缩(在Hadoop的集群上动态的添加设备),可扩展,容错,输入格式的松散耦合。

    Hive 的官方文档中对查询语言有了很详细的描述,请参考:http://wiki.apache.org/hadoop/Hive/LanguageManual ,本文的内容大部分翻译自该页面,期间加入了一些在使用过程中需要注意到的事项。

    1.  DDL 操作

    •建表

    •删除表

    •修改表结构

    •创建/删除视图

    •创建数据库

    •显示命令

    建表:

    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
      [(col_name data_type [COMMENT col_comment], ...)] 
      [COMMENT table_comment] 
      [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
      [CLUSTERED BY (col_name, col_name, ...) 
      [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
      [ROW FORMAT row_format] 
      [STORED AS file_format] 
      [LOCATION hdfs_path]

    •CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常

    •EXTERNAL 关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)

    •LIKE 允许用户复制现有的表结构,但是不复制数据

    •COMMENT可以为表与字段增加描述

    •ROW FORMAT

     DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

             用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。

    •STORED AS

                SEQUENCEFILE

                | TEXTFILE

                | RCFILE    

                | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

           如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCE 。

    创建简单表:

    hive> CREATE TABLE pokes (foo INT, bar STRING);

    创建外部表:

    CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,

         page_url STRING, referrer_url STRING,

         ip STRING COMMENT 'IP Address of the User',

         country STRING COMMENT 'country of origination')

     COMMENT 'This is the staging page view table'

     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'

     STORED AS TEXTFILE

     LOCATION '<hdfs_location>';

    建分区表

    CREATE TABLE par_table(viewTime INT, userid BIGINT,

         page_url STRING, referrer_url STRING,

         ip STRING COMMENT 'IP Address of the User')

     COMMENT 'This is the page view table'

     PARTITIONED BY(date STRING, pos STRING)

    ROW FORMAT DELIMITED ‘\t’

       FIELDS TERMINATED BY '\n'

    STORED AS SEQUENCEFILE;

    建Bucket表

    CREATE TABLE par_table(viewTime INT, userid BIGINT,

         page_url STRING, referrer_url STRING,

         ip STRING COMMENT 'IP Address of the User')

     COMMENT 'This is the page view table'

     PARTITIONED BY(date STRING, pos STRING)

     CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

     ROW FORMAT DELIMITED ‘\t’

       FIELDS TERMINATED BY '\n'

    STORED AS SEQUENCEFILE;

    创建表并创建索引字段ds

    hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

    复制一个空表

    CREATE TABLE empty_key_value_store

    LIKE key_value_store;

    例子

    create table  user_info (user_id int, cid string, ckid string, username string) 

    row format delimited 

    fields terminated by '\t'

     lines terminated by '\n';

    导入数据表的数据格式是:字段之间是tab键分割,行之间是断行。

    文件内容格式:

    100636  100890  c5c86f4cddc15eb7        yyyvybtvt
    100612  100865  97cc70d411c18b6f        gyvcycy
    100078  100087  ecd6026a15ffddf5        qa000100

    显示所有表:

    hive> SHOW TABLES;

    按正条件(正则表达式)显示表,

    hive> SHOW TABLES '.*s';

    修改表结构

    •增加分区、删除分区

    •重命名表

    •修改列的名字、类型、位置、注释

    •增加/更新列

    •增加表的元数据信息

    表添加一列 :

    hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

    添加一列并增加列字段注释

    hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

    更改表名:

    hive> ALTER TABLE events RENAME TO 3koobecaf;

    删除列:

    hive> DROP TABLE pokes;

    增加、删除分区

    •增加

    ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

          partition_spec:

      : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

    •删除

    ALTER TABLE table_name DROP partition_spec, partition_spec,...

    重命名表

    •ALTER TABLE table_name RENAME TO new_table_name 

    修改列的名字、类型、位置、注释:

    •ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

    •这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

    表添加一列 :

    hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

    添加一列并增加列字段注释

    hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

    增加/更新列

    •ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)  

    • ADD是代表新增一字段,字段位置在所有列后面(partition列前)

         REPLACE则是表示替换表中所有字段。

    增加表的元数据信息

    •ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:

             :[property_name = property_value…..]

    •用户可以用这个命令向表中增加metadata

    改变表文件格式与组织

    •ALTER TABLE table_name SET FILEFORMAT file_format

    •ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS

    •这个命令修改了表的物理存储属性

    创建/删除视图

    •CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT

    •增加视图

    •如果没有提供表名,视图列的名字将由定义的SELECT表达式自动生成

    •如果修改基本表的属性,视图中不会体现,无效查询将会失败

    •视图是只读的,不能用LOAD/INSERT/ALTER

    •DROP VIEW view_name

    •删除视图

    创建数据库

    •CREATE DATABASE name

    显示命令

    •show tables;

    •show databases;

    •show partitions ;

    •show functions

    •describe extended table_name dot col_name

    2.  DML 操作:元数据存储

     

         hive不支持用insert语句一条一条的进行插入操作,也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。
     

    DML包括:INSERT插入、UPDATE更新、DELETE删除

     

    •向数据表内加载文件

    •将查询结果插入到Hive表中

    •0.8新特性 insert into

     

    向数据表内加载文件

    •LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

    •Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置。

    •filepath

    •相对路径,例如:project/data1

    •绝对路径,例如: /user/hive/project/data1

    •包含模式的完整 URI,例如:hdfs://namenode:9000/user/hive/project/data1

    例如:

    hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

    加载本地数据,同时给定分区信息

    •加载的目标可以是一个表或者分区。如果表包含分区,必须指定每一个分区的分区名

    •filepath 可以引用一个文件(这种情况下,Hive 会将文件移动到表所对应的目录中)或者是一个目录(在这种情况下,Hive 会将目录中的所有文件移动至表所对应的目录中)

    LOCAL关键字

    •指定了LOCAL,即本地

    •load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径,则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI,比如:file:///user/hive/project/data1.

    •load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置

    例如:加载本地数据,同时给定分区信息:

    hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

    • 没有指定LOCAL

             如果 filepath 指向的是一个完整的 URI,hive 会直接使用这个 URI。 否则

    •如果没有指定 schema 或者 authority,Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority,fs.default.name 指定了 Namenode 的 URI

    •如果路径不是绝对的,Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table (或者 partition)所指定的路径中

    加载DFS数据 ,同时给定分区信息:

    hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
    The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.

    OVERWRITE

    •指定了OVERWRITE

    •目标表(或者分区)中的内容(如果有)会被删除,然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

    •如果目标表(分区)已经有一个文件,并且文件名和 filepath 中的文件名冲突,那么现有的文件会被新文件所替代。

    将查询结果插入Hive表

    •将查询结果插入Hive表

    •将查询结果写入HDFS文件系统

    •基本模式

         INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

    •多插入模式

     FROM from_statement

    INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

    [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

    •自动分区模式

     INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

    将查询结果写入HDFS文件系统

    •INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

            FROM from_statement

            INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

         [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

    •数据写入文件系统时进行文本序列化,且每列用^A 来区分,\n换行

    INSERT INTO 

    •INSERT INTO  TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

    3.  DQL 操作:数据查询SQL

    SQL操作

    •基本的Select 操作

    •基于Partition的查询

    •Join

    3.1 基本的Select 操作

    SELECT [ALL | DISTINCT] select_expr, select_expr, ...

    FROM table_reference

    [WHERE where_condition]

    [GROUP BY col_list [HAVING condition]]

    [   CLUSTER BY col_list

      | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

    ]

    [LIMIT number]

    •使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL,表示查询所有记录。DISTINCT表示去掉重复的记录

    •Where 条件

    •类似我们传统SQL的where 条件

    •目前支持 AND,OR ,0.9版本支持between

    •IN, NOT IN

    •不支持EXIST ,NOT EXIST

    ORDER BY与SORT BY的不同

    •ORDER BY 全局排序,只有一个Reduce任务

    •SORT BY 只在本机做排序

     

    Limit

    •Limit 可以限制查询的记录数

    SELECT * FROM t1 LIMIT 5

    •实现Top k 查询

    •下面的查询语句查询销售记录最大的 5 个销售代表。

    SET mapred.reduce.tasks = 1 
      SELECT * FROM test SORT BY amount DESC LIMIT 5

    •REGEX Column Specification

    SELECT 语句可以使用正则表达式做列选择,下面的语句查询除了 ds 和 hr 之外的所有列:

    SELECT `(ds|hr)?+.+` FROM test

    例如:

    按先件查询

    hive> SELECT a.foo FROM invites a WHERE a.ds='<DATE>';

    将查询数据输出至目录:

    hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';

    将查询结果输出至本地目录:

    hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

    选择所有列到本地目录 :

    hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
    hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
    hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
    hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;
    hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>';
    hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;
    hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

    将一个表的统计结果插入另一个表中:

    hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;
    hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;
    JOIN
    hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

    将多表数据插入到同一表中:

    FROM src
    INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
    INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
    INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
    INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

    将文件流直接插入文件:

    hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
    This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce side (please see the Hive Tutorial or examples)

    3.2 基于Partition的查询

    •一般 SELECT 查询会扫描整个表,使用 PARTITIONED BY 子句建表,查询就可以利用分区剪枝(input pruning)的特性

    •Hive 当前的实现是,只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中,才会启用分区剪枝

    3.3 Join

    Syntax

    join_table: 
       table_reference JOIN table_factor [join_condition] 
      | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition 
      | table_reference LEFT SEMI JOIN table_reference join_condition 

    table_reference: 
        table_factor 
      | join_table 

    table_factor: 
        tbl_name [alias] 
      | table_subquery alias 
      | ( table_references ) 

    join_condition: 
        ON equality_expression ( AND equality_expression )* 

    equality_expression: 
        expression = expression

    •Hive 只支持等值连接(equality joins)、外连接(outer joins)和(left semi joins)。Hive 不支持所有非等值的连接,因为非等值连接非常难转化到 map/reduce 任务

    •LEFT,RIGHT和FULL OUTER关键字用于处理join中空记录的情况

    •LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

    •join 时,每次 map/reduce 任务的逻辑是这样的:reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统

    •实践中,应该把最大的那个表写在最后

    join 查询时,需要注意几个关键点

    •只支持等值join

    •SELECT a.* FROM a JOIN b ON (a.id = b.id)

    •SELECT a.* FROM a JOIN b 
        ON (a.id = b.id AND a.department = b.department)

    •可以 join 多于 2 个表,例如

      SELECT a.val, b.val, c.val FROM a JOIN b 
        ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

    •如果join中多个表的 join key 是同一个,则 join 会被转化为单个 map/reduce 任务

    LEFT,RIGHT和FULL OUTER

    •例子

    •SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

    •如果你想限制 join 的输出,应该在 WHERE 子句中写过滤条件——或是在 join 子句中写

    •容易混淆的问题是表分区的情况

    • SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key) 
      WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘

    •如果 d 表中找不到对应 c 表的记录,d 表的所有列都会列出 NULL,包括 ds 列。也就是说,join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话,LEFT OUTER 就使得查询结果与 WHERE 子句无关

    •解决办法

    •SELECT c.val, d.val FROM c LEFT OUTER JOIN d 
      ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

    LEFT SEMI JOIN

    •LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行

    •SELECT a.key, a.value 
      FROM a 
      WHERE a.key in 
       (SELECT b.key 
        FROM B);

           可以被重写为:

          SELECT a.key, a.val 
       FROM a LEFT SEMI JOIN b on (a.key = b.key)

    UNION ALL

    •用来合并多个select的查询结果,需要保证select中字段须一致

    •select_statement UNION ALL select_statement UNION ALL select_statement ...

    4.  从SQL到HiveQL应转变的习惯

    1、Hive不支持等值连接 

    •SQL中对两表内联可以写成:

    •select * from dual a,dual b where a.key = b.key;

    •Hive中应为

    •select * from dual a join dual b on a.key = b.key; 

    而不是传统的格式:

    SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

    2、分号字符

    •分号是SQL语句结束标记,在HiveQL中也是,但是在HiveQL中,对分号的识别没有那么智慧,例如:

    •select concat(key,concat(';',key)) from dual;

    •但HiveQL在解析语句时提示:

            FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification

    •解决的办法是,使用分号的八进制的ASCII码进行转义,那么上述语句应写成:

    •select concat(key,concat('\073',key)) from dual;

    3、IS [NOT] NULL

    •SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

    4、Hive不支持将数据插入现有的表或分区中,

    仅支持覆盖重写整个表,示例如下:

    INSERT OVERWRITE TABLE t1
    SELECT * FROM t2;

    4、hive不支持INSERT INTO, UPDATE, DELETE操作

        这样的话,就不要很复杂的锁机制来读写数据。
         INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

    5hive支持嵌入mapreduce程序,来处理复杂的逻辑

    如:

    FROM (
    MAP doctext USING 'python wc_mapper.py' AS (word, cnt)
    FROM docs
    CLUSTER BY word
    ) a
    REDUCE word, cnt USING 'python wc_reduce.py';
    --doctext: 是输入

    --word, cnt: 是map程序的输出

    --CLUSTER BY: 将wordhash后,又作为reduce程序的输入。

    此外,map程序、reduce程序可以单独使用:

    FROM (
    MAP doctext USING 'python wc_mapper.py' AS (word, cnt)
    FROM docs
    CLUSTER BY word
    ) a
    REDUCE word, cnt USING 'python wc_reduce.py';

    6、hive支持将转换后的数据直接写入不同的表,还能写入分区、hdfs和本地目录。

    这样能免除多次扫描输入表的开销。

    FROM t1
    INSERT OVERWRITE TABLE t2
    SELECT t3.c2, count(1)
    FROM t3
    WHERE t3.c1 <= 20
    GROUP BY t3.c2
     
    INSERT OVERWRITE DIRECTORY '/output_dir'
    SELECT t3.c2, avg(t3.c1)
    FROM t3
    WHERE t3.c1 > 20 AND t3.c1 <= 30
    GROUP BY t3.c2
     
    INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'
    SELECT t3.c2, sum(t3.c1)
    FROM t3
    WHERE t3.c1 > 30
    GROUP BY t3.c2;

    实际示例

    创建一个表

    CREATE TABLE u_data (
    userid INT,
    movieid INT,
    rating INT,
    unixtime STRING)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '/t'
    STORED AS TEXTFILE;


    下载示例数据文件,并解压缩
    wget http://www.grouplens.org/system/files/ml-data.tar__0.gz
    tar xvzf ml-data.tar__0.gz

    加载数据到表中:

    LOAD DATA LOCAL INPATH 'ml-data/u.data'
    OVERWRITE INTO TABLE u_data;

    统计数据总量:

    SELECT COUNT(1) FROM u_data;

    现在做一些复杂的数据分析:

    创建一个 weekday_mapper.py: 文件,作为数据按周进行分割
    import sys
    import datetime

    for line in sys.stdin:
    line = line.strip()
    userid, movieid, rating, unixtime = line.split('/t')

    生成数据的周信息

    weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
    print '/t'.join([userid, movieid, rating, str(weekday)])

    使用映射脚本

    //创建表,按分割符分割行中的字段值
    CREATE TABLE u_data_new (
    userid INT,
    movieid INT,
    rating INT,
    weekday INT)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '/t';
    //将python文件加载到系统
    add FILE weekday_mapper.py;

    将数据按周进行分割

    INSERT OVERWRITE TABLE u_data_new
    SELECT
    TRANSFORM (userid, movieid, rating, unixtime)
    USING 'python weekday_mapper.py'
    AS (userid, movieid, rating, weekday)
    FROM u_data;

    SELECT weekday, COUNT(1)
    FROM u_data_new
    GROUP BY weekday;

    处理Apache Weblog 数据

    将WEB日志先用正则表达式进行组合,再按需要的条件进行组合输入到表中
    add jar ../build/contrib/hive_contrib.jar;

    CREATE TABLE apachelog (
    host STRING,
    identity STRING,
    user STRING,
    time STRING,
    request STRING,
    status STRING,
    size STRING,
    referer STRING,
    agent STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
    WITH SERDEPROPERTIES (
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?",
    "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
    )
    STORED AS TEXTFILE;

    展开全文
  • 工作中,根据场景不同,可能建表方式不同,有时候用like、as方式建表会达到事半功倍的效果,下面是小白总结的三种创建的方式。 1、使用create命令创建一个新 create命令建表,参数不同,也有些许的区别,下面...

           工作中,根据场景不同,可能建表方式不同,有时候用like、as方式建表会达到事半功倍的效果,下面是小白总结的三种创建表的方式。

    目录

    1、使用create命令创建一个新表

    2、使用like创建表

    3、使用as创建表


    1、使用create命令创建一个新表

    create命令建表,参数不同,也有些许的区别,下面来看一下,创建一个带有分区的表,分区按天,取名为d

    可以看到分区是用partitioned by方式,指定分区的参数,另外下面的stored as是用来指定存储的方式,这里不止有orc方式,也可以用txtfile方式,但是orc方式是对数据进行压缩的,占很少内存,现在大多数选择这种方式存储,不过也有一些情景下使用txtfile,并且该方式的数据是行式存储,而orc是列式存储。

    当然,也可以不创建分区表,那么去掉partitioned的指定就可以。

    USE database;
    CREATE TABLE tablename(
         orderid bigint	comment '订单号',
         uid string	comment '用户id',
         orderdate date comment '预订日期'
    	 )
     COMMENT 'table comment'
     PARTITIONED BY(d STRING COMMENT 'date')
     STORED AS ORC;

    2、使用like创建表

    这种方式实质就是复制表结构。在工作中,如果你有一个已经存在的表,现在想创建一个一模一样的表,这个时候这个方法就很省时省力了。复制时,只会复制表结构,不会复制表中属性值(包扣表的分区以及存储格式之类的)

    use database;
    create table tablename1 like  tablename;
    

    3、使用as创建表

    这种方式是把一张表的某些字段抽取出来,创建成一张新表。

    这种方式需要注意以下几点:

    1.as只会复制属性以及属性值到新的表中

    2.使用as创建的表,并不会带原表的分区(分区丢失),并且分区的字段变成一个正常的字段,没有分区的效用,还有一些字段的约束等(可以通过describe formatted查看)

    use database;
    create table tablename1 as   
    select *  from tablename
     where d = '2019-01-01'
    ;

          以上就是三种建表方式啦,各有各的好处吧,可以视情况而定,而不是一直用第一种建表方式~

    展开全文
  • 1.1建表语句 create table 表名 ( 属性 类型, 属性 类型, … ) create table student( ID int primary key auto_increment, name varchar(20), age int, check(age>0) ) 1.2删除表语句 drop table ...

    1、 表定义

    建表语句

    create table 表名 (
    属性 类型 注释 [默认值],
    属性 类型 注释 [默认值],

    drop table if exists student;
    create table student(
    	id int primary key auto_increment comment 'ID',
    	name varchar(20) comment ‘学生姓名’,
    	age int comment '学生年龄',
    	check(age>0)
    )
    

    删除表语句

    drop table 表名

    drop table student
    

    修改表语句

    修改表名

    表重命名
    rename table oldTableName to newTableName;
    

    修改表字段、字段类型、备注

    增加表字段
    alter table student add class varchar(20) comment'xxx';
    
    修改表字段类型
    alter table student modify class varchar(50) comment'xxx';
    
    删除表字段
    alter table student drop class
    
    修改字段名
    alter table student change studentName stu_name varchar(20) not null comment'学生姓名';
    

    2、SQL数据查询和操作

    SQL查询

    SQL查询的基本结构

    select A1,A2....
    from r1,r2...
    where P
    
    A表示表属性
    r表示表
    P表示条件
    

    select子句

    1. 属性名中不能使用“-”,用下划线“_”替代
    --例子:查出所有老师职务
    select teacher_name,dept_name from teacher
    
    1. SQL中允许关系或者SQL表达式结果出现重复的元祖(重复的记录)
      若是要去重,可以再select后面添加关键字distinct,(distinct只能对所选择的属性的合集进行去重)
    select distinct teacher_name from teacher
    
    1. 星号 “”*”在select子句后,用来表示选择全部的属性
    2. 属性可以进行运算操作

    where子句

    1. where子句允许我们选出那些在from子句结果集中满足特定谓词的元祖
    2. where子句中可以使用逻辑连词 and,or,not 和between范围查询,逻辑连词的运算对象可以包换比较运算符

    from子句

    form子句是一个查询求值需要访问的关系列表,通过from子句在一个子句中列出所有关系上的笛卡尔积

    1. from子句中存在多个表时,在where子句后需要在属性前添加表前缀以示区分

    更名运算

    提供给属性和表重新命名机制,使用as子句:old-name as new-name,一般在多张表或者使用聚合函数的属性的时候用

    字符串运算

    1. like相关的模糊查询,“%”和“_”的区别
    2. 查询特殊字符(%和_等),需要在like比较运算中使用escape关键字作为定义转义符,MySQL中使用‘\’时报错,使用其他都没有问题。
    --匹配name属性含有%字符的所有元祖(记录)
    select * from student where name like "%/%%" escape "/"
    
    1. 字符串上的函数,串联、提取子串、计算字符串长度、大小写转换、去掉空格

    排序元祖显示次序

    SQL提供元祖排序控制,order by子句,以及升序asc,降序desc

    -- 根据学生表按学生升高降序,以及名字升序排序
    select * from student order student_height desc,name asc
    

    集合运算

    聚集函数

    1. avg:平均
    2. min:最小
    3. max:最大
    4. sum:求和
    5. count:记录总数
    6. group by (分组聚集):分组
    -- 例子:在老师表查询出所有系和平均薪资
    select dept_name ,avg(salary) as avg_salary 
    from teacher 
    group by dept_name
    

    注意:任何没有出现在group by子句中的属性,如果出现在select子句中的话,它只能出现在聚集函数内部,否则这样查询时会报错的。

    select dept_name ,avg(salary) as avg_salary 
    from teacher 
    group by dept_name 
    having avg(salary)>40000
    

    注意:和select子句情况类似,任何出现在having子句中的,但是没有被聚集的属性必须出现在group by子句中,否则查询时错误的(having中的要么是聚集函数中的属性或者是group by 子句后的属性)

    空值

    1. 我们可以再谓词中用特殊关键字null测试空值
    select name from teacher where name not is null
    
    1. 聚集函数中只有count函数会将null也统计进去,其他的函数都是忽略null

    嵌套子查询

    集合成员资格

    连接词in测试元祖是否是集合中的成员,相反的有 not in

    -- 找出2019年秋季和2020年春季都开设的课程
    select course_id 
    from section 
    where semester = "Fall" and year = 2019 and
    course_id in (select course_id 
    			  from section 
    			  where semester = "Spring" and year = 2020)
    

    集合比较

    -- 找出满足下面条件多有老师名字,他们的工资至少比Biology系某一个教师工资高
    --case one
    select distinct t.name 
    from teacher t,teacher t1 
    where t.salary >t1.salary and t1.dept_name = 'Biology';
    
    --case two (重点:至少比某一个要大)
    select distinct name 
    from teacher 
    where salary > some (select salary 
    					 from teacher 
    					 where dept_name = 'biology') 
    

    在这里插入图片描述

    -- 查找出下面条件的所有教师名字,他们的工资比Biology系的每一个老师的工资都要高
    select name 
    from teacher 
    where salary > all (select salary 
    					from teacher 
    					where dept_name = 'Biology')
    

    在这里插入图片描述

    空关系测试

    在这里插入图片描述

    --找出在2019年秋季学期和2020春季学期开课的所有课程
    -- case one
    select course_id 
    from section as S 
    where semester = 'Fall' and year = 2019 and  
    	exists (select * 
    			from section as T 
    			where semeter = 'Spring ' and year = 2020 and S.course_id = T.course_id)
    

    首先外行的select选择出2019年秋季课程,接着会再exists的内部在检查一遍,如果满足也是2020年春季课程的话,则exists结果 为true,否则为false,结果为true则结果加入外行输出结果集

    not exists 刚好和exists相反,如果内部检查不存在的话,满足要求返回true,否则为false

    EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
    IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    重复元祖存在性测试

    SQL中提供一个布尔函数,用来测试在一个子查询中是否存在重复元祖。如果子查询中没有存在重复的元祖unique结构返回true值。

    -- 找出所有课程在2020年最多开设一次的课程
    select T.course_id 
    from course as T 
    where unique (select R.course_id 
    			  from section as R 
    			  where T.course_id = R.course_id and R.year = 2020)
    

    unique 相当于 1>= :只有不重复的情况下才能是true

    not unique 相当于 1<:至少存在两个

    from子句中的子查询

    SQL允许在from子句中使用子查询。因为select-from-where返回结果都是关系,因此可以插到另一个select-from-where中任何关系可以出现的位置

    --查询所有系中工资总额最大值的系
    select max(totle_salary)
    from (select dept_name,sum(salary) 
    	  from instructor group by dept_name) 
    	  as dept_totle(dept_name ,totle_salary)
    

    with子句

    with子句提供定义临时关系,这个定义只对包含with子句查询有效
    在这里插入图片描述
    在这里插入图片描述

    3、SQL增删改操作

    数据库修改——删除

    -- r是表名 P是删除的条件
    delete from r where P
    

    数据库修改——插入

    -- []中的可以省略
    insert into r[(A1,A2,....)] 
    values(e1,e2,....)
    

    数据库修改——更新

    update r 
    set <A1 = e1, [A2 = e2,....]>
    [where P]
    

    SQL提供了case结构,避免更新次序引发的问题

    case
    when pred1 then result1
    when pred1 then result2
    ...
    when predn then resultn
    else result0
    end
    
    --工资小于5000的涨5%,其他的涨3%
    update teacher 
    set salary = case
    			 when salary<5000 then salary*1.05
    			 else salary*1.03
    end
    

    3、SQL语句总结

    SQL查询语句通用形式

    -- A是表属性(字段)
    select <[distinct] A1,A2,...>
    from <r1,r2,...>
    [where<condition>]
    [group by <A1,A2,...> [having<cond2>]]
    [order by <A1[desc],[A2[asc|desc]]>]
    

    SQL的查询执行顺序

    from -> where -> group -> having ->select -> order by

    视图和索引

    视图

    视图是从一个或几个基本表(或视图)导出的表。它与基本表不同,是一个虚表。数据库中只存放视百图的定义,而不存放视图对应的数据,这些数据仍存放在度原来的基本表中。所以基本表中的数据发生变化,从视图中查询出的数据也就随之改变了。从这个内意义上讲,视图就像一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化。视图一经定义,就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图,但对视图的更新(增、删、改)操容作则有一定得限制。

    索引

    为关系中的某些属性创建索引,它允许数据库系统高效的找到关系中那些在索引上给定的取值元祖,而不需要扫描所有的元祖

    create index <i-name> on <table-name> (<attribute-list>)
    
    --例子:普通索引
    create index stu_index on student (ID) 
    
    create index stu_ID_name_index on student (ID , name)
    
    -- 唯一索引
    create unique index stu_index on student (ID)
    
    -- 删除索引
    drop index <i-name>
    

    4 、事务和完整性约束

    事务

    事务(transaction)由查询和更新语句的序列组成,SQL标准规定当一条SQL语言被执行,就隐式开启一个事务,下列SQL语句之一是结束一个事务

    • Commit work:提交当前事务,也就是将数据持久化更新到数据库保存,在事务被提交之后,一个新的事务自动开启
    • Rollback work:回滚当前事务,及撤销该事务所有SQL语句对数据库的更新,这样数据库会回到执行该事务第一条语句之前状态

    事务的四个特性:

    • 原子性
    • 一致性
    • 隔离性
    • 持久性

    完整性约束

    完整性约束保证授权用户对数据库所做的改变不会导致数据一致性的破坏

    按约束对象区分
    1. 域完整性
      在单列上的约束
    2. 关系完整性
      再多列上或者表上的约束或者是表之间的约束
    按约束来源区分
    1. 结构完整性约束

      1. 实体完整性(主键约束):主键不能为空
      2. 参照完整性(外键约束):外键只能为空或者参考表中属性的集合

      涉及的表存在参考完整性约束,在增删改的时候,都需要考虑约束情况

      在这里插入图片描述
      在这里插入图片描述

      --建表是创建外键
      --被参照关系也可以明确指出,但是必须是生命的主码或者候选码(正确)
      [CONSTRAINT constraint_name]foreign key (dept_name) references department (dept_name)
      

      SQL中的联机动作

      
      create table course(
      	foreign key (dept_name) reference department
      		[on delete cascade]
      		[on update cascade]

      on delete cascade ,如果删除department中的元祖导致参照完整性约束违反,删除不会被系统拒绝,而是对course关系做级联删除。即删除被删除系的元祖。级联更新的话也是类似。
      如果存在多个级联操作的话,那么参照完整性实在事务结束时检查。
      其他级联操作选择

      on delete set null 
      
      on delete set default 
      
      1. 用户自定义完整性
        比如:老师的年龄在20—50之间等
    2. 内容约束
      比如用户自定义约束

    按状态区分——静态完整性约束

    列约束

    在这里插入图片描述

    --MySQL-列约束(属性后面追加约束)
    create table <table-name> (
    	colname int [primary key] [auto_increment] [comment '注释'] [default <默认值>],
    	colname varchar(30) [unique] [comment '注释'] [default <默认值>],
    	colname int [comment '注释'] [default <默认值>],
    	colname int [constraint <constraint-name>]check(colname>20) not null [comment '注释'] [default <默认值>],
    
    	[constraint <constraint-name> foreign key(colname) references <table-name> (colname)],
    )
    
    --MySQL建表
    create table dept(
    	id int primary key auto_increment comment 'ID' ,
     	Dname varchar(30) not null comment '系名称'
     );
    
    --case one 列约束
    create table teacher(
     	id int primary key auto_increment comment ‘ID’,
    	name varchar(30) not null  UNIQUE comment ‘老师姓名’,
    	age int CONSTRAINT tea_age CHECK(age>20) comment ‘老师年龄’,
    	dept_id int comment ‘老师所在系ID’,
    	[constraint dept_id] foreign key(dept_id) references dept(id)
    )
    
    表约束

    在这里插入图片描述

    create table <table-name>(
    	colname int auto_incremrnt [comment '注释'] [default <默认值>],
    	colname varchar(30)  [comment '注释'] [default <默认值>],
    	colname int [comment '注释'] [default <默认值>],
    	colname int  not null [comment '注释'] [default <默认值>],
    	
    	[primary key(cloname)]
    	[constraint <constraint-name> unique(colname)],
    	[constraint <constraint-name> foreign key(colname) references <table-name> (colname)],
    	[constraint <constraint-name> check(cloname>20)]
    )
    --case two 表约束
    create table teacher(
    	id int auto_increment,
    	name varchar(30) not null,
    	age int,
    	dept_id int,
    	primary key(id)
    	constraint Tname unique(name),
    	constraint Tage check(age>20),
    	constraint dept_id foreign key(dept_id) references dept(id)
    )
    

    在这里插入图片描述
    W3school约束创建、删除、修改,以及不同数据库的约束的区别

    MySQL中删除约束

    1、删除外键约束

    alter table <table-name> drop foreign key <key-name>
    
    --例子
    alter table teacher3 drop foreign key dept_id 
    

    2、删除主键约束

    如果主键时自增的话,直接删除的话会报错

    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key              
    #这说明此列是自动增长列,无法直接删除
    

    列的属性还带有AUTO_INCREMENT,那么要先将这个列的自动增长属性去掉,才可以删除主键。

    --先去掉自增
    alter table <table-name> modify id int
    --在删除主键约束
    alter table <table-name> drop primary key
    

    主键添加自增

    alter table <table-name> modify colname dataType auto_increment
    

    属性(ID)设置成主键且自增

    alter table <table-name> modify colname dataType auto_increment primary key 
    

    3、删除唯一约束

    alter table <table-name> drop index <constraint-name>
    

    动态完整性约束

    MySQL参考手册——触发器语法和示例

    展开全文
  • 建表注意 1、建议字段定义为NOT NULL 搜索引擎 MyISAM InnoDB 区别 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个类型,这两个类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持...
  • SQL建表查询优化的30个小技巧(收藏)

    千次阅读 多人点赞 2020-01-18 11:09:56
    SQL语句优化一直是一个比较热门的话题,是面试的时候经常备问到的问题之一,网上有许多关于这方面的文章,可谓是多而杂乱,为了便于学习,我特意搜集以下30条具体建议,以帮助更多朋友。如果有不对的地方,还请各位...
  • [SQL] 建表、查看信息

    千次阅读 2018-05-15 20:29:17
    建表 (1) 创建 create table targer_table as select * from source_table;复制结构+数据 create table targer_table as select * from source_table where 1=2;只会创建相同的结构,不会复制数据。 ...
  • sql语句建表

    千次阅读 2018-05-17 16:50:18
    当所需的表名和字段是中文时 可以两种建表的语句CREATE TABLE `仓库`( `仓库ID` INT(4) primary key auto_increment, `仓库号` VARCHAR(50) UNIQUE, `城市` VARCHAR(50) DEFAULT '阳泉', `面积` INT(5) DEFAULT...
  • hive SQL——建表、删、插入数据

    千次阅读 2018-07-04 16:44:36
    1. 建表(create table xx)   -- 使用create命令创建一个新,带分区 CREATE TABLE miniapps_dept ( dept string comment '字段注释' app_name string comment '字段注释', appkey string comment '...
  • Hive的hql是基于sql而来,而sql中关于的创建有几种方式。同样,hive也支持这些的创建方式。 官网文档关于建表的地址:hive常见建表方式官网文档 1.自主创业方式create table 使用create table从无到有,...
  • 创建一张 create TABLE tb_bookinfo( id int(11) NOT NULL, barcode varchar(10), bookname varchar(10), typeid int(10) unsigned, author varchar(30), ISBN varchar(20), price float(8,2), page int(10) ...
  • create table workers ( wID char(5) check ( wID like‘[a-z][0-9][0-9][0-9][0-9]’), wName char(10) NOT NULL, wSex char() check ( wSex IN (‘女’,‘男’...这样一个简单的代码,无法成功建表 Oracle11g
  • SQL Server建库建表命令

    千次阅读 多人点赞 2020-03-16 17:12:00
    数据库建库建表 1.直接右键数据库,选择新建数据库; 2.通过新建查询,输入代码建库建表。 使用CREATE DATABASE创建数据库school。 数据文件的逻辑名称自定义,需要注意的点是主数据文件的物理名称需填写存储文件的...
  • 高级建表SQL

    2010-08-16 09:15:58
    --建表AB 和已知AA一样create table AB like AA--把已知AA中数据插入AB Insert into AB(A1,A2) select A1,A2 from AA--把已知AA中符合条件的数据插入AB Insert into AB(A1,A2) select A1,A2 from AA where...
  • Sql Server快速建表

    千次阅读 2012-11-04 15:16:45
    create table 学生 ( 学号 varchar(16) primary key, 姓名 varchar(8), 性别 char(2) check (性别 IN ('男','女')), 出生日期 date, 所在系 varchar(20), 专业 varchar(20), 班号 varchar(10), 联系号码...
  • 1、建表 create Table Student( Sno char(9) not null, Sname char(20) not null, Ssex char(2) null, Sage smallint null, Sdept char(20) null, primary key (Sno) ); create Table Course( Cno char(4)...
  • oracle开发建表实例sql

    2019-12-04 14:32:07
    oracle开发建表实例sql -- CREATE TABLE(创建表格) CREATE TABLE TEST.TB_TABLE1 ( IARTICLEID NUMBER(20) NOT NULL, CTITLE VARCHAR2(30), ITAG INTEGER, CAUTHOR VARCHAR2(30), ITYPE ...
  • SQL insert overwrite insert into create 创建 导入
  • hive建表操作

    2019-04-15 16:22:06
    HIVE 建表及对表基本操作 建表的三种形式 1.直接建表 create [external] table [if not exists] tab_name ( row_1 type, row_2 type ) partition by (row_3 type,type_4 type ) row format delimited fields ...
  • 1、建表 create table student (  id number(10,0) not null,  uname varchar2(255 char),  pword varchar2(255 char),  primary key (id) ); 2、插入 insert into student values(1,'张三','qweerwe'); ...
  • SqlServer 代码建库建表

    千次阅读 2017-03-11 18:53:18
     check (stuTel like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),--check约束 用了通配符   constraint DE_stuAddress --default约束   default '地址不详' for ...
  •  add constraint CK_uemail check (UEmail like '%@%' ) alter table BBSUsers--为性别添加默认约束约束  add constraint DK_usex default('1') for USex alter table BBSUsers--等级默认约束  add ...
  • 1. sql基础 ) 建表语句 --建表 --学生 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL....
  • MySQL和DB2建表SQL差异

    2017-11-27 13:44:00
    MySQL5和DB2 V9建表SQL差异 背景: MySQL5和DB2在创建的时候有些差异,在实际工作中,我常常将一些DB2的导入到MySQL5中,然后用SQLyog Enterprise Edition V5.25来开发数据库脚本,用PL/SQL Developer V...
  • -- 空间的创建 CREATE TABLESPACE my_tabs2 DATAFILE 'd:/software/Oracle/Space/my_tabs2.dbf' size 60M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED; -- 创建用户 create user user2 identified by user2 default...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 30,542
精华内容 12,216
关键字:

likesql建表表