MySQL之SQL语句的使用(一)

#一、忘记密码的修改步骤
1).停止mysql服务
运行输入services.msc 停止mysql服务
或 运行输入 net stop mysql

2).在cmd下输入
mysqld --skip-grant-tables 启动服务器（不要关闭该窗口）

3). 打开新的cmd 输入 mysql -u root -p 不输入密码回车
如果在新出现的窗口下出现如下图提示，不用管回车即可。

回车之后窗口就会如下所示，

在mysql命令提示符下输入： use mysql;
再输入：
update user set password=‘新密码’ where user=‘root’;

4).关闭两个cmd窗口，在任务管理器中结束mysqld进程

5).重启mysql服务即可修改完毕

6).修改完成密码之后可能回报如下错误:
Host ‘localhost’ is not allowed to connect to this MySQL server
解决办法如下所示：
解决办法：
Mysql的安装路径\my.ini
在[mysqld]下加下面两行，
skip-name-resolve
skip-grant-tables
重启mysql的windows服务

二、数据库操作

001.创建ymh数据库并制定器字符集为gbk以及校验规则为gbk_bin
create database [if not exists] ymh character set gbk collate gbk_bin;

002.查看创建数据库的源码
show create database ymh;

003.查看当前连接的数据库
select database();

004.查看当前数据库服务器中已有的数据库
show databases;

005.切换数据库至yangminghan
use yangminghan;

006.修改数据库ymh的字符集为utf8
alter database ymh character set utf8;

007.删除数据库ymh
drop database [if exists] ymh;

三、表的操作

001.往已存在的student表中添加address列数据类型为varchar(40)
alter table student add address varchar(40);

002.查看创建表的源码
show create table student;

003.查看表的结构
desc student;

004.修改student表中的address为addr数据类型为varchar(30)
alter table student change address addr varchar(30);

005.修改student表中的addr的数据类型为varchar(40)
alter table student modify addr varchar(40);

006.删除student表中的addr列
alter table student drop addr;

007.修改表名student为stu
alter table student rename to stu;

008.修改表名stu为student
alter table student rename student;

009.删除student表
drop table [if exists] student;

010.创建grade表并制定id为主键，同时让其自增
方式一：
create table [if not exists] grade (
id int primary key auto_increment,
projectName varchar(40)
);
方式二：
create table grade(
id int auto_increment,
projectName varchar(40),
primary key(id)
);

创建完成grade表之后添加主键
alter table grade add primay key(id);

创建完成grade表之后添加自增
alter table change id id int auto_increment;

011.去除grade表中的主键（只是删除主键约束，并不会删除主键列）
如果主键当前是自增的，必须删除自增之后删除主键的语句才会有效。
alter table grade drop primary key;

012.去除grade表中的自增
alter table grade change id id int;

013.创建grade表并指定projectName为非空
create table grade(
id int,
projectName varchar(40) not null
);

创建完表之后添加非空的方式
alter table grade modify projectName varchar(40) not null;

014.创建grade表并指定projectName的值唯一
create table grade(
id int,
projectName varchar(40) unique
);

创建完表之后添加唯一的约束
alter table grade modify projectName varchar(40) unique;

015.创建grade表，其中设置它的字符集为gbk以及它的校验规则为gbk_bin;
方式一：
create table grade(
id int,
projectName varchar(40)
) character set gbk collate gbk_bin;

方式二：
创建完成表之后设置其字符集和校验规则
alter table grade character set gbk collate gbk_bin;

016.创建student表和grade表，其中student表中的id列被引入到grade表中当作外键。
方式一：
create table student(
id int primary key,
name varchar(40),
address text
);

create table grade(
id int primary key,
chinese int check(chinese >=0 and chinese <=100),#mysql中无效但不报错
english int,
math int,
stu_id int,
foreign key(stu_id) references student(id)
);
方式二：表创建完成之后添加外键约束
create table grade(
id int primary key,
chinese int check(chinese >=0 and chinese <=100),#mysql中无效但不报错
english int,
math int,
);

alter table grade add foreign key(stu_id) references student(id);

017.删除约束
alter table grade drop foreign key stu_id;

018.查看当前数据库的已有表
show tables;

019.查看所有mysql的编码
show variables like ‘character%’;
以下几个是与客户端有关的
client connetion result 和客户端相关 、
database server system 和服务器端相关

020.查询student表中的数据出现中文乱码，可以设置客户端编码
set character_set_results=gbk;

021.向student表中的数据插入中文的时候出现如下错误：
Incorrect string value: ‘\xD5\xC5\xC8\xFD’ for column ‘username’ at row
解决办法：set character_set_client=gbk;

020和021方法只可以解决当前窗口编码，如下方法可以永久有效
在mysql安装目录下有my.ini文件
default-character-set=gbk 客户端编码设置
character-set-server=utf8 服务器端编码设置
注意:修改完成配置文件，重启服务

引用约束
注意：

1. 添加记录时必须先添加主表中的记录，再添加子表中的记录
2. 不能更改主表中具有外键约束的记录的主键
3. 删除记录的时候不允许删除具有外键关系的主表中的记录(删除的顺序应当是先删除子表中的记录，然后删除主表中的记录)

四、数据库的增删改查(CRUD)

