Aide mémoire SQL


Télécharger Aide mémoire SQL
3.53.5 étoiles sur 5 a partir de 1 votes.
Votez ce document:

Télécharger aussi :


Cours SQL Server en ligne : Commandes et Instructions de base

Afficher les erreurs

SHOW warnings;

Afficher les bases de données

SHOW DATABASES;

Créer une base de données

CREATE DATABASE nom_base;

Utiliser une base de données

USE nom_base;

Effacer une base de données

DROP DATABASE name_database ;

Créer une sauvegarde (backup)

mysqldump name_database > /home/glycine/login/name_database.backup

Restaurer une sauvegarde (backup)

mysql name_database < name_database.backup

Donner toutes les permissions

GRANT ALL ON *.* to [email protected] IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY 'something' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO [email protected]"%"

IDENTIFIED BY 'something' WITH GRANT OPTION;

GRANT RELOAD,PROCESS ON *.* TO [email protected]; GRANT USAGE ON *.* TO [email protected];

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*

TO [email protected]>

IDENTIFIED BY 'stupid';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON expenses.*

TO [email protected]"> [email protected] IDENTIFIED BY 'stupid';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON customer.* TO [email protected]'%'>

IDENTIFIED BY 'stupid';

Voir les permissions d’un utilisateur

SHOW GRANTS FOR [email protected];

Enlever les permissions

REVOKE ALL PRIVILEGES ON *.* FROM [email protected];

 

Utiliser des commandes mysql à partir d’un fichier  \. Commandes_ fichier.sql

Sauvegarder commandes et résultats dans un fichier

\T nom_fichier

Création de tables

Création simple

CREATE TABLE example ( id INT,

data VARCHAR(100) );

Utilisation auto increment

CREATE TABLE example_autoincrement (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100)

);

Utilisation primary key, not null É

CREATE TABLE phase_3 (

when_date DATE,

when_time TIME,

authors VARCHAR(25) NOT NULL,

parents VARCHAR(25),

title VARCHAR(80) NOT NULL,

description TEXT NOT NULL,

thumb_url VARCHAR(255) NOT NULL,

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

) ;

Utilisation clé primaire et clé étrangère

CREATE TABLE players (

player_nom VARCHAR(20),

player_prenom VARCHAR(20),

team_id INT,

player_id INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY (player_id), //clé primaire pour identifiant unique et index

FOREIGN KEY (team_id) REFERENCES team(team_id) //clé étrangère pour créer relation avec

autre table

);

CREATE TABLE gene (

name VARCHAR(30) NOT NULL,

organism_id INT,

id_gene INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id_gene)

);

Utilisation de default

CREATE TABLE protein (

name VARCHAR(30) NOT NULL,

size INT,

molecular_weight FLOAT DEFAULT 0.0

id_protein INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id_protein)

);

 

Utilisation table avec champs enregistrant la date actuelle

CREATE TABLE example_timestamp (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(100),

cur_timestamp TIMESTAMP(8)

);

Utilisation d’un type particulier de moteur

CREATE TABLE example_innodb ( id INT,

data VARCHAR(100) ) TYPE=innodb;

Types de champs

Nom

Description

Taille

CHAR(M) [binary]

Une chaîne de caractères de taille fixe qui utilise des espaces pour remplir le champ jusqu'à concurrence de M caractères. La taille M est comprise entre 1 et 255 caractères. Tous les espaces supplémentaires sont enlevés lorsqu'on récupère la valeur. Le tri et la comparaison de valeurs se fait indifféremment de la casse a moins que le mot-clé 'binary' soit spécifié.

M

VARCHAR(M) [binary]

Une chaîne de caractères de taille variable qui est stockée avec sa longueur (L+1). La taille M maximale est comprise entre 1 et 255 caractères. Le tri et la comparaison de valeurs se fait indifféremment de la casse a moins que le mot-clé 'binary' soit spécifié.

L+1

