MySQL

启动和关闭服务

net start mysql  启动
net stop mysql 关闭

DOS登录mysql【必须在环境变量里配置MySQL的变量】
mysql -u root -p 密码 -h localhost

sql

语法要求
1.sql语句可以单行可以多行，以分号结尾
2.可以用空格/缩进/分行/增加语句的可读性
3.关键字不区分大小写，但是建议使用大写。

sql语句的分类
DDL    DML    TCL   DQL

#DDL

基本操作
查看所有数据库名称
SHOW DATABASES

操作数据库

创建数据库 
CREATE DATABASE IF NOT EXISTS school
修改数据库的编码
ALTER DATABASE stu CHARACTER utf8
删除数据库
DROP DATABASE IF EXISTS stu

数据类型

int 整型
double  浮点型  double(5,2)   最大值999.99
decimal 泛型  在表示金钱方面，不会精度丢失
char 固定长度的字符串 最大char(255) 数据的长度不足指定的长度用空格补充  char(50)   
varchar 可变长度的字符串 最大varchar(65535)
text  2GB
blob 字节类型
date 日期类型  yyyy-MM-dd           
time 时间类型  HH:mm:ss
timestamp 时间戳类型

操作表

选中你要添加表的数据库
USE school

创建表

CREATE TABLE stu(
  sid CHAR(6),
  sname VARCHAR(20),
  age INT,
  gender VARCHAR(10)
);
CREATE TABLE classes(
  cid INT,
  cname VARCHAR(20)
);

查看当前数据库下的所有表
SHOW TABLES
查看指定表的创建语句
SHOW CREATE TABLE stu
查看表结构
DESC stu
修改表
添加列  classname
ALTER TABLE stu ADD (classname VARCHAR(20))
修改列的属性  把gender的类型改为char(2)
ALTER TABLE stu MODIFY gender CHAR(2)
删除列 classname
ALTER TABLE stu DROP classname
修改表的名称
ALTER TABLE stu RENAME TO student
删除表  student
DROP TABLE student

#DML

插入数据

INSERT INTO  stu VALUES ('1','sd',8,'男');
INSERT INTO  stu(sid,sname) VALUES ('2','sx');
INSERT INTO  stu VALUES ('2','lsn',8,'男'),('3','sb',8,'男');

修改数据

UPDATE stu SET age=age+1 WHERE sid='1'
UPDATE stu SET age=100 WHERE gender IS  NULL
UPDATE stu SET gender='女' WHERE age>99
UPDATE stu SET sname='sdd' WHERE age=19 OR age=100
UPDATE stu SET sname='sdd' WHERE age IN(19,100)
UPDATE stu SET sname='sxx' WHERE age>=6 AND age<=20
UPDATE stu SET sname='sxx' WHERE age BETWEEN 6 AND 20

删除数据

DELETE FROM stu WHERE sid='1'
DELETE FROM stu

格式化  

TRUNCATE TABLE stu
虽然TRUNCATE和DELETE都可以删除表的所有数据。但是原理不同，delete的效率没有TRUNCATE
TRUNCATE其实是DDL语言，因为它先drop table在create table，而且TRUNCATE的记录无法回滚

#DCL

创建用户

CREATE USER 用户名@localhost IDENTIFIED BY '123456'
用户只能在localhost这个ip登录mysql服务器
CREATE USER 用户名@'%' IDENTIFIED BY '123456'
用户可以再任何电脑上登录mysql服务器

给用户授权

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON school.* TO 用户名@localhost
GRANT ALL ON school.* TO 用户名@localhost

撤权

REVOKE CREATE ON school.* FROM 用户名@localhost

查看用户的权限

SHOW GRANTS FOR 用户名@localhost

删除用户

DROP USER 用户名@localhost

#DQL

语法
select    要查询的列的名称
from      要查询的表的名称
where     筛选条件
group by  对结果的分组
having    分组之后的筛选
order by  对最终结果的排序
limit     结果得限定

查询基础【stu表为例】
查询所有的列
SELECT * FROM stu
查询指定的列
SELECT sname FROM stu

条件查询

 =  !=  <>  >   <  >=   <=  
 between   and
 in
 is null
 and
 or
 not

查询性别为女 且年龄小于8的记录
SELECT * FROM stu WHERE gender='女' AND age<8
查询学号为1，或姓名为lsd的记录
SELECT * FROM stu WHERE sid='1' OR sname='lsd'
查询学号是1,2,3的记录
SELECT * FROM stu WHERE sid IN('1','2','3')
查询学号不是1,2,3的记录
SELECT * FROM stu WHERE sid NOT IN('1','2','3')
查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL
查询年龄在10-20之间的记录
SELECT * FROM stu WHERE age BETWEEN 10 AND 20 
查询非男生的学生记录
SELECT * FROM stu WHERE gender!='男' 
SELECT * FROM stu WHERE gender<>'男' 

