MYSQL - 常用语句大全

mysqld 常规MySQL服务器

mysqld-opt 优化mysql服务器，提供一些功能可以挖掘更好的功能　　mysqld-max 与mysqld一样，但可以支持更新，更具实验性质的功能（更不稳定）

•     　===常用命令==== 

1)     安装mysql
　　参见自带的INSTALL-SOURCE文件
　　$ ./configure ?prefix=/app/mysql-5.0.51a?with-charset=utf8 ?with-extra-charsets=utf8,gb2312,utf8
启动/关闭mysql
　　$ path/mysqld_safe -user=mysql &
　　$ /mysqladmin -p shutdown

2)     创建/删除 数据库或表

　　$ mysqladmin-u root -p create xxx
　   mysql>create database 数据库名；

mysql> create table table_name（column_name datatype {identity |null|not null},f_timeTIMESTAMP（8）,…）ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;

　　# TIMESTAMP（8） YYYYMMDD 其中（2/4/6/8/10/12/14）对应不同的时间格式

　　例： CREATE TABLE guest （name varchar（10）,sexvarchar（2）,age int（3）,career varchar（10））;

　　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 表名；

3)     查看数据库和查看数据库下的表
　　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表的语句

4)     当前使用的数据库
　　mysql> select database（）;

5)     创建索引
　　可以在建表的时候加入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; #索引列相关变量变化后自动重建索引

6)     查询及常用函数
　　mysql> select t1.name, t2.salary from employee ASt1, info AS t2 where t1.name = t2.name;
　　mysql> select college, region, seed fromtournament ORDER BY region, seed;
　　mysql> select col_name from tbl_name WHEREcol_name > 0;
　　mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录]
　　mysql> select DATE_FORMAT（NOW（）,’%m/%d/%Y’） as DATE, DATE_FORMAT（NOW（）,’%H:%m:%s’） ASTIME;
　　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,ISNOT NULL,LIKE,REGEXP/RLIKE
　　mysql> select count（*）,AVG（number_xx）,Host,userfrom mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值

7)     UNIX_TIMESTAMP（date）
　　返回一个Unix时间戳记（从’1970-01-0100:00:00′GMT开始的秒数）
　　mysql> select UNIX_TIMESTAMP（）;
　　mysql> select UNIX_TIMESTAMP（’1997-10-04 22:23:00′）;
　　mysql> select FROM_UNIXTIME（875996580）; #根据时间戳记算出日期

8)     控制条件函数
　　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;

9)     改变表结构
　　mysql> alter table table_name alter_spec [,alter_spec …]
　　例：alter table dbname add column userid int（11） not null primary key auto_increment;
　　这样，就在表dbname中添加了一个字段userid，类型为int（11）。

10)  调整列顺序
　　mysql> alter table tablename CHANGE id id int（11） first| after …;

 

11)  添加表中数据
　　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 frommysql.user where 1=0;
　　mysql> insert into user（host,user） select host,user from mysql.user;

12)  复制表结构
　　mysql> create table target_table like source_table

13)  复制整个表

mysql> Create table table_name as select …

14)  更改表名
　　命令：rename table 原表名 to 新表名；

15)  表的数据更新
　　mysql> update table01 set field04=19991022[, field05=062218]where field01=1;

16)  将wp_posts表中post_content字段中文字”old”替换为”new”
　　mysql> update wp_posts set post_content=replace（post_content,’old’，’new’）

17)  删除数据
　　mysql> delete from table01 where field01=3;
　　#如果想要清空表的所有纪录，建议用truncatetable tablename而不是delete from tablename.

•       　===数据库日常操作维护====

18)  修改root口令
　　$ mysqladmin -u root -p password ‘新密码’

19)  查看服务器状态
　　$ path/mysqladmin version –p

20)  连接远端mysql服务器
　　$ path/mysql -u 用户名 -p #连接本机
　　$ path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器

21)  创建用户
　　mysql> grant select,insert,update,delete,alter onmydb.* to test2@localhost identified by“abc”；
　　mysql> grant all privileges on *.* to test1@”%” identified by “abc”；
　　mysql> flush privileges;

22)  用户管理
　　mysql> update user set password=password （’11111′） where user=’test1′； #修改test1密码为111111
　　mysql> DELETE FROM user WHERE User=”testuser” andHost=”localhost”； #删除用户帐号
　　mysql> SHOW GRANTS FOR user1; #显示创建user1用户的grant语句

