MySQL常用命令

*Mysql命令语句不区分大小写

一、库操作

　　1. 导出整个数据库

　　　　mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)
　　　　mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

　　2. 导出一个数据库结构
　　　　mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
　　　　-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

　　3. 导入数据库

　　　　1)source 命令
　　　　　　进入mysql数据库控制台，如mysql -u root -p
　　　　　　mysql>use 数据库
　　　　　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
　　　　　　mysql>source wcnc_db.sql

　　　　2)使用mysqldump命令

　　　　　　mysqldump -u username -p dbname < filename.sql

　　　　3)使用mysql命令

　　　　　　mysql -u username -p -D dbname < filename.sql

　　4. 启动与退出MySQL

　　　　　1)进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql>

　　　　　2)退出MySQL：quit或exit

　　5. 创建数据库
　　　　命令：create database <数据库名>
　　　　例如：建立一个名为xhkdb的数据库
　　　　mysql> create database xhkdb; 

　　6. 显示所有的数据库
　　　　命令：show databases （注意：最后有个s）
　　　　mysql> show databases;

　　7. 删除数据库
　　　　命令：drop database <数据库名>
　　　　例如：删除名为 xhkdb的数据库
　　　　mysql> drop database xhkdb;

　　8.连接数据库

　　　　命令： use <数据库名>
　　　　例如：如果xhkdb数据库存在，尝试存取它：
　　　　mysql> use xhkdb;
　　　　屏幕提示：Database changed

　　9. 查看当前使用的数据库
　　　　mysql> select database();

　　　　当前数据库包含的表信息：
　　　　mysql> show tables; （注意：最后有个s）

二、单表操作，操作之前应连接某个数据库

1. 导出一个表
   mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
   mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
2. 导入数据库表 

　　　　1) 创建.sql文件 

　　　　2) 先产生一个库如auction.c:mysqlbin>mysqladmin -u root -p creat auction，会提示输入密码，然后成功创建。 
　　　　3) 导入auction.sql文件 
  　　　　　　c:mysqlbin>mysql -u root -p auction < auction.sql。 
　　　　　　　通过以上操作，就可以创建了一个数据库auction以及其中的一个表auction。

　　3. 导入数据库表2

　　　　首先创建一个文本文件”mysql.txt”,每行包含一个记录，用定位符(tab)把值分开，并且以在create table语句中列出的列次序给出，使用下面命令将文本文件“mysql.txt”装载到mytable表中：

　　　　Mysql>load data local infile “mytable.txt” into table mytable;

　　4. 建表
　　　　命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

　　　　mysql> create table MyClass(
　　　　> id int(4) not null primary key auto_increment,
　　　　> name char(20) not null,
　　　　> sex int(4) not null default '0',
　　　　> degree double(16,2));

　　5. 删除表
　　　　命令：drop table <表名>
　　　　例如：删除表名为 MyClass 的表
　　　　mysql> drop table MyClass;

　　6. 获取表结构

　　　　命令： desc 表名，或者show columns from 表名
　　　　mysql>describE MyClass
　　　　mysql> desc MyClass; 
　　　　mysql> show columns from MyClass;

　　7. 查询表中的数据（详见附录）
　　　　1)、查询所有行
　　　　　　命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >
　　　　　　例如：查看表 MyClass 中所有数据
　　　　　　mysql> select * from MyClass;
　　　　2)、查询前几行数据
　　　　　　例如：查看表 MyClass 中前3-5行数据 （行标号从0开始，即第一行的标号为0。）
　　　　　　mysql> select * from MyClass order by id limit 2,3; (表示从行标号为2开始的3个数据，即标号为2，3，4行数据）

　　　　　　或者：mysql> select * from MyClass limit 2,3;

　　　　3) 选择特定行/列

　　　　　　Mysql>select * from MyClass where name = ‘Tom’;（行）

　　　　　　Mysql>select name from MyClass;（列）

　　　　　　Mysql>select name, birth from mysql;(多列)

　　　　4) 组合条件来查询：

　　　　　　Mysql>select * from mysql where sex = ‘f’ and birthaddr = ‘China’;

　　　　5) 模糊匹配查询

　　　　　　在teacher这张表中查询姓“wang"老师的数量：

　　　　　　mysql>select name from teacher where name LIKE "wang%%";

　　8. 对行进入排序

　　　　对表中的记录按某个字段进行排序：

　　　　Mysql>select name,birth from mytable order by birth;

　　　　逆序：

　　　　Mysql>select name,birth from mytable order by birth desc;

　　9. 行计数

　　　　数据库经常要统计一些数据，如表中员工的数目，我们就要用到计数函数COUNT().此函数可对非NULL结果的记录进行记数。

　　　　Mysql > SELECT COUNT(*) FROM mytable;

     　　 员工中男女数量：

      　　Mysql > SELECT sex, COUNT(*) FROM mytable GROUP BY sex;

    　　 　　 +-------+------------+

    　　　　 | sex  |  COUNT(*)|

   　　　　  |   f   |     2      |

   　　　　　|   m  |     1     |

   　　　　  +-------+------------+

  　　　　   2 row in set (0.00 sec)

