Tuesday, 7 November 2017

Write command to import an mysql file

Run below command on terminal to import a mysql database from file
mysql -u username -p database_name < file.sql 


How to get a list of MySQL user accounts

To get the list of MySQL user accounts run below query:
SELECT User FROM mysql.user;

Monday, 24 July 2017

How to delete records from multiple tables in MYSQL with one query

Delete records from multiple tables with one query.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL
);

CREATE TABLE table_b (
  id INT(11) DEFAULT NULL
);

INSERT INTO table_a VALUES 
  (2),
  (3),
  (1);

INSERT INTO table_b VALUES 
  (2),
  (5),
  (1);

 

Delete records:

DELETE t1, t2
FROM
  table_a t1
JOIN table_b t2
  ON t1.id = t2.id
WHERE
  t1.id = 1;

 

Check results:

SELECT * FROM table_a;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+

SELECT * FROM table_b;
+------+
| id   |
+------+
|    2 |
|    5 |
+------+

How to copy records from one table too another in Mysql ?

In this example we are going to see the mysql query to copy data from one table too another.
 
INSERT INTO table_b SELECT id, column_a FROM table_a;

What is the DELIMITER in MySQL and what it’s used for?

The DELIMITER command is used to change the standard delimiter of MySQL commands. The standard command delimiter is the ';' character that defines the end of the query.

