MySQL学习笔记（一）

小满先生！

已于 2022-02-28 19:22:24 修改

阅读量279

点赞数

分类专栏： MySQL 文章标签： mysql

于 2020-09-10 11:06:17 首次发布

本文链接：https://blog.csdn.net/qq_43473072/article/details/108374736

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL学习笔记（一）

关系型数据库

一、MySQL的基础操作与基础了解

1.如何登陆数据库服务器？

// windows
mysql -uroot -p(密码)
// mac
/usr/local/mysql/bin/mysql -u root -p
// /usr/local/mysql/bin/mysql是MySQL的安装路径，root是你MySQL的用户名，二者需要根据自己的情况进行调整。

2.如何查询服务器中所有的数据库？

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.08 sec)

3.如何选中某一个数据库进行操作？

（1）在查询数据前应选中数据

 mysql> select * from admin;  //查询
ERROR 1046 (3D000): No database selected
mysql> use sys	//选中数据库
Database changed   //数据更改

（2）SQL语句中的查询

mysql> select * from admin;  //对应选中数据库查询
//由于数据库中不存在admin所以会报错，正常可以遍历。
ERROR 1146 (42S02): Table 'sys.admin' doesn't exist

（3）查询指定数据

//查询指定数据
mysql> select * from admin where Admin_ID=1;
ERROR 1146 (42S02): Table 'sys.admin' doesn't exist

（4）退出数据库

//退出数据库
mysql> exit;
Bye

4.如何创建与删除数据库？
创建：create databases表名
删除：drop databases表名

mysql> show databases;//查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;//创建数据库
Query OK, 1 row affected (0.02 sec)
mysql> show databases;//查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test//先选中
Database changed
mysql> show tables//查看数据库中所有的数据表
    -> ;
Empty set (0.02 sec)
mysql> CREATE TABLE pet( 
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);//在数据库中创建数据表
Query OK, 0 rows affected (0.05 sec)
mysql> show tables	//查看数据表创建成功
    -> ;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)
mysql> describe pet//查看pet数据表  //查看创建好的数据表的结构
    -> ;	//注意分号不能丢
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

5.如何用INSERT增加数据记录？

mysql> select * from pet;//查询数据记录
Empty set (0.01 sec)
mysql> INSERT INTO pet//往数据表中添加数据记录
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from pet;//再一次查询数据记录
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO pet	//往数据表中添加数据记录
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from pet;	//再一次查询
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO pet
    -> VALUES('旺财','周星驰','狗','公',1990-01-01,NULL); //注意这里的日期格式是字符
ERROR 1292 (22007): Incorrect date value: '1988' for column 'birth' at row 1
mysql> INSERT INTO pet
    -> VALUES('旺财','周星驰','狗','公','1990-01-01',NULL);
Query OK, 1 row affected (0.00 sec)
mysql>  select * from pet;//再一次查询
+----------+--------+---------+------+------------+-------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+-------+
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰 | 狗      | 公   | 1990-01-01 | NULL  |
+----------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

6.MySql数据类型与类型选择
（1）MySql常用的数据类型
数值：

日期/时间：

字符串（字符）类型：

数据类型选择：
日期选择按照格式选择；
数值、字符串按照大小选择。
7.数据记录增删改查基础操作
（1）增：
insert into +数据表
values(内容);

mysql> insert into pet values('Claws','Gwen','cat','m','1994-03-17',NULL);
Query OK, 1 row affected (0.00 sec)

（2）删：
delete from+数据表+where+条件

mysql> delete from pet where name='hsw'
    -> ;
Query OK, 1 row affected (0.01 sec)

（3）改：
update +数据表 set 相应数据类=‘修改内容’ +where+条件

mysql> update pet set name='旺旺财' where owner='周星驰';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(4)查：
select * from+表名字

mysql> select * from pet
    -> ;
+----------+--------+---------+------+------------+-------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+-------+
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL  |
| 旺旺财   | 周星驰 | 狗      | 公   | 1990-01-01 | NULL  |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL  |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL  |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL  |
+----------+--------+---------+------+------------+-------+
6 rows in set (0.00 sec)

