java课后作业---MySQL

课堂笔记

登录root用户，创建一个新用户

–使用root登录，创建自己的用户，
–然后退出，再用新用户登录数据库
–创建用户 你的用户名 密码是：123456

CREATE USER '你的用户名'@'localhost' IDENTIFIED BY '123456';
CREATE USER '你的用户名'@'%' IDENTIFIED BY '123456';
GRANT ALL privileges ON *.* TO '你的用户名'@'%';

-----查询用户是否创建成功

use mysql;
select * from user where User = '你的用户名';

----例如：
select * from user where User = ‘玛卡巴卡’;
创建成功之后，再退出数据库，delete connection ，使用新的用户名登录。

查询和修改表的内容

/*查询表的属性 */
desc t_student;

/*修改表，t_liuhm1  增加列scores2 类型为 int */
alter table t_student add scores2 int ;

/* 修改表 t_liuhm1 删除列 scores2 */
alter table t_student drop column scores2  ;

/* 修改表 t_liuhm1 调整 scores 类型为 INT UNSIGNED  */
ALTER TABLE t_student MODIFY COLUMN scores INT UNSIGNED  ;

条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;

课后作业

use zmh01;
show tables;


create table `t_student` (   
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` varchar(255) not null,   
    `score1` int not null, 
    `score2` int not null, 
    `score3` int not null, 
    `comment` varchar(255),
    primary key (`id`)  
    )   comment='学生成绩表'  collate='utf8_general_ci'  engine=innodb;


--插入数据，指定列名插入数据，其中id主键没有插入，由数据库自增
use zmh01;
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("张三",22,56,77,"差");
use zmh01;
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("李四",15,57,29,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("王五",16,23,27,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小咪",76,49,89,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小欣",12,26,34,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小群",74,35,67,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小狗",88,56,87,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小豆",99,96,98,"优");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小图",53,27,39,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("小平",79,84,57,"良");

use zmh01;
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("画画",45,57,29,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("悄悄",46,53,27,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("果果",76,49,89,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("可可",12,76,34,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("怜怜",74,35,67,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("莉莉",88,56,87,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("芸芸",99,96,98,"优");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("丹丹",53,27,39,"差");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("燕燕",79,84,57,"良");
INSERT INTO `t_student` (`name`,`score1`,`score2`,`score3`,`comment`)
VALUES ("青青",49,33,57,"良");

use zmh01;
UPDATE `t_student` SET
`score1` = 38,`score2`= 13
WHERE `name` = "画画" ;

UPDATE `t_student` SET
`score2`= 49
WHERE `name` = "燕燕" ;

UPDATE `t_student` SET
`score3`= 88
WHERE `name` = "丹丹" ;

use zmh01;
UPDATE `t_student` SET
`score1` = 37,`score2` = 15
WHERE name = "芸芸" ;

UPDATE `t_student` SET
`score3` = 48,`score2`= 22
WHERE `name` = "莉莉" ;

UPDATE `t_student` SET
`score2` = 29
WHERE `name` = "怜怜" ;

UPDATE `t_student` SET
`score1` = 45,`score2`= 19
WHERE `name` = "果果" ;

UPDATE `t_student` SET
`score1` = 47,`score2`= 99
WHERE `name` = "悄悄" ;

UPDATE `t_student` SET
`score1` = 55
WHERE `name` = "青青" ;

use zmh01;

SELECT * FROM `t_student`
WHERE score1< 50 and score2 < 30 ;
ORDER BY `id` ASC;

use zmh01;
SELECT * FROM `t_student`
WHERE score2 > 90 and score3 > 80 ;
ORDER BY `id` desc;

use zmh01;
SELECT * FROM `t_student`
WHERE score2 < 60 and score3 > 70 ;
ORDER BY `id` DESC;