首页  思维导图  详情

MySQL

2020-12-27 12:19:28   12  举报





AI智能生成

MySQL

作者其他创作

大纲/内容

安装

linux

ubuntu

sudo apt-get install mysql-server

centos

下载：https://dev.mysql.com/downloads/repo/yum/ rpm -ivh mysql.noarch.rpm yum update yum install mysql-server chown mysql:mysql -R /var/lib/mysql mysqld --initialize systemctl start mysqld

mac

https://dev.mysql.com/downloads/mysql/

windows

https://dev.mysql.com/downloads/windows/installer/

语法

术语

数据库

数据表

列

行

冗余

主键

外键

复合键

索引

参照完整性

表头

列

行

值

键

数据类型

数值

TINYINT

SMALLINT

MEDIUMINT

INT | INTEGER

BIGINT

FLOAT

DOUBLE

DEC | DECIMAL

字符串

CHAR

VARCHAR

BLOB

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

TEXT

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

日期/时间

YEAR

DATE

TIME

DATETIME

TIMESTAMP

运算符

算术

/ 或 DIV

% 或 MOD

比较

<=>

<> 或 =

BETWEEN

NOT BETWEEN

NOT IN

REGEXP 或 RLIKE

IS NULL

IS NOT NULL

逻辑

NOT 或 !

AND

XOR

位

命令

连接

mysql -u user_name -p

选择库

USE db_name;

查看

SHOW STATUS;

SELECT USER();

SELECT DATABASE();

SHOW DATABASES;

SHOW TABLES;

SHOW TRIGGERS;

SHOW CREATE type;

SHOW COLUMNS FROM table_name;

SHOW INDEX FROM table_name;

SHOW type STATUS [FROM db_name] [LIKE 'pattern'] \G;

SHOW TABLE STATUS FROM my_db;

SHOW TABLE STATUS from my_db LIKE 'my%'\G;

SHOW VARIABLES;

SELECT VERSION();

设置

自动提交

SET AUTOCOMMIT=0;

SET AUTOCOMMIT=1;

退出

exit

quit

SQL

导入

LOAD DATA

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE file_name [REPLACE | IGNORE] INTO TABLE table_name [FIELDS field_options] [LINE line_options] [IGNORE number LINES] [(column1,...columnN)];

SOURCE

SOURCE file_name;

mysql

mysql -u user_name -p db_name < file_name

mysql -u user_name -p db_name | mysql -h input_user_host -P port -u user_name -p db_name

mysqlimport

mysqlimport [options] db_name file_name

导出

SELECT ... INTO OUTFILE

SELECT * FROM table_name WHERE [condition] INTO OUTFILE file_name [FIELDS field_options] [LINE line_options];

mysqldump -u user_name -p  [options] [db_name] [table_name] > file_name

库

创建

CREATE DATABASE [IF NOT EXISTS] db_name [options];

mysqladmin -u user_name -p create db_name

删除

DROP DATABASE [IF EXISTS] db_name;

mysqladmin -u root -p drop db_name

选择

USE db_name;

表

复制

结构

CREATE TABLE table_name LIKE old_table_name;

结构+数据

CREATE TABLE table_name AS ( SELECT column1,column2,...columnN FROM old_table_name)

CREATE TABLE table_name (id INTEGER PRIMARY KEY) AS (SELECT * FROM old_table_name);

创建

CREATE [TEMPORARY] TABLE table_name( column1 datatype [constraint], column2 datatype [constraint], ..... columnN datatype [constraint], PRIMARY KEY( 一个或多个列 ) )[ENGINE=engine_name auto_increment=number DEFAULT CHARSET=charset_name];

自动递增

AUTO_INCREMENT

LAST_INSERT_ID()

删除

表

DROP TABLE table_name;

外键

ALTER TABLE table_name DROP FOREIGN KEY foreign_name;

列

ALTER TABLE table_name DROP column_name;

默认值

ALTER TABLE table_name ALTER column_name DROP DEFAULT;

修改

表

ALTER TABLE table_name [options];

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE table_name ENGINE=engine_name;

ALTER TABLE table_name DROP FOREIGN KEY foreign_name;

ALTER TABLE table_name AUTO_INCREMENT = 100;

索引

添加

ALTER TABLE table_name ADD PRIMARY KEY (column_name...);