TEXT and BLOB

Un TEXT/BLOB avec une longueur maximale de 65535 caractères. Ces champs ne peuvent pas servir de clé d'index.

L+2

INT[(D)] [UNSIGNED] [ZEROFILL]

Un entier normal. Intervalle signé: -2147483648 à 2147483647. Intervalle non-signé: 0 à 4294967295.

4

FLOAT

Nombre réel de 4 octets (précision de 8 chiffres). (BIG nombre réel plus grand)

DECIMAL(M,N)

Un nombre où M représente le nombre de décimales signifiantes qui seront stockées pour les valeurs, et N représente le nombre de chiffres qui seront stockés après le point des décimales.

DATE

Un type permettant de stocker des dates. Utilise la syntaxe "YYYY-MM-DD", mais peut être mis a jour par un nombre ou une chaîne de caractères. Comprend au moins les syntaxes suivantes: 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD', 'YYMM', 'YY'. Intervalle de 0000-00-00 à 9999-12-31.

4

TIME

Un type permettant de stocker des heures. Utilise la syntaxe "HH:MM:SS", mais peut être mis à jour avec un nombre ou une chaîne de caractères. Comprend au moins les syntaxes suivantes: 'HH:MM:DD, 'HHMMDD', 'HHMM', 'HH'.

3

ENUM('value','value2',...)

Une chaîne de caractères qui peut avoir une valeur parmi un ensemble de valeurs spécifiques. Par exemple la colonne test ENUM("one","two", "three") peut avoir une des valeurs suivantes:

  1. "one"
  2. "two"
  3. "three"

1 ou 2

SET('value','value2',...)

Une chaîne de caractères qui peut avoir une ou plusieurs valeurs d'un ensemble de valeurs possibles. Par exemple, la colonne test SET("one","two") peut avoir une des valeurs suivantes:

  1. ""
  2. "one"
  3. "two"
  4. "one,two"

1-8

Visualiser table ou database

Afficher toutes les tables de la base de données courante

SHOW TABLES;

Afficher de toutes les champs d'une table (players par exemple)

SHOW COLUMNS FROM table_name; ou bien : DESCRIBE table_name; ou bien : DESC table_name;

Insérer des valeurs

Insertion de données directement

