mysql数据库的一些操作

进入服务器中的mysql容器

# sql语句不敏感，不区分大小写，sql语句可以分行写，以;结束
# 查看有多少个数据库
show databases;

# 使用数据库
use mysqltest;

# 查看当前正在用的数据库
select database();

# 创建数据库
CREATE DATABASE test charset utf8;

# 删除数据库
drop test;

# 创建数据库
CREATE DATABASE mysql;

# 在使用某个库后，查看该数据库的表有哪些
show tables;

# 创建student表
CREATE TABLE student(
	id int,
	name VARCHAR(10),
	age int
);

# 删除表
drop TABLE student;

# 在数据库中建表
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据库数据
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 MySQL", "菜鸟教程", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 python", "菜鸟教程", NOW());

# 在student表中插入数据
INSERT into student(id) VALUES(1), (2), (3);
INSERT into student(id, name, age) VALUES(4, '周杰伦', 31), (5, '林俊杰', 33);
# 当插入的数据是全列的表名，可以省略student括号中的表名
INSERT into student VALUES(6, '张学友', 31), (5, '黎明', 33);

# 查找数据
SELECT * from runoob_tbl WHERE BINARY runoob_author='菜鸟教程';

# 更新数据
UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

# 删除数据
DELETE FROM runoob_tbl WHERE runoob_id=3;

# 分组聚合
SELECT gender, AVG(age), SUM(age), MIN(age), MAX(age), COUNT(*) FROM student GROUP BY gender;

# 排序分页
SELECT * FROM student WHERE age > 20 ORDER BY age asc; # 查询完age大于20的后，按照age从小到大排序，不写asc也可以，默认这个 
SELECT * FROM student WHERE age > 20 ORDER BY age DESC; # 查询完age大于20的后，按照age从大到小排序
SELECT * FROM student LIMIT 10, 5; # 查询完数据后，从11开始，显示后面的5条数据

关联查询

1、创建一个mysqltest的数据库，并见表插入数据

use mysqltest;
CREATE TABLE student(id int,name VARCHAR(255),address VARCHAR(255), class_id INT);
INSERT INTO student(id, name, address, class_id) VALUES(1, '周杰伦', '北京', 1);
INSERT INTO student(id, name, address, class_id) VALUES(2, '张学友', '上海', 2);
INSERT INTO student(id, name, address, class_id) VALUES(3, '林俊杰', '广州', 3);
INSERT INTO student(id, name, address, class_id) VALUES(4, '刘德华', '深圳', 3);
INSERT INTO student(id, name, address, class_id) VALUES(5, '蔡依林', '北京', 2);
INSERT INTO student(id, name, address, class_id) VALUES(6, '萧亚轩', '杭州', 1);
INSERT INTO student(id, name, address, class_id) VALUES(7, '王力宏', '广州', 3);

CREATE TABLE class(id int,name VARCHAR(255));
INSERT INTO class(id, name) VALUES(1, '理科1班');
INSERT INTO class(id, name) VALUES(2, '文科1班');
INSERT INTO class(id, name) VALUES(3, '理科2班');

2、FROM多表查询(会产生笛卡尔积，效率不高)

SELECT * FROM student, class;
# 使用where
SELECT student.*, class.name FROM student, class WHERE student.class_id = class.id;

SELECT s.*, c.name FROM student AS s, class AS c WHERE s.class_id = c.id;

3、内关联(双向奔赴：取两者的交集)

SELECT * FROM student AS s INNER JOIN class AS c ON s.class_id = c.id; # INNER可以省略
SELECT s.*, c.name FROM student AS s INNER JOIN class AS c ON s.class_id = c.id; # INNER可以省略

INSERT INTO student(id, name, address, class_id) VALUES(8, '方文山', '广州', 6);
INSERT INTO class(id, name) VALUES(5, '文科2班');

4、左外关联(以student表中的数据为主)

SELECT * FROM student AS s LEFT JOIN class AS c ON s.class_id = c.id; # OUTER可以省略

5、右外关联(以class表中的数据为主)

SELECT * FROM student AS s RIGHT JOIN class AS c ON s.class_id = c.id; # OUTER可以省略