Mysql 增删改查SQL语句 模糊查询 空值处理 聚合函数

--创建数据库TestSchool

--TblStudent班级表、

----tSId  --学生编号、

----  tSName --姓名

----  tSGender--性别

----  tSAddress --地址

----  tSPhone --电话

----  tSAge --年龄

----  tSBirthday --生日

----  tSCardId --身份证号

----  tSClassId --班级Id

--

--创建一个班级表TblClass,班级名称和班级ID

--创建学生成绩表TblScore

--tScoreId（成绩id,主键,自动编号）、tSId（学生编号）、tEnglish（英语成绩）、tMath（数学成绩）

create table TblScore

(

      tScoreId int identity(1,1) primary key,

      tSId int not null,

      tEnglish float,

      tMath float

)

--创建老师表TblTeacher

--tTId、tTName、tTGender、tTAge、tTSalary、tTBirthday

create table TblTeacher

(

      tTId int identity(1,1) primary key,

      tTName nvarchar(50) not null,

      tTGender bit,

      tTSalary money,

      tTAge int,

      tTBirthday datetime

      

)

create table TblStudent

(

      tSId int identity(1,1) primary key,

      tSName nvarchar(50) not null,

      tSGender nchar(1),

      tSAddress nvarchar(500),

      tSAge int,

      tSBirthday datetime,

      tSCardId varchar(18),

      tSClassId int

)

--向表中部分列插入数据

insert into TblStudent(tSName,tSgender,tsage)

values('石蓉','女',15)

--向TblStudent表中插入一条数据

insert into

TblStudent(TSName,tsgender,tsaddress,tsage,tsbirthday,tscardId,tsclassid)

values('熊丽','女','北京市海淀区',16,'1998-5-5','123456789654123697',1)

--如果向表中的所有列（除自动编号以外的所有列）都要插入值，那么可以省略列名，同时必须保证后面的值列表中的顺序必须与表中列的顺序一致。

insert into TblStudent

values('刘天龙','男','北京市海淀区',17,'1997-5-5','123456789654123698',1)

select * from TblStudent

create table TblClass

(

      tClassId int identity(1,1) primary key,

      tClassName nvarchar(50)

)

--向自动编号列插入值

--启动某个表的“自动编号列”手动插入值的功能

SET IDENTITY_INSERT TblClass ON

insert into TblClass(tClassId,tClassName)

values(500,'.net黑马二期')

SET IDENTITY_INSERT TblClass off

select * from TblClass

--向班级表中插入一条记录

--insert into 表名(列,列,列) values(值,值,值)

--1.自动编号列，默认就会自动增长，所以不需要（默认情况下也不能向自动编号列插入值）

insert into TblClass(tClassName) values(N'.net黑马四期')

select * from TblClass

/*

fsdfdsfsfsdf

*/

use ttt;

--在SQL语句中的直接写的字符串中，如果包含中文，一定要在字符串前面加N

insert into Table_2 values(N'科比布莱恩特')

select * from Table_2

--

--打开和关闭查询结果窗口：ctrl + R

--更新语句：

--update 表名set 列=新值,列=新值,... where 条件

--update语句，如果不加where条件，那么表示对表中所有的数据都进行修改，所以一定要加where条件

select * from TblStudent

update TblStudent set tsAge=tsAge-1,tsname=tsname+'（女）' where tsgender='女'

 

--删除数据语句：

--delete from 表名where ...

--delete语句如果不加where条件，表示将表中所有数据都删除，加Where条件后，会按照Where条件进行删除。

use heima13

select * from TblStudent

--删除TblStudent表中的所有数据，

--自动编号并没有恢复到默认，仍然继续编号。

delete from TblStudent

insert into TblStudent

values('刘天龙','男','北京市海淀区',17,'1997-5-5','123456789654123698',1)

use itcast2014

select * from TblStudent

--删除所有性别为'女'，同时年龄小于岁的。

delete from TblStudent where tsgender='女' and tsage<20

 

--删除表中的全部数据：

--1.delete from Biao

--2.truncate table Biao

--如果确实要删除表中全部数据，那么建议使用truncate

