This comes straight from my notes so you might not discover numerous documentaries round it however all of them are very helpful. If it’s essential to know extra about any command you may all the time drop a word or look into assets I’ve shared on the finish of this text.
./mysqladmin -u root shutdown
./mysqladmin –host=localhost –port=3305 -u root shutdown //for second occasion listening on port 3305
4. Viewing MySQL course of checklist and killing the offending MySQL course of
That is extraordinarily helpful to see which question is working on which host, from which location question has fired, which question has locked which desk, and so forth.
$ cd mysql/bin
./mysqladmin -u root processlist
Kill MySQL processes
$ cd mysql/bin
./mysqladmin -u root kill ProcessID
However, if you’re new to the SQL world, it is higher to start out with a complete SQL course like The Full SQL Bootcamp course by Jose Portilla on Udemy. That can aid you to be taught SQL higher and faster, and these sorts of articles can even make extra sense upon getting some SQL data beneath your belt.
5. The best way to see MySQL assist
You’ll be able to see the MySQL assist from the command immediate itself like the next, you simply want to know it.
mysql> assist alter;
6. Restore a desk in MySQL
REPAIR TABLE TableName
7. Copying knowledge from one desk to a different
That is very helpful if you find yourself altering the desk and also you wish to take the backup of knowledge.
insert into ORDERS_TMP choose * from ORDERS
8. Dropping columns from the desk
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;
9. Including Keys(Indexes) to a desk
alter desk ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (right here CLIENT_ID is a column in ORDers desk)
10. modifying a column
That is helpful in case you wish to modify knowledge sort or measurement of a specific column
$ alter desk ORDERS modify column BRAND varchar(15) default NULL
11. Rename Desk
That is once more a helpful methodology for making a backup of a desk earlier than enjoying with it.
Renaming the brand new desk to the unique title:
mysql> ALTER TABLE new_table RENAME old_table;
Listed here are some extra MySQL instructions from that are very helpful for anybody working with the MySQL database. This is very helpful for software builders who’s going to make use of the MySQL database for his or her purposes.
12. Rising no of connections for MySQL
You’ll be able to improve this worth in the principle config file (like /and so forth/my.cnf) utilizing this syntax:
[mysqld]
set-variable=max_connections=250
13. Myisamchk command
in case you run “‘myisamchk ORDERS.MYI” it is going to examine whether or not ORDERS desk is corrupted or not. if corrupted it is going to say
MyISAM-table ‘ORDERS.MYI’ is corrupted Repair it utilizing swap “-r” or “-o”
to repair it you may run
“‘myisamchk -r ORDERS.MYI”
If you wish to be taught extra about this command different MySQL-specific instructions, I additionally recommend you undergo MySQL Fundamentals by Pinal Dave on Pluralsight. It is without doubt one of the finest programs to be taught MySQL when you have a Pluralsight membership.
14. UNIX_TIMESTAMP operate
SELECT UNIX_TIMESTAMP(‘1997-10-04 22:23:00’); -> 875996580
give the date and can return no of seconds, it returns the worth of the argument as seconds since ‘1970-01-01 00:00:00’ UTC
15. Diff between 2 dates in MySQL
mysql> SELECT TIMEDIFF(‘1997-12-31 23:59:59.000001′,’1997-12-30 01:01:01.000002’);
-> ’46:58:57.999999′
16. Returns Time to seconds
Returns the time argument, transformed to seconds.
mysql> SELECT TIME_TO_SEC(’22:23:00′);
-> 80580
17. UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
If referred to as with no argument, returns a Unix timestamp (seconds since ‘1970-01-01 00:00:00’ UTC) as an unsigned integer. If UNIX_TIMESTAMP() is named with a date argument, it returns the worth of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.
The date could also be a DATE string, a DATETIME string, a TIMESTAMP, or a quantity within the format YYMMDD or YYYYMMDD. The server interprets date as a worth within the present time zone and converts it to an inside worth in UTC. Shoppers can set their time zone
18. TAKING THE BACKUP OF A TABLE
$ CREATE TABLE ORDER_TEMP SELECT * FROM ORDER;
19. Working mysql question from unix command immediate
mysql -u root -h <hostname> <database title >-e “UPDATE ORDERT SET TYPE =’PARTIAL’ WHERE TYPE=’FULL’
-h for host and –e for expression.
20. Exhibiting checklist of databases in MySQL
mysql> present databases;
Hello Guys, listed here are some extra MySQL instructions which are helpful for day 2-day work.
21. Methodology for changing the present timestamp so far:
choose from_unixtime(left(1201159475416, 10));
this methodology is used to transform the timestamp to the date-time format in MySQL, the left() methodology will return 10 char from the required string if we retailer the timestamp worth in a millisecond.
mysql> choose from_unixtime(left(1210916129820 , 10))
-> ;
+——————————————+
| from_unixtime(left(1210916129820 , 10)) |
+——————————————+
| 2008-05-16 01:35:29 |
+——————————————+
1 row in set (0.00 sec)
22. Viewing MySQL command historical past
There’s one hidden file referred to as .mysql-history, on which all instructions are saved which we typed in MySQL console. It usually resides in the house listing.
23. Taking a backup of the MyISAM database in MySQL
One solution to take the backup of a database is to repeat the information.MYD,.MYI and .frm, this fashion you may write scripts that may copy the database from one server to one other, merge databases, and so forth.
24. To take away a column from a desk
$ alter desk ice cream drop column taste ;
25. Altering the scale of a column and datatype of a column in MySQL
$ alter desk individuals modify title VARCHAR(35) ;
$ alter desk ORDERS modify CLIENT varchar(255) default NULL;
26. Displaying index from a MySQL desk
mysql> SHOW INDEX FROM database.ORDERS;
27. Getting MySQL server model
you should use a way referred to as model()
mysql> choose model();
+———–+
| model() |
+———–+
| 3.23.58 |
+———–+
1 row in set (0.02 sec)
28. mysqldump Command
The mysqldump consumer is a backup program o It may be used to dump a database or a set of databases for backup or switch to a different SQL server (not essentially a MySQL server). The dump usually incorporates SQL statements to create the desk, populate it, or each.
29. To take a dump of a MySQL table-use beneath command
~/MySQL/bin/mysqldump -u root database_name ORDERS > orders.txt
1. command to dumb solely tables definitions, not the info “ used the command” if the trail is just not set then it’s essential to run that command from MySQL/bin listing
./mysqldump -d -u root database_name ORDERS , CLIENTS , COMPANY > ~/tmp/check.database.sql
2. Command to recreate desk from that file
mysql -u root database_name < ~/tmp/check.database.sql
Different Database and SQL Articles you might like
- The best way to discover duplicate data from a desk? (answer)
- 5 Internet sites to be taught SQL on-line for FREE? (useful resource)
- 5 Books to Study SQL? (books)
- The best way to be part of greater than two tables in a single SQL question? (answer)
- The best way to discover the size of String in MSSQL? (instance)
- The distinction between char, varchar, nchar, and nvarchar in SQL SERVER? (reply)
- The best way to create an Identification column in SQL Server? (instance)
- 5 suggestions whereas migrating from Oracle to Microsoft SQL Server? (suggestions)
- The best way to exchange NULL with empty String in SQL Server? (tutorial)
- Distinction between row_number, rank, and dense_rank in SQL Server? (reply)
- What’s the distinction between WHERE and HAVING clause in SQL Server? (reply)
- The best way to break up String in SQL SERVER? (reply)
- 50 SQL Server Cellphone Interview Query (SQL server questions)
- The best way to discover the second highest wage of an worker in SQL Server? (question)
Thanks for studying this text to date. When you discover these MySQL instructions helpful then please share them with your mates and colleagues. When you’ve got any questions or suggestions then please drop a word.