总结：
增加–insert；删除–delete；修改–update；查询–select。

二、MySQL的建表约束

1.主键约束
它能够唯一确定一张表中的位移记录，通过给某个字段添加约束，就可以使得该字段不重复且不为空。
关键字：primary key
（1）单一主键约束：
create table user(id int primary key,name varchar(20));

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> create table user(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> describe user;//id中得key含有key约束即id不可重复不可为空
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into user
    -> values('1','李珊珊');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user
    -> values('1','李珊珊');//存在不能在添加
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
mysql> insert into user
    -> values('','李珊珊');//不能为空
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

（2）联合主键约束：
只要联合的主键值加起来不重复就可以，二者之一不可为空

create table user1(id int ,name varchar(20),password varchar(20),primary key(id,name));
insert into user1 values(1,'张三','123');
insert into user1 values(1,'张三','123');//不可添加都重复
insert into user1 values(2,'张三','123');//可以添加，id不重复
insert into user1 values(1,'李四','123');//可以添加name不重复
insert into user1 values(NULL,'张三','123');//空不可添加
mysql> select * from user1
    -> ;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 张三 | 123      |
|  1 | 李四 | 123      |
|  2 | 张三 | 123      |
+----+------+----------+
3 rows in set (0.00 sec)

建表后的主键约束添加与删除：
（1）添加：
alter table +数据表+add primary key(元素)；

create table user3(id int ,name varchar(20));
alter table user3 add primary key(id);
mysql> desc user3
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

（2）删除：
alter table +数据表+drop primary key；

alter table user3 drop primary key;
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(3)修改：
alter table +数据表 modify 元素与类型 primary key;

alter table user3 modify id int primary key;
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.自增约束
他能够在主键约束的基础上，每次自动约束元素自动增长。
关键字：primary auto_increment

create table user2(id int primary key auto_increment,name varchar(20));
insert into user2 (name) values('张三');//只写入name---自增约束使该元素id自动增加1
insert into user2 (name) values('张三')//再一次写入id为2
mysql> select * from user2
    -> ;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 张三 |
+----+------+
2 rows in set (0.00 sec)

3.唯一约束
约束修饰字段的值不可以重复。
关键字：unique
（1）添加：
3种添加:
1.建表添加
2.可以使用alter…add…
3.alter…modif…

//创建添加
//create table user4(id int,name varchar(20)，unique(name));//联合方式
//create table user4(id int,name varchar(20) unique);//单个方式

//后续添加方式
create table user4(id int,name varchar(20));
alter table user4 add unique(name);//add添加
insert into user4 values(1,'zhangsan');
insert into user4 values(2,'zhangsan');//名字相同不能重复插入
alter table user4 modify id int unique;//modif的添加

mysql> desc user4
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from user4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

//注意
create table user5(id int,name varchar(20),unique(name,id));//联合方式---组合在一起不重复
insert into user5 values(1,'zhangsan');
insert into user5 values(1,'zhangsan');//不可以添加
insert into user5 values(2,'zhangsan');//可以添加

mysql> select * from user5;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
mysql> desc user5 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

（2）删除：
1个删除：
1.alter…drop…

alter table user4 drop index id;
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4.非空约束
修饰的字段不能为空
关键词：not null
（1）添加
同上

create table user6(id int,name varchar(20) not null);
insert into user6 (id) values(1);//不可以name为空
insert into user6 values(1,'张三');//可以
insert into user6 (name) values('lisi');//可以

mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from user6
    -> ;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | lisi   |
+------+--------+
2 rows in set (0.00 sec)

（2）删除:
同上

5.默认约束
当我们插入字段值的时候，如果没有传值就会使用默认值
关键字：default +默认值

create table user7(id int,name varchar(20),age int default 10);
insert into user7 (id,name) values(1,'zhangsan');
insert into user7 values(2,'zhang',20);
mysql> select * from user7
    -> ;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    2 | zhang    |   20 |
+------+----------+------+
2 rows in set (0.00 sec)
mysql> desc user7
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

6.外键约束
设计到两个表，一个是父表、一个是子表
关键字：foreign key(元素) references 其他表(元素));

