0 Posted 2021-01-31Updated 2023-06-06Linux / Data6 minutes read (About 953 words)

Quick Guide of Mysql for Newbies

Dataabse type

Relation Database	Non-relational Database
- Store specific types of project like customer, products. - Store as tables	- No tables or relationships - Don’t understand SQL

INSTALL

For Ubuntu:雪梦科技 2020; 云栖社区, Mac: WebCoder 2016, 简书; Windows: BruceLong 2020; 博客园

Quick Start

mysql

-- print store location
show variables like '%datadir%';

-- print database in stoarage
SHOW DATABASES;

-- Create a new database
CREATE DATABASE `sql_invoicing`;
-- Access the database
USE `sql_invoicing`;

-- Create a table
CREATE TABLE runoob_tbl(
  runoob_id INT NOT NULL AUTO_INCREMENT,
  runoob_title VARCHAR(100) NOT NULL,
  runoob_author VARCHAR(40) NOT NULL,
  submission_date DATE,
  PRIMARY KEY ( runoob_id )
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- show tables
SHOW TABLES

-- Check the index
desc runoob_tbl

/*
INT: datatype = integer;
DATE: datatype = date;
NOT NULL: value auto-fill;
AUTO_INCREMENT:  fill-logic;
VARCHAR(10): ??? I don't know
*/
-- Insert value to table
INSERT INTO runoob_tbl
  (runoob_title, runoob_author, submission_date)
  VALUES
  ("学习 PHP", "菜鸟教程", NOW());

-- Insert multi-lines value to table
INSERT INTO runoob_tbl
  (runoob_title, runoob_author, submission_date)
  VALUES
  ("学习 myspl", "karobben", NOW()),
  ('learn python', 'Karobben', NOW());

-- Print the whole table
SELECT * from runoob_tbl;
-- Print the `runoob_title` column(field)
SELECT `runoob_title` from runoob_tbl;
-- Print the filtered result by WHEN
SELECT * from runoob_tbl WHERE runoob_author='karobben'; #Capitals letters tolerant
SELECT * from runoob_tbl WHERE BINARY runoob_author='Karobben'; #Capital letter matters
-- Print the filtered result by LIKE
SELECT * from runoob_tbl  WHERE runoob_author LIKE '%rob%';

-- Change the value of tables by update
UPDATE runoob_tbl SET runoob_title='sleep' WHERE runoob_id=3;
-- Change a part of string in column(field)
UPDATE runoob_tbl  SET runoob_author=REPLACE(runoob_author, 'ka', 'Ka')
-- Change the value of table by delete
DELETE FROM runoob_tbl WHERE runoob_id=1;

-- delete the tale
DROP TABLE `runoob_tbl`;
-- delete the database
DROP DATABASE `sql_invoicing`

Set Environment

Change the store directory: 虫文儿~ 2019; 博客园

Show the directory of the DATABASE

show variables like '%datadir%';

Show all databases

SHOW DATABASES;

Create & Drop

Database

ps: ‘;’ is needed for separate commands in sql language.

CREATE DATABASE IF NOT EXISTS `sql_invoicing`;
USE `sql_invoicing`;

SET NAMES utf8;
SET character_set_client= utf8mb4;

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sql_invoicing      |
| sys                |
+--------------------+

drop database `sql_invoicing`; #deleting sql_invoicing

Table

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW TABLES;
desc runoob_tbl;

+-------------------------+
| Tables_in_sql_invoicing |
+-------------------------+
| runoob_tbl              |
+-------------------------+
1 row in set (0.00 sec)

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| runoob_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100) | NO   |     | NULL    |                |
| runoob_author   | varchar(40)  | NO   |     | NULL    |                |
| submission_date | date         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

DROP TABLE runoob_tbl;

Datatypes

Details: runoob.com

INSERT

Insert a Row

-- Insert value to table
INSERT INTO runoob_tbl
  (runoob_title, runoob_author, submission_date)
  VALUES
  ("学习 PHP", "菜鸟教程", NOW());

-- Insert multi-lines value to table
INSERT INTO runoob_tbl
  (runoob_title, runoob_author, submission_date)
  VALUES
  ("学习 myspl", "karobben", NOW()),
  ('learn python', 'Karobben', NOW());

ALTER (insert a column)

ALTER TABLE runoob_tbl
ADD COLUMN Citation VARCHAR(15) AFTER runoob_author;

Select the column

WHERE

SELECT * from runoob_tbl WHERE runoob_author='karobben';

+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | 学习 myspl   | Karobben      | 2021-01-31      |
|         3 | sleep        | Karobben      | 2021-01-31      |
|         4 | learn python | Karobben      | 2021-01-31      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

LIKE

-- match the column which end with a| grep a$
'%a'     
-- match the column which start with a| grep ^a
'a%'     
-- match the column which contain a| grep a
'%a%'    
-- match pattern: grep ^.a.$
'_a_'    
-- match pattern: grep ^.a$
'_a'
-- match pattern: grep ^a$
'a_'

SELECT * from runoob_tbl  WHERE runoob_title LIKE '% %';

+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | 学习 myspl   | Karobben      | 2021-01-31      |
|         4 | learn python | Karobben      | 2021-01-31      |
+-----------+--------------+---------------+-----------------+
2 rows in set (0.00 sec)

UPDATE & DELETE

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
DELETE FROM runoob_tbl WHERE runoob_id=3;

UNION

So, union could combine the unique values from different selected columns to a new table
sql SELECT runoob_title FROM runoob_tbl UNION SELECT runoob_author FROM runoob_tbl;

+--------------+
| runoob_title |
+--------------+
| 学习 myspl   |
| sleep        |
| learn python |
| Karobben     |
+--------------+

it can also be used to merge tables: Example

ORDER

SELECT * from runoob_tbl;
SELECT * from runoob_tbl ORDER BY runoob_title ASC;

+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | 学习 myspl   | Karobben      | 2021-01-31      |
|         3 | sleep        | Karobben      | 2021-01-31      |
|         4 | learn python | Karobben      | 2021-01-31      |
+-----------+--------------+---------------+-----------------+
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         4 | learn python | Karobben      | 2021-01-31      |
|         3 | sleep        | Karobben      | 2021-01-31      |
|         2 | 学习 myspl   | Karobben      | 2021-01-31      |
+-----------+--------------+---------------+-----------------+

INPUT and Output

Input

-- insert and replace
LOAD DATA INFILE '/var/lib/mysql-files/test.csv' REPLACE
INTO TABLE regulation_net
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

IGNORE 1 ROWS;

output

## output sql as txt file
mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

-- output as csv
SELECT * FROM regulation_net;


SELECT * INTO OUTFILE '/var/lib/mysql-files/test.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM regulation_net;