select * from students;
select id, name from students;
select name as 姓名, age as 年龄 from students;
select distinct gender from students;
select * from students where id > 5;
select * from students where name rlike "^张";
select * from students where id between 3 and 6;
select * from students where id in (5, 8 , 9);
select * from students where id >5 order by age asc;
select * from students where id >5 order by age desc;
select count(*) from students;
select max(age) from students;
select min(age) from students;
select sum(age) from students;
select avg(height) from students;
select id, group_concat(name, age) from students group by id;
select id, avg(height) from students group by id;
select * from students limit 3, 5;
select * from students inner join classes on students.classes_id = classes.id;
select * from students left join classes on students.classes_id = classes.id;
select * from students where age > (select avg(age) from students);;
create database school_of_three_kindoms charset=utf8;
use school_of_three_kindoms;
-- 创建学生基本信息表
create table students(
-- 学籍号:int unsigned无符号整型, auto_increment自增,primary key设置为主键,not null非空
id int unsigned auto_increment primary key not null,
-- 姓名: varchar(30)可变字符类型, default ""默认为空字符
name varchar(30) default "",
-- 年龄: tinyint unsigned无符号整型, default 0 默认为 0
age tinyint unsigned default 0,
-- 身高: 浮点型(5个数字,包含2个小数,如 180.05)
height decimal(5, 2),
-- 性别: enum枚举类型("1"对应"男","2"对应"女","3"对应"保密"")
gender enum("男","女","保密"),
-- 所属班级: int unsigned 无符号整型,默认值为0
classes_id int unsigned default 0
);
-- 创建班级
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20)
);
insert into students values
(null, "曹操", 50, 183.05, 1, 1),
(null, "夏侯惇", 40, 193.05, 1, 1),
(null, "许褚", 42, 186.05, 1, 1),
(null, "司马懿", 48, 188.05, 1, 1),
(null, "刘备", 48, 179.01, 1, 2),
(null, "张飞", 46, 179.60, 1, 2),
(null, "关羽", 47, 188.01, 1, 2),
(null, "孙权", 39, 185.09, 1, 3),
(null, "周瑜", 30, 190.09, 1, 3),
(null, "大乔", 28, 162.32, 2, 3),
(null, "小乔", 26, 160.19, 2, 3),
(null, "刑天", 100, 900.15, 1, 4),
(null, "鬼符三通", 59, 179.68, 1, 5),
(null, "曹焱兵", 20, 186.34, 1, 5),
(null, "曹玄亮", 13, 160.21, 1, 5),
(null, "夏玲", 21, 176.02, 2, 5);
insert into classes values
(0, "班级1_魏"),
(0, "班级2_蜀"),
(0, "班级3_吴");
Mysql常用查询语法