ALTER TABLE table_name ADD index_type index_name (column_name,...);

删除

ALTER TABLE table_name DROP INDEX index_name ;

列

添加

ALTER TABLE table_name ADD column_name datatype [FIRST | AFTER column];

删除

ALTER TABLE table_name DROP column_name;

修改

ALTER TABLE table_name MODIFY column_name datatype;

ALTER TABLE table_name CHANGE column_name new_column_name datatype;

默认值

修改

ALTER TABLE table_name ALTER column_name SET DEFAULT default_num;

删除

ALTER TABLE table_name ALTER column_name DROP DEFAULT;

约束

数据

插入

单条

INSERT [IGNORE] INTO table_name (column1, column2,...columnN) VALUES (value1, value2,...valueN);

INSERT [IGNORE] INTO table_name VALUES (value1,value2,value3,...valueN);

多条

INSERT INTO table_name (column1, column2,...columnN) VALUES (value1, value2,...valueN), (value1, value2,...valueN), ...... (value1, value2,...valueN);

INSERT INTO table_name VALUES (value1,value2,value3,...valueN), (value1,value2,value3,...valueN), ...... (value1,value2,value3,...valueN);

删除

DELETE FROM table_name [WHERE conditions];

清空

TRUNCATE TABLE table_name;

修改

UPDATE table_name SET column1=value1, column2=value2...., columnN=valueN [WHERE conditions];

查询

SELECT [Distinct] column1, column2, columnN [AS my_new_column_name] [FROM table_name] [AS my_new_table_name] [WHERE [conditions | expression]] [GROUP BY column1, column2....columnN] [HAVING [conditions]] [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], .. columnN [ASC | DESC]] [LIMIT no_of_rows [OFFSET row_num]] ;

SELECT age AS my_new_name FROM table_name;

SELECT * FROM table_name AS my_new_name;

重复记录

过滤

DISTINCT

SELECT DISTINCT column FROM table_name;

查询

SELECT column1,column2,...columnN count(1) AS count FROM table_name GROUP BY column1,column2...columnN HAVING count>1 AND conditions;

FROM

SELECT column FROM table_name;

INNER JOIN

SELECT * FROM table1 [INNER] JOIN table2 ON condition;

LEFT JOIN

SELECT * FROM table1 LEFT JOIN table2 ON condition;

RIGHT JOIN

SELECT * FROM table1 RIGHT JOIN table2 ON condition;

WHERE

SELECT * FROM table_name WHERE [condition];

BINARY

SELECT * FROM table_name WHERE BINARY [condition];

AND

SELECT * FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];

BETWEEN

SELECT * FROM table_name WHERE age BETWEENT 1 AND 18;

SELECT * FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN];

%

SELECT * FROM table_name WHERE column LIKE 'XXXX%';

SELECT * FROM table_name WHERE column LIKE 'XXXX_';

[]

[^]

REGEXP

[...]

[^...]

p1|p2|p3

{n}

{n,m}

NOT IN

EXISTS

IS NOT

IS NULL

GROUP BY

SELECT * FROM table_name WHERE [conditions] GROUP BY column1, column2....columnN;

SELECT NAME, SUM(SALARY) FROM my_tb GROUP BY name;

WITH ROLLUP

HAVING

SELECT * FROM table_name WHERE [conditions] GROUP BY column1, column2....columnN HAVING [conditions];

SELECT * FROM my_tb GROUP BY name HAVING count(name) < 2;

ORDER BY

SELECT * FROM table_name WHERE [condition] ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], .. columnN [ASC | DESC] ;

LIMIT

SELECT * FROM table_name WHERE [condition] LIMIT no_of_rows [OFFSET row_num];

子查询

SELECT column1,... columnN FROM table_name WHERE condition (SELECT column1,... columnN FROM table_name [WHERE conditions]);

UNION

SELECT column1,... columnN FROM table_name [WHERE conditions] UNION [ALL | DISTINCT] SELECT column1,... columnN FROM table_name [WHERE conditions];

事务

隔离级别

SET [GLOBAL | SESSION] TRANSACTION isolation_level

开始

BEGIN;

START TRANSACTION;

保存点

创建

SAVEPOINT identifier;

删除

RELEASE SAVEPOINT identifier

结束

