mysql常用操作

整个操作数据库的流程

连接mysql==>连接database==>对table进行各种操作==>退出

汇总

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

mysql层

连接mysql:

mysql -uUSER_NAME -pPASSWORD

或

mysql -uUSER_NAME -P然后根据提示输入密码

或

mysql mydb -uUSER_NAME -p

或

mysql mydb -uUSER_NAME -pPASSWORD

连接远程mysql
假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令

mysql -h110.110.110.110 -uroot -pabcd123

修改mysql密码

(1)

mysql -u root -pOLD-PASSWORDset password for 'root'@'localhost' = password("NEW-PASSWORD")

(2)

mysqladmin -u root password "NEW-PASSWORD"mysqladmin -u root password OLD-PASSWORD "NEW-PASSWORD"

退出mysql

quit

或

exit

database层

创建数据库

命令:create database <数据库名>
例如:建立一个名为xhkdb的数据库

create database xhkdb;

显示所有的数据库

命令:show databases （注意:最后有个s）

show databases;

删除数据库

命令:drop database <数据库名>
例如:删除名为 xhkdb的数据库

drop database xhkdb;

连接数据库

命令:use <数据库名>
例如:如果xhkdb数据库存在，尝试存取它:

use xhkdb;

屏幕提示:Database changed

查看当前使用的数据库

select database();

当前数据库包含的表信息:

（注意:最后有个s）

show tables; 

table层

对table进行各种操作前,必须先连接某个数据库

字段类型

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型：变长字符串类型

建表

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

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));

CREATE TABLE 表名称 (列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, …. )

CREATE TABLE Persons (Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )

获取表结构

命令:desc 表名，或者show columns from 表名

DESCRIBE MyClassdesc MyClass;show columns from MyClass;

删除表

命令:drop table <表名>
例如:删除表名为 MyClass 的表

drop table MyClass;

在表中增加字段：

命令：alter table 表名 add字段 类型 其他;
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0

alter table MyClass add passtest int(4) default '0'

在tbname表中增加字段：

alter table tbname add column userid int(11) not null primary key auto_increment;

这样，就在表tbname中添加了一个字段userid，类型为int(11)。

更改表名：

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

rename table MyClass to YouClass;

修改表

ALTER TABLE table_name ADD column_name datatype

ALTER TABLE table_name DROP COLUMN column_name

ALTER TABLE table_name ALTER COLUMN column_name datatype

data层

插入数据

INSERT INTO 表名称 VALUES (值1, 值2,….)
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,….)

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

insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

查询表中的数据

SELECT 列名称 FROM 表名称
SELECT DISTINCT 列名称 FROM 表名称
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
SELECT 列名称 FROM 表名称 WHERE (列 运算符 值 OR 列 运算符 值) AND 列 运算符 值
SELECT 列名称A, 列名称B FROM 表名称 ORDER BY 列名称A DESC, 列名称B ASC

查询所有行

命令:select <字段1，字段2，…> from < 表名 > where < 表达式 >
例如:查看表 MyClass 中所有数据

select * from MyClass;

查询前几行数据

例如:查看表 MyClass 中前2行数据

select * from MyClass order by id limit 0,2;

或者:

select * from MyClass limit 0,2;

查询某一列为null或不为null的数据

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

查询不同的记录

SELECT DISTINCT 列名称 FROM 表名称

like

SELECT column_name(s) FROM table_name WHERE column_name LIKE patternSELECT column_name(s) FROM table_name WHERE column_name NOT LIKE pattern

通配符,描述
%,替代一个或多个字符
_,仅替代一个字符
[charlist],字符列中的任何单一字符
[^charlist]或者[!charlist],不在字符列中的任何单一字符

[IN]

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

[BETWEEN]

左闭右开[value1,value2)

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

[AS]

SELECT column_name AS alias_name FROM table_name AS alias_name

[JOIN]

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
INNER JOIN 与 JOIN 是相同的。

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

[LEFT JOIN]

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

[RIGHT JOIN]

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN。

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

[FULL JOIN]

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

[UNION]

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2

[SELECT INTO]

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

SELECT * INTO Persons_backup FROM Persons

SELECT * INTO Persons IN 'Backup.mdb' FROM Persons

SELECT LastName,FirstName INTO Persons_backup FROM Persons

SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P

删除表中数据

DELETE FROM 表名称 WHERE 列名称 = 值

命令：delete from 表名 where 表达式
例如：删除表 MyClass中编号为1 的记录

delete from MyClass where id=1;

清空表中所有记录

delete from MYTABLE;

修改表中数据：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

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

更新字段内容
update 表名 set 字段名 = 新内容
update 表名 set 字段名 = replace(字段名,’旧内容’,’新内容’);
文章前面加入4个空格

update article set content=concat('　　',content); 

约束

NOT NULL 非空

强制列不接受 NULL 值。

CREATE TABLE Persons (Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255))

UNIQUE 唯一

唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

PRIMARY KEY 主键

唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

FOREIGN KEY 外键

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

CHECK

用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

DEFAULT

用于向列中插入默认值。

索引

创建索引

CREATE INDEX index_name ON table_name (column_name);

CREATE UNIQUE INDEX index_name ON table_name (column_name);

删除索引

DROP INDEX index_name ON table_name;

DROP INDEX table_name.index_name;

DROP INDEX index_name;

ALTER TABLE table_name DROP INDEX index_name;

视图

创建视图

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No

函数

合计函数（Aggregate functions）

Aggregate 函数的操作面向一系列的值，并返回一个单一的值。

函数 描述
AVG(column) 返回某列的平均值
COUNT(column) 返回某列的行数（不包括 NULL 值）
COUNT(*) 返回被选行数
FIRST(column) 返回在指定的域中第一个记录的值
LAST(column) 返回在指定的域中最后一个记录的值
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
SUM(column) 返回某列的总和

Scalar 函数

Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。

函数 描述
UCASE(c) 将某个域转换为大写
LCASE(c) 将某个域转换为小写
MID(c,start[,end]) 从某个文本域提取字符
LEN(c) 返回某个文本域的长度
INSTR(c,char) 返回在某个文本域中指定字符的数值位置
LEFT(c,number_of_char) 返回某个被请求的文本域的左侧部分
RIGHT(c,number_of_char) 返回某个被请求的文本域的右侧部分
ROUND(c,decimals) 对某个数值域进行指定小数位数的四舍五入
MOD(x,y) 返回除法操作的余数
NOW() 返回当前的系统日期
FORMAT(c,format) 改变某个域的显示方式
DATEDIFF(d,date1,date2) 用于执行日期计算