3.2顯示數據庫中所有表的清單 查看當前數據庫中的表 語法1:mysql> show tables; 代碼1:mysql> show tables;
3.3查看其它數據庫jxc中的表 語法1:mysql> show tables from databasename; 代碼1:mysql> show tables from jxc; /***********************************************************/ 4.創建/刪除/選擇數據庫 創建jxc數據庫: 語法:mysql> create database databasename; 代碼:mysql> create database jxc; 刪除jxc數據庫: 語法:mysql> drop database databasename; 代碼:mysql> create database abc; 代碼:mysql> drop database abc; 選擇jxc數據庫: 語法:mysql> use database; 代碼:mysql> use jxc; /***********************************************************/
5查看一個表的數據結構 5.1 describte查看表customers結構 語法1:mysql> describe tablename; 代碼1:mysql> describe customers; 5.2.show columns查看表customers;結構 語法1:mysql> show columns from tablename; 代碼1:mysql> show columns from customers;
5.3.查看一個表的指定列名的數據結構 語法1:mysql> show index from tablename column; 代碼1:mysql> show index from customers name;
5.4.查看一個表customers的索引 語法1:mysql> show index from tablename; 代碼1:mysql> show index from customers;
6.數據常用操作(select,insert,update,delete) 6.1 select選擇： 語法:select * from [表名1,表名1,,,] where [條件范圍] 代碼:select * from orders where orderid>100;
6.2 insert插入 語法: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 更新： 語法:update table1 set [列名]=[新數據] where [條件范圍] 代碼:update books set title="Thinking in Java" where isbn='iso-902126';
6.4刪除： 語法:delete from [表名] where [條件范圍] 代碼:delete from books where isbn='iso-902126';
6.5其它方法 查找：select * from table1 where field1 like '%value1%' ---like的語法很精妙 排序：select * from table1 order by field1,field2 [desc] 總數：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 最小：select min(field1) as minvalue from table1
7.用grant創建數據庫的用戶和權限 GRANT命令語法: GRANT [許可權列表1],[許可權列表2] ON [數據庫.表名] TO [用戶名@主機名] IDENTIFIED BY '密碼';
代碼實現1: grant select,insert,delete,update on discuz.* to jake@localhost identified by '201314'; 功能說明 把在數據庫discuz所有表的select,insert,delete,update這4個權限 添加到新用戶jake,密碼為'201314';
代碼實現2: grant all on discuz.* to tom@localhost identified by '123456'; 把在數據庫discuz全部權限所有表添加到新用戶tom,密碼為'123456';
[許可權列表1]選項如下 select 表，列 insert 表，列 udpate 表，列 delete 表 index 表 alter 表 create 數據庫，表 drop 數據庫，表
/***********************************************************/ 8.revoke取消用戶和用戶權限 revoke格式: revoke [許可權列表1],[許可權列表2] privileges,[columns] ON [數據庫.表名] FROM [用戶名@主機名]
代碼: 先授權給laoliu(老劉) grant all on books.* to laoliu identified by 'laoliu11';
撒去一部分權限 revoke alter,create,drop on books.* from laoliu; 撒去laoliu的所有權限 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'); 添加一個來自本機的mysql 用戶backup,口令為：1234