模糊查询

模糊查询必须使用like关键字 【_】匹配任意一个字符  【%】匹配0-N个任意字符
查询姓名由3个字构成的学生信息
SELECT * FROM stu WHERE sname LIKE '___';
查询姓名由3个字构成并且第3个字是b的学生记录
SELECT * FROM stu WHERE sname LIKE '__b';
查询姓名以l开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'l%';
查询姓名包含d的学生记录
SELECT * FROM stu WHERE sname LIKE '%d%';

字段的控制查询【emp表】

CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job  VARCHAR(50),
mgr  INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
);

distinct  去除相同的记录
查询不同的工资数
SELECT DISTINCT sal FROM emp

查看雇员的工资与奖金之和
SELECT ename,sal+comm FROM emp
comm的很多咧的记录值有可能是null， 因为任何东西与null相加都是null所以必须函数转换
SELECT ename,sal+IFNULL(comm,0) FROM emp

给列添加别名
SELECT sal AS '工资' FROM emp
SELECT sal 工资 FROM emp

排序【stu】
查询所有学生记录 按年龄升序
SELECT * FROM stu ORDER BY age ASC
查询所有学生记录 按年龄降序
SELECT * FROM stu ORDER BY age DESC
查询所有雇员按月薪降序，月薪相同按奖金升序
SELECT * FROM emp ORDER BY sal DESC,comm ASC

聚合函数

count
查询emp表中的记录数
SELECT COUNT(empno) FROM emp
查询有奖金的人数
SELECT COUNT(comm) FROM emp
查询月薪大于5555的员工数
SELECT COUNT(empno) FROM emp WHERE sal>5555
统计工资总和大于5555的员工数
SELECT COUNT(empno) FROM emp WHERE sal+IFNULL(comm,0)>5555
查询总人数和有奖金的总人数
SELECT COUNT(empno) 总数,COUNT(comm) 有奖金的人数 FROM emp

sum和avg
查询员工的月薪和
SELECT SUM(sal) FROM emp
查询员工的月薪和以及奖金和
SELECT SUM(sal),SUM(comm) FROM emp
查询所有员工的月薪与奖金之和
SELECT SUM(sal+IFNULL(comm,0)) FROM emp
统计员工的平均工资
SELECT AVG(sal) FROM emp

MAX和MIN
#查询最高的工资与最低的工资
SELECT MAX(sal),MIN(sal) FROM emp

分组查询

当分组查询是需要用到group by字句

查询每个部门的部门编号，以及每个部门的工资总和
SELECT SUM(sal),deptno FROM emp GROUP BY deptno
查询每个部门的部门编号，以及每个部门的人数
SELECT COUNT(empno),deptno FROM emp GROUP BY deptno
查询每个部门的部门编号，以及每个部门员工工资大于5555的人数
SELECT deptno,COUNT(empno) FROM emp WHERE sal>5555 GROUP BY deptno

having字句 【对分组之后的结果再一次筛选】
查询工资共和大于10000的    部门以及工资总和
SELECT deptno,SUM(sal) FROM emp  GROUP BY deptno HAVING SUM(sal)>10000

limit

 limit index1,index2  index1表示起始从index1+1开始，index2代表返回几条

查询前2条记录
SELECT * FROM emp LIMIT 0,2
查询第三行往后的1条记录
SELECT * FROM emp LIMIT 3,1

9条数据   每页显示2条  显示5页
显示第2页 3,4   limit2,2
显示第4页 7,8   limit6,2
(当前页-1)*页面的容量
select * from emp limit (当前页-1)*页面的容量,页面的容量

在oracle中的分页   

select * from
(select e.*,rownum rn
(select * from emp order by sal) e
where rownum<=当前页*页面容量) em
where em.rn>(当前页-1)*页面容量

配置环境变量

只要把mysql的安装目录找到，赋值其bin所在的目录地址，粘贴到环境变量里的path下

解决mysql在DOS窗口的乱码问题
1.可以查看当前mysql的编码格式： show variables  like  'char%';
控制台的编码只能是GBK，而不能修改为u8,那我们只有修改character_set_client=gbk;[客户端发送给服务器端]
                                                character_set_results=gbk;[服务器端发送给客户端的]  这两个设置DOS窗口关闭设置失效
