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.删除数据库：

ｄｒｏｐ database payment;

使用 mysqladmin 删除数据库

mysqladmin -u root -p ｄｒｏｐ abc_db

5.选择数据库：

use home_db;

6.数据类型：

数值类型:

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

日期和时间类型：

DATE:TIEM:DATETIME:YEAR:TIMESTAMP:

字符串类型：

ｃｈａｒ: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.删除数据表：

ｄｒｏｐ TABLE t_order;

9.ｉｎｓｅｒｔ 插入:

插入一条数据：

ｉｎｓｅｒｔ INTO t_order(title)VALUES(‘手机‘);

ｉｎｓｅｒｔ 插入多条数据：

ｉｎｓｅｒｔ INTO t_order(title)VALUES(‘笔记本‘),(‘平板电脑‘);

10.ｓｅｌｅｃｔ 查询：

所有字段查询：

ｓｅｌｅｃｔ * FROM t_order;

某些字段查询：

ｓｅｌｅｃｔ t_order.id, t_order.title FROM t_order;

分页查询：

ｓｅｌｅｃｔ t_order.title FROM t_order WHERE id LIMIT 1,10

11.where 条件：

OR,AND的应用：

ｓｅｌｅｃｔ * FROM t_order WHERE title =‘手机‘ OR title=‘笔记本‘;ｓｅｌｅｃｔ * FROM t_order WHERE title =‘手机‘ AND id=2;

12.ｕｐｄａｔｅ 更新：

ｕｐｄａｔｅ t_order SET title=‘苹果手机‘ WHERE id =2;

13.ｄｅｌｅｔｅ 删除：

ｄｅｌｅｔｅ FROM t_order WHERE id=2;

14.like 查询

ｓｅｌｅｃｔ * FROM t_order WHERE title LIKE ‘%手机%‘；ｓｅｌｅｃｔ * FROM t_order WHERE title LIKE ‘_机‘;

14.union 联合查询：

连接两个以上的 ｓｅｌｅｃｔ 语句合并成一个结果集合

ｓｅｌｅｃｔ * FROM t_orderｕｎｉｏｎｓｅｌｅｃｔ * FROM t_user;ｓｅｌｅｃｔ * FROM t_orderｕｎｉｏｎALL ｓｅｌｅｃｔ * FROM t_user;

15.order by 排序：

升序排序：

ｓｅｌｅｃｔ * FROM t_order ORDER BY id ASC;

降序排序：

ｓｅｌｅｃｔ * FROM t_order ORDER BY id DESC;

16.group by 分组：

ｓｅｌｅｃｔ title, count(*) as total FROM t_order GROUP BY title;

17.join 连接：

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

ｓｅｌｅｃｔ * FROM t_order o INNER JOIN t_user u ON o.id =u.id;

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

ｓｅｌｅｃｔ * FROM t_order o LEFT JOIN t_user u ON o.id =u.id;

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

ｓｅｌｅｃｔ * FROM t_order o RIGHT JOIN t_user u ON o.id =u.id;

18.null的处理：

null值的判断：

ｓｅｌｅｃｔ * FROM t_user WHERE name IS NULL;

非null值的判断：

ｓｅｌｅｃｔ * FROM t_user WHERE name IS NOT NULL;

19.REGEXP 正则表达式：

ｓｅｌｅｃｔ * FROM t_order WHERE title REGEXP(‘手机#39;);ｓｅｌｅｃｔ * 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 ｃｈａｒ(10) DEFAULT ‘0‘ ;CHANGE:ALTER TABLE t_user CHANGE `password` `user_password` VARCHAR(10) NOT NULL;

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

修改字段默认值：

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

删除一个字段：

ALTER TABLE t_user ｄｒｏｐ 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 ｒｅｎａｍｅ TO `t_sys_user`;ｒｅｎａｍｅ 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`))

删除索引：

ｄｒｏｐ INDEX nameIndex ON t_user;ALTER TABLE t_product ｄｒｏｐ 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(ｓｅｌｅｃｔ * 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

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

ｉｎｓｅｒｔ INTO `t_order_clone`(id,title) ｓｅｌｅｃｔ * FROM t_order;

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

CREATE TABLE `t_user_clone` AS (ｓｅｌｅｃｔ * FROM t_user);

只复制表结构到新表：

CREATE TABLE `t_user_customer` ｓｅｌｅｃｔ * FROM t_user WHERE 1=2;

25.数据库元数据：

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

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

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.导出数据：

用ｏｕｔｆｉｌｅ导出表的数据：

ｓｅｌｅｃｔ * FROM t_user INTO ｏｕｔｆｉｌｅ ‘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.txtmysqldump -u root -p payments > G:/payments.sql

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

mysqldump -u root -p payments --no-create-info > G:/payments.txtmysqldump -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 的字符数

ｓｅｌｅｃｔ CHAR_LENGTH("abcde") AS `length` //输出 5

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

ｓｅｌｅｃｔ CHARACTER_LENGTH("web"); //3

ｃｏｎｃａｔ(s1,s2...sn)：字符串 s1,s2 等多个字符串合并为一个字符串

ｓｅｌｅｃｔ ｃｏｎｃａｔ("wo-","de-","zuguo") //wo-de-zuguo

CONCAT_WS(x, s1,s2...sn): 同 ｃｏｎｃａｔ(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

ｓｅｌｅｃｔ CONCAT_WS("@","come","on","baby");//come@on@baby

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

ｓｅｌｅｃｔ FIELD("you","see", "you", "late"); //2。索引以1开始而不是0.

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

ｓｅｌｅｃｔ FIND_IN_SET("a","i,am,a,worker"); //3

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

ｓｅｌｅｃｔ FORMAT("1.44",1); //1.4SELECT FORMAT("1.55",1); //1.6

ｉｎｓｅｒｔ(s1,x,len,s2):字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

ｓｅｌｅｃｔ ｉｎｓｅｒｔ("hello word",7,4,"世界");//hello 世界

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

ｓｅｌｅｃｔ LOCATE("a","this is a student");-- 9

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

ｓｅｌｅｃｔ LCASE("ABC");-- abc

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

ｓｅｌｅｃｔ LEFT("this is a apple",3);-- thi

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

ｓｅｌｅｃｔ 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 次ｒｅｐｌａｃｅ(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