MySQL心得3--命令行操作表数据

1. 与界面操作相比，通过SQL语句操作更为灵活，功能更为强大。

  为了让客服端能识别汉字:

set character_set_client=gb2312;

为了让结果不出现乱码:

set character_set_results=gb2312;

2.       插入记录命令

    一旦创建了数据库和表，下一步就是向表里插入数据。通过insert或replace语句可以向表中插入一行或多行数据。（replace语句的用法和insert基本相同，使用replace可以在插入数据之前将与新纪录冲突的旧记录删除，从而使新纪录正常插入）

insert into 表名(字段名1，字段名2.。。。)

values(值1，值2.。。。);

例如：

Insert into xs(学号,姓名,专业名,总学分,照片,备注)values('081101','王林','计算机' ,50,null,null);

或者：（如果提供的值的个数和顺序与表中字段一致，可以省略字段名）

insert into xs values('081101','王林','计算机',50,null,null);

或者：（如果只给表中的部分字段插入值时，要指明字段名）

insert into xs(学号,姓名,专业名,出生日期,总学分)

values('081101','王林','计算机','1990-02-10',50);

如果只给表的部分列插入数据，需要指定这些列。对于没有指出的列，它们的值根据列默认值或有关属性来确定，MySQL处理的原则是：

3.  insert 语法格式：

Insert [low_priority |delayed | high_priority] [ignore]

     [into] 表名 [(字段名,...)]

     values ({expr | default},...),(...),...

     | SET col_name={expr| default}, ...

     [ on duplicate keyupdate=expr, ... ]

（1）具有identity属性的列，系统生成序号值来唯一标志列。

（2）具有默认值的列，其值为默认值。

（3）没有默认值的列，若允许为空值，则其值为空值；若不允许为空值，则出错。

（4）类型为timestamp的列，系统自动赋值。

values子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若表名后不给出列名，则在values子句中要给出每一列（除identity和timestamp类型的列）的值，如果列值为空，则值必须置为null，否则会出错。values子句中的值：

（1）expr：可以是一个常量、变量或一个表达式，也可以是空值null，其值的数据类型要与列的数据类型一致。例如，列的数据类型为int，插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。

（2）default：指定为该列的默认值。前提是该列原先已经指定了默认值。如果列清单和values清单都为空，则insert会创建一行，每个列都设置成默认值。

Inert语句支持下列修饰符：

low_priority：可以使用在insert、delete和update等操作中，当原有客户端正在读取数据时，延迟操作的执行，直到没有其他客户端从表中读取为止。

delayed：若使用此关键字，则服务器会把待插入的行放到一个缓冲器中，而发送insert delayed语句的客户端会继续运行。如果表正在被使用，则服务器会保留这些行。当表空闲时，服务器开始插入行，并定期检查是否有新的读取请求（仅适用于myisam、memory和archive表）。

High_priority：可以使用在selectT和insert操作中，使操作优先执行。

Ignore：使用此关键字，在执行语句时出现的错误就会被当做警告处理。

onduplicate key update…：使用此选项插入行后，若导致uniqe key或primary key出现重复值，则根据update后的语句修改旧行（使用此选项时delayed被忽略）。

set子句：set子句用于给列指定值，使用set子句时表名的后面省略列名。要插入数据的列名在set子句中指定，col_name为指定列名，等号后面为指定数据，未指定的列，列值指定为默认值。

4.  使用insert语句可以向表中插入一行数据，也可以插入多行数据，插入的行可以给出每列的值，也可只给出部分列的值，还可以向表中插入其他表的数据。

使用insert into…select…，可以快速地从一个或多个表中向一个表插入多个行。语法格式如下：

Insert [low_priority |delayed | high_priority] [ignore]

     [into] 表名 [(字段名,...)]

    select ...

[ on duplicate key update=expr, ... ]

Select语句中返回的是一个查询到的结果集，insert语句将这个结果集插入到指定表中，但结果集在每行数据的字段数、字段的数据类型要与被操作的表完全一致。

    MySQL还支持图片的存储，图片一般可以以路径的形式来存储，即插入图片可以采用直接插入图片的存储路径。当然也可以直接插入图片本身，只要用load_file函数即可。

例如：

Insert into XS  values('081102', '程明', '计算机', 1, '1991-02-01',50, 'D:\IMAGE\picture.jpg', null);

下列语句是直接存储图片本身：

Insert into XS

    values('081102', '程明', '计算机', 1, '1991-02-01',50, load_file('D:\IMAGE\picture.jpg'), null);

    在表建完后设置主键，duplicateentry ‘学号’for key 1 ；

5. 删除记录:

delete from 表名   //删除表中的所有记录，此表变为了空表

delete from 表名 where 条件   //删除表中符合条件的记录

例如：delete from xs where 学号='081102';

drop table xs ; //删除整张表，结构和记录

truncate table 表名   //快速删除表中的所有记录

从单个表中删除，语法格式：

Delete [low_priority] [quick] [ignore] from表名

[where where_definition]  [orderby...] [limit row_count]

说明：

