-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE python charset=utf8;
-- 使用数据库
USE python;
-- 查看当前使用的数据库
SELECT DATABASE();
-- 删除数据库-慎重
DROP DATABASE python;

表结构操作sql语句

-- 将前面删除的数据库再创建出来
CREATE DATABASE python CHARSET=utf8;
-- 选择要操作的数据库
SHOW DATABASES;
USE python;
-- 查看当前数据库中所有的表
show tables;
-- 创建一张表，表名为students，字段有 id、name、age、height、gender
CREATE TABLE students(
id INT UNSIGNED PRIMARY KEY auto_increment NOT NULL,
name VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 0,
height DECIMAL(5,2),
gender enum('男', '女')
)
-- 创建完表之后，可以查看一下当前数据库中有哪些表
SHOW tables;
-- 查看表结构
DESC students;
-- 给students表添加一个 birthday 字段
ALTER TABLE students ADD birthday datetime;
-- 查看表结构
DESC students;
-- 将 students表中的birthday字段类型从最开始的datetime改为date
-- 不能填写不存在的字段名
ALTER TABLE students MODIFY birthday date;
-- 查看表结构
DESC students;
-- 将students 表中的 birthday字段名改为birth已经类型改成datetime
ALTER TABLE students CHANGE birthday birth datetime;
-- 查看表结构
DESC students;
-- 将students表中的birth字段进行删除
ALTER TABLE students DROP birth;
-- 查看表结构
DESC students;
-- 查看创建students表的语句
SHOW CREATE TABLE students;

-- CREATE TABLE `students` (
--   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
--   `name` varchar(20) NOT NULL,
--   `age` tinyint(3) unsigned DEFAULT '0',
--   `height` decimal(5,2) DEFAULT NULL,
--   `gender` enum('男','女') DEFAULT NULL,
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 查看创建数据库python的语句
SHOW CREATE DATABASE python;
-- CREATE DATABASE `python` /*!40100 DEFAULT CHARACTER SET utf8 */
-- 删除数据表students
DROP TABLE students;
-- 查看数据库中的表
SHOW TABLES;

where条件查询

-- 创建数据库
create database python_test_1 charset=utf8;
-- 使用数据库
USE python_test_1;
-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0),
(0,'凌小小',28,180.00,2,1,0),
(0,'司马二狗',28,120.00,1,1,0);

SELECT * FROM students;

-- 查询id为1的学生信息
SELECT * FROM students WHERE id = 1;

-- 比较运算查询
-- 查询编号大于3的学生
SELECT * FROM students WHERE id > 3;
-- 查询编号不大于4的学生
SELECT * FROM students WHERE id <= 4;
-- 编号大于5的所有学员的姓名
SELECT name FROM students WHERE id > 5;
-- 编号不等于6的学员的姓名
SELECT name FROM students WHERE id != 6;
-- 如果判断的是非计算字段,无法查询到结果,但是不会报错
SELECT name FROM students WHERE name > 0;
-- 查询姓名不是“黄蓉”的学生
SELECT * FROM students WHERE name != "黄蓉";

-- 查询没被删除的学生
SELECT * FROM students WHERE is_delete < 1;

-- 逻辑运算查询
-- 查询编号大于3的女同学
SELECT * FROM students WHERE id > 3 AND gender = '女';

-- 查询编号小于4或没被删除的学生
SELECT * FROM students WHERE id < 4 OR is_delete != 1;

-- 查询年龄不在10岁到15岁之间的学生
SELECT * FROM students WHERE NOT (age < 15 AND age > 10);

-- 查询身高,不在160-170之间的
SELECT * FROM students WHERE NOT (height < 170 and height > 160);

-- 模糊查询
-- 查询姓黄的学生
SELECT DATABASE();
USE python_test_1;

SELECT * FROM students;

SELECT * FROM students WHERE name like '黄%';
-- 找到名字里带小的学员信息
SELECT * FROM students WHERE name LIKE '%小%';

-- 查询姓黄并且“名”是一个字的学生
SELECT * FROM students WHERE name LIKE '黄_';
-- 查找姓小,并且名字是一个字的学员
SELECT * FROM students WHERE name LIKE '小_';

-- 查询姓黄或叫靖的学生
SELECT * FROM students WHERE name LIKE '黄%' OR name LIKE '%靖';

