1.mysql中的注释
# #号注释
-- 双横杠注释
2.mysql的查询操作
(1).查询stu_info数据库中的全部信息
select * from stu_info;
(2).查询stu_info数据库中属性为name的信息
select name from stu_info;
3.mysql的添加操作
(1).向stu_info数据库的stu_name和sex属性添加信息
insert into stu_info(stu_name,sex) values('张三',1);
insert into stu_info(stu_name,sex) values('李四',0);
insert into stu_info(stu_name,sex) values('王五',0);
(2).向cla_info数据库中添加信息
insert into cla_info set cla_name = '舞蹈班';
insert into cla_info set cla_name = '舞蹈班',cla_teacher='罗翔';
(3).批量插入
insert into stu_info(stu_name,sex) select n_name,n_sex from new;
4.mysql的更新操作
(1).更新stu_info数据库中id=2(id为主键,具有唯一性)的数据的grade=89
update stu_info set grade = 89 where id=2;
5.mysql的删除操作
(1).删除stu_info数据库中id=3的数据
delete from stu_info where id=3
(2).慎重使用(将stu_info数据库中的信息全部删除)
delete from stu_info
6.mysql常用聚合函数
(1).求总行数
select count(*) from stu_info;
(2).求平均值
select avg(grade) as avg_grade from stu_info;
(3).求最大值,并将其命名为max_grade
select max(grade) as max_grade from stu_info;
(4).求最小值
select min(grade) as min_grade from stu_info;
(5).求和
select sum(grade) from stu_info;
(6).保留小数点后几位,四舍五入
select round(3.141592,4);
(7).查询当前数据库版本
select version();
(8).当前数据库系统所在服务器时间
select now();
(9).日期格式化
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
(10).日期增加或减少
select date_add(now(),interval 1 day);
select date_add(now(),interval -1 day);
select date_add(now(),interval 1 hour);
(11).拼接字符串
select concat(stu_name,grade) from stu_info;
(12).去掉重复关键字
select distinct stu_name from stu_info;
(13).在distinct后面有多个字段,会对多个字段的组合进行去重
select distinct id,stu_name from stu_info;