sql语法

sql语法

1.sql基础

SQL分类：

• 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
• 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
• 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
• 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

2.sql语法

1.查询

SELECT 
selection_list /*要查询的列名称*/
FROM 
table_list /*要查询的表名称*/
[JOIN /*要连接的表*/  ON /* 连接条件*/]
WHERE 
condition /*行条件*/
GROUP BY 
grouping_columns /*对结果分组*/
HAVING 
condition /*分组后的行条件*/
ORDER BY
sorting_columns /*对结果分组*/
LIMIT 
offset_start, row_count /*结果限定*/

2.数据操作

1.插入

语法1.：

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stus(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');
INSERT INTO stus(sid, sname) VALUES('s_1001', 'zhangSan');

语法2.：

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stus VALUES('s_1002', 'liSi', 32, 'female');

注意：所有字符串数据必须使用单引号

2.修改

单表修改语法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stus SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stus SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;
UPDATE stus SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;
UPDATE stus SET gender=’female’ WHERE gender IS NULL
UPDATE stus SET age=age+1 WHERE sname=’zhaoLiu’;

多表修改语法：

UPDATE 表1 【inner】 john 表2 on 表 SET 列名1= 新值1,列名2 =新值2
【where 筛选条件】

3.删除

单表删除语法：

①DELETE FROM 表名 [WHERE 条件]

DELETE FROM stus WHERE sid=’s_1001’003B
DELETE FROM stus WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stus;

②语法：

TRUNCATE TABLE 表名

TRUNCATE TABLE stus;

• 虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！
• TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的.

多表删除语法：

DELETE FROM 表1 别名1 INNER JOIN 表2 别名2 on 连接条件 【AND 筛选条件】

3.表操作

• 创建表：

CREATE TABLE 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
…
);

例如：
CREATE TABLE stus(
	sid	    CHAR(6),
	sname	VARCHAR(20),
	age		INT,
	gender	VARCHAR(10) 
);

再例如：
CREATE TABLE emps(
	eid		CHAR(6),
	ename	VARCHAR(50),
	age		INT,
	gender	VARCHAR(6),
	birthday	DATE,
	hiredate	DATE,
	salary	DECIMAL(7,2),
	resume	VARCHAR(1000)
);

• 查看当前数据库中所有表名称：SHOW TABLES；
• 查看指定表的创建语句：SHOW CREATE TABLE emps;
• 查看表结构：DESC emps;
• 删除表：DROP TABLE emps
• 修改表结构:
• a) 修改之添加列：给stus表添加classname列：
  ALTER TABLE stu ADD (classname varchar(100));
• b) 修改之修改列类型：修改stu表的gender列类型为CHAR(2)：
  ALTER TABLE stus MODIFY gender CHAR(2);
• c) 修改之修改列名：修改stu表的gender列名为sex：
  ALTER TABLE stus change gender sex CHAR(2);
• d) 修改之删除列：删除stsu表的classname列：
  ALTER TABLE stus DROP classname;
• e) 修改之修改表名称：修改stu表名称为student：
  ALTER TABLE stus RENAME TO student;

4.库操作

• 查看所有数据库名称：SHOW DATABASES；

• 切换数据库：USE mydb1，

• 创建数据库：

  • CREATE DATABASE [IF NOT EXISTS] mydb1；
  • create database 数据库名;
  • create database 数据库名 character set 字符集;

• 删除数据库：DROP DATABASE [IF EXISTS] mydb1；

• 查看某个数据库的定义的信息: show create database 数据库名;

• 查看正在使用的数据库:select database();

3.mysqlc常见函数

1.数学函数

ABS（x）	返回x的绝对值
CEIL（x）	返回大于x的最小整数值
FLOOR（x）	返回大于x的最大整数值
MOD(x,y)	返回x/y的模
RAND(x)	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

2.字符串函数

CONCAT(S1,S2,......,Sn)	连接S1,S2,......,Sn为一个字符串
CONCAT(s, S1,S2,......,Sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s)  或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

3.日期和时间函数

CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW()
SYSDATE()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()	返回当前系统日期时间

YEAR(date)
MONTH(date)
DAY(date)
HOUR(time)
MINUTE(time)
SECOND(time)	返回具体的时间值

WEEK(date)
WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2)
TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔
返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE  expr  type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

（1）DATE_ADD(datetime,INTERVAL expr type)

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号
表达式类型	YEAR_MONTH
YEAR	DAY_HOUR
MONTH	DAY_MINUTE
DAY	DAY_SECOND
HOUR	HOUR_MINUTE
MINUTE	HOUR_SECOND
SECOND	MINUTE_SECOND

（2）DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）		
%H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)		
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天		
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

4.流程函数

IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE 
WHEN 条件1 THEN result1
WHEN 条件2 THEN result2
....
[ELSE resultn]
END	

相当于Java的if...else if...


CASE  expr 
WHEN 常量值1 THEN 值1
WHEN 常量值1 THEN 值1
....
[ELSE 值n]
END	相当于Java的switch