++创建/删除 数据库或表 $ mysqladmin -u root -p create xxx mysql> create database 数据库名; mysql> create TABLE items ( id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY , symbol CHAR(4) NOT NULL, username CHAR(8), INDEX sym (symbol),INDEX ….. UNIQUE(username) ) type=innodb; mysql> drop database [if exists] 数据库名 mysql> create table 表名; mysql> drop table 表名;
++查看数据库和查看数据库下的表 mysql> show databases; mysql> show tables; mysql> show table status; mysql> desc 表名; #查看具体表结构信息 mysql> SHOW CREATE DATABASE db_name #显示创建db_name库的语句 mysql> SHOW CREATE TABLE tbl_name #显示创建tbl_name表的语句
++创建用户 mysql> grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”; mysql> grant all privileges on *.* to test1@”%” identified by “abc”; mysql> flush privileges;
++用户管理 mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111 mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #删除用户帐号 mysql> SHOW GRANTS FOR user1; #显示创建user1用户的grant语句
++忘记mysql密码 先停止所有mysql服务进程 $ mysqld_safe –skip-grant-tables & mysql mysql> use mysql; mysql> update user set password=password(’111111′) where user=’root’; mysql> flush privileges; 然后重启mysql并以新密码登入即可
++创建索引 可以在建表的时候加入index indexname (列名)创建索引, 也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… ) mysql> CREATE INDEX number ON guest (number(10)); mysql> SHOW INDEX FROM tbl_name [FROM db_name] #显示现有索引 mysql> repair TABLE date QUICK; #索引列相关变量变化后自动重建索引
++查询及常用函数 mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; mysql> select college, region, seed from tournament ORDER BY region, seed; mysql> select col_name from tbl_name WHERE col_name > 0; mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录] mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME; mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW()); mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”); #加密密码用 mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序
* 函数count,AVG,SUM,MIN,MAX,LENGTH字符长度,LTRIM去除开头的空头,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左边/右边x个字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY严格检查大小写
* 比较运算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE mysql> select count(*),AVG(number_xx),Host,user from mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值
++控制条件函数 mysql> select if(1<10,2,3), IF(55>100,’true’,’false’); #IF()函数有三个参数,第一个是被判断的表达式,如果表达式为真,返回第二个参数,如果为假,返回第三个参数. mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;
++系统信息函数 mysql> select DATABASE(),VERSION(),USER(); mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一个测试mysql运算性能工具
++将wp_posts表中post_content字段中文字”old”替换为”new” mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)
++调整列顺序 mysql> alter table tablename CHANGE id id int(11) first;
++修改表中数据 insert [into] table_name [(column(s))] values (expression(s)) 例:mysql>insert into mydatabase values(’php’,’mysql’,’asp’,’sqlserver’,’jsp’,’oracle’); mysql> create table user select host,user from mysql.user where 1=0; mysql> insert into user(host,user) select host,user from mysql.user;
++更改表名 命令:rename table 原表名 to 新表名;
++表的数据更新 mysql> update table01 set field04=19991022[, field05=062218] where field01=1;
++删除数据 mysql> delete from table01 where field01=3; #如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
++SHELL提示符下运行SQL命令 $ mysql -e “show slave status/G ”
++坏库扫描修复 cd /var/lib/mysql/xxx && myisamchk playlist_block
++insert into a (x) values (’11a’) 出现: ata truncated for column ‘x’ at row 1 解决办法: 在my.ini里找到 sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION” 把其中的STRICT_TRANS_TABLES,去掉,然后重启mysql就ok了
++复制表 mysql> create table target_table like source_table