mysql基础_老师讲解笔记

mysql基础_老师讲解笔记

---

头客上机笔记

1、初识MySql

1、创建数据库
mysql -uroot -p123123 -h127.0.0.1
show databases;
create database MyDb;

2、创建表
create database TestDb;
use TestDb;
create table t_emp
(
	id INT, 
	name varchar(32),
	deptId INT,
	salary FLOAT
);
DROP TABLE IF EXISTS t_emp;

3、使用主键约束
use MyDb;
create table t_user1
(
	userId INT primary key, 
	name varchar(32),
	password varchar(11),
	phone varchar(11),
	email varchar(32)
);

create table t_user2
(
	name varchar(32),
	phone varchar(11),
	email varchar(32),
	primary key(name,phone)
);

4、外键约束
use MyDb;
show tables;
create table t_class
(
	id INT primary key, 
	name varchar(22)
);

create table t_student
(
	id INT primary key, 
	name varchar(22),
	classId INT,
	CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id)
);
DROP TABLE IF EXISTS t_class;
DROP TABLE IF EXISTS t_student;
【先删子表，再删父表】

5、添加常见约束
use MyDb;
create table t_user
(
	id INT primary key AUTO_INCREMENT, 
	username varchar(32) not null UNIQUE,
	sex VARCHAR(4) DEFAULT '男'
);

---

2、数据库和表的基本操作一

1、查看表结构与修改表名

use MyDb;
show databases;
show tables;

查看数据表基本结构 语法规则为:DESCRIBE 表名;
查看数据表详细结构 语法规则为：SHOW CREATE TABLE 表名; 
修改表名 语法规则为：ALTER TABLE 旧表名 RENAME 新表名; 

ALTER TABLE tb_emp RENAME jd_emp;
show tables;
DESCRIBE jd_emp;

2、修改字段名与字段数据类型
修改字段名 语法规则为： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型; 
修改字段数据类型 语法规则为： ALTER TABLE 表名 MODIFY 字段名 数据类型; 

ALTER TABLE tb_emp CHANGE Id prod_id INT;
ALTER TABLE tb_emp MODIFY Name varchar(30);

3、添加与删除字段
添加字段 语法规则为： ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名; 
删除字段 语法规则为： ALTER TABLE 表名 DROP 字段名; 

ALTER TABLE tb_emp ADD Country varchar(20)  AFTER Name;
ALTER TABLE tb_emp DROP Salary;

4、修改字段的排列位置
修改字段的排列位置 语法规则为： ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;

ALTER TABLE tb_emp MODIFY Name varchar(25) FIRST;
ALTER TABLE tb_emp MODIFY DeptId INT AFTER Salary;

5、删除表的外键约束
删除表的外键约束 语法规则为： ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名; 

 ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;

---

3、数据库和表的基本操作二

1、插入数据
为表的所有字段插入数据 语法规则：INSERT INTO 表名 (字段名) VALUES (内容);

insert into tb_emp values
('1','Nancy','301','2300.00'),
('2','Tod','303','5600.00'),
('3','Carly','301','3200.00');

2、更新数据
更新表中指定的内容 语法规则为： 
UPDATE 表名 
SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 
WHERE 过滤条件; 

UPDATE tb_emp 
SET Name = 'Tracy', DeptId = '302', Salary = '4300.00' 
WHERE id=3; 

3、删除数据
删除表中的指定行 语法规则为： 
DELETE FROM 表名 
WHERE 条件语句; 

删除表中的所有行 语法规则为： DELETE FROM 表名; 
小提示： TRUNCATE TABLE 语句也可以用来删除表中的所有记录。但是与 DELETE 不同的是，TRUNCATE TABLE 语句直接删除的是表，而不是表中的内容，删除结束后还会重新创建一个表。所以它的执行速度会比 DELETE 语句快。 语法为：`TRUNCATE TABLE 表名;`

DELETE FROM tb_emp 
WHERE Salary>3000; 

---

4、单表查询一

1、基本查询语句
查询数据表中指定字段的内容 SELECT 基本语法规则为： SELECT 字段名 FROM 表名; 

SELECT Name,Salary FROM tb_emp;
SELECT * FROM tb_emp;

2、带IN关键字的查询
带IN关键字的查询 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,...); 

