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
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
IN
NOT IN
LIKE
REGEXP 或 RLIKE
IS NULL
IS NOT NULL
逻辑
NOT 或 !
AND
OR
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)];
[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];
[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)
AS
( SELECT column1,column2,...columnN FROM old_table_name)
CREATE TABLE table_name
(id INTEGER PRIMARY KEY)
AS
(SELECT * FROM old_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];
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);
(value1, value2,...valueN),
......
(value1, value2,...valueN);
INSERT INTO table_name VALUES (value1,value2,value3,...valueN),
(value1,value2,value3,...valueN),
......
(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]]
;
[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]]
;
AS
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;
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;
OR
SELECT * FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN];
Like
%
SELECT * FROM table_name WHERE column LIKE 'XXXX%';
_
SELECT * FROM table_name WHERE column LIKE 'XXXX_';
[]
[^]
REGEXP
^
$
.
[...]
[^...]
p1|p2|p3
*
+
{n}
{n,m}
IN
NOT IN
EXISTS
IS
IS NOT
IS NULL
GROUP BY
SELECT * FROM table_name WHERE [conditions]
GROUP BY column1, column2....columnN;
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];
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] ;
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];
LIMIT no_of_rows [OFFSET row_num];
子查询
SELECT column1,... columnN FROM table_name
WHERE condition
(SELECT column1,... columnN FROM table_name [WHERE conditions]);
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];
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 WORK;
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];
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;
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;
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)
对数
LN
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
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
注释 / 删除 ：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
接口
0 条评论
下一页