--truncate特点：

--1>truncate语句不能跟where条件（无法根据条件来删除，只能全部删除数据）

--2>同时自动编号恢复到初始值。

--3>使用truncate删除表中所有数据要比delete效率高的多。

--4>truncate删除数据，不触发delete触发器。

select * from TblTeacher

--1.使用insert into向TblTeacher表中插入条数据。

select * from TblTeacher

insert into TblTeacher values('刘岐','男',30,1500000,'1983-10-10','2012-5-8')

--2.向TblPerson表中插入条数据。

select * from TblPerson

insert into TblPerson values('百川儿',21,175,1)

--练习：给studentId是的英语成绩加分

select * from TblScore

update TblScore set tEnglish=tEnglish-50 where tsid=1

update TblScore set tEnglish=tEnglish+10 where tsid=1

--练习：考试题偏难，所有人的数学成绩加分,最高不能超过分

--2.1先把那些+5分后，成绩超过分的人，设置为分。

update TblScore set tMath=100 where tMath+5>100

--2.2将那些成绩+5分没有超过分的人，让他们的成绩+5分。

update TblScore set tMath=tMath+5 where tmath+5<=100

update TblScore set tMath=tMath+5

--练习：所有女学生的年龄减岁

select * from TblStudent

update TblStudent set tsage=tsage-1 where tsgender='女'

--删除工资大于的老师

select * from TblTeacher

delete from TblTeacher where ttSalary>20000

============将老师表清空========

--删除所有老师,删除数据时候 把自增长列的值还原成种子

truncate table TblTeacher

insert into TblTeacher values('张三','男',18,1999,'1998-10-10','2014-1-1')

-----------------------------------------------------------------------

use heima13

create table Employees

(

      EmpId int identity(1,1),

      EmpName varchar(50),

      EmpGender char(2),

      EmpAge int,

      EmpEmail varchar(100),

      EmpAddress varchar(500)

)

create table Department

(

      DepId int identity(1,1),

      DepName varchar(50)

)

select * from Employees

select * from Department

----------------------------------------------------------

--通过t-sql语句来创建约束

------------------------------------------------------------

--新建一张表：员工信息表

create table Employees

(

      EmpId int identity(1,1),

      EmpName varchar(50),

      EmpGender char(2),

      EmpAge int,

      EmpEmail varchar(100),

      EmpAddress varchar(500)

)

create table Department

(

      DepId int identity(1,1) ,

      DepName varchar(50)

)

Drop table Employees

select * from Employees

--红警-- Red Alert----

--============手动增加约束==========

--手动删除一列(删除EmpAddress列)

alter table Employees drop column EmpAddress

--手动增加一列(增加一列EmpAddr varchar(1000))

alter table Employees add EmpAddr nvarchar(1000)

--手动修改一下EmpEmail的数据类型(varchar(200))

alter table Employees alter column EmpEmail varchar(200)

--为EmpId增加一个主键约束

alter table Employees add constraint PK_Employees_EmpId primary key(EmpId)

--非空约束,为EmpName增加一个非空约束(修改列)

alter table Employees alter column EmpName varchar(50) not null

--为EmpName增加一个唯一约束

alter table Employees add constraint UQ_Employees_EmpName unique(EmpName)

--为性别增加一个默认约束，默认为'男'

alter table Employees add constraint DF_Employees_EmpGender default('男') for EmpGender

--为性别增加一个检查约束，要求性别只能是：'男' or '女'

alter table Employees add constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女')

--为年龄增加一个检查约束：年龄必须在岁之间，含岁与岁。

alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)

--创建一个部门表，然后为Employee表增加一个DepId列。

alter table Employees add EmpDepId int not null

--为Department表设置主键。主键列是：DepId

alter table Department add constraint PK_Department_DepId primary key(DepId)

--增加外键约束

alter table Employees add constraint FK_Employees_Department foreign key(EmpDepId) references Department(DepId) -- on delete cascade

 

 

 

--删除约束---------------------------

alter table Employees drop constraint FK_Employees_Department,CK_Employees_EmpAge,CK_Employees_EmpGender,DF_Employees_EmpGender,UQ_Employees_EmpName

