命令行模式:
1、启动MySQL的服务:
-- 创建数据库
CREATE DATABASE day13;
-- 删除数据库
CREATE DATABASE day15;
-- 查询所有数据库
SHOW DATABASES;
-- 删除数据库
DROP DATABASE day13;
-- 使用数据库
USE day13;
-- 创建表
CREATE TABLE student(
id INT(5),
NAME VARCHAR(20),
gender VARCHAR(20),
servlet INT(5);
jsp INT(5);
english INT(5);
address VARCHAR(20);
age INT(2)
);
-- 删除math字段
ALTER TABLE student DROP math;
-- 查询数据库中的表
SHOW TABLES;
-- 查询表字段属性
DESC student;
-- 查询表具体数据
SELECT * FROM student;
-- 删除数据
-- 删除所有数据(这个方法很少用)
DELETE FROM student ;
-- 删除部分数据
DELETE FROM student WHERE id=1;
-- 另一种删除
-- delete from
-- 可以全表删除 1)可以带条件删除 2)只能删除表的数据,不能删除表的约束 3)使用delete from删除的数据可以回滚(事务)
-- truncate table
-- 可以全表删除 1)不能待条件删除 2)既可以删除表的数据,也可以删除表的约束 3)使用truncate table删除的数据不能回滚
TRUNCATE TABLE test WHERE id=4;
-- 带条件的修改(推荐使用)
UPDATE student SET gender='女' WHERE id=3;
-- 修改带多个字段 set 字段名=值,字段名=值,。。。
UPDATE student SET chinese=100,english=102,math=122 WHERE id=8;
-- 查询数据(重点)(以下是基于一张表的查询)
-- 查询所有列
SELECT * FROM student;
-- 查询指定列
SELECT id,NAME FROM student;
-- 查询时指定别名(在字段的后面加上 as 编号)
-- (表也有别名,单表别名没有意义,多表查询才有意义,而且经常使用)
SELECT id AS '编号',NAME AS '姓名' FROM student ;
-- 查询时添加常量列
-- 需求:在查询student表时添加一个班级列 “数据库学习”
SELECT '我就试一试' AS 'test',id,NAME,'数据库学习' AS '学习内容' FROM student;
-- 查询时合并列(只能合并数值字段类型)
-- 查询每个学生的servlet和jsp的总成绩
SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;
-- 查询时去重复记录
SELECT DISTINCT gender FROM student;
-- 另一种方法
SELECT DISTINCT(gender) FROM student;
-- 查询学生所在地区
SELECT DISTINCT(address) FROM student ;
-- 或者
SELECT DISTINCT address FROM student;
-- 条件查询(where)
-- 逻辑条件 :and(与) or(或)
SELECT * FROM student WHERE id=2 AND NAME='nihao';
SELECT * FROM student WHERE id=1 OR NAME='nick';
-- 比较条件:> < >= <= == <>(不等于) between and
SELECT * FROM student WHERE id>4;
SELECT * FROM student WHERE id<4;
SELECT * FROM student WHERE id<>4;
SELECT * FROM student WHERE id BETWEEN 3 AND 7;
-- 判空条件:(null 空字符串):is null / is not null / ='' / <>''
SELECT * FROM student WHERE address IS NULL;
SELECT * FROM student WHERE address='';
SELECT * FROM student WHERE address<>'';
-- 模糊条件:like
-- 需求:查询name以n开头的数据
SELECT * FROM student WHERE NAME LIKE 'n%';
-- 需求:查询name以n开头并且仅有三位字母的数据
-- 注意:
SELECT * FROM student WHERE NAME LIKE 'n__';
-- 聚合查询
-- 分页查询(limit 起始行,查询几行)
-- 起始行 从0开始
-- 分页 当前页 每页显示多少条
--
-- 需求 查询第1、2条记录
SELECT * FROM student LIMIT 0 ,2;
-- 查询第3、4条记录
SELECT * FROM student LIMIT 2,2;
-- 没有就不显示
SELECT * FROM student LIMIT 7,2;
-- 查询排序(order by asc/desc)
-- asc:顺序 正序,数组:递增,字母自然顺序(a-z)
SELECT * FROM student ORDER BY id ASC;
-- 默认情况下 按照插入记录顺序排序
SELECT * FROM student ORDER BY id; -- 默认正序
-- descL: 倒序 反序 。数值:递减 字母:自然反序(z-a)
SELECT * FROM student ORDER BY id DESC;
-- 注意 多个排序条件
SELECT * FROM student ORDER BY servlet ASC, jsp DESC;
SELECT * FROM student ORDER BY servlet ;
SELECT * FROM student ORDER BY servlet DESC;
-- 分组查询
-- 需求 查询男女的人数
-- 预期结果 :
-- 男
-- 女
-- 1)把学生按照性别分组(group by gender)
SELECT gender FROM student GROUP BY gender;
-- 2)统计每组的人数(count(*))
SELECT gender,COUNT(*) FROM student GROUP BY gender;
SELECT address,COUNT(*) FROM student GROUP BY address;
UPDATE student SET jsp=99 WHERE id=2;
-- 分组查询后的筛选
-- 需求 :查询总人数大于2的性别
-- 查询每个性别的人数
SELECT gender,COUNT(*) FROM student GROUP BY gender;
-- 筛选人数大于2的记录(用having)
SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>2;
-- 注意分组之前的条件用where关键字,分组之前用having关键字
-- --------------其他------------------------
SELECT * FROM student;
UPDATE student SET gender='女' WHERE id=3;
UPDATE student SET gender='女' WHERE id=4;
UPDATE student SET servlet=70 WHERE id=4;
UPDATE student SET servlet=99 WHERE id=2;
UPDATE student SET servlet=60 WHERE id=3;
UPDATE student SET servlet=90,jsp=67 WHERE id=1;
UPDATE student SET servlet=80,jsp=97 WHERE id=2;
ALTER TABLE student ADD COLUMN address VARCHAR(10);
UPDATE student SET address='武汉青山' WHERE id=1;
UPDATE student SET address='武汉汉阳' WHERE id=2;
UPDATE student SET address='武汉洪山' WHERE id=3;
UPDATE student SET address='武汉新洲' WHERE id=4;
CREATE TABLE test(
id INT(2) PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20),
age INT(2)
);
ALTER TABLE test ADD id INT(2) PRIMARY KEY AUTO_INCREMENT;
DESC test;
SELECT * FROM test;
INSERT INTO test(id,NAME,age) VALUES(4,'xiaoming',3);
SELECT * FROM student;
ALTER TABLE student ADD COLUMN servlet INT(5);
ALTER TABLE student ADD COLUMN jsp INT(5);
UPDATE student SET servlet=39,jsp=88 WHERE id=11;
INSERT INTO student VALUES(11,'nick','man');
INSERT INTO student VALUES (12, 'nihao', 'ni') ;