mysql笔记
时间：2010-03-21 来源：xhq6632
1、取随机记录
SELECT id FROM student order by rand() limit 10
2、分配远程用户权限
GRANT ALL ON db_name.* TO username@% IDENTIFIED BY 'userpassword'
或
GRANT ALL PRIVILEGES ON *.* TO username@% IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
3、select into 和 set
select a,b,c into @a,@b,@c from t where id=1;
set @a='',@b=1,@c=NOW();
4、忘记密码解决办法
更新中……
1。停止mysql服务。//打开命令行窗口，停止mysql服务： Net stop mysql
2。启动mysql，一般到mysql的安装路径，找到 mysqld-nt.exe
执行：mysqld-nt --skip-grant-tables 当前窗口将会停止。
3。另外打开一个命令行窗口，进入MYSQL的安装位置下BIN目录，运行mysql
4。输入如下命令：
>use mysql
>update user set password=password("new_password") where user="root";
>flush privileges;
>exit
6。用Ctrl+Alt+Del，找到mysqld-nt的进程杀掉它，在重新启动mysql-nt服务，就可以用新密码登录了
2。启动mysql，一般到mysql的安装路径，找到 mysqld-nt.exe
执行：mysqld-nt --skip-grant-tables 当前窗口将会停止。
3。另外打开一个命令行窗口，进入MYSQL的安装位置下BIN目录，运行mysql
4。输入如下命令：
>use mysql
>update user set password=password("new_password") where user="root";
>flush privileges;
>exit
6。用Ctrl+Alt+Del，找到mysqld-nt的进程杀掉它，在重新启动mysql-nt服务，就可以用新密码登录了
5.OPTIMIZE TABLE
当您的库中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。Discuz! 在系统数设置界面提供了数据表优化的功能，可以去除删除操作后留下的数据文件碎片，减小文件尺寸，加快未来的读写操作。您只要在做完批量删除，或定期（如每一两个月）进行一次数据表优化操作即可。
OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作
OPTIMIZE TABLE语法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表，OPTIMIZE TABLE按如下方式操作：
1. 如果表已经删除或分解了行，则修复表。
2. 如果未对索引页进行分类，则进行分类。
3. 如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。
对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。
使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld，您可以使OPTIMIZE TABLE对其它表类型起作用。
注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
OPTIMIZE TABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。已经这么做了，因此，用于MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器，在默认情况下，这些命令将被复制到复制从属服务器中。
--备份与恢复
-----------------------------------------------------------------
--备份数据库(cmd 命令行下进入mysql/bin目录操作)
mysqldump --default-character-set=gbk -uroot -proot bugfree > d:/bugfree080228.txt
--导出数据库(在同一数据库操作时可以不设定字符)
mysqldump -uroot -proot bugfree > f:/bugfree.txt
--导出表(在同一数据库操作时可以不设定字符)
mysqldump -uroot -proot bugfree > f:/bugfree.txt
--创建库
mysql> create database rbugfree;
--导入数据
mysql> source d:/bugfree080228.txt
-----------------------------------------------------------------
--基本操作
-----------------------------------------------------------------
--登陆数据库
D:\phpStudy\MySQL\bin>mysql -uroot -proot
--查看数据库
mysql> show databases;
--选择数据库
mysql> use bugfree;
--设置字符集
mysql> set names 'gbk';
--查询数据库中的表
mysql> show tables;
--创建表
mysql> create table test(
-> tid int(10) not null,
-> tname varchar(100) not null,
-> tdate datetime not null default '0000-00-00',
-> primary key (tid));
--查看表结构
mysql> desc test;
--添加列
mysql> alter table test add(tage int(3));
--修改原表结构
mysql> alter table test modify tage int(5) not null;
--修改列的默认值
mysql> alter table test alter tage set default '0';
--去掉列的默认值
mysql> alter table test alter tage drop default;
--删除列
mysql> alter table test drop column tage;
--插入数据
mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');
--查询数据
mysql> select * from test;
--模糊查询
mysql> select * from test where tname like '%杨%';
--修改数据
mysql> update test set tname='张三' where tid='2';
--删除数据
mysql> delete from test where tid='2';
--删除表
mysql> drop table test;
--重命名表
mysql> alter table test rename testbak;
--分页查询(limit 起始行，取多少行)
mysql> select * from testbak limit 2,1;
--刷新数据库
mysql> flush privileges;
--显示数据库版本
mysql> select version();
--显示当前时间
mysql> select current_date;
--修改用户密码
D:\phpStudy\MySQL\bin>mysqladmin -uroot -proot password yangjuqi
--将查询出的数据写入文件
mysql> select * from testbak into outfile "d:/test.txt" fields terminated by ",";
--查看数据库状态
mysql> status;
--查看所有编码
mysql> show variables like 'character_set_%';
--导入sql文件命令
mysql>source d:/mysql.sql;
Code
/*初始化*/
drop procedure if exists useCursor //
/*建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '' ;
declare allName varchar(255) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test.level ;
/* mysql 不知道为什么用异常加入判断 ？
* 此请参考官方文档 20.2.11. 光标 光标
* 这把 游标 异常后 捕捉
* 并设置 循环使用 变量 tmpname 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
select allName ;
END;//
OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作
OPTIMIZE TABLE语法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表，OPTIMIZE TABLE按如下方式操作：
1. 如果表已经删除或分解了行，则修复表。
2. 如果未对索引页进行分类，则进行分类。
3. 如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。
对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。
使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld，您可以使OPTIMIZE TABLE对其它表类型起作用。
注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
OPTIMIZE TABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。已经这么做了，因此，用于MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器，在默认情况下，这些命令将被复制到复制从属服务器中。
6.常用语句
--备份与恢复
-----------------------------------------------------------------
--备份数据库(cmd 命令行下进入mysql/bin目录操作)
mysqldump --default-character-set=gbk -uroot -proot bugfree > d:/bugfree080228.txt
--导出数据库(在同一数据库操作时可以不设定字符)
mysqldump -uroot -proot bugfree > f:/bugfree.txt
--导出表(在同一数据库操作时可以不设定字符)
mysqldump -uroot -proot bugfree > f:/bugfree.txt
--创建库
mysql> create database rbugfree;
--导入数据
mysql> source d:/bugfree080228.txt
-----------------------------------------------------------------
--基本操作
-----------------------------------------------------------------
--登陆数据库
D:\phpStudy\MySQL\bin>mysql -uroot -proot
--查看数据库
mysql> show databases;
--选择数据库
mysql> use bugfree;
--设置字符集
mysql> set names 'gbk';
--查询数据库中的表
mysql> show tables;
--创建表
mysql> create table test(
-> tid int(10) not null,
-> tname varchar(100) not null,
-> tdate datetime not null default '0000-00-00',
-> primary key (tid));
--查看表结构
mysql> desc test;
--添加列
mysql> alter table test add(tage int(3));
--修改原表结构
mysql> alter table test modify tage int(5) not null;
--修改列的默认值
mysql> alter table test alter tage set default '0';
--去掉列的默认值
mysql> alter table test alter tage drop default;
--删除列
mysql> alter table test drop column tage;
--插入数据
mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');
--查询数据
mysql> select * from test;
--模糊查询
mysql> select * from test where tname like '%杨%';
--修改数据
mysql> update test set tname='张三' where tid='2';
--删除数据
mysql> delete from test where tid='2';
--删除表
mysql> drop table test;
--重命名表
mysql> alter table test rename testbak;
--分页查询(limit 起始行，取多少行)
mysql> select * from testbak limit 2,1;
--刷新数据库
mysql> flush privileges;
--显示数据库版本
mysql> select version();
--显示当前时间
mysql> select current_date;
--修改用户密码
D:\phpStudy\MySQL\bin>mysqladmin -uroot -proot password yangjuqi
--将查询出的数据写入文件
mysql> select * from testbak into outfile "d:/test.txt" fields terminated by ",";
--查看数据库状态
mysql> status;
--查看所有编码
mysql> show variables like 'character_set_%';
--导入sql文件命令
mysql>source d:/mysql.sql;
7.让mysql查询区分大小写
select * from partner where md5(log_name)=md5('wangyang') and log_pswd=password('admin');
8.将blob类型转换成字符串类型
select convert(con_bytes using utf8) from mms_content where id=3;
9.使用游标
Code/*初始化*/
drop procedure if exists useCursor //
/*建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '' ;
declare allName varchar(255) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test.level ;
/* mysql 不知道为什么用异常加入判断 ？
* 此请参考官方文档 20.2.11. 光标 光标
* 这把 游标 异常后 捕捉
* 并设置 循环使用 变量 tmpname 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
select allName ;
END;//
相关阅读 更多 +
