建库
create database linux
注意::整形和浮点型不用加''号，其他字符串型那部分需要加''
表一
| 字段 |
数据类型要求 |
是否为空 |
注释 |
| sno |
最多20位 |
否 |
学号（主键） |
| sname |
可变长 |
否 |
学生姓名 |
| sage |
最小整数，非负数 |
否 |
学生年龄 |
| ssex |
0，1 |
否 |
学生性别（1是男，0是女）默认为男） |
| sbirthday |
时间类型 |
默认为空 |
学生生日 |
| class |
可变长 |
否 |
学生班级 |
root@localhost [(none)] >create table `test1` (
`son` char(20) not null primary key comment '学号',
`sname` varchar(10) not null comment '学生姓名',
`sage` tinyint not null comment '学生年龄',
`ssex` enum('0','1') not null default '1' comment '学生性别（1是男，0是女,默认为男）',
`sbirthday` date default null comment '学生生日',
`class` varchar(10) not null comment '学生班级'
);
# 查看库
root@localhost [nono] >show databases;
# 切换库
root@localhost [mysql] >use mysql
# 查看库里的表
root@localhost [mysql] >show tables;
## 查看表结构
root@localhost [mysql] >desc mysql.user;
# 查看表内内容
root@localhost [(none)] >select * from mysql.user\G
# 查看表内的列
root@localhost [(none)] >select user,host from mysql.user;
# 能查看注释的内容
root@localhost [linux] >show full columns from test;
root@localhost [linux] >show create table linux.test;
-------------------------------------------------------
## 向表里添加信息
root@localhost [linux] >INSERT INTO test (son, sname, sage, ssex, sbirthday, class) VALUES (1, '张三', 20, '1', '2023-07-24', '一班');
# 多行
insert into test(sname,sage,ssex,sbirthday,class) value
('徐导',20,'1',now(),'1'),
('曾导',18,'1',now(),'1'),
('李导',25,'1',now(),'2');
联合主键
primary key (sno,cno)
create table linux50.score(sno char(20) comment'学号（主键）',cno varchar(20) comment'课程号（主键）',mark float(4,1) comment'成绩',primary key (sno,cno));
001
## zerofill：零填充 加了这个选项后 写入1时 这里指定了3位 那么会保存为001 只能写整数类型
建表练习题
## student
create table linux50.student(
sno bigint(20) not null primary key auto_increment comment '学号（主键）',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女）默认为男）',
sbirthday datetime null comment '学生生日',
class varchar(5) not null comment '学生班级');
## course
CREATE TABLE `course` (
`cno` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '课程号（主键）',
`cname` varchar(10) NOT NULL COMMENT '课程名称',
`tno` varchar(10) NOT NULL COMMENT '教师编号',
PRIMARY KEY (`cno`,`cname`,`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
## score
CREATE TABLE score(
sno BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '学号（主键）',
cno BIGINT(20) NOT NULL COMMENT '课程号（主键）',
mark FLOAT(4,1) NOT NULL COMMENT '成绩',
PRIMARY KEY(sno,cno));
## teacher
CREATE TABLE `teacher` (
`tno` bigint(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '教师编号（主键）',
`tname` varchar(10) NOT NULL COMMENT '教师姓名',
`tage` tinyint(3) unsigned NOT NULL COMMENT '教师年龄',
`tsex` enum('1','0') NOT NULL DEFAULT '1' COMMENT '教师性别（1是男，0是女）默认为男）',
`prof` varchar(10) NOT NULL COMMENT '教师职称',
`depart` varchar(10) NOT NULL COMMENT '教师部门',
PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查询练习
1.查询student表中的所有记录的sname、ssex和class列。
root@localhost [linux50] >select sname,ssex,class from student;
+-------+------+-------+
| sname | ssex | class |
+-------+------+-------+
| 旭 | 1 | L5 |
| 东 | 1 | L5 |
+-------+------+-------+
2 rows in set (0.00 sec)
2.查询教师所有的单位即不重复的depart列。
root@localhost [linux50] >select distinct depart from teacher;
+-----------+
| depart |
+-----------+
| 语言系 |
| 文学系 |
| 科学系 |
+-----------+
3 rows in set (0.00 sec)
3.查询student表的所有记录。
root@localhost [linux50] >select * from student;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 4 | 旭 | 20 | 1 | 2023-07-25 18:18:30 | 5 |
| 5 | 东 | 21 | 1 | 2023-07-25 18:18:28 | 5 |
| 6 | 李 | 18 | 0 | 2023-07-25 19:38:13 | 9 |
| 7 | 明 | 22 | 1 | 2023-07-25 19:39:16 | 7 |
| 8 | 王 | 11 | 1 | 2023-07-25 20:06:23 | 2 |
| 9 | 徐 | 23 | 1 | 2023-07-25 20:06:42 | 2 |
+-----+-------+------+------+---------------------+-------+
6 rows in set (0.00 sec)
4.查询score表中成绩在60到80之间的所有记录。
root@localhost [linux50] >select * from score;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
| 1 | 1 | 90.0 |
| 2 | 1 | 10.0 |
| 3 | 1 | 60.0 |
| 1 | 2 | 90.0 |
| 2 | 2 | 99.5 |
| 3 | 2 | 80.0 |
| 1 | 3 | 80.5 |
| 2 | 3 | 60.0 |
| 3 | 3 | 88.0 |
+-----+-----+------+
9 rows in set (0.00 sec)
root@localhost [linux50] >select * from score where mark>=60 and mark<=80;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
| 3 | 1 | 60.0 |
| 3 | 2 | 80.0 |
| 2 | 3 | 60.0 |
+-----+-----+------+
3 rows in set (0.00 sec)
5.查询score表中成绩为85，86或88的记录。
root@localhost [linux50] >select * from score where mark in ('85','86','88');
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
| 3 | 3 | 88.0 |
+-----+-----+------+
1 row in set (0.00 sec)
6.查询student表中2班或性别为“女”的同学记录。
## 查询没加''报错
root@localhost [linux50] >select * from student where class=2 or ssex=0;
ERROR 1054 (42S22): Unknown column '2' in 'where clause'
root@localhost [linux50] >select * from student where class='2' or ssex='0';
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 6 | 李 | 18 | 0 | 2023-07-25 19:38:13 | 9 |
| 8 | 王 | 11 | 1 | 2023-07-25 20:06:23 | 2 |
| 9 | 徐 | 23 | 1 | 2023-07-25 20:06:42 | 2 |
+-----+-------+------+------+---------------------+-------+
3 rows in set (0.00 sec)
7.以class降序查询Student表的所有记录。
root@localhost [linux50] >select * from student order by class;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | 王 | 11 | 1 | 2023-07-25 20:06:23 | 2 |
| 9 | 徐 | 23 | 1 | 2023-07-25 20:06:42 | 2 |
| 4 | 旭 | 20 | 1 | 2023-07-25 18:18:30 | 5 |
| 5 | 东 | 21 | 1 | 2023-07-25 18:18:28 | 5 |
| 7 | 明 | 22 | 1 | 2023-07-25 19:39:16 | 7 |
| 6 | 李 | 18 | 0 | 2023-07-25 19:38:13 | 9 |
+-----+-------+------+------+---------------------+-------+
6 rows in set (0.00 sec)
8.以cno升序、mark降序查询Score表的所有记录
root@localhost [linux50] >select * from score order by cno asc, mark desc;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
| 1 | 1 | 90.0 |
| 3 | 1 | 60.0 |
| 2 | 1 | 10.0 |
| 2 | 2 | 99.5 |
| 1 | 2 | 90.0 |
| 3 | 2 | 80.0 |
| 3 | 3 | 88.0 |
| 1 | 3 | 80.5 |
| 2 | 3 | 60.0 |
+-----+-----+------+
9 rows in set (0.00 sec)
9.查询2班的学生人数。
# count == wc -l
root@localhost [linux50] >select count(*) as student_count from student where class='2';
+---------------+
| student_count |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
10.查询”曾志高翔“教师任课的学生成绩。
root@localhost [linux50] >select * from course;
+-----+--------+-----+
| cno | cname | tno |
+-----+--------+-----+
| 1 | 数学 | 001 |
| 2 | 语文 | 002 |
| 3 | 英语 | 003 |
+-----+--------+-----+
3 rows in set (0.00 sec)
root@localhost [linux50] >select * from score;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
| 1 | 1 | 90.0 |
| 2 | 1 | 10.0 |
| 3 | 1 | 60.0 |
| 1 | 2 | 90.0 |
| 2 | 2 | 99.5 |
| 3 | 2 | 80.0 |
| 1 | 3 | 80.5 |
| 2 | 3 | 60.0 |
| 3 | 3 | 88.0 |
+-----+-----+------+
9 rows in set (0.00 sec)
root@localhost [linux50] >select * from student;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | 旭 | 20 | 1 | 2023-07-26 17:33:14 | 5 |
| 2 | 东 | 21 | 1 | 2023-07-26 17:33:27 | 5 |
| 3 | 徐 | 22 | 1 | 2023-07-26 17:33:39 | 5 |
+-----+-------+------+------+---------------------+-------+
3 rows in set (0.00 sec)
root@localhost [linux50] >select * from teacher;
+-----+--------+------+------+------------+-----------+
| tno | tname | tage | tsex | prof | depart |
+-----+--------+------+------+------------+-----------+
| 001 | 曾导 | 18 | 1 | 教学总垠 | 语言系 |
| 002 | 徐导 | 50 | 1 | 讲师 | 文学系 |
| 003 | 李导 | 80 | 1 | 助教 | 科学系 |
+-----+--------+------+------+------------+-----------+
3 rows in set (0.00 sec)
root@localhost [linux50] >select teacher.tname,course.cname,student.sname,score.mark from teacher join
course on teacher.tno=course.tno join score on course.cno=score.cno join student on score.sno=student.sno where teacher.tname='曾导';
root@localhost [linux50] >select teacher.tname,course.cname,student.sname,score.mark
from teacher
join course on teacher.tno=course.tno
join score on course.cno=score.cno
join student on score.sno=student.sno
where teacher.tname='曾导';
+--------+--------+-------+------+
| tname | cname | sname | mark |
+--------+--------+-------+------+
| 曾导 | 数学 | 旭 | 90.0 |
| 曾导 | 数学 | 东 | 10.0 |
| 曾导 | 数学 | 徐 | 60.0 |
+--------+--------+-------+------+
3 rows in set (0.00 sec)
11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
root@localhost [linux50] >select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher
join course on teacher.tno=course.tno
join score on score.cno=course.cno
join student on student.sno=score.sno
where student.ssex='1' and course.cname='语文';
+--------+--------+-----------+-------+--------+------+
| tname | prof | depart | sname | cname | mark |
+--------+--------+-----------+-------+--------+------+
| 徐导 | 讲师 | 文学系 | 旭 | 语文 | 90.0 |
| 徐导 | 讲师 | 文学系 | 东 | 语文 | 99.5 |
| 徐导 | 讲师 | 文学系 | 徐 | 语文 | 80.0 |
+--------+--------+-----------+-------+--------+------+
3 rows in set (0.00 sec)
12.把11题查出的成绩按照降序排序。
sql练习
1.查询表里所有的数据
select * from city;
# 查询 所有字段 从 表；
2.查询部分字段的数据
select name from city limit 10;
# 查询name和CountryCode的列，的前10行。
select name,CountryCode from city limit 10;
3.查询city表中，所有中国的城市信息CHN
root@localhost [world] >select * from city where CountryCode='CHN';
4.查询city表中，城市名称叫北京的信息
root@localhost [world] >select * from city where District='peking';
5.查询人口数小于100人城市信息
root@localhost [world] >select * from city where Population<100;
6.查询中国，人口数超过500w的所有城市信息
root@localhost [world] >select * from city where CountryCode='CHN' and Population>5000000;
7.查询中国或美国的城市信息
root@localhost [world] >select * from city where CountryCode='CHN' or CountryCode='USA';
root@localhost [world] >select * from city where CountryCode in ('CHN','USA');
8.查询人口数为100w-200w (包括两头)城市信息
root@localhost [world] >select * from city where Population>=1000000 and Population<=2000000;
root@localhost [world] >select * from city where Population between 1000000 AND 2000000;
9.查询中国或美国，人口数大于500w的城市
root@localhost [world] >select * from city where CountryCode in ('CHN','USA') and Population>5000000;
10.查询CHN城市名为qing开头的城市信息
root@localhost [world] >select * from city where CountryCode='CHN' and name like 'qing%';
11.查询 城市名为jing结尾的城市信息，只显示name和Population列
root@localhost [world] >select name,Population from city where CountryCode='CHN' and name like '%jing';
12.查询mysql里都有哪些用户和主机地址
13.查询表的字段都有哪些
desc city;