文章目录:
4.1 基本查询语句(SELECT , FROM , WHERE)
4.5 查询空值(IS NULL)与去重(DISTINCT)
1.数据库的相关操作
· 1.1 连接数据库
mysql -u[用户名] -p[密码] -h[主机名](最后一个可以不加,特定情况时需要添加)
· 1.2 创建数据库
模板:CREATE DATABASE 数据库名; 举例:CREATE DATABASE school;
· 1.3 查看数据库有哪些
模板:SHOW DATABASES; (无需加具体的数据库名)
· 1.4 查看指定的数据库
模板:SHOW CREATE DATABASE 数据库名; 举例:SHOW CREATE DATABASE school;
· 1.5 删除指定的数据库
模板:DROP DATABASE 数据库名; 举例:DROP DATABASE school;
· 1.6 使用数据库(很重要)
模板:USE 数据库名; 举例:USE school; (在对其中一个数据库进行一系列操作的时候,必须要先声明使用的是哪一个数据库,即上面的SQL语句!!!)
· 1.7用户的创建和删除
首先连接数据库 mysql -u[用户名] -p[密码] -h[主机名](这个可以不加,特定情况需要添加) 在这里我们通过root用户来创建新的用户 模板:CREATE USER "用户名"@"localhost" identified by "密码"; (用户名和localhost两侧的引号可加可不加) 举例:CREATE USER "U4"@"localhost" identified by"123456"; (此时,我们使用root用户创建了一个新用户U4,密码为123456) 对于新创建的用户,我们也可以对它进行删除 模板:DROP USER "用户名"@"localhost"; 举例:DROP USER "U4"@"localhost"; (此时,我们删除了上面创建的新用户U4)
· 1.8 授权
在创建的一些用户之后,我们可以为指定的用户授权!!! 模板:GRANT <权限列表> [权限可以是:SELECT,UPDATE,DELETE,INSERT] ON <数据库对象> [数据库对象可以是基本表、视图] TO <用户名> [WITH GRANT OPTION]; [添加上这条语句之后,表示被授权的用户可以继续将权限转授给其他用户] 举例1:GRANT SELECT ON school.student TO U4; (此时,我们为用户U4授予school数据库中student表的查询权限) (在这里,需要说明一点:如果没有指定具体的数据库,那么在基本表或者视图的前面要加上该数据库名的前缀) (上面的student表是在school数据库中的,所以我们要加上对应数据库名的前缀) (如果不想加这个前缀,也可以使用下面的语句) 举例2:USE school; [出现Database changed,表示需要使用这个数据库] GRANT SELECT ON student (由于我们已经事先声明USE school;所以在这里就不需要添加数据库名的前缀了) TO U4; (这两种方法是一样的,大家可以根据编写SQL语句的习惯来选择!!!) 上面提到的两个例子,是授予部分权限,如果想授予指定用户的所有权限,可以使用下面的语句: 模板:GRANT ALL PRIVILEGES ON school.student TO U5; (此时,我们为用户U5授予了school数据库中student表的所有权限)
· 1.9收权
在授予指定用户的一些权限之后,我们同样可以将这些权限收回!!! 模板:REVOKE <权限列表> [权限可以是:SELECT,UPDATE,DELETE,INSERT] ON <数据库对象> [数据库对象可以是基本表、视图] FROM <用户名> [CASCADE|RESTRICT]; (级联CASCADE:当这个用户为其他用户授权时,我们使用级联将其一并收回) (比如用户root为U4授予了SELECT权限,而用户U4又为U5授予了UPDATE权限,此时使用CASCADE就把两个权限一起收回了) (约束RESTRICT:当这个用户没有给其他用户授权时,才可以将它收回) 举例:REVOKE SELECT,UPDATE(grade) ON school.sc FROM U4 CASCADE; (此时,我们把用户U4对school数据库中student表的查询和更新成绩的权限收回,同时级联收回U4授出给其他用户的权限)
· 1.10 视图的创建与删除
举例1:涉及单个表的视图的创建 创建信工学院开设的课程视图XG_C(cno,cname,credit) 属性名依次表示课程号、课程名、学分,并查询视图中的数据。 CREATE VIEW XG_C(cno,cname,credit) AS SELECT cs_id,cs_name,cs_credit FROM course WHERE cs_depart="信工"; 使用SELECT查询所创建视图中的数据 SELECT * FROM XG_C;
举例2:涉及多个表的视图的创建 创建教师授课视图Tea_C(tno,tname,cno,cname,credit) 属性名依次表示教师号,教师名,课程号,课程名,学分,并查询视图中的数据。 CREATE VIEW Tea_C(tno,tname,cno,cname,credit) AS SELECT teacher.tea_id,tea_name,course.cs_id,cs_name,cs_credit FROM course JOIN teaching ON course.cs_id=teaching.cs_id JOIN teacher ON teaching.tea_id=teacher.tea_id; 使用SELECT查询所创建视图中的数据 SELECT * FROM Tea_C;
举例3:在已有视图的基础上,继续创建新的视图 创建信工学院学生平均成绩视图XG_AVE(sno,gave) CREATE VIEW XG_AVE(sno,gave) AS SELECT sno,AVG(grade) FROM XG_GRADE GROUP BY sno; 使用SELECT查询所创建视图中的数据 SELECT * FROM XG_AVE;
举例4:删除视图 DROP VIEW [视图名]; (可以添加CASCADE级联操作,表示将此视图导出的所有视图一并删除)
· 1.11 断开服务器的连接
模板1:exit; 模板2:quit;
2.表的相关操作
· 2.1 创建表
模板:CREATE TABLE 表名 ( 字段名1 数据类型 [列级约束], 字段名2 数据类型 [列级约束], ...... 表级约束 ); 举例:CREATE TABLE student ( sno char(10), sname varchar(5), sage int(5), sex char(3) ); (在此,我们只是创建了一个基本表,并没有对表中字段添加相关约束,随后会一一介绍!!!)
· 2.2 查看表的结构
模板1:DESCRIBE 表名; 模板2:DESC 表名; 举例: DESC student;
· 2.3 查看表的详细信息
模板:SHOW CREATE TABLE 表名; 举例:SHOW CREATE TABLE student;
· 2.4 查看某个数据库中有哪些表
模板:SHOW TABLES; 举例:SHOW TABLES; (这个和查看有哪些数据库一样,无需添加数据库名或者表名)
· 2.5 删除指定的表
模板:DROP TABLE 表名; 举例:DROP TABLE student;
· 2.6 字段名的数据类型
int 整型 char(n) 定长字符型 varchar(n) 变长字符型 float(m,d) 单精度型,m表示总位数,d表示小数位数 decimal(m,d) 双精度型 date 日期型 (这里只介绍MySQL支持的主要数据类型)
· 2.7 添加主键约束
举例:CREATE TABLE student ( sno char(10) PRIMARY KEY, sname varchar(5), sage int(5), sex char(3) ); (我们在第一个字段名sno的后面添加列级约束:PRIMARY KEY,即指定字段sno为student表的主键)
· 2.8 添加外键约束
模板:CONSTRAINT 外键约束名 FOREIGN KEY(外键名) REFERENCES 主表名(主键名) (在添加外键约束的时候,需要有一个父表(主表)和子表) 举例:CREATE TABLE student ( sno char(10) PRIMARY KEY, sname varchar(5), sage int(5), sex char(3) ); CREATE TABLE score ( sno char(10) PRIMARY KEY, cno char(10), cname varchar(10), ctype varchar(5), CONSTRAINT fk_temp FOREIGN KEY(sno) REFERENCES student(sno) ); (我们在此就为score表添加了外键约束,外键约束名为fk_temp,外键名为sno)
· 2.9 添加常用约束
常用约束类型有: (1)primary key 单一主键约束,primary key(字段名1,字段名2) 联合主键 (2)foreign key 外键约束 (3)unique 唯一约束 ,取值不能重复,但允许有一个为空 (4)null 为空约束(系统默认的) (5)not null 非空约束 (6)default 值 默认约束,给定字段一个默认值, 添加字符串型默认值要使用单引号,表示为'值’。 如果是整型则不需要加任何符号; 如果要添加的是中文默认值,则需要加上 DEFAULT CHARSET=utf8; 使用英文字符则不需要。 (7)auto_increment 自增约束,默认情况下初始值和增量都为1。
1.添加唯一约束 CREATE TABLE student ( sno char(10) PRIMARY KEY, sname varchar(5) UNIQUE, sage int(5), sex char(3) );
2.添加非空约束 CREATE TABLE student ( sno char(10) PRIMARY KEY, sname varchar(5) NOT NULL, sage int(5), sex char(3) );
3.使用默认约束 CREATE TABLE student ( sno char(10) PRIMARY KEY, sname varchar(5), sage int(5), sex char(3) DEFAULT "男" )DEFAULT CHARSET=utf8;
4.设置表的属性值自动增加 CREATE TABLE student ( sno char(10) PRIMARY KEY AUTO_INCREMENT, sname varchar(5), sage int(5), sex char(3) ); (默认情况下,关键词AUTO_INCREMENT的初始值的增量均为1)
3.数据库和表的基本操作
· 3.1 修改表名
模板:ALTER TABLE 旧表名 RENAME [TO] 新表名; 举例:ALTER TABLE student RENAME people; (此时我们把student表修改为一个新的表名people)
· 3.2 修改字段名和字段的数据类型
模板1:修改字段名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型; (数据类型必须跟在新字段名的后面,数据类型可改可不改,如果不需要修改字段的数据类型, 可以把新字段的数据类型设置为和原来一样,但是,千万不要空着它!) 举例: ALTER TABLE student CHANGE sage Sage int(5);
模板2:修改字段的数据类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型; 举例: ALTER TABLE student MODIFY sage varchar(5);
· 3.3 添加和删除字段
模板1:添加字段(默认在表的最后一列添加字段) ALTER TABLE 表名 ADD 字段名 数据类型 [约束类型]; 举例:ALTER TABLE student ADD smajor varchar(10) NOT NULL;
模板2:添加字段(指定在表的第一列添加字段) ALTER TABLE 表名 ADD 字段名 数据类型 [约束类型] FIRST; 举例:ALTER TABLE student ADD smajor varchar(10) FIRST;
模板3:添加字段(指定在表的某一列添加字段) ALTER TABLE 表名 ADD 字段名 数据类型 [约束类型] ALTER 指定已存在的字段名; 举例:ALTER TABLE student ADD smajor varchar(10) AFTER sage;
模板4:删除字段 ALTER TABLE 表名 DROP 字段名; 举例:ALTER TABLE student DROP smajor;
· 3.4 修改字段的排列位置
模板1:修改字段为表的第一字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 FIRST; 举例:ALTER TABLE student MODIFY sname varchar(5) FIRST;
模板2:修改字段到表的某个字段之后 ALTER TABLE 表名 MODIFY 字段名 数据类型 AFTER 指定字段名; 举例:ALTER TABLE student MODIFY sname varchar(5) AFTER sage;
· 3.5 删除表的外键约束
模板:ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名; 举例:ALTER TABLE score DROP FOREIGN KEY fk_temp; (其中fk_temp为score表的外键约束名)
· 3.6 插入数据(INSERT INTO , VALUES)
模板1:INSERT INTO 表名 (字段名1,字段名2,......) VALUES(字段名1的值,字段名2的值,......), (字段名1的值,字段名2的值,......), ...... (字段名1的值,字段名2的值,......); (如果插入的全部列的值,则INSERT INTO 表名后面的内容就可以省略;如果是部分列,则不能省略) 举例:INSERT INTO student(sno,sname) VALUES("201805050201","张三"), ("201805050255","李四"), ("201805050230","王五");
模板2:带子查询的插入语句 INSERT INTO 表名 (字段名1,字段名2,......) SELECT 字段名1,字段名2,...... FROM 表名 此处可以添加一些指定条件(WHERE,GROUP BY等等) 举例:INSERT INTO sc(sno,grade) SELECT sno,AVG(grade) FROM student GROUP BY sno; (此时,我们把student表按照学号分组,并计算每个学生的平均成绩,之后再把学号和平均成绩插入新表sc)
· 3.7 查看数据是否成功插入表中
模板:SELECT *; FROM 表名; 举例:SELECT *; FROM student;
· 3.8 更新数据(UPDATE , SET)
模板1:更新表中的所有行的一列值 UPDATE 表名 SET 字段名1=它的新值; 举例:UPDATE student SET sage=20; (此时,我们把student表的sage字段对应的所有值都更新为20) (模板中更新的新值也可以为空,即:NULL)
模板2:更新表中的所有行的多列值 UPDATE 表名 SET 字段名1=它的新值, 字段名2=它的新值, ......; 举例:UPDATE student SET sage=20, sex="男", smajor="计科";
模板3:更新表中指定行的1列或多列值 UPDATE 表名 SET 字段名1=它的新值, 字段名2=它的新值, ...... WHERE 条件表达式; 举例:UPDATE student SET sage=18, sex="女" sno="201805050202" WHERE id=3;
模板4:带子查询的更新语句 UPDATE 表名 SET 字段名=它的新值 (可以有多个字段名) WHERE 条件表达式 IN ( SELECT 字段名 FROM 表名 WHERE 条件表达式 ); 举例:UPDATE sc SET grade=0 WHERE sno IN ( SELECT sno FROM student WHERE sdept="信工" ); (此时,我们把信工学院的全体学生的成绩更新为0)
· 3.9 删除数据(DELETE , TRUNCATE)
模板1:删除表中的指定行 DELETE FROM 表名 WHERE 条件表达式; 举例:DELETE FROM student WHERE sage>=18;
模板2:删除表中的所有行 DELETE FROM 表名; 举例:DELETE FROM student;
模板3:带子查询的删除语句 DELETE FROM 表名 WHERE 字段名 IN ( SELECT 字段名 FROM 表名 WHERE 条件表达式 ); 举例:DELETE FROM sc WHERE sno IN ( SELECT sno FROM student WHERE sdept="信工" ); (此时,我们就删除了信工学院全体学生的选课记录)
模板4:DELETE 和 TRUNCATE TRUNCATE TABLE 表名; 举例:TRUNCATE TABLE student; (DELETE:一条一条的删除表中的数据) (TRUNCATE:直接删除的是表,而不是表中的内容,但是删除结束后,还会重新创建一个表)
4.单表查询
· 4.1 基本查询语句(SELECT , FROM , WHERE)
模板1:查询表中的某个字段 SELECT 字段名 FROM 表名; 举例:SELECT sno FROM student;
模板2:查询表中的多个字段 SELECT 字段名1,字段名2 FROM 表名; 举例:SELECT sno,sname FROM student; 在模板1和2中,我们也可以用关键字 as 给字段名起别名,方便我们查看和理解查询的结果!!! 举例:SELECT sno as 学生学号,sname as 学生姓名 FROM student; (在这里,关键字 as 可以省略)
模板3:查询表中的所有字段 SELECT * FROM 表名; 举例:SELECT * FROM student;
模板4:条件查询 SELECT 字段名1,字段名2 FROM 表名 WHERE 条件表达式; 举例:SELECT sno,sname FROM student WHERE sex<>"女";
· 4.2 带IN和NOT IN关键字的查询
模板:SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,...); 举例:SELECT sno,sname FROM student WHERE grade IN (98,99,100); (查询成绩为98,99,100的学生的学号和姓名) (NOT IN的使用方法和IN是一样的,在这就不再举例了)
· 4.3 带(NOT)BETWEEN AND 的范围查询
模板:SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2; 举例:SELECT sno,sname FROM student WHERE grade BETWEEN 90 AND 100; (查询成绩在90到100之间的所有学生的学号和姓名) (NOT BETWEEN AND 和 BETWEEN AND的使用方法是一样的)
· 4.4 带LIKE的字符匹配查询
· 4.4.1 百分号通配符"%"
模板1:SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符%'; (百分号通配符%可以匹配任意长度的字符,甚至包括零字符。其中 % 的位置可以根据需要在字符之间改变) 举例1:SELECT sno,sname FROM student WHERE sno LIKE "20180505%"; (查询学号以20180505开头的学生学号和姓名) 举例2:SELECT sno,sname FROM student WHERE sno LIKE "%0505%"; (查询学号中间包含0505的学生学号和姓名) 举例3:SELECT sno,sname FROM student WHERE sno LIKE "%0201"; (查询学号结尾为0201的学生学号和姓名)
· 4.4.2 下划线通配符"_"
模板:SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符_'; (下划线通配符"_"只能模糊匹配1个字符) (如果你想用下划线通配符"_"匹配多个字符,那只能多用几个"_"咯!其中"_"的位置可以根据需要在字符间变化。 举例1:SELECT sno,sname FROM student WHERE grade LIKE "_9"; (查询学生成绩以9结尾的学生学号和姓名) 举例2:SELECT sno,sname FROM student WHERE grade LIKE "________02"; (查询学号以02结尾的学生学号和姓名,假设学号是2018050502的格式,那么在02之前就需要用8个下划线通配符"_") 举例3:SELECT sno,sname FROM student WHERE grade LIKE "_子%"; (查询学生姓名中第二个字为"子"的学生学号和姓名)
· 4.5 查询空值(IS NULL)与去重(DISTINCT)
模板:SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL; (查询数据表中的空值对应的数据) 举例:SELECT sno,sname FROM student WHERE grade IS NULL; (查询student表中学生成绩为空的数据)
模板:SELECT DISTINCT 字段名 FROM 表名; 举例:SELECT DISTINCT grade FROM student; (对student表中的成绩字段进行去重处理)
· 4.6 带 AND 与 OR 的多条件查询
模板:SELECT 字段名 FROM 表名 WHERE 表达式1 AND 表达式2; (小提示: 可以同时添加多个过滤条件,增加条件的同时只需增加一个 AND 关键字。) 举例:SELECT sno,sname FROM student WHERE sage=18 AND sex="男"; (查询年龄为18,且性别为男的学生学号和姓名)
模板:SELECT 字段名 FROM 表名 WHERE 表达式1 OR 表达式2; (与AND相反,在WHERE声明中使用OR关键字表示只需满足两个条件中的其中一个条件即可返回结果) 举例:SELECT sno,sname FROM student WHERE sage=18 OR sex="男"; (查询年龄为18或者性别为男的学生学号和姓名,只要满足其中一个就行)
· 4.7 对查询结果进行排序(ORDER BY)
模板:SELECT 字段名 FROM 表名 WHERE 条件表达式 (WHERE子句也可以不加) ORDER BY 字段名 [ASC[DESC]]; (默认升序:ASC,也可以定义降序:DESC) 举例:SELECT sno,sname FROM student WHERE sex="男" ORDER BY sage DESC; (我们对性别为男的学生进行,按年龄降序排序,最后将其学号和姓名输出)
· 4.8 分组查询(GROUP BY)
模板:SELECT 字段名 FROM 表名 GROUP BY 字段名; (分组查询的关键字是Group By,查询的是每个分组中 首次出现的一条记录) (一般情况下,GROUP BY都和聚合函数一起使用) 举例:SELECT * FROM student GROUP BY sage; (在GROUP BY子句之后还可以使用 HAVING 来对分组结果进行筛选)
· 4.9 使用 LIMIT 限制查询结果的数量
模板:SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数; (第一个参数:OFFSET,可选参数,表示偏移量,如果不指定默认值为0,表示从查询结果的第一条记录开始, 若偏移量为1,则从查询结果中的第二条记录开始,以此类推) (第二个参数,记录数,表示返回查询结果的条数) 举例:SELECT * FROM student ORDER BY sage ASC LIMIT 1,4; (表示从student表中第2条记录开始,向后检索4条数据,并按年龄升序排序)