- 查看数据库
Show databases;
- 创建数据库
Create database mysql;
- 删除数据库school2
Drop database mysql;
- 切换数据库school
Use mysql;
- 显示创建数据库的细节
Show create database mysql;
- 创建一个gbk的数据库
Create database mysql character set gbk;
- 显示所有表
Show tables’
- 创建表stu(id,姓名,性别,年龄,英语,语文,数学)
Create table stu(id int primary key auto_increment,name varchar(20),sex varchar(20),age int,english varchar(20),chinese varchar(20),math varchar(20));
- 删除表
Drop table stu;
- 查看表结构
Desc stu;
- 查看创建表结构细节
Show create table stu;
- 添加列(字段)
Alter table stu add birthday varchar(20);
- 删除列(字段)
Alter table stu drop birthday;
14.表格约束条件
主键:primary key;
自增:auto_increment
非空:not null;
唯一:unique;
- 插入数据
Insert into stu values(1,’zhangsan’,’nan’,18,’20’,’115’,’65’);
- 删除表
Drop table stu;
- 修改表数据
Update stu set age = age+10;
Update stu set name = “张三” where name = “zhangsan”;
- 删除所有表
Drop tables;
- 全查
Select * from stu;
- 解决中文乱码
Set character_set_client = gbk;
Set character_set_results = gbk;
Charset gbk;
- 查询id=5的数据
Select * from stu where sid = 5;
- 查询英语成绩大于80的学生
Select * from stu where english>80;
- 查询所有学生各科成绩
姓名 | 语文 | 数学 | 英语 |
- 语文加10分
Select * (chinese+10) from stu;
- 统计每个学生的总分
Select name as 姓名,(English+Chinese+Math) as 总分 from stu;
- 查询总分大于230分的同学
Select * from stu where ((English+Chinese+Math)>230;
- 查询数学成绩在80-90之间的同学
Select * from stu where math between 80 and 90;
- 查询数学语文英语都大于80的同学成绩
Select * from stu where matn>80 and english>80 and chinese>80;
- 查询数学成绩在 80 60 90内的同学,即数学成绩有60、80、90的。
Select * from stu where math in(80,60,90);
- 模糊查询-查询所有姓名中包含张的同学
Select * from stu where name like ‘%张%’;
- 排序(升降)
Select * from stu order by age;
Select * from stu order by age desc;
- 分组,根据年龄
Select * from stu group by age;
- 分页查询:从m+1开始,n条数
Select * from limit 0,2;
- 分组查询加条件
Select * from stu group by sex having age>18;
- 聚合函数
count 个数(统计多少个学生)
Select count(*) from stu;
(统计总成绩大于250分的人数)
Select count(*) from stu where (math+english+chinese)>250;
sum 总数(统计班级各科总成绩)
Select sum(math),sum(english),sum(chinese) from stu;
(统计班里所有科目的总成绩)
Select sun (math+english+chinese) from stu;
avg 平均数(统计语文成绩的平均)
Select avg(chinese)/count(*) from stu;
Select avg(chinese) from stu;
max 最大值(统计班级语文的最高分和最低分)
Select max(chinese) from stu;
min 最小值
Select min(chinese) from stu;
- 查询李老师所有学生
Select * from student where sid in(select ts_id from ts where ts_tid = (select tid from teacher where name = ‘李老师’));
- 查询张三的所有老师
Select * from teacher where tid in(select ts_tid from ts where ts_sid = (select sid from student where name = “张三”));
- 内连接查询
Select * from stu inner join class on stu.cid = class.id;
- 左外连接查询
Select * from stu left join class on stu.cid = class.id;
- 右外连接查询
Select * from stu right join class on stu.id = class.id;
- 交叉查询
Select * from stu,class;
Select * from stu cross join class;