春
新
乐
快

I am poor and inexperienced.

Single
Mysql

【MySql】基本的SQL语句

avatar
Lucky 2023-12-29 328
0 评论

1、创建数据库与表

1.1、创建数据库

CREATE DATABASE practice;

1.2、使用数据库

USE practice;

1.3、创建数据库表

CREATE TABLE emp(
id VARCHAR(32),
full_name VARCHAR(32),
age INT,
salary DECIMAL(10,2),
dept_id VARCHAR(32),
create_time datetime,
PRIMARY KEY (id)
);

1.4、删除数据库表

DROP TABLE emp;

1.5、删除数据库

DROP DATABASE practice;

 

2、聚合查询

先执行

INSERT INTO emp
(id,full_name,age,salary,dept_id,create_time)
VALUE
('1',"张三",22,'8000.00','1','2023-12-29 00:04');

INSERT INTO emp
(id,full_name,age,salary,dept_id,create_time)
VALUES
('2',"小明",23,'8500.00','1','2023-12-29 00:04'),
('3',"小红",23,'8600.00','2','2023-12-29 00:04'),
('4',"李四",24,'8700.00','3','2023-12-29 00:04'),
('5',"小丽",23,'8200.00','1','2023-12-29 00:04');
SELECT * FROM emp;

2.1、聚合函数COUNT

SELECT COUNT(*) FROM emp;

2.2、聚合函数SUM

SELECT SUM(salary) FROM emp;

2.3、聚合函数AVG

SELECT AVG(salary) FROM emp;

2.4、聚合函数MAX

SELECT MAX(salary) FROM emp;

2.5、聚合函数MIN

SELECT MIN(salary) FROM emp;

-- 起别名
SELECT MIN(salary) min_salary FROM emp;

2.6、其他需求例子

-- 统计每个部门工资总和
SELECT dept_id, sum(salary)
FROM emp
GROUP BY dept_id;


-- 统计部门工资总和大于8700
SELECT dept_id, sum(salary)
FROM emp
GROUP BY dept_id
HAVING SUM(salary)>8700

为什么不用where，因为where会执行聚合前，having是聚合后，sum(salary)聚合之后大于8700，因此用having

-- 统计1部门工资总和
-- 统计1部门工资总和
SELECT dept_id, sum(salary)
FROM emp
WHERE dept_id = 1

 

3、排序

3.1、升序

— 按年龄升序

SELECT * FROM emp
ORDER BY age;

SELECT * FROM emp
ORDER BY age ASC;

3.2、降序

— 按年龄降序

SELECT * FROM emp
ORDER BY age DESC;

3.3、其他

-- 年龄降序,工资升序
SELECT * FROM emp
ORDER BY age DESC, salary ASC;

-- 有where时候,排序放后面
SELECT * FROM emp
WHERE dept_id = 1
ORDER BY age DESC, salary ASC;

 

4、修改表的结构

先执行

INSERT INTO emp
(id,full_name,age,salary,dept_id,create_time)
VALUES
('6',"小马",27,'10000.00','3','2023-12-29 00:04'),
('7',"小五",26,'9000.00','2','2023-12-29 00:04'),
('8',"小力",25,'9000.00','2','2023-12-29 00:04'),
('9',"小周",28,'11000.00','2','2023-12-29 00:04'),
('10',"小刘",26,'11000.00','3','2023-12-29 00:04'),
('11',"小找",27,'8000.00','2','2023-12-29 00:04');

执行SELECT * FROM emp 会发现id中不会1-12这样排，原因是因为id是varchar类型，是按第一位排再到第二位排。

4.1、查看表的描述

DESCRIBE emp;

desc emp;

4.2、修改表字段的类型

ALTER TABLE emp MODIFY id int;

4.3、添加表的字段

ALTER TABLE emp
ADD COLUMN email VARCHAR(32);

4.4、删除表的字段

ALTER TABLE emp
DROP email;

 

4.5、其他

