2.1登入到mysql控制臺(tái)[用戶名為root,密碼缺省為空]
切換到mysql的bin目錄cd d:/wamp/mysql/bin
登入到mysql控制臺(tái)
語(yǔ)法:mysql -h hostname -u username -p
連接到另一臺(tái)主機(jī)
代碼:mysql -h 192.168.1.1 -u root -p
password:直接按回車
連接到本機(jī)
代碼:mysql -h 127.0.0.1 -u root -p
password:直接按回車
出現(xiàn)mysql>表示登陸到mysql控制臺(tái)成功
2.2退出mysql控制臺(tái)
mysql>exit;
/***********************************************************/
3.1檢查所有mysql數(shù)據(jù)庫(kù)清單;
語(yǔ)法:mysql> show databases;
代碼:mysql> show databases;

3.2顯示數(shù)據(jù)庫(kù)中所有表的清單
查看當(dāng)前數(shù)據(jù)庫(kù)中的表
語(yǔ)法1:mysql> show tables;
代碼1:mysql> show tables;

3.3查看其它數(shù)據(jù)庫(kù)jxc中的表
語(yǔ)法1:mysql> show tables from databasename;
代碼1:mysql> show tables from jxc;
/***********************************************************/
4.創(chuàng)建/刪除/選擇數(shù)據(jù)庫(kù)
創(chuàng)建jxc數(shù)據(jù)庫(kù):
語(yǔ)法:mysql> create database databasename;
代碼:mysql> create database jxc;
刪除jxc數(shù)據(jù)庫(kù):
語(yǔ)法:mysql> drop database databasename;
代碼:mysql> create database abc;
代碼:mysql> drop database abc;
選擇jxc數(shù)據(jù)庫(kù):
語(yǔ)法:mysql> use database;
代碼:mysql> use jxc;
/***********************************************************/

5查看一個(gè)表的數(shù)據(jù)結(jié)構(gòu)
5.1 describte查看表customers結(jié)構(gòu)
語(yǔ)法1:mysql> describe tablename;
代碼1:mysql> describe customers;
5.2.show columns查看表customers;結(jié)構(gòu)
語(yǔ)法1:mysql> show columns from tablename;
代碼1:mysql> show columns from customers;

5.3.查看一個(gè)表的指定列名的數(shù)據(jù)結(jié)構(gòu)
語(yǔ)法1:mysql> show index from tablename column;
代碼1:mysql> show index from customers name;

5.4.查看一個(gè)表customers的索引
語(yǔ)法1:mysql> show index from tablename;
代碼1:mysql> show index from customers;

6.數(shù)據(jù)常用操作(select,insert,update,delete)
6.1 select選擇：
語(yǔ)法:select * from [表名1,表名1,,,] where [條件范圍]
代碼:select * from orders where orderid>100;

6.2 insert插入
語(yǔ)法:insert into table1(column1,column,,,) values(value1,value2,,,);
代碼:insert into books(isbn,author,title,price) values('iso-902126','jahn.D','mysql6.0',99.0);

6.3 update 更新：
語(yǔ)法:update table1 set [列名]=[新數(shù)據(jù)] where [條件范圍]
代碼:update books set title="Thinking in Java" where isbn='iso-902126';

6.4刪除：
語(yǔ)法:delete from [表名] where [條件范圍]
代碼:delete from books where isbn='iso-902126';

6.5其它方法
查找：select * from table1 where field1 like '%value1%' ---like的語(yǔ)法很精妙
排序：select * from table1 order by field1,field2 [desc]
總數(shù)：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最?。簊elect min(field1) as minvalue from table1

/***********************************************************/

7.用grant創(chuàng)建數(shù)據(jù)庫(kù)的用戶和權(quán)限
GRANT命令語(yǔ)法:
GRANT [許可權(quán)列表1],[許可權(quán)列表2]
ON [數(shù)據(jù)庫(kù).表名]
TO [用戶名@主機(jī)名]
IDENTIFIED BY '密碼';

代碼實(shí)現(xiàn)1:
grant select,insert,delete,update
on discuz.* to jake@localhost
identified by '201314';
功能說明
把在數(shù)據(jù)庫(kù)discuz所有表的select,insert,delete,update這4個(gè)權(quán)限
添加到新用戶jake,密碼為'201314';

代碼實(shí)現(xiàn)2:
grant all
on discuz.* to tom@localhost
identified by '123456';
把在數(shù)據(jù)庫(kù)discuz全部權(quán)限所有表添加到新用戶tom,密碼為'123456';

[許可權(quán)列表1]選項(xiàng)如下
select 表，列
insert 表，列
udpate 表，列
delete 表
index 表
alter 表
create 數(shù)據(jù)庫(kù)，表
drop 數(shù)據(jù)庫(kù)，表


[許可權(quán)列表2]選項(xiàng)如下
create temporary tables 允許使用temporary關(guān)鍵字
file 允許數(shù)據(jù)庫(kù)導(dǎo)入和導(dǎo)出到文件
lock tables 允許使用lock talbes命令
reload 允許重新載入授權(quán)表
show databases 允許查看所有的數(shù)據(jù)庫(kù)清單
shutdown 允許使用關(guān)閉MYSQL

all 上面所以權(quán)限
usage 允許只登錄，但不允許任何操作

