目录
一、新建表
1、数据类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
(1)数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
(2)日期和时间类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
(3)字符串类型
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
2、创建数据表
(1)表结构组成
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
(2)创建语句
CREATE TABLE table_name (column_name column_type);
-- 注释:CREATE TABLE <表名> (<列名1> <类型1>)
提示:使用 CREATE TABLE 创建表时,必须指定以下信息:
- 要创建的表的名称不区分大小写,不能使用SQL语言中的关键字,如DROP、ALTER、INSERT等。
- 数据表中每个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
(3)实例举例
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
实例解析:
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
- AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
- ENGINE 设置存储引擎,CHARSET 设置编码。
(4)练习题
1)创建表
CREATE TABLE `dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dept_name` varchar(32),
PRIMARY KEY (`id`)
) ;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) ,
`dept_id` int(11) ,
PRIMARY KEY (`id`),
FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ;
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(32) ,
`teacher_id` int(11),
PRIMARY KEY (`id`),
FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) ,
`gender` tinyint(4) COMMENT '0:male,1:female',
`birthday` date ,
`dept_id` int(11),
PRIMARY KEY (`id`),
FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ;
CREATE TABLE `score` (
`sid` int(11),
`course_id` int(11) ,
`score` tinyint(4),
FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
FOREIGN KEY (`sid`) REFERENCES `students` (`id`)
) ;
2)插入测试数据
INSERT INTO `dept` VALUES (1,'Education'),(2,'Computer Science'),(3,'Mathematics');
INSERT INTO `teacher` VALUES (1,'Zhang san',1),(2,'Li si',1),(3,'Wang wu',2),(4,'Liu liu',3),(5,'Ding \nqi',3),(6,'Zhang yang',2);
INSERT INTO `course` VALUES (1,'math',3),(2,'english',2),(3,'chinese',4),(4,'history',1),(5,'biology',5),(6,'physical',2);
INSERT INTO `students` VALUES (1,'Andrew',0,'1985-08-05',1),(2,'Andy',0,'1983-02-09',1),(3,'Bob',0,'1983-01-03',1),(4,'Ruth',1,'1984-12-23',2),(5,'Mike',0,'1985-01-02',2),(6,'John',0,'1984-02-23',3),(7,'Cindy',1,'1984-12-04',3),(8,'Susan',1,'1983-11-11',3),(9,'John',1,'1984-09-18',2),(10,'Esther',1,'1988-12-25',3);
INSERT INTO `score` VALUES (1,1,56),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3,2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),(6,2,58),(6,4,88),(7,1,46),(7,2,65),(7,3,63),(7,4,68),(7,5,32),(8,4,88),(8,5,100),(1,6,76),(2,6,54),(4,6,98),(5,6,87),(8,6,76),(9,1,90),(9,2,59),(10,3,67);
3)练习题
- 1、查询85年之后出生的学生人数
SELECT COUNT(*) FROM `students` WHERE `birthday` >'1985-01-01'
- 2、查询名字相同的学生名单和个数
SELECT `name`, COUNT(*) FROM `students` GROUP BY `name`
- 3、将所有学生姓名中前后的空格去掉
SELECT TRIM(`name`) FROM `students`
- 4 、查看每门课程被选修的学生数
SELECT `course_id`,COUNT(`sid`) FROM `score` GROUP BY `course_id`
- 5、查询姓zhang的老师的个数
SELECT COUNT(*) FROM `teacher` WHERE SUBSTRING_INDEX(`name`,' ',1) = 'Zhang'
- 6 、将所有老师的名字拆分成姓和名两个字段显示
SELECT
SUBSTRING(`name`, 1, LOCATE(' ', `name`)) '姓', -- locate()返回子串位置
SUBSTRING_INDEX(`name`, ' ', - 1) '名' -- 推荐使用SUBSTRING_INDEX()
FROM
`teacher`
- 7、把所有学生的生日格式转换成年月日的格式,并计算每个学生年龄
SELECT
`name`,
DATE_FORMAT(`birthday`, '%Y%m%d'),
FLOOR(DATEDIFF(NOW(), `birthday`) / 365) '年龄'
FROM
`students`
- 8、查询有不及格学生的课程和不及格学生个数
SELECT
b.`course_name`,
COUNT(*)
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
WHERE a.`score` < 60
GROUP BY a.`course_id`
- 9、查询每门课程的平均成绩,按升序排序,如果平均成绩相同按课程ID降序排序
SELECT
b.`course_name`,
AVG(a.`score`)
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
GROUP BY a.`course_id`
ORDER BY AVG(a.`score`),a.`course_id` DESC
- 10、查询有学生的单科成绩小于60的姓名和课程名称
SELECT
b.`name`,
c.`course_name`
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
JOIN `course` c
ON a.`course_id` = c.`id`
WHERE a.`score` < 60
- 11、查看平均成绩大于等于60的所有学生的姓名和平均成绩
SELECT
any_value (b.`name`),
AVG(a.`score`)
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
GROUP BY b.`id`
HAVING AVG(a.`score`) > 60
- 12、将所有学生的考试成绩展示为课程名:成绩样式
SELECT
a.`sid`,
CONCAT(b.`course_name`, ':', a.`score`)
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
- 13、查询不同老师所教不同课程平均分从高到低
SELECT
b.`teacher_id`,
b.`id` -- 课程ID
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
GROUP BY b.`teacher_id`, b.`id`
ORDER BY AVG(a.`score`) DESC
- 14、查询英语课程成绩排名第3到第5位的学生姓名和成绩
SELECT
b.`name`,
a.`score`
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
JOIN `course` c
ON a.`course_id` = c.`id`
WHERE c.`course_name` = 'english'
ORDER BY a.`score` DESC
LIMIT 2, 3
- 15、查询所有同学的学号,姓名,选课数和总成绩
SELECT
a.`sid`,
b.`name`,
COUNT(a.`course_id`),
SUM(a.`score`)
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
GROUP BY b.`id`
- 16、查询各科成绩中的最高分和最低分
SELECT
b.`course_name`,
MAX(a.`score`),
MIN(a.`score`)
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
GROUP BY b.`course_name`
- 17、计算各科平均成绩和及格率百分比
SELECT
b.`course_name`,
AVG(a.`score`),
SUM(IF(a.`score`>=60,1,0))/COUNT(*)
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
GROUP BY b.`course_name`
- 18、查看只学习了一门课程的学生的姓名和学号
SELECT
`id`,
`name`
FROM
`students`
WHERE `id` = (SELECT `sid` FROM `score` GROUP BY `sid` HAVING COUNT(`course_id`)=1)
- 19、查询没学过zhangsan老师课程的学生的学号和姓名
SELECT
`id`,
`name`
FROM
`students`
WHERE `id` NOT IN
(SELECT DISTINCT
a.`sid`
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
JOIN `teacher` c
ON b.`teacher_id` = c.`id`
WHERE c.`name` = 'Zhang san')
- 20、查询既学过英语也学过语文的学生的学号和姓名
SELECT
b.`id`,
b.`name`,
GROUP_CONCAT(c.`course_name`)
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
JOIN `course` c
ON a.`course_id` = c.`id`
WHERE c.`course_name` IN ('english', 'chinese')
GROUP BY b.`id`
HAVING COUNT(*) >1
- 21、按平均成绩从高到低显示所有学生的姓名和语文,数学,英语三科成绩
SELECT
any_value(b.`name`),
SUM(CASE WHEN c.`course_name`='chinese' THEN a.score ELSE 0 END) 'chinese',
SUM(IF(c.`course_name`='math',a.`score`,0)) 'math',
SUM(IF(c.`course_name`='english',a.`score`,0)) 'english'
FROM
`score` a
JOIN `students` b
ON a.`sid` = b.`id`
JOIN `course` c
ON a.`course_id` = c.`id`
GROUP BY a.`sid`
ORDER BY AVG(a.`score`) DESC
- 22、统计按照各科成绩,分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数
SELECT
b.`course_name`,
SUM(IF(a.`score`>=90,1,0)) '90分以上',
SUM(IF(a.`score`<90 AND a.`score`>=80,1,0)) '80~90分',
SUM(IF(a.`score`<80 AND a.`score`>=60,1,0)) '60~80分',
SUM(IF(a.`score`<60,1,0)) '不及格'
FROM
`score` a
JOIN `course` b
ON a.`course_id` = b.`id`
GROUP BY b.`course_name`
- 23、查看所有英语成绩超过数学成绩的学生的学号和姓名
SELECT
a.`id`,a.`name`,c.`score`, b.`score`
FROM
`students` a
JOIN (SELECT a.`sid`,a.`score` FROM `score` a JOIN `course` b ON a.`course_id` = b.`id` WHERE b.`course_name` = 'math') b
ON a.`id` = b.`sid`
JOIN (SELECT a.`sid`,a.`score` FROM `score` a JOIN `course` b ON a.`course_id` = b.`id` WHERE b.`course_name` = 'english') c
ON a.`id` = c.`sid`
WHERE c.`score`> b.`score`
——————————————————————————————————————————
引用说明:
1)CSDN博主「胡嚞衎」的原创文章,原文链接:https://blog.csdn.net/hu_zhe_kan/article/details/104399726
2)菜鸟教程:MySQL 创建数据表
https://www.runoob.com/mysql/mysql-create-tables.html
3)菜鸟教程:MySQL 数据类型