{"id":2132,"date":"2014-11-02T15:07:23","date_gmt":"2014-11-02T13:07:23","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=2132"},"modified":"2015-05-19T21:57:26","modified_gmt":"2015-05-19T19:57:26","slug":"mysql-mini-guide-useful-instructions","status":"publish","type":"post","link":"https:\/\/www.extradrm.com\/?p=2132","title":{"rendered":"Mysql user guide – useful instructions \/ fetch count rows"},"content":{"rendered":"

Renum\u00e9roter une table avec auto increment<\/strong><\/span>
\nset @i=0;
\nupdate myvideos set id=(@i:=@i+1);
\nALTER TABLE myvideos AUTO_INCREMENT = 1;<\/p>\n

Arrangement de zones :<\/strong><\/span><\/p>\n

ALTER table Auteurs
\nMODIFY COLUMN AuteurID\u00a0 mediumint(8)
\nAFTER notice_ref<\/p>\n

ALTER TABLE Auteurs
\nMODIFY COLUMN notice_ref mediumint( 8 )
\nAFTER `AuteurID`
\n————————————————
\nRENAME TABLE `jarid`.`a030` TO `jarid`.`Auteurs` ;<\/p>\n

D\u00e9tecter les doublons :<\/strong><\/span><\/p>\n

SELECT * , count( * )
\nFROM tisn
\nGROUP BY isn
\nHAVING count( * )>1<\/p>\n

SELECT * , count( * )
\nFROM a220
\nGROUP BY notice_ref
\nHAVING count( * ) >1
\nORDER BY notice_ref DESC<\/p>\n

SELECT DISTINCT a220
\nFROM `a220`<\/p>\n

Synchronisation de strutures de tables champs par champs :<\/strong><\/span><\/p>\n

INSERT INTO jarid (notice_id) SELECT isn FROM tisn;<\/p>\n

UPDATE jarid j, tia000 t
\nSET j.a020 = t.A000
\nWHERE j.notice_id = t.notice_id<\/p>\n

SELECT\u00a0\u00a0\u00a0\u00a0 ISN, Ville, Occ, Diffuseur, Editeur
\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE_000_A041
\nWHERE\u00a0\u00a0\u00a0\u00a0 Editeur is null<\/p>\n

===== Variantes sur cr\u00e9ation et jointure ====<\/p>\n

CREATE TABLE result AS
\n(SELECT first.*,
\nsecond.f1,
\nsecond.f2,
\nsecond.f3
\nFROM\u00a0\u00a0 first
\nINNER JOIN second
\nON first.id = second.id);<\/p>\n

CREATE TABLE events AS
\n(SELECT e010.*,
\ne110.summary
\nFROM\u00a0\u00a0 e010
\nINNER JOIN e110
\nON e010.isn = e110.col1);<\/p>\n

SELECT a.isn, a.nom
\nFROM e010duplicate a
\nINNER JOIN e010duplicate b ON a.nom = b.nom
\nWHERE a.isn <> b.isn<\/p>\n

============= VALEURS NULL =========================
\nSELECT *
\nFROM `jarid`
\nWHERE A0302 IS NOT NULL<\/p>\n

Injections Autorit\u00e9s par occurrence<\/span><\/strong>
\nUPDATE jarid j, A030 t
\nSET j.a0302 = t.A030
\nWHERE j.notice_id = t.notice_ref and t.occ = 2<\/p>\n

==============================================<\/p>\n

Restore a mysql database under ubuntu :<\/strong><\/p>\n

Type in console<\/p>\n

$ mysql -h adresse_ip -u user -p -D nom_de_la_base_de_donn\u00e9es < nom_du_fichier_sql<\/pre>\n
For example<\/strong><\/p>\n
$ mysql -h localhost -u root -ap -D mesvideos < mesvideos.sql<\/pre>\n
Backup one mysql Database under ubuntu :<\/strong>
\nType in console<\/p>\n
$ mysqldump -h adresse_ip -u user -p nom_de_la_base_de_donnees > nom_du_fichier_sql<\/pre>\n
For example<\/strong><\/p>\n
$ mysqldump -h localhost -u root -p mesvideos > mesvideos.sql<\/pre>\n
====================================================================<\/p>\n
MySQL Fetch using PHP<\/h1>\n
PHP provides huge list of MySQL functions to access database from front end. Among those functions, we are going to discuss about some of them that are used to fetch data from database. Such functions are differed with the type of results they are expected to return.<\/p>\n
In PHP, MySQL fetch results can be obtained by the following functions. All of these functions will fetch only one row per function call. If required, we should call such functions with a loop for getting subsequent MySQL fetch results row by row.<\/p>\n