Selecting All Columns or Specific columns
In this section you will be learn how to create database and create a specific columns in MySQL .
CREATE Statement
The CREATE statement is used to create the table.
Example:
Create the tables:
| mysql> CREATE TABLE Employee ( -> id int unsigned not null auto_increment primary key, -> f_name varchar(20), -> l_name varchar(20), -> age int, -> salary int, -> email varchar(60) -> ); Query OK, 0 rows affected (0.03 sec) |
Insert the Data in the table:
The INSERT statement is used the load the data in the
table. Keyword are used in this statement are follows :
INSERT - Insert data into the table
INTO - Specified the Table name for inserting the data
VALUES - Data for the columns in the table.
| mysql> INSERT INTO Employee (f_name,
l_Name, age, salary, email) VALUES ("Deepak",
"Kumar", 31, 25000, "[email protected]"); Query OK, 1 row affected (0.00 sec) |
MySQL creates the table by the name of Employee with the column along the first row. If you want to only select columns, replace the * with the names of the columns, separated by commas. The select statement follow just like the , SELECT * FROM Employee :
| mysql> SELECT * FROM Employee; +----+-----------+----------+------+--------+---------------+ | id | f_name | l_name | age | salary | email | +----+-----------+----------+------+--------+---------------+ | 1 | Deepak | kumar | 31 | 25000 |[email protected] | +----+-----------+----------+------+--------+---------------+ 1 row in set (0.00 sec) |
Display the MySQL version
| mysql> SELECT VERSION(); |
For displaying the current date and time
| mysql> SELECT NOW( ); |
Mysql Evaluating Expression
Expressions have operators and terms and they are evaluated to generate values. Expressions contain constants, function calls and references to table columns. These values can be combined using different kinds of operators like comparison or arithmetic operators. Expression terms can be grouped with parentheses.
| select concat(last_name, ', ', first_name), (YEAR(death) - YEAR(birth)) - if(RIGHT(death,5)<RIGHT(birth,5),1,0) from president where birth > '1985-5-5' and death is not null; |
When an Expression is encountered MySQL, it evaluates the expression to produce a result.
Writing Expressions
An Writing expression can be as simple as a single constant. Expressions may use function calls. But some function takes arguments and some does not. And multiple arguments are separated by commas.
| 0 | Numeric constant |
| 'abc' | String constant |
| '06-11-22' | Date constant |
Example:
| mysql> select ((25*4)/10)+35; +----------------+ | ((25*4)/10)+35 | +----------------+ | 45.00 | +----------------+ 1 row in set (0.00 sec) |
CONCAT()
The CONCAT() function uses argument between parenthesis. These can be use columns name
or plane text string .
Example
| mysql> SELECT CONCAT(f_name, " ", l_name) from employee_data where title = 'Programmer'; +-----------------------------+ | CONCAT(f_name, " ", l_name) | +-----------------------------+ | Alok Kumar | | deepak ranjan | | sushil pal | | chandan kumar | +-----------------------------+ 4 rows in set (0.00 sec) |