|
目录
SQL分类
DCL（Data Control Language）：数据控制语言，用来定义访问权限和事务管理等。
主要包括GRANT、COMMIT、ROLLBACK等语句
一、DDL：操作数据库 1 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名；语句用于创建新的数据库：
SQL> CREATE DATABASE mydb1;
SQL> CREATE DATABASE mydb2 character SET GBK;
SQL> CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
2 查看数据
查看当前数据库服务器中的所有数据库
SQL> SHOW DATABASES;
查看前面创建的mydb2数据库的定义信息
SQL> Show CREATE DATABASE mydb2;
3 修改数据库
查看服务器中的数据库，并把mydb2的字符集修改为utf8;
SQL> ALTER DATABASE mydb2 character SET utf8;
4 删除数据库
DROP DATABASE [IF EXISTS] 数据库名；
SQL> DROP DATABASE mydb3;
5 其他语句
使用（切换）数据库
SQL> USE mydb2;
查看当前使用的数据库（非DDL语句）
SQL> Select database();
二、DDL : 操作数据表 1 创建数据表：
语法:
CREATE TABLE [IF NOT EXISTS] [数据库名.]表名(
字段名1 数据类型 [属性 ] [ 索引] [注释],
字段名2 数据类型 [属性 ] [ 索引] [注释]，
……
字段名n 数据类型 [属性 ] [ 索引] [注释]
);
CREATE TABLE Employees
(
id INT,
age INT,
first VARCHAR(10),
last VARCHAR(10)
);
2 查看数据表：
创建完成数据表后，可以通过SHOW CREATE TABLE查看数据表
SHOW CREATE TABLE table_name;
如果只想查看表中列的相关信息使用DESC 表名：
DESC table_name;
查看数据库中所有表：
SHOW TABLES;
3 修改数据表：
修改表名
ALTER TABLE 原表名 RENAME [TO] 新表名;
ALTER TABLE stu RENAME student;
或者
RENAME TABLE stu TO student;#方言
修改字段名（列）
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;
ALTER TABLE student CHANGE stu_age stu_sex VARCHAR(10);
修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE student MODIFY stu_sex CHAR;
添加字段
ALTER TABLE 表名 ADD 新字段名 数据类型 [FIRST|AFTER 字段名2];
ALTER TABLE student ADD stu_hobby VARCHAR(50);
删除字段
ALTER TABLE 表名 DROP 字段名;
ALTER TABLE student DROP stu_hobby;
修改字段排列位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 [FIRST|AFTER 字段名2];
ALTER TABLE student MODIFY stu_name VARCHAR(50) FIRST;
ALTER TABLE student MODIFY stu_name VARCHAR(50) AFTER stu_sex;
4 删除数据表：
DROP TABLE语句用于删除现有表。
DROP TABLE table_name;
三、DML操作表数据
DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。（重要）
主要包括：INSERT 、UPDATE、 DELETE
1.插入数据
为所有列插入数据
语法：
INSERT INTO 【库名.】表名(字段名1,字段名2,……) VALUES(值1,值2,……);
-- 创建表 --
CREATE TABLE emp(
id INT,
name VARCHAR(10),
gender VARCHAR(10),
birthday DATE,
salary DECIMAL(10,2),
entry_date DATE,
resume_text VARCHAR(200)
);
-- 插入全部数据 --
INSERT INTO emp( -- VALUES中的值一定要和INSERT语句中的列名顺序对应--
id,
name,
gender,
birthday,
salary,
entry_date,
resume_text
) VALUES(
1,
'lilei',
'male',
'1992-05-10', -- 插入的日期和字符一样，都使用引号括起来--
8000,
'2013-06-10',
'none'
);
或者
INSERT INTO emp
VALUES( -- VALUES中值的顺序必须与数据表中字段的顺序对应--
3,
'king',
'female',
'1993-06-15',
9000,
'2014-07-10',
null -- 如果插入空值，请使用null--
);
为指定列插入数据
INSERT INTO emp(
id,
name,
gender,
birthday
) VALUES(
5,
'mary',
'female',
'1995-07-10'
);
批量插入数据
语法:
INSERT INTO 表名[(字段名1,字段名2,……)]
VALUES(值1,值2,……),
(值1，值2,……),
……
(值1，值2,……);
CREATE TABLE teacher(
id INT,
name VARCHAR(50),
age INT
);
INSERT INTO teacher
VALUES (1,'AA',20),
(2,'BB',21);
或者
INSERT INTO teacher(id,name)
VALUES (3,'CC'),
(4,'DD');
2. 更新数据
语法：
UPDATE 表名
SET 字段名1=值1 [,字段名2=值2,……]
[WHERE条件表达式];
将所有员工薪水修改为10000元。
UPDATE emp SET salary=10000
将姓名为‘zs’的员工薪水修改为8000元。
UPDATE emp SET salary=8000 WHERE name='zhangsan';
将姓名为‘lisi’的员工薪水修改为9000元,gender改为‘female’。
UPDATE emp SETsalary=9000,gender='female' WHERE name='lisi';
将所有女性员工的薪水在原有基础上增加1000元。
UPDATE emp SETsalary=salary+1000 WHERE gender='female';
3. 删除数据
使用删除数据
语法：DELETE FROM 表名 [WHERE 条件表达式];
删除表中名称为‘zs’的记录。
DELETE FROM emp WHERE name='zs';
删除表中所有记录。
DELETE FROM emp;
使用TRUNCATE删除数据 （DDL）
语法：TRUNCATE [TABLE] 表名;
删除emp表中所有数据：
TRUNCATE TABLE emp;
DELETE和TRUNCATE区别：
DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句。
DELETE 删除表中的数据，表结构还在；DELETE语句后面可以跟WHERE子句，指定条件从而实现删除部分数据;删除后的数据可以找回。删除数据后主键id不能再使用。
TRUNCATE 语句只能用于删除表中所有的数据；实际是把表直接DROP掉，然后再创建一个同样的新表；主键id会重新计算再次使用；删除的数据不能找回。无法进行事务回滚。执行速度比DELETE快。
四、DQL数据查询
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类（重要）
查询关键字：SELECT
SELECT语句是用于查看计算结果或者查看从数据表中筛选出的数据的。
SELECT语句语法格式：
SELECT
selection_list /*要查询的列名称*/
【 FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*排序*/
LIMIT offset_start, row_count /*结果限定*/ 】
1. 简单查询
查询所有字段
查询stu表中的所有数据：
SELECT * FROM stu;
查询指定字段
查询stu表中所有的sid和sname：
SELECT sid, sname FROM stu;
去除重复记录
去除重复记录（两行或两行以上记录中列的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：
语法格式：SELECT DISTINCT 字段名 FROM 表名;
select distinct did from t_employee;-- 查询员工的部门编号
使用别名
在当前select语句中给某个字段或表达式计算结果，或表等取个临时名称，便于当前select语句的编写和理解。这个临时名称称为别名。
select 字段名1 as "别名1", 字段名2 as "别名2" from 表名称 as 别名;
SELECT eid AS "编号",ename AS "姓名",salary AS "薪 资" FROM t_employee AS emp;
给列起别名时，是可以省略AS关键字的：
SELECT eid "编号",ename "姓名",salary "薪 资" FROM t_employee AS emp;
SELECT ename,salary,salary+1000 new_sal FROM t_employee;
#查询员工的姓名、薪资、奖金比例、实发工资
#实发工资 = 薪资 + 薪资 * 奖金比例
#NULL在mysql中比较和计算都有特殊性，所有的计算遇到的null都是null。
SELECT ename AS 姓名,
salary AS 薪资,
commission_pct AS 奖金比例,
salary + salary * IFNULL(commission_pct,0) AS 实发工资
FROM t_employee;
2. 条件查询与运算符
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
运算符、关键字含义
=
等于,不能用于判断null
安全等于，可以用于null判断
!=
不等于
不等于 有个别数据库不支持“!=”，所以建议使用“”
<
小于
大于
>=
大于等于
AND 或 &&
与，同时满足多个条件
OR 或 ||
或，满足多个条件之一即可
NOT 或 !
非，满足此相反条件
IN(set) 或 NOT IN(set)
判断某个字段是否在指定集合中
IS NULL 或 IS NOT NULL
关键字判断是否为空值
[NOT] LIKE
模糊查询，配合“%”和“_”使用，只针对字符串或日期类型
BETWEEN x AND y
表示区间范围
(1) 查询薪资高于9000的女性员工信息
SELECT * FROM t_employee WHERE salary>9000 AND gender='女';
(2) 查询编号是1或者姓名是'贾宝玉'的员工信息
SELECT * FROM t_employee WHERE eid=1 OR ename='贾宝玉';
(3) 查询编号为1,3,5的员工记录
SELECT * FROM t_employee WHERE eid=1 OR eid=3 OR eid=5;
SELECT * FROM t_employee WHERE eid IN(1,3,5);
(4) 查询编号不是1,3,5的员工记录
SELECT * FROM t_employee WHERE eid NOT IN(1,3,5);
SELECT * FROM t_employee WHERE NOT eid IN(1,3,5);
(5)查询薪资在9000到10000之间的员工记录
SELECT * FROM t_employee WHERE salary >= 9000 AND salary <=10000;
SELECT * FROM t_employee WHERE salary BETWEEN 9000 AND 10000;
(6)查询薪资不在9000到15000之间的员工记录
SELECT * FROM t_employee WHERE salary NOT BETWEEN 9000 AND 15000;
SELECT * FROM t_employee WHERE NOT salary BETWEEN 9000 AND 15000;
(7) 查询佣金比例为null的员工记录
SELECT * FROM t_employee WHERE commission_pct = NULL;#失败
SELECT * FROM t_employee WHERE commission_pct <=> NULL;#安全等于，可用于判断null值
SELECT * FROM t_employee WHERE commission_pct IS NULL;
(8) 查询佣金比例不为null的员工记录
SELECT * FROM t_employee WHERE commission_pct IS NOT NULL;
SELECT * FROM t_employee WHERE NOT commission_pct IS NULL;
3. 模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
通配符:
_ 表示一个字符
% 表示任意0~n个字符
(1) 查询姓名由4个字构成的员工记录
SELECT * FROM t_employee WHERE ename LIKE '____';
-- 其中 “_”匹配任意一个字符，4个“_”表示4个任意字符
(2) 查询名字以’李'开头的员工记录
SELECT * FROM t_employee WHERE ename LIKE '李%';
(3) 查询姓名第三个字是'格'的员工记录
SELECT * FROM t_employee WHERE ename LIKE '__格%';
(4) #查询名字中包含'冰'字的员工记录
SELECT * FROM t_employee WHERE ename LIKE '%冰%';
4. 排序
语法格式:
SELECT 字段名1,字段名2,……
FROM 表名
ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC]……
(1)查询所有员工记录，结果按薪资升序排序
SELECT * FROM t_employee
ORDER BY salary ASC;
#或者
SELECT *
FROM stu
ORDER BY sage; -- 默认ASC升序排列--
(2)查询所有员工记录，按编号降序排序
SELECT *
FROM t_employee
ORDER BY eid DESC;
(3)查询员工的薪资，按照薪资从低到高，薪资相同按照员工编号从高到低
SELECT *
FROM t_employee
ORDER BY salary ASC , eid DESC;
5. 聚合函数
聚合函数是用来做纵向运算的函数：
函数含义
COUNT()
统计指定列不为NULL的记录行数
MAX()
计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
MIN()
计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
SUM()
计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
AVG()
计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
(1)COUNT
当需要纵向统计时可以使用COUNT()。
语法格式：SELECT COUNT(*|1|列名) FROM 表名;
查询员工表中记录总数：
SELECT COUNT(*) AS total FROM t_employee;
查询员工表中有佣金的人数：
SELECT COUNT(commission_pct) total FROM t_employee;
注意：因为count()函数中给出的是commission_pct列，那么只统计commission_pct列非NULL的行数。
查询员工表中月薪大于15000的人数：
SELECT COUNT(*) FROM t_employee WHERE salary > 15000;
统计实发月工资大于15000元的人数：
SELECT COUNT(*) FROM t_employee WHERE salary*(IFNULL(commission_pct,0)+1) > 15000;
(2)SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和：
SELECT SUM(salary) FROM t_employee;-- 不包含佣金
查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(salary), SUM(salary*IFNULL(commission_pct,0)) FROM t_employee;
查询所有雇员月薪+佣金和：
SELECT SUM(salary+salary*IFNULL(commission_pct,0)) FROM t_employee;
统计所有员工平均工资：
SELECT AVG(salary) FROM t_employee;
(3)MAX和MIN
查询最高工资和最低工资：
SELECT MAX(salary), MIN(salary) FROM t_employee;
找出年龄最小、最大的员工的出生日期
SELECT MAX(birthday),MIN(birthday) FROM t_employee;
6. 分组查询
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要按照部门来分组。
注：凡和聚合函数同时出现的列名，则一定要写在group by 之后
分组查询
查询每个部门的部门编号和每个部门的工资和：
SELECT did,SUM(salary) "sum"
FROM t_employee
GROUP BY did;
查询每个部门的部门编号以及每个部门的人数，并按人数倒序排序：
SELECT did,COUNT(*) "cnt"
FROM t_employee
WHERE salary>10000
GROUP BY did ORDER BY cnt DESC;
查询每个部门的部门编号以及每个部门工资大于10000的人数：
SELECT did,COUNT(*)
FROM t_employee
WHERE salary>10000
GROUP BY did;
-- ----------------------
SELECT did,COUNT(*) "count"
FROM t_employee
WHERE salary>10000
GROUP BY did WITH ROLLUP -- 合计，WITH ROLLUP，加在group by后面，用于合计分组统计结果
2. HAVING子句
查询工资总和大于40000的部门编号以及工资和：
SELECT did, SUM(salary)
FROM t_employee
GROUP BY did
HAVING SUM(salary) > 40000;
#或者
SELECT did, SUM(salary) "total"
FROM t_employee
GROUP BY did
HAVING total > 40000;-- 使用别名
查询部门女员工薪资总和大于20000的部门编号及部门女员工工资总和
SELECT did, SUM(salary) "total"
FROM t_employee
WHERE gender='女'
GROUP BY did
HAVING total > 20000 ;
注：HAVING与WHERE的区别:
HAVING是在分组后对数据进行过滤mssql 查询子句，WHERE是在分组前对数据进行过滤；
HAVING后面可以使用分组函数(统计函数)， WHERE后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。
7. LIMIT
LIMIT用来限定查询结果的起始行，以及总行数。
语法格式：SELECT 字段名1,字段名2,……FROM 表名 LIMIT [m,]n;
如上语法格式中，LIMIT后可以跟两个参数，第一个参数m是可选的，代表起始索引，若不指定则使用默认值0，代表第一条记录，第二个参数n是必选的，代表从第m+1条记录开始，取n条记录，
1.查询5行记录，起始行从0开始
SELECT * FROM t_employee LIMIT 0, 5;
或者
SELECT * FROM t_employee LIMIT 5;
注意：起始行从0开始，即第一行开始！
2.从第三条记录开始，查询10行记录
SELECT* FROM t_employee LIMIT 3, 10;
3.分页查询
如果每一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；
如果每一页记录为n条，希望查看第x页记录应该怎么查呢？
LIMIT (x-1)*n, n
查询语句语法顺序：
查询语句书写顺序：SELECT - FROM - WHERE - GROUP BY- HAVING - ORDER BY - LIMIT
查询语句执行顺序：FROM - WHERE -GROUP BY - HAVING - SELECT - ORDER BY - LIMIT
（编辑：通辽站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
