-- 一、管理数据库
-- 1.1 创建数据库CREATE DATABASE day15;SHOW DATABASES;
CREATE TABLE student(
id INT, NAME VARCHAR(20), age INT);-- 查看表
SHOW TABLES;-- 二、管理数据
-- 1.1插入数据（insert into）-- 需求： 往学生表插入数据INSERT INTO student VALUES(1,'张三',20);-- 1）插入的字段值顺序一定是按照表的字段顺序INSERT INTO student VALUES('张三',1,20); -- ERROR 1366 (HY000): Incorrect integer value: 'eric' for column 'id' at row 1-- 2）字段的数据一定要和值的数量要一致INSERT INTO student VALUES(2,20); -- ERROR 1136 (21S01): Column count doesn't match value count at row 1-- 3）插入部分字段INSERT INTO student(id,NAME) VALUES(2,'李四'); -- 1.2 修改数据(update)-- 需求： 修改学生表-- 1)批量操作（不建议经常使用这种）UPDATE student SET NAME='张三';-- 2)按条件修改,修改一个字段-- 需求： 修改id为2的学生姓名UPDATE student SET NAME='李四' WHERE id=2;-- 需求： 修改id为1的学生姓名和年龄-- 3)修改多个字段UPDATE student SET NAME='王五',age=40 WHERE id=1;-- 1.3 删除数据（delete from）
-- 1) 全表数据删除DELETE FROM student;-- 2）按条件删除数据DELETE FROM student WHERE id=2;-- 3)truncate table也可以删除全表数据TRUNCATE TABLE student;-- 注意：delete from 和 truncate table 的区别？-- 1）delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件删除-- 2）delete from删除的数据可以回滚，truncate table删除的数据不能回滚。-- 3）delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束(auto_increment)重置-- truncate table student where id=1; 错误的语法
-- 1.4 查看所有数据（重点中的重点）SELECT * FROM student;CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20))INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME) VALUES('张三');SELECT * FROM test;
DELETE FROM test;
TRUNCATE TABLE test;-- 三、查询数据(select)
-- 3.1 查询所有字段SELECT * FROM student;-- 3.2 查询指定的字段
SELECT id,NAME FROM student;-- 3.3 查询时指定别名
SELECT id AS '编号',NAME AS '姓名' FROM student;-- AS可以省略SELECT id '编号',NAME '姓名' FROM student;-- 3.4 查询时添加常量列
-- 临时使用的列可以通过查询时动态添加进去-- 需求： 查询学生数据时添加一个"班级"列。值为“java就业班”SELECT id AS '编号',NAME AS '姓名','java就业班' AS '班级' FROM student;-- 3.5 查询时合并列
-- 需求： 查询每个学生的总分。SELECT NAME AS '姓名',(servlet+mysql) AS '总成绩' FROM student;-- 注意： 合并列的字段必须是数值类型的字段。非数值类型合并没有效果SELECT NAME AS '姓名',(servlet+NAME) FROM student; -- 3.6 查询去除重复记录(distinct)-- 需求： 查询有哪些的地区学生SELECT DISTINCT address FROM student;-- 另一种语法SELECT DISTINCT(address) FROM student;-- 3.7 条件查询(where)
-- 1)逻辑条件： and(与) or（或）-- 当出现两个或两个以上的条件，那么这两个条件直接一定会存在逻辑关系。要么是与关系，要么或关系-- 需求：查询学生的id为1，且姓名为张三的学生
SELECT * FROM student WHERE id=1 AND NAME='张三'; -- (交集，结果数小于或等于任何一个条件的结果数)-- 需求：查询学生的id为2，或姓名为张三的学生
SELECT * FROM student WHERE id=2 OR NAME='张三'; -- (并集，结果数一定会大于或等于任何一个条件的结果数) -- 2)比较条件： > < >= <= = <> (between and)-- 需求： 查询servlet分数大于80分的学生SELECT * FROM student WHERE servlet>80;-- 需求：查询mysql分数小于或等于85分的学生SELECT * FROM student WHERE mysql<=85;SELECT * FROM student WHERE mysql<85 OR mysql=85;-- 需求： 查询servlet分数大于或等于80分，且小于或等于85分的学生SELECT * FROM student WHERE servlet>=80 AND servlet<=85;-- 上面sql代替的语法SELECT * FROM student WHERE servlet BETWEEN 80 AND 85; -- 在..。之间（包前包后）-- 需求：查询年龄不等于30岁的学生SELECT * FROM student WHERE age<>30;-- 3)判空条件： is null , is not null, ='' ， <>''
-- null： 表示没有数据 is null is not null-- 空字符：有数据 ='' -- 需求：查询没有性别数据的学生（数据‘男’或‘女’）SELECT * FROM student WHERE gender IS NULL OR gender='';-- 需求： 查询有性别数据的学生
SELECT * FROM student WHERE gender IS NOT NULL AND gender<>''; -- 4)模糊条件： like -- 模糊替代符号：-- %： 替代任意个字符-- _: 替代一个字符 -- 需求： 查询姓‘李’的学生SELECT * FROM student WHERE NAME LIKE '李%';-- 需求： 查询姓名中包含‘四’字的学生SELECT * FROM student WHERE NAME LIKE '%四%';-- 需求：查询姓‘李’，全名只有两个字的学生SELECT * FROM student WHERE NAME LIKE '李_'; -- 3.8 聚合函数查询:用于统计结果-- max() min() avg() count()-- 需求： 查询servlet的最高分-- max() 取最大值SELECT MAX(servlet) FROM student;-- 需求： 查询mysql的最低分-- min(): 取最小值SELECT MIN(mysql) FROM student;-- 需求： 查询servlt的平均分-- avg(): 平均函数SELECT AVG(servlet) FROM student;-- 需求：查询当前有几个学生-- count(): 统计表的记录数量SELECT COUNT(*) FROM student;-- count(id): 统计有值的id字段的数量(排除null的数据)SELECT COUNT(id) FROM student;-- 使用count统计表的数据，不要使用存在null的字段SELECT COUNT(gender) FROM student; -- 3.9 分页查询(limit)-- limit 起始行数,查询的行数-- 起始行数从0开始SELECT * FROM student;-- 需求：学生共20条数据，每页显示5条，共4页-- 看第3页(第11到15条)的学生数据sql：select * from student limit 10,5;-- 看第4页(第16到20条)的学生数据sql: select * from student limit 15,5; -- 知道： 当前页码，每页显示条数-- 结论分页查询当前页数据的sql： select * from student limit (当前页码-1)*每页显示条数,每页显示条数;-- 需求： 查询第1,2条数据
SELECT * FROM student LIMIT 0,2;-- 需求： 查询第3,4条数据SELECT * FROM student LIMIT 2,2;-- 需求：查询第5,6条数据SELECT * FROM student LIMIT 4,2;-- 3.10 查询后排序（order by）
-- desc: 降序。数值从大到小，字母z-a-- asc: 升序。数值从小到大，字母a-z-- 默认情况下，按照插入的顺序排序SELECT * FROM student;-- 需求：按照id的升序排序SELECT * FROM student ORDER BY id ASC;-- 需求： 按照servlet成绩降序排序SELECT * FROM student ORDER BY servlet DESC;-- 多个排序条件的情况：先按照前面的条件排序，当出现重复记录，再按照后面的条件排序
-- 需求： 按照age升序，按照servlet成绩升序排序SELECT * FROM student ORDER BY age ASC,servlet ASC;-- select * from student order by username asc;
-- 3.11 分组查询(group by)
-- 需求： 查询每个地区有多少人-- 预期结果：-- 广州天河 3-- 广州越秀 1SELECT address,COUNT(*) FROM student;-- 1)对地区进行分组 2)在分组的基础可以进行统计，统计的是每组的数据
SELECT address,COUNT(*) FROM student GROUP BY address;-- 需求： 统计男女的人数-- 注意： where条件必须放在group by分组之前SELECT gender,COUNT(*) FROM student WHERE gender IS NOT NULL AND gender<>'' GROUP BY gender;-- 3.12 分组后筛选(having)
-- 需求： 查询哪些地区的人数大于2个的地区-- 1）查询哪些地区多少人 2）筛选人数大于2的地区-- 注意： having使用在group by分组之后的，对分组后的条件进行筛选SELECT address,COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>2 ;-- 四、字段类型
-- char(20) vs varchar(20)-- char(20): 固定长度的字符串。不管实际存储的数据的大小，一定占用20个字符空间-- varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。-- int vs int(4)
-- int: 默认最多11位，长度根据实际存储的数值的长度-- int(4): 固定的数组长度CREATE TABLE test( id1 INT, id2 INT(4) ZEROFILL -- zerofill: 零填充)INSERT INTO test VALUES(1,1);SELECT * FROM test;-- date vs datetime vs timestamp
-- date: 日期-- datetime： 日期+时间-- timestamp: 时间戳,用于记录当前数据的插入或更新的时间CREATE TABLE test_date( date1 DATE, date2 DATETIME, date3 TIMESTAMP)INSERT INTO test_date(date1,date2) VALUES('2015-06-17 17:35:45','2015-06-17 17:35:45');
SELECT * FROM test_date;
UPDATE test_date SET date2='2015-06-17 17:40:45';
SELECT * FROM student;
ALTER TABLE student CHANGE NAME username VARCHAR(20);
CREATE TABLE student2( id INT, NAME VARCHAR(20), chinese FLOAT, english FLOAT, math FLOAT);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);INSERT INTO student2(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);INSERT INTO student2(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);INSERT INTO student2(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);INSERT INTO student2(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);INSERT INTO student2(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);SELECT * FROM student2;
-- 练习：
-- 查询表中所有学生的信息。SELECT * FROM student2;-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME,english FROM student2;-- 过滤表中英语成绩的重复数据
SELECT DISTINCT english FROM student2;-- 使用别名表示学生分数。
SELECT NAME AS '姓名',chinese AS '语文',english AS '英语',math AS '数学' FROM student2;-- 查询姓名为李一的学生成绩
SELECT * FROM student2 WHERE NAME='李一';-- 查询英语成绩大于等于90分的同学
SELECT * FROM student2 WHERE english>=90;-- 查询总分大于200分的所有同学
SELECT * FROM student2 WHERE (chinese+english+math)>200;-- 查询所有姓李的学生英语成绩。
SELECT NAME,english FROM student2 WHERE NAME LIKE '李%';-- 查询英语>80或者总分>200的同学
SELECT * FROM student2 WHERE english>80 OR (chinese+english+math)>200;-- 统计每个学生的总分。
SELECT NAME AS '姓名',(chinese+english+math) AS '总分' FROM student2;-- 在所有学生总分数上加10分特长分。
SELECT NAME AS '姓名',(chinese+english+math+10) AS '总分' FROM student2;
SELECT * FROM student;-- 添加两个列 servlet mysqlALTER TABLE student ADD COLUMN servlet INT;ALTER TABLE student ADD COLUMN mysql INT;
UPDATE student SET servlet=75,mysql=80 WHERE id=1;
UPDATE student SET servlet=86,mysql=90 WHERE id=2;-- 添加地址字段
ALTER TABLE student ADD COLUMN address VARCHAR(20);UPDATE student SET address='广州天河' WHERE id=1;
UPDATE student SET address='广州越秀' WHERE id=2;INSERT INTO student VALUES(3,'王五',30,85,65,'广州天河');
ALTER TABLE student ADD COLUMN gender VARCHAR(2);