目录
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0
DDL:数据定义语言——用来定义数据库对象:创建库、表、列等
DML:数据操作语言——用来操作数据库表中的记录
DQL:数据查询语言——用来查询数据
DCL:数据控制语言——用来定义访问权限和安全级别
数据类型
double:浮点型,double(5,2):最多5位,其中必须有2位小数,即最大值:999.99
char:固定长度字符串类型,char(10) 'abc '
varchar:可变长度字符串类型,varcha(10) 'abc' 只占三个长度
text:字符串类型
blob:二进制类型
date:日期类型,格式:yyyy-MM-dd;
time:时间类型,格式:hh:mm:ss
datetime:日期时间类型,格式:yyyy-MM-dd hh:mm:ss
注:字符串类型、日期类型都要用单引号括起来
DDL:
库
1.登录MySQL mysql -uroot -p 然后输入密码
2.创建数据库 create database 数据库名;
3.显示数据库 show databases;
4.使用某数据库 use 数据库名;
查看表 show tables;
1.创建表
CREATE TABLE student(
id bigint,
stu_name varchar(20),
stu_age int
);
2.修改表结构
a.添加一列
ALTER TABLE student ADD stu_gender TINUINT;
b.修改一个表的字段类型
ALTER TABLE student MODIFY stu_name VARCHAR(30);
c.修改表的列名
ALTER TABLE student CHANGE id stu_id int;
ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;
d.删除一列
ALTER TABLE student DROP stu_name;
3.修改表名
RENAME TABLE student TO newStu;
4.修改表的字符集为gbk
ALTER TABLE student CHARACTER SET 字符集名称;
5.查看表结构
DESC student;
6.查看表的创建细节
SHOW CREATE TABLE student;
7.删除表
DROP TABLE student;
DML:
1、插入操作
a.插入一条语句
INSERT INTO Student(id, stu_name,stu_age) VALUE(1,'李华', 18);
b.插入多条语句,批量插入
INSERT INTO student(id, stu_name, stu_age)
VALUES(1, '张三', 17),
(2, '李四', 18);
c.省略字段插入
INSERT INTO student VALUE(3, '李华', 18);
2、更新操作
UPDATE student SET stu_age = 19 WHERE id = 1;#不加where条件,这一列全更改
UPDATE student SET stu_age = 20,stu_score = 90 WHERE stu_name = '李华';
UPDATE student SET stu_age = stu_age + 1 WHERE stu_name = '李华';
3、删除操作
DELETE FROM student;
DELETE FROM student WHERE id = 1;
TRUNCATE TABLE student;
delete 和 truncate区别:delete 删除表中的数据,表结构还在
truncate 直接把表drop掉,然后再创建一个同样的新表,执行速度比delete快
自动增长时,delete 删除,主键自动增长删除后会在删除之前的基础之上,继续增长
DQL:
1、查询所有列
SELECT * FROM student;
SELECT * FROM student WHERE id = 1;
使用
1)查询性别为男,并且年龄为20的学生记录
SELECT * FROM student WHERE gender = '男' AND age = 20;
2)查询学号为1001或者姓名为李华的记录
SELECT * FROM student WHERE id = 1001 OR stu_name = '李华';
3)查询学号为1001,1002,1003的记录
SELECT * FROM student WHERE id = 1001 OR id = 1002 OR id = 1003;
SELECT * FROM student WHERE id BETWEEN 1001 AND 1003;
SELECT * FROM student WHERE id in(1001,1002,1003);
4)查询年龄为null 不为null 的记录
SELECT * FROM student WHERE stu_age IS NULL;
SELECT * FROM student WHERE stu_age IS NOT NULL;
5)查询年龄在18到20之间的学生记录
SELECT * FROM student WHERE stu_age >= 18 AND stu_age <= 20;
SELECT * FROM student WHERE stu_age BETWEEN 18 AND 20;
2、模糊查询
1)查询姓名由5个字母构成的学生记录
SELECT * FROM student WHERE stu_name like '_____';
2)查询姓名由5个字母构成,并且第五个字母为“s”的学生记录
SELECT * FROM student WHERE stu_name like '____s';
3)查询姓名以“m”开头的学生记录
SELECT * FROM student WHERE stu_name like 'm%';
4)查询姓名中第二个字母为“u”的学生记录
SELECT * FROM student WHERE stu_name like '_u%';
5)查询姓名中包含“s”的学生记录
SELECT * FROM student WHERE stu_name like '%s%';
3、去重查询
SELECT DISTINCT stu_name FROM student;
4、ifnull的使用
SELECT stu_age, stu_score, IFNULL(stu_age, 1) + IFNULL(stu_score, 0) FROM student;
5、对查询的列起个别名—as,as可以省略不写
SELECT IFNULL(stu_age, 1) + IFNULL(stu_score, 0) AS res FROM student;
6、排序 对字段salary排序
SELECT * FROM employee ORDER BY salary; #升序
SELECT * FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC; #降序
SELECT * FROM employee ORDER BY salary DESC, id DESC;
7、聚合函数
COUNT():统计指定列不为NULL的记录行数
1)查询emplyee表中的记录数
SELECT COUNT(*) FROM emplyee;
2)查询员工表中有绩效的人数---performance字段不为空的总数
SELECT COUNT(performance) FROM employee;
3)查询员工表中月薪大于2500的人数
SELECT COUNT(*) FROM employee WHERE salary >= 2500;
4)查询月薪与绩效之和大于5000的人数
SELECT COUNT(*) FROM employee WHERE salary + IFNULL(performance, 0) > 5000;
5)查询有绩效的人数,和有管理费的人数
SELECT COUNT(performance), COUNT(manage) FROM employee;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0
1)查询所有员工月薪和
SELECT SUM(salary) FROM employee;
2)查询所有员工月薪和,以及所有员工绩效和
SELECT SUM(salary), SUM(performance) FROM employee;
3)查询所有员工月薪+绩效和
SELECT SUM(salary + IFNULL(performance, 0)) FROM employee;
SELECT SUM(salary + performance) FROM employee;
4)统计所有员工平均工资
SELECT AVG(salary) FROM employee;
MAX(),MIN()
查询最高工资和最低工资
SELECT MAX(salary), MIN(salary) FROM employee;
8、分组查询
SELECT *, GROUP_CONCAT(salary) FROM employee GROUP BY gender;
group by + 聚合函数:
1)查询每个部门的部门名称和每个部门的工资和
SELECT department, SUM(salary) FROM employee GROUP BY department;
SELECT department, GROUP_CONCAT(name), GROUP_CONCAT(salary), SUM(salary) FROM employee
GROUP BY department;
2)查询每个部门的名称以及每个部门的人数
SELECT department, COUNT(*) FROM employee GROUP BY department;
3)查询每个部门的部门名称以及每个部门工资大于1500的人数
SELECT department, COUNT(*) FROM employee WHERE salary > 1500 GROUP BY department;
group by + having:
查询工资大于2000的,工资总和大于9000的部门名称以及工资和
SELECT department, SUM(salary)
FROM employee
WHERE salary > 2000
GROUP BY department
HAVING SUM(salary) > 9000;
having 和 where 的区别:
1)having 是在分组后对数据进行过滤 ; where 是在分组前对数据进行过滤
2)having 后面可以使用分组函数(统计函数); where 后面不可以使用分组函数
3)where 是对分组前记录的条件,如果某行记录没有满足where子句的条件,那么这行记录不会参与分组,而 having 是对分组后数据的约束
书写格式:select -> from -> where -> Group by -> having -> Order by -> limit
执行顺序:from -> where -> Group by -> having -> select -> Order by -> limit
数据的完整性
1、实体完整性
定义:表中的一行(一行记录)代表一个实体(entity)
作用:标识每一行数据不重复。行级约束
约束类型:主键约束、唯一约束、自动增长
1)主键约束
CREATE TABLE student(id BIGINT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE student(id BIGINT, name VARCHAR(20), PRIMARY KEY(id));
CREATE TABLE student(id BIGINT, num BIGINT, name VARCHAR(20), PRIMARY KEY(id, num));
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(id);
2)唯一约束,可以为空
CREATE TABLE student(id BIGINT UNIQUE, name VARCHAR(20) UNIQUE);
3)自动增长列
CREATE TABLE student(id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) UNIQUE);
2、域完整性
作用:数据类型:数值类型、日期类型、字符串类型 非空约束 默认约束
CREATE TABLE student(
id BIGINT PRIMARY KEY,
age INT NOT NULL DEFAULT 0,
gender VARCHAR(10) NOT NULL DEFAULT '男'
);
3、参照完整性
ALTER TABLE test1 ADD CONSTRAINT fk_num2_num1 FOREIGN KEY(num2) REFERENCES test1(num1);
test1的num2参照test1的num1
4、表之间关系
一对一、一对多、多对多
5、多表操作
合并结果集:把两个select语句的查询结果合并到一起
SELECT * FROM 表1 UNION SELECT * FROM 表2;
UNION---合并时去除重复记录 UNION ALL---合并时不去除重复记录
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
1)内连接
SELECT * FROM employee e
INNER JOIN department d
ON e.dep_id = d.id;
#99查询
SELECT * FROM student st, score sc, category ca
WHERE st.id = sc.sid
AND sc.cid = ca.id;
#内连接查询
SELECT * FROM student st
INNER JOIN score sc
ON st.id = sc.sid
INNER JOIN category ca
ON sc.cid = ca.id;
2)左连接
SELECT * FROM employee e
LEFT JOIN department d
ON e.dep_id = d.id;