分享33个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.序列:

使用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.导出数据:

用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.

FINDINSET(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;