MySQL常用命令

MySQL常用命令

1.启动MYSQL服务 net start mysql

停止MYSQL服务 net stop mysql

2 . netstat –na | findstr 3306 查看被监听的端口 , findstr用于查找后面的端口是否存在

3 . mysql –u root –p123456 db_name

​ mysql -h主机地址 -u用户名 －p用户密码

4 . 进入MYSQL命令行工具后 , 使用status; 或/s 查看运行环境信息

create database 名字; 创建数据库
use database  名字; 选择数据库
drop database 名字 直接删除数据库 
show tables; 显示表
describe table 名字; 表的详细描述
select 中加上distinct去除重复字段
显示当前mysql版本和当前日期

select version(),current_date;
flush privileges //刷新数据库
describe table_name; 缩写形式 : desc table_name;
 alter table t1 rename t2;  重命名表
 
添加数据 : Insert into 表名 [(字段1 , 字段2 , ….)] values (值1 , 值2 , …..); 
查询 : 查询所有数据 : select * from table_name; 
查询指定字段的数据 : select 字段1 , 字段2 from table_name; 

2、修改mysql中root的密码：

mysql> mysql -u root -p

mysql> update user set password=password(”密码″) where user=’root’;

3、mysqldump

备份数据库

shell> mysql dump -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

4、 将建表语句提前写在test.txt中:

mysql > mysql -h myhost -u root -p database < test.txt

10 . 更新指定数据 , 更新某一个字段的数据（注意，不是更新字段的名字）

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

11 . 删除表中的信息 :
　　 删除整个表中的信息 : delete from table_name;
　　 删除表中指定条件的语句 : delete from table_name where 条件语句 ; 条件语句如 : id=3;

12 . 创建数据库用户
　　一次可以创建多个数据库用户如：
　　CREATE USER username1 identified BY ‘password’ , username2 IDENTIFIED BY ‘password’….

13 . 用户的权限控制：grant

库，表级的权限控制 : 将某个库中的某个表的控制权赋予某个用户
　　 Grant all ON db_name.table_name TO user_name [ indentified by ‘password’ ];

14 . 表结构的修改
　　（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开始

15 . 增加主键，外键，约束，索引。。。。
　　① 约束（主键Primary key、唯一性Unique、非空Not Null）
　　② 自动增张 auto_increment
　　③外键Foreign key-----与reference table_name(col_name列名)配合使用，建表时单独使用
　　④ 删除多个表中有关联的数据----设置foreign key 为set null —具体设置参考帮助文档

SQL语句运用实例:

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,'rtrttruui',11),
(null,'hjjkhuou',10),
(13,'sateydjykhl',1),
(14,'pocnhfztat',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约束。 

1、显示当前数据库服务器中的数据库列表：

mysql> SHOW DATABASES;
注意：mysql库里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。

2、显示数据库中的数据表：
mysql> USE 库名；
mysql> SHOW TABLES;

3、显示数据表的结构：
mysql> DESCRIBE 表名;

4、建立数据库：
mysql> CREATE DATABASE 库名;

5、建立数据表：
mysql> USE 库名;
mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

6、删除数据库：
mysql> DROP DATABASE 库名;

7、删除数据表：
mysql> DROP TABLE 表名；

8、将表中记录清空：
mysql> DELETE FROM 表名;

9、显示表中的记录：
mysql> SELECT * FROM 表名;

10、往表中插入记录：
mysql> INSERT INTO 表名 VALUES (”hyq”,”M”);

11、更新表中数据：
mysql-> UPDATE 表名 SET 字段名1=’a’,字段名2=’b’ WHERE 字段名3=’c’;

12、用文本方式将数据装入数据表中：
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;

13、导入.sql文件命令：
mysql> USE 数据库名;
mysql> SOURCE d:/mysql.sql;

14、命令行修改root密码：
mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

15、显示use的数据库名：
mysql> SELECT DATABASE();

16、显示当前的user：
mysql> SELECT USER();

五、备份数据库1.导出整个数据库

导出文件默认是存在mysql/bin目录下
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql

2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql

3.导出一个数据库结构
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.带语言参数导出
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

六、导入数据库

例如：数据库名为 dbTest

首先，进入mysql

mysql -uroot -p123456

然后，创建数据库

create dbTest

exit

最后，导入数据库

mysql -uroot -p123456 dbTest < dbTest_bk.sql

mysqldump导出抛出异常：

mysqldump: Got error: 1044: Access denied for user ‘username’@’%’ to database ‘dbname’ when using LOCK TABLES

解决办法，添加参数 –skip-lock-tables：

mysqldump –skip-lock-tables -h172.88.12.102 -username-pAnJnVs3C2tYXyTwV dbname> dbname_bk.sql

导入数据库：
登录MySQL: mysql -uroot -p123456
创建数据库： create database gameTop;

导入数据库：

mysql -uroot -p123456 gameTop < gameTop_db.sql

导入数据库表：

mysql -uroot -p123456 gameTop gametop800 < gameTop_table.sql

导入数据库表：
mysql -uroot -p123456 gameTop < gameTop_table.sql （不指定表名）

在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令，注意启动者应具有管理员权限。
刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、 root帐户设置密码，可用如下命令进行：

use mysql;
delete from User where User=”";
update User set Password=PASSWORD(’newpassword’) where User=’root’;

如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令：

mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;

上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。
在 进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技 术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。

MySql的用户管理是通过 User表来实现的，添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用 户。其中GRANT的常用用法如下：

grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;

若 要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段， 也可以使用REVOKE操作。
下面给出本人从其它资料(www.cn-Java.com)获得的对常用权限的解释：
全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
**特别的权限：**ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。