数据库操作 | 杨亦涛-关注知识分享博客网站

4天

数据库概念

mysql navicat

sql语句查询

mysql高级

1、概念

1.1、数据库的作用

用来存储数据，方便查询

1.2、关系型数据库

核心是用二维表存储数据

行，记录，代表的是一条事物的信息
列，字段，代表一条事物的某一个属性

表可以存很多行
数据库可以存很多表

1.3、SQL

结构化查询语言，操作关系型数据库

不区分大小写，select Select

1.4、MySQL

瑞典公司开发，被卖给Sun，Sun又被卖给Oracle

开源免费支持多平台

2、MySQL安装

服务端：不能带中文路径

启动停止重启

客户端：

命令行客户端

navicat

3、navicat使用

连接服务端

输入ip、端口、用户名、密码

数据库操作

创建字符集用过utf8

编辑不能改名称

删除

数据表

创建

设计表

添加字段

修改字段

删除字段

重命名

删除

数据操作

添加

删除

修改

查询

4、数据类型与约束

int ：有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义

varchar ：varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符

decimal ：decimal(5,2)表示共存5位数，小数占2位，整数占3位

主键：字段值必须唯一，且不能为null，唯一标识某一条数据，一般字段名叫id，int类型、无符号、自动递增

5、sql语言

表

创建表

create table 表名(
字段名类型约束,
字段名类型约束
…
)

— 注释 ctrl + /
— 取消注释 ctrl + shift + /

例：创建学生表，字段要求如下：

姓名(长度为10)

