WHAT DO YOU MEAN YOU WERE ONLY JOKING WHEN YOU SAID “DROP DATABASE *” WAS A REPAIR COMMAND – lol

REFERENCE

mysql

Emulate Oracle's ROWNUM :

DROP TABLE scores ;

CREATE TABLE scores (
id int(8) NOT NULL AUTO_INCREMENT,
value int(8) DEFAULT 0, 
rank int(8) DEFAULT 0,
PRIMARY KEY (id)
);

INSERT INTO scores 
(id, value, rank) 
VALUES ('1', '10', '0'), ('2', '20', '0');

UPDATE scores
SET value = value+1;

SET @rownum =0; 
UPDATE scores t,
(
SELECT @rownum := @rownum +1 rownum, scores. *
FROM scores
ORDER BY value DESC
) r
SET t.rank = r.rownum 
WHERE ( t.id = r.id);

SELECT * from scores
ORDER BY rank ASC;

SELECT *,@bonus:=CONCAT(rank,value) 
FROM scores
ORDER BY @bonus;

Misc:

mysqldump --add-drop-table --extended-insert -u ${USER} -p ${PASSWORD} ${USER}

Howto :

sudo aptitude install mysql-server
mysql> create database tmp

SQLLITE

DROP TABLE lines ;

CREATE TABLE lines (
id int(8) PRIMARY KEY,
line TEXT
);

/* SQL error: lines.id may not be NULL */
INSERT INTO lines (id,line) VALUES ( 1, 'one') ;
INSERT INTO lines (id,line) VALUES ( 2, 'two') ;
INSERT INTO lines  VALUES ( NULL, 'three') ;
INSERT INTO lines (id,line) VALUES ( null, 'four') ;
INSERT INTO lines (line) VALUES ( 'five' ) ;

SELECT COUNT(*) FROM lines;
/* #| 5 */


#  sqlite3 "${file}" ".dump"
 cd $HOME/.config/f-spot
 file=photos.db  
 sqlite3 "${file}" ".dump" | tee $file.sql
 grep -o  "file://[^\']*" $file.sql | sort | uniq | tee $file.txt

RAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (10) disk I/O error *****/

@more: mono db pictures f-spot database

todo

mysql

“mysqldump: Got error: 1045: Access denied for user”

Checking for corrupt, not cleanly closed and upgrade needing tables..

sudo aptitude reinstall --purge mysql-server-5.0
sudo dpkg-reconfigure --force --priority=low mysql-server-5.0
Stopping MySQL database server: mysqld.
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

mysqlcheck --repair --all-databases
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
mysql --user=root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.0.51a-17 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database tmp;

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

SQLITE

How to replace paths in fspot datatabase :

sudo apt-get install sqlite3
sqlite3 -list ~/.config/f-spot/photos.db ".dump" | less

How to export / merge tags :

TSQL : MICROSOFT

misc

tags

sql.txt · Last modified: 2014/09/04 22:10 (external edit)
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki