sql 规范:
<1> 在数据库系统中,SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写,表名库名小写;
<2> SQL语句可单行或多行书写,以“;”结尾。关键词不能跨多行或简写。
<3> 用空格和缩进来提高语句的可读性。子句通常位于独立行,便于编辑,提高可读性。
<4> 注释:单行注释:--
多行注释:/*......*/
<5>sql语句可以折行操作,用逗号分隔;
数据库操作:
-- 创建数据库
CREATE DATABASE db_name [CHARACTER SET utf8];
--查看数据库
SHOW DATABASES;查看所有数据库
SHOW CREATE DATABASE db_name;查看数据库的创建信息
--修改数据库
ALTER DATABASE db_name [CHARACTER SET xxx];
--删除数据库
DROP DATABASE db_name;
--使用数据库
USE db_name;
数据表操作:
--创建表
CREATE TABLE table_name(字段1 类型 [约束条件],
字段2 类型 [约束条件],
字段3 类型 [约束条件])[CHARACTER SET xxx];
(字段若是索引:可设置自增,auto_increment;
对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10;
字段也可以设置默认值:defalut 值)
数据类型:
- int 整数
- varchar(n) n 表示字符串长度;
- char(n) n表示固定的字符串长度;
- tinyint ,小整数,数据类型用于保存一些范围的整数数值范围,MySQL中无布尔值,使用tinyint(1)构造。
- decima(m[d])l,精确小数,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
- float[(m.,d)], 单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
- DOUBLE[(M,D)] ,精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
- text数据类型用于保存变长的大字符串,可以组多到65535 (2**16 − 1)个字符。
- set集合类型
- DATE:YYYY-MM-DD(1000-01-01/9999-12-31)
约束条件:
- 非空:not null
- 唯一:unique
- 主键:primary key
-- 查看表结构信息
DESC table_name;查看表结构
SHOW TABLES; 查看当前数据库里的所有表
SHOW CREATE TABLE table_name;查看当前数据表结构
--修改表结构
----增加字段
ALTER TABLE table_name ADD 字段 类型 [约束条件] [first/after 字段名];#[first/after 字段名控制添加字段的位置
---修改字段
ALTER TABLE table_name MODIFY 字段 类型 [约束条件];
---修改字段名
ALTER TABLE tabler-name CHANGE 字段 新字段 类型;
---删除一列
ALTER TABLE table_name DROP 字段;
---修改表名
ALTER TABLLE table_name TO new_table_name;
--删除表;
DROP TABLE table_name;
--表记录的增删改查
---增加信息
INSERT [INTO] table_name (字段1,字段2,。。。) VALUES (值1,。。。),(值2.。。。);
INSERT [INTO] table_name set 字段1=值,字段2=值。。。。;
---修改信息
UPDATE table_name set 字段=值 WHERE 子句;
---删除表记录
DELETE FROM table_name [where 子句];
---查语句
SELECT *|字段1|字段2|。。。FROM table_name
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 限制条数
group function:
--<1> 统计表中所有记录 -- COUNT(列名):统计行的个数 -- 统计一个班级共有多少学生?先查出所有的学生,再用count包上 select count(*) from ExamResult; -- 统计JS成绩大于70的学生有多少个? select count(JS) from ExamResult where JS>70; -- 统计总分大于280的人数有多少? select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280; -- 注意:count(*)统计所有行; count(字段)不统计null值. -- SUM(列名):统计满足条件的行的内容和 -- 统计一个班级JS总成绩?先查出所有的JS成绩,再用sum包上 select JS as JS总成绩 from ExamResult; select sum(JS) as JS总成绩 from ExamResult; -- 统计一个班级各科分别的总成绩 select sum(JS) as JS总成绩, sum(Django) as Django总成绩, sum(OpenStack) as OpenStack from ExamResult; -- 统计一个班级各科的成绩总和 select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)) as 总成绩 from ExamResult; -- 统计一个班级JS成绩平均分 select sum(JS)/count(*) from ExamResult ; -- 注意:sum仅对数值起作用,否则会报错。 -- AVG(列名): -- 求一个班级JS平均分?先查出所有的JS分,然后用avg包上。 select avg(ifnull(JS,0)) from ExamResult; -- 求一个班级总分平均分 select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))) from ExamResult ; -- Max、Min -- 求班级最高分和最低分(数值范围在统计中特别有用) select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) 最高分 from ExamResult; select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) 最低分 from ExamResult; -- 求购物表中单价最高的商品名称及价格 ---SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗? SELECT MAX(price) FROM order_menu; -- 注意:null 和所有的数计算都是null,所以需要用ifnull将null转换为0! -- -----ifnull(JS,0)
正则表达式:
SELECT * FROM employee WHERE emp_name REGEXP '^yu';
外键约束:
ALTER TABLE student ADD CONSTRAINT abc
FOREIGN KEY(charger_id)
REFERENCES classcharger(id);
ALTER TABLE student DROP FOREIGN KEY abc;
多表查询:
内链接:
select * from employee,department where employee.dept_id = department.dept_id;
--select * from employee inner join department on employee.dept_id = department.dept_id;
外链接:
--(1)左外连接:在内连接的基础上增加左边有右边没有的结果 select * from employee left join department on employee.dept_id = department.dept_id;
--(2)右外连接:在内连接的基础上增加右边有左边没有的结果 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;
4、查询平均成绩大于八十分的同学的姓名和平均成绩 SELECT sname,AVG(num) FROM student INNER JOIN score ON student.sid=score.student_id GROUP BY student_id HAVING AVG(num)>80 5、查询所有学生的学号,姓名,选课数,总成绩 SELECT sname,student.sid,COUNT(student.sid),SUM(num) FROM student INNER JOIN score ON student.sid=score.student_id GROUP BY student_id 6、查询姓李老师的个数 SELECT COUNT(tid) FROM teacher WHERE tname LIKE "李%" 7、查询没有报李平老师课的学生姓名 SELECT sname FROM student WHERE sid not in (SELECT DISTINCT student_id FROM score WHERE course_id in (SELECT cid FROM teacher INNER JOIN course ON teacher.tid=course.teacher_id WHERE tname LIKE "李平%")) 8、查询物理课程比生物课程高的学生的学号 SELECT A.student_id,A.num,b.num FROM (SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="物理"))as A INNER JOIN (SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="生物"))as B ON A.student_id=B.student_id WHERE A.num>B.num 9、查询没有同时选修物理课程和体育课程的学生姓名 SELECT sname FROM student WHERE sid NOT in (SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE cname="物理" OR cname="体育") GROUP BY student_id HAVING COUNT(sid)=2) 10、查询挂科超过两门(包括两门)的学生姓名和班级 SELECT sname,caption FROM student LEFT JOIN class ON student.class_id=class.cid where sid in (SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING COUNT(sid)>1 ) 11 、查询选修了所有课程的学生的姓名 SELECT sname FROM student WHERE sid in (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(sid)!=(SELECT COUNT(cid) FROM course)) 12、查询李平老师教的课程的所有成绩记录 SELECT * FROM score WHERE course_id in (SELECT cid FROM course LEFT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname="李平老师")