● quick修饰符：可以加快部分种类的删除操作的速度。

● from子句：用于说明从何处删除数据，后跟要删除数据的表名。

● where子句：where_definition中的内容为指定的删除条件。如果省略where子句则删除该表的所有行，where子句后面详细介绍

●order by子句：各行按照子句中指定的顺序进行删除，此子句只在与limit联用时才起作用。

●limit子句：用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。

6.  从多个表中删除行，语法格式：

delete [low_priority] [quick] [ignore] 表名[.*] [,表名 [.*] ...]  from table_references  [wherewhere_definition]

或：

delete [low_priority] [quick] [ignore]

   from  tbl_name[.*] [, tbl_name[.*] ...]

   Using  table_references   [where where_definition]

说明：对于第一种语法，只删除列于from子句之前的表中对应的行。对于第二种语法，只删除列于from子句之中（在using子句之前）的表中对应的行。作用是，可以同时删除多个表中的行，并使用其他的表进行搜索。

例子：

假设有3个表t1、t2、t3，它们都含有id列。要删除t1中id值等于t2的id值的所有行和t2中id值等于t3的id值的所有行，使用如下语句：

delete   t1, t2

     from  t1, t2, t3

     where  t1.id=t2.id and t2.id=t3.id;

或：

Delete from t1, t2 

     using t1, t2, t3

     where  t1.id=t2.id and  t2.id=t3.id;

7.  使用truncate table语句将删除指定表中的所有数据，因此也称其为清除表数据语句。

语法格式：

truncate table 表名；

说明：由于TRUNCATETABLE语句将删除表中的所有数据，且无法恢复，因此使用时必须十分小心。

truncate table 在功能上与不带where子句的delete语句相同，二者均删除表中的全部行。但truncate table比delete速度快，且使用的系统和事务日志资源少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。而truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。使用 truncate table，auto_increment计数器被重新设置为该列的初始值。

注意：对于参与了索引和视图的表，不能使用 truncate table删除数据，而应使用delete语句。

8.       修改记录（更新记录）

   要修改表中的一行数据，可以使用update语句，update可以用来修改一个表，也可以修改多个表。简要格式：

update 表名

set 字段名1=值1 [,字段名2=值2.。。。]

where 条件

修改单个表，语法格式：

update[low_priority] [ignore] 表名

   set col_name1=expr1 [, col_name2=expr2 ...]

   [where where_definition]

   [order by ...]  [limit row_count]

说明：

set子句：根据where子句中指定的条件对符合条件的数据行进行修改。若语句中不设定where子句，则更新所有行。col_name1、col_name2…为要修改列值的列名，expr1、expr2…可以是常量、变量或表达式。可以同时修改所在数据行的多个列值，中间用逗号隔开。

9.   修改多个表，语法格式：

update [low_priority] [ignore] table_references

   set col_name1=expr1 [, col_name2=expr2 ...]

   [where where_definition]

说明：table_references中包含了多个表的联合，各表之间用逗号隔开。

10. 按SQL包的格式导进来：source SQL包路径；

显示表的记录：select * from 表名；

插入记录时，除了数值类型不用加引号，其他类型都要加单引号（比如：char、date等）

11.       show语句

show tables或show tables from库名：显示当前数据库中所有表的名称。

show databases：显示MySQL中所有数据库的名称。

show columns from表名 from库名或show columnsfrom 库名.表名：显示表中列的名称。

show  grants for user_name：显示一个用户的权限，显示结果类似于grant命令。

show index from table_name：显示表的索引。

show  staus：显示一些系统特定资源的信息，例如，正在运行的线程数量。

show variables：显示系统变量的名称和值。

show  processlist：显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

show table status：显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。

show privileges：显示服务器所支持的不同权限。

show create database库名：显示创建某一个数据库的create database语句。

show create table表名：显示创建一个表的create table语句。

show events：显示所有事件的列表。

show innoDB status：显示InnoDB存储引擎的状态。

show logs：显示BDB存储引擎的日志。

show warnings：显示最后一个执行的语句所产生的错误、警告和通知。

show errors：只显示最后一个执行语句所产生的错误。

show [storage] engines：显示安装后的可用存储引擎和默认引擎。

show procedure status：显示数据库中所有存储过程基本信息，包括所属数据库、存储过程名称、创建时间等。

show create procedure sp_name：显示某一个存储过程的详细信息。

12. describe语句（即desc）

describe语句用于显示表中各列的信息，结果等于showcolumns from语句。

语法格式：

{describe | desc} tb1_name [col_name |wild ]

说明：

desc是describe的简写，二者用法相同。

col_name可以是一个列名称，或一个包含‘％’和‘_’的通配符的字符串，用于获得对于带有与字符串相匹配的名称的各列的输出。没有必要在引号中包含字符串，除非其中包含空格或其他特殊字符。

例如：

显示学生表： desc|describe xs；

     显示学生表学号列：   desc xs 学号；

注意：用图形界面时，输入数据的时候要防止出现不必要的空格，否则检索数据时可能会出现漏洞。