000.向student表中添加5条数据
INSERT student VALUES(1,"张三","北京");
INSERT student VALUES(1,"张三","北京");
INSERT student VALUES(2,"李四","上海");
INSERT student VALUES(3,"王五","杭州");
INSERT student VALUES(4,"赵六","广州");
INSERT student VALUES(5,"刘七","深圳");

001.向student表中添加指定字段的数据
INSERT student (id,NAME) VALUES(6,"胡八");

002.修改student表中id=6的地址为"武汉"
UPDATE student SET address="武汉" WHERE id=6;

003.向grade表中添加两条数据
INSERT grade VALUES(1,34,17,89,1);
INSERT grade (id,chinese,english,math) VALUES(2,23,34,100);

004.修改grade表中id=2的外键值为6
UPDATE grade SET stu_id=6 WHERE id=2;

005.将student表中id=6的学生的姓名改为胡八一、地址改为西藏
UPDATE student SET NAME="胡八一",address="西藏"  WHERE id=6;

006.查看张三的信息
select * from student where name="张三";

007.查看id在2~4之间的人
select * from student where id>=2 and id<=4;
SELECT * FROM student WHERE id BETWEEN 2 AND 4;

008.查看所有人的姓名
SELECT NAME FROM student;

009.查看所有人的姓名、地址
SELECT NAME,Address FROM student;

010.查询所有名字中含有"八"的名字
SELECT * FROM student WHERE NAME LIKE '%八%';

011.查询姓名中含有"八"并且姓名长度为3的人
SELECT * FROM student WHERE NAME LIKE "八__" OR NAME LIKE "_八_" OR NAME LIKE "__八";

012.查询student表中有几个性别
SELECT DISTINCT sex FROM student;

013.查询student表中地址和性别都不同的记录
SELECT DISTINCT address,sex FROM student;

014.查询grade表中china列并且都加10
SELECT chinese+10 FROM grade;

015.查询grade表中的总分并给其别名为totalGrade
SELECT chinese+english+math AS totalGrade FROM grade;

016.查询student表中address在北京或者上海的人
SELECT * FROM student WHERE address="北京" OR address="上海";
SELECT * FROM student WHERE address IN("北京","上海");

017.查询student表中address为null
SELECT * FROM student WHERE address IS NULL;

018.查询student表中address不为null的人
SELECT * FROM student WHERE address IS NOT NULL;

019.对grade表中的人的math成绩降序排列
SELECT * FROM grade ORDER BY math ASC;

020.对grade表中的人的math成绩升序排列
SELECT * FROM grade ORDER BY math DESC;

021.根据多个字段进行排序(chinese升序，对于chinese一样的人再进行math降序排列)
SELECT * FROM grade ORDER BY chinese ASC,math DESC;

022.根据总分降序排列grade表中的人
SELECT *,chinese+english+math AS 总分 FROM grade ORDER BY chinese+english+math DESC;

023.查询参加考试的人的成绩并且以总成绩进行降序排列(原理：for循环遍历查询)
SELECT *,g.chinese+g.math+g.english AS 总分 FROM student s CROSS JOIN grade g ON s.id=g.stu_id ORDER BY g.chinese+g.math+g.english DESC;

024.查询参加考试的人的成绩
SELECT NAME,chinese,english,math ,chinese+english+math  总分 FROM student s INNER JOIN grade g  ON s.id=g.stu_id;

025.查询参加考试的人
SELECT s.id,NAME FROM grade g INNER JOIN student s  ON s.id=g.stu_id;

026.查询没有参加考试的人
SELECT * FROM student WHERE id NOT IN (SELECT stu_id FROM grade);

027.查询student表中所有人的成绩(此次查询就算是没有参加考试的人也能够查出来)
SELECT NAME ,chinese+english+math 总分 FROM student s LEFT JOIN grade g ON s.id=g.stu_id; 

028.查询grade表中所有人(此次查询就算是参加考试的人没有在student表中也能够查出来)
SELECT NAME ,chinese+english+math 总分 FROM student s RIGHT JOIN grade g ON s.id=g.stu_id; 

029.联合查询UNION，实质是多条语句sql结合使用，前提是各个语句表中的字段必须一致
SELECT * FROM student WHERE NAME="张三" UNION SELECT * FROM student WHERE address="上海";

030.聚合函数count、max、min、avg、sum

031.查询男女各有多少人
SELECT COUNT(*) 数量,sex FROM student GROUP BY sex;

032.根据20岁以上的人分组
SELECT COUNT(*) 数量,sex FROM student WHERE age>20 GROUP BY sex;

033.分组的人数少于2的人不参与分组
SELECT COUNT(*) 数量,sex FROM student  WHERE age>20 GROUP BY sex HAVING COUNT(*) >2;

034.查询当前时间
SELECT NOW();

五、存储过程##

001.修改sql语句的结束符为$
 delimiter $
 
002.创建无参的存储过程
 create procedure pro1()
 begin
 select * from student;
 end;
 $
 
 调用：call pro1() $
 删除：drop procedure pro1 $
 
003.创建带输入参数的存储过程
 create procedure pro1(in a int)
 begin
 select * from student where id=a;
 end;
 $
 
 调用:call pro1(1) $

 create procedure pro2(in a int,in b int)
 begin
 select a+b as result;
 end;
 $

 调用:call pro2(2,3) $
 
004.创建带输出参数的存储过程
 create procedure pro3(in a int,out b varchar(40))
 begin
 select name into b from student where id=a;
 end;
 $
 
 调用:call pro3(2,@name) $
 查询:select @name $