COMMIT WORK;

COMMIT;

回滚

ROLLBACK

ROLLBACK TO identifier

索引

创建

单列索引

CREATE INDEX index_name ON table_name (column_name);

组合索引

CREATE INDEX index_name ON table_name (column1, column2);

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

删除

DROP INDEX [index_name] ON table_name; ;

查看

SHOW INDEX FROM table_name;

视图

创建

CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

DROP VIEW [IF EXISTS] view_name;

修改

ALTER VIEW view_name AS SELECT ..;

函数

创建

CREATE FUNCTION function_name([arg1 arg1_type,...argN argN_type]) RETURNS arg_type BEGIN ... RETURN value; END;

DROP FUNCTION [IF EXISTS] function_name;

修改

ALTER FUNCTION function_name options;

触发器

创建

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW [trigger_order] BEGIN ... END;

DROP TRIGGER [IF EXISTS] trigger_name;

函数

数字

SUM(expression)

COUNT(expression)

AVG(expression)

RAND()

ABS(x)

SIGN(x)

MOD(x,y)

n DIV m

三角函数

SIN(x)

COS(x)

COT(x)

TAN(x)

反

ASIN(x)

ACOS(x)

ATAN(x)

ATAN2(n, m)

PI()

角/弧度

RADIANS(x)

DEGREES(x)

最大/小值

MAX(expression)

MIN(expression)

GREATEST(expr1, expr2, expr3, ...)

LEAST(expr1, expr2, expr3, ...)

取整

FLOOR(x)

CEIL(x)

CEILING(x)

ROUND(x)

TRUNCATE(x,y)

对数

LOG(x) 或 LOG(base, x)

LOG10(x)

LOG2(x)

SQRT(x)

幂

EXP(x)

POW(x,y)

POWER(x,y)

字符串

ASCII(s)

REPEAT(s,n)

REVERSE(s)

STRCMP(s1,s2)

长度

CHAR_LENGTH(s)

CHARACTER_LENGTH(s)

查找

FIELD(s,s1,s2...)

SELECT FIELD("c", "a", "b", "c", "d", "e");

FIND_IN_SET(s1,s2)

SELECT FIND_IN_SET("c", "a,b,c,d,e");

POSITION(s1 IN s)

LOCATE(s1,s)

REPLACE(s,s1,s2)

截取

LEFT(s,n)

MID(s,n,len)

RIGHT(s,n)

SUBSTR(s, start, length)

SUBSTRING(s, start, length)

SUBSTRING_INDEX(s, delimiter, number)

合并

CONCAT(s1,s2...sn)

CONCAT_WS(x, s1,s2...sn)

插入

INSERT(s1,x,len,s2)

格式化

LPAD(s1,len,s2)

RPAD(s1,len,s2)

FORMAT(x,n)

大小写

UCASE(s)

LCASE(s)

UPPER(s)

LOWER(s)

空格

TRIM(s)

LTRIM(s)

RTRIM(s)

SPACE(n)

时间/日期

EXTRACT(type FROM d)

SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11')

日期和时间

计算

TIMESTAMP(expression, interval)

SELECT TIMESTAMP("2017-07-23", "13:10:11");

SUBTIME(t,n)

SELECT SUBDATE('2011-11-11 11:11:11', 1)

SUBDATE(d,n)

SELECT SUBTIME('2011-11-11 11:11:11', 5);

ADDTIME(t,n)

SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5");

DATE_ADD(d，INTERVAL expr type)

SELECT ADDDATE('2011-11-11 11:11:11',1)

SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE)

DATE_FORMAT(d,f)

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')

SYSDATE()

SELECT SYSDATE();

NOW()

SELECT NOW();

LOCALTIMESTAMP()

SELECT LOCALTIMESTAMP();

LOCALTIME()

SELECT LOCALTIME();

CURRENT_TIMESTAMP()

SELECT CURRENT_TIMESTAMP();

日期

计算

PERIOD_ADD(period, number)

SELECT PERIOD_ADD(201703, 5);

PERIOD_DIFF(period1, period2)

SELECT PERIOD_DIFF(201710, 201703);

ADDDATE(d,n)

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);

DATEDIFF(d1,d2)

SELECT DATEDIFF('2001-01-01','2001-02-02')

