Search This Blog

2008-01-09

mysql höpölöpö


1 MySQL
2
3 echo Start the mysql as the root user using SecretPass password
4
5 echo Note that the clear command does not work from the shell so use the Ctrl + l shortcut to clear the messy screen
6 [mysql dir]/bin/mysql -h hostname -u root -p SecretPass
7
8 echo show the existing databases on this host (show present something like this )
9
10 show databases ;
11
12 mysql> show databases ;
13 +--------------------+
14 | Database |
15 +--------------------+
16 | information_schema |
17 | mysql |
18 +--------------------+
19 5 rows in set (0.00 sec)
20
21
22
23 echo running mysql in batch mode
24
25 shell> mysql -h host -u user -pSecretPass < /path/to/batch-file
26
27 ou can catch the output in a file for further processing:
28 shell> mysql < batch-file > mysql.out
29
30
31
32 DROP DATABASE `db_transpub` ;
33 CREATE DATABASE `db_transpub` DEFAULT CHARACTER SET utf8 ;
34 use db_transpub ;
35 DROP TABLE IF EXISTS `tb_contactDetails`;
36 CREATE TABLE `tb_contactDetails` (firstname VARCHAR(20), middlename VARCHAR(20), lastname VARCHAR(35),officeid VARCHAR(10),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
37 VARCHAR(15),datestamp DATE,timestamp time, userId VARCHAR(15), PRIMARY KEY (`userId`));
38
39
40 miscelanious
41 echo how to get the version of mysql select version() phpMyAdmin provides php interface to mysql database(s)
42 MySQL Cheat Sheet
43
44 http://dev.mysql.com/doc/
45
46 search the documentation
47
48 To login (from unix shell) use -h only if needed.
49
50 [mysql dir]/bin/mysql -h hostname -u root -p
51 Create a database on the sql server.
52
53 create database [databasename];
54 List all databases on the sql server.
55
56 show databases;
57 Switch to a database.
58
59 use [db name];
60 To see all the tables in the db.
61
62 show tables;
63 To see database field formats.
64
65 describe [table name];
66 To delete a db.
67
68 drop database [database name];
69 To delete a table.
70
71 drop table [table name];
72 Show all data in a table.
73
74 SELECT * FROM [table name];
75 Returns the columns and column information pertaining to the designated table.
76
77 show columns from [table name];
78 Show certain selected rows with the value "whatever".
79
80 SELECT * FROM [table name] WHERE [field name] = "whatever";
81 Show all records containing the name "Bob" AND the phone number '3444444'.
82
83 SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
84 Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
85
86 SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
87 Show all records starting with the letters 'bob' AND the phone number '3444444'.
88
89 SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
90 Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
91
92 SELECT * FROM [table name] WHERE rec RLIKE "^a$";
93 Show unique records.
94
95 SELECT DISTINCT [column name] FROM [table name];
96 Show selected records sorted in an ascending (asc) or descending (desc).
97
98 SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
99 Return number of rows.
100
101 SELECT COUNT(*) FROM [table name];
102 Sum column.
103
104 SELECT SUM(*) FROM [table name];
105 Join tables on common columns.
106
107 select lookup.illustrationid, lookup.personid,person.birthday from lookup
108 left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
109 Switch to the mysql db. Create a new user.
110
111 INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
112 Change a users password.(from unix shell).
113
114 [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
115 Change a users password.(from MySQL prompt).
116
117 SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
118 Allow the user "bob" to connect to the server from localhost using the password "passwd"
119
120 grant usage on *.* to bob@localhost identified by 'passwd';
121 Switch to mysql db.Give user privilages for a db.
122
123 INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
124
125 or
126
127 grant all privileges on databasename.* to username@localhost;
128 To update info already in a table.
129
130 UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
131 Delete a row(s) from a table.
132
133 DELETE from [table name] where [field name] = 'whatever';
134 Update database permissions/privilages.
135
136 FLUSH PRIVILEGES;
137 Delete a column.
138
139 alter table [table name] drop column [column name];
140 Add a new column to db.
141
142 alter table [table name] add column [new column name] varchar (20);
143 Change column name.
144
145 alter table [table name] change [old column name] [new column name] varchar (50);
146 Make a unique column so you get no dupes.
147
148 alter table [table name] add unique ([column name]);
149 Make a column bigger.
150
151 alter table [table name] modify [column name] VARCHAR(3);
152 Delete unique from table.
153
154 alter table [table name] drop index [colmn name];
155 Load a CSV file into a table.
156
157 LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
158 Dump all databases for backup. Backup file is sql commands to recreate all db's.
159
160 [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
161 Dump one database for backup.
162
163 [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
164 Dump a table from a database.
165
166 [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
167 Restore database (or database table) from backup.
168
169 [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
170 Create Table Example 1.
171
172 CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
173 VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
174 Create Table Example 2.
175
176 create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default '
bato');
177
178 ;who has what priviledges
179
180 show grants ;
181
182
183 simple setup of php based web interface to a mysql database
184
185 This tutorial uses Ubuntu 7.10 the Gutsy Gibbon, Apache 2.0 , php5 and mysql
186
187 The follwoing main phpases are used:
188
189
190
191 - install Apache
192
193 - install mysql
194
195 - configure mysql - add users , create databases on mysql
196
197 - configure Apache
198
199 - install myPhpAdmin
200
201 - configure myPhpAdmin
202
203
204
205 1.Install Apache
206
207 #INSTALL APACHE
208
209 sudo apt-get install apache2
210
211 #Start or stop Apache
212 sudo /etc/init.d/apache2 stop | start | restart
213 #if installation of previous packages has failed
214 dpkg --configure -a
215
216 #install PHP mod_php for Apache
217 sudo apt-get install php5 libapache2-mod-php5
218
219 echo '
<?php echo "Haaudi World"; ?>' > /var/www/test.php
220 open http://localhost/test.php - > you should see Haudi World
221
222 2. Install mySQL
223
224 #if installation of previous packages has failed
225
226 dpkg --configure -a
227 sudo /etc/init.d/apache2 stop | start | restart
228
229 #install mysql server
230 sudo apt-get install mysql-server
231 #when prompted type in your password
232
233 #if not prompted the command to get to the mysql prompt is :
234
235 mysql -uroot -pmysql
236
237 #set the password with the following:
238 mysql> SET PASSWORD FOR '
root'@'localhost' = PASSWORD('SecretPass');
239 #run the mysql with:
240 mysql -uroot -pSecretPass
241
242 #view the databases
243
244 show databases ;
245
246 #create a database with one table
247
248 create database testdb ;
249
250 use db_test
251
252 CREATE TABLE tb_testtable(
253 column0 INT PRIMARY KEY AUTO_INCREMENT,
254 column1 VARCHAR(100),
255 column2 VARCHAR(10) ) TYPE=INNODB;
256
257 3. Intall php
258
259 #install the php admin
260 sudo apt-get install libapache2-mod-auth-mysql php5-mysql
261 #when prompted configure the type of servers
262
263 #Other php modules usefull for development
264 apt-get install php5-gd
265 apt-get install php5-ldap
266 apt-get install php5-mysql
267 apt-get install php5-openssl
268 apt-get install php5-pear
269
270 4. Install phpMyAdmin
271
272 Download the latest stable build of phpMyAdmin [[http://surfnet.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.11.3-all-languages.tar.bz2]]
273
274 #place the tar under a phpMyAdmin folder (you would have to create it mkdir /var/www/phpMyAdmin ) under your doc root folder
275
276 mv /home/user/downloads/phpMyAdmin-2.11.3-all-languages/ /var/www/phpMyAdmin
277
278 tar -xvf phpMyAdmin-2.11.3-all-languages.tar.bz2
279
280 #configure the most sipliest (not sucure !!!) setup
281
282 vim /var/www/phpMyAdmin/config.inc.php
283
284 paste the following code in it:
285
286 ==============================COPY PASTE START
287
288 <?php
289
290 $cfg['
blowfish_secret']='shazaam' ; // use here a value of your choice
291 $i=0;
292 $i++;
293 $cfg['
Servers'][$i]['auth_type'] = 'cookie';
294 ?>
295
296 ==============================COPY PASTE END
297
298 Go to the login page :
299
300 http://localhost/phpMyAdmin/
301
302 Login in with the same password you set up during the mysql configuration(here user:root pass:SecretPass)
303
304




syntax highlighted by Code2HTML, v. 0.9.1

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (14) sql server 2008 (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) git cheat sheet (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) linux cheat sheet (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) aws cheat sheet cli (1) aws cli (1) aws cli amazon cheat sheet (1) aws elb (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) isg-pub issue-tracker architecture (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) openssl (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vbox virtual box cheat sheet (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

VideoBar

This content is not yet available over encrypted connections.