对数据仓库的操作(CURD)：
新增:
create database db_test;
新增的时候设置编码:
create database da_test_1 character set utf-8;
删除：
drop database da_test_1;
修改：
修改数据仓库的字符编码集:
alter database db_test character set 'gbk';
查看：
获取所有的数据仓库：
show databases;
查看数据仓库的编码：
show create database db_test;
选择数据仓库：
use db_test;
查看当前选择的是哪个数据仓库：
select database();
对数据表的操作(CURD):
新增：
create table emp(
id int primary key auto_increment,
name varchar(50) not null,
workid varchar(32) unique not null,
salary double
);
查看：
查看表结构：
desc emp;
查看该数据仓库中的所有表：
show tables;
查看表的字符编码:
show create table person;
修改:
增加一列字段：
alter table emp add telephone varchar(20);
修改现有列的 数据类型和约束：
alter table emp modify telephone varchar(30) not null;
修改现有列的名称：
alter table emp change telephone phone varchar(11);
删除存在的列:
alter table emp drop phone;
修改表的名字：
rename table emp to person;
修改表的字符编码:
alter table person character set utf8;
删除：
drop table person;
对表中记录的操作(CUD):
新增：
insert into (id,name,workid) values (null,'xiaoshitou','P001');
插入数据时的注意事项：
1、值与列一一对应。并且有多少个列，需要写多个值。如果某一个列没有值。可以使用null。表示插入空。
2、值的数据类型，与列被定义的数据类型要相匹配。并且值得长度。不能够超多定义的列的长度。
3、字符串类型：插入字符类型的数据，必须英文的单引号括起来。在mysql中。
4、date 时间类型：也必须用英文的单引号括起来，如 ‘yyyyMMdd’ ,’yyyy-MM-dd’,’yyyy/MM/dd’ 。
5、在插入数据的时候，如果某些列可以为null，或者是自动增长的列，或者有默认值的，在插入的时候可以省略。
6、如果给表中的所有列插入数据，这时可以省略表名后面的列名，直接写values
修改：
update emp set salary = 15000 where id = 1;
删除：
delete from emp where id=1;
删除表中所有的记录：
truncate table emp;
delete from emp; 和truncate table emp;的区别？
前者是一条一条记录删除，
后者是先删除表，然后重新建一张表
对表中记录的查询操作(R):
准备工作（创建表和添加数据）：
create database day02;use day02;create table student( id int primary key auto_increment, name varchar(32) not null, age int , gender varchar(10) not null, score double not null, birthday date );insert into student (id,name,age,gender,score,birthday) values(null,'zhangsan',23,'male',98.99,'1990-09-09');insert into student (id,name,age,gender,score,birthday) values(null,'lisi',23,'男',56.99,'1990-02-09');insert into student (id,name,age,gender,score,birthday) values(null,'王五',24,'女',75.99,'1988-01-01');insert into student (id,name,age,gender,score,birthday) values(null,'赵六',25,'男',80.99,'1980-11-12');insert into student (id,name,age,gender,score,birthday) values(null,'王思聪',null,'女',84,null);
数据表中记录查询：
查询表中所有的记录：
select * from student;
查询表中指定列的数据:
select name,age,score from student;
按条件来查询：
查询分数大于80的所有学生信息: select * from student where score >80;
过滤重复数据：
获取学生表中所有的年龄，不能重复
select distinct age from student;
对查询的结果进行排序：
用学生的age进行降序排序
select * from student order by age desc;
先按照age进行降序排列，年龄相同时，利用分数升序排列：
select * from student order by age desc,score asc;
给列起别名：
可以用as，也可以不用
select id as 'GongHao',name 'XingMing' from student;
聚合函数：
count函数:
统计个数，统计一共有多少学生:
count的注意点：count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。
sum函数：
求和方法：统计该班级所有学生的分数：
select sum(score) from student;
sum的注意点：
1、如果使用sum 多列进行求和的时候，如果某一列中的有null，这一列所在的行中的其他数据不会被加到总和。
2、可以使用mysql 数据库提供的函数 ifnull(列名,值)
3、在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)
avg 函数：
求平均值，求班级的平均年龄
select avg(age) from student;
max, min函数：
求最大值和最小值：求班级的最高分，最低分
select max(score) as '最高分',min(score) '最低分' from student;
group by 分组函数：
准备工作：
create table student(
name varchar(30),
sex char(10),
course varchar(30),
score int);insert into student values('张三', '男', '语文', 93);insert into student values('张三', '男','数学', 96);insert into student values('张三', '男', '英语', 99);insert into student values('李四', '女', '语文', 90);insert into student values('李四', '女','数学', 85);insert into student values('王五', '男', '语文', 80);insert into student values('王五', '男','数学', 75);insert into student values('王五', '男', '英语', 70);
表中的数据为：
求：每个学生的总成绩？
分析：要求每个人的总成绩，首先需要按学生姓名来分组，然后对分组的成绩求和，就可以完成。
求平均分大于80的学生？
select name,avg(score) from student group by name having avg(score) > 80;
select语句执行的顺序：
1、from ：确定数据源
2、Where ： 指定条件对记录进行筛选
3、Group by : 对记录进行分组
4、聚合函数：如sum，count，avg等...
5、Having ：对分组的数据，进行筛选
6、计算所有的表达式
7、使用order by 对结果进行排序
8、Select对数据的显示
数据库的备份和恢复：
备份：
mysqldump -u root -p mytestdb > e:t/est.sql
恢复：
注意：恢复的时候，首先应该自己创建一个数据库
create database dbtest;
执行:mysql -u root -p db_test < e:/test.sql
数据库的多表设计：
多对多的关系:
一个A可以对应多个B，一个B可以对应多个A；比如说两张表：学生和课程表，一个学生可以选择多门课程；一门课程也可以被不同的学生选择。
设计方式：
创建一个中间表C，增加两列，引入A和B表中主键，作为外键。
外键的增加方式：
1、已经创建好的表中，添加外键:
alter table C add foreign key(A_id) references A(id);
alter table C add foreign key(B_id) references B(id);
2、在定义表的时候增加外键
create table C(
A_id int,
B_id int,
foreign key(A_id) references A(id),
foreign key(B_id) references B(id)
);
一对多的关系：
一个A对应多个B,而多个B只能对应一个A。
设计方式：
在多的一张表中增加一列，引入一表中主键作为外键。
一对一的关系：
一个A只能对应一个B，一个B也只能对应一个A。
设计方式：
在任意一张表增加一列，引入另一张表的主键作为自己的外键。
表设计案例：
需求：设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每个课程由一个老师负责
3、每门课程可以由多个学生选修
4、每个学生可以选修多门课程
5、学生选修课程要有成绩
分析：
教师和课程是一对多的关系，得在课程表中增加一列，引入教师表的主键，作为自己的外键；
课程和学生是多对多的关系，得创建一张中间表，增加两列，引入课程和学生的主键，作为自己的外键；
学生选修课需要成绩，所以得在学生和课程的中间表增加一列，存放成绩
Sql语句：
创建教师表:
create table teacher(
id int primary key auto_increment,
name varchar(50)
);
创建课程表：
create table course(
id int primary key auto_increment,
name varchar(50),
teacher_id int,
foreign key(teacher_id) references teacher(id)
);
创建学生表:
create table student(
id int primary key auto_increment,
name varchar(50)
);
创建学生课程中间表：
create table studentcourse(
student_id int,
course_id int,
score double,
foreign key(student_id) references student(id),
foreign key(course_id) references course(id)
);
多表查询：
准备工作：
create table A( A_ID int primary key auto_increment, A_NAME varchar(20) not null);insert into A values(1,'苹果');insert into A values(2,'橘子');insert into A values(3,'香蕉');create table B( A_ID int primary key auto_increment, B_PRICE double);insert into B values(1,2.30);insert into B values(2,3.50);insert into B values(4,null);
内链接：
查询两张表中公共的部分：
查询出每种水果的价格：
方式一：
select a.A_ID,a.A_NAME,B_PRICE from a,b where a.A_ID = b.A_ID;
方式二：
select a.A_ID,a.A_NAME,B_PRICE from a inner join b on a.A_ID = b.A_ID;
左外连接：
以左边的表为基础去右边的表查询，如果有关联的记录，就显示出来，如果没有对应的记录就显示null
select * from a left join b on a.A_ID = B.A_ID;
全连接：
就是左连接和右连接之后，去掉重复数据，就用union，不需要去重就用union all
select a.*,b.* from a left join b on a.A_ID = B.A_ID
UNION
select a.*,b.* from b left join a on a.A_ID = B.A_ID;
不去重：
select a.*,b.* from a left join b on a.A_ID = B.A_ID
UNION ALL
select a.*,b.* from b left join a on a.A_ID = B.A_ID;
事务：
事务就是：在处理一件完整的事情，要么全部成功，要么全部失败；在数据库中，执行一组sql语句，要么全部成功，要么全部失败
开启事务：start transaction;
提交事务：commit;
回滚事务：rollback;
事务的特性（ACID）:
A:原子性，一件事务不可分割，要么发生，要么全不发生
C:一致性， 事务执行前后一致性，成功则提交，失败回滚
I:隔离性，一个用户的事务，不能被其他用户的事务影响
D:持久性，事务一旦提交，对数据库的改变就是永久性的
如果不考虑事务的隔离性，事务的并发会出现：
脏读：读取其他事务未提交的数据
不可重复读：在一个事务内，多次读取表中的数据，结果不一致(与脏读的区别是：读取的是已经提交的)，主要是针对记录的值（update操作）
虚读：在一个事务内，多次查询的表的数据不一致，主要是针对记录的个数（insert，delete）
SQL查询强化案例：
学生选课信息查询案例:
准备工作：
CREATE TABLE teacher ( id int(11) NOT NULL primary key auto_increment, name varchar(20) not null unique );CREATE TABLE student ( id int(11) NOT NULL primary key auto_increment, name varchar(20) NOT NULL unique, city varchar(40) NOT NULL, age int ) ;CREATE TABLE course( id int(11) NOT NULL primary key auto_increment, name varchar(20) NOT NULL unique, teacher_id int(11) NOT NULL, FOREIGN KEY (teacher_id) REFERENCES teacher (id) );CREATE TABLE studentcourse ( student_id int NOT NULL, course_id int NOT NULL, score double NOT NULL, FOREIGN KEY (student_id) REFERENCES student (id), FOREIGN KEY (course_id) REFERENCES course (id) );insert into teacher values(null,'关羽');insert into teacher values(null,'张飞');insert into teacher values(null,'赵云');insert into student values(null,'小王','北京',20);insert into student values(null,'小李','上海',18);insert into student values(null,'小周','北京',22);insert into student values(null,'小刘','北京',21);insert into student values(null,'小张','上海',22);insert into student values(null,'小赵','北京',17);insert into student values(null,'小蒋','上海',23);insert into student values(null,'小韩','北京',25);insert into student values(null,'小魏','上海',18);insert into student values(null,'小明','广州',20);insert into course values(null,'语文',1);insert into course values(null,'数学',1);insert into course values(null,'生物',2);insert into course values(null,'化学',2);insert into course values(null,'物理',2);insert into course values(null,'英语',3);insert into studentcourse values(1,1,80);insert into studentcourse values(1,2,90);insert into studentcourse values(1,3,85);insert into studentcourse values(1,4,78);insert into studentcourse values(2,2,53);insert into studentcourse values(2,3,77);insert into studentcourse values(2,5,80);insert into studentcourse values(3,1,71);insert into studentcourse values(3,2,70);insert into studentcourse values(3,4,80);insert into studentcourse values(3,5,65);insert into studentcourse values(3,6,75);insert into studentcourse values(4,2,90);insert into studentcourse values(4,3,80);insert into studentcourse values(4,4,70);insert into studentcourse values(4,6,95);insert into studentcourse values(5,1,60);insert into studentcourse values(5,2,70);insert into studentcourse values(5,5,80);insert into studentcourse values(5,6,69);insert into studentcourse values(6,1,76);insert into studentcourse values(6,2,88);insert into studentcourse values(6,3,87);insert into studentcourse values(7,4,80);insert into studentcourse values(8,2,71);insert into studentcourse values(8,3,58);insert into studentcourse values(8,5,68);insert into studentcourse values(9,2,88);insert into studentcourse values(10,1,77);insert into studentcourse values(10,2,76);insert into studentcourse values(10,3,80);insert into studentcourse values(10,4,85);insert into studentcourse values(10,5,83);
-- 1、查询平均成绩大于70分的同学的学号和平均成绩
select student_id,FORMAT(avg(score),2) as '平均成绩' from studentcourse group by student_id having avg(score) > 70;
-- 2、查询所有同学的学号、姓名、选课数、总成绩
select student.id,student.name,t.countCourse,t.sumScore from student,(
select student_id,count(*) as 'countCourse',sum(score) as 'sumScore' from studentcourse group by student_id
) as t where student.id = t.student_id;
-- 3、查询学过赵云老师所教课的同学的学号、姓名
select id,name from student where id in (
select student_id from studentcourse where course_id in(
select id from course where teacher_id = (
select id from teacher where name='赵云')));
http://www.cnblogs.com/xiaoshitoutest/p/7127377.html
更多精彩分享
- Our Top Tips for Buying Baby Clothing 2024-04-16 13:54:09.997
- Manage dairy business efficiently with Milk Distribution Software 2024-01-22 19:45:24.153
- Top App Development company in Pune 2023-05-27 21:25:52.243
- 告别“老顽固” 2023-04-18 11:15:46.437
- 时光飞逝 2024-03-26 11:25:53.823
- 会当凌绝顶，一览众山小 2023-04-18 10:38:34.08
- 自豪 2023-04-18 10:31:27.963
- 绽放 2023-04-18 10:30:19.243
- 毕业有感 2023-04-18 10:27:54.643
- 投入六个月的经历，收获了整个人生 2023-04-18 10:18:55.813
Manage dairy business efficiently with Milk Distribution Software