--通过一条代码来增加多个约束

alter table Employees add

constraint FK_Employees_Department foreign key(EmpDepId) references Department(DepId),

constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120),

constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女')

---------------创建表的同时就为表增加约束------------------------------

drop table Employees

drop table Department

create table Employees

(

      EmpId int identity(1,1) primary key,

      EmpName varchar(50) not null unique check(len(EmpName)>2),

      EmpGender char(2) default('男'),

      EmpAge int check(EmpAge>0 and EmpAge<120),

      EmpEmail varchar(100) unique,

      EmpAddress varchar(500) not null,

      EmpDepId int foreign key references Department(DepId) on delete cascade

)

create table Department

(

      DepId int identity(1,1) primary key,

      DepName varchar(50) not null unique

)

 

 

-----------数据检索（查询）---------------------------------------

use itcast2014

-- * 表示显示所有列

-- 查询语句没有加where条件表示查询所有行

select *

from TblStudent

---只查询表中的部分列

select tsid,tsname,tsgender from TblStudent

--根据条件，只查询部分行(使用where条件筛选部分行显示)

select * from TblStudent where tsclassId=5

--为查询结果集中的列起别名

select tsid as 学生编号,tsname as 学生姓名,tsgender as 性别 from TblStudent

select

      tsid as 学生编号,

      tsname as 学生姓名,

      tsgender as 性别

from TblStudent

select

      tsid '(学生 编号)',

      tsname 学生姓名,

      tsgender 性别

from TblStudent

select

      学生编号=tsid,

      学生姓名=tsname,

      性别=tsgender

from TblStudent

select

      学生编号=tsid,

      学生姓名=tsname,

      性别=tsgender,

      婚否='否'

from TblStudent

--并不是说select必须配合from一起来使用，可以单独使用select

select

      当前系统时间=getdate()

      

select

      班长='严守卫',

      班花='待定',

      班草='待定',

      班主任='宋词'

      

      

      

      

select * from MyStudent

--distinct关键字，针对已经查询出的结果然后去除重复

select distinct * from TblStudent

select distinct tsname,tsgender,tsaddress from TblStudent

-----------排序--------------------------

--order by 列名

select * from TblStudent

--按照年龄，降序排序

select * from TblStudent order by tsage desc  --降序排序

--按照年龄，升序排序

select * from TblStudent order by tsage asc --升序排序

select * from TblStudent order by tsage  --默认就是升序排序

select * from TblScore order by tMath desc

select * from TblScore order by tEnglish

--查询数学成绩最高的前名

select top 5 * from TblScore order by tMath desc

--获取数学成绩最低的前个

select top 5 * from TblScore order by tMath asc

--如果top后跟的不是数字，而是一个表达式一定要使用()把表达式括起来。

select top (2*2) * from TblScore order by tMath desc

--会查询出条数据。

select top 35 percent * from TblScore order by tMath desc

--获得年纪最小的个学生

select * from MyStudent

select top 5 * from MyStudent order by Fage asc

--获得年纪最大的%的学生(percent)

select top 10 percent * from MyStudent order by fage desc

--聚合函数默认把整个表中的数据当做“一组”，然后才进行的统计。

select * from NewPerson

--统计出所有人的年龄的总和

select sum(age) as 年龄总和 from NewPerson

--统计当前表中一共有多少条记录

select count(*) from NewPerson

--计算平均年龄

select

      平均年龄=(select sum(age) as 年龄总和 from NewPerson)*1.0/(select count(*) from NewPerson)

--计算年龄最大的

select max(age)   from NewPerson

--年龄最小的

select min(age) from NewPerson

--计算平均值avg

select avg(age*1.0) from NewPerson

-------聚合函数的一些其他问题--------------

--1.聚合函数不统计空值

select * from TblStudent

select count(tsid) from TblStudent

select avg(tsage) from TblStudent --avg()也是不统计空值的。

select sum(tsage) from TblStudent --sum()对于null值，认为是

--2.如果使用聚合函数的时候，没有手动group by分组，那么聚合函数会把整个表中的数据作为一组来统计

----------带条件查询-------------------