create table students(
name varchar(10)
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄

create table students2(
name varchar(10),
age int unsigned
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄，身高(保留小数点2位)

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

删除表

如果表不存在，会报错
drop table students

如果表存在，才删除，不存在也不会报错
drop table if exists students3;

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

数据操作

插入数据

insert into 表名 values(…)
例：插入一个学生，设置所有字段的信息

insert into students3 values(0,’亚瑟’,16,160.5)
insert into students3 values(default,’亚瑟2′,16,160.5)
insert into students3 values(null,’亚瑟’,16,160.5)

给部分字段设置数据

insert into students3(name) values(‘鲁班’)

insert into students3(name,age) values(‘鲁班2′,30)

执行多条语句插入多条数据

insert into students3 values(null,’亚瑟4′,16,160.5);
insert into students3 values(null,’亚瑟5′,16,160.5);
insert into students3 values(null,’亚瑟6’,16,160.5)

执行一条语句插入多条数据

insert into students3(name) values(‘鲁班’),(‘鲁班2’),(‘鲁班3’),(‘鲁班4′)

更新数据

格式：update 表名 set 列1=值1,列2=值2… where 条件
例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为 20

update students3 set age=10 where id=5

update students3 set name=’狄仁杰’,age=22,height=123.246 where age=20

删除数据

格式：delete from 表名 where 条件
例：删除id为6的学生数据

delete from hero2

delete from students3 where id=12

逻辑删除

1、给表添加一个字段，名字叫isdelete int类型，0代表未删除，1代表删除

2、把表中所有的数据isdelete字段改为0

3、想删除数据时，使用update语句，把某条数据的isdelete字段改为1

update students3 set isdelete=1 where id=5

4、在查询数据时，只查询isdelete为0的数据

select * from students3 where isdelete=0

4天

数据库概念

mysql navicat

sql语句查询

mysql高级

1、概念

1.1、数据库的作用

用来存储数据，方便查询

1.2、关系型数据库

核心是用二维表存储数据

行，记录，代表的是一条事物的信息
列，字段，代表一条事物的某一个属性

表可以存很多行
数据库可以存很多表

1.3、SQL

结构化查询语言，操作关系型数据库

不区分大小写，select Select

1.4、MySQL

瑞典公司开发，被卖给Sun，Sun又被卖给Oracle

开源免费支持多平台

2、MySQL安装

服务端：不能带中文路径

启动停止重启

客户端：

命令行客户端

navicat

3、navicat使用

连接服务端

输入ip、端口、用户名、密码

数据库操作

创建字符集用过utf8

编辑不能改名称

删除

数据表

创建

设计表

添加字段

修改字段

删除字段

重命名

删除

数据操作

添加

删除

修改

查询

4、数据类型与约束

int ：有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义

varchar ：varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符

decimal ：decimal(5,2)表示共存5位数，小数占2位，整数占3位

主键：字段值必须唯一，且不能为null，唯一标识某一条数据，一般字段名叫id，int类型、无符号、自动递增

5、sql语言

表

创建表

create table 表名(
字段名类型约束,
字段名类型约束
…
)

— 注释 ctrl + /
— 取消注释 ctrl + shift + /

例：创建学生表，字段要求如下：

姓名(长度为10)

create table students(
name varchar(10)
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄

create table students2(
name varchar(10),
age int unsigned
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄，身高(保留小数点2位)

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

删除表

如果表不存在，会报错
drop table students

如果表存在，才删除，不存在也不会报错
drop table if exists students3;

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

数据操作

插入数据

insert into 表名 values(…)
例：插入一个学生，设置所有字段的信息

insert into students3 values(0,’亚瑟’,16,160.5)
insert into students3 values(default,’亚瑟2′,16,160.5)
insert into students3 values(null,’亚瑟’,16,160.5)

给部分字段设置数据

insert into students3(name) values(‘鲁班’)

insert into students3(name,age) values(‘鲁班2′,30)

执行多条语句插入多条数据

insert into students3 values(null,’亚瑟4′,16,160.5);
insert into students3 values(null,’亚瑟5′,16,160.5);
insert into students3 values(null,’亚瑟6’,16,160.5)

执行一条语句插入多条数据

insert into students3(name) values(‘鲁班’),(‘鲁班2’),(‘鲁班3’),(‘鲁班4′)

更新数据

格式：update 表名 set 列1=值1,列2=值2… where 条件
例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为 20

update students3 set age=10 where id=5

update students3 set name=’狄仁杰’,age=22,height=123.246 where age=20

删除数据

格式：delete from 表名 where 条件
例：删除id为6的学生数据

delete from hero2

delete from students3 where id=12

逻辑删除

1、给表添加一个字段，名字叫isdelete int类型，0代表未删除，1代表删除

2、把表中所有的数据isdelete字段改为0

3、想删除数据时，使用update语句，把某条数据的isdelete字段改为1

update students3 set isdelete=1 where id=5

4、在查询数据时，只查询isdelete为0的数据

select * from students3 where isdelete=0

查询

查询所有字段

select * from 表名

select * from students

查询指定字段

select name,hometown from students

给字段取别名

select name as 姓名,hometown as 家乡 from students

给表取别名

select stu.name,stu.hometown from students as stu

去重，重复的记录只显示一条，当显示的一整行记录跟另一整行记录完全一样时，认为是重复的记录

select distinct sex from students

select distinct sex,class from students

比较运算

例1：查询小乔的年龄

select age from students where name=’小乔’

例2：查询20岁以下的学生

select * from students where age<=20 例3：查询家乡不在北京的学生 select * from students where hometown<>‘北京’

逻辑运算

例1：查询年龄小于20的女同学

select * from students where age<20 and sex='女' 例2：查询女学生或'1班'的学生 select * from students where sex='女' or class='1班' 例3：查询非天津的学生 select * from students where not hometown='天津' 模糊查询例1：查询姓孙的学生 select * from students where name like '孙%' 例2：查询姓孙且名字是一个字的学生 select * from students where name like '孙__' 例3：查询叫乔的学生 select * from students where name like '%乔' 例4：查询姓名含白的学生 select * from students where name like '%白%' 范围查询例1：查询家乡是北京或上海或广东的学生 select * from students where hometown='北京' or hometown='上海' or hometown='广东' select * from students where hometown in ('北京','上海','广东') 例2：查询年龄为18至20的学生 select * from students where age in (18,19,20) select * from students where age between 18 and 20 小值在前，大值在后 select * from students where age>=20 and age<=18 空判断例1：查询没有填写身份证的学生 select * from students where card is null 例2：查询填写了身份证的学生 select * from students where card is not null '' 代表插入的数据为空字符串 insert into students values('013','凯','男','北京',20,'1班','') null 代表插入的值为空 insert into students values('014','凯2','男','北京',20,'1班',null) 插入数据时，如果一些字段没指定数据，默认为null insert into students(studentNo,name) values('015','凯3') 排序例1：查询所有学生信息，按年龄从小到大排序 select * from students order by age 例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序 select * from students order by age desc,studentNo 对中文字段进行排序 select * from students order by convert(name using gbk) 聚合函数例1：查询学生总数 count(*)代表如果一行记录中任何一个字段有值，就会统计在内 select count(*) from students count(card)代表统计某一个列的个数，不会把null值统计在内 select count(card) from students 例2：查询女生的最大年龄 select max(age) from students where sex='女' 例3：查询1班的最小年龄 select min(age) from students where class='1班' 例4：查询北京学生的年龄总和 select sum(age) from students where hometown='北京' 例5：查询女生的平均年龄 select avg(age) from students where sex='女' 分组例1：查询各种性别的人数 select sex,count(*) from students group by sex 例2：查询各种年龄的人数 select age,count(*) from students group by age 例1：查询男生总人数 having必须放在group by 后面 select count(*) from students where sex='男' select sex,count(*) from students group by sex having sex='男' 查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄 select class,avg(age),max(age),min(age) from students where class!='1班' group by class select class,avg(age),max(age),min(age) from students group by class having class!='1班' 按照多个字段分组，多个字段值一模一样时，认为是一组的 select class,sex,count(*) from students group by class,sex 获取部分行 limit start,count mysql中的第一条记录的位置是0 例1：查询前3行学生信息 select * from students limit 1,2 limit 2 就等于 limit 0,2 分页每页显示4条 1页 select * from students limit 0,4 (n-1)*4 2页 select * from students limit 4,4 3页 select * from students limit 8,4 连接查询等值连接例1：查询学生信息及学生的成绩生成临时表，然后在临时表中使用关联的字段进行过滤 select stu.name,sc.score from students as stu,scores as sc where stu.studentNo=sc.studentNo 内连接，不生成临时表，连接时先判断条件，只有符合条件才会连接再放到结果 select * from students as stu inner join scores as sc on stu.studentNo=sc.studentNo 例2：查询课程信息及课程的成绩 select * from scores,courses where scores.courseNo=courses.courseNo select * from courses inner join scores on scores.courseNo=courses.courseNo 例3：查询学生信息及学生的课程对应的成绩 select * from students, scores, courses where students.studentno = scores.studentno and scores.courseno = courses.courseno select students. name, courses. name as coursename, scores.score from students inner join scores on students.studentno = scores.studentno inner join courses on scores.courseno = courses.courseno 查询练习题 1、查询学生"百里守约"的基本信息 2、查询学生"百里守约"或”百里玄策”的基本信息 3、查询姓"张"学生的姓名，年龄，班级 4、查询姓名中含有"约"字的学生的基本信息 5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号 6、查询姓"百"或者姓”孙”的学生的基本信息 7、查询姓"百"并且家乡是"山西"的学生信息 8、查询家乡是"北京"、”新疆”、”山东”或者"上海"的学生的信息 9、查询姓"孙"，但是家乡不是"河北"的学生信息 10、查询家乡不是"北京"、"新疆"、"山东"、"上海"的学生的信息 11、查询全部学生信息，并按照“性别”排序 12、查询现有学生都来自于哪些不同的省份 13、查询所有男生，并按年龄升序排序 14、统计共有多少个学生 15、统计年龄大于20岁的学生有多少个 16、统计男生的平均年龄 17、查询1班学生中的最大年龄是多少 18、统计2班男女生各有多少人 19、统计每个班级中每种性别的学生人数，并按照班级升序排序 20、查询年龄最小的学生的全部信息 4天数据库概念 mysql navicat sql语句查询 mysql高级 1、概念 1.1、数据库的作用用来存储数据，方便查询 1.2、关系型数据库核心是用二维表存储数据行，记录，代表的是一条事物的信息列，字段，代表一条事物的某一个属性表可以存很多行数据库可以存很多表 1.3、SQL 结构化查询语言，操作关系型数据库不区分大小写，select Select 1.4、MySQL 瑞典公司开发，被卖给Sun，Sun又被卖给Oracle 开源免费支持多平台 2、MySQL安装服务端：不能带中文路径启动停止重启客户端：命令行客户端 navicat 3、navicat使用连接服务端输入ip、端口、用户名、密码数据库操作创建字符集用过utf8 编辑不能改名称删除数据表创建设计表添加字段修改字段删除字段重命名删除数据操作添加删除修改查询 4、数据类型与约束 int ：有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义 varchar ：varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符 decimal ：decimal(5,2)表示共存5位数，小数占2位，整数占3位主键：字段值必须唯一，且不能为null，唯一标识某一条数据，一般字段名叫id，int类型、无符号、自动递增 5、sql语言表创建表 create table 表名( 字段名类型约束, 字段名类型约束 ... ) -- 注释 ctrl + / -- 取消注释 ctrl + shift + / 例：创建学生表，字段要求如下：姓名(长度为10) create table students( name varchar(10) ) 例：创建学生表，字段要求如下：姓名(长度为10)，年龄 create table students2( name varchar(10), age int unsigned ) 例：创建学生表，字段要求如下：姓名(长度为10)，年龄，身高(保留小数点2位) create table students3( id int unsigned primary key auto_increment, name varchar(10), age int unsigned, height decimal(5,2) ) 删除表如果表不存在，会报错 drop table students 如果表存在，才删除，不存在也不会报错 drop table if exists students3; create table students3( id int unsigned primary key auto_increment, name varchar(10), age int unsigned, height decimal(5,2) ) 数据操作插入数据 insert into 表名 values(...) 例：插入一个学生，设置所有字段的信息 insert into students3 values(0,'亚瑟',16,160.5) insert into students3 values(default,'亚瑟2',16,160.5) insert into students3 values(null,'亚瑟',16,160.5) 给部分字段设置数据 insert into students3(name) values('鲁班') insert into students3(name,age) values('鲁班2',30) 执行多条语句插入多条数据 insert into students3 values(null,'亚瑟4',16,160.5); insert into students3 values(null,'亚瑟5',16,160.5); insert into students3 values(null,'亚瑟6',16,160.5) 执行一条语句插入多条数据 insert into students3(name) values('鲁班'),('鲁班2'),('鲁班3'),('鲁班4') 更新数据格式：update 表名 set 列1=值1,列2=值2... where 条件例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为 20 update students3 set age=10 where id=5 update students3 set name='狄仁杰',age=22,height=123.246 where age=20 删除数据格式：delete from 表名 where 条件例：删除id为6的学生数据 delete from hero2 delete from students3 where id=12 逻辑删除 1、给表添加一个字段，名字叫isdelete int类型，0代表未删除，1代表删除 2、把表中所有的数据isdelete字段改为0 3、想删除数据时，使用update语句，把某条数据的isdelete字段改为1 update students3 set isdelete=1 where id=5 4、在查询数据时，只查询isdelete为0的数据 select * from students3 where isdelete=0 查询查询所有字段 select * from 表名 select * from students 查询指定字段 select name,hometown from students 给字段取别名 select name as 姓名,hometown as 家乡 from students 给表取别名 select stu.name,stu.hometown from students as stu 去重，重复的记录只显示一条，当显示的一整行记录跟另一整行记录完全一样时，认为是重复的记录 select distinct sex from students select distinct sex,class from students 比较运算例1：查询小乔的年龄 select age from students where name='小乔' 例2：查询20岁以下的学生 select * from students where age<=20 例3：查询家乡不在北京的学生 select * from students where hometown<>‘北京’

逻辑运算

例1：查询年龄小于20的女同学

select * from students where age>(select avg(age) from students)

例2：查询王昭君的成绩，要求显示成绩

select studentNo from students where name=’王昭君’

select score from scores where studentNo=(select studentNo from students where name=’王昭君’)

列子查询：子查询返回的结果是一列多行
例3：查询18岁的学生的成绩，要求显示成绩

select studentNo from students where age=18

select * from scores where studentNo in(select studentNo from students where age=18)

行子查询：子查询返回的结果是一行多列
例4：查询男生中年龄最大的学生信息

select * from students where sex=’男’ and age=(select max(age) from students where sex=’男’)

select * from students where sex=’男’ and age=30

select * from students where (sex,age)=(‘男’,30)

select * from students
where (sex,age)=(select sex,age from students where sex=’男’ order by age desc limit 1)

表子查询：子查询返回的结果给另一个查询当作表来使用

例5：查询数据库和系统测试的课程成绩

select * from scores
inner join courses on scores.courseNo=courses.courseNo
where courses.name in (‘数据库’,’系统测试’)

select * from scores
inner join
(select * from courses where name in (‘数据库’,’系统测试’)) as c
on scores.courseNo=c.courseNo

查询演练

求所有电脑产品的平均价格,并且保留两位小数

select round(avg(price),2) from goods

查询所有价格大于平均价格的商品，并且按价格降序排序

select * from goods where price>(select round(avg(price),2) from goods) order by price desc

查询类型为’超极本’的商品价格

select price from goods where cate=’超级本’

查询价格大于或等于”超级本”价格的商品，并且按价格降序排列

select * from goods where price >=any(select price from goods where cate=’超级本’) order by price desc

创建类型表

create table goods_cates(
cate_id int unsigned primary key auto_increment,
cate_name varchar(10)
)

select distinct cate from goods
select cate from goods group by cate

插入数据，查询出来的字段需要和想插入的字段保持顺序一致
insert into goods_cates(cate_name) select distinct cate from goods

创建品牌表,并且插入数据，查询出来的字段名需要和想插入的字段名一模一样

create table goods_brands(
brand_id int unsigned primary key auto_increment,
brand_name varchar(10)
)
select distinct brand_name from goods

更新商品表的类型

select * from goods
inner join goods_cates on goods.cate=goods_cates.cate_name

update goods
inner join goods_cates on goods.cate=goods_cates.cate_name
set goods.cate=goods_cates.cate_id

更新商品表的品牌

update goods
inner join goods_brands on goods.brand_name=goods_brands.brand_name
set goods.brand_name=goods_brands.brand_id

查询商品信息，包括品牌、类型

select goods.id,goods.name,goods_cates.cate_name,goods_brands.brand_name from goods
inner join goods_cates on goods.cate_id=goods_cates.cate_id
inner join goods_brands on goods.brand_id=goods_brands.brand_id

查询练习题
1、查询学生”百里守约”的基本信息
select * from students where name=’百里守约’

2、查询学生”百里守约”或”百里玄策”的基本信息
select * from students where name=’百里守约’ or name=’百里玄策’

3、查询姓”张”学生的姓名，年龄，班级
select name,age,class from students where name like ‘张%’

4、查询姓名中含有”约”字的学生的基本信息
select * from students where name like ‘%约%’

5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号
select studentNo,name,age,class,card from students where name like ‘孙__’

6、查询姓”百”或者姓”孙”的学生的基本信息
select * from students where name like ‘百%’ or name like ‘孙%’

7、查询姓”百”并且家乡是”山西”的学生信息
select * from students where name like ‘百%’ and hometown=’山西’

8、查询家乡是”北京”、”新疆”、”山东”或者”上海”的学生的信息
select * from students where hometown in(‘北京’,’新疆’,’山东’,’上海’)

9、查询姓”孙”，但是家乡不是”河北”的学生信息
select * from students where name like ‘孙%’ and hometown!=’河北’

10、查询家乡不是”北京”、”新疆”、”山东”、”上海”的学生的信息
select * from students where hometown not in(‘北京’,’新疆’,’山东’,’上海’)

11、查询全部学生信息，并按照“性别”排序
select * from students order by sex

12、查询现有学生都来自于哪些不同的省份
select hometown from students group by hometown

13、查询所有男生，并按年龄升序排序
select * from students where sex=’男’ order by age

14、统计共有多少个学生
select count(*) from students

15、统计年龄大于20岁的学生有多少个
select count(*) from students where age>20

16、统计男生的平均年龄
select avg(age) from students where sex=’男’

17、查询1班学生中的最大年龄是多少
select max(age) from students where class=’1班’

18、统计2班男女生各有多少人
select class,sex,count(*) from students where class=’2班’ group by sex

19、统计每个班级中每种性别的学生人数，并按照班级升序排序
select class,sex,count(*) from students group by class,sex order by class

20、查询年龄最小的学生的全部信息
select * from students order by age limit 1
表关系如下：
– 部门表dept：部门标号（DEPTNO），部门名称（DNAME），所在位置（LOC）
– 雇员表emp：员工标号（Empno），员工名称（Emname），员工工位（Job），经理（Mgr），雇佣日期（Hiredate），薪水（Sal），部门编号（Deptno）

写出sql语句：
– 找出部门名称为ACCOUNTING的部门下的所有员工名称？
select emname from emp where deptno=(select deptno from dept where dname=‘ACCOUNTING’)

– 找出部门名称为SALES的部门下每月需要发出的薪水总额？
select sum(sal) from emp where deptno=(select deptno from dept where dname=‘SALES’)

– 找出部门名称为SALES的部门的部门经理？

select * from emp where deptno=(select deptno from dept where dname=‘SALES’)

select jingli.emname from emp as jingli
inner join emp as yuangong on jingli.emname= yuangong.mgr
where jingli.deptno=(select deptno from dept where dname=‘SALES’)

– 找出部门名称为RESEARCH的部门下雇佣日期为1980-12-17的员工？

select * from emp where deptno=(select deptno from dept where dname=‘RESEARCH’) and diredate=‘1980-12-17’
4天

数据库概念

mysql navicat

sql语句查询

mysql高级

1、概念

1.1、数据库的作用

用来存储数据，方便查询

1.2、关系型数据库

核心是用二维表存储数据

行，记录，代表的是一条事物的信息
列，字段，代表一条事物的某一个属性

表可以存很多行
数据库可以存很多表

1.3、SQL

结构化查询语言，操作关系型数据库

不区分大小写，select Select

1.4、MySQL

瑞典公司开发，被卖给Sun，Sun又被卖给Oracle

开源免费支持多平台

2、MySQL安装

服务端：不能带中文路径

启动停止重启

客户端：

命令行客户端

navicat

3、navicat使用

连接服务端

输入ip、端口、用户名、密码

数据库操作

创建字符集用过utf8

编辑不能改名称

删除

数据表

创建

设计表

添加字段

修改字段

删除字段

重命名

删除

数据操作

添加

删除

修改

查询

4、数据类型与约束

int ：有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义

varchar ：varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符

decimal ：decimal(5,2)表示共存5位数，小数占2位，整数占3位

主键：字段值必须唯一，且不能为null，唯一标识某一条数据，一般字段名叫id，int类型、无符号、自动递增

5、sql语言

表

创建表

create table 表名(
字段名类型约束,
字段名类型约束
…
)

— 注释 ctrl + /
— 取消注释 ctrl + shift + /

例：创建学生表，字段要求如下：

姓名(长度为10)

create table students(
name varchar(10)
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄

create table students2(
name varchar(10),
age int unsigned
)

例：创建学生表，字段要求如下：

姓名(长度为10)，年龄，身高(保留小数点2位)

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

删除表

如果表不存在，会报错
drop table students

如果表存在，才删除，不存在也不会报错
drop table if exists students3;

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

数据操作

插入数据

insert into 表名 values(…)
例：插入一个学生，设置所有字段的信息

insert into students3 values(0,’亚瑟’,16,160.5)
insert into students3 values(default,’亚瑟2′,16,160.5)
insert into students3 values(null,’亚瑟’,16,160.5)

给部分字段设置数据

insert into students3(name) values(‘鲁班’)

insert into students3(name,age) values(‘鲁班2′,30)

执行多条语句插入多条数据

insert into students3 values(null,’亚瑟4′,16,160.5);
insert into students3 values(null,’亚瑟5′,16,160.5);
insert into students3 values(null,’亚瑟6’,16,160.5)

执行一条语句插入多条数据

insert into students3(name) values(‘鲁班’),(‘鲁班2’),(‘鲁班3’),(‘鲁班4′)

更新数据

格式：update 表名 set 列1=值1,列2=值2… where 条件
例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为 20

update students3 set age=10 where id=5

update students3 set name=’狄仁杰’,age=22,height=123.246 where age=20

删除数据

格式：delete from 表名 where 条件
例：删除id为6的学生数据

delete from hero2

delete from students3 where id=12

逻辑删除

1、给表添加一个字段，名字叫isdelete int类型，0代表未删除，1代表删除

2、把表中所有的数据isdelete字段改为0

3、想删除数据时，使用update语句，把某条数据的isdelete字段改为1

update students3 set isdelete=1 where id=5

4、在查询数据时，只查询isdelete为0的数据

select * from students3 where isdelete=0

查询

查询所有字段

select * from 表名

select * from students

查询指定字段

select name,hometown from students

给字段取别名

select name as 姓名,hometown as 家乡 from students

给表取别名

select stu.name,stu.hometown from students as stu

去重，重复的记录只显示一条，当显示的一整行记录跟另一整行记录完全一样时，认为是重复的记录

select distinct sex from students

select distinct sex,class from students

比较运算

例1：查询小乔的年龄

select age from students where name=’小乔’

例2：查询20岁以下的学生

select * from students where age<=20 例3：查询家乡不在北京的学生 select * from students where hometown<>‘北京’

逻辑运算

例1：查询年龄小于20的女同学

select * from students where age>(select avg(age) from students)

例2：查询王昭君的成绩，要求显示成绩

select studentNo from students where name=’王昭君’

select score from scores where studentNo=(select studentNo from students where name=’王昭君’)

列子查询：子查询返回的结果是一列多行
例3：查询18岁的学生的成绩，要求显示成绩

select studentNo from students where age=18

select * from scores where studentNo in(select studentNo from students where age=18)

行子查询：子查询返回的结果是一行多列
例4：查询男生中年龄最大的学生信息

select * from students where sex=’男’ and age=(select max(age) from students where sex=’男’)

select * from students where sex=’男’ and age=30

select * from students where (sex,age)=(‘男’,30)

select * from students
where (sex,age)=(select sex,age from students where sex=’男’ order by age desc limit 1)

表子查询：子查询返回的结果给另一个查询当作表来使用

例5：查询数据库和系统测试的课程成绩

select * from scores
inner join courses on scores.courseNo=courses.courseNo
where courses.name in (‘数据库’,’系统测试’)

select * from scores
inner join
(select * from courses where name in (‘数据库’,’系统测试’)) as c
on scores.courseNo=c.courseNo

查询演练

求所有电脑产品的平均价格,并且保留两位小数

select round(avg(price),2) from goods

查询所有价格大于平均价格的商品，并且按价格降序排序

select * from goods where price>(select round(avg(price),2) from goods) order by price desc

查询类型为’超极本’的商品价格

select price from goods where cate=’超级本’

查询价格大于或等于”超级本”价格的商品，并且按价格降序排列

select * from goods where price >=any(select price from goods where cate=’超级本’) order by price desc

创建类型表

create table goods_cates(
cate_id int unsigned primary key auto_increment,
cate_name varchar(10)
)

select distinct cate from goods
select cate from goods group by cate

插入数据，查询出来的字段需要和想插入的字段保持顺序一致
insert into goods_cates(cate_name) select distinct cate from goods

创建品牌表,并且插入数据，查询出来的字段名需要和想插入的字段名一模一样

create table goods_brands(
brand_id int unsigned primary key auto_increment,
brand_name varchar(10)
)
select distinct brand_name from goods

更新商品表的类型

select * from goods
inner join goods_cates on goods.cate=goods_cates.cate_name

update goods
inner join goods_cates on goods.cate=goods_cates.cate_name
set goods.cate=goods_cates.cate_id

更新商品表的品牌

update goods
inner join goods_brands on goods.brand_name=goods_brands.brand_name
set goods.brand_name=goods_brands.brand_id

查询商品信息，包括品牌、类型

命令行客户端

连接服务端
mysql -uroot -p 回车后，输入密码

显示所有仓库
show databases;

打开某个仓库
use ceshi;

显示当前仓库所有的表
show tables;

显示表结构
desc students;

中文乱码
set charset gbk;

函数

随机从一个表中取一条数据
select * from goods order by rand() limit 1

视图

创建视图

create view v_students as
select name as 姓名,sex 性别,age 年龄 from students

查询视图

select * from v_students

删除视图

drop view v_students

事务

转账

大乔 500
小乔 200

update set account=500-100 where name=’大乔’

update set account=200+100 where name=’小乔’

提交，所有步骤都执行成功，才提交

begin; 开启事务

update students set age=age-10 where name=’大乔’;

update students set age=age+10 where name=’小乔’;

commit; commit后，数据才变化到表中

回滚，任何一步失败，回到以前的状态

begin;

update students set age=age-10 where name=’大乔’;

update students setb age=age+10 where name=’小乔’;

rollback; rollback后，所有的操作全部失败，数据回到原始状态

索引

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

外键

从表的一列，被主表的一列约束，从表被约束的一列的值，必须在主表主表中那一列值的范围内

外键约束,会极大的降低表更新的效率

修改密码

在mysql仓库下执行

更新密码
update user set password=password(‘111’)

刷新权限
flush privileges

软件安装总结
解压 tar 作用：解压文件，获取真正的配置文件
配置 configure 作用：根据默认的配置项或者更改配置项，生成编译配置文件(Makefile)
编译 make 作用：根据 Makefile 内容，编译生成指定的软件所需要的所有文件
安装 make install 作用：将编译生成的所有文件，转移到软件指定安装的目录下面

nginx操作

启动nginx
/data/server/nginx/sbin/nginx

启动后检查
netstat -tnulp | grep nginx

mysql操作

启动数据库
service mysqld start

检查数据库启动状态
netstat -tnulp|grep mysqld

客户端连接服务端
mysq -uroot

php操作

启动php
/data/server/php/sbin/php-fpm

关闭php
pkill php-fpm

netstat -tnulp|grep php

一个网站运行需要的环境

一个服务器（linux系统）
web服务软件（nginx）
php软件
数据库（mysql）

网站要正常访问

nginx，php，mysql都启动

linux中的navicat过期后，删除/home/admin下的.navicat64文件夹即可

rm -rf /home/admin/.navicat64

1、linux中的navicat连接windows中的mysql服务端

输入ip、端口、用户名、密码

2、windows中的navicat连接linux中的mysql服务器

设置mysql支持远程连接

修改一个root用户的host为%

flush privileges

输入ip、端口、用户名、密码