Accueil > Programmation > mysql - mariadb > requetes mysql

requetes mysql

Publié le 24 avril 2018, dernière mise-à-jour le 23 février 2025, > 13 visites, >> visites totales.

sélectionner les permissiosn des utilisateurs

select * from information_schema.user_privileges;

GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
user1@%	def	USAGE	NO
user2@%	def	USAGE	NO
user3@%	def	USAGE	NO

fonctions

https://mariadb.com/kb/fr/concat/
concat ( chaine1, chaine2)
- ex : update set =concat(,'animaux')

concat_ws ( separateur,chaine1, chaine2)

ex :

update <span class="base64" title="PGNvZGUgY2xhc3M9J3NwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lJyBkaXI9J2x0cic+ZXNzYWk8L2NvZGU+"></span> set <span class="base64" title="PGNvZGUgY2xhc3M9J3NwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lJyBkaXI9J2x0cic+dmlzaXRlczwvY29kZT4="></span>=concat_ws(",",<span class="base64" title="PGNvZGUgY2xhc3M9J3NwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lJyBkaXI9J2x0cic+dmlzaXRlczwvY29kZT4="></span>,'animaux');

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html

INSERT
- INSERT [INTO] table [(colonne1,colonne2,colonne3,...)] VALUES (valeur1,valeur2,valeur3,...)
- on peut rajouter ON DUPLICATE KEY UPDATE
SELECT
- SELECT [options] items [INTO file_details] FROM tables [ WHERE conditions ] [ GROUPS BY group_type ] [ HAVING where_condition ] [ ORDER BY order_type DESC|ASC] [ LIMIT limit_criteria ] [ PROCEDURE proc_name(arguments)] [lock_options]
UPDATE
- UPDATE [LOW_PRIORITY] [IGNORE] nomtable SET colonne1=expression1,colonne2=expression2,... [WHERE condition] [ORDER BY critères de tri] [LIMITE nombre]
ALTER TABLE
CREATE DATABASE
CREATE USER / GRANT

SELECT

SELECT [options] items [INTO file_details] FROM tables [ WHERE conditions ] [ GROUPS BY group_type ] [ HAVING where_condition ] [ ORDER BY order_type DESC|ASC] [ LIMIT limit_criteria ] [ PROCEDURE proc_name(arguments)] [lock_options]


SELECT id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A WHERE 1 limit 0,25

SELECT id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A WHERE A.id<10 limit 0,25

SELECT A.id,B.id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A,<code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS B WHERE A.md5=B.md5 limit 0,25

SELECT A.id,B.id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A,<code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS B WHERE A.id!= B.id and A.md5=B.md5 limit 0,25

SELECT A.*,B.* FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A,<code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS B WHERE A.id!= B.id and A.md5=B.md5 limit 0,25



Q:
SELECT A.id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A WHERE A.id>= ALL ( select id FROM PYTHONS) limit 0,25
R:
1 réponse 


Q:
SELECT A.id FROM <code class='spip_code spip_code_inline' dir='ltr'>PYTHONS</code> AS A WHERE A.id> ANY ( select id FROM PYTHONS) limit 0,25
R:
TOTAL - 1 réponses


SELECT x,y,z FROM Points1 WHERE (x,y,z) in (SELECT x,y,z FROM Points2)

INSERT

INSERT [INTO] table [(colonne1,colonne2,colonne3,...)] VALUES (valeur1,valeur2,valeur3,...)

INSERT INTO points (x,y,z) VALUES (8.5,5.6,-5.6)

UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] nomtable SET colonne1=expression1,colonne2=expression2,... [WHERE condition] [ORDER BY critères de tri] [LIMITE nombre]

UPDATE homothetie set longueur=longueur * 1.5

UPDATE homothetie set longueur=longueur * 1.5 where id=5

UPDATE <code class='spip_code spip_code_inline' dir='ltr'>BLENDERSpoub</code> SET <code class='spip_code spip_code_inline' dir='ltr'>NomLong</code> = REPLACE(<code class='spip_code spip_code_inline' dir='ltr'>NomLong</code>, '/media/a/Elements', '/media/a/E2/Elements') WHERE <code class='spip_code spip_code_inline' dir='ltr'>NomLong</code> LIKE '%/media/a/Elements%' COLLATE utf8mb4_bin

CREATE DATABASE

CREATE DATABASE IF NOT EXISTS <code class='spip_code spip_code_inline' dir='ltr'>basededonnee</code> ;

mysql --user=root --password=mdp  < mysqm avec mysqm:'CREATE DATABASE IF NOT EXISTS <code class='spip_code spip_code_inline' dir='ltr'>basededonnee</code> ;'

CREATE USER

CREATE USER 'nom'@'localhost' IDENTIFIED BY 'mdp';

GRANT SELECT,INSERT,UPDATE ON <code class='spip_code spip_code_inline' dir='ltr'>basededonnee</code> . * TO 'nom'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT SELECT,INSERT,UPDATE ON micalaur.* TO basededonnee@localhost IDENTIFIED BY 'mdp';
GRANT ALL PRIVILEGES ON <code class='spip_code spip_code_inline' dir='ltr'>nom</code> . * TO 'nom'@'localhost';

CREATE USER 'a09.gfsd'@'localhost' IDENTIFIED BY 'gfd456456j';
GRANT SELECT,INSERT,UPDATE ON <code class='spip_code spip_code_inline' dir='ltr'>wind04.5</code> . * TO 'a04.5gfsd'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

CREATE USER 'nom'@'localhost' IDENTIFIED BY 'mdp';
GRANT SELECT,INSERT,UPDATE ON <code class='spip_code spip_code_inline' dir='ltr'>basededonnee</code> . * TO 'nom'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

CREATE USER 'a09.gfsd'@'localhost' IDENTIFIED BY 'ljkljklhj';# MySQL a retourné un résultat vide (aucune ligne).
 GRANT SELECT,INSERT,UPDATE ON <code class='spip_code spip_code_inline' dir='ltr'>wind09</code> . * TO 'a09.gfsd'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;# MySQL a retourné un résultat vide (aucune ligne).

chiffrage des données

INSERT INTO dossiersSecrets(titre, contenu) VALUES ('$titre', AES_ENCRYPT('AA','BB'));

SELECT titre, AES_DECRYPT(contenu,'motdepasse') AS contenu FROM dossiersSecrets;

[bruno sanchiz]

requetes mysql

sélectionner les permissiosn des utilisateurs

fonctions

chiffrage des données

Derniers articles modifiés

Programmation

Dans la même rubrique

Mots-clés