编译器:navicat (编译器有的不用分号和引号,还是建议按cmd的规则来都加上)
1.查看所有数据库
SHOW DATABASES;
2.创建名为test 的数据库
CREATE DATABASE test;
![](https://i-blog.csdnimg.cn/blog_migrate/04250a75339fbac84402611e33db7576.png)
3.创建名为test1的数据库
CREATE DATABASE test1;
4.删除数据库test1
DROP DATABASE test1;
5.使用数据库test
USE test;
6.在test里创建一个名为S1_info的数据表
CREATE TABLE S1_info (
S1_ID INT,
S1_NAME VARCHAR(20),
S1_AGE INT,
S1_SEX INT,
S1_ENTER_DATE DATE
)
![](https://i-blog.csdnimg.cn/blog_migrate/3a64a76fed0d57720a0e379d2b3c19c6.png)
![](https://i-blog.csdnimg.cn/blog_migrate/080f4972456b6566baf1ea028c186798.png)
7.在test创建一个名为S2_detail的数据表
CREATE TABLE S2_detail(
S2_ID INT,
S2_APAT VARCHAR(20),
S2_POSITION VARCHAR(20),
S2_SALARY INT
)
8.删除数据表
DROP TABLE 表名;
9.插入数据-分别给2个table加入数据
(前面有个小错误,先修改属性)
DESC S1_info #查询表结构数据类型
ALTER TABLE S1_info MODIFY S1_SEX VARCHAR(20) #修改字段属性
插入数据:
INSERT INTO S1_info
VALUES
(1001,"QQ",24,'MAN','2022-03-01'),
(1002,"WW",30,'FEMALE','2022-03-10'),
(1003,"EE",45,'FEMALE','2021-10-10'),
(1004,"RR",45,'MAN','2020-02-10')
INSERT INTO S2_detail
VALUES
(1001,"data",'manager',12000),
(1002,"market",'CEO',30000),
(1003,"HR",'normal',6000),
(1004,'market','normal',8000)
10.查询数据
10.1 查询单表全部数据
SELECT * FROM S1_info
![](https://i-blog.csdnimg.cn/blog_migrate/6934105c39e07dfb9c72d14ca2a3c9d3.png)
10.2 查询单表部分数据
SELECT S1_NAME,S1_AGE FROM S1_info;
![](https://i-blog.csdnimg.cn/blog_migrate/a972900dad304bb3eb753fca94b5aa4c.png)
10.3 条件查询
SELECT * FROM S1_info WHERE S1_AGE <35
10.4 排序(desc降序,默认为升序)
SELECT * FROM S1_info ORDER BY S1_AGE DESC
SELECT * FROM S1_info ORDER BY S1_AGE
![](https://i-blog.csdnimg.cn/blog_migrate/e3bec4f75ec517f045d28ecf46f08d63.png)
10.5 分页显示,显示前2条,
SELECT * FROM S1_info LIMIT 2
#跳过1条,选取2条
SELECT * FROM S1_info LIMIT 1,2
![](https://i-blog.csdnimg.cn/blog_migrate/776f83d3976bf9db140b271ff4508a59.png)
10.6 聚合函数
SELECT sum(S2_SALARY),count(S2_APAT) SS_SUM FROM S2_detail
10.7 分组-按照部分分组查看每个部门的人数和部分总薪水
SELECT S2_APAT,COUNT(S2_APAT), SUM(S2_SALARY) SUM_SALA
FROM S2_detail
GROUP BY S2_APAT
![](https://i-blog.csdnimg.cn/blog_migrate/21725e1e1aabbd0254edb9a1121babf1.png)
10.8 多表连接查询
为了2个表有区分,在第一张表加了一行数据
INSERT INTO S1_info VALUES (100,"TT",24,'MAN','2022-03-01')
SELECT *
FROM S1_info
INNER JOIN S2_detail #内连接
#LEFT JOIN S2_detail #左连接
#RIGHT JOIN S2_detail #右连接
ON S1_ID=S2_ID
内连接和右连接:
左连接:
10.9 多表连接——查询多表 部分数据 并进行排序
SELECT S1_ID,S1_NAME,S1_AGE,S2_SALARY
FROM S1_info
LEFT JOIN S2_detail
ON S1_ID=S2_ID
WHERE S2_SALARY>6000
ORDER BY S2_SALARY
![](https://i-blog.csdnimg.cn/blog_migrate/000f9ad5d749fad54ace9230f414b554.png)
11.更新数据
UPDATE S2_detail SET S2_SALARY='7000' WHERE S2_APAT="hr"
![](https://i-blog.csdnimg.cn/blog_migrate/b09ff9dd2d3e2593947cab606dfb87c3.png)
12.删除数据
DELETE FROM S1_info WHERE S1_ID=1001
![](https://i-blog.csdnimg.cn/blog_migrate/297218f193b301e4f0ba5eaea62b1ac9.png)
13.使用like语句模糊查询(LIKE语句必须和where搭配)
#%s 查询以s结尾的数据,
#s%查询以s开头的数据,
#%s%查询中间包含s的数据
SELECT s1_name,s1_enter_date FROM S1_info where s1_enter_date LIKE '%3%'
![](https://i-blog.csdnimg.cn/blog_migrate/6c0b7e96085e38ea9adb24404a03f9b7.png)
14.ALTER 删除,添加,修改字段
#14.1 删除列
ALTER TABLE S1_info DROP S1_ENTER_DATE
#14.2 添加列
ALTER TABLE S1_info ADD enter_date int
#14.3 修改字段类型
ALTER TABLE S1_info enter_date VARCHAR(10)
#14.4 修改字段名
ALTER TABLE S1_info CHANGE enter_date hobby VARCHAR(10)
14.1
14.2
14.3 之前演示过
14.4
其他:
1. ps:新增列内容的填充,用update table set columnname="content" where condition
2. 本次建表字段名有点问题,导致2个表没有重复的字段名,不利于其他的语法的展示
回顾一下语法