Mysql入门笔记

查看数据库表结构

• 查询所有的表结构，前提条件，先切换到数据库中

show tables;

• 查询表结构的详细的信息

desc tableName;

• 查看表结构定义的信息

show create table tableName;

---

删除表

• 删除表结构

drop table tableName;

---

修改表（alter）

• 添加一个新的字段

alter table 表名称 add 字段名称 数据类型(长度) 约束;

• 修改字段的数据类型，长度或约束

alter table 表名称 modify 字段名称 数据类型(长度) 约束;

• 删除某一个字段

alter table 表名称 drop 字段名称;

• 修改字段的名称

alter table 表名称 change 旧字段 新字段 数据类型(长度) 约束;

• 修改表的名称

rename table 旧表名 to 新表名;

---

插入数据（insert）

• 添加数据，使用insert关键字来添加数据

insert into 表 (字段1, 字段2, 字段3) values(值1, 值2, 值3); 
#向表中的指定的字段中添加值

insert into 表 values (值1, 值2, 值3, 值4, ……);
#向表中的说有的字段添加值

• 注意事项
  插入的数据与字段数据的类型相同
  数据的大小应该在规定的范围内
  数据中的数据的列的位置和字段位置是相同的
  字符串和日期类型的数据，必须要使用单引号括起来

修改数据语句（update）

• 修改数据，使用update关键字来完成修改数据

update 表名称 set 字段1=值1, 字段2=值2  where 条件;

• 注意
  如果没有where的关键字，说明修改的默认所有的记录
  如果有where的关键字，修改的是符合条件的数据

---

删除数据（delete）

• 删除语句的语法

delete from 表 where 条件;
#如果没有where的关键字，说明默认删除所有的数据
#如果有where的关键字，删除的是符合条件的数据 

• 删除所有的数据

delete from 表;
#一行的数据
#支持事务的操作，事务是数据库中的特性  (start transaction; ------rollback; )
#											开启事务		    	回滚	

truncate 表;
#先把整个表删除掉（数据也删除了），创建一个与原来一模一样的表；

查询数据（select）

• 查询语句的语法

select * from 表;							--默认查询所有的字段的数据

select 字段1, 字段2, 字段3  from 表;			--查询指定的字段的数据

 select distinct 字段 from 表;				--去除重复的关键字

• 可以对查询的列进行运算
  例：select 字段名+10 from 表

查询语句中可以使用as的关键字，其别名

• 别名的真正的用法，采用的多表查询，为了区分每张表，表起个别名
• as的关键字可以省略不写，中间需要使用空格
• 一般都是给表起别名

使用where的条件语句，进行查询条件的过滤

---

where句子后可以使用的符号

• 常用的符号

>  <  <=  >=   <>(不等于)

in 表示范围

select * from 表 where 字段 = 22;			--查询一条数据，条件是字段=22
select * from 表 where 字段 in(2, 33,42);	--查询的结果可以是多条数据，字段需要在in值的范围内

like 模糊查询

• like关键字的值的写法

select * from 表 where 字段 like ‘张_’; 
--使用_占位符，结果：张飞、张三、张X、张翼德（不符合）

select * from 表 where 字段 like ‘张%’
--使用%位符，结果：张飞、张三、张X、张翼德，只要是姓张的都可以

select * from 表 where 字段 like ‘%张’;
--结果：以张结尾的

select * from 表 where 字段 like ‘%张%’;
--结果只要包含张字的

• 总结like的关键字
  • 条件需要使用单引号
  • 需要使用占字符
    1. _ 代表的一个位置
    2. %代表的多个位置

and			--与
or			--或
not			--非
between 范围1 and 范围2 --范围之间

使用order by 对结果进行排序

1. 语法

order by 字段 asc/desc;

	asc			--代表升序（默认值）
	desc		--代表降序

2. 注意
   order by 直接放在select的句尾

select * from xx where xx order by xx;

3. 练习

• 对数学成绩排序后输出

select username, math from stu order by math asc;
select username, math from stu order by math desc

• 对总分排序按从高到低的顺序输出

select username, (math+Chinese+english) from stu order (math+Chinese+english) desc;
select username, (math+Chinese+english) as t from stu order t desc;

