mysql数据库常用命令(详细)

 

 

mysql数据库常用命令(详细)

***************************************************
windows下mysql的启动与停止
启动MYSQL服务 net start mysql
停止MYSQL服务 net stop mysql

***************************************************
#linux下mysql的启动与停止(注意启动者应具有管理员权限)
启动MYSQL服务 /etc/init.d/mysql start   或 service mysqld start
停止MYSQL服务 /etc/init.d/mysql stop    或 service mysqld stop
重启MYSQL服务 /etc/init.d/mysql restart 或 service mysqld restart

#查看被监听的端口
lsof -P -i:3306

netstat -nap|grep 3306
###
# 导出命令
###

#导出整个数据库：
mysqldump -h主机 -u用户名 -p密码 db_name > /home/test.sql

3.导出一个数据库结构(-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table)
mysqldump -h主机 -u用户名 -p密码 -d –add-drop-table db_name > db_name.sql

#导出数据库中某一个表，包括表结构和数据：
mysqldump -h主机 -u用户名 -p密码 db_name table1> /home/table1.sql

#导出数据库中多个表，包括表结构和数据：
mysqldump -h主机 -u用户名 -p密码 db_name table1 table2 ……> /home/table.sql
 
#导出数据库结构
mysqldump -h主机 -u用户名 -p密码 -d db_name >/home/db_name.sql

#导出一个表，只有表结构
mysqldump -h主机 -u用户名 -p密码 -d db_name table1>/home/table1.sql

###
# 导入命令
###

1) mysql -h主机 -u用户名 -p 密码 db_name < /home/db_name.sql

2) 用source命令导入.sql文件

mysql -uroot -p
mysql>use 数据库
mysql>source /home/db_name.sql

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

###
# 数据库常用命令
###

#连接mysql数据库
shell>/usr/local/mysql/bin/mysql -uroot -p

#创建数据库
mysql> create database db_name;

#显示所有数据库
mysql> show databases;

#选择数据库
mysql> use db_name;

#直接删除数据库，不提醒
mysql> drop database name

#删除数据库时可先判断是否存在
drop database if exits db_name

#显示表
mysql> show tables;

#查看表的具体属性信息及表中各字段的描述
mysql> describe/desc table_name;

#查询数据，去除重复字段
mysql> select distinct 字段名 from table1 where 选择条件

#显示当前mysql版本和当前日期
mysql> select version(),current_date;
 
#修改mysql中root的密码：
mysql> update user set password=password("xueok654123") where user="root";

#刷新数据库
mysql> flush privileges;

#创建表
mysql> create table table_name (字段1 数据类型 , 字段2 数据类型);

#删除表
mysql> drop table table_name;
例 : drop table mytable;

#清空表
mysql> delete from table_name;
mysql> truncate table table_name;

#查看表的结构(显示某个表创建时的全部信息)：
mysql> show create table table_name;

#添加数据
mysql> insert into 表名 [(字段1 , 字段2 , ….)] values (值1 , 值2 , …..);
(如果向表中的每个字段都插入一个值,那么前面 [ ] 括号内字段名可写也可不写)
例 : mysql> insert into mytable (id,username) values (1,’zhangsan’);
　　
#查询
查询所有数据 : select * from table_name;
查询指定字段的数据 : select 字段1 , 字段2 from table_name;
例 : select id,username from mytable where id=1;

#查询并排序
mysql> select 字段1 , 字段2 from table_name order by desc(降序)/asc(升序)
例 : select id,username from mytable where id=1 order by desc;
　　
#更新数据
mysql> Update table_name set 字段名='新值' [, 字段2 ='新值' , …..][where id=id_num] [order by 字段 顺序]
例 : mysql> update mytable set username=’lisi’ where id=1;

#删除数据　　
删除整个表中的信息 : delete from table_name;
删除表中指定条件的语句 : delete from table_name where 条件

#表结构的修改
1)增加一个字段格式：
mysql> alter table table_name add column (字段名 字段类型); ----此方法带括号

2)指定字段插入的位置：
mysql> alter table table_name add column 字段名 字段类型 after 某字段；

3)删除一个字段：
mysql> alter table table_name drop字段名;

4)修改字段名称/类型
mysql> alter table table_name change 旧字段名 新字段名 新字段的类型;

5)重命名表或修改表名:
mysql> alter table t1 rename t2;
例：alter table table_name rename to new_table_name;

#查看数据库当前引擎
SHOW CREATE TABLE table_name;

#修改数据库引擎
ALTER TABLE table_name ENGINE=MyISAM | InnoDB;

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

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

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


#创建数据库用户
一次可以创建多个数据库用户如：
CREATE USER username1 identified BY "password" , username2 IDENTIFIED BY "password"

###
# mysql授权问题
###

#给新增用户授权：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的用户管理是通过 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";

特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

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

#删除授权：
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> status
　　
　　15 . 增加主键，外键，约束，索引。。。。(使用方法见17实例)
　　① 约束（主键Primary key、唯一性Unique、非空Not Null）
　　② 自动增张 auto_increment
　　③外键Foreign key-----与reference table_name(col_name列名)配合使用，建表时单独使用
　　④ 删除多个表中有关联的数据----设置foreign key 为set null ---具体设置参考帮助文档
　　
#17 . SQL语句运用实例:
1) 建users表,设置主键
create table users (
id int(10) 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 (10) primary key auto_increment,
content longtext not null,
userid int(5),
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,'aiaiai',1),
   (14,'hohoahaoaoo',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) 修改数据库引擎类型
　　alter table users engine=MyISAM;
　　
7) 同表查询,已知一个条件的情况下.查询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');
　　
8) 显示年龄比领导还大的员工：
select a.name from users a,users b where a.managerid=b.id and a.age>b.age;

说明: 表之间存在着关系，ER概念的解释，用access中的示例数据库演示表之间的关系.只有innodb引擎才支持foreign key，mysql的任何引擎目前都不支持check约束。