-- 查看数据库
show databases;

-- 查看表
show tables;

-- 查看创建语句
show create table emp;

 

5、分页查询

-- 每页五条,第一页
SELECT * FROM emp
ORDER BY id
LIMIT 5 OFFSET 0
-- pageSize pageSize*(num-1)

-- 每页五条,第二页
SELECT * FROM emp
ORDER BY id
LIMIT 5 OFFSET 5
-- pageSize pageSize*(num-1)

-- 每页五条,第三页
SELECT * FROM emp
ORDER BY id
LIMIT 5 OFFSET 10
-- pageSize pageSize*(num-1)

 

6、多表查询

先执行以下语句

CREATE TABLE dept(
id int,
d_name varchar(32),
c_id varchar(32),
PRIMARY KEY (id)
);

INSERT INTO dept(id,d_name,c_id)
VALUES
(1,'研发部','1'),
(2,'销售部','1'),
(3,'人事部','2');
SELECT * FROM emp;
SELECT * FROM dept;

6.1、模拟使用场景

-- 查询两张表的信息,但是都重复了
SELECT * FROM emp,dept;

-- 让他一一对应,让员工表的部门id等于部门表的id
SELECT * FROM emp AS e, dept d
WHERE e.dept_id = d.id;

-- 我想只要员工id 名字 部门id 部门名称即可
SELECT e.id, e.full_name, d.id, d.d_name
FROM emp e, dept d
WHERE e.dept_id = d.id;

-- 发现查出数据两个id重复了,起个别名
SELECT e.id AS e_id, e.full_name, d.id AS d_id, d.d_name
FROM emp AS e, dept AS d
WHERE e.dept_id = d.id;

-- 内连接也可以实现查询(两个表交叉部门)
SELECT e.id AS e_id, e.full_name, d.id AS d_id, d.d_name
FROM emp AS e INNER JOIN dept AS d
ON e.dept_id = d.id

-- 只想查员工1的时候
SELECT e.id AS e_id, e.full_name, d.id AS d_id, d.d_name
FROM emp AS e INNER JOIN dept AS d
ON e.dept_id = d.id
WHERE e.id = 1

7、左连接查询

INSERT INTO emp(id,full_name,age,salary,create_time)
VALUE (13,'LUCKY', '23', '8000', '2023-12-29 12:20');
SELECT * FROM emp ;

-- 然后内连接查询,发现没有LUCKY数据(因为小明不属于交集)
SELECT e.id AS e_id, e.full_name, d.id as d_id, d.d_name
FROM emp AS e INNER JOIN dept AS d
ON e.dept_id = d.id;

-- 然后我想看员工全部信息,部门没有的为空,左连接
SELECT e.id AS e_id, e.full_name, d.id AS d_id, d.d_name
FROM emp AS e LEFT JOIN dept AS d
ON e.dept_id = d.id;

-- 插入一条数据
INSERT INTO dept(id,d_name,c_id)
VALUE (4, '财务部', '0');

-- 右链接查询
SELECT e.id AS e_id, e.full_name, d.id as d_id, d.d_name
FROM emp AS e RIGHT JOIN dept AS d
ON e.dept_id = d.id

8、外键

ALTER TABLE emp MODIFY dept_id int;

-- 设置外键
ALTER TABLE emp
ADD CONSTRAINT fk_dept_id
FOREIGN KEY (dept_id)
REFERENCES dept (id);

-- 删除失败 因为有外键关联着
DELETE FROM dept WHERE id = 1
SELECT * FROM dept

-- 删除外键
ALTER TABLE emp
DROP FOREIGN KEY fk_dept_id
-- 因为性能问题,一般不用外键

 

9、多对多关系

CREATE TABLE student(
id int,
`name` VARCHAR(32),
other VARCHAR(32),
PRIMARY KEY (id)
);

INSERT INTO student(id,`name`,other)
VALUES
(1,'张三',''),
(2,'小明',''),
(3,'李四',''),
(4,'王五',''),
(5,'老六','');

