mysql常用语句汇总

1.连接数据库：

连接本地数据库：

mysql -u root -p

连接远程数据库：

mysql -h 192.169.22.199 -u root -p

退出数据库：

exit

2.创建数据库：

create database  payment;

使用 mysqladmin 创建数据库

mysqladmin -u root -p create abc_db;

3.显示所有数据库：

show databases;

4.删除数据库：

drop database payment;

使用 mysqladmin 删除数据库

mysqladmin -u root -p drop abc_db

5.选择数据库：

use home_db;

6.数据类型：

数值类型:

TINYINT:
SMALLINT:
MEDIUMINT:
INT(INTEGER):
BIGINT:
FLOAT:
DOUBLE:
DECIMAL:

日期和时间类型：

DATE:
TIEM:
DATETIME:
YEAR:
TIMESTAMP:

字符串类型：

CHAR:
VARCHAR:
TINYBLOB:
TINYTEXT:
BLOB:
TEXT:
MEDIUMBLOB:
MEDIUMTEXT:
LONGBLOB:
LONGTEXT:

7.创建数据表：

CREATE TABLE IF NOT EXISTS `t_order`(
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

8.删除数据表：

DROP TABLE t_order;

9.insert 插入:

插入一条数据：

INSERT INTO t_order(title)VALUES('手机');

INSERT 插入多条数据：

INSERT INTO t_order(title)VALUES('笔记本'),('平板电脑');

10.select 查询：

所有字段查询：

SELECT * FROM t_order;

某些字段查询：

SELECT t_order.id, t_order.title FROM t_order;

分页查询：

SELECT t_order.title FROM t_order WHERE id LIMIT 1,10

11.where 条件：

OR,AND的应用：

SELECT * FROM t_order WHERE title ='手机' OR title='笔记本';


SELECT * FROM t_order WHERE title ='手机' AND id=2;

12.update 更新：

UPDATE t_order SET title='苹果手机' WHERE id =2;

13.delete 删除：

DELETE FROM t_order WHERE id=2;

14.like 查询

SELECT * FROM t_order WHERE title LIKE '%手机%'；

SELECT * FROM t_order WHERE title LIKE '_机';

14.union 联合查询：

连接两个以上的 SELECT 语句合并成一个结果集合

SELECT  *  FROM t_order UNION  SELECT  *  FROM t_user;

SELECT  *  FROM t_order UNION ALL SELECT * FROM t_user;

15.order by 排序：

升序排序：

SELECT * FROM t_order ORDER BY id ASC;

降序排序：

SELECT * FROM t_order ORDER BY id DESC;

16.group by 分组：

SELECT title, count(*) as total FROM t_order GROUP BY title;

17.join 连接：

inner join(内连接) on：
获取两个表中字段匹配关系的记录

SELECT * FROM t_order o INNER JOIN  t_user  u ON o.id =u.id;

left join(左连接)on:
获取左表所有记录，即使右表没有对应匹配的记录

SELECT * FROM t_order o LEFT JOIN  t_user  u ON o.id =u.id;

right join(右连接)on:
获取右表所有记录，即使左表没有对应匹配的记录

SELECT * FROM t_order o RIGHT JOIN  t_user  u ON o.id =u.id;

18.null的处理：

null值的判断：

SELECT * FROM t_user WHERE name IS NULL;

非null值的判断：

SELECT * FROM t_user WHERE name IS NOT NULL;

19.REGEXP 正则表达式：

SELECT * FROM t_order WHERE title REGEXP('手机$');
SELECT * FROM t_order WHERE title REGEXP('^笔');

表达式的操作符：
^：匹配字符串的开始位置
$：匹配字符串的结束位置
.：匹配除 "\n" 之外的任何单个字符
[...]：匹配所包含的任意一个字符
[^...]：匹配未包含的任意字符
w1|w2|w3：匹配 w1 或 w2 或 w3
*:匹配零次或多次
+:匹配一次或多次
{n}:匹配 n 次
{n,m}:最少匹配 n 次且最多匹配 m 次

20.事务：

BEGIN:开启一个事务
COMMIT:事务确认
ROLLBACK:事务回滚
SAVEPOINT:
RELEASE SAVEPOINT:
ROLLBACK TO:
SET:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别:
READ UNCOMMITTED、
READ COMMITTED、
REPEATABLE READ、
 SERIALIZABLE。

21.ALTER 修改命令：
添加一个字段：

ALTER TABLE t_user ADD COLUMN `password` VARCHAR(64) NOT NULL;

修改一个字段：

 MODIFY：
ALTER TABLE t_user MODIFY COLUMN password CHAR(10) DEFAULT '0' ;

CHANGE:
ALTER TABLE t_user CHANGE `password`  `user_password` VARCHAR(10) NOT NULL; 

modify和change都是只能修改相同的类型，不同的类型，如char不能改成integer。change可以修改字段名，modify不能修改字段名。

修改字段默认值：

ALTER TABLE t_user ALTER `user_password` SET DEFAULT '0';

删除一个字段：

ALTER TABLE t_user DROP  password;

修改字段名称：

ALTER TABLE t_user CHANGE `password`  `user_password` VARCHAR(10) NOT NULL; 

指定添加字段的位置：
加字段放在某个字段之后：

ALTER TABLE t_sys_user ADD COLUMN `marker` VARCHAR(20) DEFAULT NULL AFTER `name`;

加字段放在第一位：

ALTER TABLE t_sys_user ADD COLUMN `alt` VARCHAR(20) DEFAULT NULL FIRST;

修改表名：

ALTER TABLE t_user RENAME TO `t_sys_user`;

RENAME TABLE t_sys_user TO t_user;

显示表的所有列的信息：

SHOW COLUMNS FROM t_order;

DESC t_order;

显示表的创建语句：

SHOW CREATE TABLE t_order;

修改表的存储引擎：

ALTER TABLE t_sys_user engine=InnoDB;

22.索引：
添加索引：
添加普通索引：

CREATE INDEX nameIndex ON t_user(`name`);

ALTER TABLE t_user ADD INDEX nameIndex(`name`);

CREATE TABLE `t_product`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`title` VARCHAR(20) NOT NULL,
`description` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY(`id`),
INDEX name_index(`name`)
)

