SQL_study_notes5：other_important_notes_about_SQL

1 the union statement
KEY POINT:
Firstly, union can only take one order by at the end of the statement.
Then, the num of columns in each select statement must match. you can not select two columns from the first statement and one from the next.
In addition, if for some reason I want to see duplicates, I can use the operator union all, it return every match, not just the distinct ones.

select title from job_current
union
select title from job_desired
union
select title from job_listings
order by title;
### OR union all
select title from job_current
union all
select title from job_desired
union all
select title from job_listings
order by title;
### OR
create table my_union as
select title from job_current
union
select title from job_desired
union
select title from job_listings
order by title;

2 the intersect and except statement

### intersect returns only those columns that are in the first query and also in the second query.
select title from job_current
intersect
select title from job_desired;
### except returns only those columns that are in the first query but not in the second query.
select title from job_current
except
select title from job_desired;

3 the check constraint statement

### a check constraint restricts what values you can insert into a column. It uses the same conditionals as a where clause.
create table piggy_bank
(
id int auto_increment not null primary key,
coin char(1) check(coin in ('P', 'N','D', 'Q')
);
### if the value you are trying to insert fails the check condition, you get an error.
### But you can create your tables with check constrains in my SQL, but it won't do anything for you. 
### MySQL ignores them, which is that CHECK does not enforce data integrity in MySQL.
alter table my_contacts
add constraint check gender in ('M', 'F');
create table my_table
(
column1 int check( column1 > 10 and column1 < 100 ),
column2 char(1) check(column2 not in ('X', 'Y', 'Z')),
column3 varchar(3) check('A' = substring(column3, 1, 1)),
column4 varchar(3) check('A' = substring(column4, 1, 1) and '9' = substring(column4, 2, 1))
)

4 the view statement

create view web_designers as
select
mc.first_name,
mc.last_name,
mc.phone,
mc.email
from my_contacts mc
natural join
job_desire jd
where jd.title = 'Web Designer';
### when you actually use your view in a query. it is behaving as though it were a subquery. here is what the select we just used with our view is actually telling SQL to do.
select * from web_designers;
### which is the same as the following statement.
select * 
from
(
create view web_designers as
select
mc.first_name,
mc.last_name,
mc.phone,
mc.email
from my_contacts mc
natural join
job_desire jd
where jd.title = 'Web Designer'
)
as web_designers;
###??? Check option
drop view pb_dimes;

5 the classic ACID test
To help me decide what steps in my SQL can be considered a transaction, remember the acronym ACID. There are four characteristics that have to be true before we can call a set of SQL statements a transaction:
A: atomicity, C: consistency, I: isolation, D: durability.

6 some notes about the password & username

set password for 'root' @ 'localhost' = password('bbbbbbb');
### 'root': the username of our root user is simply 'root'
### 'localhost': this is where the SQL software is installed and running
### 'bbbbbbb': this is the passward we chose for our root user
### ADD a new user
create user newuser_name identified by 'aaaaaaa';
### Remarks: User creation varies RDBMS to RDBMS. You need to check your documentation to find the correct way to create a user in your RDBMS
### I can use GRANT statement to control exactly what users can do to tables and columns.
grant select on activities to newuser_name;
grant select on location to newuser_name;
grant select on info to newuser_name;
### once the privileges need to be removed from someone, we can use REVOKE statement.
revoke select on clown_info from newuser_name;
revoke grant option on delete on chores from username1, username2;
revoke delete on chores from username cascade;
### cascade means the revoke will affect anyone down the chains, as well as the original target.
revoke delete on chores from username restrict;
### if someone will be also affected, using restrict statement will return error.
grant select on clown_info to username identified by 'aaaaaaa';

7 some notes about creating a role

### I can create the groups the privileges the user need, while at the same time giving each user an individual account.
### attenetion: there might be no roles in MySQL
create role group_name;
grant select, insert, alter on table_name to group_name;
grant group_name to username;
grant group_name to username1 with admin option; 
### it allows username1 to grant the role of group_name to others
revoke group_name from username cascade;
revoke group_name from username restrict;
drop role group_name;