MySQL(03)表查询

DDL:对库和表继续操作 　create　drop　alter

DML:对表里的数据操作　insert　delete　update

create table if not exists class　　　　　　　　
(
    class_id int primary key,
    class_name varchar(20) not null unique,
    class_addr varchar(20) not null
)engine innodb default charset utf8;

create table  if not exists course
(
    course_id int primary key,
    course_name varchar(20) not null
)engine innodb default charset utf8;

create table  if not exists student
(
    student_id int primary key,
    student_name varchar(20) not null,
    gender varchar(10) not null,
    birth date default '2000-01-01',
    class_id int not null,
    course_id int not null,
　　　　
　　//外键约束, 
    constraint fk_stuclass foreign key(class_id)
    references class(class_id)
    on delete no action on update cascade,　　//on delete no action　父表主键删除记录时,不会影响子表匹配的记录
　　　　　　　　　　　　　　　　　　　　　　　　　　　//on update cascade　　 父表主键更新时,子表也会同步更新

    constraint fk_strcourse foreign key(course_id)
    references course(course_id)
    on delete cascade on update cascade
)engine innodb default charset utf8;

//插入数据

insert into course values(1,'C'),(2,'C++'),(3,'Go');
insert into class values(1,'一班','一楼'),(2,'二班','二楼'),(3,'三班','三班');
insert into student values(1,'张三','男','1999-01-01',1,3),
　　　　　　　　　　　　　　　　(2,'李四','男','1999-01-02',3,1),
　　　　　　　　　　　　　　　　(3,'百里猪','女','2002-02-07',1,2);

通过上列语句 创建好表和插入数据后进行查询

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

select student_name,gender from student;　　搜索指定字段的记录

where 添加条件 运算符　=　　!=　　>　　<　　>=　　<=　等

select * from student where student_id=3;　　查询student_id字段为3的记录, 也可以 select 某个字段

select * from student where student_id between 2 and 3;　　查询 student_id   2~3的记录, 如果是 not between 2 and 3; 表示非2~3的记录

update class set class_addr='三楼' where class_id=3;　　　更新数据

like % 和 _　,%表示匹配任意多个字符,　_ 表示匹配一个字符

select * from course where course_name like '%+';　匹配以 + 字符结尾的任意字段 如果是 +% 表示匹配 +开头的任意字段

select * from course where course_name like 'G_'; 　匹配 G 开头且只有两个字符的任意字段

多条件查询　and(与)　or(或)　not (非)

select * from student where student_id=1 and gender='男';　　查询满足这两个条件的记录

select * from student where student_id=1 or gender='女';　　满足其中一个条件的记录

in

select * from student where student_id in(1,3);　　查询in指定的数据范围的记录

select * from student where student_id is null;　　is查询空值

select distinct class_id from student;　　　　　　去重复查询,  去掉class_id重复的值只显示一个,  只能修饰一个字段用作统计

分页  limit

select * from student limit 2;　　查询表中前两条记录

select * from student limit 1,3;　查询从第二条开始直到第三条的记录

合并结果集, 查询其实就是把表里的数映射到临时的结果集里, 两个互不影响

select 'a',100;　　查询结果集里的东西
select 'b',200;　　这两条现在是不能输出到同一结果集里

select 'a',100 union　　这样就可以把两个结果输出到同一结果集了, 需要注意查询的必须是同一字段才能合并
select 'b',200;

select 'a',100 union　　但像这样的重复记录只会显示一个　　select 'a',100 union all　这样就不会省略重复
select 'a',100;　　　　　　　　　　　　　　　　　　　　　  select 'a',100;

结果集插入到表里, 优先查询数据,并把输出到结果集里的数据插入到指定表中, 也可以把整张表的输出结果集都插入到另一张表里(等于复制)

 create table if not exists test
(
    id int primary key,
    name varchar(10)
)engine innodb default charset utf8;


insert into test
    select 100,'a' union
    select 101,'b' UNION
    select 102,'c';
_______________________________________________
 create table if not exists test2
(
    id int primary key,
    name varchar(10)
)engine innodb default charset utf8;

insert into test2 select *from test;

create table test3(select * from test2);　　//创建表并把查询的结果集表 赋值到新表里
desc test3

create table test4(select name from test3);　当然也可以只查询一部分并赋值
desc test4

需要注意的是,这样复制的表都是没有主键的,需要自行创建　　还有一种 select * into newtable from oldtable　这种mysql不支持,sql server支持

取别名

select student_name as 姓名,gender as 性别 from student;　　在查询 结果集里帮字段取别名,不会影响源表

 排序　 select * from student order by class_id; 默认是升序,   select * from student order by class_id desc; 这样是降序

 

