1.MySQL安装包分类：
一种是msi格式的，一种是zip格式的。
msi格式的需要安装，不用配置环境变量。
zip格式的免安装，需要配置环境变量。
2.安装初始化mysql后，默认几个库介绍
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
information_schema信息数据库
mysql核心数据库，主要负责存储数据库的用户、权限设置、关键字等
performance_schema性能优化的引擎
test是一个完全的空数据库，没有任何表，用于测试，可以删除。
3.常见错误
Unknown column 'id' in 'where clause'
id字段不存在，字段id名可能写错了。
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'int(20)' at line 1
检查与MySQL服务器版本对应的手册，以便正确使用“int(20)”在第1行
4.基本指令练习
#查询已知DB名
show databases
#删除test1
drop database test1
#创建数据库
create database test1；
#查看正在使用的DB
select database();
#使用test1
use test1；
#查询表格
show table
#创建表users
create table users(
sid int(10) primary key auto_increment,
sname varchar(20),
sex char,
age int(10),
tele int(20),
addre varchar(20)
);
#对表结构进行操作
#增加新列
alter table users add addre1 varchar(20);
#在第一个位置添加
ALTER TABLE users ADD uid VARCHAR(20) first;
#在指定位置之后添加
ALTER TABLE users ADD addre2 VARCHAR(20) after addre1;
#删除列
alter table users drop addre1
#修改列
alter table users change addre2 addres varchar(20);
alter table users modify tele text;
#查询表结构
DESC users
#对表数据进行操作crud
#增加数据
insert into users(sid,sname,sex,age,tele,addre) values(1,"舒畅",'女',30,16666666666,"吉林省吉林市");
INSERT INTO users(sid,sname,sex,age,tele,addre) VALUES(2,"刘亦菲",'女',30,17777777777,"湖北省武汉市");
INSERT INTO users(sid,sname,sex,age,tele,addre) VALUES(3,"李易峰",'男',30,18888888888,"四川省成都市");
INSERT INTO users(sid,sname,sex,age,tele,addre) VALUES(4,"吴亦凡",'男',27,19999999999,"广东省广州市");
INSERT INTO users(sid,sname,sex,age,tele,addre) VALUES(5,"迪丽热巴",'女',18,16666666666,"吉林省吉林市");
#查询数据
SELECT * FROM users;
#修改数据
UPDATE users SET sname="李晨" WHERE sid =5;
DESC users;
#删除数据
delete from users where id=5;
5.表结构的关系
#多对多 学生和课程的关系 需要建立中间表
CREATE TABLE student(
sId INT(5),
sName VARCHAR(20)
);
CREATE TABLE course(
cId INT(5),
cName VARCHAR(20)
);
SELECT * FROM student;
#需要建立中间表stuCourse
CREATE TABLE stuCourse(
sId INT(5),#学生表id
cId INT(5),#课程表id
scId INT(5)#中间表id
);
SHOW TABLES;
INSERT INTO student VALUES(1,"舒畅");
INSERT INTO student VALUES(2,"刘亦菲");
INSERT INTO student VALUES(3,"李易峰");
INSERT INTO student VALUES(4,"吴亦凡");
SELECT * FROM course;
INSERT INTO course VALUES(1,"C语言");
INSERT INTO course VALUES(2,"C++语言");
INSERT INTO course VALUES(3,"PHP语言");
INSERT INTO course VALUES(4,"Javascript脚本语言");
INSERT INTO stuCourse VALUES(1,1,1);
INSERT INTO stuCourse VALUES(1,2,2);
INSERT INTO stuCourse VALUES(1,3,3);
INSERT INTO stuCourse VALUES(1,4,4);
INSERT INTO stuCourse VALUES(2,1,5);
INSERT INTO stuCourse VALUES(2,2,6);
INSERT INTO stuCourse VALUES(2,3,7);
INSERT INTO stuCourse VALUES(2,4,8);
INSERT INTO stuCourse VALUES(3,1,9);
INSERT INTO stuCourse VALUES(3,2,10);
INSERT INTO stuCourse VALUES(3,3,11);
INSERT INTO stuCourse VALUES(3,4,12);
INSERT INTO stuCourse VALUES(4,1,13);
INSERT INTO stuCourse VALUES(4,2,14);
INSERT INTO stuCourse VALUES(4,3,15);
INSERT INTO stuCourse VALUES(4,4,16);
SELECT * FROM student;
#查看下舒畅都选了那些课程
SELECT * FROM student s INNER JOIN stuCourse sc ON s.`sId` = sc.`sId`
INNER JOIN course c ON c.`cId`=sc.`cId` WHERE sName = "舒畅";
#查看下刘亦菲都选了那些课程
SELECT * FROM student s INNER JOIN stuCourse sc ON s.`sId` = sc.`sId`
INNER JOIN course c ON sc.`cId` = c.`cId` WHERE sName = "刘亦菲";
#查看下C语言都被谁选了
SELECT * FROM course c INNER JOIN stuCourse sc ON sc.`cId`=c.`cId`
INNER JOIN student s ON sc.`sId` = s.`sId` WHERE cName = "C语言";