添加唯一索引：

CREATE UNIQUE INDEX nameIndex ON t_user(`name`);

ALTER TABLE t_user ADD UNIQUE INDEX nameIndex(`name`);

CREATE TABLE `t_product`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`title` VARCHAR(20) NOT NULL,
`description` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY(`id`),
UNIQUE name_index(`name`)
)

删除索引：

DROP INDEX nameIndex ON t_user;

ALTER TABLE t_product DROP INDEX name_index;

显示索引：

 SHOW INDEX FROM t_user;

23.临时表：

CREATE TEMPORARY TABLE `t_temporary`(
`id` INTEGER(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)

从旧表中获取数据生成临时表：

CREATE TEMPORARY TABLE t_tem AS(
SELECT * FROM t_order LIMIT 0,10
)

24.复制表：
查看需要复制表的创建语句：

SHOW CREATE TABLE t_order; 

复制创建语句进行新表创建：

CREATE TABLE `t_order_clone` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

往新表填充需要复制表的数据：

INSERT INTO `t_order_clone`(id,title) SELECT * FROM t_order;

直接在创建的时候就从旧表拿数据：

CREATE TABLE `t_user_clone` AS (
SELECT * FROM t_user
);

只复制表结构到新表：

CREATE TABLE `t_user_customer` SELECT * FROM t_user WHERE 1=2;

25.数据库元数据：

SELECT VERSION(); //数据库版本
SELECT DATABASE(); //数据库名称
SELECT USER();//数据库当前登录用户
SHOW STATUS;//数据库的状态
SHOW VARIABLES;//数据库的配置变量

26.序列：
使用AUTO_INCREMENT 来定义序列
创建表的时候添加AUTO_INCREMENT序列

CREATE TABLE `t_user`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)

重置序列的开始值（只能目前最大的值大，不能比目前最大的值的小）：