-- 范围查询
-- 查询编号为3至8的学生(BETWEEN是包含左右边界数值的)
-- 如果查询范围较大,超出数据范围不会报错
SELECT * FROM students WHERE id BETWEEN 3 AND 8;
SELECT * FROM students WHERE id BETWEEN 3 and 50;

-- 查询(SELECT)编号(id)不是(NOT)3至8(BETWEEN)的男生(gender)
SELECT * FROM students WHERE id NOT BETWEEN 3 and 8 AND gender = '男';
-- 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
-- for the right syntax to use near '(BETWEEN 3 and 8) AND gender = '男'' at line 1, Time: 0.000000s

-- 查询不连续数据  IN
-- 查询身高为1.8米或者1.7米的学生信息
SELECT * FROM students WHERE height in (170.00, 180);
-- 查询名称为郭靖或者黄蓉或者小月月的学员
SELECT * FROM students WHERE name in ('郭靖','黄蓉','小月月');

-- int(5) 五位数字,如果不够5位,用空格补齐,超出5位  一样可以存储. 
-- varchar(20)  存储20个字符长度的字符,无论是字符串还是数字
-- bigint(10)  存储的数据,一定也要在它认可的编码格式范围内,保存英文字母,保存不了

-- 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
-- for the right syntax to use near '180.00,170.00' at line 1, Time: 0.000000s

-- 
-- 空判断查询
-- 查询没有填写身高的学生
SELECT * FROM students WHERE height IS NULL;

SELECT * FROM students WHERE height is NOT NULL;

分页分组语句

-- 查询未删除男生信息，按学号降序
SELECT * FROM students WHERE is_delete < 1 AND gender = '男' ORDER BY id desc;
-- 显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序:
-- 先按照第一个排序规则进行排序,如果值相同则按照第二个排序规则排序
-- 排序规则默认是升序
SELECT * FROM students ORDER BY age desc, height;

-- 查询前3行男生信息:
-- 起始位置从0开始计算,如果我们想要从最开始进行查询,则使用0,end_index;
SELECT * FROM students LIMIT 2,2;
-- 如果从最开始进行截取,则可以省略开始位置信息
SELECT * FROM students LIMIT 3;
-- 如果查询范围内,无数据,不会查询到结果,也不会报错
SELECT * FROM students LIMIT 25,3;

SELECT * FROM students;

SELECT * FROM students LIMIT 0,3;

SELECT * FROM students LIMIT 3,3;

SELECT * FROM students LIMIT 6,3;

-- 返回非NUll数据行数
SELECT count(*) FROM students WHERE height is not null;

SELECT COUNT(height) FROM students;

-- 返回所有数据总行数

SELECT COUNT(*) FROM students;

-- 查询女生标号的最大值
SELECT MAX(id) FROM students WHERE gender='女';

-- 查询未删除学员的最小值
SELECT MIN(id)FROM students WHERE is_delete<1;

-- 查询男生的总身高
SELECT SUM(height) FROM students WHERE gender='男';

-- 查询女生的平均身高
SELECT SUM(height)/COUNT(*) FROM students WHERE gender='女';

SELECT SUM(height)/COUNT(*) FROM students WHERE gender='男';

-- 使用avg进行平均身高的求取
SELECT AVG(height) FROM students WHERE gender = '女';

-- 求男生的平均身高, 包含身高是null的
SELECT AVG(height) FROM students WHERE gender = '男';
SELECT AVG(IFNULL(height,0)) FROM students WHERE gender = '男';

-- 根据gender字段来分组
SELECT gender FROM students GROUP BY gender;
-- 根据哪个数据字段进行分组,显示字段时,行数要和分组字段相同
-- SELECT gender,height FROM students GROUP BY height;

-- 根据name和gender字段进行分组
SELECT name,gender FROM students GROUP BY name, gender;

-- 查询各种性别的平均身高
SELECT gender,AVG(height) FROM students GROUP BY gender;

-- 查询各种性别分别有多少位学员
SELECT gender, COUNT(*) FROM students GROUP BY gender;


SELECT gender,id FROM students GROUP BY gender,;

-- 查询每个性别中各有哪些学员,将姓名拼接后组合到表中
-- 多条数据对应一个分组时,将数据用逗号隔开,统一填写到一个单元格内
-- GROUP_CONCAT就是拼接数据使用的
SELECT gender, GROUP_CONCAT(name) FROM students GROUP BY gender;

