mysqli的增删改查 mysql增删改查语句代码

1.mysql命令行语句使用：

mysql -u root -p   //登录用户root
  show databases;
  use mysql;          //进入数据库mysql
  show tables;      //进入数据库后，查看数据库中的表
  select * from user;            //查看数据库中的表user
  select * from user where user = 'root';    //查看表user中属性为‘root’的内容
  exit;                    //退出

2.mysql可视化界面 Navicate

1. 两种注释：# 、 –

2)创建班级表

create table cla_info(
cla_id int not null auto_increment comment '班级主键',
cla_name varchar(50) not null comment '班级名称',
primary key(cla_id)
)comment '班级基础信息';

• 代表查询全部字段

SELECT * FROM stu_info;
SELECT stu_name FROM stu_info;

查找一个元素是否等于集合中某个元素时，用any（如下例5）

3)添加

INSERT INTO stu_info(stu_name,sex)VALUES('张三',1);

INSERT INTO cla_info SET cla_name = '舞蹈班';

-- 批量插入；把user_info表中的字段id、name插入到cla_info表中
INSERT INTO cla_info(cla_id,cla_name)
SELECT id,name FROM user_info;

4)更新（修改） 用id查找效率更快（唯一且自增（可以二分查找））
where筛选条件，几个条件同时满足：where id = 1 and name = ‘xyz’;

UPDATE stu_info SET grade = 89 WHERE id = 1;
SELECT * FROM stu_info WHERE id = 1;
UPDATE stu_info SET grade = 89 WHERE stu_name = '张三';

5)删除 删除只是在表中删除，mysql库中没有删除，id会在原基础继续

DELETE FROM stu_info; # 删库跑路
DELETE FROM stu_info WHERE id = 3;

6）日期获取、增加

-- now获取的是电脑系统时间
SELECT NOW();

-- 慎用now获取服务器时间（不同地区（东八区和西八区）系统时间不同）
SELECT DATE_ADD(NOW(),INTERVAL 1 day);
SELECT DATE_ADD(NOW(),INTERVAL -1 day);
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR);

7）聚合函数

-- 求表总行数
SELECT COUNT(*) FROM stu_info;

-- 求平均值
SELECT AVG(grade) FROM stu_info;

-- 给字段AVG(grade)重新命名为avg_grade
-- **as起别名**
SELECT AVG(grade) AS avg_grade FROM stu_info;

-- 最大值
SELECT MAX(grade) AS max_grade FROM stu_info;

-- 最小值
SELECT MIN(grade) AS min_grade FROM stu_info;

-- 求和
SELECT SUM(grade) AS sum_grade from stu_info;

-- 保留小数后几位，四舍五入
SELECT ROUND(3.1415926545,5);

-- 查询当前数据库版本
SELECT VERSION();

-- 日期格式化
SELECT DATE_FORMAT('2021/2/12 13:45:32','%Y-%m-%d %H:%i:%s');

-- 字符串拼接
SELECT CONCAT(stu_name,grade) AS tar FROM stu_info;

-- 去除重复关键字distinct(关键字必须放在最前面);
-- 只有组合(id,stu_name)重复时才会去除
SELECT * FROM stu_info;
SELECT DISTINCT stu_name,sex FROM stu_info;

例1：

-- 创建表stu_info
CREATE TABLE stu_info(
id int not null auto_increment comment '学生序号',
stu_name VARCHAR(20) not null COMMENT '学生姓名',
sex TINYINT COMMENT'1:男，0：女',
cla_id int not null COMMENT '班级序号',
grade int COMMENT'学生成绩', 
PRIMARY KEY(id)
)COMMENT'学生基本信息管理表';

-- 插入1
INSERT INTO stu_info(id,stu_name,sex,cla_id,grade) VALUES(3,'张三',1,1,'20');
-- 插入2
INSERT INTO stu_info SET id = 5, stu_name = '李四', sex = 0, cla_id = 2, grade = '80';


-- 创建表cla_info

CREATE TABLE cla_info(
cla_id int not null auto_increment COMMENT'班级序号',
cla_name VARCHAR(20) not null COMMENT '班级名称',
cla_teacher VARCHAR(10) COMMENT'班主任名称',
PRIMARY KEY(cla_id)
)COMMENT'班级基本信息管理表';

-- 插入1
INSERT INTO cla_info(cla_id,cla_name,cla_teacher) VALUES(1,'艺术班','宋丹丹');

-- 插入2
INSERT INTO cla_info SET cla_id = 2, cla_name = '舞蹈班', cla_teacher = '金星';
INSERT INTO cla_info(cla_id,cla_name,cla_teacher) VALUES(3,'法律班','罗翔');


-- 1.查询成绩最高的女生信息 
-- SELECT MAX(grade) from stu_info 找到成绩最大值
-- 从stu_info表中查询sex = 0且grade = max的元素集合
--这里的grade为int型或varchar都可以，max(grade)也可以返回字符串最大值
SELECT *  FROM stu_info where grade = (SELECT MAX(grade) from stu_info) and sex = 0;

-- 2.查询成绩不及格的学生信息
SELECT * FROM stu_info where grade < 60;

-- 3.查询成绩大于90的女生信息
-- 两个条件：成绩>90,女生
SELECT * from stu_info WHERE grade > 90 AND sex = 0;

-- 4.查询男生中成绩不及格的学生信息
-- 男生；成绩不及格
SELECT * from stu_info where grade <60 and sex = 1;

-- 5.修改所有成绩小于30的学生所在班级为舞蹈班
-- 找到grade<30的学生的班级序号 SELECT cla_id FROM stu_info WHERE grade <30
-- **如果x等于集合a[]中某个值---> WHERE x == any(a[])  !!!注意any的用法**
UPDATE cla_info SET cla_name = '舞蹈班' WHERE cla_id = any(SELECT cla_id FROM stu_info WHERE grade <30);
-- in子查询，in确定一个字段是否属于结果集
UPDATE cla_info SET cla_name = '舞蹈班' where cla_id in(SELECT cla_id from stu_info where grade<30);


select * from cla_info;

例2：

# 1.查询宋丹丹老师的学生中，成绩最高的学生信息

-- 宋丹丹老师的班级号 SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹';
-- 这个班级中的最高成绩 SELECT MAX(grade) FROM stu_info WHERE cla_id = (SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹'
)
-- 在stuo_info中找grade等于最高成绩而且班级序号等于宋丹丹老师班级号的学生

SELECT * from stu_info WHERE grade =(
SELECT MAX(grade) FROM stu_info WHERE cla_id = (
SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹'
)
) AND cla_id =(SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹'
)

# 2.查询成绩最高的学生所在的班级信息

SELECT * FROM stu_info AS a
LEFT JOIN cla_info AS b ON a.cla_id = b.cla_id
where  a.grade = (
	SELECT MAX(grade) FROM stu_info
)


# 3.查询人数最多的班级中，的男生信息
SELECT a.* FROM stu_info as a
RIGHT JOIN (
	SELECT cla_id,count(*) as s_count FROM stu_info GROUP BY cla_id ORDER BY s_count DESC  LIMIT 1
) as t on a.cla_id = t.cla_id WHERE a.sex = 1