ClusterDB SQL语法

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了ClusterDB SQL语法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6653字，纯文字阅读大概需要10分钟。

内容图文

CustrixDB支持的DML语法

SELECT, DISTINCT, [LEFT | RIGHT | OUTER] JOIN, STRAIGHT_JOIN
UNION, HAVING, GROUP BY, LIMIT, ASC, DESC, ORDER BY, FOR UPDATE
Subqueries, including with IN
EXISTS, NOT EXISTS
INSERT, INSERT... ON DUPLICATE KEY UPDATE, UPDATE, DELETE, REPLACE INTO
CREATE, DROP, TRUNCATE, AUTO_INCREMENT 
LOAD DATA INFILE
START TRANSACTION, COMMIT, ROLLBACK, PREPARE
COUNT(), AVG(), STD(), SUM(), MAX(), MIN(), GROUP_CONCAT()

还支持

Foreign Keys
Data Types
EXPLAIN
Partitioned Tables (RANGE only)
MySQL Replication
Stored Routines
Triggers

ClustrixDB不支持的DML语句

ASC or DESC qualifier for GROUP BY; instead, for ordered output, ORDER BY must be explicit. For example:
select a,count(*) from foo group by 1 desc; -- will give a syntax error 
select a,count(*) from foo group by 1 order by desc; -- OK
COLLATE in SELECT 
CUBE
EXCEPT
IGNORE keyword as part of a DELETE statement 
INTERSECT
LOCK TABLES statement
NOT in conjunction with user-defined variables that reference a function, e.g. SET @a = not foo();
ROLLUP
SELECT INTO ... OUTFILE option. Use  mysql -e "SELECT ..." > file_name instead.
Subqueries with ALL or SOME

 

导出数据：

clxm@p2cn1uclx101m_10.248.100.241 ~]$ mysql -uroot -e 'select * from scloud.sbtest1 where id < 10 order by id' -N > outfile.log 

导入数据：

MySQL [test]> load data infile  '/home/clxm/outfile.log' into table sbtest1;

 

支持管理SQL语句

ANALYZE TABLE
EXPLAIN and EXPLAIN REFERENCED
OPTIMIZE TABLE
ClustrixDB supports the syntax for OPTIMIZE TABLE, but does not perform any operation. ClustrixDB uses background processes to automatically optimize storage. 
REPAIR TABLE
SHOW BINARY LOGS
SHOW CHARACTER SET (with no WHERE clause)
SHOW COLLATION 
SHOW COLUMNS
SHOW CREATE [DATABASE | TABLE | FUNCTION | TRIGGER | SEQUENCE | VIEW]
SHOW DATABASES
SHOW ENGINES
For compatibility, ClustrixDB lists InnoDB as part of the output of SHOW ENGINES, but the Clustrix engine will always be applied. 
Not all engines are listed as output of SHOW. See ENGINES for more information.
SHOW GRANTS
SHOW INDEX
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW STATUS
SHOW TABLES
SHOW TRIGGER
SHOW WARNINGS
The syntax for SHOW WARNINGS is supported, but no warnings are ever displayed.
SHOW VARIABLES

不支持的SQL管理语句

CHECKSUM TABLE
FLUSH 
SHOW AUTHORS
SHOW BINLOG EVENTS
SHOW CLIENT STATISTICS
SHOW COLUMNS with WHERE clauses
SHOW CREATE USER
SHOW CONTRIBUTORS
SHOW ERRORS
SHOW EVENTS
SHOW EXPLAIN FOR 
SHOW FUNCTION [CODE | STATUS]
SHOW INDEX STATISTICS 
SHOW LOCALES
SHOW MASTER LOGS 
SHOW PLUGINS
SHOW PRIVILEGES 
SHOW PROFILE
SHOW PROCEDURE [CODE | STATUS]
SHOW QUERY_RESPONSE_TIME
SHOW TABLE_STATISTICS
SHOW USER_STATISTICS

 

支持的函数和操作符

:=
!
!=
*
/
&
&&
%
^
+
<
<<
<=
<=>
=
=
>
>=
>>
|
||
~
ABS
ACOS
ADDDATE
ADDTIME
AES_DECRYPT
AES_ENCRYPT
ASCII
ASIN
ATAN
ATAN2
AVG
BETWEEN AND
If one of the values being compared for BETWEEN is NULL, ClustrixDB will return NULL 
BIN
BINARY OPERATOR
BIT_AND
BIT_COUNT
BIT_LENGTH
BIT_OR
BIT_XOR
CASE
CAST
CEIL
CEILING
CHAR Function
CHAR_LENGTH
CHARACTER_LENGTH
CHARSET
COALESCE
COMPRESS
CONCAT
CONCAT_WS
CONNECTION_ID
CONTAINS
CONV
CONVERT
CONVERT_TZ
COS
COT
COUNT
ClustrixDB allows multiple inputs to the COUNT() function, where some versions of MySQL and MariaDB do not 

