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
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
Posts