23)  忘记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并以新密码登入即可

24)  mysql数据库的备份和恢复
　　$ mysqldump -uuser -ppassword -B DB_name [--tablestable1 --tables table2] > exportfile.sql
　　$ mysql -uroot -p xxx < aaa.sql #导入表
　　$ mysqldump -u 用户名 -p 数据库名表名> 导出的文件名 ##导出单独的表

25)  导出一个数据库结构
　　$ mysqldump -u wcnc -p -d ?add-drop-tablesmgp_apps_wcnc >wcnc_db.sql
　　-d 没有数据 ?add-drop-table 在每个create语句之前增加一个drop table

 

26)  SHELL提示符下运行SQL命令
　　$ mysql -e “show slave status\G ”

27)  坏库扫描修复
　　cd /var/lib/mysql/xxx && myisamchkplaylist_block

28)  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了

29)  innodb支持事务
　　新表：create TABLE table-name （field-definitions） TYPE=INNODB;
　　旧表： alter TABLE table-name TYPE=INNODB;
　　mysql> start transaction #标记一个事务的开始
　　mysql> insert into…… #数据变更
　　mysql> ROLLBACK或commit #回滚或提交
　　mysql> SET AUTOCOMMIT=1; #设置自动提交
　　mysql> select @@autocommit; #查看当前是否自动提交

30)  表锁定相关
　　mysql> LOCK TABLE users READ; # 对user表进行只读锁定
　　mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
　　mysql> UNLOCK TABLES; #不需要指定锁定表名字， MySQL会自动解除所有表锁定

31)  系统信息函数
　　mysql> select DATABASE（）,VERSION（）,USER（）;
　　mysql> select BENCHMARK（9999999,LOG（RAND（）*PI（））） ASPERFORMANACE; #一个测试mysql运算性能工具

 

•  　　=====一些mysql优化与管理======

 

32)  管理用命令
　　mysql> show variables #查看所有变量值
　　? max_connections 数据库允许的最大可连接数，
　　#需要加大max_connections可以在my.cnf中加入set-variable =max_connections=32000,可以对与下面的threads_connected值决定是否需要增大。
　　show status [like …];
　　? threads_connected 数据库当前的连接线程数
　　#FLUSH STATUS 可以重置一些计数器
　　show processlist;
　　kill id;

33)  my.cnf配置
　　?Enable Slow Query Log
　　long_query_time=1
　　log-slow-queries=/var/log/mysql/log-slow-queries.log
　　log-queries-not-using-indexes
　　# mysqldumpslow -s c -t 20 host-slow.log #访问次数最多的20个sql语句
　　# mysqldumpslow -s r -t 20 host-slow.log #返回记录集最多的20个sql
　　?others
　　max_connections=500 #用过的最大连接数SHOWStatus like ‘max_used_connection’；
　　wait_timeout=10 #终止所有空闲时间超过10 秒的连接
　　table_cache=64 #任何时间打开表的总数
　　ax_binlog_size=512M #循环之前二进制日志的最大规模
　　max_connect_errors = 100
　　query_cache_size = 256M #查询缓存
　　#可用 SHOW STATUS LIKE‘qcache%’；查看命中率
　　#FLUSH STATUS重置计数器， FLUSHQUERY CACHE清缓存
　　thread_cache = 40
　　#线程使用，SHOW STATUS LIKE‘Threads_created %’； 值快速增加的话考虑加大
　　key_buffer = 16M
　　#show status like ‘%key_read%’； Key_reads 代表命中磁盘的关键字请求个数
　　#A: 到底 Key Buffer 要设定多少才够呢？ Q: MySQL 只会 Cache 索引（*.MYI），因此参考所有 MYI文件的总大小
　　sort_buffer_size = 4M #查询排序时所能使的缓冲区大小，每连接独享4M
　　#show status like ‘%sort%’； 如sort_merge_passes很大，就表示加大
　　sort_buffer_sizesort_buffer_size = 6M #查询排序时所能使用的缓冲区大小，这是每连接独享值6M
　　read_buffer_size = 4M　#读查询操作所能使用的缓冲区大小
　　join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小
　　skip-locking #取消文件系统的外部锁
　　skip-name-resolve
　　thread_concurrency = 8　　#最大并发线程数，cpu数量*2
　　long_query_time = 10 #Slow_queries记数器的查询时间阀值