ALTER TABLE t_user auto_increment=1000;

27.导出数据：

用OUTFILE导出表的数据：

SELECT * FROM t_user INTO OUTFILE 'G:/t_user.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

用mysqldump导出表的SQL格式的数据（包含创建语句和插入数据语句）：

mysqldump -u root -p payments t_user_clone > t_user_bak.txt

mysqldump -u root -p payments > G:/payments.sql

只导出需要插入的数据（不包含创建的语句）：

mysqldump -u root -p payments  --no-create-info > G:/payments.txt

mysqldump -u root -p payments  --no-create-info > G:/payments.sql

28.导入数据：
source 命令导入
先创数据库 payment，再导入：
mysql -u root -p payment < G:/payment.sql

CREATE DATABASE paymentdb;
use  paymentdb;
source G:/payments.sql

29.函数：

字符串函数:

CHAR_LENGTH(s):返回字符串 s 的字符数

SELECT CHAR_LENGTH("abcde") AS `length`  //输出 5

CHARACTER_LENGTH(s) ：返回字符串 s 的字符数

SELECT CHARACTER_LENGTH("web");  //3

CONCAT(s1,s2…sn)：字符串 s1,s2 等多个字符串合并为一个字符串

SELECT CONCAT("wo-","de-","zuguo") //wo-de-zuguo

CONCAT_WS(x, s1,s2…sn): 同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

SELECT CONCAT_WS("@","come","on","baby");
//come@on@baby

FIELD(s,s1,s2…) ：返回第一个字符串 s 在字符串列表(s1,s2…)中的位置

SELECT FIELD("you","see", "you", "late"); //2。索引以1开始而不是0.

FIND_IN_SET(s1,s2)：返回在字符串s2中与s1匹配的字符串的位置

SELECT FIND_IN_SET("a","i,am,a,worker"); //3

FORMAT(x,n)：函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。

SELECT  FORMAT("1.44",1);  //1.4
SELECT  FORMAT("1.55",1); //1.6

INSERT(s1,x,len,s2):字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

SELECT INSERT("hello word",7,4,"世界");//hello 世界

LOCATE(s1,s):从字符串 s 中获取 s1 的开始位置

SELECT LOCATE("a","this is a student");-- 9

LCASE(s):将字符串 s 的所有字母变成小写字母

SELECT LCASE("ABC");-- abc

LEFT(s,n):返回字符串 s 的前 n 个字符

SELECT LEFT("this is a apple",3);-- thi

LOWER(s):将字符串 s 的所有字母变成小写字母

SELECT LOWER('ABC') -- abc

LPAD(s1,len,s2):在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

LTRIM(s) :去掉字符串 s 开始处的空格

MID(s,n,len)：从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)

POSITION(s1 IN s)：从字符串 s 中获取 s1 的开始位置

REPEAT(s,n)：将字符串 s 重复 n 次
REPLACE(s,s1,s2)：将字符串 s2 替代字符串 s 中的字符串 s1

REVERSE(s)：将字符串s的顺序反过来

RIGHT(s,n)：返回字符串 s 的后 n 个字符

RPAD(s1,len,s2)：在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

RTRIM(s)：去掉字符串 s 结尾处的空格

SPACE(n)：返回 n 个空格

STRCMP(s1,s2)：比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

SUBSTR(s, start, length)：从字符串 s 的 start 位置截取长度为 length 的子字符串

SUBSTRING(s, start, length)：从字符串 s 的 start 位置截取长度为 length 的子字符串

SUBSTRING_INDEX(s, delimiter, number) ：返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。

TRIM(s)：去掉字符串 s 开始和结尾处的空格

UCASE(s)：将字符串转换为大写

UPPER(s)：将字符串转换为大写

30.总条数：

SELECT COUNT("*") as `total` FROM t_order;

31.求和：

SELECT SUM(saled) as sum FROM t_order;

32.求平均值：

SELECT AVG(saled) AS avg FROM t_order;

33.求最大值：

SELECT MAX(saled) AS maxs FROM t_order;

34.求最小值：

SELECT MIN(saled) AS mins FROM t_order;