# 創(chuàng)建部門表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('開發(fā)部'),('市場(chǎng)部'),('財(cái)務(wù)部'); 
# 創(chuàng)建員工表
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性別
 salary double, -- 工資
 join_date date, -- 入職日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外鍵，關(guān)聯(lián)部門表(部門表的主鍵)
) 
insert into emp(name,gender,salary,join_date,dept_id) values('孫悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('豬八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);

--所有的情況羅列出來,員工表4*部門表3=12列
select * from emp,dept;
--滿足條件的情況,只有四條
select * from emp,dept where emp.`dept_id` = dept.`id`;
--只展示員工名字和部門名
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

select id from dept where name='開發(fā)部' ;
select * from emp where dept_id = 1;

select name from dept where id =(select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;
--d是dept表的別名
--e是select * from emp where join_date >='2011-1-1'的結(jié)果表別名

-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加數(shù)據(jù)
INSERT INTO account (NAME, balance) VALUES ('張三', 1000), ('李四', 1000);
-- 張三賬號(hào)-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號(hào)+500
update account set balance = balance + 500 where name='李四';

--開啟事務(wù)
start transaction;
-- 張三賬號(hào)-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號(hào)+500
update account set balance = balance + 500 where name='李四';
rollback;

--查看mysql是否開啟自動(dòng)提交事務(wù),1:開啟,0:未開啟
select @@autocommit;
--設(shè)置為不自動(dòng)提交事務(wù)
set @@autocommit=1

--開啟事務(wù)
start transaction;
savepoint 回滾點(diǎn)名字a;
savepoint 回滾點(diǎn)名字b;
rollback to 回滾點(diǎn)名字a;

--查看隔離級(jí)別
select @@tx_isolation;
--設(shè)置隔離級(jí)別
set global transaction isolation level 級(jí)別字符串;
--示例設(shè)置隔離級(jí)別為read committed
set global transaction isolation level read committed;

CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';

create user 'user1'@'%' identified by '123';

--權(quán)限有: CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的權(quán)限則使用 ALL
GRANT 權(quán)限 1, 權(quán)限 2... ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'主機(jī)名';
--數(shù)據(jù)庫名.表名:該用戶可以操作哪個(gè)數(shù)據(jù)庫的哪些表。如果要授予該用戶對(duì)所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用*表示，如*.*
--示例:
grant CREATE,ALTER,SELECT,INSERT,UPDATE on db2.account to 'user1'@'%';
--查看用戶權(quán)限
SHOW GRANTS FOR '用戶名'@'主機(jī)名';
--撤銷用戶授權(quán)
revoke all on 數(shù)據(jù)庫名.表名 from '用戶名'@'主機(jī)名';
--刪除用戶
DROP USER '用戶名'@'主機(jī)名';
--修改管理員登錄密碼(未登錄情況)
mysqladmin -uroot -p password 新密碼
--修改普通用戶密碼
set password for '用戶名'@'主機(jī)名' = password('新密碼');