* Mysql基础练习
* -- 创建数据库
* create database b default CHARACTER set utf8 collate utf8_general_ci;
* -- 使用数据库
* USE a;
* -- 创建表
* CREATE TABLE b (id INT(4),NAME VARCHAR(12),sex VARCHAR(2));
* -- 查询b表 *表示所有字段
* SELECT * FROM b;
* -- 插入数据
* INSERT INTO b VALUES (
* '1','ZhangSan','F');
* INSERT INTO b VALUES (
* '2','ww','M');
* --修改数据
* UPDATE b SET NAME = 'WangWu' WHERE id = '2';
* --删除数据
* DELETE FROM b WHERE id = '1';
* CREATE TABLE c (id INT(4),course INT(6));
* INSERT INTO c VALUES (
* '1','88');
* INSERT INTO c VALUES (
* '2','98');
* INSERT INTO c VALUES (
* '3','90');
* INSERT INTO c VALUES (
* '4','67');
* SELECT id,course FROM c;
* -- 简单的多表查询
* -- id =2 , name,course
* SELECT b.name,c.course
* FROM b,c
* WHERE b.id = c.id;
* -- join on
* SELECT b.`name`,c.course
* FROM b
* JOIN c
* ON b.id = c.id;
* -- 排序 desc asc
* SELECT *
* FROM c
* ORDER BY course ASC;
* 左连接 右连接 分组 统计 求和 平均值 包含 不包含 前几条 第几条 修改列名 增加列名 删除列名
* 存储过程
* 设置主键
* 题目1
* 新建表 a(id,name), b(id,English,Math)
* 表中插入数据a(id=1,2,3,4), b(id=1,2,3)，其中a、b表的id字段都设置为主键，其他字段值自己随便输入
* 题目：
* 查询出每个学生的姓名及总分
* 查询出英语成绩排第2的学生的姓名
* 题目2
* use test;
* DROP TABLE IF EXISTS c;
* CREATE TABLE c(Sid VARCHAR(10),Sname VARCHAR(10),Sage DATETIME,Ssex VARCHAR(10));
* INSERT INTO c VALUES('01' , '赵雷' , '1990-01-01' , '男');
* INSERT INTO c VALUES('02' , '钱电' , '1990-12-21' , '男');
* INSERT INTO c VALUES('03' , '孙风' , '1990-05-20' , '男');
* INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
* INSERT INTO c VALUES('05' , '孙风' , '1991-12-01' , '男');
* INSERT INTO c VALUES('06' , '吴兰' , '1992-03-01' , '女');
* INSERT INTO c VALUES('07' , '郑竹' , '1989-07-01' , '女');
* INSERT INTO c VALUES('08' , '王菊' , '1990-01-20' , '女');
* INSERT INTO c VALUES('01' , '赵雷' , '1990-01-01' , '男');
* INSERT INTO c VALUES('02' , '钱电' , '1990-12-21' , '男');
* INSERT INTO c VALUES('03' , '孙风' , '1990-05-20' , '男');
* INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
* INSERT INTO c VALUES('05' , '孙风' , '1991-12-01' , '男');
* INSERT INTO c VALUES('06' , '吴兰' , '1992-03-01' , '女');
* INSERT INTO c VALUES('07' , '郑竹' , '1989-07-01' , '女');
* INSERT INTO c VALUES('08' , '王菊' , '1990-01-20' , '女');
* 查询表test.c包含有哪些字段及字段的数据类型
* desc c;
* 修改sid字段类型为int(4),默认值为88
* ALTER table c MODIFY sid int(4) default 88;
* 给表c新增字段address，字段类型为varchar(20)，并且为非空，默认值设置为‘cdtest'
* alter table c add address varchar(20) not null default 'cdtest';
* 将列address改名为addr
* alter table c change column address addr varchar(20);
* 删除列address
* ALTER table c drop COLUMN address;
* 将表c中字段Sid设置为主键
* alter table c add PRIMARY key (sid);
* 查询不重复的记录
* select DISTINCT * from c;
* 删除前面5条记录
* DELETE from c limit 5;
* 清空表c所有数据
* truncate c;
* 删除后10行数据
* delete from c where Sid = 10 and id not in(select [t.id](http://t.id) from (select id from ShouyeData where funcid = 10 limit 10) as t)
* 修改表名
* ALTER TABLE table_name RENAME TO new_table_name
* 删除pid的primary key约束
* alter table products drop primary key;
* 统计
* select count(*) from c;
* 请用SQL语句查询部门平均成绩，要求按照部门的字符串降序排序，其部门名不含有 “Services”
* Employee 表结构如下：
* employee_id, employee_name, depart_id, depart_name, salary
* use test;
* create
* table
* Employee(
* employee_id int(6),
* employee_name varchar(20),
* depart_id int(6),
* depart_name varchar(20),
* salary int(10)
* );
* insert
* into
* Employee
* values('1','ZhangSan','01','Sales','10021'
* );
* insert
* into
* Employee
* values('2','LiSi','01','Sales','18021'
* );
* insert
* into
* Employee
* values('3','WangWu','02','Services','6800'
* );
* insert
* into
* Employee
* values('4','ZhaoLiu','02','Services','7990'
* );
* insert
* into
* Employee
* values('5','Zhao5','03','CaiWu','6600'
* );
* insert
* into
* Employee
* values('6','Zhao6','03','CaiWu','8600'
* );
* select * from test.Employee;
* * -- 请用SQL语句查询部门平均成绩，要求按照部门的字符串降序排序，其部门不含有 “Services”
* select depart_name,avg(salary) from Employee where depart_name not like '%Services%' group by depart_id order by depart_name desc ;
* 成绩排名（第2）：
* USE test;
* CREATE TABLE tt(id INT(4),NAME VARCHAR(20),score DOUBLE);
* INSERT INTO tt VALUE ('1','z','23');
* INSERT INTO tt VALUE ('2','z1','99.5');
* INSERT INTO tt VALUE ('3','z2','96.5');
* INSERT INTO tt VALUE ('4','z3','96.5');
* SELECT * FROM tt;
* SELECT * FROM tt ORDER BY score DESC LIMIT 1,1;
[图片上传失败...(image-9b4831-1543979146713)]
* SELECT * FROM tt ORDER BY score DESC LIMIT 1,2;
[图片上传失败...(image-3e2f8f-1543979146713)]
* SELECT t.score,(SELECT COUNT(s.score)+1 FROM tt s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC;
[图片上传失败...(image-58edc7-1543979146713)]
* SELECT * FROM
* (SELECT t.score,(SELECT COUNT(s.score)+1 FROM tt s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC)aa
* WHERE aa.rank = '2';
* [图片上传失败...(image-282ddc-1543979146713)]
* SELECT t.score,(SELECT COUNT(s.score)+1 FROM (SELECT s.score,COUNT(s.score) FROM tt s GROUP BY score ORDER BY score DESC) s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC;
[图片上传失败...(image-8a1706-1543979146713)]
* SELECT a.sid FROM
* (SELECT * FROM sc WHERE cid='01') a,
* (SELECT * FROM sc WHERE cid='02') b
* WHERE a.sid=b.sid AND a.score > b.score;
* 或：
* SELECT aa.sid FROM
* sc AS aa,
* sc AS bb
* WHERE aa.Sid=bb.sid
* AND aa.cid='01'
* AND bb.cid='02'
* AND aa.`score`>bb.score
网友评论