create table classes(id int primary key,name varchar(20));//班级表
create table students(id int primary key,name varchar(20),class_id int,foreign key(class_id) references classes(id));//学生表

insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');
mysql> desc classes
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
|  3 | 三班   |
|  4 | 四班   |
+----+--------+
4 rows in set (0.00 sec)
insert into students values(1001,'张三',1);
insert into students values(1002,'张三',2);
insert into students values(1003,'张三',3);
insert into students values(1004,'张三',4);
mysql> desc students ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
| 1004 | 张三   |        4 |
+------+--------+----------+
4 rows in set (0.00 sec)
//insert into students values(1005,'李四',5);//主表classes中没有的数据值，在副标中，是不可以使用的。
mysql> insert into students values(1005,'李四',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
//delete from classes where id=4;//主表中的记录被副标引用，是不可以被删除的
mysql> delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

总结：
1.主表（父表）classes中没有的数据值，在副标（子表）中，是不可以使用的。
2.主表中的记录被副标引用，是不可以被删除的。

三、数据库的三大设计范式

1.第一范式（1NF）
数据表中的所有字段都是不可分割的原子值。

create table student2(id int primary key,name varchar(20),address varchar(30));
insert into student2 values (1,'张三','中国黑龙江省克山县西城镇自治村10组');
insert into student2 values (2,'李四','中国黑龙江省克山县西城镇群众村1队');
insert into student2 values (3,'王五','中国黑龙江省克山县西城镇自治村1组');
insert into student2 values (4,'赵六','中国黑龙江省克山县西城镇自治村2组');
//地点的字段还可以拆分，不满足第一范式
create table student3(id int primary key,name varchar(20),cuntry varchar(30),province varchar(30),city varchar(30),details varchar(30));

//后面的details还可以分

总结：范式设计的越详细，对于某些实际操作可能更好，但是不一定都是好处。主要根据实际开发使用。

2.第二范式(2NF)
必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖与主键。如果要出现不完全依赖，只可能发生在联合主键的情况下。

//订单表
create table myorder(product_id int,customer_id int,product_name varchar(20),customer_name varchar(20),primary key(product_id,customer_id));
//--问题？除主键以外的其他列，只依赖于主键的部分字段。
//解决？拆表
create table myorder1(
	order_id int primary key,
	product_id int,
	customer_id int
);

create table product(
	id int primary key,
	name varchar(20)
);

create table customer(
	id int primary key,
	name varchar(20)
);
//分成三个表就满足设计

3.第三范式（3NF）
必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

create table myorder(
	ordeer_id int primary key,
	product_id int,
	customer_id int,
	customer_phone varchar(15)
);//customer存放错误存在传递依赖

create table customer(
	id int primary key,
	name varchar(20),
	customer_phone varchar(15)
);//正确存放

四、查询练习

1.数据准备

（1）创建的四个数据表

//学生表
//Student
//学号
//姓名
//性别
//出生年月日
//所在班级
create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(20) not null,
	sbirthday datetime,
	class varchar(20)
);

//教师表
//Teacher
//教师编号
//教师名字
//教师性别
//出生年月日
//职称
//所在部门
create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);

//课程表
//Course
//课程号
//课程名称
//教师编号
create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

//成绩表
//Score
//学号
//课程号
//成绩
//注意这里使用联合主键，否则一个人只能学一个科目
create table score(
	sno varchar(20) ,
	cno varchar(20) not null,
	degree decimal,
	primary key(sno,cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno)
);

（2）数据表添加数据

//student添加数据
insert into student values('101','陈鹤','男','1998-02-23','17303');
insert into student values('102','陈俊宇','男','1999-05-22','17303');
insert into student values('103','程康','男','1999-09-09','17303');
insert into student values('104','高叶子','女','1998-12-13','17303');
insert into student values('105','王建龙','男','1998-01-21','17302');
insert into student values('106','郭雅鑫','女','1999-08-30','17303');
insert into student values('107','阚建文','男','1999-06-09','17301');
insert into student values('108','木健男','男','1998-11-23','17301');
insert into student values('109','于爽','女','1998-10-03','17301');