　　10. 插入数据
　　　　命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
　　　　例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为3 的名为Wang 的成绩为96.5.
　　　　mysql> insert into MyClass (seq, name, marks) values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

　　11. 删除表中数据

　　　　命令：delete from 表名 where 表达式

　　　　例如：删除表 MyClass中编号为1 的记录
　　　　mysql> delete from MyClass where id=1;

　　12. 修改表中数据：update 表名 set 字段=新值,… where 条件

　　　　mysql> update MyClass set name='Mary' where id=1;

　　　　update 表名 set 字段名 = replace(字段名,'旧内容','新内容');

　　　　文章前面加入4个空格
　　　　update article set content=concat('　　',content);

　　13. 在表中增加字段：

　　　　命令：alter table 表名 add字段 类型 其他; 

　　　　例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0 
　　　　mysql> alter table MyClass add passtest int(4) default '0'

　　14. 更改表中的字段类型

　　　　Mysql > ALTER TABLE MyClass MODIFY COLUMN id INT(20);

　　15. 更改表名：

　　　　命令：rename table 原表名 to 新表名; 
　　　　例如：在表MyClass名字更改为YouClass 
　　　　mysql> rename table MyClass to YouClass;
　　　　

三、多表操作

多表查询

假设有两个表mytable & title。Mytable 包含的字段为：name, sex, birth, birthaddr; title包含的字段为：writer, title, senddate. Writer中存储的为文章作者的姓名。我们可利用如下语句对这两个表进行组合查询：例如我们要查询作者abccs的姓名、性别和文章：

Mysql>SELECT name, sex, title FROM mytable, title WHERE name = writer AND name = ‘abccs’;

必须要指出的是，如果title的writer列表也取名为name，则语句应写为where mytable.name=title.name, 以示差别。

四、用批处理方式使用MySQL

首先建立一个批处理文件mytest.sql，内容如下：

Use abccs;

Select * from mytable;

Select name, sex, from mytable where name = ‘abccs’;

可用如下命令运行批处理文件：

[ada@localhost ~]mysql –uroot –p < mytest.sql;

如果结果非常多，可使用如下命令：

[ada@localhost ~]mysql –uroot –p < mytest.sql | more;

五、SQL库字段类型

1．INT[(M)] 型： 正常大小整数类型 
2．DOUBLE[(M,D)] [ZEROFILL] 型： 正常大小(双精密)浮点数字类型 
3．DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列 
4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度 
5．BLOB TEXT类型，最大长度为65535(2^16-1)个字符。 
6．VARCHAR型：变长字符串类型

　　

附录：SELECT语句的使用：

SELECT的完整语法为： 

SELECT[ALL|DISTINCT|DISTINCTROW] 
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} 
FROM tableexpression[,…][IN externaldatabase] 
[WHERE…] 
[GROUP BY…] 
[HAVING…] 
[ORDER BY…] 
[WITH OWNERACCESS OPTION] 
说明： 
用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。 
1 ALL、DISTINCT、DISTINCTROW谓词 

