MySQL数据库(二)：常用的SQL语句(上)

1、表层面上的(DDL)

数据库中不严格区分大小写，但是可视化提示都是大写，例子有部分会使用大写字母
①添加表字段：alter table 表名 add 字段 类型
例如：ALTER TABLE STUDENT ADD GENDER ENUM('M','F');
②修改表字段(不能重名名)：alter table 表名 modify 字段 类型和约束
例如：ALTER TABLE STUDENT MODIFY BIRTHDAY DATE DEFULT '1998-01-01';
③修改表字段(可以重命名)：alter table 表名 change 原字段名 新字段名 类型及约束
例如：ALTER TABLE STUDENT CHANGE GENDER BIRTHDAY DATE DEFALUT '2000-01-01';
④删除表字段：alter table 表名 drop 字段名
例如：ALTER TABLE STUDENT DROP NAME;

2、操作数据(DML)

2.1增添数据

①整行插入数据：insert into 表名 values(所有字段)
注意：不管字段是否有默认值都得插入(要按照约束插入，如果不按照约束会成功，但是不会显示，且有警告)
例如：INSERT INTO STUDENT VALUES('1','M','BEIYUE');
②指定列中插入数据：insert into 表名 (字段名1，[字段名2]) values (值1，[值2])
注意：一定要用括号包裹插入的字段，哪怕只往一个字段里面加入内容
例如：INSERT INTO STUDENT (ID,NAME) VALUES (1,'BEIYUE');
③指定指定列中插入多条数据：insert into表名(字段名1，[字段名2]) values (值1，[值2])，(值1，[值2])
注意：类型为枚举类型时候可以根据下标索引取值从1开始
例如：INSERT INTO STUDENT (ID,NAME,GENDER) VALUES (1,'XZ',1),(1,'XW',1),(1,'XL',2);

2.2修改数据

①更改记录内容：update 表名 set 字段1=替换的值，字段2=替换的值[where 条件]；
注意：加条件是为了防止将整个字段修改，找到对应记录的字段修改想要修改的记录，修改的时候不能将字段加括号
例如：UPDATE STUDENT SET GENDER='M' WHERE NAME='BEIYUE';
②删除记录：delete from 表名 [where条件] (物理删除，从磁盘中清空，注销账号不保留用户数据那种)
例如DELETE FROM STUDENT WHERE NAME='BEIYUE';
③删除记录 ：(逻辑删除，并非真正删除，注销账号保留用户数据那种)
一般会为用户添加一个is_del字段默认为0未删除状态，用户注销就将is_del改为1，并不删除数据，相当于修改数据，即先增添字段在修改字段
例如：将id为1的记录进行逻辑删除

ALTER TABLE STUDENT ADD  `IS_DEL` INT DEFAULT 0;
UPDATE STUDENT `SET IS_DEL`=1 WHERE ID=1;

2.2.mysql 1103错误，无法连接数据库

这是因为mysql服务端不允许远程登录，要修改host选项

1. 创建链接之后默认会有几个库，首先切换到mysql库
2. 打开mysql找到user表将user字段为root的的host字段改为%就可以链接了

use mysql；
update user set host=‘%’ where user=‘root’；

3. 要重启mysql服务才行

net stop mysql  #关闭mysql服务端
net start mysql #重启mysql服务端

2.3查询数据

①查询整个表记录：select * from 表名；*是通配符代表所有数据
例如：SELECT * FROM STUDENT;
②查询指定字段：select 字段1，字段2 from 表名
注意：字段不能加括号，加上括号后只能查询一列的字段记录
例如：SELECT NAME,GENDER FROM STUDENT;
③查询指定字段数据，并给字段起别名：select 字段1 as 别名，字段2 as 别名 ，s.class from 表名 as s(并没有修改表结构，只是显示出的结果不同)
例如：SELECT NAME AS '姓名',GENDER AS '性别' FROM STUDENT;
④查询指定字段并去重：select distinct 字段1，字段2 from 表名,如果定义多个字段，那么只有当字段一样的时候才会去重，不能加括号
例如：SELECT DISTINCT NAME FROM STUDENT;

2.3.1where筛选条件

通过集合where增加限制条件帮助更精确的增删改查(CURD),通常结合查询语句使用，也可以结合一些运算符使用
比较运算符：

①查询id大于3的数据：select * from student where id>3;
②查询年龄大于18岁的信息：select * from student where age>18;
③查询姓名不是beiyue的数据：select * from student where name!=‘beiyue’;

逻辑运算符

①查询年龄18~22之间的所有学生信息：select * from student where age>=18 and age<=22;
②查询id大于3的女同学：select * from student where id>3 and gender =‘女’;
③查询id小于2或者id大于4的学生信息：select * from student where id<3 or id>4;
④查询年龄不是18的女同学select * from student where not age=18 and gender=‘女';

2.3.2模糊查询

like关键字用来进行模糊查询，并且结合%以及_使用。
% 表示任意多个任意字符， _ 表示一个任意字符
①查询名字以a开始的学生信息：select * from student where name like 'a%';
②查询名字含有a的学生信息：select * from student where name like '%a%';
③查询名字仅有2个字符的学生信息：select * from student where name like '__';
④查询名字至少有2个字符的学生信息：select * from student where name like '__%';

2.3.3范围查询

①查询id是1或者4或者6的学生信息：
方法一：select * from student where id=1 or id=4 or id=6;方法二：select * from student where id in(1,4,6)
②查询年龄为18或者20的学生信息：select * from student where age in(18,20);
③查询年龄不是18,20的学生信息：select * from student where age not in(18,20);
④查询id是2至4的学生信息：select * from student where id between 2 and 4;
⑤查询id是3-5的男同学信息：select * from student where gender='男' and id (between 3 and 5);between最好加上括号增加可读性和辨识度
⑥查询年龄不在18至20之间的学生信息：select * from student (age not between 18 and 20)

2.3.4空判断

查询没有填写年龄的学生：select * from student where age is null;
查询填写了年龄的学生：select * from student where age is not null;

2.4聚合函数

2.4.1count 总数

①求students总人数：select count(*) from student;
②求男性的人数：select count(*) from student where gender='男';
③女性的人数：select count(*) from student where gender=’女‘;

2.4.2max() 最大值

①查询最大的年龄：select max(age) as '最大年龄' from stuednt;
②女性最大的id：select max(id) from stuednt where gender='女';

2.4.3min() 最小值

①查询未删除的学生的最小编号：select min(id) from student where is_del=0;

2.4.4sum 求和

①查询男生年龄和：select sum(age) from student where gender=1;

2.4.5avg 平均值

①查询未删除女生的年龄的平均值：select avg(age) from student where is_del=0 and gender=2;默认保留四位小数
②计算男性的平均年龄，保留2位小数：select round(avg(age),2) where is_del=0 and gender=1;

3round以及rand的用法

rand()会随机生成一个0到1之间的浮点数

round(n,m);n是要处理的数，m是要保留的位数，默认是0也就是只保留整数，也可以为负数

两者可以结合起来使用，比如生成1000到1100的随机数