INSERT INTO players VALUES (null, "olly", "olly", .j;

INSERT INTO connections VALUES (null, "olly", "1998-12-31 23:59:59", ..j; INSERT INTO acc VALUES(1034033, 'jsmith', 'smithy', now());

Insertion ciblée

INSERT INTO Ontology (term, description) VALUES

('start codon', 'denotes an Methionine codon of a transcript');

INSERT INTO Ontology (term, description) VALUES

('exon type', '3\'UTR, initial, internal, terminal, 5\'UTR');

INSERT INTO Sequence (sequence, defline, accession, version, length, moltype) VALUES

('ATGACGATCAGCATCAGCTACAGCTG', '> seq1', 'seq1', 1, 26, 1);

Insertion de données à partir d’un fichier (séparés par des tabulations (‘\t’) par défaut)

LOAD DATA LOCAL INFILE "text.txt" INTO TABLE players;

LOAD DATA LOCAL INFILE "text2.txt" INTO TABLE customers FIELDS TERMINATED BY ‘\;’;

Ajout de données à partir des lignes d'une autre table

INSERT INTO TABLE1 (colonne1, colonne2, colonne3)

SELECT colonne10, colonne20, (colonne30 + colonne40) / 2 FROM TABLE2;

Modifier table, enregistrement ou valeur

Modifier valeur d'un colonne/champs/attribut d’un enregistrement

UPDATE nom_table SET nom_champs = 'blabla' WHERE id_table = 2; UPDATE players SET nb_connections = nb_connections + 1;

UPDATE Qualifier SET value = 'terminal' WHERE qualifier_id = 2;

Modififer valeurs de toute une table à partir des informations d'une autre table

UPDATE TABLE1

SET   (colonne3, colonne4) = (SELECT colonne32, colonne13

FROM TABLE2

WHERE TABLE1.colonneX = TABLE2.colonneY)

WHERE colonneZ IN (SELECT col FROM TABLE2)

Retirer une colonne/champs/attribut

ALTER TABLE players DROP COLUMN age;

Ajouter une nouvelle colonne/champs/attribut

ALTER TABLE account ADD access INT;

ALTER TABLE players ADD COLUMN profession varchar(50);

Modifier type et nom d'un colonne/champs/attribut

ALTER TABLE table_name CHANGE column_name new_column_name VARCHAR(15);

Modifier type sans changer le nom du colonne/champs/attribut

ALTER TABLE table_name MODIFY column_name VARCHAR(15);

Effacer une clé primaire

ALTER TABLE table_name DROP PRIMARY KEY;

Ajouter une clé primaire

ALTER TABLE account ADD primary key(acc_id);

Créer un index (accélération de requètes)

CREATE INDEX index_name ON table_name(column_name);

 

Effacement conditionnel d'une enregistrement/ligne dans une table

DELETE FROM players WHERE age < 5;

DELETE FROM employees WHERE name = 'Alpha';

Effacement conditionnel de table

DROP TABLE IF EXISTS players;

Effacement conditionnel d'une base

DROP DATABASE IF EXISTS game;

Les commandes SELECT (Requêtes)

SELECT * FROM players;                //récupère tous les joueurs

Keyword

Description

SELECT

Retrieves fields from one or more tables.

FROM

Tables containing the fields.

WHERE

Criteria to restrict the records returned.

GROUP BY

Determines how the records should be grouped.

HAVING

Used with GROUP BY to specify the criteria for the grouped records.

ORDER BY

Criteria for ordering the records.

LIMIT

Limit the number of records returned.

Operateur

Description

=

Equal to

<> or !=

Not equal to

Less than

<=

Less than or equal to

greater than

>=

greater than or equal to

LIKE

Used to compare strings

BETWEEN

Checks for values between a range

IN

Checks for values in a list

NOT IN

Ensures the value is not in the list

SELECT profession, age FROM players WHERE ville = "Toulouse"; //récupère tous les joueurs de TOULOUSE

SELECT profession FROM players WHERE email LIKE "%@club-internet.fr"; //Affiche les professions

Les fonctions d’aggrégations

Function

Example

Description

AVG()

SELECT AVG(cost) FROM Invoice GROUP BY ClientID;

Returns the average value in a group of records. The example returns the average order for each customer.

COUNT()

SELECT COUNT(cost) FROM Invoice GROUP BY ClientID;

Returns the number of records in a group of records. The example returns the number of orders for each customer.

MAX()

SELECT MAX(cost) FROM Invoice GROUP BY ClientID;

Returns the largest value in a group of records. The example returns the largest order by each customer.

MIN()

SELECT MIN(cost) FROM Invoice GROUP BY ClientID;

Returns the lowest value in a group of records. The example returns the smallest order by each customer.

SUM()

SELECT SUM(cost) FROM Invoice GROUP BY ClientID;

Returns the sum of a field. The example returns the total amount spent by each customer.

 

SELECT count(*) FROM players;     //récupère le nombre de joueurs

 

SELECT * FROM players ORDER BY age; //récupère tous les joueurs classés par age

SELECT * FROM players GROUP BY profession; //récupère tous les joueurs classer par profession

SELECT * FROM players ORDER BY age DESC LIMIT 10; //Seulement les 10 premier joueurs les plus agés

SELECT DISTINCT Directory FROM search; //ensure that one record is returned for each Directory name

SELECT Directory, COUNT(*) FROM search GROUP BY Directory; // SELECT * FROM my_table WHERE name = 'Smith’ AND age < 30;

SELECT * FROM my_table WHERE age BETWEEN 30 AND 40; //selectionner les personnes ayant entre 30 et 40 ans

SELECT * FROM my_table WHERE name LIKE 'S____'; //les noms commençant par S et ayant 4 caractères après le S

SELECT id, when_date, when_time, title, authors FROM phase_3 WHERE cat_author='1' AND title LIKE '%cow%'; // récupère tous les auteurs de catégorie 1 et dont les titres des ouvranges contienne “cow”

Il est également possible de faire intervenir des opérateurs logiques (booléens) (AND, OR, NOT). Expressions régulières

Metacharacter

Description

\\n

New line (line feed)

\\f

Form feed

\\t

Tab

\\r

Carriage return

\\v

Vertical tab

Class Keyword

Description of Matches

[[:alnum:]]

Alphanumeric - any number or letter. Equivalent to [a-z], [A-Z] and [0-9]

[[:alpha:]]

Alpha - any letter. Equivalent to [a-z] and [A-Z]

[[:blank:]]

Space or Tab. Equivalent to [\\t] and [ ]

[[:cntrl:]]

ASCII Control Character

[[:digit:]]

Numeric. Equivalent to [0-9]

[[:graph:]]

Any character with the exception of space

[[:lower:]]

Lower case letters. Equivalent to [a-z]

[[:print:]]

Any printable character

[[:punct:]]

Characters that are neither control characters, nor alphanumeric (i.e punctuation characters)

[[:space:]]

Any whitespace character (tab, new line, form feed, space etc)

[[:upper:]]

Upper case letters. Equivalent to [A-Z]

[[:xdigit:]]

Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9]

 

Metacharacter

Description

*

Any number of matches

+

One or more matches

{n}

n number of matches

{n,}

Not less than n number of matches

{n1,n2}

A range of matches between n1 and n2

?

Optional single character match (character my be present or not to qualify for a match)

Metacharacter

Description

^

Beginning of text

$

End of text

[[:<:]]

Start of word

[[:>:]]

End of word

SELECT * FROM product WHERE prod_name REGEXP 'DVD-RW M.del [1-6]543';

Jointures entre tables

SELECT author.Surname, author.Forename, search.Directory, search.Page FROM search, author WHERE author.AuthorID = search.AuthorID;

SELECT start, stop, term

FROM Sequence, Ontology, Feature, Location WHERE accession = 'seq1' AND term = 'exon' AND Feature.ontology_id = Ontology.ontology_id AND Feature.sequence_id = Sequence.sequence_id AND Feature.feature_id = Location.feature_id;

Nommer le résultat d’une requête / Raccourcis

SELECT Directory, COUNT(*) AS 'Number of Entries' FROM search GROUP BY Directory;

Sous requêtes

SELECT * FROM table WHERE colonne1 = (SELECT colonne2 FROM table2);

SELECT * FROM table WHERE colonne = (SELECT colonne2 FROM table2 WHERE colonne2 = (SELECT colonne3 FROM table3));

Opérateurs pour sous requêtes : IN and NOT IN

SELECT numero_tel FROM clients WHERE numero_client NOT IN ( SELECT numero_tel FROM numero_employe WHERE salaire < 2000 );

Autres opérateurs

EXISTS, IS NULL, NOT EQUAL TO (<>) , LIKE (%)

Note: Pour certaine version SQL le séparateur <> peut être écrit comme != Sélectionner une sous-partie d’une chaîne de caractères (string)

SELECT SUBSTRING('Quadratically',5);

Résultat: 'ratically'

SELECT SUBSTRING('foobarbar' FROM 4);

Résultat: 'barbar'

SELECT SUBSTRING('Quadratically',5,6);

Résultat: 'ratica'

 

Faire une union de tables avec MySQL

create

temporary table

temp   ( ...

);

insert

into temp select

blabla

from

tablewhere       ...

_name

;

insert
select
delete

into temp select * from temp; temp;

blabla

from

table_name where ...

;

Cours SQL Server en ligne : Commandes et Instructions de base



1859
x