适用场合
使用MyISAM: 不需事务,空间小,以查询访问为主
使用InnoDB: 多删除、更新操作,安全性高,事务处理及并发控制
查看当前默认存储引擎
语法:SHOW VARIABLES LIKE ‘storage_engine%’;
修改存储引擎:
修改my.ini配置文件:default-storage-engine= InnoDB 把InnoDB改成其他存储
设置表的存储引擎:(此为单张表的存储引擎,如果某个表基本上不做并发事务或者大量修改,基本上都是用来查询,可以用这个方法来设置为MyISAM)
如果没有写存储引擎,则默认是InnoDB
语法: CREATE TABLE 表名( #省略代码
)ENGINE=存储引擎;
举例: CREATE TABLE `myisam` ( id INT(4) )ENGINE=MyISAM;
*.frm:表结构定义文件 *.MYD:数据文件
*.MYI:索引文件
InnoDB类型表文件
*.frm:表结构定义文件
ibdata1文件
注意:存储位置 因操作系统而异,可查my.ini
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" innodb_data_home_dir="D:/MySQL Datafiles/"
语法: INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
注意: 字段名是可选的,如省略则依次插入所有字段 多个列表和多个值之间使用逗号分隔 值列表和字段名列表一一对应 如插入的是表中部分数据,字段名列表必填
举例1: INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)
VALUES('123','黄小平',1,'13956799999','1996-5-8');
举例2:
如果没写字段名,需要一一对应,不能缺,如果不想写数据,就用null。
如果插入的是表中部分数据,字段名列表必填,如果有设置默认值的字段,可以不写字段名和数据
语法: INSERT INTO 新表(字段名列表)
VALUES(值列表1),(值列表2),……,(值列表n);
实例: INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);
注意:为避免表结构发生变化引发的错误,建议插入数据时写明具体字段名!
举例:
如果插入的是表中部分数据,多行同时插入
语法:
方法一: 此方法用于建立新表
CREATE TABLE 新表(SELECT 字段1,字段2…… FROM 原表);
方法二: 此方法用于已有表,直接插入查询结果。数据源类型要与原表一致!
INSERT INTO 表名(SELECT 字段1,字段2…… FROM 原表);
实例: CREATE TABLE `phoneList`(
SELECT `studentName`,`phone`
FROM `student`);
注意:如新表已存在,将会报错!如新表已存在,不能重复创建
举例: 编写SQL语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中
语法: UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];
实例: UPDATE student SET sex = ‘女’; UPDATE student SET address = ‘北京女子职业技术学校家政班’
WHERE address = ‘北京女子职业技术学校刺绣班’;
注意:如果没有加条件,则所有数据都会变。基本机制是逐行匹配
举例:
语法: 方法一: DELETE FROM 表名 [WHERE条件];
方法二: TRUNCATE TABLE 表名;
注: TRUNCATE语句删除后将重置自增列,表结构及其字段、约束、索引保持不变,
执行速度比DELETE语句快
实例: DELETE FROM student WHERE studentName = ‘王宝宝’; TRUNCATE TABLE student;
举例:
练习一:
为科目表添加数据,要求使用一条INSERT语句
举例:
方式: SELECT * FROM 表名
查询产生一个虚拟表 看到的是表形式显示的结果,但结果并不真正存储
每次执行查询只是从数据表中提取数据,并按照表的形式显示出来
语法: SELECT <列名|表达式|函数|常量> FROM <表名> [WHERE <查询条件表达式>] [ORDER BY <排序的列名>[ASC或DESC]];
实例: SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1
ORDER BY `studentNo`;
查询全部的行和列 语法:SELECT * FROM `student`;
查询部分列 语法: SELECT `studentNo`,`studentName`,`address` FROM `student` WHERE `address`=‘河南新乡’;
使用AS命名列 给列标题换中文名
SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名, `address `AS 学生地址 FROM `student`
WHERE `address` <> '河南新乡‘;
注意: <>是除了他以外的所有。
SELECT `firstName` + '.' + `lastName` AS 姓名 FROM `employees`;
注意: 1. + 连接的数据类型必须兼容 2. 如果 + 连接字符型数据,结果为字符串数据的连接 3. 如果 + 连接数值型数据,结果为数值的和
语法:SELECT `studentName` FROM `student` WHERE `email` IS NULL;
举例: 查询email里是null的列。
原来有数据,但是数据被清除的列如何查询?
答:使用常量列。
实例:SELECT `studentName` AS 姓名,`address` AS 地址, ’北京信息中心’ AS 学校名称
FROM `student`;
举例:
结果是:
举例:
产生结果:
举例:
#字符串连接 SELECT CONCAT('周','大','刚') #字符串替换 (原字符,开始位置,长度,新字符) SELECT INSERT('刘瑞泽',1, 2, '锐则') #将字符串转为小写 SELECT LOWER('HELLOW') #将字符串转为大写 SELECT UPPER('hellow') #字符串截取
SELECT SUBSTRING('this is my world',3,6)
举例: #获取当前日期 SELECT CURDATE() #获取当前时间 SELECT CURTIME() #获取当前日期和时间 SELECT NOW() #返回日期date为一年中的第几周 SELECT WEEK(NOW()) #返回日期date的年份 SELECT YEAR(NOW()) #返回时间time的小时值 SELECT MINUTE(NOW()) #返回日期参数date1和date2之间相隔的天数 前一个数减后一个数 以天为单位 SELECT DATEDIFF(NOW(),'1993-6-14 14:00') #计算日期参数date加上n天后的日期
SELECT ADDDATE('1993-6-14 14:00',9147)
常用函数——数学函数
举例: #返回大于或等于数值x的最小整数 SELECT CEIL(5.2) #返回小于或等于数值x的最大整数 SELECT FLOOR(5.8) #返回0~1间的随机数
SELECT RAND()
ORDER BY子句实现按一定顺序显示查询结果
练习:把成绩都降低10%后加5分,再查询及格成绩,并按照成绩从高到低排序
答案: SELECT `studentNo` AS 学生编号,(studentResult*0.9+5 ) AS 综合成绩 FROM `result` WHERE (`studentResult`*0.9+5) >=60
ORDER BY studentResult DESC;
实例:
MySQL查询语句中使用LIMIT子句限制结果集
语法: SELECT <字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [ORDER BY <排序的列名>[ASC 或 DESC]] [LIMIT [位置偏移量,]行数];
举例:
1.将学生表中学号为2的学生的邮箱修改为stu20000@163.com,密码改为000
2.将科目表中课时数大于200且年级编号为1的科目的课时减少10
3.将所有年级编号为1的学员姓名、性别、出生日期、手机号码信息保存到新表student_grade1中
4.查询2016年2月17日考试前5名的学员的学号和分数
5.将所有女学生按年龄从大到小排序,从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息
7.查询参加2016年2月17日考试的所有学员的最高分、最低分、平均分
题目:编写SQL语句,查看年龄比“李斯文”小的学生,要求显示这些学生的信息
方法一: 分析:第一步:查询得到“王小姐”的出生日期 第二步:利用WHERE语句,筛选出生日期比“王小姐”大的学生 #1 SELECT `bornDate` FROM `student` WHERE `studentName`='王小姐' #2 SELECT * FROM `student` WHERE bornDate <'2011-06-16 21:52:28'
方法二:子查询
注意:将子查询和比较运算符联合使用,必须保证子查询返回的值不能多于一个
SELECT * FROM `student` WHERE bornDate <( SELECT `bornDate` FROM `student` WHERE `studentName`='王小姐' )
练习题:
查询指定学生成绩
题目:查询参加最近一次Logic Java考试成绩的学生的最高分和最低分
题目:
1.查询“Logic Java”课程考试成绩为60分的学生名单
2.查询参加“Logic Java”课程最近一次考试的在读学生名单
3.查询未参加“Logic Java”课程最近一次考试的在读学生名单