数据库学习

一.连接数据库

1，win+R->services.msc->MySql-启动

2，cmd->mysql -uroot -p->输入密码

二.新建数据库

三.常用语句

1.操作表，创建表

CREATE TABLE Students
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT not NULL ,
s_name VARCHAR(20) DEFAULT '',
age INT DEFAULT 0,
height DECIMAL (5,2),
gender enum('男','女') DEFAULT '男',  -- 枚举是从一开始的
cls_id INT ,
is_delete BIT DEFAULT 0
);

2.删除表

drop TABLE students  //删除表结构

delete from students   //保留表结构，删除表数据

3.增删改数据

insert into Students values (0,'小明',21,182,1,1,0);

DELETE from students WHERE s_name="梦月";

UPDATE students SET cls_id=3 WHERE age=22;

四.查询数据

1.基础查询

SELECT * from students;

SELECT DISTINCT gender from students;  //消除重复行

SELECT s_name as 姓名,age as 年龄 from students;  //改名

2.条件查询

//比较运算

SELECT * from students WHERE age>18;   

//逻辑运算   and/or/NOT
SELECT * FROM students WHERE age>18 and gender=1; 

SELECT * FROM students WHERE not (age>18 and gender=1)

//模糊查询  like %一个或多个字符 -一个字符/rlike ^周.*伦$ 开头结尾

-- 查询名字中含有小的人
-- SELECT s_name from students WHERE s_name LIKE "%小%";
-- 查询以周开头，以伦结尾的名字
SELECT s_name from students WHERE s_name RLIKE "^周.*伦$";

3.范围查询

-- 不连续范围 in(,) not in(,)
-- SELECT s_name FROM students WHERE s_name in("周杰","丹丹");
-- 连续范围 BETWEEN...AND../ not bewteen...AND
-- 查询学生年纪在18-24之间的
-- SELECT s_name,age from students WHERE age BETWEEN 18 and 21;
-- SELECT * from students WHERE age not BETWEEN 18 and 21;
-- 空判断 is null/is not NULL
SELECT * FROM students WHERE height is NULL;

4.排序查询

-- 排序 ORDER BY 字段 asc（升序） DESC(降序)
-- 年纪从小到大排序，如果年纪相同就高度从高到低
-- SELECT * from students ORDER BY age,height DESC;
-- 18到50岁男性按年纪从小到大排序
 SELECT * FROM students WHERE gender=1 and (age BETWEEN 18 and 50) ORDER BY age;

5.分页查询

查询前2个学生信息

-- SELECT * FROM students LIMIT 2;
-- 公式：第N页，limit (N-1)*每页个数
SELECT * FROM students LIMIT 2,2;

6.聚合函数

- 总数COUNT() 最大MAX() 最小MIN() 
-- 平均值AVG() 求和sum() 四舍五入round()
-- SELECT MAX(age) from students;
-- SELECT count(*) FROM students;
-- SELECT AVG(height) FROM students;

-- 分组 GROUP BY ,一般与聚合函数一起使用 GROUP_CONCAT() 显示同组中的信息
SELECT gender,GROUP_CONCAT(s_name) FROM students GROUP BY gender;

-- HAVING/WHERE 
-- where是分组前对数据进行过滤，不能和聚合函数一起使用，having是分组后对数据进行过滤，和聚合函数一起使用

7.链接查询与自关联

-- 内链接：两个表匹配的数据
-- 右链接：两表匹配的数据和右表特有的数据，左表不存在的用null填充
-- 左链接：两表匹配的数据和左表特有的数据，右表中不存在的用null填充
-- SELECT * FROM students INNER JOIN classes on students.cls_id=classes.id;
-- 显示学生所有信息并且只显示班级名称
-- SELECT s.*,c.c_name FROM students as s INNER JOIN classes as c on s.cls_id=c.id ORDER BY s.id;
-- SELECT c.c_name as 班级,s.s_name as 姓名 FROM students as s INNER JOIN classes as c on s.cls_id=c.id ORDER BY c.c_name;
-- SELECT c.c_name as 班级,s.s_name as 姓名 FROM students as s RIGHT JOIN classes as c on s.cls_id=c.id;
-- SELECT c.c_name as 班级,s.s_name as 姓名 FROM students as s RIGHT JOIN classes as c on s.cls_id=c.id HAVING s.s_name is NULL;

-- 子关联表查询
-- 查询山东省唐山市下属的所有县
-- SELECT city.city,country.district FROM area as city INNER JOIN area as country on city.id=country.parent HAVING city.city="唐山市";

-- 子查询 select..(select..)
-- 查询最高的学生信息
SELECT * FROM students where height=(SELECT MAX(height) FROM students);
SELECT * FROM students WHERE height=(SELECT MAX(height) FROM students WHERE gender=1);