[數(shù)據(jù)庫(kù).表名]選項(xiàng)如下
數(shù)據(jù)庫(kù).表名 選擇數(shù)據(jù)庫(kù)中一個(gè)表給XX用戶
數(shù)據(jù)庫(kù).* 選擇數(shù)據(jù)庫(kù)中所有表給XX用戶

/***********************************************************/
8.revoke取消用戶和用戶權(quán)限
revoke格式:
revoke [許可權(quán)列表1],[許可權(quán)列表2] privileges,[columns]
ON [數(shù)據(jù)庫(kù).表名]
FROM [用戶名@主機(jī)名]

代碼:
先授權(quán)給laoliu(老劉)
grant all
on books.*
to laoliu
identified by 'laoliu11';

撒去一部分權(quán)限
revoke alter,create,drop
on books.*
from laoliu;
撒去laoliu的所有權(quán)限
revoke all
on books.*
from laoliu;

/***********************************************************/
9.添加MYSQL用戶其它方法
shell> mysql -u root -p1234 mysql
mysql> insert into user(Host,User,Password) values ('localhost','backup','databse');
添加一個(gè)來(lái)自本機(jī)的mysql 用戶backup,口令為：1234

shell>mysql –u root –p
mysql>GRANT FILE ON *.* TO backup@192.168.1.200 IDENTIFIED BY '1234';
mysql>/exit
開放一個(gè)賬號(hào)backup密碼1234給來(lái)自IP:192.168.1.200有檔案處理的權(quán)限


/***********************************************************/
10.創(chuàng)建表/修改表/刪除表//優(yōu)化表

10.1創(chuàng)建表
語(yǔ)法:create table tablename(columns,...)
代碼:
create table order_items
(orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid,isbn)
);

10.2修改表
10.2.1添加/刪除一列
語(yǔ)法:alter table [表名] add column [列名] [類型];
在表中添加一列remark
代碼:alter table order_items add column remark char(50);
刪除一列
語(yǔ)法:alter table [表名] drop column [列名];
從表中刪除一列remark
alter table order_items drop column remark;

10.2.2添加/刪除主鍵
添加orderid,isbn為主鍵
語(yǔ)法:alter table [表名] add primary key [列名1,列名1];
代碼:alter table order_items add primary key (orderid,isbn);
刪除主鍵
語(yǔ)法:alter table [表名] drop primary key
代碼:Alter table tabname drop primary key

10.2.3建立/刪除索引
建立索引
語(yǔ)法:create index [索引名] on [表名] (列名);
代碼:create index orderid_ix on orders (orderid);
刪除索引
語(yǔ)法:drop index [索引名] on [表名] (列名);
代碼:drop index orderid_ix on orders;

10.3刪除表:
刪除表
語(yǔ)法:drop table [表名]
代碼:drop table orders;

10.4優(yōu)化表:
當(dāng)一個(gè)表的數(shù)據(jù)有上萬(wàn)行，訪問速度變慢，就必須為它們進(jìn)行優(yōu)化
通常的方式是做出一個(gè)optmize.sql文件，
直接導(dǎo)入這個(gè)優(yōu)化腳本文件進(jìn)行批量?jī)?yōu)化一些關(guān)鍵的表，以提高訪問速度

優(yōu)化表customers數(shù)據(jù)(customers)
語(yǔ)法:mysql>optmize table tablename;
代碼:mysql>optmize table customers;

10.5 用命令行加載一個(gè)new_tb.sql文件
從而讓MYSQL一次批量執(zhí)行上*SQL文件中的在量的sql語(yǔ)句
1.文本文件做好sql命令集合，然后復(fù)制到命令行一個(gè)個(gè)的執(zhí)行
2.如果表太多的話，直接存為*.sql文件，然后用命令裝入文件

格式: mysql -h [主機(jī)IP] -u [用戶名] -D [數(shù)據(jù)庫(kù)名] -p < [此目錄中的*.sql文件名]
運(yùn)行cmd
cd d:/wamp/mysql/bin
mysql -h 127.0.0.1 -u root -D pubs -p <new_tb.sql;
上面就是用mysql把d:/wamp/mysql/bin/new_tb.sql文件加載到books數(shù)據(jù)庫(kù),
注意：數(shù)據(jù)庫(kù)pubs必須存在和-D要大寫

new_tb.sql文件內(nèi)容(此文件的SQL命令是可以存1000條以上的)
create table customers
(customerid int unsigned not null auto_increment primary key,
name char(50) not null,
address char(100) not null,
city char(30) not null
);

create table orders
(orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2),
date date not null
);

create table books
(isbn char(13) not null primary key,
author char(50),
title char(100),
price float(6,2)
);

create table order_items
(orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid,isbn)
);

create table book_reviews
(isbn char(13) not null primary key,
review text
);

執(zhí)行OK后，再進(jìn)行檢查表是否自動(dòng)建立OK?
c:>mysql -h 127.0.0.1 -u root -p
mysql>show tables from pubs;
結(jié)果顯示以上5個(gè)表都已經(jīng)建立OK;
/***********************************************************/

11.表的視圖建立與刪除
建立視圖
語(yǔ)法:create iview [視圖名] as [select 語(yǔ)句);
代碼:create view v_orders as select * from orders;
刪除視圖
語(yǔ)法:drop iview [視圖名]
代碼:create view v_orders
/***********************************************************/

最新評(píng)論