//teacher添加数据
insert into teacher values('801','李诚','男','1958-12-03','副教授','计算机系');
insert into teacher values('802','张旭','男','1969-09-09','讲师','电子工程系');
insert into teacher values('803','王萍','女','1972-11-13','助教','计算机系');
insert into teacher values('804','刘冰','男','1977-08-14','助教','电子工程系');

//source添加数据
insert into course values('3-105','计算机导论','803');
insert into course values('3-245','操作系统','801');
insert into course values('6-166','数字电路','802');
insert into course values('9-888','计模拟电路','804');

//添加成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','95');
insert into score values('105','3-105','74');
insert into score values('109','3-105','88');
insert into score values('103','9-888','89');
insert into score values('105','9-888','96');
insert into score values('109','9-888','84');
insert into score values('103','6-166','89');
insert into score values('105','6-166','99');
insert into score values('109','6-166','76');

2.查询练习

（1）查询student表的所有记录。

mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 陈鹤      | 男   | 1998-02-23 00:00:00 | 17303 |
| 102 | 陈俊宇    | 男   | 1999-05-22 00:00:00 | 17303 |
| 103 | 程康      | 男   | 1999-09-09 00:00:00 | 17303 |
| 104 | 高叶子    | 女   | 1998-12-13 00:00:00 | 17303 |
| 105 | 王建龙    | 男   | 1998-01-21 00:00:00 | 17302 |
| 106 | 郭雅鑫    | 女   | 1999-08-30 00:00:00 | 17303 |
| 107 | 阚建文    | 男   | 1999-06-09 00:00:00 | 17301 |
| 108 | 木健男    | 男   | 1998-11-23 00:00:00 | 17301 |
| 109 | 于爽      | 女   | 1998-10-03 00:00:00 | 17301 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

（2）查询student表中的所有记录的sname、ssex和class列。

mysql> select sname, ssex, class from student;
+-----------+------+-------+
| sname     | ssex | class |
+-----------+------+-------+
| 陈鹤      | 男   | 17303 |
| 陈俊宇    | 男   | 17303 |
| 程康      | 男   | 17303 |
| 高叶子    | 女   | 17303 |
| 王建龙    | 男   | 17302 |
| 郭雅鑫    | 女   | 17303 |
| 阚建文    | 男   | 17301 |
| 木健男    | 男   | 17301 |
| 于爽      | 女   | 17301 |
+-----------+------+-------+
9 rows in set (0.00 sec)

（3）查询教师所有单位即不重复的depart列。

mysql> select depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
| 计算机系        |
| 电子工程系      |
+-----------------+
4 rows in set (0.00 sec)
//distinct 排除重复元素
mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

（4）查询score表中的成绩在60到80之间的所有记录。

//查询区间  between ... and ...
mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     74 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     76 |
+-----+-------+--------+
4 rows in set (0.04 sec)
//采用运算符比较
mysql> select * from score where degree>60 and degree<80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     74 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     76 |
+-----+-------+--------+
4 rows in set (0.00 sec)

（5）查询score表中的成绩为85，86或88的记录。

//表示或者关系的查询  in
mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 109 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.01 sec)

（6）查询student表中“17303”班或性别为“女”的同学记录。

//表示或者关系（不同字段）
mysql> select * from student where class='17303' or ssex='女';
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 陈鹤      | 男   | 1998-02-23 00:00:00 | 17303 |
| 102 | 陈俊宇    | 男   | 1999-05-22 00:00:00 | 17303 |
| 103 | 程康      | 男   | 1999-09-09 00:00:00 | 17303 |
| 104 | 高叶子    | 女   | 1998-12-13 00:00:00 | 17303 |
| 106 | 郭雅鑫    | 女   | 1999-08-30 00:00:00 | 17303 |
| 109 | 于爽      | 女   | 1998-10-03 00:00:00 | 17301 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)

