mysql笔记

声明

学习内容来自 "编程俱乐部"

数据库登录

mysql -uroot -p123456		// root 为用户名， 123456为用户密码

数据库退出

exit

查询数据库服务器中的数据库

show databases;

在数据库服务器中创建数据库

create database test;		// test 为创建的数据库名称

选中相应的数据库

use databasename;		// databasename 则是数据库名

显示数据库中的数据表

show tables;

创建一个数据表

create table pet (name varchar(20), owner  varchar(20), species varchar(20), sex char(1), birth date, death date);		
// pet 为 数据表名
// 括号内的 每一个 “，”  符号 ，表示一个数据字段， 前面为 数据字段名称，后面为数据字段的数据类型

查看数据表的结构

describe pet;		// pet 为数据表名称

查看表中的记录

select * from pet;		// * 表示所有的字段， pet 为数据表

添加数据记录

insert into pet values ( 'heer', 'wuyz', 'haster', 'f', '1999-03-30', null);
// pet 表示 插入数据的表名称
// 括号内的数据要与数据表的结构相对应

insert into pet (name, id) values('zhangsan', 1);
//指定字段的插入，其他为默认值。

数据类型

数值，日期 和字符串

删除数据

delete from pet where name='herr';
// pet 为 数据表名称，
// name 为 字段项
// 'herr' 为字段项里的一个值

更新数据项

update pet set name='wangwangcai' where owner='wuyz';
// pet 为 数据表名称
// name 为 数据字段
// wangwangcai 为 要修改的值
// owner 为该条记录里面的其他字段
// wuyz 为该条记录里面的其他字段值

mysql 建表约束

主键约束

主键约束( primary key )：能够唯一确定一张表中的一条记录。也就是我们通过某个字段添加约束，就可以使得该字段不重复且不为空。
	例如  create table user( id int primary key, name varchar(20) );
	// primary key, 修饰 id 为表的主键约束