ClustrixDB will include trailing NULL values in the results of COUNT(), while MySQL does not

COUNT DISTINCT
CRC32
CURDATE
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURTIME
DATABASE
DATE FUNCTION
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
DECODE
DIV
ELT
ENCODE
ENCRYPT
EQUALS
EXP
EXPORT_SET
FIELD
FIND_IN_SET
FLOOR
FORMAT
FOUND_ROWS
FROM_BASE64
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
GET_LOCK
Use of GET_LOCK with Statement-based Replication is considered unsafe, though ClustrixDB does not raise an error. MySQL raises an error. 
GREATEST
GROUP_CONCAT
Multiple GROUP_CONCAT statements are supported, but only one ORDER BY is allowed per select statement
HEX
HOUR
IF
IFNULL
IN
INSERT Function
INSTR
INTERVAL
IS
IS NOT
IS NOT NULL
IS NULL
IS_FREE_LOCK
IS_USED_LOCK
ISNULL
JSON_ARRAY
JSON_CONTAINS_PATH
JSON_DEPTH
JSON_EXTRACT
JSON_KEYS
JSON_LENGTH
JSON_OBJECT
JSON_QUOTE
JSON_SEARCH
JSON_TYPE
JSON_UNQUOTE
JSON_VALID
LAST_DAY
LAST_INSERT_ID
LAST_VALUE
LCASE
LEAST
LEFT
LENGTH
LIKE
LINESTRING
LN
LOCALTIME
LOCALTIMESTAMP
LOCATE
In ClustrixDB, LOCATE() returns NULL when position is provided as NULL. MySQL returns 0 in such instances.
LOG
LOG10
LOG2
LOWER
LPAD
LTRIM
MAKE_SET
MAKEDATE
MAKETIME
MAX
MD5
MICROSECOND
MID
MIN
MINUTE
MOD
MONTH
MONTHNAME
NAME_CONST
NEXTVAL
NOT BETWEEN
NOT IN
NOT LIKE
NOT REGEXP
NOW
NULLIF
OCT
ORD
PASSWORD
PERCENT_RANK
PERIOD_ADD
The results of this function do not match MySQL when year boundaries are being crossed or approached.
PERIOD_DIFF
PI
POW
POWER
QUARTER
QUOTE
RADIANS
RAND
Does not accept an argument as seed.
RANK
REGEXP
RELEASE_LOCK
REPEAT Function
REPLACE Function
REVERSE
RIGHT
RLIKE
ROUND
ROW_COUNT
When updating a row, if the values are not being changed (e.g. (1,1) → (1,1)) mysql reports 0, ClustrixDB returns 1
When replacing a row with an identical row, mysql reports 1, ClustrixDB returns 2
On ClustrixDB, ROW_COUNT does not reflect the number of rows affected by a stored procedure 
ROW_COUNT does not match mysql behavior for DDL (e.g. CREATE TABLE then  SELECT ROW_COUNT)
When committing an explicit transaction, ClustrixDB reports the ROW_COUNT() value for the most recent statement before the COMMIT. In other words, row_count behaves as if there was no COMMIT.
ROW_NUMBER
RPAD
RTRIM
SCHEMA
SEC_TO_TIME
SECOND
SESSION_USER
SHA
SHA1
SHA2
SIGN
SIN
SLEEP
SQRT
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
STR_TO_DATE
STRCMP
SUBDATE
SUBSTR
SUBSTRING
SUBSTRING_INDEX
SUBTIME
SUM
SYSDATE
Does not reflect time zone settings; always returns UTC.
SYSTEM_USER
TAN
TIME function
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP FUNCTION
TIMESTAMPADD
TIMESTAMPDIFF
TO_BASE64
TO_DAYS
TO_SECONDS
TRIM
TRUNCATE
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UNIX_TIMESTAMP
UPPER
USER
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
UUID
UUID_SHORT
VALUES or VALUE
VAR_POP
VAR_SAMP
VARIANCE
VERSION
WEEK
WEEKDAY
WEEKOFYEAR
XOR
YEAR
YEARWEEK

 

不支持的函数和操作符

ANALYSE()
BENCHMARK() 

CHARSET() 

COERCIBILITY() 

COLLATION() 

CURRENT_USER() 

EXTRACT() 

GET_FORMAT() 

INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA()

IS_IPV4, IS_IPV4_COMPAT (), IS_IPV4_MAPPED (), IS_IPV6 ()

LOCK TABLES()

LOAD_FILE() 
MAKEDATE() 

SESSION_USER()
TO_SECONDS()

 

支持的DDL操作

ALTER CLUSTER
DISTRIBUTE
SLICES
REPLICAS

 

不支持的DDL操作

ALTER TABLE...CONVERT
CREATE SEQUENCE
CREATE VIEW ... WITH CHECK OPTION 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

内容总结

以上是互联网集市为您收集整理的ClusterDB SQL语法全部内容，希望文章能够帮你解决ClusterDB SQL语法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---