MYSQL基本語法使用

一、mysql之DML

增加語句

刪除語句和truncate

更新語句

replace語句

select查詢語句

二、select多種用法

查詢時的別名使用

分組

分組后的篩選

結果排序

分頁功能

分表

多表關聯查詢

練習題

一、單表查詢

二、多表查詢

前面已經學習了mysql的安裝和基本語法，接下來我們繼續學習其他的mysql基本語法是如何使用的

一、mysql之DML

增加語句

#語法
insert INTO table_name[(field1 [, field2 ……]) values(value1 [, vaule2 ……])  [,()]];
-- 插入多條數據 --
insert into t_user(id passwd) values(1,"123"),(2,"234"),(3,"345")

示例：

刪除語句和truncate

刪除語言，請注意刪除的條件！！！如果不帶條件，則刪除全表。

delete from table_name [where 條件];
truncate table table_name;

示例：

-- 如果沒有條件，則清空全數據包【慎重！！】
delete from user;

-- truncate清空全表【注意：truncate刪除數據是不經過數據字典】
truncate table students;

更新語句

更新就是修改表中的數據

#語法結構
update table_name set 字段1=新值 [, 字段2=新值, 字段3=字段3 + 1]   [where 條件];

示例：

#其他示例
update user_1 set age=20,password="root" where id=2;
update user_1 set age=age+1;

replace語句

該語句是集更新和插入為一體的語句；

如果表中沒有這條數據，則執行插入，否則執行更新；

??注意：replace的更新，本質是先刪除，再插入

replace into user_1 (id,name) values(10,"test");

select查詢語句

在開發中，查詢語句是使用最多，也是CRUD中，復雜度最高的sql語句

#查詢語法結構
select *|字段1 [, 字段2 ……] from 表名稱 [, 表名稱2……] [where 條件] [group by 字段 [having 分組后的篩選]] [order by 字段 [desc|asc] [字段2 [desc|asc] ……]] [limit 分頁]-- 查詢所有數據
select * from user_1;-- 查詢需要的字段信息
select id,name,password from user_1;-- 查詢一個字段，一個等值條件
select name from user_1 where id=1;#等值查詢select password from user_1 where name="李四";

?select語句中的特殊情況：

對數值型數據列、變量、常量可以使用算數操作符創建表達式（+ - * /）

對日期型數據列、變量、常量可以使用部分算數操作符創建表達式（+ -）

運算符不僅可以在列和常量之間進行運算，也可以在多列之間進行運算。

SELECT last_name, salary, salary*12 FROM employees;

二、select多種用法

查詢時的別名使用

select count(*) from test
select count(*) as 總人數 from test

分組

group by 字段

select gender from test group by gender;
select gender, count(gender) from test group by gender;

分組后的篩選

select gender,count(gender) from test group by gender having count(gender) > 5

結果排序

將查詢結果，以特定的順序展示（升序或者降序）

# 語法結構
order by 字段 
order by 字段 asc|desc;
order by 字段 asc|desc, 字段2 ;

分頁功能

select語句，查詢數據時，可能結果會非常多，此時就不能直接展示，分頁展示。

總數量（all_data）：查詢 select count(*)

每頁展示的數量(page_size)：程序員定

當前頁(cur_page)：默認第一頁，用戶自己點擊選擇

總頁數(all_page)：總數量 % 每頁的數量 == 0 整除后的商：商 + 1

limit num  # 查詢多少條limit num1, num2; # num1: 偏移量, num2 : 每頁的數量  limit cur_page * (page_size - 1), page_size;

分表

數據直接都存儲在一張表中：

如果數據很大，性能會出現問題
將不同的數據，如果放在同一個表中，可能數據冗余
數據冗余，會導致數據可能出錯

將不同的類型，采用不同的數據表進行存儲，如果兩張表或者多張表之間存在關聯關系，則可以采用外鍵來描述這種關聯關系。

主表中，一般是一個字段，改字段一般是從表的主鍵。

