mysql指令收集^<--快速查找指令
常用指令收集^//登入
mysql -u root -h hostname -p
// show 出Y料
show databases;
//使用Y料
use Y料烀Q;
//z查使用中的Y料
select database();
//秀出Y料
shell指令mysqlshow
mysql下 show databases;
//示BY的B
status
//秀出Y料焓褂r
select database();
//秀出Y料鹊馁Y料表名Q
show tables;
//秀出Y料谖幻Q及热
describe Y料表名Q;
//加入新的Y料至谖
INSERT INTO manber( 谖, 谖, )
VALUES ( '插入字串' , '插入字串' );
//加入谖恢临Y料煳捕
ALTER TABLE `TABLE_NAME` ADD `新增谖幻Q` VARCHAR( 5 ) ;
//show 出 Y料表人有的热
select * from Y料表名Q;
// show 出Y料表後R入一新的文件
mysql -u root -hhostname -p -e "select * from cd limit 1" > R出的名Q
// 搜つ彻P的Y料
select * from table名Q where type_id='24';
//修改芋P的Y料
update table名Q set 谖1='5' where 谖2='24' limit 5;
//算Y料旃P
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 ;
//yselect
select * from twble_name order by rand() ;
//新增Y料
insert into Y料烀Q(谖1,谖2) values(Y料1,Y料2);
//h除Y料
delete from Y料烀Q ; //不用加星
MA指令用收集^
csv 格式 笕
load data infile 'n案路' into table test fields terminated by ',';
在 windows 下麻 要去判e一下 Q行字元~~不然o法R入csv
--------------------------------------------------------------------------------
shell 下绦 mysql
mysql -u root -h hostname -p -e"MYSQL下的指令"
php 中的 mysql 函式
mysql_affected_rows --- 取得先前操作MySQL所受到影的列的的
mysql_change_user --- 更改B的使用者
mysql_close --- P]MySQLB
mysql_connect --- _MySQL伺服器B
mysql_create_db --- 新增一MySQLY料
mysql_data_seek --- 移炔恐
mysql_db_query --- 送出MySQL查
mysql_drop_db --- h除MySQLY料
mysql_errno --- 南惹MySQL操作骰劐e`息代
mysql_error --- 南惹MySQL操作骰劐e`息
mysql_fetch_array --- 取得查後的列Y果
mysql_fetch_field --- 取得谖毁Y
mysql_fetch_lengths --- 取得各谖蛔畲箝L度
mysql_fetch_object --- 取得查後的物件Y果
mysql_fetch_row --- 取得瘟薪Y果
mysql_field_flags --- 取得指定谖坏钠
mysql_field_name --- 取得指定谖坏拿Q
mysql_field_len --- 取得指定谖坏拈L度
mysql_field_seek --- O定Y果指说街付ǖ谖黄移量
mysql_field_table --- 取得指定谖凰在的表格名Q
mysql_field_type --- 取得指定谖坏男B
mysql_free_result --- 放w
mysql_insert_id --- 取得先前INSERT操作的id
mysql_list_dbs --- 列出MySQL伺服器上可用的Y料
mysql_list_fields --- 列出谖
mysql_list_tables --- 列出Y料熘械谋砀
mysql_num_fields --- 取得谖坏的
mysql_num_rows --- 取得Y果中列的的
mysql_pconnect --- _MySQL伺服器持mB
mysql_query --- 送出MySQL查
mysql_result --- 取得Y果Y料
mysql_select_db --- x褚Y料
mysql_tablename --- 取得表格名Q
例 :
[url]http://banic.e104.org/mysql/[/url]
//新增一自有略id 的Y料
create table twbb_type(
type_id int not null auto_increment,
name char(1),
type_name varchar(16),
primary key(type_id)
);
嘞薰芾 :
a:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES; //重①Y料嘞
或是
a:
mysqladmin -u root password 密a字串
官方f明 :
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):
a:
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:
a:
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:
a:
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.
页:
[1]