联合主键约束：将两个字段当作该表的约束。只有当两个字段相同才会不能增加记录。
	例如： create table user ( id int, name varchar(20), password varchar(20), primary key(id, name);
	//这时 id 和 name 字段成为联合主键约束
自增约束( auto_increment )：配合主键约束一起使用，自行增加。
	例如： create table user( id int primary key auto_increment, name varchar(20) );
	// auto_increment 表示自增， 自增是在已有的最后一个数值 加一。中间如果有空余的id值也不会补充。

添加与删除主键约束

alter table user add primary key(id); 
// 添加 user 表中 字段 id 为 主键约束

alter table user drop primary key;
//删除主键约束

alter table user modify id int primary key;
// 修改字段为主键约束

唯一约束

唯一约束：修饰该字段的值不可以重复。

增加唯一约束

	例如：create table user( id int, name varchar(20) unique );
	或是：create table user( id int, name varchar(20), unique(name) ); 
	或是：create table user( id int, name varchar(20) );
				alter table user add unique(name);
	或是： create table user( id int, name varchar(20) );
				alter table user modify name varchar(20) unique;

联合唯一约束

两个字段同样时，才会约束。
例如： create table user( id int, name varchar(20), unique(id, name) );
		//在插入时时，插入值只有当 id 和 name 与数据表中的某条记录的id 和 name 相同，才会收到约束。

删除唯一约束

alter table user drop index name;
// name 为字段

非空约束

非空约束：修饰的字段不能为空。
例如： create table user( id int, name varchar(20) not null );

默认约束

默认约束：当我们插入字段值时，如果没有传入值，就会使用默认值。
例如：create table user(id int primary, name varchar(20) not null default 'lisi' );
// name 的默认字段为 ‘lisi'

外键约束

外键约束：牵扯到主表和副表，
	1.主表中不存在的数据在副表中不可以使用。
	2.主表中数据被副表使用，主表中的数据是不可以删除。
例如： create table classes( id int primary key, name varchar(20) );
			create table students( id int primary key, name varchar(20) not null default 'lisi', class_id int, foreign key(class_id) references classes(id);

数据库的三大设计范式

第一范式( 1NF )

第一范式：数据表中的所有字段都是不可分割的原子。
例如：字段值还可以继续拆分，则不满足第一范式。
			范式设计得越详细，对于某些操作有好处，但是不一定全部都是好处。

第二范式( 2NF )

第二范式：满足第一范式的前提下，除主键外，每一字段的都必须完全依赖于主键。
例如：如果出现不完全依赖，只可能发生在联合主键的情况下，这时候要差分为三个表。

第三范式( 3NF )

第三范式：满足第二范式的前提下，除主键字段外，其他字段之间不能有传递依赖。

查询操作

查询所有记录

select * from  student;
// * 表示所有
// student 是查询的表

查询指定字段

select id, name from student;
// id  和 name 都为字段，使用 “,” 隔开
// student 是查询的表 

查询指定字段并且排重

select distinct depart from teacher;
// distinct 表示 不重复的
// depart 指定字段
// teacher 是查询的表

同一字段查询区间

select * from score where degree between 60 and 80;
 	// * 表示查询所有
	// 条件为 degree字段的分数在 60 到 80 之间
select  id, name from score where degree between 60 and 80;
	//查询显示 id,name 来自 score 表，且 degree 字段值为 60 到 80；
select * from score where degree < 60 or degree > 80;
	// 显示所有，来自 score 表， 字段degree 值小于60 或是 大于 80；

同一字段 或的关系

select * from score where degree=88 or degree=90 or degeree=70;
select * from score where degree in( 88, 90, 70);
	//显示所有，来自score 表，字段 degree值等于 88 或是 90 或是 70 

不同字段 或的关系

select * from score where degree in( 88, 90, 70 ) or id=10;
	// 显示所有 来自 score表，字段 degree 值为 88，90，70 或是 字段id 值为 10；

不同字段 且的关系

select * from student where sex='女' and class='95031‘；
	// 显示所有来自 student 表，字段 sex 值为 '女' 且 class 字段为 '95031'

某一字段 降序 和 升序

	descend : 翻译降序
	ascend : 翻译升序
select * from student order by class desc;
	// 显示所有来自student 表， 按照 class 字段 降序排序
select * from student order by class asc;
	//显示所有来自student 表， 按照 class 字段 升序排序

两种字段 合并的 降序 和升序

默认情况下是以 primary key 升序来排列
select * from score order by cno asc;
	// 主要以 cno 字段升序，接着以 primary key 来 升序
select * from score order by cno asc, degree desc;
	// 主要以 cno 字段升序，接着 degree 字段降序

统计个数

select count(*) from student;
	// 统计 student 表的个数
select count(*) from student where class='95031';
	//统计 student 表 class 字段为 ‘95031’的 个数

最大最小

select max(degree) from score;
	//来自 score 表 字段degree 最大值
select min(degree) from score;
	//来自 score 表 字段 degree 最小值

子查询

select sno, cno from score where degree=( select max(degree) from score );
	// 显示字段 sno，cno 来自score 表， 且 degree 字段 等于 最大值
select sno, cno from score order by degree desc limit 0,1;
	// limit 0,1 表示 从下标 0 开始， 查找 1条记录。
	//显示字段 sno，cno 来自 score 表，并且 以 degree 字段 降序排序 且 从位置 0 开始选择 1 条记录。

计算平均值

select avg(degree) from score where cno='3-105';
	//计算 degree 的平均值 来自 score 表， 且 cno 字段 为 ‘3-105'
select cno from score group by cno;
	//显示 cno 字段 来自 score 表， 且以 cno 字段分组
select cno,avg(degree) from score group by cno;
	//计算 degree 字段的平均值 来自 score 表，且 cno 字段的不分组计算。

分组条件( group by … having) 以及模糊查询(like)

select cno from score group by cno having count(cno)>=2;
	//显示 cno 字段 来自 score 表，通过 cno 字段分组 且 分组数值大于等于 2；
select cno from score group by cno having count(cno)>=2 and cno like '3%';
	//  cno like '3%' 表示 cno 字段 以  3开头
select cno,avg(degree),count(*)  from score group by cno having count(cno)>=2 and cno like '3%';
//显示 cno字段，degree 平均值，平均值个数， 来自 score 表 通过 cno 字段分组 ，分组条件 cno 字段数大于等于 2 且 cno 字段 值 以 3 开头。

多表查询

select sname, cno, degree from student, score where student.sno=score.sno;
	// 当查询两个表中的两种字段以上时，两个表可定是外键约束的关系，即条件加上  where student.sno = score.sno ,表示 两个表中的 字段值相等，其中 from 后面的两个表明位置随意。

select sno, cname, degree from course, score where course.cno=score.cno;
// 三表查询

select sname, cname, degree, score.cno, score.sno from student, course, score where student.sno=score.sno and score.cno=course.cno;
//其中 显示 cno 和 sno 要带上表名，消除二义性

取别名

select sno as key_sno, cno as key_cno, degree from score;
	// as 表示取别名

子查询加分组求平均

select cno, avg(degree) from score where sno in( select sno from student where class='95031' ) group by cno;
	// 显示 cno， degree 字段平均值 来自 score 表，条件为 来自表 student, class 字段为 ’95031‘ 的 sno 字段 值  通过 cno 字段进行分组。 

year 函数 和 in 关键字子查询

// year(), month(),day(), hour()
select year(sbirthday) from student where sno in( 108, 101);
	// 显示 sbirthday 字段 年份 来自 student 表， 条件为 sno 字段 为 108 或 101；
select * from student where year(sbirthday) in ( select year(sbirthday) from student where sno in(108, 101 );

union 关键字

select * from teacher where depart='计算机系' and prof not in( select prof from teacher where depart='电子工程系' ) union select * from teacher where depart='电子工程系' and prof not in( select prof from teacher where depart='计算机系‘ );
	// union 将两个查询得语句合并

any 关键字(任意一个)

select * from score where cno='3-245' and degree > any( select  degree from score where cno='3-105' );
	// 显示 所有 来自 score 表， cno 字段为 ’3-245‘ 且 degree 字段大于 任意一个 来自 score 表 cno 字段为 ’3-105‘的字段值

all 关键字( 所有)

select * from score where cno='3-105' and degree > all( select degree from score where cno='3-245' ) order by desc;

复制数据表做条件查询

select * from score a where degree < ( select avg(degree) from score b where a.con=b.con );

where 条件 要在 group by … having … 前面

select class, count(class) from student where ssex='男' group by class having count(class) >= 2;

year() 和 now() 求年龄

select sname, year( now() ) - year(sbirthday) as age from student;
	// 显示姓名 和 年龄 来自 student 表

两表查询，按等级

select sno, cno, grade from score, grade where degree between low and upp;

SQL 四种连接查询

内连接

内连接：inner join 或则 join
	两张表中的数据，通过某个字段相对，查询出相关记录；
select * from person inner join card on person.cardId=card.id;
select * from person join card on person.cardId=card.id;

外连接

左连接：left join 或者 left outer join

左连接：把左边表里面的所有数据显示出来，而右边表中的数据，如果与左相等就显示，没有则补 NULL.
select * from person left join card on person.cardId=card.id;

右连接：right join 或者 right outer join

右连接：把右边表里面的所有数据显示出来，而左边表中的数据，如果与右相等就显示，没有则补 NULL
select * from person right join card on person.cardId=card.id;

完全外连接：full join 或者 full outer join

事务

第一种事务提交

mysql 默认开启事务，即执行select 语句，是立即提交的，可以使用 select @@autocommit；查询。
事务回滚：撤销mysql执行效果。  rollback; 
set autocommit=0; 即取消立即执行，则事务就可以回滚
事务提交：提交后是不能回滚。 commit;

第二种事务提交

begin; 或是 start transaction; 开启事务
遇到 rollback 或是 commit;   结束开启事务。

ADIC的特性与使用

A: 原子性，事务是最小的单位不可分割。
C：一致性， 同时事务中的mysql语句，必须同时成功或是同时失败
I: 隔离性， 事务1 和事务2之间是具有隔离性。
C: 持久性，事务一旦结束就不可以返回。

隔离性

1. read uncommitted;	//读未提交
2. read committed;		//读已经提交
3. repeatable read; 	//可以重复读
4. serializable;		//串行化

//查看隔离级别
select @@global.transaction_isolation;
// 修改隔离级别
set global transaction isolation level read uncommitted;
脏读：一个事务读到另外一个事务没有提交的数据。隔离性在 read uncommitted 模式下。
不可重复读：虽然只能读到另外一个事务提交的数据，但是重复读取一个表的数据，发现前后数据不一致。 隔离性在 read committed 模式下。
幻读：事务a 和事务b同时操作一张表，事务a提交的数据，有可能事务b读取不到，照成幻读，在 repeatable read 模式下。
serializable 模式下： 当表被另外一个事务操作的时候，其他事务里面的写操作是不可以进行，写操作会进入排队状态，等待第一个事务操作结束，（没有等待超时）才会执行写操作。