--select  列

--from 表

--where 条件

--查询没有及格的学生(假设：数学或英语，只要有一门没有及格就叫做没有及格)的学号

select * from TblScore

select tsid from TblScore where tEnglish<60 or tMath<60

--查询年龄在岁之间的男学生(包含和)

select * from MyStudent

select * from MyStudent  where fage>=20 and fage<=30 and fgender='男'

select * from MyStudent where fage between 20 and 30 and fgender='男'

--Between…and …   在之间,（闭区间，包含两个端点值）

--查询年龄在岁之间的男学生

--查询math成绩在分之间的所有学生

select * from TblScore where tMath between 80 and 90

----------

select * from TblStudent

--查询出所有班级Id为,4,5的那些学生

--19,1,27,86    select *  from TblStudent where tsclassId in (19,1,27,86)

select *  from TblStudent where tsclassId=3 or tsclassid=4 or tsclassId=5

select *  from TblStudent where tsclassId in (3,4,5) 

--对于in或者or 查询，如果查询中的条件是连续的几个数字，最好使用>=  <=或者between...and不要使用or或者in。提高效率

select *  from TblStudent where tsclassId >=3 and tsclassId<=5

select * from TblClass

--模糊查询：

--通配符：_    、  %   、 []   、 ^

--  _  表示任意的单个字符

--姓张，两个字的。

select * from MyStudent where fname like '张_'

--姓张，三个字的

select * from MyStudent where fname like '张__'

-- % 匹配任意多个任意字符

--无论姓名字数,只要第一个字符是'张'的就查询出来

select * from MyStudent where fname like '张%'

select * from MyStudent where fname like '张%' and len(fname)=2

--  []  表示筛选，范围。

select * from TblStudent

select * from TblStudent where tsname like '张[0-9]妹'

select * from TblStudent where tsname like '张_妹'

select * from TblStudent where tsname like '张[a-z]妹'

select * from TblStudent where tsname like '张[a-z0-9]妹'

select * from TblStudent where tsname like '张[^0-9]妹'

select * from TblStudent where tsname not like '张[0-9]妹'

update TblStudent set tsname=replace(tsname,'（女）','')

--查询出姓名中包含%的那些人

--通配符放到[]中就转义了就不认为是通配符了。

select * from TblStudent where tsname like '%[%]%'

--WHERE ColumnA LIKE '%5/%%' ESCAPE '/'

--自己指定一个转义符

select * from TblStudent where tsname like '%/]%' ESCAPE '/'

select * from TblStudent where tsname like '%/[%'  ESCAPE '/'

select * from TblStudent where tsname like '%/[%/]%'  ESCAPE '/'

-----------------空值处理-------------------------------------

select * from TblStudent

--查询所有年龄是null的同学信息

--null值无法使用=或<>来进行比较

--unknown

--判断null值必须使用is null或者is not null

select * from TblStudent where tsage is null

select * from TblStudent where tsage=null

--查询所有年龄不是null的同学

select * from TblStudent where tsage<>null

select * from TblStudent where tsage is not null

select * from TblStudent where tsage=25

select * from TblStudent where tsage<>25

--任何值与null进行计算，得到的结果还是null

select 2000+null

---------通过order by 语句进行排序：

--1.降序order by 列名desc

--2.升序order by 列名   或order by 列名asc

--3.order by语句必须一定要放在整个sql语句的最后。

--4.根据多列进行排序

--5.可以按照表达式进行排序

--现根据英语成绩排序，再根据数学成绩排序(先按照英语成绩排序，当英语成绩相同的时候再按照数学成绩排序)

select * from TblScore order by tEnglish desc,tmath desc

select * from biao

inner join ..

where ...

gourp by ...

having ....

order by ..

w english 100,math=20

n english 20,math=100

select

      *,

      平均分=(tEnglish+tmath)*1.0/2

from TblScore

order by 平均分 desc

select

      *

from TblScore

order by (tEnglish+tmath)*1.0/2 desc

SELECT *    --3.

FROM  Score  --1.

where english>=60 and math>=60   --2.

ORDER BY english DESC,math DESC   --4.