STR_TO_DATE(string, format_mask)

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");

LAST_DAY(d)

SELECT LAST_DAY("2017-06-20");

MAKEDATE(year, day-of-year)

SELECT MAKEDATE(2017, 3);

FROM_DAYS(n)

SELECT FROM_DAYS(1111)

DATE_SUB(date,INTERVAL expr type)

SELECT DATE_SUB("2017-06-15", INTERVAL 2 DAY);

CURDATE()

SELECT CURDATE();

CURRENT_DATE()

SELECT CURRENT_DATE();

DATE()

SELECT DATE("2017-06-15");

年

YEAR(d)

SELECT YEAR("2017-06-15");

月

MONTH(d)

SELECT MONTH('2011-11-11 11:11:11')

MONTHNAME(d)

SELECT MONTHNAME('2011-11-11 11:11:11')

日

TO_DAYS(d)

SELECT TO_DAYS('0001-01-01 01:01:01');

DAYOFYEAR(d)

SELECT DAYOFYEAR('2011-11-11 11:11:11')

DAYOFMONTH(d)

SELECT DAYOFMONTH('2011-11-11 11:11:11')

DAY(d)

SELECT DAY("2017-06-15");

DAYNAME(d)

SELECT DAYNAME('2011-11-11 11:11:11')

星期

WEEK(d)

SELECT WEEK('2011-11-11 11:11:11');

WEEKDAY(d)

SELECT WEEKDAY("2017-06-15");

WEEKOFYEAR(d)

SELECT WEEKOFYEAR('2011-11-11 11:11:11');

YEARWEEK(date, mode)

SELECT YEARWEEK("2017-06-15");

DAYOFWEEK(d)

SELECT DAYOFWEEK('2011-11-11 11:11:11')

季

QUARTER(d)

SELECT QUARTER('2011-11-11 11:11:11')

时间

计算

TIMEDIFF(time1, time2)

SELECT TIMEDIFF("13:10:11", "13:10:10");

TIME(expression)

SELECT TIME("19:30:10");

TIME_FORMAT(t,f)

SELECT TIME_FORMAT('11:11:11','%r')

SEC_TO_TIME(s)

SELECT SEC_TO_TIME(4320);

MAKETIME(hour, minute, second)

SELECT MAKETIME(11, 35, 4);

CURRENT_TIME

SELECT CURRENT_TIME();

CURTIME()

SELECT CURTIME();

时

HOUR(t)

SELECT HOUR('1:2:3');

分

MINUTE(t)

SELECT MINUTE('1:2:3');

秒

TIME_TO_SEC(t)

SELECT TIME_TO_SEC('1:12:00');

SECOND(t)

SELECT SECOND('1:2:3');

微秒

MICROSECOND(date)

SELECT MICROSECOND("2017-06-20 09:34:00.000023");

其他

LAST_INSERT_ID()

COALESCE(expr1, expr2, ...., expr_n)

CASE

CASE expression WHEN condition1 THEN result1 ... WHEN conditionN THEN resultN ELSE result END

SELECT CASE 　　WHEN 1 > 0 　　THEN '1 > 0' 　　WHEN 2 > 0 　　THEN '2 > 0' 　　ELSE '3 > 0' 　　END

类型转换

CAST(x AS type)

SELECT CAST("2017-08-29" AS DATE);

进制

BIN(x)

SELECT BIN(15);

BINARY(s)

SELECT BINARY "RUNOOB";

CONV(x,f1,f2)

字符集

CHARSET(s)

SELECT CHARSET('ABC')

CONVERT(s USING cs)

SELECT CHARSET(CONVERT('ABC' USING gbk))

逻辑

IF(expr,v1,v2)

NULLIF(expr1, expr2)

ISNULL(expression)

IFNULL(v1,v2)

数据库

用户

SESSION_USER()

SYSTEM_USER()

USER()

CURRENT_USER()

CONNECTION_ID()

DATABASE()

VERSION()

配置文件

路径

linux

ubuntu

/etc/mysql/my.cnf

centos

mac

windows

文件

远程连接

vim ./mysqld.cnf 注释 / 删除：bind-address = 127.0.0.1 service mysql restart mysql -u root -p USE mysql; GRANT ALL PRIVILEGES ON *.* TO 'root'@'@' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; exit

接口