create table if not exists salary
(
    grade varchar(20) primary key,
    number int
)engine innodb default charset utf8;

create table if not exists department
(
    department_id int primary key,
    department_name varchar(20),
    salary_grade varchar(20),
    constraint fk_salary foreign key(salary_grade)
    references salary(grade)
    on delete set null on update cascade
)engine innodb default charset utf8;

create table if not exists employee
(
    employee_id int primary key auto_increment,
    employee_name varchar(20),
    enter_date date default '2000-01-01',
    department_id int,
    constraint fk_department foreign key(department_id)
    references department(department_id)
    on delete cascade on update cascade
)engine innodb default charset utf8;


insert into salary
    SELECT 'S',10000 union ALL
    SELECT 'A',8000 union ALL
    SELECT 'B',6000 union ALL
    SELECT 'C',3000;
insert into department
    select 1,'开发部','S' union all
    select 2,'测试部','A' union all
    select 3,'人事部','B' union all
    select 4,'业务部','C';
insert into employee(employee_name,department_id)    自增和带默认值的字段都可以不输入,  因为日期占了位置,所以需要置顶字段否则无法空隔, 要么就连日期一起输入
    select '张三',1 union all
    select '李四',2 union all
    select '王五',2 union all
    select '赵六',4 union all
    select '钱七',1 union all
    select '孙八',3 union all
    select '杨九',2 union all
    select '陆十',3 union all
    select '百里猪',1;

交叉连接, 这种查询会出现笛卡尔积再去筛选,效率低

SELECT department.department_name,employee.employee_name
from department,employee　　　　　　　　　　　　　　　　　　　　　　　　　　
where department.department_id = employee.department_id;

内连接　这种查询会先筛选满足条件再继续两张表的连接, 不会出现笛卡尔积, 提高了效率

SELECT department.department_name,employee.employee_name
from department inner join employee
on department.department_id = employee.department_id;

外连接

　　左外连接: 基于左表显示出 左表查询字段的所有记录　右表只显示和左表匹配的 , 没有匹配的用null代替

insert into department values(5,'财务部','C');　　插入一个财务部,

SELECT department.department_name,employee.employee_name
from department left join employee 
on department.department_id = employee.department_id;

财务部也会通过department_name字段列出, 但没有employee表没有匹配的记录, 所以会用null代替, 内连接会因为没有匹配的记录而不列出财务部

　　右外连接:　和左表相反,关键字  left替换成right

　　全外连接:　full join具有左右两种连接的效果 但 MySQL不支持 但可以 用union把左右外连接连接查询

SELECT department.department_name,employee.employee_name
from department left join employee 
on department.department_id = employee.department_id
union
SELECT department.department_name,employee.employee_name
from department right join employee 
on department.department_id = employee.department_id;

select department.department_name,employee.employee_name,salary.number
from department inner join employee 
on department.department_id = employee.department_id
inner join salary 
on department.salary_grade = salary.grade;

select 表1.字段1 , 表2.字段2 , 表3,字段3

from 表1 inner join 表2

on 表1.主键 = 表2.关联主键的外键

inner join 表3

on 表2.主键=表3.关联主键的外键

多表查询

 

---

 

函数

select count(*) from tablename 对整张表的有效记录进行计数  

select count(*) from tablename where 字段=值  计数符合条件的记录

select count(字段1,字段2) from tablename;　　 对指定字段计数

　　　max(字段)　　　计算字段的最大值

　　　min(字段)　　　 计算字段的最小值

　　　avg(字段)　　　 计算字段的平均值

　　　sum(字段)　　　对字段求和

以上是聚合函数

now() 日期函数

date_format(date,format)　　日期转字符串

select date_format(now(),'%Y-%m-%d-%H:%i:%s'); 显示时间戳

upper(str)/icase(str)　转成大写

lower()/lcase()　　　 转成小写

mid(str,start,lenth)/substring(str,start,lenth) 对字符串提取其中一段

round(float,精度);　不写精度时默认四舍五入取整, 可以设置精度

pow(4,2) 4的平方,  pow(4,0.5)/pow(4,1/2) 4的开方

 

分组

select * from table group by table.字段;  　　显示以字段分好组的记录

select 字段 from table group by table.字段;　只显示指定字段且分好组的记录

一张表很多客户,有些客户有很多订单 , 查询订单总额小于10k的客户:

select customer,sum(price) from orders group by customer having sum(pirce)<10000;

这句语句意思是, 对customer字段分组, 条件是分组完后每组的pirce总和小于10k的记录,  只显示满足条件的记录中的 customer和price字段的总和

having 和 where 一样都是条件关键字, 但where只能用在分组之前, having只能用在分组之后