自整理的一些MySQL基本操作

基本语句与一些笔记：

 

# 查看所有数据库
SHOW DATABASES;


# 创建数据库
CREATE DATABASE school;


# 使用该数据库
USE school;


# 查看数据库中所有表
SHOW TABLES;


# 创建表
CREATE TABLE IF NOT EXISTS course(
	c_id INT PRIMARY KEY AUTO_INCREMENT,
	c_name VARCHAR(20),
	c_weight INT
)DEFAULT CHARSET=utf8;

# 往表中插入值
INSERT INTO course(c_name,c_weight) 
	VALUES('高等数学',4),('大学英语',8),('C语言',2);
	
	
	
CREATE TABLE IF NOT EXISTS exam(
	s_id INT,
	c_id INT,
	score INT,
	grade NCHAR(10),
	PRIMARY KEY(s_id,c_id)
);


INSERT INTO exam(s_id,c_id,score,grade) 
	VALUES(1,1,89,'良好'),(1,2,58,'不及格'),(1,3,72,'中等'),(2,1,62,'及格'),(2,2,98,'优秀'),
	(2,3,73,'中等'),(3,1,53,'不及格'),(3,2,91,'优秀'),(3,3,82,'良好');
	

# 删除操作	
DELETE FROM student
	WHERE s_name = 'maqi';
	
# 更改（更新）操作
UPDATE student
	SET s_age = 58
	WHERE s_name = 'wangwu';
	
	
#查询
	
SELECT s_name,s_age FROM student;
	
SELECT * FROM student
	WHERE s_age > 30 and s_age < 50;
	
SELECT s_name,s_age FROM student
	WHERE s_age > 30 and s_age < 50;
	
	
	
# 排序

//从小到大
SELECT * FROM student
	ORDER BY s_age;
	
# 从大到小
SELECT * FROM student
	ORDER BY s_age DESC;

	
# 范围LIMIT
SELECT * FROM student
LIMIT 3;   # 从开始位置显示3个

SELECT * FROM student
LIMIT 2,3;  # 从第2个位置开始显示3个



# 模糊查询
SELECT * FROM student
WHERE s_name LIKE 'zh%';    # 用%表示 不确定的内容

# 唯一查询
SELECT DISTINCT s_age FROM student;

# IN 和 NOT IN 查询
SELECT * FROM student
WHERE s_name IN ('zhangsan','wangwu','maqi');

SELECT * FROM student WHERE s_name NOT IN ('zhangsan','wangwu','maqi');

# 别名    只是显示出来是这个别名，在表里面还是原来的(s_name,s_age)
SELECT s_name AS 姓名,s_age AS 年龄 FROM student;





# 唯一操作
SELECT DISTINCT name FROM student1
WHERE name NOT IN (
	SELECT name FROM student1
	WHERE score < 80
);



SELECT teamA.name AS team1,teamB.name AS team2 FROM team AS teamA,team AS teamB
WHERE teamA.name <> teamB.name;



# 等值连接
SELECT * FROM exam
INNER JOIN student
ON exam.s_id = student.s_id
INNER JOIN course
ON exam.c_id = course.c_id;


SELECT s_name,c_name,score,grade FROM exam
INNER JOIN student
ON exam.s_id = student.s_id
INNER JOIN course
ON exam.c_id = course.c_id;