SELECT Name,Salary
FROM tb_emp
WHERE ID NOT IN (1);

4、带BETWEEN AND的范围查询
带BETWEEN AND关键字的查询 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2; 
带NOT BETWEEN AND关键字的查询 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 NOT BETWEEN n1 AND n2; 


SELECT Name,Salary
FROM tb_emp
WHERE Salary  BETWEEN 3000 AND 5000;

5、单表查询二

1、
使用通配符%模糊匹配数据内容  语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符%'; 。 其中 % 的位置可以根据需要在字符间变化。
使用通配符_模糊匹配数据内容 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符_'; 。 其中`_`的位置可以根据需要在字符间变化。

SELECT Name,Salary
FROM tb_emp
WHERE Name LIKE 'C%';

2、查询空值与去除重复结果
查询空值 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL; 
去除重复结果 语法规则为： SELECT DISTINCT 字段名 FROM 表名;

SELECT *
FROM tb_emp
WHERE DeptId IS NULL;

SELECT DISTINCT Name
FROM tb_emp;

3、带AND和OR的多条件查询
带AND关键字的多条件查询 语法规则为： SELECT 字段名 FROM 表名 WHERE 表达式1 AND 表达式2;
带OR关键字的多条件查询 语法规则为： SELECT 字段名 FROM 表名 WHERE 表达式1 OR 表达式2;

SELECT *
FROM tb_emp
WHERE DeptId='301' and Salary>3000;

SELECT  *
FROM tb_emp
WHERE DeptId = '301' OR DeptId = '303';

6、单表查询三

1、对查询结果排序 SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]]; 
指定排序方向 

SELECT *
FROM tb_score
where class_id=1
ORDER BY score DESC; 

2、分组查询
分组查询的单独使用 SELECT 字段名 FROM 表名 GROUP BY 字段名;

SELECT *
FROM tb_class
GROUP BY class_id;

第3关：使用 LIMIT 限制查询结果的数量
LIMIT的使用 SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;

SELECT * 
FROM tb_score 
ORDER BY score DESC
LIMIT 1,4;

7、使用聚合函数查询

第1关：COUNT()函数
COUNT()函数基本使用 select count(*/字段名) from 数据表;

select count(*) 
from tb_class;
select classid,count(*) 
from tb_class 
where classid=367;

第2关：SUM()函数
SUM()函数基本使用 select sum(字段名) from 数据表;

select sum(score) 
from tb_class;
select course,sum(score) 
from tb_class
where course='语文';

第3关：AVG()函数
AVG()函数基本使用  select avg(字段名) from 数据表;
 
select course,avg(score) 
from tb_class 
where course="语文";
select course,avg(score) 
from tb_class 
where course="英语";

第4关：MAX()函数
MAX()函数基本使用 select max(字段名) from 数据表;

select course,max(score) 
from tb_class 
where course="语文";
select course,max(score) 
from tb_class 
where course="英语";

5、MIN()
select course,min(score) 
from tb_class 
where course="语文";
select course,min(score) 
from tb_class 
where course="英语";

8、连接查询

1、内连接查询
内连接查询 表1 [inner] join 表2 on 表1.字段=表2.字段

SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student 
join tb_class 
on tb_student.class_id=tb_class.id;

2、外连接查询
外连接查询 表1 left/right [outer]  join 表2 on 表1.字段=表2.字段

SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student 
left join tb_class 
on tb_student.class_id=tb_class.id;
使用右外连接查询所有学生姓名和对应的班级
SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student 
right join tb_class 
on tb_student.class_id=tb_class.id;

第3关：复合条件连接查询

SELECT tb_student.name as studentName,tb_student.score,tb_class.name as className 
FROM tb_student 
left join tb_class 
on tb_student.class_id=tb_class.id
WHERE tb_student.score>90 
order by tb_student.score DESC;

9、子查询

第1关：带比较运算符的子查询
子查询 SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

1.查询大于所有平均年龄的员工姓名与年龄
SELECT name,age 
FROM tb_emp 
where age>(SELECT AVG(age) FROM tb_emp);


select Student.Sname
from Student,SC
where Student.Sid=SC.Sid and Cid=(select Cid from Course where Cname='数据库应用');


select SC.Sid
from SC
where Score >ANY (select Score from SC where Sid='1002');


sel