一、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 root@127.0.0.1 |
+---------------------------------------------------------------------+
| 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 root@127.0.0.1 |
+------------------------------------------+
| grant usage on *.* to 'root'@'127.0.0.1' |
+------------------------------------------+
1.8、shell中-n不为空返回真，使用-n判空，必须考虑把变量用包含