Create user
We’ll create a user with the name testuser , and the password test123test!.
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123test!';
That’s it, congratulations! In just one command you’ve created your first MySQL user. However, this user won’t be able to do anything with MySQL until they are granted additional privileges. In fact, they won’t even be able to login without additional permissions.
Grant Permissions to MySQL User
The basic syntax for granting permissions is as follows:
GRANT permission ON database.table TO 'user'@'localhost';
Here is a short list of commonly used permissions :
- ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
- CREATE – Allow a user to create databases and tables.
- DELETE – Allow a user to delete rows from a table.
- DROP – Allow a user to drop databases and tables.
- EXECUTE – Allow a user to execute stored routines.
- GRANT OPTION – Allow a user to grant or remove another user’s privileges.
- INSERT – Allow a user to insert rows from a table.
- SELECT – Allow a user to select data from a database.
- SHOW DATABASES- Allow a user to view a list of all databases.
- UPDATE – Allow a user to update rows in a table.
GRANT CREATE ON *.* TO 'testuser'@'localhost';
Using an asterisk (*) in the place of the database or table is a completely valid option, and implies all databases or all tables.
Example #2: To grant testuser the ability to drop tables in the specific database, tutorial_database , use the DROP permission:
GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';
When finished making your permission changes, it’s good practice to reload all the privileges with the flush command!
FLUSH PRIVILEGES;
View Grants for MySQL User
After you’ve granted permissions to a MySQL user you’ll probably want to double check them. Use the following command to check the grants for testuser :
SHOW GRANTS FOR 'testuser'@'localhost';
View a List of MySQL Users
Viewing a full list of MySQL users, including the host they’re associated with, can be done with the following select statement:
SELECT User,Host FROM mysql.user;
Create database:
- CREATE DATABASE awscred
- USE awscred
- status
Create table
- CREATE TABLE credentials (DSN varchar(255), rootCA_Cert blob, rootCA_privateKey blob);
- DESCRIBE credentials;
Creating a database
o mysql> CREATE DATABASE dbname;
o example: create database lamp_class;
• Deleting a database
o mysql> DROP DATABASE dbname;
o example: drop database lamp_class;
• Changing database in use
o mysql> USE dbname;
o example: use lamp_class;
• Creating a table
o mysql> CREATE TABLE tablename (column1
COL_DEFINITION1, column2 COL_DEFINITION2, ...);
o example: create table students
(
studentID int unsigned not null
auto_increment primary key,
firstName char(30) not null,
lastName char(30) not null
);
• Deleting a table
o mysql> DROP TABLE tablename;
o example: drop table students;
Query Data:
- SELECT name, imdb_rating FROM movies;
從Table movies中query name and imdb_rating兩個columns的data
可以query多個columns。
- SELECT DISTINCT genre FROM movies;
如果結果大於一筆，只回傳一筆data。
- SELECT * FROM movies WHERE imdb_rating > 8;
可以使用 WHERE關鍵字來指定篩選條件。上例只回傳imdb_rating > 8的data。這裡的operator可以是 =, !=, >, <, >=, <=
- SELECT * FROM movies WHERE name LIKE 'Se_en';
LIKE關鍵字通常與WHERE一起使用，找出特定pattern的data。上例回傳name欄位中是'Se_en'這個pattern的data. 不分大小寫。'_' 表示任何字元，
- SELECT * FROM movies WHERE name LIKE 'a%';
- SELECT * FROM movies WHERE name LIKE '%man%';
另一個wildcard字元是 '%', 第一個例子表 a開頭的所有 data, 第二個例子是中間含有 man的data。用來matching 0個或多個自
- SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';
- SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
使用BETWEEN, AND 取出一個區間中的data。
- SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy';
- SELECT * FROM movies WHERE genre = 'comedy' OR year < 1980;
WHERE後面可以加上多個AND, OR條件式。
- SELECT * FROM movies ORDER BY imdb_rating DESC;
- SELECT * FROM movies ORDER BY imdb_rating ASC;
Sorting results by descending/ascending order using DESC/ASC keywords。
- SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3;
使用limit來限制輸出的data組數。
總結：
SELECT : is the clause you use every time you want to query information from a database.
WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
LIKE and BETWEEN are special operators that can be used in a WHERE clauseAND and OR are special operators that you can use with WHERE to filter the query on two or more conditions.
ORDER BY lets you sort the results of the query in either ascending or descending order.
LIMIT lets you specify the maximum number of rows that the query will return. This is especially important in large tables that have thousands or even millions of rows.
Aggregate Functions:
- SELECT COUNT(*) FROM fake_apps;
計算總共有幾筆data。
- SELECT COUNT(*) FROM fake_apps WHERE price = 0;
就如一般query data一樣，可用WHERE加上條件式。上例是計算免費的app的數量。
- SELECT price, COUNT(*) FROM fake_apps GROUP BY price;
利用price來分群，個別列出結果。
- SELECT price, COUNT(*) AS PRICECOUNT FROM fake_apps GROUP BY price ORDER BY PRICECOUNT DESC;
分群之後，利用count結果來排序。
- SELECT SUN(downloads) FROM fake_apps;
Summary總共多少download數量。
- SELECT category, SUN(downloads) FROM fake_apps GROUP BY category;
利用category來分群Summarize downloads的結果。
- SELECT MAX(downloads) FROM fake_apps;
- SELECT MIN(downloads) FROM fake_apps;
- SELECT AVG(downloads) FROM fake_apps;
求最大, 最小或平均downloads數量。
- SELECT ROUND(AVG(downloads), 2) FROM fake_apps;
設定只秀出小數點後面幾位數。
- SELECT name, category, MIN(downloads) FROM fake_apps GROUP BY category;
一併列出name和category，並且使用category來分群。
總結：
- Aggregate functions combine multiple rows together to form a single value of more meaningful information.
COUNT takes the name of a column(s) as an argument and counts the number of rows where the value(s) is not NULL.GROUP BY is a clause used with aggregate functions to combine data from one or more columns.SUM() takes the column name as an argument and returns the sum of all the values in that column.MAX() takes the column name as an argument and returns the largest value in that column.MIN() takes the column name as an argument and returns the smallest value in that column.AVG() takes a column name as an argument and returns the average value for that column.ROUND() takes two arguments, a column name and the number of decimal places to round the values in that column.
Reference:
1. http://www.tutorialspoint.com/sql/
2. https://www.codecademy.com/learn
na name, df im db_ratingme, imdb_rating