create table grade(id int auto_increment,name varchar(50) unique,primary key (id)
);insert into grade(name) value("java精品班"),("python數據分析班"),("網絡安全班"),("云原生高級班");create table student(id int primary key auto_increment,name varchar(50) unique,gender enum("F","M"),age int default 18,address varchar(255),class_id int
);

#插入一些數據
insert into student(name, class_id) values("小明", 1);
insert into student(name, class_id) values("小紅", 2);
insert into student(name, class_id) values("小謝", 1);
insert into student(name, class_id) values("小高", 3);
insert into student(name, class_id) values("小紫", 1);
insert into student(name, class_id) values("小蘭", 4);

-- 此時，插入了一條錯誤數據，該數據描述的外鍵不存在，是一條臟數據！！！
insert into student(name, class_id) values("小黑666", 44);
## 如果要避免出現這種情況，必須加入外鍵約束！

多表關聯查詢

select *|字段 [,……] from 表名稱 [,表名稱] ……-- 交叉連接(cross join)
-- 在查詢多表時，不指定表的關聯關系，數據只能全部匹配
-- 引發笛卡爾積現象
select * from student, grade;-- sql98的標準寫法
select * from student cross join grade;

#內連接
select * from student, grade where student.class_id = grade.id;
select * from student, grade where student.class_id = grade.id and student.name = "小明";

練習題

一、單表查詢

素材： 表名：worker-- 表中字段均為中文，比如 部門號 工資 職工號 參加工作 等
CREATE TABLE `worker` (`部門號` int(11) NOT NULL,`職工號` int(11) NOT NULL,`工作時間` date NOT NULL,`工資` float(8,2) NOT NULL,`政治面貌` varchar(10) NOT NULL DEFAULT '群眾',`姓名` varchar(20) NOT NULL,`出生日期` date NOT NULL,PRIMARY KEY (`職工號`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群眾', '張三', '1990-7-1');
INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '團員', '李四', '1997-2-8');
INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '黨員', '王亮', '1983-6-8');
INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群眾', '趙六', '1994-9-5');
INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '黨員', '錢七', '1992-12-30');
INSERT INTO `worker` (`部門號`, `職工號`, `工作時間`, `工資`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '黨員', '孫八', '1996-9-2');

要求：

1、顯示所有職工的基本信息。
select*from worker;
2、查詢所有職工所屬部門的部門號，不顯示重復的部門號。 ?
select distinct 部門號 from worker;
3、求出所有職工的人數。
select count(*) as 總人數 from worker;
4、列出最高工和最低工資。 
select max(工資),min(工資) from worker;
5、列出職工的平均工資和總工資。
select avg(工資),sum(工資) from worker;
6、創建一個只有職工號、姓名和參加工作的新表，名為工作日期表。 
create table workday(`職工號` int(11) primary key auto_increment,`姓名` varchar(10) unique,`參加工作` varchar(10)
);
7、顯示所有女職工的年齡。 select name, age from worker where gender="女";
8、列出所有姓劉的職工的職工號、姓名和出生日期。
select 職工號,姓名,出生日期 from worker where 姓名 like "劉%";
9、列出1960年以前出生的職工的姓名、參加工作日期。
mysql> select 姓名,工作時間 from worker where 工作時間 <=1960-01-01;
10、列出工資在1000－2000之間的所有職工姓名。 
select 姓名  from worker where 工資 between 1000 and 2000;
11、列出所有陳姓和李姓的職工姓名。
select 姓名 from worker where 姓名 like "陳%" and 姓名 like "李%";
12、列出所有部門號為2和3的職工號、姓名、黨員。
select 職工號, 姓名,政治面貌 from worker where 部門號 between 2 and 3 and 政治面貌 = "黨員";
13、將職工表worker中的職工按出生的先后順序排序。
select*from worker order by 出生日期 asc;
14、顯示工資最高的前3名職工的職工號和姓名。 
select*from worker order by 工資 desc limit 3;
15、求出各部門黨員的人數。 
select 部門號,sum(政治面貌="黨員") as 黨員人數 from worker group by 部門號;
16、統計各部門的工資和平均工資
select 部門號,sum(工資) as 總工資, avg(工資) as 平均工資 from worker group by 部門號;
17、列出總人數大于4的部門號和總人數
select 部門號,count(*) as 總人數 from worker group by 部門號 having count(*) > 4;

