MySQL script to find the post "viagra" in Wordpress. You can execute the below command,
------------
SELECT ID FROM wp_posts WHERE post_content LIKE '%viagra%';
-----------
To find the complete post content, execute the command,
------------------
SELECT * FROM wp_posts WHERE ID=711;
------------------
Where you will get the ID from the above command.
To create, Insert, do a query and find the contents inside a table, execute the below commands.
======================
* mysql> create table t1 (Slno INT, Name VARCHAR(10));
* mysql> INSERT INTO t1 (Slno,Name) VALUES (19,'test');
* mysql> select * from t1; <-- To find the complete table contents.
* mysql> select * from t1 where Name='test10'; <-- To find the row corresponding to column with name=test10. The output will be in form of,
+------+--------+
| Slno | Name |
+------+--------+
| 10 | test10 |
+------+--------+
1 row in set (0.00 sec)
mysql> desc t1; <= Description of table.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Slno | int(11) | YES | | NULL | |
| Name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
========================
Connection between two tables.
mysql> select * from t2;
+------+
| Slno |
+------+
| 20 |
| 21 |
| 19 |
+------+
mysql> select * from t1 ;
+------+--------+
| Slno | Name |
+------+--------+
| 19 | test21 |
| 20 | test12 |
| 21 | test22 |
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+------+--------+
13 rows in set (0.00 sec)
mysql>
mysql> select * from t1 where Slno not in(select * from t2); <=== This shows all the contents from table "t1" not containing columns in t2.
+------+--------+
| Slno | Name |
+------+--------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+------+--------+
10 rows in set (0.01 sec)
==========================
To find the syntax of the table, execute the below command,
==============
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`Slno` int(11) DEFAULT NULL,
`Name` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
=================
MySQL table creation using text file.
-------------------------------------
[root@localhost ~]# cat mytest.sql
create database mytest;
use mytest;
create table t1 (Name VARCHAR(10), SLNO INT(10));
INSERT INTO mytest.t1 (Name,SLNO) VALUES (20,'test20');
INSERT INTO mytest.t1 (Name,SLNO) VALUES (21,'test21');
INSERT INTO mytest.t1 (Name,SLNO) VALUES (22,'test22');
\q;
[root@localhost ~]# mysql < mytest.sql
[root@localhost ~]# mysql -e "select * from mytest.t1"
+------+------+
| Name | SLNO |
+------+------+
| 20 | 0 |
| 21 | 0 |
| 22 | 0 |
+------+------+
[root@localhost ~]#
-------------------------------
How to find the count of rows in a table.
To get this done, execute the below command.
*select count(*) from <tablename>;
How to lock the table.
Read lock:
Syntax : read lock <tablename> read;
The session that holds the lock can read the table (but not write it).
ex:
mysql> lock table info read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+------+------+------+
| Name | Sex | AGE |
+------+------+------+
| Jeff | Male | 22 |
+------+------+------+
1 row in set (0.01 sec)
mysql> update info set AGE=30 where Name='jeff';
ERROR 1099 (HY000): Table 'info' was locked with a READ lock and can't be updated
mysql>
Write lock:
Syntax :
The session that holds the lock can read and write the table.
Only the session that holds the lock can access the table. No other session can access it until the lock is released.
ex:
mysql> lock table info write;
Query OK, 0 rows affected (0.00 sec)
mysql>
If I access info from other terminal, it is not possible or read or write to it.
mysql> select * from info;
+------+------+------+
| Name | Sex | AGE |
+------+------+------+
| Jeff | Male | 22 |
+------+------+------+
1 row in set (25.28 sec) <====== The table was write locked. When I unlocked after 25.28 sec, the mysql> table was accessible.
To release locks, execute 'unlock' utility,
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
How to change MySQL engine
If a table is created, default engine will be associated with the table.
For ex:
mysql> create table test (No INT(10));
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`No` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | <======== Engine is MyISAM by default.
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
The backend files contain MyISAM index, data and format files. "test.MYI" "test.MYD" "test.frm"
It is possible to alter the engine using the MySQL query,
mysql> alter table test engine= InnoDB;
Here, the engine has changed to InnoDB and the index files "test.MYI" and "test.MYD" are vanished. Insted the index file will be updated in "ibdata", which is the InnoDB index file.
To change the default engine as InnoDB, change the default storage engine in the configuration file(my.cnf) as below,
------
default-storage-engine=Innodb
------
Restart MySQL to make the changes. Once it is complete, create a table testing, and verify the engine type as,
mysql> create table testing (No INT(10));
Query OK, 0 rows affected (0.10 sec)
mysql> show create table testing;
+---------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------+
| testing | CREATE TABLE `testing` (
`No` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | <== Default storage engine changed to InnoDB.
+---------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
------------
SELECT ID FROM wp_posts WHERE post_content LIKE '%viagra%';
-----------
To find the complete post content, execute the command,
------------------
SELECT * FROM wp_posts WHERE ID=711;
------------------
Where you will get the ID from the above command.
To create, Insert, do a query and find the contents inside a table, execute the below commands.
======================
* mysql> create table t1 (Slno INT, Name VARCHAR(10));
* mysql> INSERT INTO t1 (Slno,Name) VALUES (19,'test');
* mysql> select * from t1; <-- To find the complete table contents.
* mysql> select * from t1 where Name='test10'; <-- To find the row corresponding to column with name=test10. The output will be in form of,
+------+--------+
| Slno | Name |
+------+--------+
| 10 | test10 |
+------+--------+
1 row in set (0.00 sec)
mysql> desc t1; <= Description of table.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Slno | int(11) | YES | | NULL | |
| Name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
========================
Connection between two tables.
mysql> select * from t2;
+------+
| Slno |
+------+
| 20 |
| 21 |
| 19 |
+------+
mysql> select * from t1 ;
+------+--------+
| Slno | Name |
+------+--------+
| 19 | test21 |
| 20 | test12 |
| 21 | test22 |
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+------+--------+
13 rows in set (0.00 sec)
mysql>
mysql> select * from t1 where Slno not in(select * from t2); <=== This shows all the contents from table "t1" not containing columns in t2.
+------+--------+
| Slno | Name |
+------+--------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+------+--------+
10 rows in set (0.01 sec)
==========================
To find the syntax of the table, execute the below command,
==============
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`Slno` int(11) DEFAULT NULL,
`Name` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
=================
MySQL table creation using text file.
-------------------------------------
[root@localhost ~]# cat mytest.sql
create database mytest;
use mytest;
create table t1 (Name VARCHAR(10), SLNO INT(10));
INSERT INTO mytest.t1 (Name,SLNO) VALUES (20,'test20');
INSERT INTO mytest.t1 (Name,SLNO) VALUES (21,'test21');
INSERT INTO mytest.t1 (Name,SLNO) VALUES (22,'test22');
\q;
[root@localhost ~]# mysql < mytest.sql
[root@localhost ~]# mysql -e "select * from mytest.t1"
+------+------+
| Name | SLNO |
+------+------+
| 20 | 0 |
| 21 | 0 |
| 22 | 0 |
+------+------+
[root@localhost ~]#
-------------------------------
How to find the count of rows in a table.
To get this done, execute the below command.
*select count(*) from <tablename>;
How to lock the table.
Read lock:
Syntax : read lock <tablename> read;
The session that holds the lock can read the table (but not write it).
ex:
mysql> lock table info read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+------+------+------+
| Name | Sex | AGE |
+------+------+------+
| Jeff | Male | 22 |
+------+------+------+
1 row in set (0.01 sec)
mysql> update info set AGE=30 where Name='jeff';
ERROR 1099 (HY000): Table 'info' was locked with a READ lock and can't be updated
mysql>
Write lock:
Syntax :
The session that holds the lock can read and write the table.
Only the session that holds the lock can access the table. No other session can access it until the lock is released.
ex:
mysql> lock table info write;
Query OK, 0 rows affected (0.00 sec)
mysql>
If I access info from other terminal, it is not possible or read or write to it.
mysql> select * from info;
+------+------+------+
| Name | Sex | AGE |
+------+------+------+
| Jeff | Male | 22 |
+------+------+------+
1 row in set (25.28 sec) <====== The table was write locked. When I unlocked after 25.28 sec, the mysql> table was accessible.
To release locks, execute 'unlock' utility,
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
How to change MySQL engine
If a table is created, default engine will be associated with the table.
For ex:
mysql> create table test (No INT(10));
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`No` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | <======== Engine is MyISAM by default.
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
The backend files contain MyISAM index, data and format files. "test.MYI" "test.MYD" "test.frm"
It is possible to alter the engine using the MySQL query,
mysql> alter table test engine= InnoDB;
Here, the engine has changed to InnoDB and the index files "test.MYI" and "test.MYD" are vanished. Insted the index file will be updated in "ibdata", which is the InnoDB index file.
To change the default engine as InnoDB, change the default storage engine in the configuration file(my.cnf) as below,
------
default-storage-engine=Innodb
------
Restart MySQL to make the changes. Once it is complete, create a table testing, and verify the engine type as,
mysql> create table testing (No INT(10));
Query OK, 0 rows affected (0.10 sec)
mysql> show create table testing;
+---------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------+
| testing | CREATE TABLE `testing` (
`No` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | <== Default storage engine changed to InnoDB.
+---------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>