Archive for the ‘MySQL’ Category

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.

Copy a table structure in MySQL

We can clone or copy a table structure in MySQL using LIKE or AS operators. Using LIKE: We can use CREATE TABLE … LIKE to create an empty table based on the definition of another table, including any column fields and indexes defined in the original table: Syntax: CREATE TABLE new_table LIKE original_table;

Change USER password in MySQL

We can the user password in MySQL, using UPDATE, SET, ALTER command. To change password, current login user must have granted permissions on user table of mysql database and must have UPDATE, SET and ALTER permissions on user table. To reset password in MySQL 5.6 or older versions(This statement not works in MySQL 5.7 or […]

emp table script MySQL

Standard emp table script for MySQL Server 1) Create table structure 2) Insert data into emp table

Change root password in mysql 5.7

In MySQL 5.7, the password field in mysql.user table field is removed. It is replaced authentication_string. Steps: 1. First select mysql database, which stores users information. mysql>use mysql; 2. And then show the tables. We will find the user table. mysql> show tables; 3. To get user table fields: mysql> describe user;

Powered by k2schools