二、多表查詢

1.創建student和score表
CREATE ?TABLE student (
id ?INT(10) ?NOT NULL ?UNIQUE ?PRIMARY KEY ,
name ?VARCHAR(20) ?NOT NULL ,
sex ?VARCHAR(4) ,
birth ?YEAR,
department ?VARCHAR(20) ,
address ?VARCHAR(50)
);創建score表。SQL代碼如下：
CREATE ?TABLE score (
id ?INT(10) ?NOT NULL ?UNIQUE ?PRIMARY KEY ?AUTO_INCREMENT ,
stu_id ?INT(10) ?NOT NULL ,
c_name ?VARCHAR(20) ,
grade ?INT(10)
);2.為student表和score表增加記錄
向student表插入記錄的INSERT語句如下：
INSERT INTO student VALUES( 901,'張老大', '男',1985,'計算機系', '北京市海淀區');
INSERT INTO student VALUES( 902,'張老二', '男',1986,'中文系', '北京市昌平區');
INSERT INTO student VALUES( 903,'張三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英語系', '遼寧省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英語系', '福建省廈門市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'計算機系', '湖南省衡陽市');向score表插入記錄的INSERT語句如下：
INSERT INTO score VALUES(NULL,901, '計算機',98);
INSERT INTO score VALUES(NULL,901, '英語', 80);
INSERT INTO score VALUES(NULL,902, '計算機',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '計算機',70);
INSERT INTO score VALUES(NULL,904, '英語',92);
INSERT INTO score VALUES(NULL,905, '英語',94);
INSERT INTO score VALUES(NULL,906, '計算機',90);
INSERT INTO score VALUES(NULL,906, '英語',85);

要求：

3.查詢student表的所有記錄
select*from student;
4.查詢student表的第2條到4條記錄
select*from student limit 1,4;
5.從student表查詢所有學生的學號（id）、姓名（name）和院系（department）的信息
mysql> select id,name,department from student;
6.從student表中查詢計算機系和英語系的學生的信息
select*from student where department ="計算機系" or department="英語系";
7.從student表中查詢年齡18~22歲的學生信息
select*from student where age between 18 and 22;
8.從student表中查詢每個院系有多少人
select department,count(department) as 人數 from student group by department; 
9.從score表中查詢每個科目的最高分
select c_name,max(grade) from score group by c_name;
10.查詢李四的考試科目（c_name）和考試成績（grade）
select c_name,grade from student,score where student.id=score.stu_id and student.name="李四";
11.用連接的方式查詢所有學生的信息和考試信息
select*from student,score;
12.計算每個學生的總成績
select student.name as 姓名, sum(score.grade) as 總成績 from student join score on student.id=score.stu_id group by student.id,student.name;
13.計算每個考試科目的平均成績
select c_name ,avg(grade) from score group by c_name;
14.查詢計算機成績低于95的學生信息
select*from student join score on (student.id=score.stu_id) where grade <95 and c_name="計算機";
15.查詢同時參加計算機和英語考試的學生的信息
select*from student join score on (student.id=score.stu_id) where c_name="計算機" or c_name="英語" and ;
16.將計算機考試成績按從高到低進行排序
select grade as 計算機成績 from score where c_name="計算機" order by grade desc ;
17.從student表和score表中查詢出學生的學號，然后合并查詢結果
select id as 學號 from student union select stu_id as 學號 from score order by 學號;
18.查詢姓張或者姓王的同學的姓名、院系和考試科目及成績
select name,department,c_name,grade from student join score on (student.id=score.stu_id) where student.name like "張老六" or student.name like "王%";
19.查詢都是湖南的學生的姓名、年齡、院系和考試科目及成績
select name,(YEAR(CURDATE()) - birth) as 年齡,department,c_name as 考試科目,grade as 成績 from student join score on(student.id=score.stu_id) and address like "湖南%";