CREATE TABLE teacher(
id int,
`name` VARCHAR(32),
other VARCHAR(32),
PRIMARY KEY (id)
);

INSERT INTO teacher(id,`name`,other) VALUES
(1,'张老师',''),
(2,'王老师',''),
(3,'梁老师',''),
(4,'吴老师','');

CREATE TABLE stu_tea(
id int,
student_id int,
teacher_id int,
PRIMARY KEY (id)
);

INSERT INTO stu_tea(id,student_id,teacher_id) VALUES
(1,1,2),
(2,1,3),
(3,1,4),
(4,2,1),
(5,2,2),
(6,3,1),
(7,3,4),
(8,4,2),
(9,4,3),
(10,5,1),
(11,5,2);
-- 以学生为标准查找老师
SELECT * FROM student s
LEFT JOIN stu_tea st
ON s.id = st.student_id
LEFT JOIN teacher t
ON st.teacher_id = t.id;

-- 然后我门想要学生id,学生名字,老师名字
SELECT s.id,s.name s_name,t.name t_teacher FROM student s
LEFT JOIN stu_tea st
ON s.id = st.student_id
LEFT JOIN teacher t
ON st.teacher_id = t.id;

10、条件查询

10.1、精准查询where

SELECT * FROM emp
SELECT * FROM emp WHERE full_name = '张三';

10.2、模糊查询like

SELECT * FROM emp
WHERE full_name LIKE '张%';

10.3、多条件and与or

SELECT * FROM emp
WHERE full_name = '张三' and age = 22;
SELECT * FROM emp
WHERE dept_id = 1 or dept_id = 2;

10.4、区间between

SELECT * FROM emp
WHERE age >= 22 and age <= 25;
SELECT * FROM emp
WHERE age BETWEEN 22 and 25;

11、索引

添加索引

ALTER TABLE emp
ADD INDEX index_name(full_name)
-- 添加复合索引
ALTER TABLE emp
ADD INDEX index_name(full_name,其他字段)
-- 0/索引能加快查询的效率,用唯一/较少的相同的数据字段
-- 1/索引遵循最左原则,查询不包含最左的字段,索引会失效
-- 2/一个表不是索引越多越好,因为索引多会影响删改,降低效率

12、组件自增和事务

12.1、自增 auto_increment

SELECT * FROM emp;
-- 执行这个会报错
INSERT INTO (full_name, age, salary)
VALUES ('小李', 23, 6000);
-- 了解创建表
CREATE TABLE emp1(
-- NOT ALL 不为空 auto_increment自动增长
id int NOT NULL auto_increment,
-- NOT ALL 不为空
full_name VARCHAR(32) NOT NULL,
-- 设置提示字段 COMMIT '年龄'
age INT COMMIT '年龄',
salary decimal(10, 2),
-- default 1 是 设置默认值为 1
dept_id int default 1,
create_time datetime,
PRIMARY KEY (id)
);

-- 修改字段id自增
ALTER TABLE emp MODIFY id int auto_increment;
-- 再次执行会成功
INSERT INTO emp(full_name, age, salary)
VALUES ('小李', 23, 6000);

12.2、事务

多条sql语句作为整体执行，保证全部执行成功，或者全部失败。

例子：张三给小红转正100元，假如张三减少一百元执行成功，单小红增加100元失败的时候，这样张三的100元会凭空消失。这样不行的！！！

这就需要事务的回滚

SELECT * FROM emp;
-- 开启事务
BEGIN;
UPDATE emp set salary=salary+100 where id=2;
UPDATE emp set salary=salary-100 WHERE id=1;
-- 回滚
ROLLBACK;
-- 开启事务
BEGIN;
UPDATE emp set salary=salary+100 where id=2;
UPDATE emp set salary=salary-100 WHERE id=1;
-- 提交事务
COMMIT;
Mysql SQL 数据库