(1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。 
例：SELECT ALL FirstName,LastName 
FROM Employees 
(2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。 
(3) DISTINCTROW 如果有重复的记录，只返回一个 

*MySQL中没有TOP这一谓词；MySQL可用limit实现谓词TOP的功能。
 例：返回前5个定货额最大的定单 
SELECT * 
FROM [ Order Details] 
ORDER BY UnitPrice*Quantity*(1-Discount) DESC

LIMIT 5

2. 用 AS 子句为字段取别名 

如果想为返回的列取一个新的标题，或者，经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。 
例：返回FirstName字段取别名为NickName 
SELECT FirstName AS NickName ,LastName ,City 
FROM Employees 
例：返回新的一列显示库存价值 
SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock 
FROM Products

3. FROM子句 

FROM 子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询得到的复合结果。如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。 
例：下列SQL语句返回所有有定单的客户： 
SELECT OrderID,Customer.customerID 
FROM Orders Customers 
WHERE Orders.CustomerID=Customers.CustomeersID

4. WHERE 子句指定查询条件

   　1） 比较运算符 

比较运算符及其含义 
= 等于；> 大于； < 小于 ；>= 大于等于 ；<= 小于等于 ；<> 不等于 ；!> 不大于 ；!< 不小于 

2） 范围（BETWEEN 和 NOT BETWEEN） 
BETWEEN …AND…运算符指定了要搜索的一个闭区间。 

3） 列表（IN ，NOT IN） 
IN 运算符用来匹配列表中的任何一个值。IN子句可以代替用OR子句连接的一连串的条件。 
例：要找出住在 London、Paris或Berlin的所有客户 
SELECT CustomerID, CompanyName, ContactName, City 
FROM Customers 
WHERE City In(‘London’,’ Paris’,’ Berlin’) 

4） 模式匹配(LIKE) 
LIKE运算符检验一个包含字符串数据的字段值是否匹配一指定模式。 
LIKE运算符里使用的通配符 
查询时的通配符只有'_'和'%'。 
_ 任何一个单一的字符 
% 任意数量（包括0个）的字符  

5. 用ORDER BY子句排序结果 

ORDER子句按一个或多个（最多16个）字段排序查询结果，可以是升序（ASC）也可以是降序（DESC），缺省是升序。ORDER子句通常放在SQL语句的最后。 
ORDER子句中定义了多个字段，则按照字段的先后顺序排序。 
例： 
SELECT ProductName,UnitPrice, UnitInStock 
FROM Products 
ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName 
ORDER BY 子句中可以用字段在选择列表中的位置号代替字段名，可以混合字段名和位置号。 
例：下面的语句产生与上列相同的效果。 
SELECT ProductName,UnitPrice, UnitInStock 
FROM Products 
ORDER BY 1 DESC , 2 DESC,3 

6 .运用连接关系实现多表查询 

例：找出同一个城市中供应商和客户的名字 
SELECT Customers.CompanyName, Suppliers.ComPany.Name 
FROM Customers, Suppliers 
WHERE Customers.City=Suppliers.City 

例：找出产品库存量大于同一种产品的定单的数量的产品和定单 
SELECT ProductName,OrderID, UnitInStock, Quantity 
FROM Products, [Order Deails] 
WHERE Product.productID=[Order Details].ProductID 
AND UnitsInStock>Quantity 

7. 分组和总结查询结果 

在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。 
GROUP BY 子句的语法 
SELECT fidldlist FROM table WHERE criteria [GROUP BY groupfieldlist [HAVING groupcriteria]] 
GROUP BY字段中的Null值以备分组但是不能被省略。 
在任何SQL合计函数中不计算Null值。 
GROUP BY子句后最多可以带有十个字段，排序优先级按从左到右的顺序排列。 
例：在‘WA’地区的雇员表中按头衔分组后，找出具有同等头衔的雇员数目大于1人的所有头衔。 
SELECT Title ,Count(Title) as Total FROM Employees WHERE Region = ‘WA’ GROUP BY Title HAVING Count(Title)>1 

聚集函数 意义 
SUM ( ) 求和 
AVG ( ) 平均值 
COUNT ( ) 表达式中记录的数目 
COUNT (* ) 计算记录的数目 
MAX 最大值 
MIN 最小值 
VAR 方差 
STDEV 标准误差 
FIRST 第一个值 
LAST 最后一个值 

 

8. 联合查询 

UNION运算可以把多个查询的结果合并到一个结果集里显示. 
UNION运算的一般语法: 
[表]查询1 UNION [ALL]查询2 UNION … 
例:返回巴西所有供给商和客户的名字和城市 
SELECT CompanyName,City 
FROM Suppliers 
WHERE Country = ‘Brazil’ 
UNION 
SELECT CompanyName,City 
FROM Customers 
WHERE Country = ‘Brazil’ 
注: 
缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项 
UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同. 
每一个查询参数中可以使用GROUP BY 子句 或 HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句. 

9. 交叉查询 

交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部. 
Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法: 
TRANSFORM aggfunction 
SELECT 语句 
GROUP BY 子句 
PIVOT pivotfield[IN(value1 [,value2[,…]]) ] 
Aggfounction指SQL聚积函数, 
SELECT语句选择作为标题的的字段, 
GROUP BY 分组 
说明： 
Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制它的取值. 
value代表创建列标题的固定值. 
例:显示在1996年里每一季度每一位员工所接的定单的数目: 
TRANSFORM Count(OrderID) 
SELECT FirstName.LastName AS FullName 
FROM Employees INNER JOIN Orders 
ON Employees.EmployeeID = Orders.EmployeeID 
WHERE DatePart(“yyyy”,OrderDate)= ‘1996’ 
GROUP BY FirstName
ORDER BY FirstName
POVOT DatePart(“q”,OrderDate)

9 .子查询 

子查询可以理解为 套查询.子查询是一个SELECT语句. 
1) 表达式的值与子查询返回的单一值做比较 
语法: 
表达式 comparision [ANY|ALL|SOME](子查询) 
说明： 
ANY 和SOME谓词是同义词,与比较运算符(=,<,>,<>,<=,>=)一起使用.返回一个布尔值True或 False.ANY的意思是,表达式与子查询返回的一系列的值逐一比较,只要其中的一次比较产生True结果,ANY测试的返回 True值(既WHERE子句的结果),对应于该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的一系列的值的比较都产生 True结果,才回返回True值. 
例:主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品 
SELECT * FROM Products 
WHERE UnitPrice>ANY 
(SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25)

2) 检查表达式的值是否匹配子查询返回的一组值的某个值 
语法: 
[NOT]IN(子查询) 
例:返回库存价值大于等于1000的产品. 
SELECT ProductName FROM Products 
WHERE ProductID IN 
(SELECT PrdoctID FROM [Order DEtails] 
WHERE UnitPrice*Quantity>= 1000)

3) 检测子查询是否返回任何记录 
语法: 
[NOT]EXISTS (子查询) 
例:用EXISTS检索英国的客户 
SELECT ComPanyName,ContactName 
FROM Orders 
WHERE EXISTS 
(SELECT * 
FROM Customers 
WHERE Country = ‘UK’ AND 
Customers.CustomerID= Orders.CustomerID)