MySQL学习笔记

March 31st, 2006

作者: Jeffrey
出处:http://blog.zhangjianfeng.com/article/208

++mysql数据库的安装
看安装包自带安装说明或使用rpm包即可

++安装mysql
参见自带的INSTALL-SOURCE文件
./configure –prefix=/app/mysql-5.0.51a –with-charset=utf8 –with-extra-charsets=gbk,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>drop database 数据库名;
mysql>create table 表名;
mysql>drop table 表名;

++查看数据库和查看数据库下的表
mysql>show databases;
mysql>show tables;
mysql>desc 表名; #查看具体表结构信息

###用户相关操作命令####

++创建用户
mysql>grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”;
#以上命令新建用户test2,密码abc,仅可本机登录, 拥有对数据库mydb进行查询、插入、修改、删除权限
mysql>grant all privileges on *.* to test1@”%” identified by “abc”;
#以上命令新建用户test1,密码abc,可以从任何主机登入, 拥有对所有数据库进行查询、插入、修改、删除权限,其中”%”表示所有主机,也可以直接指定IP.
mysql>flush privileges; #刷新权限表

++查看/修改mysql系统用户信息
mysql> use mysql;
mysql> select host,user,password from user; #查看现有用户信息
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111

++删除用户帐号
DELETE FROM user WHERE User=”testuser” and Host=”localhost”;

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

++将wp_posts表中post_content字段中文字”old”替换为”new”
update wp_posts set post_content=replace(post_content,’old’,'new’)

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

++创建表
create table table_name
(column_name datatype {identity |null|not null},
…)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可查看表结构信息

++创建索引
create index index_name
on table_name (col_name[(length)],… )
例: CREATE INDEX number ON guest (number(10));

++查询
> (或>=) 检查左边值是否大于(或大于等于)右边值
< (或<=) 检查左边值是否小于(或小于等于)右边值
[not] between 检查左边值是否在某个范围内
[not] in 检查左边是否某个特定集的成员
[not] like 检查左边是否为右边的子串
is [not] null 检查左边是否为空值
在这里，可以用通配符_代表任何一个字符，%代表任何字符串。使用关键字、和可以生成复杂的词，它们运行检查时使用　　布尔表达式的多重标准集。
例： 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;

++改变表结构
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)。

++调整列顺序
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’);

++更改表名
命令：rename table 原表名 to 新表名;

++表的数据更新
(1) 一次修改一个字段，再次注意语法。文本需要加引号但数字不要。
mysql>update table01 set field03=’new info’ where field01=1;
(2)一次改变多个字段，记住在每一个更新的字段间用逗号隔开。
mysql>update table01 set field04=19991022, field05=062218 where field01=1;
(3) 一次更新多个数据
mysql>update table01 set field05=152901 where field04>19990101;

++删除数据
mysql>delete from table01 where field01=3;

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

++坏库扫描修复
cd /var/lib/mysql/xxx && myisamchk playlist_block

附:
++字段类型
1．INT[(M)]
正常大小整数类型
2．DOUBLE[(M,D)] [ZEROFILL]
正常大小(双精密)浮点数字类型
3．DATE
日期类型。支持的范围是’1000-01-01′到’9999-12-31′。MySQL以’YYYY-MM-DD’格式来显示DATE值，但是允许你使用字符串或数字把值赋给　　DATE列
4．CHAR(M)
定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
5．BLOB TEXT
BLOB或TEXT类型，最大长度为65535(2^16-1)个字符。
6．VARCHAR
变长字符串类型，最常用的类型。

++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了

++复制表
create table target_table like source_table

++一些mysql管理命令
show variables
#查看所有变量值,max_connections是数据库系统允许的最大可连接数,数据库当前的连接线程数threads_connected
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000
show processlist;

SHOW VARIABLES LIKE ‘character_set_%’; #查看可用字符集

* 全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。