【mySQL学习笔记】基础操作

1、配置环境变量

1、找到mysql安装路径
2、我的电脑 右键–>属性–>点击“高级系统设置”–>点击“环境变量”
3、将mysql安装路径添加到用户变量与系统变量 的 Path 里
4、Windows+R打开cmd 输入 mysql -u root -p 点击回车即可 密码为root

2、mysql常用命令

2.1 数据库操作

关键字：database、use、show、create、drop
create database if not exists myDatabase;//创建名为myDatabase的数据库
use myDatabase;//转到 myDatabase 数据库
drop myDatabase//删除数据库
show databases;//展示当前所有数据库
show warnings;//显示warning
show tables;//查看当前数据库中所有的表格
show tables from otherDatabase;//显示otherDatabase数据库中的表
show create database myDatabase;//显示建立数据库的过程

2.2数据表操作

2.2.1 创建表的结构

/*创建表的表结构*/
CREATE TABLE student(
sid INT(15),
sname VARCHAR(30),
age INT,
gender CHAR(4)
);

/*利用子查询语句创建表*/
CREATE TABLE teacher SELECT * FROM student;

2.2.2 表中插入/添加数据

INSERT INTO student(sid,sname,age,sex) VALUES(1001,'步老师',19,'男');
INSERT INTO student VALUES(1002,'吕老师',19,'女',15318808914);
INSERT INTO student VALUES(1005,'宋老师',19,'女',15318808914);

/*利用子查询向表中插入数据：查出来的列数及每一列的数据类型要与插入的列数和数据类型一致*/
INSERT INTO student(sid,sname) SELECT sid,sname FROM teacher;

2.2.3 有关表的删除

where 约束条件

/*删除表中的数据*/
DELETE FROM student WHERE sid=1001;
DELETE FROM student;//没有约束条件即删除全部表中信息
/*截断表 即只删除所有行，表结构保留*/
TRUNCATE TABLE tableName;
/*删除表 表中数据、表结构全部删除*/
DROP TABLE tableName;

2.2.4 修改表

ALTER TABLE。。。ADD 添加新列
ALTER TABLE。。。MODIFY 修改列类型
ALTER TABLE。。。CHANGE 修改列名
ALTER TABLE。。。RENAME TO 修改表名

/*修改表之添加新列*/
ALTER TABLE student ADD tellphone INT(15);
/*修改表之修改列类型或长度*/
ALTER TABLE student MODIFY gender VARCHAR(4);
ALTER TABLE student MODIFY tellphone INT(30);
/*修改表之修改列名称*/
ALTER TABLE student CHANGE gender sex VARCHAR(4);
/*修改表之修改表名称*/
ALTER TABLE student RENAME TO stu;
/*修改表之删除某一列*/
ALTER TABLE student DROP gender;

/*修改表中的语句    where条件子句*/
UPDATE student SET sname='不老师',age=999 WHERE sid=1001;
UPDATE student SET age='18' WHERE sid=1002;

2.2.5查询表

查询表结构

/*查询表结构*/
DESC student;
DESC teacher;

基本查询

1、查询所有列、查询指定列
说明：（1）、*表示选取表中所有列
（2）、DISTINCT 表示输出无重复结果的记录；ALL选项是默认的，表示输出所有记录，包括重复记录

//这俩输出所有记录
SELECT * FROM student;
SELECT ALL* FROM student;
//输出无重复结果记录
SELECT DISTINCT * FROM student;

SELECT sid,sname,age FROM student;
//同样输出无重复结果
SELECT DISTINCT sname,age FROM student;

2、为列取别名
AS关键字，可加可不加

SELECT sname AS 姓名,age 年龄 FROM student;
SELECT sname 姓名,age 年龄 FROM student;

3、使用where子句指定查询条件

//1)比较判断
SELECT sid,sname,age FROM student WHERE sid=1001;
//2)之间判断 查询区间[1001,1005]
SELECT sid,sname,age FROM student WHERE sid BETWEEN 1001 AND 1005;
//3）模糊查询 通配符%表示0个或任意字符，—表示任意一个字符
SELECT sid,sname,age FROM student WHERE sname LIKE '步%';
SELECT sid,sname,age FROM student WHERE sid LIKE '_0%';
//4）空值判断
SELECT sname FROM student WHERE sname IS NOT NULL;
//5)之内判断
SELECT sid FROM student WHERE sid IN(1002,1005);

4、使用ORDER BY子句对查询结果进行排序

注意：（1）、当SELECT语句中包含多个子句时，ORDER BY子句必须是最后一个子句
（2）、可以使用列的别名、列的位置进行排序
（3）、DESC 降序，ASC升序（默认不写即为升序）

SELECT age,sid FROM student WHERE sid IN(1002,1005) ORDER BY age DESC,sid ASC ;
//ASC可省略
SELECT age,sid FROM student WHERE sid IN(1002,1005) ORDER BY age DESC,sid;

分组查询

函数	说明
COUNT(*)	计算记录的个数（包括null）
COUNT(列名)	对一列中的值计算个数（不包括null）
SUM(<列名>)	求某一列值的总和
AVG(<列名>)	求某一列值的平均值
MAX(<列名>)	求某一列值的最大值
MIN(<列名>)	求某一列值的最小值

1、聚合函数
IFNULL(comm,0) //若comm=null,则返回0。因为null加任何数都是null

SELECT AVG(sal) AS 平均工资,SUM(sal+IFNULL(comm,0)) 总补助款,
COUNT(*) AS 总人数,COUNT(comm) 总补助人数,
MAX(sal) 最高工资,MIN(最低工资)
FROM emp WHERE deptno=30;

2、使用GROUP BY子句
3、使用HAVING子句

