Notice: Based mostly on the Language chapter of the MySQL e book.
Looking the databases
The next SQL instructions present details about the databases situated on the present server. The INFORMATION_SCHEMA desk containing this info is SCHEMATA.
The mysqlshow command line device can be utilized as a substitute.
You possibly can’t present databases if the server has been began with the –skip-all-databases choice.
If you do not have the ‘SHOW DATABASES’ privilege, you will solely see databases on which you have got some permissions.
Present all databases:
The SCHEMA key phrases can be utilized instead of DATABASES. MySQL does not assist customary SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with different DBMS’s.
Add a filter on the databases names
SHOW DATABASES LIKE 'sample';
The LIKE operator right here works as in regular SELECTs or DML statements. So you’ll be able to checklist all databases whose identify begins with ‘my’:
SHOW DATABASES LIKE'MY%';
Add advanced filters
You possibly can add extra advanced filters utilizing the WHERE clause:
SHOW DATABASES WHERE (situations);
WHERE clause lets you use common expressions, ‘<' and '>‘ operators, string features or different helpful expressions to filter the data returned by SHOW DATABASES.
Listing tables and views
The next SQL instructions present details about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this info are `TABLES` and `VIEWS`.
For the reason that following statements present little or no details about views, if it’s good to get metadata about them you will most likely choose to question the VIEWS desk.
The mysqlshow command line device can be utilized as a substitute.
Present all tables
USE database; SHOW TABLES;
SHOW TABLES FROM database;
The two kinds proven above are equal.
Apply a filter
You possibly can apply a filter to the tables names, to point out solely tables whose identify match a sample. You should utilize the LIKE operatore, as you do in SELECTs or within the DML statements:
SHOW TABLES LIKE `sample`;
Additionally, you’ll be able to apply a extra advanced filter to any column returned by the SHOW TABLES command utilizing the WHERE clause:
SHOW TABLES WHERE situation;
By default, SHOW TABLES returns just one column containing the identify of the desk. You may get further info through the use of the FULL key phrase:
SHOW FULL TABLES;
This may add a column known as `Table_type`. This may have Three values: ‘BASE TABLE’ for tables, ‘VIEW’ for views ans ‘SYSTEM VIEW’ for particular tables created by the server (usually used solely INFORMATION_SCHEMA tables).
So you’ll be able to solely checklist tables:
SHOW FULL TABLES WHERE `Table_type`='BASE TABLE';
Or, you’ll be able to solely checklist views:
SHOW FULL TABLES WHERE `Table_type`='VIEW';
Present solely open tables
You may get a listing of the non-temporary tables (not views) that are open within the cache:
SHOW OPEN TABLES;
This command has the identical parameters as SHOW TABLES, apart from FULL (ineffective on this case). You possibly can’t get this info from the INFORMATION_SCHEMA.
The next SQL instructions present details about the columns in a desk or in a view. The INFORMATION_SCHEMA desk containing this info is COLUMNS.
The mysqlshow command line device can be utilized as a substitute.
DESCRIBE `desk`; DESCRIBE `database`.`desk`; DESCRIBE `desk` 'filter';
DESC can be utilized as a shortcut for DESCRIBE.
‘filter’ generally is a column identify. If a column identify is specified, solely that column shall be proven. If ‘filter’ comprises the ‘%’ or the ‘_’ characters, it will likely be evaluated as a LIKE situation. For instance, you’ll be able to checklist all fields which begin with ‘my’:
DESC `desk` 'my%';
EXPLAIN `desk`; --synonym SHOW [FULL] FIELDS FROM `desk`; -- synonym SHOW COLUMNS FROM `desk`; --synonym SHOW COLUMNS FROM `desk` FROM `database`; SHOW COLUMNS FROM `desk` LIKE 'sample'; SHOW COLUMNS FROM `desk` WHERE situation;
FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, nevertheless it does not assist all of its clauses.
A databases identify may be specified each within the kind
SHOW COLUMNS FROM `desk` FROM `database`;
SHOW COLUMNS FROM `database`.`desk`;
Utilizing the FULL key phrase, further data may be retried: the columns’ collation, privileges you have got on the column and the remark.
The next SQL instructions present details about the indexes in a desk. Details about keys is contained within the `COLUMNS` desk within the INFORMATION_SCHEMA.
The mysqlshow -k command line device can be utilized as a substitute.
SHOW INDEX FROM `TABLE`; SHOW INDEX FROM `TABLE` FROM `databases`;
The KEYS reserved phrase can be utilized as a synonym of INDEX. No different clauses are offered.
information_schema is a digital database offered by MySQL 5 and later, that comprises metadata concerning the server and the databases.
On this e book, we are going to quote the MySQL identifiers (tables names, fields, databases, and so forth.) utilizing backquotes (
Backquote is ASCII 96. It may be sort on GNU/Linux programs by urgent: ALT+’.
Most frequently, that is non-compulsory. Nevertheless, this enables higher error messages from MySQL. For instance, this error shouldn’t be very useful:
mysql> SELECT user_id, group_id FROM person,group LIMIT 1; ERROR 1064 (42000): You may have an error in your SQL syntax; examine the guide that corresponds to your MySQL server model for the best syntax to make use of close to 'group LIMIT 1' at line 1
However this one is best:
mysql> SELECT user_id, group_id FROM `person`,`group` LIMIT 1; ERROR 1146 (42S02): Desk 'savannah.group' does not exist
Okay, it was only a lacking
mysql> SELECT user_id, group_id FROM `person`,`teams` LIMIT 1; +---------+----------+ | user_id | group_id | +---------+----------+ | 100 | 2 | +---------+----------+ 1 row in set (0.02 sec)
This syntax permits the person to make use of reserver phrases and a few unlawful characters in objects’ names. It’s even doable to make use of backquotes by tying it twice:
RENAME TABLE `person` TO ````
Nevertheless, this isn’t a transportable syntax. The SQL customary recommends the usage of a double quote (
"). If you wish to write transportable SQL quote, don’t quote the identifiers. However is there one thing like transportable SQL, even remotely? 😉
Definitions: what are DDL, DML and DQL?
- DDL (Information Definition Language) refers back to the CREATE, ALTER and DROP statements
- DML (Information Manipulation Language) refers back to the INSERT, UPDATE and DELETE statements
- DQL (Information Question Language) refers back to the SELECT, SHOW and HELP statements (queries)
- DCL (Information Management Language) refers back to the GRANT and REVOKE statements
- The power to set variables in an announcement with the := project operator:
- For e.g. (@complete) to calculate the whole in an instance, you need to have the whole column first as a result of it should be calculated earlier than the person proportion calculations
- Session variables are set during the thread.
- Within the overwhelming majority of instances you’d use a programming language to do that type of factor.
- Mysql variables may be helpful when engaged on the Mysql command line.
- If no data are returned, the person variable is not going to be set for that assertion.
- A person variable set within the discipline checklist can’t be used as a situation.
choose @take a look at := 2; choose @take a look at + 1
- The worth of a variable is about with the SET assertion or in a SELECT assertion with :=
set @startdate="some_start_date", @enddate="some_end_date"
SELECT @toremember:=depend FROM membros;
choose @numzero := depend from table1 the place discipline=0; choose @numdistinct := depend(distinct discipline) from table1 the place discipline <> 0 ; choose @numzero @numdistinct;
- You possibly can copy values retrieved by a SELECT into a number of variables:
A world variable is seen to all customers.
An expression and a column could also be given aliases utilizing AS. The alias is used because the expression’s column identify and can be utilized with order by or having clauses. For e.g.
SELECT CONCAT(last_name,' ', first_name) AS full_name, nickname AS nick FROM mytable ORDER BY full_name
These aliases can be utilized in ORDER BY, GROUP BY and HAVING clauses. They shouldn’t be utilized in WHERE clause.
A desk identify can have a shorter identify for reference utilizing AS. You possibly can omit the AS phrase and nonetheless use aliasing. For e.g.
SELECT COUNT(B.Booking_ID), U.User_Location FROM Customers U LEFT OUTER JOIN Bookings AS B ON U.User_ID = B.Rep_ID AND B.Project_ID = '10' GROUP BY (U.User_Location)
Aliasing performs an important position while you’re utilizing self joins. For e.g. folks desk has been known as p and c aliases!
SELECT p.identify AS guardian, c.identify AS baby, MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage FROM folks AS p LEFT JOIN folks AS c ON p.identify=c.guardian WHERE c.identify IS NOT NULL GROUP BY guardian HAVING minage > 50 ORDER BY p.dob;
choose syntax is as follows:
SELECT * FROM desk WHERE situation GROUP BY grouping discipline HAVING group situation ORDER BY order LIMIT restrict, offset
Listing of fields
You have to specify what knowledge you are going to retrieve within the SELECT clause:
SELECT DATABASE() -- returns the present db's identify SELECT CURRENT_USER() -- returns your username SELECT 1+1 -- returns 2
Any SQL expression is allowed right here.
You may as well retrieve all fields from a desk:
SELECT * FROM `stats`
When you SELECT solely the mandatory fields, the question shall be sooner.
The desk’s identify
In case you are retrieving outcomes from a desk or a view, normally you specify the desk’s identify within the FORM clause:
SELECT id FROM `stats` -- retrieve a discipline known as id from a desk known as stats
SELECT MAX(id) FROM `stats` SELECT id*2 FROM `stats`
You may as well use the `db_name`.`table_name` syntax:
SELECT id FROM `sitedb`.`stats`
However you may also specify the desk’s identify within the SELECT clause:
SELECT `stats`.`id` -- retrieve a discipline known as id from a desk SELECT `sitedb`.`stats`.`id`
You possibly can set a filter to resolve what data should be retrieved.
For instance, you’ll be able to retrive solely the report which has an id of 42:
SELECT * FROM `stats` WHERE `id`=42
Or you’ll be able to learn a couple of report:
SELECT * FROM `antiques` WHERE buyerid IS NOT NULL
You possibly can group all data by a number of fields. The report which have the identical worth for that discipline shall be grouped in a single computed report. You possibly can solely choose the grouped report and the results of some combination features, which shall be computed on all data of every group.
For instance, the next will group all data within the desk `customers` by the sphere `metropolis`. For every group of customers residing in the identical metropolis, the utmost age, the minimal age and the common age shall be returned:
SELECT metropolis, MAX(age), MIN(age), AVG(age) GROUP BY `metropolis`
Within the following instance, the customers are grouped by metropolis and intercourse, in order that we’ll know the max, min and avg age of male/feminine customers in every metropolis:
SELECT metropolis, intercourse, MAX(age), MIN(age), AVG(age) GROUP BY `metropolis`, `intercourse`
The HAVING clause declares a filter for the data that are computed by the GROUP BY clause. It is totally different from the WHERE clause, that operates earlier than the GROUP BY. This is what occurs:
- The data which match to the WHERE clause are retrieved
- These data are used to compute new data as outlined within the GROUP BY clause
- The brand new data that match to the HAVING situations are returned
This implies which WHERE decides what report are used to compose the brand new computed data.
HAVING decides what computed data are returned, so it might function on the outcomes of combination features. HAVING shouldn’t be optimized and may’t use indexes.
Incorrect use of HAVING:
SELECT metropolis, intercourse, MAX(age), MIN(age), AVG(age) GROUP BY `metropolis` HAVING intercourse='m'
This most likely provides a mistaken outcomes. MAX(age) and different combination calculations are made utilizing all values, even when the report’s intercourse worth is ‘f’. That is hardly the anticipated consequence.
Incorrect use of HAVING:
SELECT metropolis, intercourse, MAX(age), MIN(age), AVG(age) GROUP BY `metropolis`, `intercourse` HAVING intercourse='m'
That is appropriate and returns the anticipated outcomes, however the execution of this question shouldn’t be optimized. The WHERE clause can and must be used, as a result of, in order that MySQL does not computes data that are excluded later.
Right use of HAVING:
SELECT metropolis, intercourse, MAX(age), MIN(age), AVG(age) GROUP BY `metropolis` HAVING MAX(age) > 80
It should group all data, as a result of cannot resolve the max age of every metropolis earlier than the GROUP BY clause is execute. Later, it returns solely the report with a MAX(age)>80.
You possibly can set an arbitrary order for the data you retrive. The order could also be alphabetical or numeric.
SELECT * FROM `stats` ORDER BY `id`
By default, the order is ASCENDING. You may as well specify that the order should be DESCENDING:
SELECT * FROM `stats` ORDER BY `id` ASC -- default SELECT * FROM `stats` ORDER BY `id` DESC -- inverted
NULLs values are thought of as minor than another worth.
You may as well specify the sphere place, instead of the sphere identify:
SELECT `identify`, `buyerid` FROM `antiques` ORDER BY 1 -- identify SELECT `identify`, `buyerid` FROM `antiques` ORDER BY 2 -- buyerid SELECT `identify`, `buyerid` FROM `antiques` ORDER BY 1 DESC
SQL expressions are allowed:
SELECT `identify` FROM `antiques` ORDER BY REVERSE(`identify`)
You possibly can retrieve data in a random order:
SELECT `identify` FROM `antiques` ORDER BY RAND()
If a GROUP BY clause is specified, the outcomes are ordered by the fields named in GROUP BY, except an ORDER BY clause is current. You possibly can even specify within the GROUP BY clause if the order should be ascending or descending:
SELECT metropolis, intercourse, MAX(age) GROUP BY `metropolis` ASC, `intercourse` DESC
You probably have a GROUP BY however you don’t need the data to be ordered, you should utilize ORDER BY NULL:
SELECT metropolis, intercourse, MAX(age) GROUP BY `metropolis`, `intercourse` ORDER BY NULL
You possibly can specify the utmost of rows that you just need to learn:
SELECT * FROM `antiques` ORDER BY id LIMIT 10
This assertion returns a most of 10 rows. If there should not 10 rows, it returns the variety of rows you discover. The restrict clause is normally used with ORDER BY.
You may get a given variety of random data:
SELECT * FROM `antiques` ORDER BY rand() LIMIT 1 -- one random report SELECT * FROM `antiques` ORDER BY rand() LIMIT 3
You possibly can specify what number of rows must be skipped earlier than beginning return the report discovered. The primary report is 0, not one:
SELECT * FROM `antiques` ORDER BY id LIMIT 10 SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10 -- synonym
You should utilize the LIMIT clause to get the pagination of outcomes:
SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10 -- first web page SELECT * FROM `antiques` ORDER BY id LIMIT 10, 10 -- second web page SELECT * FROM `antiques` ORDER BY id LIMIT 20, 10 -- third web page
Additionally, the next syntax is accepted:
SELECT * FROM `antiques` ORDER BY id LIMIT 10 OFFSET 10
You possibly can LIMIT to examine the syntax of a question with out ready it returns the outcomes:
SELECT ... LIMIT 0
- SQL_CALC_FOUND_ROWS speeds down the LIMIT.
- LIMIT is especially helpful for SELECTs which use ORDER BY, DISTINCT and GROUP BY, as a result of their calculations does not must contain all of the rows.
- If the question is resolved by the server copying internelly the outcomes into a short lived desk, LIMIT helps MySQL to calculate what number of reminiscence is required by the desk.
The DISTINCT key phrase can be utilized to take away all duplicate rows from the resultset:
SELECT DISTINCT * FROM `stats` -- no duplicate rows SELECT DISTINCTROW * FROM `stats` -- synonym SELECT ALL * FROM `stats` -- duplicate rows returned (default)
You should utilize it to get the checklist of all values contained in a single discipline:
SELECT DISTINCT `sort` FROM `antiques` ORDER BY `sort`
Or you should utilize it to get the prevailing mixtures of some values:
SELECT DISTINCT `sort`, `age` FROM `antiques` ORDER BY `sort`
If one of many fields you might be SELECTing is the PRIMARY KEY or has a UNIQUE index, DISTINCT is ineffective. Additionally, it is ineffective to make use of DISTINCT along with the GROUP BY clause.
IN and NOT IN
SELECT id FROM stats WHERE place IN ('Supervisor', 'Employees')
SELECT ownerid, 'is in each orders & antiques' FROM orders, antiques WHERE ownerid = buyerid UNION SELECT buyerid, 'is in antiques solely' FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)
EXISTS and ALL
(Appropriate: Mysql 4+)
SELECT ownerfirstname, ownerlastname FROM proprietor WHERE EXISTS (SELECT * FROM antiques WHERE merchandise = 'chair')
SELECT buyerid, merchandise FROM antiques WHERE value = ALL (SELECT value FROM antiques)
There are some hints you could need to give to the server to higher optimize the SELECTs. When you give a couple of hints, the order of the key phrases is necessary:
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT | SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] ...
Normally, DML instructions (INSERT, DELETE, UPDATE) have larger precedence than SELECTs. When you specify HIGH_PRIORITY although, the SELECT could have larger precedence than DML statements.
Drive MySQL to guage the tables of a JOIN in the identical order they’re named, from the leftmost.
It is helpful solely whereas utilizing DISTINCT or GROUP BY. Tells the optimizer that the question will return few rows.
It is helpful solely whereas utilizing DISTINCT or GROUP BY. Tells the optimizer that the question will return a many rows.
Drive MySQL to repeat the consequence into a short lived desk. That is helpful to take away LOCKs as quickly as doable.
Forces MySQL to repeat the consequence into the question cache. Solely works if the worth of query_cache_type is DEMAND or 2.
Tells MySQL to not cache the consequence. Helpful if the question happens very seldom or if the consequence usually change.
Helpful in case you are utilizing the LIMIT clause. Tells the server to calculate what number of rows would have been returned if there have been no LIMIT. You possibly can retrieve that quantity with one other question:
SELECT SQL_CALC_FOUND_ROWS * FROM `stats` LIMIT 10 OFFSET 100; SELECT FOUND_ROWS();
UNION and UNION All
(Appropriate: Mysql 4+)
Following question will return all of the data from each tables.
SELECT * FROM english UNION ALL SELECT * FROM hindi
UNION is identical as UNION DISTINCT.
When you sort solely UNION, then it’s thought of that you’re asking for distinct data. If you’d like all data, you need to use UNION ALL.
SELECT phrase FROM word_table WHERE id = 1 UNION SELECT phrase FROM word_table WHERE id = 2
(SELECT journal FROM pages) UNION DISTINCT (SELECT journal FROM pdflog) ORDER BY journal
(SELECT ID_ENTRY FROM desk WHERE ID_AGE = 1) UNION DISTINCT (SELECT ID_ENTRY FROM desk WHERE ID_AGE=2)
The syntax is as follows:
Insert value1 into Column1, value2 into Column2, and value3 into Column3:
INSERT INTO TableName (Column1, Column2, Column3) VALUES (value1, value2, value3)
Insert one report (values are inserted within the order that the columns seem within the database):
INSERT INTO TableName VALUES (value1, value2, value3)
Insert two data:
INSERT INTO TableName VALUES (value1, value2, value3), (value4, value5, value6)
INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00); INSERT INTO antiques (buyerid, sellerid, merchandise) VALUES (01, 21, 'Ottoman');
You may as well insert data ‘chosen’ from different desk.
INSERT INTO table1(field1, field2) SELECT field1, field2 FROM table2
INSERT INTO World_Events SELECT * FROM National_Events
- To insert many rows, think about using LOAD DATA INFILE as a substitute.
- If bulk INSERTs are too gradual and so they function on listed non-empty tables, perhaps it is best to improve the worth of bulk_insert_buffer_size.
- Earlier than performing bulk inserts, you could need to disable thekeys.
- LOCKing a desk additionally quickens the INERT.
The syntax is:
UPDATE desk SET discipline = newvalue WHERE standards ORDER BY discipline LIMIT n
UPDATE proprietor SET ownerfirstname="John" WHERE ownerid = (SELECT buyerid FROM antiques WHERE merchandise = 'Bookcase'); UPDATE antiques SET value = 500.00 WHERE merchandise = 'Chair'; UPDATE order SET low cost=low cost * 1.05 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl2.standing="Energetic" UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz') UPDATE products_categories AS computer INNER JOIN merchandise AS p ON computer.prod_id = p.id SET computer.prod_sequential_id = p.sequential_id UPDATE table_name SET col_name = REPLACE(col_name, 'host.area.com', 'host2.area.com')
UPDATE posts SET deleted=True ORDER BY date LIMIT 1
With ORDER BY you’ll be able to order the rows earlier than updating them, and solely replace a given variety of rows (LIMIT).
It’s at the moment not doable to replace a desk whereas performing a subquery on the identical desk. For instance, if I need to reset a password I forgot in SPIP:
mysql> UPDATE spip_auteurs SET go = (SELECT go FROM spip_auteurs WHERE login='paul') the place login='admin'; ERROR 1093 (HY000): You possibly can't specify goal desk 'spip_auteurs' for replace in FROM clause
TODO:  describes a work-around that I could not make to work with MySQL 4.1. Presently the work-around shouldn’t be use 2 subqueries, presumably with transactions.
- UPDATEs pace relies upon of what number of indexes are up to date.
- When you UPDATE a MyISAM desk which makes use of dynamic format, when you make rows bigger they may very well be splitted in a couple of half. This causes studying overhead. So, in case your purposes usually do that, you could need to usually run an OPTIMIZE TABLE assertion.
- Performing many UPDATEs all collectively on a LOCKed desk is quicker than performing them individually.
REPLACE works precisely like INSERT, besides that if an previous report within the desk has the identical worth as a brand new report for a PRIMARY KEY or a UNIQUE index, the previous report is deleted earlier than the brand new report is inserted.
With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced however the assertion doesn’t abort.
Previous to MySQL 4.0.1, INSERT … SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to disregard data that might trigger duplicate-key violations.
DELETE [QUICK] FROM table1 TRUNCATE table1
- When you do not use a WHERE clause with DELETE, all data shall be deleted.
- It may be very gradual in a big desk, particularly if the desk has many indexes.
- If the desk has many indexes, you may make the cache bigger to strive making the DELETE sooner (key_buffer_size variable).
- For listed MyISAM tables, in some instances DELETEs are sooner when you specify the QUICK key phrase (DELETE QUICK FROM …). That is solely helpful for tables the place DELETEd index values shall be reused.
- TRUNCATE will delete all rows rapidly by DROPping and reCREATE-ing the desk (not all Storage Engines assist this operation).
- TRUNCATE shouldn’t be transaction-safe nor lock-safe.
- DELETE informs you what number of rows have been eliminated, however TRUNCATE does not.
- After DELETing many rows (about 30%), an OPTIMIZE TABLE command ought to make subsequent statements sooner.
DELETE FROM `antiques` WHERE merchandise = 'Ottoman' ORDER BY `id` LIMIT 1
You possibly can order the rows earlier than deleting them, after which delete solely a given variety of rows.
Create desk syntax is:
Create desk tablename (FieldName1 DataType,
The rows returned by the “choose” question may be saved as a brand new desk. The datatype would be the similar because the previous desk. For e.g.
CREATE TABLE LearnHindi
choose english.tag, english.Inenglish as english, hindi.Inhindi as hindi
FROM english, hindi
WHERE english.tag = hindi.tag
ALTER TABLE command can be utilized if you need to add / delete /modify the columns and / or the indexes; or, it may be used to alter different desk properties.
Add a column:
ALTER TABLE awards ADD COLUMN AwardCode int(2)
Modify a column:
ALTER TABLE awards CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL
ALTER TABLE awards MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL
Drop a column:
ALTER TABLE awards DROP COLUMN AwardCode
Re-order the report in a desk:
ALTER TABLE awards ORDER BY id
(this operation is barely supported by some Storage Engines; it might make some question sooner)
Renaming a desk
In an effort to rename a desk, you will need to have ALTER and DROP privileges on the previous desk identify (or on all of the tables), and CREATE and INSERT privileges on the brand new desk identify (or on all of the tables).
You should utilize ALTER TABLE to rename a desk:
RENAME TABLE `old_name` TO `new_name`
You possibly can rename a couple of desk with a single command:
RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ...
RENAME is a shortcut. You may as well use the ALTER TABLE assertion:
ALTER TABLE `previous` RENAME `new`
Utilizing ALTER TABLE you’ll be able to solely rename one desk per assertion, nevertheless it’s the one option to rename short-term tables.
DROP TABLE `awards`
Will utterly delete the desk and all of the data it comprises.
You may as well drop a couple of desk with a single assertion:
DROP TABLE `table1`, `table2`, ...
There are come non-compulsory key phrases:
DROP TEMPORARY TABLE `desk`; DROP TABLE `desk` IF EXISTS;
TEMPORARY should be specified, to drop a short lived desk. IF EXISTS tells the server that it should not increase an error if the desk does not exist.
Null is a particular logical worth in SQL. Most programming languages have 2 values of logic: True and False. SQL additionally has NULL which implies “Unknown”. A NULL worth may be set.
NULL is a non-value, so it may be assigned to TEXT columns, INTEGER columns or another datatype. A column cannot include NULLs provided that it has been declared as NOT NULL (see ALTER TABLE).
INSERT into Singer (F_Name, L_Name, Birth_place, Language) values ("", "Homer", NULL, "Greek"), ("", "Sting", NULL, "English"), ("Jonny", "5", NULL, "Binary");
Don’t quote the NULL. When you quote a Null then you definately identify the particular person NULL.
For some unusual motive, NULLs don’t present visually on home windows XP in Varchar fields however they do in Fedora’s model, so variations of mysql may give totally different outputs. Right here we set the worth of Sting and Homer’s first identify to a zero size string “”, as a result of we KNOW they’ve NO first identify, however we KNOW we have no idea the place they had been born. To examine for a NULLs use
SELECT * from Singer WHERE Birth_place IS NULL; or SELECT * from Singer WHERE Birth_place IS NOT NULL; or SELECT * from Singer WHERE isNull(Birth_place)
Keep in mind, COUNT by no means counts NULLS.
choose depend(Birth_place) from Singer; 0 and sum(NULL) provides a NULL reply.
Regular operations (comparisons, expressions…) return NULL if not less than one of many in contrast gadgets is NULL:
SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1
NULL) OR (1 + NULL) OR (1 LIKE NULL)
as a result of all of the expressions between in parenthesis return NULL. It is positively logical: if you do not know the worth represented by NULL, you do not know is it is =1 or <>1. Remember that even (NULL=NULL and (NOT NULL) return NULL.
Coping with NULL
The operate ‘COALESCE’ can simplify working with null values.
for instance, to keep away from displaying null values by treating null as zero, you’ll be able to sort:
SELECT COALESCE(colname,0) from desk the place COALESCE(colname,0) > 1;
In a date discipline, to deal with NULL as the present date:
ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))
EXP(SUM(LOG(COALESCE(*the sphere you need to multiply*,1)))
The coalesce() operate is there to protect towards making an attempt to calculate the logarithm of a null worth and could also be non-compulsory relying in your circumstances.
SELECT t4.gene_name, COALESCE(g2d.rating,0), COALESCE(dgp.rating,0), COALESCE(pocus.rating,0) FROM t4 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name LEFT JOIN dgp ON t4.gene_name=dgp.gene_name LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;
Use of IFNULL() in your SELECT assertion is to make the NULL any worth you want.
If expr1 shouldn’t be NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string worth, relying on the context through which it’s used:
mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'sure'); -> 'sure'
Null dealing with may be very counter intuitive and will trigger issues when you’ve got an incorrect operate in a delete assertion that returns null. For instance the next question will delete all entries.
DELETE FROM my_table WHERE discipline > NULL (or operate returning NULL)
If you wish to have NULL values offered final when doing an ORDER BY, do this:
SELECT * FROM my_table ORDER BY ISNULL(discipline), discipline [ ASC | DESC ]
Tough Column Names, Like `DATE` — use backtick. If utilizing “date” as a column identify, enclose it in backticks ` as follows:
CREATE TABLE IF NOT EXISTS shares ( pkey int NOT NULL auto_increment, `date` date, ticker varchar(5), open decimal (9,2), excessive decimal (9,2), low decimal (9,2), shut decimal (9,2), quantity int, timeEnter timestamp(14), PRIMARY KEY (pkey) );
VARCHAR is shorthand for CHARACTER VARYING. ‘n’ represents the utmost column size (upto 255 characters)
char(n) is just like varchar(n) with the one distinction that char will occupy mounted size of house within the database whereas varchar will want the house to retailer the precise textual content. For instance, a VARCHAR(10) column can maintain a string with a most size of 10 characters. The precise storage required is the size of the string (L), plus 1 byte to report the size of the string.
For the string ‘abcd’, L is Four and the storage requirement is 5 bytes.
A BLOB or TEXT column with a most size of 65,535 characters.
Specifying an n worth has no impact in anyway. No matter a provided worth for n, most (unsigned) worth saved is 429 crores.
If you wish to add adverse numbers, add the “signed” key phrase subsequent to it.
decimal(4,2) means numbers upto 99.99 (and NOT 9999.99 as you could count on) may be saved. 4 digits with the final 2 reserved for decimal.
Out of the three varieties DATETIME, DATE, and TIMESTAMP, the DATE sort is used if you want solely a date worth, with no time half.
MySQL retrieves and shows DATE values in ‘YYYY-MM-DD’ format.
The DATETIME sort is used if you want values that include each date and time info.
The distinction between DATETIME and TIMESTAMP is that the TIMESTAMP vary is proscribed to 1970-2037 (see beneath).
TIME can be utilized to solely retailer the time of day (HH:MM:SS), with out the date. It will also be used to signify a time interval (for instance: -02:00:00 for “two hours up to now”). Vary: ‘-838:59:59’ => ‘838:59:59’.
YEAR can be utilized to retailer the 12 months quantity solely.
When you manipulate dates, you need to specify the precise date, not solely the time – that’s, MySQL is not going to automagically use right now as the present date. Quite the opposite, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD worth, which can most likely be invalid.
The next examples present the exact date vary for Unix-based timestamps, which begins on the Unix Epoch and stops simply earlier than the primary new 12 months earlier than the
traditional restrict (2038).
mysql> SET time_zone="+00:00"; -- GMT Question OK, Zero rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1); +-------------------+ | FROM_UNIXTIME(-1) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(0); -- "Epoch" +---------------------+ | FROM_UNIXTIME(0) | +---------------------+ | 1970-01-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916799); +---------------------------+ | FROM_UNIXTIME(2145916799) | +---------------------------+ | 2037-12-31 23:59:59 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916800); +---------------------------+ | FROM_UNIXTIME(2145916800) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec)
set and enum
A SET datatype can maintain any variety of strings from a predefined checklist of strings specified throughout desk creation. The SET datatype is just like the ENUM datatype in that they
each work with predefined units of strings,
however the place the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype lets you retailer any of the values collectively, from none to all of them.
MySQL makes use of some customary SQL operators and a few non-standard operators. They can be utilized to put in writing expressions which contain fixed values, variables, values contained in fields and / or different expressions.
Desk of operator priority:
INTERVAL BINARY, COLLATE ! - (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + <<, >> & | =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN BETWEEN, CASE, WHEN, THEN, ELSE NOT &&, AND XOR ||, OR :=
- PIPES_AS_CONCAT – If this SQL mode is enabled, || has priority on ^, however – and ~ have priority on ||.
- HIGH_NOT_PRECEDENCE – If this SQL mode is enabled, NOT has the identical priority degree as !.
Use of parenthesis
You should utilize parenthesis to pressure MySQL to guage a subexpression earlier than one other indipendently from operator priority:
SELECT (1 + 1) * 5 -- returns 10
You may as well use parenthesis to make an expression extra readable by people, even when they do not have an effect on the priority:
SELECT 1 + (2 * 5) -- the identical as 1 + 2 * 5
You should utilize the = operator to assign a price to a column:
UPDATE `myTable` SET `uselessField`=0
Whenever you need to assign a price to a variable, you will need to use the := operator, as a result of the usage of = can be ambigous (is it as project or a comparability?)
SELECT @myvar := 1
You may as well use SELECT INTO to assign values to a number of variables.
If you wish to examine if 2 values are equal, you will need to use the = operator:
SELECT True = True -- returns 1 SELECT True = False -- returns 0
If you wish to examine if 2 values are totally different, you should utilize the <> or != operators, which have the identical that means:
SELECT True <> False -- returns 1 SELECT True != True -- returns 0
<> return 1 the place = returns Zero and vice versa.
IS and NULL-safe comparability
Whenever you examine a NULL worth with a non-NULL worth, you will get NULL. If you wish to examine if a price is null, you should utilize IS:
SELECT (NULL IS NULL) -- returns 1 SELECT (1 IS NULL) -- returns 0 SELECT (True IS True) -- returns an error!
You possibly can examine if a price is non-NULL:
SELECT (True IS NOT NULL) -- returns 1
There may be additionally an equality operator which considers NULL as a traditional worth, so it returns 1 (not NULL) if each values are NULL and returns 0 (not NULL) if one of many values is NULL:
SELECT col1 <=> col2 FROM myTable
There may be not a NULL-safe non-equality operator, however you’ll be able to sort the next:
SELECT NOT (col1 <=> col2) FROM myTable
IS and boolean comparisons
IS and IS NOT will also be used for boolean comparisons. You should utilize them with the reserved phrases TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL).
SELECT 1 IS TRUE -- returns 1 SELECT 1 IS NOT TRUE -- returns 0 SELECT 1 IS FALSE -- returns 0 SELECT (NULL IS NOT FALSE) -- returns 1: unkown shouldn't be false SELECT (NULL IS UNKOWN) -- returns 1 SELECT (NULL IS NOT UNKNOWN) -- returns 0
Better, Much less…
You possibly can examine if a price is larger than one other worth:
SELECT 100 > 0 -- returns 1 SELECT 4 > 5 -- return 0
You may as well examine if a price is minor than one other worth:
SELECT 1 < 2 -- returns 1 SELECT 2 < 2 -- returns 0
This type of comparisons additionally works on TEXT values:
SELECT 'a' < 'b' -- returns 1
Usually talking, alphabetical order is used for TEXT comparisons. Nevertheless, the precise guidelines are outlined by the COLLATION used. A COLLATION defines the sorting guidelines for a given CHARACTER SET. For instance, a COLLATION could also be case-sensitive, whereas one other COLLATION could also be case-insesitive.
You possibly can examine if a price is equal or better than one other worth. For instance, the next queries have the identical that means:
SELECT `a` >= `b` FROM `myTable` SELECT NOT (`a` < `b`) FROM `myTable`
Equally, you'll be able to examine if a price is much less or equal to a different worth:
SELECT `a` <= `b` FROM `myTable`
If you wish to examine if a price is included in a given vary, you should utilize the BETWEEN ... AND ... operator. AND does not have its traditional that means. Instance:
SELECT 20 BETWEEN 10 AND 100 -- returns 1
The worth after BETWEEN and the worth after AND are included within the vary.
You may as well use NOT BETWEEN to examine if a price shouldn't be included in a spread:
SELECT Eight NOT BETWEEN 5 AND 10 -- returns 0
You should utilize the IN operator to examine if a price is included in a listing of values:
SELECT 5 IN (5, 6, 7) -- returns 1 SELECT 1 IN (5, 6, 7) -- returns 0
You shouldn't embody within the checklist each numbers and strings, or the outcomes could also be unpredictable. You probably have numbers, it is best to quote them:
SELECT Four IN ('a', 'z', '5')
There may be not a theorical restrict to the variety of values included within the IN operator.
You may as well use NOT IN:
SELECT 1 NOT IN (1, 2, 3) -- returns 0
MySQL boolean logic
MySQL does not have an actual BOOLEAN datatype.
FALSE is a synonym for 0. Empty strings are thought of as FALSE in a boolean context.
TRUE is a synonym for 1. All non-NULL and non-FALSE knowledge are thought of as TRUE in a boolean context.
UNKNOWN is a synonym for NULL. The particular date 0/0/Zero is NULL.
NOT is the one operator which has just one operand. It returns Zero if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL.
SELECT NOT 1 -- returns 0 SELECT NOT FALSE -- returns 1 SELECT NOT NULL -- returns NULL SELECT NOT UNKNOWN -- returns NULL
! is a synonym for NOT.
AND returns 1 if each the operands are TRUE, else returns 0; if one of many operands is NULL, returns NULL.
SELECT 1 AND 1 -- returns 1 SELECT 1 AND -- return 0 SELECT AND NULL -- returns NULL
&& is a synonym for AND.
SELECT 1 && 1
OR returns TRUE if not less than one of many operands is TRUE, else returns FALSE; if one of many operands is NULL, returns NULL.
SELECT TRUE OR FALSE -- returns 1 SELECT 1 OR 1 -- returns 1 SELECT FALSE OR FALSE -- returns 0 SELECT NULL OR TRUE -- returns NULL
|| is a synonym for OR.
SELECT 1 || 0
XOR (eXclusive OR) returns 1 if solely one of many operands is TRUE and the opposite operand is FALSE; returns Zero if each the operands are TRUE o each the operands are FALSE; returns NULL if one of many operands is NULL.
SELECT 1 XOR 0 -- returns 1 SELECT FALSE XOR TRUE -- returns 1 SELECT 1 XOR TRUE -- returns 0 SELECT Zero XOR FALSE -- returns 0 SELECT NULL XOR 1 -- returns NULL
AND may be written as &&
OR may be written advert ||
NOT may be written as !
Solely NOT (normally) has a distinct priority from its synonym. See operator priority for datail.
MySQL helps operands which carry out all primary arithmetic operations.
You possibly can sort optimistic values with a '+', if you would like:
SELECT +1 -- return 1
You possibly can sort adverse values with a '-'. - is an inversion operand:
SELECT -1 -- returns -1 SELECT -+1 -- returns -1 SELECT --1 -- returns 1
You may make sums with '+':
SELECT 1 + 1 -- returns 2
You may make subtractions with '-':
SELECT True - 1 -- returns 0
You possibly can multiply a quantity with '*':
SELECT 1 * 1 -- returns 1
You may make divisions with '/'. Returns a FLOAT quantity:
SELECT 10 / 2 -- returns 5.0000 SELECT 1 / 1 -- returns 1.0000 SELECT 1 / 0 -- returns NULL (not an error)
You may make integer divisions with DIV. Ensuing quantity is an INTEGER. No reminder. This has been added in MySQL 4.1.
SELECT 10 DIV 3 -- returns 3
You may get the reminder of a division with '%' or MOD:
SELECT 10 MOD 3 -- returns 1
Utilizing + to forged knowledge
You possibly can convert an INTEGER to a FLOAT doing so:
SELECT 1 + 0.0 -- returns 1.0 SELECT 1 + 0.000 -- returns 1.000 SELECT TRUE + 0.000 -- returns 1.000
You possibly can't convert a string to a FLOAT worth by including 0.0, however you'll be able to forged it to an INTEGER:
SELECT '1' + 0 -- returns 1 SELECT '1' + FALSE -- returns 1 SELECT '' + '' -- returns 0
Textual content operators
There aren't any concatenation operators in MySQL.
Arithmetic operators convert the values into numbers after which carry out arithmetic operations, so you'll be able to't use + to concatenate strings.
You should utilize the CONCAT() operate as a substitute.
The LIKE operator could also be used to examine if a string matches to a sample. A easy instance:
SELECT * FROM articles WHERE title LIKE 'whats up world'
The sample matching is normally case insensitive. There are two exceptions:
- when a LIKE comparability is carried out towards a column which has been declared with the BINARY flag (see CREATE TABLE);
- when the expression comprises the BINARY clause:
SELECT * 'take a look at' LIKE BINARY 'TEST' -- returns 0
You should utilize two particular characters for LIKE comparisons:
- _ means "any character" (however should be 1 char, not Zero or 2)
- % means "any sequence of chars" (even Zero chars or 1000 chars)
Notice that "" additionally escapes quotes ("'") and this behaviour cannot be modified by the ESCAPE clause. Additionally, the escape character doesn't escape itself.
Widespread makes use of of LIKE:
- Discover titles beginning with the phrase "whats up":
SELECT * FROM articles WHERE title LIKE 'whats up%'
- Discover titles ending with the phrase "world":
SELECT * FROM articles WHERE title LIKE '%world'
- Discover titles containing the phrase "gnu":
SELECT * FROM articles WHERE title LIKE '%gnu%'
These particular chars could also be contained within the sample itself: for instance, you could possibly must seek for the "_" character. In that case, it's good to "escape" the char:
SELECT * FROM articles WHERE title LIKE '_%' -- titles beginning with _ SELECT * FROM articles WHERE title LIKE '%%' -- titles beginning with %
Generally, you could need to use an escape character totally different from "". For instance, you could possibly use "/":
SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'
Whenever you use = operator, trailing areas are ignored. Whenever you use LIKE, they're taken under consideration.
SELECT 'phrase' = 'phrase ' -- returns 1 SELECT 'phrase' LIKE 'phrase ' -- returns 0
LIKE additionally works with numbers.
SELECT 123 LIKE '%2%' -- returns 1
If you wish to examine if a sample does not match, you should utilize NOT LIKE:
SELECT 'a' NOT LIKE 'b' -- returns 1
You should utilize SOUNDS LIKE to examine if 2 textual content values are pronounced in the identical method. SOUNDS LIKE makes use of the SOUNDEX algorythm, which is bases on english guidelines and may be very approximate (however easy and thus quick).
SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- brief kind SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- lengthy kind
SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.
You should utilize REGEXP to examine if a string matches to a sample utilizing common expressions.
SELECT 'string' REGEXP 'sample'
You should utilize RLIKE as a synonym for REGEXP.
SELECT ~0 -- returns 18446744073709551615 SELECT ~1 -- returns 18446744073709551614
SELECT 1 & 1 -- returns 1 SELECT 1 & 3 -- returns 1 SELECT 2 & 3 -- returns 2
SELECT 1 | 0 -- returns 1 SELECT 3 | 0 -- returns 3 SELECT 4 | 2 -- returns 6
SELECT 1 ^ 0 -- returns 1 SELECT 1 ^ 1 -- returns 0 SELECT 3 ^ 1 -- returns 2
SELECT 1 << 2 -- returns 4
SELECT 1 >> 2 -- 0
Import / export
Except for mysqldump (cf. MySQL/Administration), you may also export uncooked knowledge utilizing:
SELECT ... FROM desk INTO OUTFILE 'path' LOAD DATA INFILE 'path' INTO TABLE desk
SELECT * FROM destinataire INTO OUTFILE '/tmp/take a look at' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211); # in one other database/pc/and so forth.: LOAD DATA INFILE '/tmp/take a look at' INTO TABLE destinataire;
Beware that the MySQL daemon itself will write the file, not the person you run the MySQL shopper with. The file shall be saved on the server, not in your host. Furthermore, the server will want write entry to the trail you specify (normally, the server can _not_ write in your house listing, e.g.). Therefore why we (unsecurely) used
/tmp within the examples.
Train I - Questions
- Who has a flat in "Goreagon" and who desires to purchase one?
- Who has a flat in "Parle" and who desires to purchase one?
- The place does "Shantanu Oak" personal the flats and the place does he need to purchase one?
- What number of entries have been recorded to date?
- What number of flats are there on the market?
- What are the names of our purchasers?
- What number of purchasers do we now have?
- Listing the purchasers whose identify begin with "S"?
- Rearrange the checklist Alphabetically sorted.
Train I - Solutions
- choose * from checklist the place FlatHave = "Goregaon" and FlatWant = "Goregaon"
- choose * from checklist the place FlatHave = "Parle" and FlatWant = "Parle"
- choose FlatHave,FlatWant from checklist the place Identify = "Shantanu" and Surname = "Oak"
- choose depend from checklist
- choose depend(FlatHave) from checklist the place FlatHave shouldn't be null
- choose distinct Identify, Surname from checklist
- choose depend(distinct Identify, surname) from checklist
- choose * from checklist the place Identify like "S%"
- choose Surname, Identify, FlatHave, FlatWant from checklist order by Identify
Train II - Questions
- A listing of all college students who scored over 90
on his or her math paper?
- A listing of all college students who scored greater than 85 in all topics?
- Declare Outcomes: Print the outcomes of all college students with consequence column.
- Discover out complete marks of all the scholars.
- What are the common marks of the category for every topic?
- What are the minimal marks in Math?
- What are the utmost marks in Math?
- Who received the best marks in Math?
Train II - Solutions
Notice: many issues have a couple of appropriate resolution.
- SELECT * FROM grades WHERE math > 90
- SELECT identify FROM grades WHERE math > 85 AND physics > 85 AND literature > 85
- SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'go') AS consequence FROM grades ORDER BY consequence DESC
- SELECT identify, math+physics+literature FROM grades
- SELECT AVG(math), AVG(physics), AVG(literature) FROM grades
- SELECT MIN(math) FROM grades
- SELECT MAX(math) FROM grades
- SELECT * FROM college students ORDER BY math DESC LIMIT 1
SELECT Vendor, ID, Rely(1) as dupes FROM table_name GROUP BY Vendor, ID HAVING Rely(1) >1
SELECT txt, COUNT FROM dupes GROUP BY txt HAVING COUNT > 1;
SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt > 1
Take away duplicate entries.
Assume the next desk and knowledge.
CREATE TABLE IF NOT EXISTS dupTest
(pkey int(11) NOT NULL auto_increment,
a int, b int, c int, timeEnter timestamp(14),
PRIMARY KEY (pkey));
insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
Notice, the primary two rows comprises duplicates in columns a and b. It comprises different duplicates; however, leaves the opposite duplicates alone.
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);