Posts Tagged ‘MySQL’

Remove Duplicates in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). Find the duplicates in MySQL. Be careful about removing duplicates. Before deleting records(duplicates) of table, its better to run the query on copy of the table. Following table named ’emp1′ contains duplicates in the column empno and empname

Find Duplicate Rows in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). We can find duplicates in different ways. Following table named ’emp1′ contains duplicates in the column empno and empname

How to stop and start MySQL server

To Stop MySQL Server #sudo mysqld stop #service mysql stop #/etc/init.d/mysql stop #sudo service mysql stop #sudo /etc/init.d/mysql stop #/usr/local/mysql/bin/mysqladmin -u root -p shutdown To Start MySQL Server #sudo mysqld start # service mysql start # /etc/init.d/mysql start# # sudo service mysql start # sudo /etc/init.d/mysql start To restart MySQL Server without running stop and […]

Largest Tables in MySQL

Largest Tables in MySQL: Following query will give largest tables of entire MySQL. SELECT table_schema as ‘Database’, table_name AS ‘Table’, round(((data_length + index_length) / 1024 / 1024), 2) ‘Size in MB’ FROM information_schema.TABLES ORDER BY (data_length + index_length); Output

How to get table size in MySQL

How to get table size in MySQL: We can get this information from the schema ‘information_schema’. Query: SELECT table_name AS ‘Table’, round(((data_length + index_length) / 1024 / 1024), 2) ‘Size in MB’ FROM information_schema.TABLES WHERE table_schema = “sakila” AND table_name = “rental”;

Difference between delete and truncate

Delete: Whenever we are using delete from tablename, then automatically deleted data internally stored in buffer. We can get it back by using rollback command.

How to execute script file in MySQL

script file contains list of commands saved in with .sql/.txt or other extension. To import or export file in MySQL, use must be granted FILE permission. Steps to execute script file in MySQL: 1. Create a file(.sql extension): We are going to get list of users in MySQL using the file prvr@ubuntu:~$ cat > getlistofusers.sql […]

Rename table in MySQL

RENAME TABLE is used to change the name of table. We must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

Powered by k2schools