MySQL

From UA Libraries Digital Services Planning and Documentation
Jump to: navigation, search

NOTE: Some overview of our databases is available on the wiki:Tracking_for_the_long_term

NOTE: The user "ds" while logged in on libcontent now has "select" privileges on all the databases we support (with no password). "ds" also has full privileges on the "test" database - you can do whatever you want there. You can find command info here: https://mariadb.com/kb/en/mariadb/documentation/sql-commands/

1) to access the databases, log in:

       mysql -u {username}

substituting the user name for {username}. The ds user does not currently have a password. If you have a password, the command is:

       mysql -u {username} -p (enter)
       {password} (enter)

example:

       mysql -u ds -p
       secret

NOTE: If you get an error when you try to log in, such as "Can't connect to local MySQL server through socket" -- contact OIT for assistance. MySQL is down, and hence, so is Acumen.

2) To find out what databases are available to you, type in: show databases;

3) to select a database:

       use {databaseName};

example:

       use InfoTrack;

4) to find out what tables are in the database:

       show tables;

5) To find out what columns are in a table:

       show columns from {table};

example:

       show columns from allColls;

6) To select everything in a table:

       select * from {table};

example:

       select * from allColls;

7) To select a single column from a table:

       select {column} from allColls;

example:

       select title from allColls;

7) To limit selection, use a "where" phrase, putting cell contents in quotes. The general form of the statement is: SELECT -- FROM {table} WHERE {column} LIKE {value} ORDER BY {column}; examples:

       select title from allColls where id_2009 like "u0003_0000580";
       select title from allColls where id_2009 like "u0003_0000580" and analogOrDigital like "D";
       select title from allColls where title like "%Gorgas%" and analogOrDigital like "D";

(NOTE: "%" is a wildcard.)

       select title from allColls where analogOrDigital like "D" order by id_2009;
Personal tools