- 查看数据库
- 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;