数据库学习总结

1
SELECT*--*为所有属性都要
FROM --加表
2
使查询表中的名字改变
(1):直接+‘ ‘+‘name‘
(2):‘name‘=类别
(3):类别 AS ‘name
3
SELECT ‘8‘ teacher
FROM class--属性teacher都变成了8
4
SELECT time
FROM class
WHERE class<2
5
SELECT time
FROM class
WHERE
class BETWEEN 0 AND 1--between a and b,确定范围
6
SELECT time
FROM class
WHERE
teacher IN (‘李‘)--用IN来选特别字,多个用逗号分隔
7
‘\‘表示为普通字符不是通意字符,相当于转义字符
8
SELECT sno
FROM stu
WHERE
sno like‘21%‘
9
CREATE TABLE st(
--Sno int PRIMARY KEY,--设置主键
name varchar(10) NULL,
学号 char(10) NULL,

)--SQL语句创建表
10
实体完整性:主键的值不能为空
11
实体完整性:cannot be same,if there are two more primary key,cannot be the every one same,but some of
them can be same;
12
参照完整性:data must come from the other table,add 外键,form restrict;
13
ALTER TABLE SC
add constraint FK_SC_STU foreign key(name) references stu(name)--FK 为外键,被应用的健为主键才行
14
级联:上一个改这个也改,在健里面改
15
ALTER TABLE stu
add constraint CK_stu_name check(name>0 and name <10)
16
数据库关系图:可看出关系
17
SCD S stu C course d department
18
disign a database,at first,consider a dependence,then consider regularize;
19
dependent relationship say that both a and b must be one-to-one relationship;just like a->b,
just one b;or (sno,con)->score;for the every a,just have one a,but for every b,likely have two
more a;
20
平凡:看b是否为a的子集,是为平凡,一般无意义
21
如果非主属性B函数依赖于构成某个候选关键字的一组主属性A,
而且A的任何一个真子集不能被B函数依赖,则称B完全函数依赖于A;
反之,若B函数能依赖于A的真子集,则称B部分函数依赖于A。
22
传递函数依赖:a->b,b can‘t->a,b->c,so a->c;
if b->a,so a<-->b,so a and c is dependent relationship,not 传递依赖;相当于等价
23
(a,b,c)都确定才能确定为全码
24
外部码确定了表之间的关系
25
所谓第一范式(1NF)是指数据库表的每一列(即每个属性)都是不可分割的基本数据项,
同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。
简而言之,第一范式就是无重复的列。 类似于主键
26
码感觉相当于主键
27
第二范式:在第一范式的基础上,要求每一个非主属性的码都是完全依赖于键
28
3nf:在第二范式的基础上不能有传递依赖即a->b,a->c,b->c;
28
bcnf:非主属性不能决定主属性
29
多值依赖:新加一个值后可能要加入更多行,
30
string constr = "Data Sourse=.;Initial Catalog=zhu;Intergrated Security=Turn";//.表示在本电脑上,若网络上则需改地址
31
string sqlstr = "select * from Stu where Stu.sno=‘"+textBox1.Text"‘";
32
if(textBox1.Text=="")
            {
                MessageBox.Show("输学号");
                return;
            }
            string constr = "Data Source=.;Initial Catalog=zhu;Integrated Security=True";
            string sqlstr = "select * from Stu where Stu.sno=‘"+textBox1.Text+"‘";//‘“为‘,”’“为‘
            SqlConnection con = new SqlConnection();
            con.ConnectionString = constr;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlstr;
                cmd.Connection = con;
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                DataTable tablel = new DataTable();
                tablel.Load(reader);
                dataGridView1.DataSource = tablel;
            }
            catch(Exception)
            {
                Console.WriteLine("错误发生在:", e);
            }
            finally
            {
                con.Close();
            }
33
insert into Stu values(3,1,1,1);--SQL的插入
34
ExecuteNonQuery,执行完之后返回一个值
35
UPDATE A,B SET A.a=B.a where A.c=B.c;
36
修改:update 表名 set cknum =‘-100.00‘ where cknum =‘CK000010000002‘

数据库学习总结

上一篇:MySQL数据库备份与还原


下一篇:0700-二叉搜索树中的搜索