MySQL基本操作整理

一、关于表的操作

1. 建表

 CREATE TABLE 表名(列名 数据类型 是否可以为NULL值,
                                 列名 数据类型 是否可以为NULL值,
                                 列名 数据类型 是否可以为NULL值
                                 PRIMARY KEY(列名，列名)
                                 )；

CREATE TABLE 'student'('id' int(11) NOT NULL,
                      'name' varchar(40) NOT NULL,
                       PRIMARY KEY('id')
                      );
或者
CREATE TABLE 'student'('id' int(11) NOT NULL PRIMARY KEY,
                      'name' varchar(40) NOT NULL
                     );

2. 利用查询结果建表

CREATE  TABLE 表名1 AS SELECT 列名，列名，... FROM 表名2 WHERE 条件；

CREATE TABLE 'student_new' AS SELECT id,name FROM student WHERE id>=10;

3. 更新表

增加列 ：ALTER TABLE 表名 ADD COLUMN 列名 数据类型 是否可以为NULL值；

删除列：ALTER TABLE 表名 DROP COLUMN 列名；

ALTER TABLE 'student' ADD COLUMN 'sex' varchar(4) NOT NULL;
ALTER TABLE 'student' DROP COLUMN 'sex';

4. 重命名表

RENAME TABLE 旧表名 TO 新表名；

RENAME TABLE student TO stu;

5. 删除表

DROP TABLE 表名；

DROP TABLE stu;

二、关于数据的操作

1. 增

INSERT INTO 表名(列名，列名，……)
                VALUES (值，值，……)，
                                 (值，值，……)；

INSERT INTO stu1(id,name,sex)
       VALUES (1,'aa','f'),
              (2,'bb','m');

2. 将查询结果插入表

INSERT INTO 表名(列名，列名，……) 
                SELECT 列名，列名，……  FROM 表名 WHERE 条件；

INSERT INTO stu1(id,name) 
       SELECT id,name FROM student WHERE id<20;

3. 删

DELETE FROM 表名 WHERE 条件；

DELETE FROM student WHERE id>25;

4. 查

SELECT 列名，列名，……
FROM 表名 JOIN 表名 ON 条件
WHERE 条件 AND 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名
LIMIT m，n；

5. 改

UPDATE 表名 SET 列名=值，列名=值，……
                           WHERE 条件；

UPDATE sutdent SET name = 'dd'
               WHERE id = 10;

三、常用函数

1. 日期时间函数

1.1 获取

NOW( )=CURRENT_TIMESTAMP( )  ——获得系统当前日期和时间 ‘2018-11-13 14:13:05’；

CURDATE( ) ——获得系统当前日期‘2018-11-13’；

CURTIME( ) ——获得系统当前时间‘14:13:05’；

DATE(‘2018-11-13 14:13:05’) ——返回日期部分‘2018-11-13’；

TIME(‘2018-11-13 14:13:05’) ——返回时间部分‘14:13:05’；

YEAR(‘2018-11-13 14:13:05’) ——返回年‘2018’；

MONTH(‘2018-11-13 14:13:05’) ——返回月‘11’；

DAY(‘2018-11-13 14:13:05’) ——返回日‘13’；

HOUR(‘2018-11-13 14:13:05’) ——返回时‘14’；

MINUTE(‘2018-11-13 14:13:05’) ——返回分‘13’；

SECOND(‘2018-11-13 14:13:05’) ——返回秒‘05’；

EXTRACT(YEAR_MONTH FROM ‘2018-11-13 14:13:05’) ——返回日期的一部分，年月‘2018-11’；