（7）以class降序查询student表的所有记录。

//升序：asc (默认升序)
mysql> select * from student order by class;
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 107 | 阚建文    | 男   | 1999-06-09 00:00:00 | 17301 |
| 108 | 木健男    | 男   | 1998-11-23 00:00:00 | 17301 |
| 109 | 于爽      | 女   | 1998-10-03 00:00:00 | 17301 |
| 105 | 王建龙    | 男   | 1998-01-21 00:00:00 | 17302 |
| 101 | 陈鹤      | 男   | 1998-02-23 00:00:00 | 17303 |
| 102 | 陈俊宇    | 男   | 1999-05-22 00:00:00 | 17303 |
| 103 | 程康      | 男   | 1999-09-09 00:00:00 | 17303 |
| 104 | 高叶子    | 女   | 1998-12-13 00:00:00 | 17303 |
| 106 | 郭雅鑫    | 女   | 1999-08-30 00:00:00 | 17303 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
//降序：desc
mysql> select * from student order by class desc
    -> ;
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 陈鹤      | 男   | 1998-02-23 00:00:00 | 17303 |
| 102 | 陈俊宇    | 男   | 1999-05-22 00:00:00 | 17303 |
| 103 | 程康      | 男   | 1999-09-09 00:00:00 | 17303 |
| 104 | 高叶子    | 女   | 1998-12-13 00:00:00 | 17303 |
| 106 | 郭雅鑫    | 女   | 1999-08-30 00:00:00 | 17303 |
| 105 | 王建龙    | 男   | 1998-01-21 00:00:00 | 17302 |
| 107 | 阚建文    | 男   | 1999-06-09 00:00:00 | 17301 |
| 108 | 木健男    | 男   | 1998-11-23 00:00:00 | 17301 |
| 109 | 于爽      | 女   | 1998-10-03 00:00:00 | 17301 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

（8）以con升序、degree降序查询score表的所有记录。

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     95 |
| 109 | 3-105 |     88 |
| 105 | 3-105 |     74 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 105 | 6-166 |     99 |
| 103 | 6-166 |     89 |
| 109 | 6-166 |     76 |
| 105 | 9-888 |     96 |
| 103 | 9-888 |     89 |
| 109 | 9-888 |     84 |
+-----+-------+--------+
12 rows in set (0.01 sec)

（9）查询“17303”班的学生人数。

mysql> select count(*) from student where class='17303';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

（10）查询score表中的最高分的学生学号和课程号。

//复合语句的子查询--或者排序
mysql> select sno,cno from score where degree = (select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 105 | 6-166 |
+-----+-------+
1 row in set (0.01 sec)
//1.找到最高分
select max(degree) from score
//2.找最高分的sno和cno
select sno,cno from score where degree = (select max(degree) from score);

//排序解法---排序存在缺陷，如果有多个最高分，，，
//select sno,con,degree from score order by degree; 
mysql> select sno,cno,degree from score order by degree;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 109 | 3-245 |     68 |
| 105 | 3-105 |     74 |
| 105 | 3-245 |     75 |
| 109 | 6-166 |     76 |
| 109 | 9-888 |     84 |
| 103 | 3-245 |     86 |
| 109 | 3-105 |     88 |
| 103 | 6-166 |     89 |
| 103 | 9-888 |     89 |
| 103 | 3-105 |     95 |
| 105 | 9-888 |     96 |
| 105 | 6-166 |     99 |
+-----+-------+--------+
12 rows in set (0.00 sec)
//select sno,cno,degree from score order by degree desc limit 0,1;  (拿第一条)
//limit 从多少开始 ，查多少条
mysql> select sno,cno,degree from score order by degree desc limit 0,1;  
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 6-166 |     99 |
+-----+-------+--------+
1 row in set (0.00 sec)

（11）查询每门课的平均成绩

//首先查看课程
mysql> select * from course;
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 803 |
| 3-245 | 操作系统        | 801 |
| 6-166 | 数字电路        | 802 |
| 9-888 | 计模拟电路      | 804 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)
//avg查看平均值
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.6667 |
+-------------+
1 row in set (0.03 sec)
mysql> select avg(degree) from score where cno='3-245';
+-------------+
| avg(degree) |
+-------------+
|     76.3333 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(degree) from score where cno='6-166';
+-------------+
| avg(degree) |
+-------------+
|     88.0000 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(degree) from score where cno='9-888';
+-------------+
| avg(degree) |
+-------------+
|     89.6667 |
+-------------+
1 row in set (0.00 sec)
//一句话查看所有课程平均值
//group by分组
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.6667 |
| 3-245 |     76.3333 |
| 6-166 |     88.0000 |
| 9-888 |     89.6667 |
+-------+-------------+
4 rows in set (0.00 sec)

