# General SQL Statements

# General SQL Statements

## Database Management

Database is similar to the database in the relational database, which is a collection of structured time series data.

### create database

Create a database named `root.ln`

with the following syntax:

```
CREATE DATABASE root.ln
```

### show databases

View all databases:

```
SHOW DATABASES
```

### delete database

Drop the database named `root.ln`

:

```
DELETE DATABASE root.ln
```

### count databases

```
COUNT DATABASES
```

## Time Series Management

Time series is a collection of data points indexed by time. In IoTDB, time series refers to a complete sequence of measurement points. This section mainly introduces the management of time series.

### create timeseries

The encoding method and data type need to be specified. For example, create a time series named `root.ln.wf01.wt01.temperature`

:

```
CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH datatype=FLOAT,ENCODING=RLE
```

### show timeseries

View all time series:

```
SHOW TIMESERIES
```

Use wildcards to match time series under database `root.ln`

:

```
SHOW TIMESERIES root.ln.**
```

### delete timeseries

Delete a time series named `root.ln.wf01.wt01.temperature`

:

```
DELETE TIMESERIES root.ln.wf01.wt01.temperature
```

### count timeseries

Count the total number of time series:

```
COUNT TIMESERIES root.**
```

Count the number of time series under a wildcard path:

```
COUNT TIMESERIES root.ln.**
```

## Time Series Path Management

In addition to the concept of time series, IoTDB also has the concepts of subpaths and devices.

**Subpath**: It is a part of the path in a complete time series name. For example, if the time series name is `root.ln.wf01.wt01.temperature`

, then `root.ln`

, `root.ln.wf01`

, and `root.ln.wf01.wt01`

are all its subpaths.

**Device**: It is a combination of a group of time series. In IoTDB, the device is a subpath from the root to the penultimate node. If the time series name is `root.ln.wf01.wt01.temperature`

, then `root.ln.wf01.wt01`

is its device.

### show devices

```
SHOW DEVICES
```

### show child paths

Check out the next level of `root.ln`

:

```
SHOW CHILD PATHS root.ln
```

### show child nodes

```
SHOW CHILD NODES root.ln
```

### count devices

Count the number of devices:

```
COUNT DEVICES
```

### count nodes

Count the number of nodes at the specified level in the path:

```
COUNT NODES root.ln.** LEVEL=2
```

## Query Data

The following are commonly used query statements in IoTDB.

### Query the data of the specified time series

Query all time series data under the device `root.ln.wf01.wt01`

:

```
SELECT * FROM root.ln.wf01.wt01
```

### Query time series data within a certain time range

Query the data in the time series `root.ln.wf01.wt01.temperature`

whose timestamp is greater than 2022-01-01T00:05:00.000:

```
SELECT temperature FROM root.ln.wf01.wt01 WHERE time > 2022-01-01T00:05:00.000
```

### Query time series data whose values are within the specified range

Query the data whose value is greater than 36.5 in the time series `root.ln.wf01.wt01.temperature`

:

```
SELECT temperature FROM root.ln.wf01.wt01 WHERE temperature > 36.5
```

### Use last to query the latest point data

```
SELECT last * FROM root.ln.wf01.wt01
```