• 对学生成绩按英语进行降序排序，英语成绩相同的同学按数学降序排序

select username English, math from stu order English desc, math desc;

聚集函数

1. 什么是聚集函数：Excel表格求数量、求和、求平均数、最大值、最小值
2. 聚集函数操作的都是某一列的数据
3. 聚集函数
   count() – 求数量

Select count(*), count(列名) from表;

练习

• 统计一个班共有多少学生？

select count(*) from stu;
select count(id) from stu;

• 统计数学成绩大于90的学生有多少个？

select count(math) from stur where math>90；
select username count(id) from stu where math>90;    --不会这样查询统计总分大于220的学生有多少个？
select count(*) from stu where (math+English+Chinese)>220;

sum() – 求某一列数据的和
sum 注意没有sum(*)，求某一列，sum对数值类型起作用

• 统计一个班数学总成绩？

select sum(math) from stu;

• 统计一个班语文、数学、英语各科的总成绩？

select sum(chinese), sum(english), sum(math) from stu;

• 统计一个班语文、数学、英语的总成绩？

select sum(Chinese+English+math) from stu;
select sum(chinese)+sum(english)+sum(math) from stu;

注意的地方

1. sum()函数可以忽略null值
2. 编写一条修改语句

Update stu set math = null where username='小花';

avg() – 求平均值

select avg(字段) from stu;

练习

• 求一个班级语文的平均分？

select avg(Chinese) from stu;

• 求一个班级的总平均分？

select avg(Chinese+English+math) from stu;

最大值和最小值
max() / min()

练习

• 求班级最高分和最低分

select max(Chinese+English+math) from stu;
select min(Chinese+English+math) from stu;

4. 总结
   聚焦函数，是函数，不要忘记编写()
   计算都是某一列的数据
   聚焦函数
   Count() --求数量
   Sum() --求和
   Avg() --平均值
   Max() --最大值
   Min() --最小值

分组查询概念(group by … having …)

1. 分组查询的效果？聚焦函数与分组效果

2. select * from stu; --查询所有的数据，默认是一组

3. 可以使用关键字 group by 字段 根据字段进行分组

4. 练习

create table orders(
	id int,
	product varchar(20),
	price float);
insert into orders (id, product, price) values(1, '电视', 900);
insert into orders (id, product, price) values(2, '洗衣机', 100);
insert into orders (id, product, price) values(3, '洗粉', 40);
insert into orders (id, product, price) values(4, '电视', 900);
insert into orders (id, product, price) values(5, '洗衣机', 90);
insert into orders (id, product, price) values(6, '洗衣机', 90);

• 对订单商品归类后，显示商品的总价？

select product，sum(price) from orders group by product;		--已经把原来的一组数据分成了三组，在统计数据的时候，一组为单位

• 查询购买了几类商品，并且每类总计大于100的商品？

select product sum(price) from orders group by product having sum(price)>100;

• 查询购买了几类商品，商品价格必需大于100，并且每类总计大于100的商品？

select product sum(price) from orders where price>100 group by product having sum(price)>100;

注意

• 使用where的条件，如果有分组，where的条件是分组之前的条件
• 新关键字，having关键字进行分组条件的过滤

6. 总结
   where关键字后不能使用聚集函数，而having可以使用聚集函数

单表约束

1. 可以把某一列的字段声明为主键，这一列的数据有如下特点
   非空
   唯一
   被引用 --> 当前主键的列，作为一条记录的标识

2. 声明主键
   使用关键字 primary key 声明某一列字段为主键

3. 测试主键
   创建新的数据

create database mydb1;
use mydb1;
create table person(
		id int primary key,
		username varchar(20)
		);

添加数据(insert)

insert into person values(1,'美美');
insert into person values(2,'小风');

4. 主键自动增长
   主键的特点，可以把主键的值交给数据库去维护
   自动增长只用于int 和 bigint 类型
   通过关键字auto_increment

演示自动增长

drop table person;
create table person(
		id int primary key auto_increment,
		username varchar(20)
		);

添加数据

insert into person values(null,'美美');
insert into person values(null,'小风');
insert into person values(4,'小花');

