today i learning the basics of mysql
just i creating the database
to creating the database in mysql
create database slashprog;
to see the databases after creating the database
show databases;
later we using this command:--- use slashprog;
create a table
like wise we can create "n" number of maximum tables in a database
to see what are all the tables inside our database
syntax:- show tables;
syntax:--- create table (tablename)
create table students(name varchar(32),age int,address varchar(32));
insert values in table
syntax:--- insert into (tablename) values(xxx,25,yyyy);
to see the table structure command is
desc (tablename)
To see all the values in table
select * from (tablename)
To see all the values in the table
select * from students;
+----------+------+--------+
| name | age | salary |
+----------+------+--------+
| kiran | 25 | 25000 |
| sathia | 26 | 10000 |
| john | 28 | 14545 |
| jenefier | 26 | 12454 |
| smith | 29 | 14564 |
| sachin | 30 | 12456 |
+----------+------+--------+
select only names to see the syntax below as follows :---
select name from students;
+----------+
| name |
+----------+
| kiran |
| sathia |
| john |
| jenefier |
| smith |
| sachin |
+----------+
to see the ages of above 26 in the database the command
select age from students where age >26;
+------+
| age |
+------+
| 28 |
| 29 |
| 30 |
+------+
to show the maximum age of of the students
select MAX(age) from students;
+----------+
| MAX(age) |
+----------+
| 30 |
+----------+
to sumup the fields of the salary of the table
select SUM(salary) from students;
+-------------+
| SUM(salary) |
+-------------+
| 89019 |
+-------------+
to see the only few rows in the tables we can set limit to them
select name,age,salary from students LIMIT 3;
+--------+------+--------+
| name | age | salary |
+--------+------+--------+
| kiran | 25 | 25000 |
| sathia | 26 | 10000 |
| john | 28 | 14545 |
+--------+------+--------+
to know the average of the ages
select AVG(age) from students;
+----------+
| AVG(age) |
+----------+
| 27.3333 |
+----------+
to count the number of entries in mysql
elect COUNT(age) from students;
+------------+
| COUNT(age) |
+------------+
| 6 |
+------------+
to see the version of the mysql
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.41 |
+-----------+
to see the time and date
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-12-02 14:37:02 |
+---------------------+
1 row in set (0.00 sec)
Update command in mysql
update students SET salary=5000,age=26 WHERE name="smith";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from students;
+----------+------+--------+
| name | age | salary |
+----------+------+--------+
| kiran | 25 | 25000 |
| sathia | 26 | 10000 |
| john | 28 | 14545 |
| jenefier | 26 | 12454 |
| smith | 26 | 5000 |
| sachin | 30 | 12456 |
+----------+------+--------+
mysql> delete from students where name="smith";
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----------+------+--------+
| name | age | salary |
+----------+------+--------+
| kiran | 25 | 25000 |
| sathia | 26 | 10000 |
| john | 28 | 14545 |
| jenefier | 26 | 12454 |
| sachin | 30 | 12456 |
+----------+------+--------+
