Basic Mysql configuration

Basic Mysql configuration

In Windows:
. Enable mysql from cmd:
System Properties > Advance > Environment Variables > (scroll down to) Path > Edit > ;E:\Program Files\xampp\mysql\bin (add this)

. create password for root user by using database location. open run > cmd. and login as root user,
. 1sr way:
~# mysqladmin -u root password "123456"
~# mysql -u root -p
~# sudo /etc/init.d/mysql restart

. 2nd way using mysqladmin
~# mysql -u root
~# UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 2 rows affected (0.22 sec)
Rows matched: 2  Changed: 2  Warnings: 0

~# FLUSH PRIVILEGES;
~# mysql -u root -p
~# database

Here,
mysql.user = "user" table in "mysql" database
Password = "Password" collum in "mysql" database
User = 'root' > update all row value's which name is root
FLUSH PRIVILEGES = re-read the tables

. Creating users and setting their permissions:
~# CREATE USER web@localhost;
~# UPDATE mysql.user SET PASSWORD = PASSWORD('NEWPASSWORD') WHERE user = 'web';
~# GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, INDEX, ALTER, CREATE TEMPORY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO web@localhost;
~# FLUSH PRIVILEGES;

~# CREATE USER admin@localhost;
~# UPDATE mysql.user SET PASSWORD = PASSWORD('290909') WHERE USER = 'admin';
OK, 1 row affected (0.00 sec)
atched: 1  Changed: 1  Warnings: 0
~# GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
~# FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

If you want to create a database and set up tables for the same use the following two sql commands:
1. CREATE DATABASE - create the database
2. CREATE TABLE - create the table
3. INSERT - To add/insert data to table

. Creating and Droping a database:
~# CREATE DATABASE sales;
~# SHOW DATABASE;
~# DROP DATABASE sales;
~# SHOW DATABASE;

. Creating database:
~# CREATE DATABASE test;

. Creating tables:
~# USE test;
~# CREATE TABLE test_customer (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
address VARCHAR(255),
city VARCHAR(255),
state CHAR(2),
zip CHAR(10),
email VARCHAR(20)
);

~# SHOW TABLES;
+----------------+
| test_customer  |
+----------------+
1 row in set (0.20 sec)

. Display the rules for the columns
~# DESRIBE test_customer;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  |     | NULL    |                |
| address | varchar(255) | YES  |     | NULL    |                |
| city    | varchar(255) | YES  |     | NULL    |                |
| state   | char(2)      | YES  |     | NULL    |                |
| zip     | char(10)     | YES  |     | NULL    |                |
| email   | varchar(20)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.30 sec)

. Now insert data to the table "test_customer"
~# INSERT INTO test_customer (id,name,address,city,state,zip,email) VALUES (1,"Md. Mahidul Hasan","Tolarbag","Dhaka","Mirpur-1","1216","mahidul24@gmail.com");
~# INSERT INTO test_customer (id,name,address,city,state,zip,email) VALUES (2,"Mrs. Maya Hasan","Tolarbag","Dhaka","Mirpur-1","1216","mahidul24@gmail.com");

. Display the table
~# SELECT * FROM test_customer;
+----+-------------------+----------+-------+-------+------+---------------------+
| id | name              | address  | city  | state | zip  | email               |
+----+-------------------+----------+-------+-------+------+---------------------+
|  1 | Md. Mahidul Hasan | Tolarbag | Dhaka | Mi    | 1216 | mahidul24@gmail.com |
|  2 | Mrs. Maya Hasan   | Tolarbag | Dhaka | Mi    | 1216 | mahidul24@gmail.com |
+----+-------------------+----------+-------+-------+------+---------------------+
2 rows in set (0.86 sec)

. Droping database:
~# DROP TABLE test_customer;
~# SHOW TABLES;

. Data type concept in mysql:
1. Numeric - for numbers
Integer: 1,2,3
  Floating point: 1.2312.., 2.123444...
Fixed point (after decimel): 1.24, 7.2

2. String - for words and text
Fixed: always should be fixed (two or three ... like state=) same size
Variable: diffrent values and sizes. Uses in name or address...
CHAR: for textual data
BINARY: for non-textual data. Fixed lenth.
VARBINARY: for non-textual data. Variable lenth.

3. Large storage - for documents and files
BLOB: binary large object for non-text data
Tinyblob - up to 256 bytes
blob - up to 64K bytes
  Mediumblob - up to 16M bytes
Longblob - up to 4G bytes

TEXT: for text type
Tinytext - up to 256 bytes
Text - up to 64K bytes
  Mediumtext - up to 16M bytes
Longtext - up to 4G bytes

4. Date and time
5. Bit values - for binary logical values and flags (declair with BIT). Like 11110000 means 240.
6. Enumeration - for mnemonic values