Getting started with CSQL
<< CSQL Transaction Control - TOC - Functions And Operators >>
This chapter discusses the SQL statements available in CSQL. For each statement, this chapter gives the supported syntax; explain the parameters, any other relevant information. This chapter starts with CSQL Interactive tool for SQL Statements, and then explains briefly about DDL and DML statements including JOIN and GROUP BY. The middle part lists about accessing the meta-data information using Catalog tool and at the end it describes about the batch mode operation with CSQL tool.
Client tool for communicating with CSQL database
CSQL provides a tool called csql, which is a sub-shell used to access the CSQL database. It supports most of the standard SQL statements. Type csql command is executed in command line to get Interactive prompt named "CSQL>" to access Database. Make sure that csqlserver is running prior to running this tool. In order to log into CSQL the information must be passes to the CSQL client program when it is started. This is done with the following commands and syntax.
$csql -u root -p manager CSQL>
Now the tool is ready to be used by the user. Here "root" is the default user name and "manager" is the password for log on. Once the CSQL prompt is displayed then the tool is ready to access the database.
Using a database
This section will demonstrate about database table and index, including all other supported and provided functionality by CSQL.
Creating new User
Login as root and specify username and password in create user statement.
Syntax: CREATE USER <User_Name> PASSWORD '<Password>': Example: $ csql -u root -p manager CSQL>CREATE USER ajmera PASSWORD 'ajmera123'; Statement Executed
Changing Password
To change password of existing user, Login with that user and use following syntax.
Syntax: ALTER USER <User_name> SET PASSWORD '<New_Password>'; Example: $ csql -u root -p manager CSQL>ALTER USER root SET PASSWORD 'root123'; Statement Executed
Dropping user
Login as root and specify user name to drop in drop user statement.
Syntax: DROP USER <User_name>; Example: $ csql -u root -p manager CSQL>DROP USER ajmera; Statement Executed
Creating new table
Specifying the table name, along with all column names and their types, could create a new table.
Syntax:
CREATE TABLE <table_name> (
col1 Datatype[<Size>] <default value>[NOT NULL],
col2 Datatype[<Size>] < default value >[NOT NULL],
col3 Datatype[<Size>] < default value >[NOT NULL],
col4 Datatype[<Size>] < default value >NOT NULL],
... .............................,
....... .............................,
[PRIMARY KEY (col1, col2, ...)] );
Example: 1
CSQL> CREATE TABLE stud (
roll INT NOT NULL,
name CHAR(20) NOT NULL,
course_fee DOUBLE,
dept_no INT
);
NOTE: Except CHAR and BINARY data type other data types do not require size. The "primary key" could be applied on all data types except FLOAT, REAL, DOUBLE.
Description The above SQL Statement will create table ' stud ‘ having 4 fields "roll" is an integer field , "name" is a string field which can store a string ,"course_fee" is a floating point with double precision field which can store values with decimal point and “dept no” is an integer field.
Auto Increment Field
To generate a unique identity for a new row, CSQL supports AUTO_INCREMENT key.
CSQL> create table employee(
eid int auto_increment,
ename char (10),
salary float
);
CSQL>insert into employee values (NULL, 'Ram T', 10545.00);
CSQL>insert into employee (ename, salary) values (‘Danial Kouch', 35956.00);
CSQL>insert into employee (ename, salary) values ('Md. khajit',45645.00);
CSQL>select * from employee;
---------------------------------------------------------
employee.eid employee.name employee.salary
---------------------------------------------------------
1 Ram T 10545.000000
2 Danial Kouc 35956.000000
3 Md. khajit 45645.000000
Note: AUTO_INCREMENT key field cannot be updated in CSQL.
DEFAULT Clause
Default clause is used to specify the default value for an attribute if no value is given, e.g., when a record is inserted.
Syntax:
Create table <Tab_Name> (
<Col_Name Col_DataType> DEFAULT <Col_Value>, …, …
);
Example:
CREATE TABLE EMPLOYEE ( empno int, ename char(10), job char (10) DEFAULT (‘Clerk’), primary key (empno) );
In the above example, if there is no value entered in the “job” column then the default job will be inserted as ‘clerk’.
INSERT Statement
The INSERT statement is used to populate a table with rows, the user can insert data into the table in two ways.
First Form Syntax :
INSERT INTO <table_name> VALUES (val1, val2, val3, val4,...);
Second Form Syntax:
INSERT INTO <table_name> (col1, col2, col3, ...) VALUES (val1, val2, val3, ...);
Generally this form is useful when data for many fields are to be NULL.
Update
The data values of existing rows can be modified using the UPDATE command. The dept_no for stud having roll 3 to 10 have been modified in the below example.
CSQL>UPDATE stud SET dept_no=10 WHERE roll=3; Statement Executed: Rows Affected = 1
Delete
Using the DELETE command the user can delete the existing rows from a table. In the below statements the record having the value dept_no=40 is deleted.
CSQL>DELETE FROM stud WHERE dept_no=40;
SELECT Statement
To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a SELECT list (the part that lists the columns to be returned), a table list (the part that lists the tables FROM which to retrieve the data), and an optional qualification (the part that specifies any restrictions).
For example, to retrieve all the rows of table "stud", type:
SELECT * FROM stud;
Here * is a shorthand for “all columns”.
The selective data can be retrieved as per the requirement using different logical and relational Operators.
LIMIT and OFFSET keyword
These two keywords allow you to retrieve exactly a part of the rows that are returned by the rest of the query. The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT and OFFSET take numeric argument, which must be non-negative constants.
LIMIT specifies the maximum number of rows to return where OFFSET specifies the offset of the first row to return. NOTE: The OFFSET of the initial row is 0(not 1).
Syntax:
SELECT projection list
FROM table_expression
[ LIMIT {number} ] [OFFSET {number}]
Example: To retrieve some rows from a certain offset up to some limit.
SELECT * FROM emp LIMIT 10 OFFSET 1;
ORDER BY Clause
It is used to sort the records by a specified column. By default, it sorts the record in ascending order, if you want to sort the records in a descending order, you can use the DESC keyword.
Syntax:
ORDER BY <col_name1> [ASC|DESC], …
Example: To select all employees sorted on department in ascending order, within department, sorted on salary in descending order.
SELECT deptno, ename, sal FROM emp ORDER BY deptno, sal desc;
COPY Table
For backup and other requisites we much have to copy the data of a table or copy the total table structure with data. We can selectively copy the data of a CSQL table to a new table or copy the total data to a new table. This provides a quick means of making a point-in-time copy of a table and is safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.
Below are here different techniques on how to do this.
Copy an existing table to a new table
To make a copy of the table into a new table in the current database, use these two commands:
Syntax:
1. CREATE TABLE <Table_Name> AS <Select_Query>; 2. INSERT INTO <Table_Name> AS <Select_Query>;
The first syntax creates a new table by duplicating the structure of the existing table. The second syntax copies the data from old to new.
Note: Condition copying is supported.
COMPACT Statement
During deletion of records, CSQL marks the records for deletion rather than reclaiming the space allocated to store the record. This increases the chance of fragmentation when INSERT and DELETE operations are performed continuously on the table. Using COMPACT TABLE statement, these fragmentation can be reduced.
Syntax:
compact table <Table_Name>;
Example:
compact table employee;
Getting Information about Metadata using Catalog tool
Catalog tool, which provides information about system metadata and user metadata of tables stored in the CSQL database. “catalog” tool on table ‘t1’, which contains two fields, f1 that is integer type and f2 is character type with size 10 bytes.
Example: The below catalog command with no option will retrieve all the tables present in the database.
$catalog <TableNames> <TableName> t1 </TableName> </TableNames>
Example: To get all the information about table, the –l option must be used.
$ catalog -u root -p manager -l
<Table Information of all tables>
<TableInfo>
<TableName> t1 </TableName>
<FieldInfo>
<FieldName> f1 </FieldName>
<Type> 0 </Type>
<Length> 4 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
<FieldInfo>
<FieldName> f2 </FieldName>
<Type> 30 </Type>
<Length> 11 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
</TableInfo>
</Table Information of all tables>
SHOW TABLES Statement
To display all the tables present in database, use this statement. At the time of executing this SHOW statement only ‘t1’ table is present in the database.
CSQL>show tables; ============TableNames=================== t1 =========================================
SHOW USERS Statement
To display all the user present in database, use this statement. At the time of executing this SHOW USERS statement only "root" user is present in the database.
CSQL>show users; =============UserNames=================== root =========================================
SHOW VARIABLES Statement
To display all configuration variables and values, which are present in csql.conf file for all the sections in CSQL such as Server, Network, Client, Cache and Replication.
CSQL>show variables;
Using CSQL in batch mode
The csql tool with -s option is for executing more than one statement at a time.
- Create a file (example1.sql file as per convention), which will contain all the valid necessary SQL statements.
- To execute all the statements in the file, type ‘csql -s example1.sql’.
Example: 1 Lets create a table named "example1", Insert 4 records into it and do it in batch mode. For this create a file for example1.sql and write all sql statements into it.
$cat > example1.sql create table example1(f1 int,f2 int); insert into example1 values(1,10); insert into example1 values(2,20); insert into example1 values(3,30); insert into example1 values(4,40); ctrl+d
Execute the file with csql -s option.
$ csql -s example1.sql Statement Executed Statement Executed: Rows Affected = 1 Statement Executed: Rows Affected = 1 Statement Executed: Rows Affected = 1 Statement Executed: Rows Affected = 1
<< CSQL Transaction Control - TOC - Functions And Operators >>