Example:
INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (2, 'NICK', 'WAHLBERG', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (5, 'JOHNNY', 'LOLLOBRIGIDA', '2006-02-15 04:34:33');

Now let’s have a look at the following script. It consists of several INSERT queries, procedure creating and several more INSERT queries.

INSERT INTO actor VALUES (6, 'BETTE', 'NICHOLSON', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (8, 'MATTHEW', 'JOHANSSON', '2006-02-15 04:34:33');
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
INSERT INTO actor VALUES (9, 'JOE', 'SWANK', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33');

When executing a script, MySQL client parses queries by delimiting them by ';' and sends them to a server.

If we execute the script in whole, we get the syntax error - 'ERROR 1064 (42000): You have an error in your SQL syntax;...'. This is because the MySQL client has extracted the following command and tried to execute it.


CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');

The point is that some objects in MySQL may contain executable code in the BEGIN...END clause and commands in the code are delimited by the ';' character; such objects may include: stored procedures and functions, triggers, and events.

In this case, somehow we have to extract the create procedure command in whole. There is the DELIMITER client side command that allows you to set its conditional query delimiter. Any character or string can be set as a delimiter. The most common are $$ and //. In our example the ‘$$’ string is used.

Below is the script with using the DELIMITER command -

INSERT INTO actor VALUES (6, 'BETTE', 'NICHOLSON', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (8, 'MATTHEW', 'JOHANSSON', '2006-02-15 04:34:33');
-- Set new delimiter '$$'
DELIMITER $$
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$ -- End of command
-- Set default delimiter ';'
DELIMITER ;
INSERT INTO actor VALUES (9, 'JOE', 'SWANK', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33');

Now our script will be executed with no errors and the create procedure command will succeed. It's also important to note that the DELIMITER command may be set only once for the whole set of commands.

The example -

-- Set new delimiter '$$'
DELIMITER $$
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$ -- End of command
CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT)
  RETURNS INT(11)
  READS SQL DATA
BEGIN
  DECLARE v_customer_id INT;
  DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
  SELECT customer_id INTO v_customer_id FROM rental
    WHERE return_date IS NULL AND inventory_id = p_inventory_id;
  RETURN v_customer_id;
END
$$ -- End of command
CREATE TRIGGER ins_film
  AFTER INSERT
  ON film
  FOR EACH ROW
BEGIN
  INSERT INTO film_text (film_id, title, description) VALUES (NEW.film_id, NEW.title, NEW.description);
END
$$ -- End of command
-- Set default delimiter ';'
DELIMITER ;

Source: http://buysql.com/mysql/42-delimiter-mysql.html


Tuning performance of MySQL Server Database

MySQL can be a rock solid, lighting quickly database server which has been designed for two factors pace and efficiency. It could be the Ferrari of databases: Light weight, quickly and Built for the higher pace tracks! I nonetheless hear an awful lot of stories from owners whose databases are running too slow. In my experience, the three principal places to seem for issues are:

  1. Faulty Database Design
  2. Lousy Queries
  3. Server factors

Faulty Database Design

Correct database design might be the single most essential factor for the ensuring efficiency and maintainability with the database. Right here is what you have to answer when designing a desk: Can I decrease the size of knowledge that every row will have? Right here is what you are able to do:

  1. Use unsigned numeric values when the application is not going to save negative numbers. Like the quantity ordered of an item in an e-commerce application is never going to become -$125.
  2. Use Variable length values as an alternative to fixed length worth i.e. utilized varchar in place of char.
  3. Tend not to use unnecessarily big field sizes. For most e-commerce application unsigned small int is extra than sufficient to retailer inventory count. A field described as unsigned small int can keep a max benefit of 65535.
  4. Don't ignore normalization; its helps prevent unnecessary repetition of info. The part B of that is, don't overuse normalization. If the desk is not going to grow in size substantially, there's no point in normalization. For instance, if the user desk has just 20 rows (i.e. 20 employees in an organization), all attempts of normalization are wasted.
  5. Use Keys. Don't decide keys by The customer id has to become indexed from the order desk. If the order desk is becoming searched 90% of your times by order date, it makes much more sense to index order date.

Remember, how a desk will probably be employed must determine how it truly is designed. Spending time right here will save years of frustration.

Lousy Queries

It sounds too excellent to become true but you won't believe the number of developers out there who totally suck at writing queries. You will discover two kinds of undesirable queries:

  • Unnecessary Queries: These are the queries that shouldn't have been made inside initial place. The only method to avoid it can be asking, Do I actually need to have this info?
  • Inefficient Queries: These are the queries that usually do not use the underlying disk structure or MySQL functions inside correct way.

Right here is actually a starting point to begin looking at issue areas:

  1. Unnecessary utilization of Select * statements when the entire processing is becoming carried out on a single column. The far more info is fetched from the server the extra work MySQL has to do and much more bandwidth it takes.
  2. Employing sub-query as an alternative to a join. On a correctly designed database, joins are incredibly rapidly. Making use of sub-queries just shows a lack of knowledge.
  3. Improper use of Keys. It can be especially valid for range checks. Remember to use the Explain statement to check the utilization of keys and then use the use crucial statement in your where clauses to force critical utilization.

Server Factors

Every thing carried out correctly, there nonetheless may well be some server factors that may possibly be causing the technique to become slow. These are:

  1. Hardware associated
  2. Server configuration associated

Right here is what you are able to do about the hardware:

  1. The extra RAM is the method the much better it can be. MySQL often fetches info from the RAM and a lot more the RAM is on the technique, the much better it can be.
  2. Purchase the fastest achievable RAM! A slower RAM is just irony.
  3. As soon as you will be settled with the RAM size and velocity, seem for processing velocity. MySQL can use multiple processors.

As soon as you might be satisfied with the hardware, you will find a set of variables in my.cnf that you should seem at:

  1. key_buffer_size: This describes the memory accessible to save the index keys. The default is 8 MB but you may set it to 25% with the RAM.
  2. query_cache_size: This worth is by default ..! if you might have a great deal of repeating queries like in reporting applications and so on, make certain you set this benefit largely.
  3. table_open_cache: This determines the number of desk descriptors that MySQL will maintain inside the cache. The default worth is 64. But, if you've 100 users accessing a disk concurrently then this worth require to at-least be 100. You also need to take into considerations joins and so on.

Source: http://www.articlesfactory.com/articles/computers/improving-mysql-server-database-performance.html


Querying Multiple Tables in MySQL

One of the drawbacks to normalization to the third form (3NF) is more cumbersome data extraction due to the greater number of tables. These require careful linking via JOIN clauses. Improper table joining can easily result in erroneous results or even in the dreaded Cartesian Product. In today’s article, we’ll explore how table joins are achieved in MySQL.

A Tale of Two Join Styles

SQL was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 and the International Organization for Standardization (ISO) in 1987.

Using ANSI-89 JOIN syntax, tables were joined on common fields using the equals symbol (=):

SELECT o.OrderID, 
          od.ProductID
   FROM   Orders AS o, 
          dbo.OrderDetails AS od
   WHERE  o.OrderDate >= '20091001'
   AND    o.OrderID = od.ProductID;

That style was eventually eclipsed by a very different one that was introduced in the new SQL-92 standard. In general, developers found this style to be more readable than its predecessor by separating the joining criteria from the filter criteria. Here is the above query expressed using an SQL-92 join:

SELECT o.OrderID, 
          od.ProductID
   FROM  Orders AS o
   INNER JOIN dbo.OrderDetails AS od
           ON o.OrderID = od.ProductID
   WHERE o.OrderDate >= '20091001';

Although some of the ANSI 86 syntax, such as OUTER JOINs using "*=" and "=*" has been deprecated by some SQL vendors, some developers continue to use it in MySQL, where it is still supported. There is of course no harm in doing so, but for the purposes of this tutorial, I’ll be focussing on the SQL-92 standard, since it has been the recommended one since 1992.

With that being said, let’s go over the different types of table joins.

Inner Joins in Mysql

The type of table join depicted in the example above is referred to as an inner join. The MySQL INNER JOIN clause matches rows in one table with rows in other tables and selects rows that contain columns from both tables. Hence, the above query would only return rows where an Order has an associated record in the OrderDetails table.

SELECT o.OrderID, 
          od.ProductID
   FROM   Orders AS o
   INNER JOIN dbo.OrderDetails AS od
           ON o.OrderID = od.ProductID
   WHERE o.OrderDate >= '20091001';

Selecting data from Three Tables in Mysql

Inner Join statements may be utilized to link any number of tables to the query, so long as there are common fields between the tables. Here is the syntax for joining three tables:

SELECT * 
FROM  table1 
        INNER JOIN table2 
        ON table1.primaryKey=table2.table1Id   
        INNER JOIN table3 
        ON table1.primaryKey=table3.table1Id

Applying the above syntax to our original query, let’s say that we only wanted to see records where there was an associated customer to the order. As long as there was a column shared between the two tables, we could hook it up to our query as follows:

SELECT o.OrderID, 
        od.ProductID
 FROM   Orders AS o
 INNER JOIN dbo.OrderDetails AS od
         ON o.OrderID = od.ProductID

INNER JOIN dbo.Customers AS c
         ON o.CustID = c.Id
 WHERE o.OrderDate >= '20091001';

Outer Joins in Mysql

If an inner join only selects rows that contain non-null linked values from both tables, an outer join selects rows regardless of whether there is a linked record in the joined table. There are three types of outer joins:

  • Left: selects rows from the first (main) table regardless of whether or not there is a linked record in the joined table.
  • Right: selects rows from the joined table regardless of whether or not it links to a record in the first (main) table.
  • Full: selects rows from both the first (main) and joined table regardless of whether or not a record links the two tables.

This join type is not supported by MySQL.

Left Join Example

The following query uses a Left Join (sometimes referred to as a left outer join) to fetch all of the records from the Orders table where the OrderDate is equal or greater than 20091001, regardless of whether or not there is an associated OrderDetail:

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o
 LEFT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001';

In cases where there is no associated OrderDetail record, the ProductID will come up as NULL.

Right Join Example

Replacing the Left Join with a Right one will fetch all of the records from the OrderDetails table where the OrderDate is equal or greater than 2009-10-01. The result set will include unmatched records in the right table of the join. In this instance, that means that each OrderDetail is returned even if there is no associated Order. Note that right joins may also be referred to as a right outer join:

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o
 RIGHT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001';

In cases where there is no associated Order record, the OrderID will show a NULL value.

Two JOINs and a UNION

One method to simulate a full join is to take the UNION of two outer joins, for example:

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o

LEFT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001'

UNION

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o
 RIGHT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001';

The limitation of this solution is that it does not handle duplicate records in either of the joined tables. UNION ALL with an Exclusion Join

One way to eliminate duplicates is to use an exclusion join to exclude anything from the second result that is already included in the first:

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o

LEFT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001'

UNION ALL

SELECT o.OrderID, 
        od.ProductID
 FROM Orders AS o
 RIGHT JOIN dbo.OrderDetails AS od
        ON o.OrderID = od.ProductID
 WHERE o.OrderDate >= '20091001';

AND   o.OrderID IS NULL;

Notice the use of UNION ALL instead of plain UNION, which would eliminate both of the duplicated records.

Conclusion

To recap what we learned here today:

  • Stick with the SQL-92 syntax.
  • Inner Joins selects only rows that contain columns from both tables.
  • Outer Joins include Left, Right, and Full.
  • Outer Join result sets include unmatched records in the left, right, or both tables of a join, respectively.
  • Full Outer Joins may be simulated in MySQL using UNION or UNION ALL with an Exclusion Join.

Source: http://www.databasejournal.com/features/mysql/querying-multiple-mysql-tables.html

Advertisement

Advertisement