mysql常用操作(数据库常用操作)

转自：http://www.linuxidc.com/Linux/2011-12/50535.htm

SQL删除一个表中所有记录命令

TRUNCATE TABLE   或可以用truncate table [table名]清除某个表内的全部数据
删除表中的所有行，而不记录单个行删除操作。 

语法
TRUNCATE TABLE name

参数
name

是要截断的表的名称或要删除其全部行的表的名称。

注释
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。 

TRUNCATE TABLE 不能用于参与了索引视图的表。

示例
下例删除 authors 表中的所有数据。

TRUNCATE TABLE authors

增加记录
  INSERT INTO 表名(字段名，字段名) VALUES (值，值)；
删除记录
  DELETE FROM 表名 WHERE 条件(oracal中就可以不要from)
修改记录
  UPDATE 表名 SET 字段=值，字段=值 WHERE 条件
查询记录
  SELECT 字段，字段 FROM 表名 WHERE 条件

了解了一些最基本的操作命令后，我们再来学习如何创建一个数据库和数据库表。

    1、使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES; 
+----------+ 
| Database | 
+----------+ 
| mysql | 
| test | 
+----------+ 
3 rows in set (0.00 sec)

    2、创建一个数据库abccs 
    mysql> CREATE DATABASE abccs; 
    注意不同操作系统对大小写的敏感。

    3、选择你所创建的数据库 
    mysql> USE abccs 
    Database changed 
    此时你已经进入你刚才所建立的数据库abccs. 

    4、 创建一个数据库表

    首先看现在你的数据库中存在什么表： 
    mysql> SHOW TABLES; 
    Empty set (0.00 sec) 
    说明刚才建立的数据库中还没有数据库表。下面来创建一个数据库表mytable: 
    我们要建立一个你公司员工的生日表，表的内容包含员工姓名、性别、出生日期、出生城市。

mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), 
 birth DATE, birthaddr VARCHAR(20)) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.00 sec)
 

    由于name、birthadd的列值是变化的，因此选择VARCHAR，其长度不一定是20。可以选择从1到255的任何长度，如果以后需要改变它的字长，可以使用ALTER TABLE语句。）;性别只需一个字符就可以表示："m"或"f"，因此选用CHAR(1);birth列则使用DATE数据类型。

    创建了一个表后，我们可以看看刚才做的结果，用SHOW TABLES显示数据库中有哪些表：

mysql> SHOW TABLES; 
+---------------------+ 
| Tables in menagerie | 
+---------------------+ 
| mytables | 
+---------------------+
 

    5、显示表的结构：

mysql> DESCRIBE mytable; 
+-------------+-------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+-------------+------+-----+---------+-------+ 
| name | varchar(20) | YES | | NULL | | 
| sex | char(1) | YES | | NULL | | 
| birth | date | YES | | NULL | | 
| deathaddr | varchar(20) | YES | | NULL | | 
+-------------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)
 

    6、 往表中加入记录

    我们先用SELECT命令来查看表中的数据：字段带别名查询的时候，中文的别名要加上单引号； 
    mysql> select * from mytable; 
    Empty set (0.00 sec)
    这说明刚才创建的表还没有记录。

    加入一条新记录：

mysql> insert into mytable 
-> value (′abccs′,′f′,′1977-07-07′,′china′); 
Query OK, 1 row affected (0.05 sec)
 

 

    再用上面的SELECT命令看看发生了什么变化。我们可以按此方法一条一条地将所有员工的记录加入到表中。

    7、用文本方式将数据装入一个数据库表

    如果一条一条地输入，很麻烦。我们可以用文本文件的方式将所有记录加入你的数据库表中。创建一个文本文件“mysql.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以在CREATE TABLE语句中列出的列次序给出，例如：

abccs f 1977-07-07 china 　 
mary f 1978-12-12 usa 
tom m 1970-09-02 usa

    使用下面命令将文本文件“mytable.txt”装载到mytable表中:mysql> LOAD DATA LOCAL INFILE "mytable.txt(加路径)" INTO TABLE pet(表名);

    再使用如下命令看看是否已将数据输入到数据库表中：mysql> select * from mytable;

