mysql Syntax Examples
WIMP: Window, IIS, mysql, PHP
Oliver Bromley, Dec 2009 Vr.155 ========================== Install mysql Install obdc 3.51.06 connector from an exe file Add an ODBC datasource: Add a path to the javac compiler From the desktop, right-click My Computer and click properties. # In the System Properties window, click on the Advanced tab. # In the Advanced section, click the Environment Variables button. # Finally, in the Environment Variables window, highlight the path variable in the Systems Variable section and click edit. add the path to javac you'll add something like this to the front. Be sure to end it with a semi colon. C:\Program Files\Java\jdk1.6.0_20\bin; DSN Information --------------- testMySqlOdbc MySQL ODBC 3.51 Driver DSN MySQL Connection Parameters --------------------------- localhost [Host/Server Name (or IP) bdword [or test, the database name] root [or blank, for User] blank [password] none 3306 [Port] GOOD mysql --user root -p now give password.... BAD shell> mysql --user=root mysql Problem ------- I get Ignoring query to other database message Solution -------- For example: $ mysql -root -p Attempts to login using current o/s user. If your password is valid, then you are logged in as the [o/s user]@localhost, not as root. And the command line mysql options -r, -o, and -t (-root) are then in effect. Username should be entered as: $ mysql -uroot -p or $ mysql --user=root --password See mysql.cmd in C:\0_repository\155_repository --- cls c: cd c:\Program Files\MySQL\MySQL Server 5.1\bin echo password is password mysql -uroot -p pause --- show databases; create database bdworld; use bdworld; create table system_t (system_id int not null, parent_system_id int, attribute varchar(100), value text); show tables; mysql --user=root mysql mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql --user=oliver@localhost ? : help \q : quit \s : server details grab.biol.soton.ac.uk ps waux |grep mysql : Show commands username = oliver [NOT ROOT] pass = 0dyss3y [zero not O] database = bdworld Use putty to log onto server, grab.biol.soton.ac.uk, then mysql -p bdworld [the -p says i'll give a password later, and the bdworld refers to the particular database we'll be looking at] //---Backup In case it contains anything which isn't easy to recreate, I have just dumped the contents into an SQL file (attached) using "mysqldump -h localhost -u oliver -p bdworld". (There is also a copy in /home/oliver on grab.) This is a routine you should do at regular intervals from now on in order to maintain a set of backups. //---Reinstate All you have to do to reinstate the old contents, if we forget to do so, is to use a mysql client and execute the contents of the SQL file using the MySQL command "". You may need to drop the database table first, because the SQL file tries to recreate it. I'm not sure whether this stops the whole run if the table already exists. However, dropping the table will ensure that all our test data is removed and the database is returned to its original state. =========================================mysql syntax examples select version(), current_date show databases select user(); use test; create table entity (entity varchar(100), attribute varchar(100), value varchar(100)); show tables; describe entity; delete from entity; :WARNING : Blanks all the table ! insert into system_t values (1,0,'system_id','1 (whole system)'); select from system_t; \c. Cancel a command, should be like Control C -------------------------- mysql --user=root mysql mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@'%' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysql> GRANT USAGE ON *.* TO dummy@localhost; -------------------------- shell> mysql --user=root mysql mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON bankaccount.* -> TO custom@localhost -> IDENTIFIED BY 'obscure'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON expenses.* -> TO custom@'whitehouse.gov' -> IDENTIFIED BY 'obscure'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON customer.* -> TO custom@'server.domain' -> IDENTIFIED BY 'obscure'; ------------------------------------ GRANT SHOW DATABASES ON *.* TO hubert@localhost IDENTIFIED BY 'password'; -------------------------------- groupadd mysql useradd -g mysql -c "MySQL Server" -d /dev/null -s /sbin/nologin mysql ------------------------------------------------- drop table system_t; : remove table data and structure and existence !!!!! ------------------------------- CREATE TABLE category ( num INT NOT NULL auto_increment, date_modified DATE DEFAULT '0000-00-00' NOT NULL, name VARCHAR(50) DEFAULT '' NOT NULL, description VARCHAR(75), keywords VARCHAR(200), status INT DEFAULT '0' NOT NULL, KEY xdate (date_modified), KEY xname (name), -------------------------------------- create table system_t (system_id int not null, parent_system_id int, attribute varchar(100), value varchar(100)); -------------------------------------------- PRIMARY KEY (num) ); ---------------------- delete from system_t where system_id=9 and parent_system_id=7; -------------------------------- To remove phantoms:>> SELECT * FROM references_table LEFT JOIN has_id_table USING (id_field) WHERE has_id_table.id_field IS NULL --------------- select last_insert_id(); ----------------------- alter table system_t AUTO_INCREMENT=o; ---------------------------------------------------------Tims creation sql [oliver@grab:oliver]mysqlshow mysqlshow: Access denied for user: 'oliver@localhost' (Using password: NO) [oliver@grab:oliver]mysqlshow -p Enter password: +----------------------------+ | Databases | +----------------------------+ | Sp2000AnnualChecklist_2000 | | bdworld | | mysql | | test | +----------------------------+ [oliver@grab:oliver]mysqld mysqld_multi mysqldump mysqldumpslow [oliver@grab:oliver]mysqldump bdworld -p > bdworld.sql Enter password: [oliver@grab:oliver]vim bdworld.sql [oliver@grab:oliver] ----------------------------------------restore from sql script mysql -p bdworld < /tmp/132_repository/bdworld.sql ----------------------------------------------------------Tims examples mySqlString = "select * from tbl_locality where institution='" mySqlString = "insert into tbl_locality (institution,accession_no,taxon_name,lat,long) " + mySqlString = "select * from tbl_results_set where result_id=" mySqlString = "select distinct synonym from view_synonym_list where synonym_list_id="; mySqlString = "select * from tbl_sequence where accession_no='" mySqlString = "delete from tbl_sequence where sequence_id=" + mySequenceId ; mySqlString = "insert into tbl_results_set_sequence_link values (" + theResultsetId + "," + mySequenceId + ")"; String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where list_name='" mySqlString = "insert into tbl_synonym_list (list_name) values ('" mySqlString = "select * from tbl_synonym_list where oid=" mySqlString = "insert into tbl_synonym (synonym_list_id, taxon, synonym) values (" mySqlString = "delete from tbl_synonym_list where list_name='" mySqlString = "delete from tbl_synonym_list where synonym_list_id=" String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list"; String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where list_name='" String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where synonym_list_id=" mySqlString = "select * from tbl_synonym where synonym_list_id=" String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where list_name='" mySqlString = "insert into tbl_synonym_list (list_name) values ('" mySqlString = "select * from tbl_synonym_list where oid=" mySqlString = "insert into tbl_synonym (synonym_list_id, taxon, synonym) values (" mySqlString = "delete from tbl_synonym_list where list_name='" mySqlString = "delete from tbl_synonym_list where synonym_list_id=" String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list"; String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where list_name='" String mySqlString = "select synonym_list_id,list_name from tbl_synonym_list where synonym_list_id=" mySqlString = "select * from tbl_synonym where synonym_list_id=" String mySqlString = "select user_id from tbl_user where username='public'"; mySqlString = "insert into tbl_results_set (user_id,comment, synonym_list_id, search_keywords) values (" mySqlString = "select result_id from tbl_results_set where oid=" String mySqlString = "select synonym_list_id, list_name from tbl_synonym_list"; String mySqlString = "select user_id from tbl_user where username='public'"; mySqlString = "insert into tbl_sequence (user_id,accession_no,organism_name,sequence,length,molecule,source,full _record) values('" mySqlString = "select sequence_id from tbl_sequence where oid=" mySqlString = "insert into tbl_sequence_feature (sequence_id,qualifier,qualifier_value) values(" mySqlString = "insert into tbl_sequence_feature (sequence_id,qualifier,qualifier_value) values(" mySqlString = "insert into tbl_sequence_feature (sequence_id,qualifier,qualifier_value) values('" mySqlString = "insert into tbl_sequence_feature (sequence_id,qualifier,qualifier_value) values('" mySqlString = "insert into tbl_sequence_feature (sequence_id,qualifier,qualifier_value) values('" mySqlString = "insert into tbl_reference (author,title,journal) values('" mySqlString = "select reference_id from tbl_reference where " mySqlString = "insert into tbl_sequence_reference_link (reference_id,sequence_id) values(" mySqlString = "delete from tbl_sequence where accession_no='" mySqlString = "Select * from tbl_sequence where accession_no=" String mySqlString="Select gene_name from view_gene_summary where result_id=theResultSetIdInt"; //String mySqlString="select '>' || accession_no || ':' || taxon || '\n' || substr(substr(sequence,9),0,length(substr(sequence,9))-2) as fasta from view_gene_taxon_sequence where sequence_id in (select sequence_id from tbl_results_set_sequence_link where result_id=" + myResultSetIdString + ")"; String mySqlString="Select FASTA from view_FASTA where result_id = " + myResultSetIdString + //String mySqlString="select '>' || accession_no || ':' || taxon || '\n' || substr(substr(sequence,9),0,length(substr(sequence,9))-2) as fasta from view_gene_taxon_sequence where sequence_id in (select sequence_id from tbl_results_set_sequence_link where result_id=" + myResultSetIdString + ")"; String mySqlString = "select layerset_id,name from tbl_layerset ";
From 2009, April ---- http://bytes.com/groups/php/12894-trouble-escaping-misc-nightmare $id = mysql_escape_string($_REQUEST[id]); $sql = "select * from the_table where ID='$id'"; http://php.activeventure.com/function/m/function.mysql-escape-string.html string mysql_real_escape_string ( string unescaped_string [, resource link_identifier]) unescaped_string The string to escape link_identifier (optional) The mysql connection resource This function will escape special characters in the unescaped_string, taking into account the current charset of the connection so that it is safe to place it in a mysql_query(). ----------- http://w3schools.invisionzone.com/index.php?showtopic=20064 mysql_query('SELECT * FROM ' . mysql_real_escape_string($_POST['table'], $conn) . ' WHERE something= ' . mysql_real_escape_string($_POST['something'], $conn), $conn); mysql> SET @t1=0, @t2=0, @t3=0; mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +----------------------+------+------+------+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+ ====================== Source: http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html There are several ways to include quote characters within a string: * A “'” inside a string quoted with “'” may be written as “''”. * A “"” inside a string quoted with “"” may be written as “""”. * Precede the quote character by an escape character (“\”). * A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment. The following SELECT statements demonstrate how quoting and escaping work: mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | ''hello'' | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT 'This\nIs\nFour\nLines'; +--------------------+ | This Is Four Lines | +--------------------+ mysql> SELECT 'disappearing\ backslash'; +------------------------+ | disappearing backslash | +------------------------+ --------------------------------------------- http://www.phpclasses.org/browse/file/22720.html function isURLExists($pmDomain, $pmURL) { mysql_connect($this->mDBHost,$this->mDBUserName,$this->mDBPassword); mysql_select_db($this->mDBDatabase); $vSQL = "SELECT count( id ) AS cnt FROM spider WHERE domain = '$pmDomain' and url = '$pmURL'"; $rs = mysql_query($vSQL); $oRecord = mysql_fetch_assoc($rs); return $oRecord['cnt']; } =========================================end of my_sql_syntax.txt