mysql笔记(二)--基本操作

文章目录

(一) mysql笔记–基本概念
(二) mysql笔记–基本操作
(三) mysql笔记–事务
(四) mysql笔记–索引
(五) mysql笔记–其他操作
(六) mysql笔记–锁

2.1 数据库与表

2.1.1 基本操作

• 创建 create database db default charset utf8; 删除 drop database db;
• 查看 数据库 show databases; 表 show tables;
• 进入数据库 use databasesName;
• 查看表描述 describe myuser; 或show columns from myuser;

1. 建表

	CREATE TABLE myuser( id VARCHAR(10),name VARCHAR(10),sex CHAR(1),src VARCHAR(20) );
	-- 指定引擎设置编码(innodb支持事务,myisam不支持)
	create table t1(id int, name char(10)) engine=innodb charset=utf8;

2. 清表与删表

delete from t1;  -- 清空表内容,但重新插入自增键仍从上一次开始
truncate from t1;  -- 速度更快,自增约束插入重1开始
drop table t1;  -- 删除表

3. 数值类型
   一共有三种,数字型,时间类型,字符串型

• 日期按照格式选择,数值与字符串按照大小,超出则会Out of range value报错,字符串类型定长往前放,不定长往后放提高速度,长度大于255使用text存
• 金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元)/decimal(类似字符串保存),千万不要直接用浮点,会有精度损失.
• 文件类型一般存在服务器的文件系统,数据库保存其路径,还有枚举类型,集合等等

4. 用户与授权

create user ‘用户名@’ip.%’ identified by ‘name’
drop ‘用户名@’ip.%’
grant 权限 on 数据库.表 to ‘用户’@’ip地址’
remove  *.*  from  -- 使用通配符

2.1.2 更改属性

1. 添加 ADD

	AlTER TABLE user2 ADD PRIMARY KEY(id,name);
	ALTER TABLE user5 ADD UNIQUE(name);

2. 删除 DROP

AlTER TABLE user2 DROP PRIMARY KEY;

3. 修改 MODIFY

AlTER TABLE user3 MODIFY id INT PRIMARY KEY;

2.2 插入

• 完整插入与半插

	INSERT INTO myuser VALUES('01','ls','1','www.rice.cn');
	INSERT INTO myuser(id,name) VALUES(02,’te’), (03,’te2’);
	INSERT INTO myuser(id,name) SELECT * FROM myuser2;

2.3 删除

	DELETE FROM myuser WHERE sex = '0';
	DELETE FROM myuser;  //全删

2.4 修改

	UPDATE myuser SET sex = '1' , age =’18’ WHERE name = 'ls';

2.5 查询与函数

• 非重复

	select distinct t_depart from teacher;  

• 区间 between

	select * from score where sc_degree between 60 and 80;  -- 大于等于小于等于


	select s_no,sc_degree,grade from score,grade 
		where sc_degree between low and upp;

• 集合in与union

	select * from score where sc_degree in(85,86,88); 
	select * from score where s_no in
	( select s_no from student where s_class = "95031");
	
	SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday  FROM student
	UNION
	SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher;

• 日期函数 now

	select year(s_birthday),month(s_birthday),day(s_birthday) from student;
	select s_name, year(now()) - year(s_birthday) age from student; //年龄计算

• 排序 asc

	select * from score order by sc_degree asc; //默认升序
	select * from score order by sc_degree desc; //降序
	
	//以班级升序,相同则以成绩降序
	select * from score order by c_no asc, sc_degree desc; 
	返回最大值 max
	select max(sc_degree) from score;  
	select max(s_no) max,min(s_no) min from student;
	选择区间条目 limit
	select * from student limit 0 , 2; //选择 起始行,取的行数

• 平均值 avg

	select avg(sc_degree) from score;

• 任意值any与全部all

1. 查询3-105课号中大于3-245课号中成绩最低的

	select * from score where c_no = '3-105' AND 
		sc_degree > any(select sc_degree from score 
			where c_no = '3-245' ) 

2. 查询3-15课号中都大于3-245课号中成绩最高的

	select * from score 
		where sc_degree > all (select sc_degree from score 
			where c_no = '3-245') 
		and c_no = '3-105';

• 分组 group

	select c_no,avg(sc_degree) from score group by c_no; //每个班的平均分

• 模糊查询 like

	select *  from score where  c_no like '3%'; //多个字符
	select *  from score where  c_no like '3_'; //单个字符

• 过滤 having

	-- where行级过滤,having组级过滤,与分组搭配使用
	select c_no,avg(sc_degree)  from score group by c_no having count(c_no) > 2;

• 多表查询与嵌套查询

1. 连表

	select s_name,sc_degree from student,score
	       where student.s_no = score.s_no;

	select s_name,c_name,sc_degree from course,score ,student
	     where  course.c_no = score.c_no
	     and student.s_no = score.s_no;

2. 找出95031班的成绩

	select * from score 
		where s_no in ( select s_no from student where s_class = "95031");

3. 各班平均分

	select c_no,avg(sc_degree) from score 
		where s_no in( select s_no from student where s_class = "95031") 
		group by c_no;

• 子查询

1. 查询选修3-105大于109号学生成绩的

	select *  from score
		where c_no = '3-105' 
		and sc_degree > (select sc_degree from score 
			where s_no = '109' and c_no = '3-105');

2. 查询’张旭’老师任课的学生成绩

	select * from score
	      where c_no in (
	      select c_no from course
	      	where t_no = (select t_no from teacher where t_name = '张旭'));

3. 查询选课人数大于5的教师名

	select * from teacher
		where t_no in(select t_no from course 
			where c_no in (
				select c_no from score group by c_no having count(s_no) > 5));

4. 求低于该科平均分的

	select * from score a 
		where sc_degree < (select avg(sc_degree) from score b where a.c_no = b.c_no);

2.6 连接

2.6.1 内连接

	inner join 或 join , 即两张表中的数据通过某个字段查询出相关记录
	select * from person inner join card on person.cardId = card.id;

    

2.6.2 外连接

• 左连接 left join 或 left outer join,无相等则补NULL

  

• 右连接 right join 或 right outer join,无相等则补NULL
  

• 完全外连接 full join 或 full join outer join
      mysql不支持full join 但可以使用左右连接然后使用union即可