# 创建班干部表：
CREATE TABLE leader(
	l_id INT PRIMARY KEY AUTO_INCREMENT,
	s_id INT,
	l_desc VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO leader(s_id,l_desc) VALUES
	(1,'班长'),(3,'学习委员'),(4,'体育委员');

	
	
# 左连接
SELECT * FROM student
LEFT JOIN leader
ON student.s_id = leader.s_id;

SELECT s_name,s_age,l_desc FROM student
LEFT JOIN leader
ON student.s_id = leader.s_id;


# 修改表的字段：

ALTER TABLE student
ADD sex VARCHAR(10);    # 增加表的字段

ALTER TABLE student
CHANGE sex new_sex ENUM('m','f');    # 修改表的字段，ENUM为枚举类型，只能修改为m或f

ALTER TABLE student
DROP COLUMN new_sex;      # 删除表的字段



CREATE DATABASE IF NOT EXISTS fiction; 

CREATE TABLE IF NOT EXISTS book(
	b_id INT PRIMARY KEY AUTO_INCREMENT,
	b_name VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO book(b_name) VALUES('射雕英雄传');

CREATE TABLE IF NOT EXISTS hero(
	h_id INT PRIMARY KEY AUTO_INCREMENT,
	h_b_id INT,
	h_name VARCHAR(20),
	CONSTRAINT fk_BookHeros    # 这个约束的别名
	FOREIGN KEY(h_b_id)		   # 外键
	REFERENCES book(b_id)      # 参考于
)DEFAULT CHARSET=utf8;

INSERT INTO hero(h_b_id,h_name) VALUES(1,'欧阳锋');


# 动态添加和删除外键约束
ALTER TABLE hero
DROP FOREIGN KEY fk_BookHeros;

# 添加约束之前，需要删除不约束的内容
# 动态添加约束
ALTER TABLE hero
ADD CONSTRAINT fk_BookHeros
FOREIGN KEY(h_b_id)
REFERENCES book(b_id);




ALTER TABLE hero
ADD CONSTRAINT fb_BookHeros
FOREIGN KEY (h_b_id)
REFERENCES book(b_id)
# 级联:修改book表中的b_id时，连同hero的h_b_id一同修改,CASCADE:级联
ON UPDATE CASCADE    # 同时更新
ON DELETE CASCADE;    # 同时删除


UPDATE book SET b_id = 2 WHERE b_id = 1;


SHOW CREATE TABLE hero;   # 可以查询是怎么创建这个表的


# 给exam表的s_id、c_id添加约束
ALTER TABLE exam
ADD CONSTRAINT Student_Exam
FOREIGN KEY (s_id)
REFERENCES student(s_id)
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE exam
ADD CONSTRAINT Course_Exam
FOREIGN KEY (c_id)
REFERENCES course(c_id)
ON UPDATE CASCADE
ON DELETE CASCADE;



CREATE TABLE IF NOT EXISTS score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	score INT
)DEFAULT CHARSET=utf8;

INSERT INTO score(name,score) VALUES
('zhangsan',72),
('lisi',91),
('wangwu',55),
('zhaoliu',62),
('maqi',38),
('niuba',29),
('chenjiu',71);


select name,score,if(score>60,'pass','fail') as mark from score;

 

CREATE TABLE orders(
	orderid INT PRIMARY KEY AUTO_INCREMENT,
	orderdate DATE,
	orderprice INT,
	customer VARCHAR(20)
)DEFAULT CHARSET=utf8;


INSERT INTO orders(orderdate,orderprice,customer) VALUES
('2020-07-25',1000,'zhangsan'),
('2020-07-23',1600,'lisi'),
('2020-07-28',700,'zhangsan'),
('2020-07-28',300,'zhangsan'),
('2020-07-26',2000,'wangwu'),
('2020-07-27',100,'lisi');


# 分组
SELECT customer, sum(orderprice) from orders
GROUP BY customer;


# 对多个列分组
SELECT orderdate,customer, sum(orderprice) from orders
GROUP BY customer,orderdate;


SELECT customer, sum(orderprice) AS sum FROM orders
GROUP BY customer
HAVING SUM >= 2000;



# 事务：Transaction
# 回滚: RollBack
# 事务的4个基本特征：ACID
# Atomic：原子性。要么都执行，要么都不执行。
# Consistency：一致性。事务前后，数据总额一致
# Isolution：隔离性。在事务执行完毕前，其他线程（会话）不会抢占
# Duration：持久性。一旦事务提交，数据的改变是持久的

USE transaction;

CREATE TABLE IF NOT EXISTS test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
)DEFAULT CHARSET=utf8;

BEGIN;

INSERT INTO test(name) VALUES('zhangsan'),('lisi');

COMMIT;
# or ROLLBACK;

# 查看表创建的属性，引擎ENGINE=InnoDB
SHOW CREATE TABLE test;   




CREATE TABLE user(
	u_id INT PRIMARY KEY AUTO_INCREMENT,
	u_name VARCHAR(20),
	u_passwd VARCHAR(40)
)DEFAULT CHARSET=utf8;

INSERT INTO user(u_name,u_passwd) VALUES('zhangsan',123456);