DAYOFYEAR(‘2018-11-13') ——判断2018-11-13是一年里的第几天；

DAYOFMONTH(‘2018-11-13') ——判断2018-11-13是一月里的第几天；

DAYOFWEEK(‘2018-11-13') ——判断2018-11-13是一周里的第几天；

DAYNAME(‘2018-11-13') ——返回所属星期名称‘Tuesday’；

MONTHNAME(‘2018-11-13') ——返回所属月份名称‘November’；

1.2 转换

STR_TO_DATE(‘11.13. 2018 14:13:05’, '%m.%d.%Y %H:%m:%s') ——返回标准形式‘2018-11-13 14:13:05’；

DATE_FORMAT(‘2018-11-13 14:13:05’, '%Y/%m/%d %H/%i/%s') ——按format形式返回日期‘2018/11/13 14/13/05’；

1.3 计算

DATEDIFF(date1, date2) ——返回两个日期相差的天数date1-date2；

TIMEDIFF(date1, date2)——返回两个日期相差的时间date1-date2；

DATE_ADD(‘2018-11-13', INTERVAL 1 DAY)

= ‘2018-11-13' + INTERAVL 1 DAY

= ADDDATE(‘2018-11-13', INTERVAL 1 DAY) ——返回加上一天的日期 ‘2018-11-14’；

DATE_SUB(‘2018-11-13', INTERVAL 1 MONTH)

= ‘2018-11-13' - INTERAVL 1 MONTH 

= SUBDATE(‘2018-11-13', INTERVAL 1 MONTH) ——返回减去一月的日期 ‘2018-10-13’；

2. 数值处理函数

PI( ) ——返回pi值；

RAND( ) ——返回0-1之间的一个随机数；

COS(X), SIN(X), TAN(X) ——三角函数计算；

ABS(X) ——返回X的绝对值；

EXP(X) ——返回e的X次方；

SQRT(X) —— 返回根号X；

MOD(X,Y)  ——返回X/Y的余；

DIV(X,Y)  ——返回X整除Y的值；

ROUND(X,Y) ——对X保留Y位小数，Y可以取负值；

TRUNCATE(X,Y) ——对X，在小数点后Y位截断；

3. 文本处理函数

3.1 整理

LTRIM(' xyz123 ') ——删除左侧空格‘xyz123 ’；

RTRIM(' xyz123 ') ——删除右侧空格‘ xyz123’；

TRIM(' xyz123 ') ——删除两侧空格‘xyz123’；

3.2 转换

UPPER('abcDEF')——转大写‘ABCDEF’；

LOWER('abcDEF')——转小写‘abcdef’；

3.3 获取

LENGTH('abcdEF') ——返回字符串长度‘6’；

3.4 涉及子串的处理

LEFT('abcdef'，2) ——截取左侧2个长度的子串‘ab’；

RIGHT('abcdef'，2) ——截取右侧2个长度的子串‘ef’；

SUBSTRING('abcdef'，2，3) ——从第2个位置开始截取长度为3的子串‘bcd’；

SUBSTRING('abcdef'，2) ——从第2个位置开始截取到字符串结束‘bcdef’；

LOCATE('cd', 'abcdefcd'，3) ——返回‘cd’在‘abcdef’中第一次出现的位置，从第3位后算起‘7’，如果没有就返回0；

LOCATE('cd', 'abcdefcd') ——返回‘cd’在‘abcdef’中第一次出现的位置‘3’，如果没有就返回0；

CONCAT('123', 'ABC','abc') ——拼接字符串‘123ABCabc’；

RPLACE('12345ABCD', '123', 'abc') ——替换‘abc45ABCD’；

4. 其他函数

COALESCE(X,Y,Z,......) ——返回参数中第一个非空的表达式；

IF(X,Y,Z) ——如果X为真则返回Y，否则返回Z；

IFNULL(X,Y) —— 如果X是NULL值则返回Y；

CASE WHEN x>y THEN x WHEN y>z THEN y ELSE z END；

5. 聚合函数

MAX( )，MIN( )，SUM( )，COUNT( )，AVG( )

四、常用关键字

DISTINCT ——去重

LIMIT ——限制筛选

UNION ——连接查询结果，自动去重

UNION ALL ——连接查询结果，不去重

五、JOIN相关操作