//只显示sid大于1001的信息
SELECT age,sid 学号 FROM student HAVING sid>1001;
//也可用别名查询
SELECT age,sid 学号 FROM student HAVING 学号>1001;

连接查询

1、相等连接
也称为简单连接或内连接，它是把两个表中指定列的值相等的行连接起来

SELECT s.sid,t.sid,t.sname FROM student s,teacher t
 WHERE s.sid=t.`sid`;//mysql的方言内连接
 //INNER..ON 写法
SELECT s.sid,t.sid,t.sname FROM student s INNER JOIN teacher t ON s.sid=t.`sid`;

2、自身连接
通过把一个表定义两个不同别名的方法（即把一个表映射成两个表）来完成自身连接的。

SELECT s.`sname` FROM student s,student sn WHERE s.age=20 AND sn.`sid`>1001;

3、不等连接
以上的连接运算符都是等号，所以以下使用其他运算符所产生的连接叫不等连接
4、左外连接
显示表1中的所有记录和表2中与表1.列相同的记录
FORM 表1 LEFT OUTER JOIN 表2 ON 表1.列=表2.列

5、右外连接
FORM 表1 RIGHT OUTER JOIN 表2 ON 表1.列=表2.列
显示表2中所有的记录和表1中与表2.列相同的记录

子查询

1、返回单值的子查询

SELECT empno,ename,sal,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='scott');

2、返回多值子查询
1）使用IN操作符的多值子查询
IN操作符比较子查询返回列表中的任何一个，即返回列表中每一个相等的数据行
例如下面的IN语句，可理解为 emp.deptn=dept.deptno

SELECT ename,deptno,sal,job FROM emp
WHERE deptno IN 
(SELECT deptno FROM dept WHERE loc='NEW YORK');

2）使用ALL操作符的多值子查询
ALL操作符比较子查询返回列表中的每一个值
<ALL 为小于最小的，>ALL为大于最大的

SELECT ename,job,sal FROM emp 
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=20);
//等同于
SELECT ename,job,sal FROM emp 
WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=20);

3）使用ANY操作符的多值子查询
ANY操作符比较子查询返回列表中的每一个值
<ANY为小于最大的，>ANY为大于最小的

SELECT ename,job,sal FROM emp 
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);
//等同于
SELECT ename,job,sal FROM emp 
WHERE sal>(SELECT MIN(sal) FROM emp WHERE deptno=10);

4）使用EXISTS操作符的多行查询
EXISTS操作符比较子查询返回列表中的每一行

SELECT ename,deptno,sal,job FROM emp
WHERE EXISTS
(SELECT *FROM dept 
WHERE dept.`deptno`=emp.`deptno` AND loc='NEW YORK');
//等价于用IN操作符实现的：
SELECT ename,deptno,sal,job FROM emp
WHERE deptno IN 
(SELECT deptno FROM dept WHERE loc='NEW YORK');

合并查询结果

当两个SELECT查询结果的结构完全一致时，可对这两个查询结果进行合并运算，运算符为UNION
语法：
SELECT 语句1
UNION [ALL]
SELECT 语句2；
注：UNION返回结果时会删除

SELECT empno,ename FROM emp WHERE sal>1250
UNION
SELECT empno,ename FROM emp WHERE deptno=2;

注：在ORDER BY之后排序的列名一定是来自第一个表中的列名，第一个表中的列名若设置了别名，在ORDER BY后。

数据的维护

1、插入数据

1）INSERT语句

INSERT INTO dept(deptno,loc)
VALUES (12,'SSSSs');
//若顺序插入，则可省略列名
INSERT INTO dept
VALUES (10,'111','NEW');

2）利用子查询向表中插入数据

INSERT INTO emp
SELECT *FROM emp1
WHERE deptno=30;

2、更新数据

1）UPDATE语句

SET sql_safe_updates=0;
UPDATE dept 
SET deptno=222 
WHERE dname='111';

2）利用子查询修改记录

SET sql_safe_updates=0;
UPDATE dept 
SET deptno=(SELECT deptno FROM dept WHERE dname='SALES')
WHERE dname='111';

3、删除数据

1、DELETE语句

DELETE FROM emp1
WHERE empno=1001;

若想删除表中所有行数据：

DELETE FROM emp1
//或使用截断表：
TRUNCATE TABLE emp1;

2、利用子查询删除行

3、数据类型

4、约束

约束类型：	主键	默认值	唯一	外键	非空
关键字：	PRIMARY KEY	DEFAULT	UNIQUE	FROEGIN KEY	NOT NULL

4.1 主键（PRIMARY KEY）、外键 (FOREIGN KEY)

/*主键*/
t_id CHAR(10) PRIMARY KEY,//主键
//or
CONSTRAINT primary_t_id PRIMARY KEY(t_id) //主键名自定义
/*复合主键 主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识*/
PRIMARY KEY(t_id,course_id)

/*外键*/
dep_id CHAR(10),
CONSTRAINT foreign_dep_id FOREIGN KEY(dep_id) REFERENCES department(dep_id)
//约束 自己起的外键名 外键关键字（属性） 参照 另一个表名（另一个表的主键）

4.2 默认值约束 (DEFAULT) 、唯一约束 (UNIQUE)、非空约束 (NOT NULL)

注：default若与check同用时，应放在check前面，防止出错

/*默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。*/
grade DECIMAL(4,1) DEFAULT 0 CHECK(grade>=0 OR grade<=100)

/*唯一约束 (UNIQUE)规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的*/
dep_name VARCHAR(20) UNIQUE,
//or
UNIQUE(dep_name)

/*非空约束 (NOT NULL) 被非空约束的列，在插入值时必须非空。*/
age INT(10) NOT NULL;