常用 mysql 命令行命令
转于:http://blog.csdn.net/coldy456/archive/2010/03/09/5358713.aspx
1 .mysql 的启动与停止
启动 MYSQL 服务 net start mysql
停止 MYSQL 服务 net stop mysql
2 . netstat –na | findstr 3306 查看被监听的端口 , findstr 用于查找后面的端口是否存在
3 . 在命令行中登陆 MYSQL 控制台 , 即使用 MYSQL COMMEND LINE TOOL
语法格式 mysql –user=root –password=123456 db_name
或 mysql –u root –p123456 db_name
4 . 进入 MYSQL 命令行工具后 , 使用 status; 或 /s 查看运行环境信息
5 . 切换连接数据库的语法 : use new_dbname;
6 . 显示所有数据库 : show databases;
7 . 显示数据库中的所有表 : show tables;
8 . 显示某个表创建时的全部信息 : show create table table_name;
9 . 查看表的具体属性信息及表中各字段的描述
Describe table_name; 缩写形式 : desc table_name;
MySql 中的 SQL 语句
1 数据库创建 : Create database db_name;
数据库删除 : Drop database db_name; 删除时可先判断是否存在,写成 : drop database if exits db_name
2 建表 : 创建数据表的语法 : create table table_name ( 字段 1 数据类型 , 字段 2 数据类型 );
例 : create table mytable (id int , username char(20));
删表 : drop table table_name; 例 : drop table mytable;
3 添加数据 : Insert into 表名 [( 字段 1 , 字段 2 , ….)] values ( 值 1 , 值 2 , …..);
如果向表中的每个字段都插入一个值 , 那么前面 [ ] 括号内字段名可写也可不写
例 : insert into mytable (id,username) values (1,’zhangsan’);
4 查询 : 查询所有数据 : select * from table_name;
查询指定字段的数据 : select 字段 1 , 字段 2 from table_name;
例 : select id,username from mytable where id=1 order by desc; 多表查询语句 ------------ 参照第 17 条实例
5 更新指定数据 , 更新某一个字段的数据(注意,不是更新字段的名字)
Update table_name set 字段名 =’ 新值 ’ [, 字段 2 =’ 新值 ’ , …..][where id=id_num] [order by 字段 顺序 ]
例 : update mytable set username=’lisi’ where id=1;
Order 语句是查询的顺序 , 如 : order by id desc( 或 asc) , 顺序有两种 : desc 倒序 (100—1, 即从最新数据往后查询 ),asc( 从 1-100) , Where 和 order 语句也可用于查询 select 与删除 delete
6 删除表中的信息 :
删除整个表中的信息 : delete from table_name;
删除表中指定条件的语句 : delete from table_name where 条件语句 ; 条件语句如 : id=3;
7 创建数据库用户
一次可以创建多个数据库用户如:
CREATE USER username1 identified BY ‘password’ , username2 IDENTIFIED BY ‘password’….
8 用户的权限控制: grant
库,表级的权限控制 : 将某个库中的某个表的控制权赋予某个用户
Grant all ON db_name.table_name TO user_name [ indentified by ‘password’ ];
9 表结构的修改
( 1 )增加一个字段格式:
alter table table_name add column ( 字段名 字段类型 ); ---- 此方法带括号
( 2 )指定字段插入的位置:
alter table table_name add column 字段名 字段类型 after 某字段;
删除一个字段:
alter table table_name drop 字段名 ;
( 3 )修改字段名称 / 类型
alter table table_name change 旧字段名 新字段名 新字段的类型 ;
( 4 )改表的名字
alter table table_name rename to new_table_name;
( 5 )一次性清空表中的所有数据
truncate table table_name; 此方法也会使表中的取号器 (ID) 从 1 开始
10 增加主键,外键,约束,索引。。。。 ( 使用方法见 17 实例 )
① 约束(主键 Primary key 、唯一性 Unique 、非空 Not Null )
② 自动增张 auto_increment
③ 外键 Foreign key----- 与 reference table_name(col_name 列名 ) 配合使用,建表时单独使用
④ 删除多个表中有关联的数据 ---- 设置 foreign key 为 set null --- 具体设置参考帮助文档
11 查看数据库当前引擎
SHOW CREATE TABLE table_name;
修改数据库引擎
ALTER TABLE table_name ENGINE=MyISAM | InnoDB;
12SQL 语句运用实例 :
--1 建 users 表
create table users (id int primary key auto_increment,nikename varchar(20) not null unique,password varchar(100) not null,address varchar(200), reg_date timestamp not null default CURRENT_TIMESTAMP);
--2 建 articles 表 , 在建表时设置外键
create table articles (id int primary key auto_increment,content longtext not null,userid int,constraint foreign key (userid) references users(id) on delete set null);
-----------------------------------------------------------------------
--2.1 建 articles 表 , 建表时不设置外键
create table articles (id int primary key auto_increment,content longtext not null,userid int);
--2.2 给 articles 表设置外键
alter table articles add constraint foreign key (userid) references users(id) on delete set null;
------------------------------------------------------------------------
--3. 向 users 表中插入数据 , 同时插入多条
insert into users (id,nikename,password,address) values (1,'lyh1','1234',null),(10,'lyh22','4321',' 湖北武汉 '),(null,'lyh333','5678', ' 北京海淀 ');
--4. 向 article 中插入三条数据
insert into articles (id,content,userid) values (2,'hahahahahaha',11),(null,'xixixixixix',10),(13,'aiaiaiaiaiaiaiaiaiaiaiaia',1),(14,'hohoahaoaoooooooooo',10);
--5. 进行多表查询,选择 users 表中 ID=10 的用户发布的所有留言及该用户的所有信息
select articles.id,articles.content,users.* from users,articles where users.id=10 and articles.userid=users.id order by articles.id desc;
--6. 查看数据库引擎类型
show create table users;
--7. 修改数据库引擎类型
alter table users engine=MyISAM; --- 因为 users 表中 ID 被设置成外键,执行此句会出错
--8. 同表查询 , 已知一个条件的情况下 . 查询 ID 号大于用户 lyh1 的 ID 号的所有用户
select a.id,a.nikename,a.address from users a,users b where b.nikename='lyh1' and a.id>b.id;
------ 也可写成
select id,nikename,address from users where id>(select id from users where nikename='lyh1');
--9. 显示年龄比领导还大的员工:
select a.name from users a,users b where a.managerid=b.id and a.age>b.age;
查询编号为 2 的发帖人 : 先查 articles 表 , 得到发帖人的编号 , 再根据编号查 users 得到的用户名。
接着用关联查询 .
select * from articles,users 得到笛卡儿积 , 再加 order by articles.id 以便观察
使用 select * from articles,users where articles.id=2 筛选出 2 号帖子与每个用户的组合记录
再使用 select * from articles,users where articles.id=2 and articles.userid=users.id 选出 users.id 等于 2 号帖的发帖人 id 的记录 .
只取用户名 :select user where user.id=(select userid from articles where article.id =2)
找出年龄比小王还大的人 : 假设小王是 28 岁 , 先想找出年龄大于 28 的人
select * from users where age>(select age from users where name='xiaowang');
***** 要查询的记录需要参照表里面的其他记录 :
select a.name from users a,users b where b.name='xiaowang' and a.age>b.age
表里的每个用户都想 pk 一下 .select a.nickname,b.nickname from users a,users b where a.id>b.id ;
更保险的语句 :select a.nickname,b.nickname from (select * from users order by id) a,(se
lect * from users order by id) b where a.id>b.id ;
再查询某个人发的所有帖子 .
select b.* from articles a , articles b where a.id=2 and a.userid=b.userid
说明 : 表之间存在着关系, ER 概念的解释,用 access 中的示例数据库演示表之间的关系 . 只有 innodb 引擎才支持 foreign key , mysql 的任何引擎目前都不支持 check 约束。
字符集出现错误解决办法
出现的问题:
mysql> update users
-> set username=' 关羽 '
-> where userid=2;
ERROR 1366 (HY000): Incorrect string value: '/xB9/xD8/xD3/xF0' for column 'usern
ame' at row 1
向表中插入中文字符时,出现错误。
mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 2 | ???? |
| 3 | ???? |
| 4 | ?í?ù |
+--------+----------+
3 rows in set (0.00 sec)
表中的中文字符位乱码。
解决办法:
使用命令:
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)
Connection id: 8
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 7 hours 39 min 19 sec
Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta
bles: 1 Queries per second avg: 0.006
--------------
查看 mysql 发现 Server characterset , Db characterset 的字符集设成了 latin1 ,所以出现中文乱码。
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
更改表的字符集。
mysql> alter table users character set GBK;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看表的结构:
mysql> show create users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'users
' at line 1
mysql> show create table users;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userid` int(11) default NULL,
`username` char(20) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| userid | int(11) | YES | | NULL | |
| username | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
这时向表中插入中文然后有错误。
mysql> insert into users values(88,' 中文 ');
ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern
ame' at row 1
mysql> insert into users values(88,' 中文 ');
ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern
ame' at row 1
还要更改 users 表的 username 的字符集。
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern
ame' at row 1
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern
ame' at row 1
因为表中已经有数据,所以更改 username 字符集的操作没有成 ***
清空 users 表中的数据
mysql> truncate table users;
Query OK, 3 rows affected (0.01 sec)
从新更改 user 表中 username 的字符集
mysql> alter table users modify username char(20) character set gbk;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
这时再插入中文字符,插入成 *** 。
mysql> insert into users values(88,' 中文 ');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 88 | 中文 |
+--------+----------+
1 row in set (0.00 sec)
mysql>
1 、 MySQL 常用命令
create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库,不提醒
show tables; 显示表
describe tablename; 表的详细描述
select 中加上 distinct 去除重复字段
mysqladmin drop databasename 删除数据库前,有提示。
显示当前 mysql 版本和当前日期
select version(),current_date;
2 、修改 mysql 中 root 的密码:
shell>mysql -u root -p
mysql> update user set password=password(”xueok654123″) where user=’root’;
mysql> flush privileges // 刷新数据库
mysql>use dbname ; 打开数据库:
mysql>show databases; 显示所有数据库
mysql>show tables; 显示数据库 mysql 中所有的表:先 use mysql ;然后
mysql>describe user; 显示表 mysql 数据库中 user 表的列信息);
3 、 grant
创建一个可以从任何地方连接服务器的一个完全的超级用户,但是必须使用一个口令 something 做这个
mysql> grant all privileges on *.* to user@localhost identified by ’something’ with
增加新用户
格式: grant select on 数据库 .* to 用户名 @ 登录主机 identified by “ 密码 ”
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;
删除授权:
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;
创建一个用户 custom 在特定客户端 it363.com 登录,可访问特定数据库 fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’
重命名表 :
mysql > alter table t1 rename t2;
4 、 mysqldump
备份数据库
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
恢复数据库
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql
如果只想卸出建表指令,则命令如下:
shell> mysqladmin -u root -p -d databasename > a.sql
如果只想卸出插入数据的 sql 命令,而不需要建表命令,则命令如下:
shell> mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据,而不想要什么 sql 命令时,应该如何操作呢?
mysqldump -T./ phptest driver
其中,只有指定了 -T 参数才可以卸出纯文本文件,表示卸出数据的目录, ./ 表示当前目录,即与 mysqldump 同一目录。如果不指定 driver 表,则将卸出整个数据库的数据。每个表会生成两个文件,一个为 .sql 文件,包含建表执行。另一个为 .txt 文件,只包含数据,且没有 sql 指令。
5 、可将查询存储在一个文件中并告诉 mysql 从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如,如果在文件 my_file.sql 中存放有查
询,可如下执行这些查询:
例如,如果您想将建表语句提前写在 sql.txt 中 :
mysql > mysql -h myhost -u root -p database < sql.txt
修改密码
mysqladmin -u 用户名 -p 旧密码 password 新密码
备份数据库
mysqldump –u root –p opt 数据库名 > 备份文件名