（12）查询score表中至少有两名学生选修的并以3开头的课程的平均分数。
分组条件及模糊查询

//group对应分组---having过滤条件----like模糊查询
//查看磕头元素大于2
mysql> select cno from score group by cno having count(cno)>=2;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
| 6-166 |
| 9-888 |
+-------+
4 rows in set (0.00 sec)
//查看开头元素大于4
mysql> select cno from score group by cno having count(cno)>=4;
Empty set (0.00 sec)
//查看以3开头（%表示后面为任意）
mysql> select cno from score group by having count(cno)>=2 and cno like '3%';
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.02 sec)
//显示对应的平均成绩
mysql> select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.6667 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)
//显示人数
mysql> select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     85.6667 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+
2 rows in set (0.00 sec)

（13）查询分数大于70小于90的sno列。
范围查询的两种方式

//1.where查询
mysql> select sno,degree from score where degree>70 and degree<90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     89 |
| 103 |     89 |
| 105 |     74 |
| 105 |     75 |
| 109 |     88 |
| 109 |     76 |
| 109 |     84 |
+-----+--------+
8 rows in set (0.14 sec)
//2.between...and
mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     89 |
| 103 |     89 |
| 105 |     74 |
| 105 |     75 |
| 109 |     88 |
| 109 |     76 |
| 109 |     84 |
+-----+--------+
8 rows in set (0.00 sec)

（14）查询所有学生的sname、cno和degree列。
多表查询

mysql> select sno,sname from student 
    -> ;
+-----+-----------+
| sno | sname     |
+-----+-----------+
| 101 | 陈鹤      |
| 102 | 陈俊宇    |
| 103 | 程康      |
| 104 | 高叶子    |
| 105 | 王建龙    |
| 106 | 郭雅鑫    |
| 107 | 阚建文    |
| 108 | 木健男    |
| 109 | 于爽      |
+-----+-----------+
9 rows in set (0.00 sec)
mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     95 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     89 |
| 103 | 9-888 |     89 |
| 105 | 3-105 |     74 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     99 |
| 105 | 9-888 |     96 |
| 109 | 3-105 |     88 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     76 |
| 109 | 9-888 |     84 |
+-----+-------+--------+
12 rows in set (0.00 sec)
//二者相等时连接
mysql> select sname,cno,degree from student,score where student.sno=score.sno; +-----------+-------+--------+
| sname     | cno   | degree |
+-----------+-------+--------+
| 程康      | 3-105 |     95 |
| 程康      | 3-245 |     86 |
| 程康      | 6-166 |     89 |
| 程康      | 9-888 |     89 |
| 王建龙    | 3-105 |     74 |
| 王建龙    | 3-245 |     75 |
| 王建龙    | 6-166 |     99 |
| 王建龙    | 9-888 |     96 |
| 于爽      | 3-105 |     88 |
| 于爽      | 3-245 |     68 |
| 于爽      | 6-166 |     76 |
| 于爽      | 9-888 |     84 |
+-----------+-------+--------+
12 rows in set (0.00 sec)

（15）查询所有学生的sno,cname,degree列
多表查询

mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 计算机导论      |     95 |
| 105 | 计算机导论      |     74 |
| 109 | 计算机导论      |     88 |
| 103 | 操作系统        |     86 |
| 105 | 操作系统        |     75 |
| 109 | 操作系统        |     68 |
| 103 | 数字电路        |     89 |
| 105 | 数字电路        |     99 |
| 109 | 数字电路        |     76 |
| 103 | 计模拟电路      |     89 |
| 105 | 计模拟电路      |     96 |
| 109 | 计模拟电路      |     84 |
+-----+-----------------+--------+
12 rows in set (0.00 sec)

总结：先分开查，在汇总查，找到二者相同点，想等时连接。
（16）查询所有学生的sname、cname和degree列。
三表查询

//sname->student   cname->course degree->sorce
mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+
| sname     | cname           | degree |
+-----------+-----------------+--------+
| 程康      | 计算机导论      |     95 |
| 王建龙    | 计算机导论      |     74 |
| 于爽      | 计算机导论      |     88 |
| 程康      | 操作系统        |     86 |
| 王建龙    | 操作系统        |     75 |
| 于爽      | 操作系统        |     68 |
| 程康      | 数字电路        |     89 |
| 王建龙    | 数字电路        |     99 |
| 于爽      | 数字电路        |     76 |
| 程康      | 计模拟电路      |     89 |
| 王建龙    | 计模拟电路      |     96 |
| 于爽      | 计模拟电路      |     84 |
+-----------+-----------------+--------+
12 rows in set (0.01 sec)

（17）查询“17303”班学生每门课的平均分。
子查询加分组求平均分

//1.select * from student where class='17303';
//2.select sno from student where class = '17303';
//3.select *from from score where sno in(select sno from student where class = '17303');

mysql> select cno,avg(degree) from score where sno in (select sno from student where class = '17303') group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     95.0000 |
| 3-245 |     86.0000 |
| 6-166 |     89.0000 |
| 9-888 |     89.0000 |
+-------+-------------+
4 rows in set (0.03 sec)

（18）查询选修‘3-105’课程的成绩高于‘109’号同学‘3-105’成绩的所有同学的记录
子查询

//1.select degree from score where sno='109' and cno = '3-105';
//2.select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
mysql> select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     95 |
+-----+-------+--------+
1 row in set (0.07 sec)

（19）查询成绩高于学号为‘109’、课程号为‘3-105’的成绩的所有记录。
子查询

//1.select degree from score where cno='3-105';
//2.select degree from score where cno='3-105' and sno='109';
//3.select * from score where degree>(select degree from score where cno='3-105' and sno='109');
mysql> select * from score where degree>(select degree from score where cno='3-105' and sno='109');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     95 |
| 103 | 6-166 |     89 |
| 103 | 9-888 |     89 |
| 105 | 6-166 |     99 |
| 105 | 9-888 |     96 |
+-----+-------+--------+
5 rows in set (0.00 sec)

下接https://blog.csdn.net/qq_43473072/article/details/108484749

解决linux数据表不能插入中文

解决linux数据表不能插入中文：

找到字段字符集为“latin1”的字段，修改字段字符集为utf8：
mysql> alter table 表格名 modify 字段 varchar(20) character set utf8;
建表解决：
加入ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

解决MySQL连接不上的问题

如果想允许用户root使用密码123456从任何主机连接到mysql服务器
mysql>grant all privileges on . to ‘root’@’%’ identified by ‘123456’ with grant option;
mysql>flush privileges;
如果想允许用户root使用密码123456从ip为192.168.1.6的主机连接到mysql服务器
mysql>grant all privileges on . to ‘root’@‘192.168.1.6’ identified by ‘123456’ with grant
option;
mysql>flush privileges;
如果想允许用户root使用密码123456从ip为192.168.1.6的主机连接到mysql服务器的dk数据库
mysql>grant all privileges on dk.* to ‘root’@’%’ identified by ‘123456’ with grant option;
mysql>flush privileges;

https://www.cnblogs.com/xyzdw/archive/2011/08/11/2135227.html