MySQL 指令大全,全方位收集區
MySQL收集區：快速查找指令：常用指令收集區：
//登入
mysql -u root -h hostname -p
// show 出資料庫
show databases;
//使用資料庫
use 資料庫名稱;
//檢查使用中的資料庫
select database();
//秀出資料庫
shell指令mysqlshow
mysql下 show databases;
//顥示連結的狀態
status
//秀出資料庫使用狀況
select database();
//秀出資料庫內的資料表名稱
show tables;
//秀出資料庫欄位名稱及內容
describe 資料表名稱;
//加入新的資料至欄位
INSERT INTO manber( 欄位, 欄位, )
VALUES ( '插入字串' , '插入字串' );
//加入欄位至資料庫尾端
ALTER TABLE `TABLE_NAME` ADD `新增欄位名稱` VARCHAR( 5 ) ;
//show 出 資料表內所有的內容
select * from 資料表名稱;
// show 出資料表後匯入一個新的文件
mysql -u root -hhostname -p -e "select * from cd limit 1" > 匯出的名稱
// 搜尋某筆的資料
select * from table名稱 where type_id='24';
//修改芋筆的資料
update table名稱 set 欄位1='5' where 欄位2='24' limit 5;
//記算資料庫筆數
select count(櫚位名) from table_name ;
或
select count(*) from table_name ;
timestamp下
select 櫚位名1,count(*) from table_name where Time < timename_timestamp('2003-5-8 12:0:0') group by 欄位名1 ;
//亂數select
select * from twble_name order by rand() ;
//新增資料
insert into 資料庫名稱(欄位1,欄位2) values(資料1,資料2);
//刪除資料
delete from 資料庫名稱 ; //不用加星號
==================================================================
php 中的 mysql 函式庫
mysql_affected_rows --- 取得先前操作MySQL所受到影響的列的數目
mysql_change_user --- 更改連線的使用者
mysql_close --- 關閉MySQL連線
mysql_connect --- 開啟MySQL伺服器連線
mysql_create_db --- 新增一個MySQL資料庫
mysql_data_seek --- 移動內部指標
mysql_db_query --- 送出MySQL查詢
mysql_drop_db --- 刪除MySQL資料庫
mysql_errno --- 從先前MySQL操作傳回錯誤訊息代號
mysql_error --- 從先前MySQL操作傳回錯誤訊息
mysql_fetch_array --- 取得查詢後的陣列結果
mysql_fetch_field --- 取得欄位資訊
mysql_fetch_lengths --- 取得各欄位最大長度
mysql_fetch_object --- 取得查詢後的物件結果
mysql_fetch_row --- 取得單列結果
mysql_field_flags --- 取得指定欄位的旗標
mysql_field_name --- 取得指定欄位的名稱
mysql_field_len --- 取得指定欄位的長度
mysql_field_seek --- 設定結果指標到指定的欄位偏移量
mysql_field_table --- 取得指定欄位所在的表格名稱
mysql_field_type --- 取得指定欄位的型態
mysql_free_result --- 釋放記憶體
mysql_insert_id --- 取得先前INSERT操作的id
mysql_list_dbs --- 列出MySQL伺服器上可用的資料庫
mysql_list_fields --- 列出欄位
mysql_list_tables --- 列出資料庫中的表格
mysql_num_fields --- 取得欄位的數目
mysql_num_rows --- 取得結果中列的數目
mysql_pconnect --- 開啟MySQL伺服器持續連線
mysql_query --- 送出MySQL查詢
mysql_result --- 取得結果資料
mysql_select_db --- 選擇一個資料庫
mysql_tablename --- 取得表格名稱
======================================================
//新增一個自動新增id 的資料庫
create table dkbbs_type(
type_id int not null auto_increment,
name char(1),
type_name varchar(16),
primary key(type_id)
);
權限管理 :
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES; //重啟資料庫權限
或是
mysqladmin -u root password 密碼字串
官方說明 :
4.3.4 Setting Up the Initial MySQL Privileges
After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. See section 2.3.1 Quick Installation Overview. The mysql_install_db script starts up the mysqld server, then initialises the grant tables to contain the following set of privileges:
The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. Note: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges.
An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user.
Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist.
Note: the default privileges are different for Windows. See section 2.6.2.3 Running MySQL on Windows.
Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
If you know what you are doing, you can also directly manipulate the privilege tables:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').
Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.
Once the root password has been set, thereafter you must supply that password when you connect to the server as root.
You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.
See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.
If you want the initial privileges to be different from those just described above, you can modify mysql_install_db before you run it.
To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.
Note: for MySQL versions older than Version 3.22.10, you should not delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.