如果删除了一条记录

delete from person where id=2;

5. 总结
   开发中，主键基本上是必须要设置的

唯一和非空约束(unique、not null)

1. 唯一约束
   可以把某一个字段声明成唯一的值
   使用该关键字unique

2. 非空
   可以把某个字段声明成非空的
   值是不能我空值
   使用关键字not null

3. 练习

create table person2(
		id int primary key auto_increment,
		username varchar(30) unique,
		email varchar(30) not null
);

多表约束

1. 外键的约束：目的是保证表结构中数据的完整性
2. 模拟的过程
3. 有一个部门表，还有一个员工表，一个部门下有多个员工
4. 创建表结构

create table dept(
		did int primary key auto_increment,
		dname varchar(20)
);

create table emp(
		eid int primary key auto_increment,
		ename varchar(20),
		sal double,
		dno int
);

create table emp(
		eid int primary key auto_increment,
		ename varchar(20),
		sal double,
		dno int,
		foreign key (dno) references dept (did)
);

向部门表添加一些数据

insert into dept values(1,'人事部');
insert into dept values(2,'研发部');

像添加几个员工？员工属于哪一个部门？

insert into emp values(1,'美美', 16000,1);		--美美员工属于人事部
insert into emp values(2,'小风', 15180,2); 		--小风员工属于研发部

想直接删掉部门

delete table dept where did=1;

部门可以直接删除 需要使用外键对两张表做约束

直接删除掉部门，程序可以删除的，但是不合理，通过设置外键的约束来避免这一类的问题发生
保证数据的完整性

外键多表的关系
使用关键字 foreign key (dno) reference dept (did);
相当于dno 字段作为emp表的外键，值是从dept表中did取值

演示
修改emp表的信息

alter table emp add foreign key (dno) references dept (did);

再删除某个部门，如果该部门下有员工，删除是不成功的，如果没有员工，可以删除部门

delete from dept where did=1;

多表的查询（重要）

1. 了解笛卡尔积（两个结果的乘积）
   假如说是有两个表A和表B

A                       B
aid	aname				bid	bname	aid
a1	a11					b1	b11		a1
a2	a22					b2	b22		a2
						b3	b33		a1

select * from A,B; 查询的结果产生的结果就是笛卡尔积
结果:

a1 a11 b1 b11
a1 a11 b2 b22
a1 a11 b3 b33
a2 a22 b1 b11
a2 a22 b2 b22
a2 a22 b3 b33

使用部门和员工两个表演示笛卡尔积

select * from dept, emp;

多表查询之内链接

• 前提条件:两个表有联系，通过外键关联。
  普通内链接
  语法:关键字... inner join ... on条件;
  注意:
  在inner join关键字之前写表1
  在inner join关键字之后写表2
  on的后面写条件: ( 表1是dept，表2是emp) dept.did = emp. dno
  语句:

select * from dept inner join emp on dept.did=emp.dno;

• 隐式内链接(用的最多的)
  语法: select * from 表1,表2 where表1.字段=表2.字段;
  语句:

select * from dept, emp where dept.did = emp. dno;

别名:

select * from dept d, emp e where d.did = e. dno;

指定字段:

select d.dname ,e.ename,e.sal from dept d,emp e where d.did = e. dno;

多表查询之外链接

外链接又分成左外链接和右外链接

• 左外链接
  语法: ... 表1 left outer join表2 on表1.字段=表2.字段
  语句:

select * from dept left outer join emp on dept.did = emp. dno;

特点:看左表，默认把左表中的全部数据都查询出来，再查询出有关联的数据。

• 右外链接
  语法: ... 表1 right outer join表2 on表1.字段=表2.字段
  语句:

select * from dept right outer join emp on dept.did = emp.dno;

特点:看右表，默认把右表中的全部数据都查询出来，再查询出有关联的数据。

子查询

1. 子查询，嵌套查询，一个select语句不能查询出结果的,可以通过多个select语句来查询结果。
2. 例子：查询出英语成结大于英语平均分的同学?
   先计算出英语的平均分( select avg(english) from stu; )
   再编写select语句

select usernane,english from stu where english >(select avg(english) from stu);