MySQL学习笔记（一）

MySQL学习笔记（一）

一、MySQL的基础操作与基础了解二、MySQL的建表约束三、数据库的三大设计范式四、查询练习解决linux数据表不能插入中文解决MySQL连接不上的问题 关系型数据库

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

1.如何登陆数据库服务器？ mysql -uroot -p(密码) 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