-- 根据gender字段进行分组，统计分组条数大于2的
-- having是进行分组过滤的字段,使用方式与where相近
SELECT gender,COUNT(*) FROM students GROUP BY gender HAVING COUNT(*) > 2;

-- 根据gender字段进行分组，汇总总人数
SELECT gender, COUNT(*) FROM students GROUP BY gender WITH ROLLUP;

-- 根据gender字段进行分组，汇总所有人的年龄
SELECT gender,GROUP_CONCAT(age) FROM students GROUP BY gender WITH ROLLUP;

-- 根据gender字段进行分组，汇总总人数,同时,给null填充上"总计"
SELECT IFNULL(gender,'总计'), COUNT(*) FROM students GROUP BY gender WITH ROLLUP;

连接查询语句

-- 查看classes表和students表
SELECT * FROM classes;
SELECT * FROM students;

-- 使用内连接,链接students和classes,使students,cls_id = classes.id;
SELECT * FROM students INNER JOIN classes ON students.cls_id = classes.id;
-- 给表起别名,给重复字段起别名,定义别名后,原名称无法使用
SELECT * FROM students as s INNER JOIN classes as c ON s.cls_id = c.id;
-- 在select中对字段进行as重命名可以修改查询出来的字段名称
SELECT students.id,students.name,students.age,classes.name AS class_name FROM students INNER JOIN classes  ON students.cls_id = classes.id;
-- 只要对表的别名进行了设定,在整个查询语句中就必须使用.
SELECT s.id,s.name,s.age,c.name AS class_name FROM students as s INNER JOIN classes as c  ON s.cls_id = c.id;
SELECT * FROM hero;
SELECT * FROM gongfu;

-- 给每个英雄匹配上对应的功夫;  inner join
SELECT * FROM hero INNER JOIN gongfu ON hero.id = gongfu.id;

-- 给每个应用匹配上对应的功夫, left join
SELECT * FROM hero LEFT JOIN gongfu ON hero.id = gongfu.id;

-- 给每个英雄匹配上对应的绝学, right join
SELECT * FROM hero RIGHT JOIN gongfu ON hero.id = gongfu.id;

-- 使用左连接查询学生表与班级表
SELECT * FROM students as s left JOIN classes as c ON s.cls_id = c.id;

-- 使用右链接查询学生表与班级表
SELECT * FROM students as s right JOIN classes as c ON s.cls_id = c.id;

-- 自连接
-- 表数据插入
-- 查询所有的省份信息
SELECT title FROM areas WHERE pid is null;
-- 查询省的名称为“山西省”的所有城市
SELECT a.title FROM areas as a INNER JOIN areas as b ON a.pid = b.id WHERE b.title = '山西省';
-- 查询所有的省份信息,查询每个省份的城市数量
SELECT a.title, COUNT(*) FROM areas as a INNER JOIN areas as b ON a.id = b.pid WHERE a.pid is NULL GROUP BY a.title;

SELECT
	b.title,
	COUNT(*) 
FROM
	areas AS a
	INNER JOIN ( SELECT * FROM areas WHERE pid IS NULL ) AS b ON b.id = a.pid 
GROUP BY
	b.title;
	
	
SELECT * FROM areas as a INNER JOIN (SELECT * FROM areas WHERE pid is NULL) as b ON b.id = a.pid;

-- 子查询
-- 查询大于平均年龄的学生:
SELECT AVG(age) FROM students;
-- 一般在使用子查询时,除非异常熟悉,否则先讲子查询书写完毕,无错误后再将其添加到主查询中.
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
-- 查找年龄最大,身高最高的学生:
SELECT MAX(age), MAX(height) FROM students;

SELECT * FROM students WHERE (age,height) = (SELECT MAX(age), MAX(height) FROM students);

-- 查询身高最高的或者年龄最大的学员
SELECT * FROM students WHERE age = (SELECT MAX(age) FROM students) or height = (SELECT MAX(height) FROM students);

-- 将查询出来的数据写入表中
CREATE TABLE apple as (SELECT * FROM students WHERE age = (SELECT MAX(age) FROM students) or height = (SELECT MAX(height) FROM students));