solvedProblems_0517_mySQL

[Eavis]

mysql -u root -p
select User from mysql.user;
drop user guest@localhost;

[Eavis]

Day1 :successfully installed all the parts of the ActiveLearning project using the percent of cells to draw the kaplan-meier import clinical data into database

[Eavis]

Day1 to be solved How to insert data into database~~

[Eavis]

https://www.tutorialspoint.com/php/index.htm

[Eavis]

sudo -i

cd /var/lib/mysql/

You can find all the MySQL related binaries in/usr/bin and /usr/sbin. All the tables and databases will be created in the /var/lib/mysqldirectory.

[Eavis]

mysql user is the only one that can access to the directory, you can use root to access there and change permissions, or you can change the directory permissions to 740 with

chmod 740 /var/lib/mysql permissions are divided in 3 parts user/group/else. and you have

drwx------ d= directory

r= read = 4

w=write = 2

x=execute = 1

so the "owner" has read(4) + write(2) + execute(1) = 7 permissions in that directory

every one else doesn't

[Eavis]

Use the mysqladmin Utility to Obtain Server Status mysqladmin --version To make a connection to your MySQL server mysql -u root -p UNIX users will also want to put your MySQL directory in your PATH, so you won't have to keep typing out the full path every time you want to use the command-line client. export PATH=$PATH:/usr/bin:/usr/sbin run the MySQL server at boot time /etc/init.d/mysqld start

[Eavis]

Running and Shutting down MySQL Server First check if your MySQL server is running or not. ps -ef | grep mysqld If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command: root@host# cd /usr/bin ./safe_mysqld & Now, if you want to shut down an already running MySQL server, then you can do it by using the following command: root@host# cd /usr/bin ./mysqladmin -u root -p shutdown Enter password: ******

[Eavis]

root@host# mysql -u root -p Enter password:*** mysql> use mysql; Database changed mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.20 sec)

[Eavis]

mysql> FLUSH PRIVILEGES; Query OK, 1 row affected (0.01 sec) mysql> SELECT host, user, password FROM user WHERE user = 'guest'; +-----------+---------+------------------+ | host | user | password | +-----------+---------+------------------+ | localhost | guest | 6f8c114b58f2ce9e | +-----------+---------+------------------+ 1 row in set (0.00 sec)

Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it, then you won't be able to connect to MySQL using the new user account at least until the server is rebooted.

[Eavis]

show databases; use nuclei; show tables; show columns from tablename; show index from tablename;

[Eavis]

when you insert the wrong sentence use \c and press the return(enter) button;

[Eavis]

Create a MySQL Database

mysqladmin -u root -p create TUTORIALS This will create a MySQL database called TUTORIALS. mysqladmin -u root -p drop TUTORIALS

[Eavis]

MySQL Data Types:

 Numeric  Date and Time  String Types

[Eavis]

Create a MySQL Table:

CREATE TABLE table_name (column_name column_type);

mysql> CREATE TABLE tutorials_tbl( -> tutorial_id INT NOT NULL AUTO_INCREMENT, -> tutorial_title VARCHAR(100) NOT NULL, -> tutorial_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( tutorial_id ) -> );  Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.  Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.  Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.

DROP a MySQL Table:

DROP TABLE table_name ;

[Eavis]

INSERT INTO command

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

mysql> INSERT INTO tutorials_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("Learn PHP", "John Poul", NOW()); '2007-05-06' NOW() is a MySQL function, which returns the current date and time.

While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.

[Eavis]

Select Query:

SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N] SELECT * from tutorials_tbl

[Eavis]

Where Clause :

SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';

[Eavis]

UPDATE Query:

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] -> UPDATE tutorials_tbl
-> SET tutorial_title='Learning JAVA'
-> WHERE tutorial_id=3;

[Eavis]

Delete Query:

DELETE FROM table_name [WHERE Clause] DELETE FROM tutorials_tbl WHERE tutorial_id=3;

[Eavis]

LIKE Clause:

SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * from tutorials_tbl WHERE tutorial_author LIKE '%jay';

A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'" But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay".

[Eavis]

Sorting Results:

SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]  You can sort the returned result on any field, if that field is being listed out.  You can sort the result on more than one field.  You can use the keyword ASC or DESC to get result in ascending or descending order. By default, it's the ascending order.  You can use the WHERE...LIKE clause in the usual way to put a condition. mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC

[Eavis]

Using Join:

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a, tcount_tbl b -> WHERE a.tutorial_author = b.tutorial_author;

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a, tcount_tbl b -> WHERE a.tutorial_author = b.tutorial_author;

[Eavis]

MySQL LEFT JOIN:

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left. If I do a LEFT JOIN, I get all the records that match in the same way and in addition I get an extra record for each unmatched record in the left table of the join; thus ensuring (in my example) that every AUTHOR gets a mention. If not contain -- return NULL;

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b -> ON a.tutorial_author = b.tutorial_author;