MySQL中DML和DQL

DML语句

   1. 插入单条数据记录  

insert into 表名[字段名列表]values(列表值)；

列：insert into
student(loginpwd,studentname,grade,phone,borndata)
values(123,黄小平,1,139587456,1995-02-03);

    插入多条数据记录

insert into subject(subjectname,classhours,gradeid)
values(logic java,220,1),(html,160,1),(java oop,230,2);

2.将查询结果插入新表

    把学生表中姓名、手机号两列数据存储到通讯录表中

    

create table 新表（select 字段1，字段2.....from 原表 ）

create table phone_list(
   select studentname ,phone from student
);

3.更新数据记录

updata 表名
set 字段1=值1，字段2=值2，...字段n=值n
[where=条件]；

例：update student set set='女'；
update student set address='北京女子职业技术学校家政班'
where address='北京女子职业技术学校刺绣班'；

4.数据删除

delete from 表名[where 条件]；
truncate table 表名；

    truncate个句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比delete语句块

例：delete from student where student="王宝宝"；

truncate table student;

5.查询语法

    

select <列名|表达式|函数|常量>from<表名>
where <查询条件表达式>
[order by <排序的列名>[asc或desc]];

例：select 
studentNo,studentName,phone,address,bornDate from student
where gradeid=1
order by studentNo;

查询全部的行列

   

select *from studnet

查询部分列

    

select studentNo ,student,address
form student
where adderss="河南新乡"；

数据查询——列别名

select studentNo,AS 学生编号，studentName as 学生姓名，adderss as 学生地址
from student 
where adderss <> "河南新乡"；

查询空行、常量列

    查询空行

select studentName from student where email is null;

6.常用函数

    聚合函数

    AVG:返回某字段的平均值；

    COUNT:返回某字段的行数；

    MAX:返回某字段的最大值；

    MIN:返回某字段的最小值；

    SUM:返回某字段的和；

    字符串函数

    1.concat(str1,str1...stm)：字符串链接 

select concat('MY','S','QL');
返回：MYSQL

    2.insert(str,pos,len,newstr)：字符串替换

select insert(
  '这是SQL Sever数据库'，3,10，'MySQL'
);
返回： 这是MySQL数据库    

    3.lower(str)：将字符串转为小写

select lower(MySQL);
返回：mysql

    4.upper(str)：将字符串转为大写

select upper(mysql)；
返回：MYSQL

    5.substring(str,num,len)：字符串截取

select substring(javamysqloracle,5,5);
返回：mysql

    常用函数

    常用数学函数

8.order by子句

    把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序

select studentNo as 学生编号，（studnetresult*0.9+5）as 综合成绩
from result
where (studentresult*0.9+5)>=60
order by studentresult desc;

9.mysql查询语句中使用limit子句限制结果集

select<字段名列表>
from<表名或视图>
where[查询条件]
[group by <分组的字段名>]
[order by <排序的列名>[asc或desc]]
[limit [位置偏移量]行数]；

10.EXISTS子查询

    检测temp表是否已经创建

drop table if exists temp;
create table temp(
    #建表语句
)；

    

select ....... from 表名 where exists（子查询）；

    子查询有返回结果：exists 子查询结果为true

    子查询无返回结果：exists 子查询结果为false，外层查询不执行

11.子查询注意事项

    1.任何允许使用表达式的地方都可以使用子查询；

    2.嵌套在父查询select语句的子查询可包括

            select子句

            from子句

            where子句

            group by 子句

            having 子句

    3.只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

12.分组查询用法

    

select ..... from <表名> where .....
group by .....

select subjectno ,AVG(studentresult) as 课程平均成绩 from result 
group by subjectno

多列分组

select gradeid as 年级编号，sex as 性别，count(*) as 人数 from student
group by gradeid,sex
order by gradeid

分组筛选  

select subjectno ,AVG(studentresult) as 课程平均成绩 from result
group by subjectno 
having avg(studentresult)>=60;

13.where与having对比

    where子句：用来筛选from子句中指定的操作所产生的行

    group by：用来分组where子句

    having：用来分组的结果中筛选行

14.常用的多表链接查询

    内连接（inner jion）

    外链接：左外连接（left jion）

                右外连接（right jion）

select ..... from 表1 inner jion 表2 on ....  等价 select .... from 表1，表2 where ....

select student.studentname,result.subjectno,result.studentresult from student ,result 

select  s.student,r.subjectno,.r.studentresult from student as s inner jion result as r on (s.studentno=r.studentno) 

三表连接

   

select s.studentname as 姓名，su.subjectname as 课程， r.studentresult as 成绩 from student as s 
inner jion result as r on (s.studentno=r.studentno)
inner jion subject as su on(su.subjectno=r.subjectno);

左外连接

    

select  s.studentname,r.subjectno,r.studentresult
from student as s 
left jion result as r on s.studentno = r.studentno;

右外连接

    

select 图书编号，图书名称，出版社名称 from
图书表
right jion 出版社 on 图书表.出版社=出版社.出版社编号；