MySQL实用命令
Wikipedia，自由的百科全书
一) 连接MYSQL：

   格式： mysql -h主机地址 -u用户名 －p用户密码

1、例1：连接到本机上的MYSQL

   首先在打开DOS窗口，然后进入mysql安装目录下的bin目录下，例如： D:\mysql\bin，再键入命令mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql> 
2、例2：连接到远程主机上的MYSQL

   假设远程主机的IP为：10.0.0.1，用户名为root,密码为123。则键入以下命令：
   mysql -h10.0.0.1 -uroot -p123 
  （注：u与root可以不用加空格，其它也一样） 
3、退出MYSQL命令

   exit （回车）

(二) 修改密码：

   格式：mysqladmin -u用户名 -p旧密码 password 新密码 
1、例1：给root加个密码123。首先在DOS下进入目录C:\mysql\bin，然后键入以下命令：

   mysqladmin -uroot -password 123 
   注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。 
2、例2：再将root的密码改为456

   mysqladmin -uroot -pab12 password 456 
(三) 增加新用户：（注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符）

   格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码" 
   例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令： 
   grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

   但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见例2。 
   例2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。 
   grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"; 
   如果你不想test2有密码，可以再打一个命令将密码消掉。 
   grant select,insert,update,delete on mydb.* to test2@localhost identified by ""; 
(四) 显示命令

1、显示数据库列表：

   show databases; 
   刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。 
2、显示库中的数据表：

   use mysql； //打开库
   show tables; 
3、显示数据表的结构：

   describe 表名; 
4、建库：

   create database 库名; 
5、建表：

   use 库名； 
   create table 表名 (字段设定列表)； 
6、删库和删表:

   drop database 库名; 
   drop table 表名； 
7、将表中记录清空：

   delete from 表名; 
8、显示表中的记录：

   select * from 表名;

#################################

查看mysql的字符集：
mysql> show variables like "character\_set\_%";
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | gbk    |
| character_set_connection | gbk    |
| character_set_database   | gbk    |
| character_set_filesystem | binary |
| character_set_results    | gbk    |
| character_set_server     | gbk    |
| character_set_system     | utf8   |
+--------------------------+--------+ 
character_set_system 总是utf-8

这5个最好总是保持一致，要不就和乱码玩爽吧:
character_set_client    
character_set_connection
character_set_database  
character_set_results   
character_set_server  

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%<???>
2008-09-12
如何启动/停止/重启MySQL
一、启动方式

D:\MySQL\MySQL Server 5.0\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

1、使用 service 启动：service mysqld start

2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start

3、使用 safe_mysqld 启动：safe_mysqld&

二、停止 
停止：
D:\MySQL\MySQL Server 5.0\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

1、使用 service 启动：service mysqld stop

2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop

3、mysqladmin shutdown

三、重启

1、使用 service 启动：service mysqld restart

2、使用 mysqld  脚本启动：/etc/inint.d/mysqld restart

一 . 安装与配置MYSQL
二 . 常用mysql命令行命令
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 –uroot –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;

8 . 添加数据 : Insert into 表名 [(字段1 , 字段2 , ….)] values (值1 , 值2 , …..); 
如果向表中的每个字段都插入一个值,那么前面 [ ] 括号内字段名可写也可不写
   例 : insert into mytable (id,username) values (1,’zhangsan’);

9 . 查询 : 查询所有数据 : select * from table_name; 
查询指定字段的数据 : select 字段1 , 字段2 from table_name;
例 : select id,username from mytable where id=1 order by desc;多表查询语句------------参照第17条实例

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

16 . 查看数据库当前引擎
   SHOW CREATE TABLE table_name;
   修改数据库引擎
   ALTER TABLE table_name ENGINE=MyISAM | InnoDB;

17 . 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,'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>