MySQL小结

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语言";