MySQL数据库进阶实战之Mysql基础操作

2021-12-29 20:11:03

首先根据阿里云教程创建数据库和用户，并登陆SQL控制窗口。

然后是mysql的基础内容

创建数据表

创建完成执行并刷新之后出现user表

CREATE TABLE `USER` (
 `user_id` int(128) NOT NULL AUTO_INCREMENT,
 `name` varchar(64) NOT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

更改表结构

更改表user新增字段sex类型是tinyint(1)非空，备注是sex,woman:0,man:1,位置在age字段后面。

ALTER TABLE user ADD COLUMN sex tinyint(1) NOT NULL COMMENT 'sex,woman:0,man:1' AFTER `age`;

对表重命名

将user表名改为student

RENAME TABLE USER TO student;

删除表

DROP TABLE student;

MySQL中常用的数据查询、删除、更新、插入等基本操作

创建数据表user

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT NULL COMMENT 'name',
 `age` int(11) DEFAULT NULL COMMENT 'age',
 `address` varchar(32) DEFAULT NULL COMMENT 'address',
 `sex` tinyint(1) DEFAULT NULL COMMENT 'sex,woman:0,man:1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.1 插入数据

通过insert into 向user表中插入7条数据

INSERT INTO USER
 (name, age, address, sex)
VALUES
 ('zhangsan', 21, 'jiangxi', 0),
 ('lisi', 22, 'hubei', 0),
 ('wangwu', 23, 'hunan', 0),
 ('lilei', 24, 'henan', 1),
 ('hanmeimei', 25, 'hebei', 1),
 ('xiaoming', 26, 'shandong', 1),
 ('xiaoli', 27, 'shanxi', 1) ;

5.2 查询表数据

SELECT id,name,age,address,sex FROM user;

5.3 更新数据

UPDATE `user` SET `age` = 28,`address` = 'sichuan' WHERE `name` = 'xiaoming' ;

5.4 删除数据

DELETE FROM `user` WHERE `name` = 'xiaoming' ;

函数操作

先创建表并插入部分数据

CREATE TABLE student_score(sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8), score INT(11));
insert into `student_score`(`sid`, `sname`, `sage`, `ssex`,`score`)
values (1001, 'xiaohua', 17,'0', 75),
 (1002, 'xiaohong', 18,'0', 80),
 (1003, 'wangwu', 18,'1', 90),
 (1004, 'lisi', 17,'1', 68),
 (1005, 'zhangsan', 19,'1', 73),
 (1006, 'xiaohei', 19,'1', 100),
 (1007, 'xiaoma', 20,'0', 77),
 (1008, 'xiaoli', 17,'1', 82),
 (1009, 'xiaobai', 19,'0', 88),
 (1010, 'wentong', 18,'0', 53);

AVG()函数：

SELECT avg(score) FROM student_score;

COUNT()函数

SELECT COUNT(*) FROM student_score;

MAX()函数

SELECT MAX(score) FROM student_score;

MIN()函数

SELECT MIN(score) FROM student_score;

SUM()函数

SELECT SUM(score) FROM student_score;

组合查询

使用CREATE TABLE语句创建数据表

student sc course teacher 四张表,并插入数据

CREATE TABLE `student` (
 `sid` int(11) NOT NULL,
 `sname` varchar(30) DEFAULT NULL,
 `sage` int(11) DEFAULT NULL,
 `ssex` varchar(8) DEFAULT NULL,
 PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values
(1005,'xiaohua',19,'0'),
(1004,'xiaohong',18,'0'),
(1003,'wangwu',18,'1'),
(1002,'lisi',17,'1'),
(1001,'zhangsan',18,'1');
CREATE TABLE `sc` (
 `sid` int(11) NOT NULL,
 `cid` int(11) NOT NULL,
 `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `sc`(`sid`,`cid`,`score`) values
(1001,101,75),
(1001,102,85),
(1002,101,65),
(1002,102,95),
(1003,101,65),
(1003,102,95),
(1004,101,80),
(1004,102,80),
(1005,101,75),
(1005,102,85);
CREATE TABLE `course` (
 `cid` int(11) NOT NULL,
 `cname` varchar(30) DEFAULT NULL,
 `tid` int(11) DEFAULT NULL,
 PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `course`(`cid`,`cname`,`tid`) values
(102,'yuwen',2),
(101,'shuxue',1);
CREATE TABLE `teacher` (
 `tid` int(11) NOT NULL,
 `tname` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `teacher`(`tid`,`tname`) values
(2,'liulaoshi'),
(1,'wanglaoshi');

下面根据上述条件解决问题

Q1：查询所有同学的学号和成绩

# 使用WHERE s. sid = sc.sid 来消除笛卡尔积。
SELECT s.sid ,s.sname ,c.score FROM student AS s ,sc AS c WHERE s.sid = c.sid ;

Q2: 查询语文成绩在80以上同学

SELECT a.score,
       b.cname,
       s.sname
  FROM sc as a,
       course AS b,
       student AS s
 WHERE a.cid= b.cid
   AND s.sid= a.sid
   AND a.cid= 102
   AND a.score> 80

Q3:查询语文成绩比数学成绩高的同学

SELECT a.sid,
       a.score,
       s.sname
  FROM(
SELECT sid, score
  FROM sc
 WHERE cid= "102") a,
       (
SELECT sid, score
  FROM sc
 WHERE cid= "101") b,
       student as s
 WHERE a.score > b.score
   AND a.sid= b.sid
   AND s.sid= a.sid

码农公寓

相关文章