一劳永逸的修改数据库的编码格式 

首先你要找到安装目录  再找到my.ini文件  defalut-character-set=gbk 

数据库的备份与还原【备份的是数据库的内容(表结构/表数据)，并不是备份的数据库本身】
备份【不需要登录mysql  打开DOS就能写前提 必须配置环境变量】
mysqldump -u root -p 密码 school>E:\school.sql
还原
mysql -u root -p 密码 school<E:\school.sql

完整性约束与多表查询

主键

创建表时指定主键

CREATE TABLE stu(
  sid INT PRIMARY KEY
);

定义列后指定主键

CREATE TABLE stu(
   sid INT,
   PRIMARY KEY(sid)
);

表完全创建之后指定主键
ALTER TABLE stu ADD PRIMARY KEY(sid)
删除主键[只删除主键约束   并不是删除这个列]
ALTER TABLE stu DROP PRIMARY KEY;

自增长
创建表时指定自增长【自增长必须是整形  mysql的自增长 内部定义好了  1开始每次+1】

CREATE TABLE stu(
  sid INT PRIMARY KEY AUTO_INCREMENT
);

表创建好了 添加自增长
ALTER TABLE stu CHANGE sid INT AUTO_INCREMENT;

查看序列
SHOW VARIABLES LIKE 'auto_inc%'

设置自增长的步长[全数据库都有效]
SET @@AUTO_INCREMENT_INCREMENT=1;

设置自增长的起点[针对某一张表]
ALTER TABLE stu AUTO_INCREMENT=1000;

非空
创建表时指定非空约束  [插入/修改表数据时 对应的非空列  如果数据为空  就会报错]
CREATE TABLE stu(
  sname VARCHAR(50) NOT NULL
);

表创建后 设置非空约束
ALTER TABLE stu MODIFY sname NOT NULL

唯一约束【可以有一个为空  当插入两个相同的数据就会报错】
CREATE TABLE stu(
  sname VARCHAR(50) UNIQUE
);

创建表后，设置唯一约束
ALTER TABLE stu ADD UNIQUE KEY(sname)

外键【主外键是构成表与表关系的唯一途径】

创建表的时候设置外键

CREATE TABLE emp(
  sid INT PRIMARY KEY,
  deptno INT,
  CONSTRAINT FK_deptno KEY(deptno) REFERENCES dept(deptno)
);
CREATE TABLE dept(
  deptno INT PRIMARY KEY
);

表创建后 添加外键约束
ALTER TABLE emp ADD CONSTRAINT FK_deptno KEY(deptno) REFERENCES dept(deptno)

删除外键约束
ALTER TABLE emp DROP FOREIGN KEY FK_deptno ;
classes

多表连接

合并结果集

作用：把两个select语句的查询结果合并到了一起
去除重复的记录
SELECT * FROM a UNION SELECT * FROM b
显示所有的记录
SELECT * FROM a UNION ALL SELECT * FROM b
注意点：被合并的两个结果：列，列的类型必须相同

连接查询

内连接
SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno
外连接
左外连接  以左边的表为主表 拿右边的表去填充 如果没有对应的填充null
SELECT * FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno
右外连接  以右边的表为主表 拿左边的表去填充 如果没有对应的填充null
SELECT * FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno

自然连接 [去除重复的]
两张连接的表中名称和类型完全一致的列作为条件 如emp表中有detpno dept表中也有deptno
SELECT * FROM emp NATURAL JOIN dept
SELECT * FROM emp NATURAL LEFT JOIN dept
SELECT * FROM emp NATURAL RIGHT JOIN dept

子查询

嵌套查询 即select里面包含select  如果一条语句中存在两个或两个以上的select 就是子查询

1.工资高于张欢的员工
 条件  sal>张欢的工资   其中张欢的工资需要一条查询
查询张三的工资
SELECT sal FROM emp WHERE ename='张三'
查询高于张三的员工    子查询形式为单行单列
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='张三')

工资高于30部门  所有人的员工信息
查询条件 工资高于30部门所有人的工资  30部门人的工资子查询

查询30部门所有人的工资
SELECT sal FROM emp WHERE deptno=30;   
查询高于30部门的所有员工   子查询形式为多行单列
SELECT * FROM emp WHERE sal >ALL(SELECT sal FROM emp WHERE deptno=30)

查询工作和工资与张三相同的员工
查询条件   工作和工资与张三相同  子查询
SELECT job,sal FROM emp WHERE ename='张三'
查询出与邓立中相同的人
SELECT * FROM emp WHERE (job,sal)IN(SELECT job,sal FROM emp WHERE ename='张三')