sql_day2

mysql_02(待补充) 以sql命令示例说明

use myee_2204; ——切换当前数据库
Create table emplyee1(
id int,
name varchar(20),
gender varchar(2),
birthday date,
email varchar(10),
remark varchar(50) ); ----------一般书写注意缩进，封号才代表结束

-- 最基本通用查询标签 * 但在实际开发很少用 多用于条件查询 而且没有权限

select * from emplyee1; 查询emplyee1表中所有记录

alter table emplyee1 rename employee1; 修改emplyee1表名为employee1

select * from employee1; 查询employee1表中全部记录

desc employee1;查看表结构

alter table employee1 add age int; 给表employee1追加字段age int类型

alter table employee1 change name username varchar(20);修改employee1表name字段名为username

show variables like"character"; 查看字符集

SELECT e1.id,e1.username,e1.birthday,e1.email,e1.age

FROM employee1 as e1; 查询部分字段 给表命名别名

create table student (
id int,name varchar(20),age int,sex varchar(2),
address varchar(100),math int,english int);

insert into student(id,name,age,sex,address,math,english)
values(1,‘刘继伟’,21,‘男’,‘金昌’,60,40),(2,‘董雨熙’,22,‘男’,‘金昌’,55,35),
(3,‘李林森’,24,‘男’,‘洛门’,55,35),(4,‘张自余’,23,‘男’,‘张掖’,55,0); 插入全部字段

select * from student where age>=22; 查询student表中所有年龄大于22的人
select /distinct/–去掉重复字段值
address ‘地址’,
name ‘学生姓名’,
age ‘学生年龄’,
(math+english) ‘总分’

from student; -- 查询字段 添加别名 -mysql中 ！= <> 都是表达不等于 and表示&&

select * from student;
select * from student where age between 22 and 23; ——between and 搭配 年龄22到23岁之间
select * from student where age in(21,22); 年龄21岁和22岁的

– 判断是否为空 where xx字段名 is null 或者is not NULL模糊查询 ”%马%” %代表0个或者多个字符 代表一个字符

select * from student where name like “_继%”; 模糊查询 名字里第二个字是继的人

insert into student(id,name,age,sex,address,math,english)
values(6,‘张小可’,20,‘女’,‘天水’,65,60);
select * from student where name like “__”; -–查询名字有三个字的人

select count(id) as ‘学生总数’ from student ; 查询学生总数 计数器（利用count聚焦函数）

select avg(english) ‘英语平均分’ from student;——同上

select math from student;

select * from student where math> ——select 嵌套
(select avg(math) from student); 查询数学成绩大于平均分的学生

select * from student order by english desc,math asc ;– 当有多个排序标准时，首先满足在前的字段排序标准

create table student_1(
id int,
name varchar(20),
chinese int,
english int,
math INT
);

desc student_1;

insert into student_1(id,name,chinese,english,math) VALUES
(1,“行哥”,89,78,90),(2,“潘金莲”,67,53,95),(3,“凤姐”,87,78,77),
(4,“旺财”,88,98,92),(5,“白小黑”,82,84,67),(6,“白小黄”,55,85,45),(7,“范蹦蹦”,75,63,30);

select * from student_1;
select name,english from student_1;
select DISTINCT * from student_1; ——排除重复字段

select name,(chinese+english+math+10) as source from student_1 where source>200;——总分source加10 总分在200以上的

select name,chinese,english,math from student_1 where name=“行哥”;
select name,english from student_1 where english>90;

select a.name,a.source from (select name,(chinese+english+math) as source from student_1) as a WHERE source>200;– a为新表别名

select name,english from student_1 where english between 80 and 90; -- between and 固定搭配
select * from student_1 where math in (89,90,91);-- 同个字段 多个字段值的匹配
select name,english from student_1 where name like “白%”;

select * from student_1 where math>80 and chinese>80;
select name,english,(chinese+math+english) as sourse from student_1 where english>80 or (chinese+math+english)>200;
select name,math from student_1 order by math asc;
select name,math,(chinese+english+math)as source from student_1 order by (chinese+english+math)desc,math desc; desc是降序 asc是升序
select name,(chinese+english+math) as source from student_1 where name like “白%” order by (chinese+english+math) desc ;– order by语句要保证是sql语句的最后一句否则会报错

select * from student_1 having math>avg(math);-- where 后面不能使用聚合函数where执行顺序在聚合函数之前
select * from student_1 having max(english);
select * from student_1 where name like “_金%”;
select avg(english) as source from student_1;
select sum(math) as sum from student_1;
select count(id) as “记录个数” from student_1;

insert into student_2 select * from student_1;
insert into student_3(id,name) select id,name from student_1;蠕虫负责必须在以及存在表中

create table student_4 like student_1; 快速建表 表结构相似