數(shù)據(jù)庫的簡(jiǎn)單使用
1歹袁、進(jìn)入數(shù)據(jù)庫
方法一
[root@node01 ~]# mysql -u root -p
Enter password:
方法二
[root@node01 ~]# mysql -uroot -pWww.1.com
2、簡(jiǎn)單命令的使用
//查詢庫
mysql> show databases;
//刪除test這個(gè)庫
mysql> drop database test;
//創(chuàng)建庫
mysql> create database db;
//切換庫
mysql> use db;
//查詢表
mysql> show tables ;
//查詢表結(jié)構(gòu)
mysql> show create table new17\G
//創(chuàng)建表
mysql> create table test(id int,name char(10));
//描述表結(jié)構(gòu)
mysql> desc test;
desc 表名;
//插入數(shù)據(jù)
mysql> insert into test values(1,'xiaochen');
//查看數(shù)據(jù)
mysql> select * from test;
//刪除user表中的數(shù)據(jù)，但是不刪表
mysql> delete from user;
//刪除user1這張表
mysql> drop table user1;
//創(chuàng)建表
create table 表名(
字段名1 類型[(寬度) 約束條件],
字段名2 類型[(寬度) 約束條件],
字段名3 類型[(寬度) 約束條件]
...
)[存儲(chǔ)引擎 字符集];
//插入數(shù)據(jù)
insert into 表名(字段1,字段2...) values(字段值列表...)
//查看表詳細(xì)結(jié)構(gòu)
show create table 表名库倘；
3、修改表
1.修改表名
alter table 表名 rename 新表名；
mysql> rename table emp to newemp;
- 增加字段
1)在最后增加字段
alter table 表名 add 字段名 數(shù)據(jù)類型 [完整性約束條件…],add 字段名 數(shù)據(jù)類型 [完整性約束條件…];
mysql> alter table new17 add age int(3) not null default 22,add sex enum('M','W');
2)在首行增加字段
alter table 表名 add 字段名 數(shù)據(jù)類型 [完整性約束條件…] first;
mysql> alter table new17 add aaa int first;
3）在某字段后增加字段
alter table 表名 add 字段名 數(shù)據(jù)類型 [完整性約束條件…] after 字段名;
mysql> alter table new17 add num varchar(10) not null after id; - 修改字段
1）修改數(shù)據(jù)類型和約束
alter table 表名 modify 字段名 數(shù)據(jù)類型 [完整性約束條件…];
mysql> alter table new17 modify id smallint;
mysql> alter table new17 modify age int(10) not null primary key auto_increment;
2）修改字段名窃祝，但不更新數(shù)據(jù)類型
alter table 表名 change 舊字段名 新字段名 舊數(shù)據(jù)類型 [完整性約束條件…];
mysql> alter table new17 change id uid smallint;
3）修改字段名同時(shí)更新字段的數(shù)據(jù)類型
alter table 表名 change 舊字段名 新字段名 新數(shù)據(jù)類型 [完整性約束條件…];
mysql> alter table new17 change num nnum char(10);
4.添加復(fù)合主鍵
alter table 表名 add primary key(字段名,字段名...);
mysql> alter table service2 add primary key(host_ip,port);
5.刪除字段
alter table 表名 drop 字段名;
mysql> alter table new17 drop aaa;
6.修改存儲(chǔ)引擎
alter table 表名 舊存儲(chǔ)引擎=新存儲(chǔ)引擎
mysql> alter table new17 engine=myisam;
7.增加約束（針對(duì)已有的主鍵增加auto_increment）
mysql> alter table new17 modify uid smallint not null auto_increment;
8.增加主鍵
mysql> alter table new17 modify name varchar(10) not null primary key;
9.增加主鍵和自動(dòng)增長(zhǎng)
mysql> alter table new17 modify id int primary key auto_increment;
10.刪除主鍵
【因?yàn)橛幸蕾囮P(guān)系，所以要先刪除自增約束條件踱侣，然后再刪除主鍵】
a. 刪除自增約束
mysql> alter table new17 modify id int(11) not null;
b. 刪除主鍵
mysql> alter table new17 drop primary key;
4粪小、復(fù)制表
1）復(fù)制表結(jié)構(gòu)＋記錄 （主鍵、外鍵和索引這些key是不會(huì)復(fù)制的）
mysql> create table new select * from new17;
2）只復(fù)制表結(jié)構(gòu)
//條件為假抡句，查不到任何記錄
mysql> select * from new where 1=2;
mysql> create table new1 select * from new where 1=2;
//連同key都會(huì)復(fù)制
mysql> create table new2 like new17;
5探膊、刪除表
drop table 表名,表名,表名;
//存在就刪除，這樣就不會(huì)報(bào)錯(cuò)待榔，使用腳本的時(shí)候可以用
drop table if exists new1;
6逞壁、更新數(shù)據(jù)
update 表名 set字段1=值1,字段2=值2... where conditon;
mysql> update mysql.user set password='' where user='root';
7、刪除數(shù)據(jù)
delete from 表名 [where condition];
//清空表里的數(shù)據(jù)锐锣，但是保留表結(jié)構(gòu)
mysql> delete from new;
//處于安全考慮將表中沒有密碼的刪掉
mysql> delete from mysql.user where authentication_string='';
mysql> delete from new where id=3 and name='robin';
mysql> delete from new where id=3 or name='robin';
8腌闯、單表查詢
select col_name1, col_name2, .... from tb_name [select_statement]
1）簡(jiǎn)單查詢
mysql> select * from tutors;
mysql> select Tname,Age from tutors;
//給字段設(shè)置別名
mysql> select Tname as 教師,Age as 年齡 from tutors;
//不使用cache直接取數(shù)據(jù)
mysql> select sql_no_cache * from new1;
//清空query cache后取數(shù)據(jù)
mysql> reset query cache;
2）避免重復(fù)查詢
//去掉完全重復(fù)的行
mysql> select distinct * from tutors;
//查詢有哪些年齡
mysql> select distinct Age from tutors;
3）通過條件查詢
mysql> select * from 表名 where 條件;
4）模糊查詢
like "通配符表達(dá)式"
通配符’%’匹配多個(gè)字符
通配符’_’匹配一個(gè)字符
mysql> select * from 表名 where Tname like "Y%" or Tname like "H_____";
5）rlike正則表達(dá)式
mysql> select * from 表名 where Tname rlike "ao$";
6）按單列排序
mysql> select * from 表名 order by Age;
mysql> select * from 表名 order by Age desc;
7)按多列排序
按照年齡排序降序，年齡相同按照TID降序
mysql> select * from new1 order by age desc,TID desc;
8)限制查詢的記錄數(shù)
//忽略前n行雕憔，共顯示m行
limit [n,]m
//從第三個(gè)開始取姿骏，取三個(gè)
mysql> select * from 表名 limit 2,3;
9)sum求和、avg求平均數(shù)斤彼、max求最大值分瘦、min求最小值、count統(tǒng)計(jì)總數(shù)
mysql> select sum(salary) from emp;
mysql> select avg(salary) from emp;
mysql> select max(salary) from emp;
mysql> select min(salary) from emp;
mysql> select count() as count from emp;
10)通過四則運(yùn)算查詢 （不支持+=）
mysql> select name,salary12 from emp;
mysql> select name,salary12 as annual_salary from emp;
mysql> select name,(salary+1000)12 as annual_salary from emp where name='rose';
- 分組函數(shù)
注：group by必須在where之后琉苇，分組前過濾嘲玫；having的用法和where相同，但可以分組后過濾翁潘，一般不使用
//顯示每個(gè)部門有多少人
mysql> select count() from emp group by did;
//取出部門人數(shù)超過三個(gè)的
mysql> select count() as num from emp group by did having num >3;
12)內(nèi)連接
特征： 只有相關(guān)聯(lián)字段具有相同的值時(shí)趁冈，才顯示對(duì)應(yīng)的結(jié)果
select tb1.col, tb2.col,.... from tb1 inner join tb2 on tb1.col=tb2.col
13)外連接
左外連接
特征：以左表為主歼争，顯示左表所有數(shù)據(jù)拜马，右表中沒有關(guān)聯(lián)的數(shù)據(jù)時(shí)，顯示為NULL
select tb1.col, tb2.col,.... from tb1 left join tb2 on tb1.col=tb2.col
mysql> select students.Name, students.Age, students.Gender, courses.Cname from students left join courses on students.CID2=courses.CID;
右連接
特征：以右表為主沐绒，顯示右表所有數(shù)據(jù)俩莽，左表中沒有關(guān)聯(lián)的數(shù)據(jù)時(shí)，顯示為NULL
select tb1.col, tb2.col,.... from tb1 right join tb2 on tb1.col=tb2.col
mysql> select students.Name, students.Age, students.Gender, courses.Cname from courses right join students on students.CID2=courses.CID;
14）嵌套查詢
特征：以查詢的結(jié)果作為另外一個(gè)查詢的條件乔遮、數(shù)據(jù)源使用
mysql> select * from 表名 where Age > (select AVG(Age) from tutors);
=================================================================================================================
1扮超、MySQL數(shù)據(jù)類型
數(shù)值類型：
整數(shù)類型 tinyint smallint mediumint int bigint
浮點(diǎn)數(shù)類型 float 單精度浮點(diǎn)數(shù)值(7個(gè)有效位) double雙精度(15個(gè))
字符串類型： char varchar
時(shí)間和日期類型： date time time datetime timestamp year
枚舉類型： enum
集合類型： set
2、屬性：
unsigned 無符號(hào)
primary key 主鍵
not null 不允許為空
default "值" 設(shè)置字段的默認(rèn)值
auto_increment 自動(dòng)增長(zhǎng), 必須與主鍵、不允許為空同時(shí)使用
3出刷、約束條件 說明
unsigned 無符號(hào)
zerofill 使用0填充
not null 標(biāo)識(shí)該字段不能為空
default 為該字段設(shè)置默認(rèn)值
unique key (uk) 標(biāo)識(shí)該字段的值是唯一的,是一種索引璧疗，可以為空，一個(gè)表中可以有多個(gè)
primary key (pk) 標(biāo)識(shí)該字段為該表的主鍵馁龟，可以唯一的標(biāo)識(shí)記錄
auto_increment 標(biāo)識(shí)該字段的值自動(dòng)增長(zhǎng),只能用于主鍵崩侠，而且是整數(shù)類型
foreign key (fk) 標(biāo)識(shí)該字段為該表的外鍵，實(shí)現(xiàn)表與表之間的關(guān)聯(lián)
on delete cascade 級(jí)聯(lián)刪除 又叫同步刪除
on update cascade 級(jí)聯(lián)更新 不管是父表還是子表變了坷檩，另一個(gè)都會(huì)跟著變
4却音、通過條件查詢
where condition
1）數(shù)字操作符：
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
大于
= 大于等于
between 5 and 10 在兩個(gè)值之間
2）邏輯操作符：
and
or
and和or并存時(shí)and的優(yōu)先級(jí)高
3）包含和不包含：
in
not in
4）空值或者非空值：
is null 空值
is not null 非空值
【
使用in查詢的優(yōu)點(diǎn)：
1.in的語法更加直觀
2.in的計(jì)算次序更容易管理(操作符少)
3.in 一般比or執(zhí)行的更快
4.in的最大優(yōu)點(diǎn)可以包含其他子句 or不行
】
5）模糊查詢
1、LIKE "通配符表達(dá)式"
通配符’%’匹配多個(gè)字符
通配符’_’匹配一個(gè)字符
【
使用通配符的原則:
盡量少使用通配符,如果其他操作符能做到就不要使用通配符
至于使用位置,使用錯(cuò)了得不到想要的結(jié)果
】
================================================================================================================
mysql 安全機(jī)制
1.登錄MySQL
/usr/local/mysql/bin/mysql -h localhost -P3306 -uroot -S /tmp/mysql.sock -p123 -e 'select user,host from mysql.user'
/usr/local/mysql/bin/mysql -h localhost -P3306 -uroot -p123 mysql -e 'select user,host from user'
/usr/local/mysql/bin/mysql -h localhost -P 3306 -u root -p -e 'select * from mysql.user'
注釋：
-h 指定主機(jī)名
-P MySQL服務(wù)器端口
-u 指定用戶名
-S 指定套接字文件的存放位置
-p 指定登錄密碼
-e 接SQL語句
- 創(chuàng)建用戶并為用戶設(shè)置密碼
//查看mysql數(shù)據(jù)庫中自帶的用戶
mysql> select user,host from mysql.user;
//創(chuàng)建用戶
mysql> create user robin;
//查詢用戶
mysql> select user,host,password from mysql.user; 【%表示任意主機(jī)】
//創(chuàng)建用戶時(shí)設(shè)置密碼
mysql> create user alice@'%' identified by '123';
//修改用戶名 [了解]
//默認(rèn)修改的是其他所有主機(jī)的
mysql> rename user zorro to jack;
mysql> rename user zorro@'localhost' to shrek@'%';
mysql> select user,host from mysql.user;
//刪除用戶
//默認(rèn)刪除的是針對(duì)于其他所有用戶的
mysql> drop user aaa;
mysql> drop user shrek@'localhost'; //不是默認(rèn)的需要指明
或
mysql> delete from mysql.user where user='shrek' and host='%';
//修改用戶密碼 [了解]
======普通用戶修改自己的密碼
set password for 'zorro'@'%'= password('123'); //password()函數(shù)加密
set password = password('123'); //修改當(dāng)前普通用戶自己的密碼
======root用戶修改自己的密碼
方法一：
mysqladmin -uroot -pWww.1.com password 'new_password'
方法二：不建議使用矢炼，root密碼丟失時(shí)使用
mysql> select password('123'); //使用函數(shù)獲得加密的值系瓢，更新到指定的字段上
mysql> update mysql.user set authentication_string=password('Mysql.456') where user='root';
mysql> flush privileges;
方法三：
mysql> set password= password('Www.2.com');
=====root用戶修改其他用戶的密碼
方法一:
set password for 'alice'@'localhost'=password('new_password');
方法二：
update mysql.user set authentication_string=password('456') where user='robin' and host='%';
方法三：
grant select on . to robin@'%' identified by '123';
MySQL權(quán)限管理
1.授權(quán)
grant 權(quán)限列表 on 庫名.表名 to 用戶名@'客戶端主機(jī)' [identified by '密碼' with option參數(shù)];
2.==權(quán)限列表 all 所有權(quán)限（不包括授權(quán)權(quán)限）
注釋： 所謂授權(quán)權(quán)限就是當(dāng)前主機(jī)能不能把自己已有的權(quán)限再授權(quán)給其他的帳號(hào)
==數(shù)據(jù)庫.表名 . 所有庫下的所有表 全局
web.* web庫下的所有表 庫級(jí)
web.stu_info web庫下的stu_info表 表級(jí)
SELECT (col1), INSERT (col1,col2) ON mydb.mytbl 列級(jí)
==客戶端主機(jī) % 所有主機(jī)(盡量不要用%授權(quán)不安全)
172.16.2.% 192.168.2.0網(wǎng)段的所有主機(jī)
172.16.2.168 指定主機(jī)
3.取消權(quán)限
revoke 權(quán)限 on 數(shù)據(jù)庫.表 from '用戶'@'IP地址' -- 取消權(quán)限
4.用戶權(quán)限的不同范圍在mysql庫中保存的表不同
1)mysql.user表中存儲(chǔ)了所有用戶的信息
select * from mysql.user\G
2)mysql.db表中保存了用戶對(duì)表的權(quán)限
select * from mysql.db\G
3)mysql.tables_priv表中保存了用戶對(duì)表的權(quán)限
select * from mysql.tables_priv\G
4)mysql.columns_priv表中保存了用戶對(duì)列的權(quán)限
select * from mysql.columns_priv\G
4.關(guān)于權(quán)限
all privileges 除grant外的所有權(quán)限
select 僅查權(quán)限
select,insert 查和插入權(quán)限
...
usage 無訪問權(quán)限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user句灌、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存儲(chǔ)過程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(關(guān)閉MySQL)
super ????使用change master夷陋、kill、logs胰锌、purge肌稻、master和set global。還允許mysqladmin????????調(diào)試登陸
replication client 服務(wù)器位置的訪問
replication slave 由復(fù)制從屬使用
5.關(guān)于數(shù)據(jù)庫和表
對(duì)于目標(biāo)數(shù)據(jù)庫以及內(nèi)部其他：
數(shù)據(jù)庫名.* 數(shù)據(jù)庫中的所有
數(shù)據(jù)庫名.表 指定數(shù)據(jù)庫中的某張表
數(shù)據(jù)庫名.存儲(chǔ)過程 指定數(shù)據(jù)庫中的存儲(chǔ)過程
. 所有數(shù)據(jù)庫
6.關(guān)于用戶和 IP
用戶名@IP地址 用戶只能在改IP下才能訪問
用戶名@192.168.1.% 用戶只能在改IP段下才能訪問(通配符%表示任意)
用戶名@%.shark.com
用戶名@% 用戶可以再任意IP下訪問(默認(rèn)IP地址為%)
7.刷新
flush privileges
8.創(chuàng)建用戶的同時(shí)直接授權(quán)
grant select on . /設(shè)置查詢數(shù)據(jù)的權(quán)限在所有的庫和表/
to 'shark_2'@"%" /指定用戶名和來源 ip/
identified by '123'; /設(shè)置密碼/
