MySQL is a open source Relational Database Management System. It is very fast reliable and flexible Database Management System. It provides a very high performance and it is multi threaded and multi user Relational Database management system. MySQL is one of the most popular relational database Management System on the web. The MySQL Database has become the world’s most popular open source Database, because it is free and available on almost all the platforms. The MySQL can run on Unix , window, and Mac OS. MySQL is used for the internet applications as it provides good speed and is very secure. MySQL was developed to manage large volumes of data at very high speed to overcome the problems of existing solutions. MySQL can be used for verity of applications but it is mostly used for the web applications on the internet.
MySQL Advantages:
- Reliability
- Performance
- Open Source
- Cross-Platform support
changing root password for mysql first time, when it is installed.
[root@client129 ~]# mysqladmin -u root password redhat
ACCESSING MYSQL:
[root@client129 ~]# mysql -u root -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.61 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
DATABASE:
mysql> create database issue;
Query OK, 1 row affected (0.01 sec)
mysql> use issue;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| issue |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database issue;
Query OK, 0 rows affected (0.00 sec)
TABLES:
mysql> create table person(
-> person_id SMALLINT UNSIGNED NOT NULL,
-> first_name VARCHAR(45) NOT NULL,
-> last_name VARCHAR(45) NOT NULL,
-> PRIMARY KEY (person_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-----------------+
| Tables_in_issue |
+-----------------+
| person |
+-----------------+
1 row in set (0.00 sec)
mysql> desc person;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
+------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Altering table name:
mysql> ALTER TABLE person RENAME TO person1;
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+-----------------+
| Tables_in_issue |
+-----------------+
| person1 |
+-----------------+
1 row in set (0.00 sec)
Altering field name and type:
mysql> desc person;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
+------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE person1 CHANGE last_name surname varchar(30);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person1;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| surname | varchar(30) | YES | | NULL | |
+------------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Adding or removing fields:
mysql> ALTER TABLE person1 ADD age smallint(3) unsigned not null;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person1;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| surname | varchar(30) | YES | | NULL | |
| age | smallint(3) unsigned | NO | | NULL | |
+------------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE person1 DROP first_name;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person1;
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| surname | varchar(30) | YES | | NULL | |
| age | smallint(3) unsigned | NO | | NULL | |
+-----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
INSERT'ing a single record:
mysql> INSERT INTO person1 (person_id,surname,age) VALUES (1,'shin',88);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM person1;
+-----------+---------+-----+
| person_id | surname | age |
+-----------+---------+-----+
| 1 | shin | 88 |
+-----------+---------+-----+
1 row in set (0.00 sec)
INSERT'ing multiple records:
mysql> INSERT INTO person1 (person_id,surname,age) VALUES (2,'sam',23),
> (3,'raja',22),
> (4,'ram',22),
> (5,'raj',22);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from person1;
+-----------+---------+-----+
| person_id | surname | age |
+-----------+---------+-----+
| 1 | shin | 88 |
| 2 | sam | 23 |
| 3 | raja | 22 |
| 4 | ram | 22 |
| 5 | raj | 22 |
+-----------+---------+-----+
5 rows in set (0.00 sec)
Deleting recored(s):
mysql> DELETE FROM person1 WHERE age < 22;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from person1;
+-----------+---------+-----+
| person_id | surname | age |
+-----------+---------+-----+
| 1 | shin | 88 |
| 2 | sam | 23 |
| 3 | raja | 22 |
| 4 | ram | 22 |
| 5 | raj | 22 |
+-----------+---------+-----+
5 rows in set (0.00 sec)
mysql> DELETE FROM person1 WHERE age <= 22;
Query OK, 3 rows affected (0.04 sec)
mysql> select * from person1;
+-----------+---------+-----+
| person_id | surname | age |
+-----------+---------+-----+
| 1 | shin | 88 |
| 2 | sam | 23 |
+-----------+---------+-----+
2 rows in set (0.00 sec)
UPDATING:
mysql> UPDATE person1 SET age = 88 WHERE age = 99 OR first_name = 'paul';
Query OK, 1 row affected, 2 warnings (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 2
mysql> SELECT * FROM person;
+-----------+------------+-----------+-----+
| person_id | first_name | last_name | age |
+-----------+------------+-----------+-----+
|
1 | sang | shin | 88 ||
2 | kelly
| jones | 22 |
|
3 | jack
| kennedy | 56 |
|
4 | paul
| kennedy | 88 |
+-----------+------------+-----------+-----+
4 rows in set (0.00 sec)
Retrieving some fields selectively:
mysql> SELECT surname,age FROM person1;
+---------+-----+
| surname | age |
+---------+-----+
| shin | 88 |
| sam | 23 |
+---------+-----+
2 rows in set (0.00 sec)
Reading and Executing SQL Script File:
mysql> SOURCE c:/tmp/student.sql
Query OK, 0 rows affected (0.10 sec)
grant all privileges on riversidecp_wp.* to @localhost identified by 'crymeariver';
dbname riversidecp_wp
username riversidewp
password crymeariver
Query to know the database size:
SELECT table_schema "db-name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
No comments:
Post a Comment