mysql常用的语句有哪些

共计 6797 个字符，预计需要花费 17 分钟才能阅读完成。

本篇内容介绍了“mysql 常用的语句有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

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

++ 安装 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

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

++ 查看服务器状态
$ path/mysqladmin version -p

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

++ 创建 / 删除 数据库或表
$ 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  identified by“abc”;
mysql grant all privileges on *.* to”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 数据库的备份和恢复
$ mysqldump -uuser -ppassword -B DB_name [–tables table1 –tables table2] exportfile.sql
$ mysql -uroot -p xxx aaa.sql #导入表
$ mysqldump -u 用户名 -p 数据库名 表名 导出的文件名 ## 导出单独的表

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

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

++ 当前使用的数据库
mysql select database();

=== 数据库日常操作维护 ====
++ 创建表
mysql create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age int(3),career varchar(10));
# desc guest 可查看表结构信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14) 对应不同的时间格式
mysql SHOW CREATE TABLE tbl_name #显示创建 tbl_name 表的语句

++ 创建索引
可以在建表的时候加入 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
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; #分组并统计次数 / 平均值

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

++ 控制条件函数
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 table_name alter_spec [, alter_spec …]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
这样，就在表 dbname 中添加了一个字段 userid，类型为 int(11)。

++ 调整列顺序
mysql alter table tablename CHANGE id id int(11) first;

++ 修改表中数据
insert [into] table_name [(column(s))] values (expression(s))
例：mysql insert into mydatabase values(’’,’mysql’,’’,’sqlserver’,’’,’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 statusG”

++ 坏库扫描修复
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

++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; #查看当前是否自动提交

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

===== 一些 mysql 优化与管理 ======
++ 管理用命令
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;

++my.cnf 配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log//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 #用过的最大连接数 SHOW Status 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 重置计数器, FLUSH QUERY 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 记数器的查询时间阀值

“mysql 常用的语句有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！