MariaDB基本操作记录

所有保留字都是大写字母

mysql -u root -p -h localhost
/*
-u 设置username
-p 设置password
-h 设置host
作用： log in as the root user and to enter monitor mode
*/


CREATE DATABASE bookstore;
USE bookstore;
// create a database structure


CREATE TABLE books (
isbn CHAR(20) PRIMARY KEY, 
title VARCHAR(50),
author_id INT,
publisher_id INT,
year_pub CHAR(4),
description TEXT );
// create tables


DESCRIBE books;
// show tables


ALTER table
// change the setting ?? 


DROP table 
// delete the table, not reversible


INSERT INTO authors
(name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic');
// insert to table


SELECT title 
FROM books;
// RETRIVE a column from table


SELECT title 
FROM books
LIMIT 5;
// limit the number to show


SELECT title, name_last 
FROM books 
JOIN authors USING (author_id);
// JOIN another table


SELECT title AS 'Kafka Books'
FROM books 
JOIN authors USING (author_id)
WHERE name_last = 'Kafka';
// To retrieve the titles of only books written by Kafka based on his name (not the author_id),
// WHERE 确定哪一行


UPDATE books
SET title = 'Amerika'
WHERE isbn = '0805210644';
// change existing data, This will change the value of the title column for the row specified


DELETE FROM books
WHERE author_id = '2034';
// delete some rows