一、mysql sql處理業務類
1.1、通過生日計算周歲
select date_format(from_days(to_days(now())-to_days('1788-11-26')),'%Y')+0;
1.2、mysqladmin命令修改密碼，-h指定數據庫服務器的ip
# /usr/mysql/bin/mysqladmin -h 192.168.0.% -uyangsq -p password
Enter password:
/usr/mysql/bin/mysqladmin: connect to server at '192.168.0.%' failed
error: 'Unknown MySQL server host '192.168.0.%' (2)'
Check that mysqld is running on 192.168.0.% and that the port is 3306.
You can check this by doing 'telnet 192.168.0.% 3306'
# /usr/mysql/bin/mysqladmin -h 192.168.0.3 -uyangsq -p password
Enter password:
New password:
Confirm new password:
1.3、關閉多個服務器，必須連接到指定的端口號
# /usr/mysql/bin/mysqladmin --port 3306 shutdown
1.4、在表的某個字段後邊添加新的字段名
alter table stuscore add column classid int not null after course;
1.5、多表關聯刪除多表
delete t1,t2 from class t1 inner join stuscore t2 on t1.classid=t2.classid and t1.classid in(1,2);
1.6、服務器維護許多提供操作相關信息的狀態變量。用FLUSH STATUS語句可以將許多狀態變量重設為0。
show status like '%thread%';
Threads_connected：當前打開的連接的數量。
Threads_created：創建用來處理連接的線程數。如果Threads_created較大，你可能要增加thread_cache_size值。緩存訪問率的計算方法Threads_created/Connections。
Threads_running：激活的（非睡眠狀態）線程數。
1.7、usage權限，可以創建賬戶而不授予任何權限，只能夠登錄，除了內置的test庫，對其他庫沒有任何權限，show databases只能列出information_schema/test。
usage權限不能被回收，也即REVOKE用戶並不能刪除用戶。
mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
mysql> revoke all privileges,grant option from root@'127.0.0.1';
mysql> show grants for root@'127.0.0.1';
+------------------------------------------+
| Grants for [email protected] |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' |
+------------------------------------------+
1.8、shell中-n不為空返回真，使用-n判空，必須考慮把變量用""包含
