1.SQL数据定义语言(基础)

1.创建数据库

  • CREATE Database database_name;
  • 习惯Sql语句中，保留的关键字否设置为大写，数据库、表、字段为小写。 

2.创建数据表

  • 创建数据表基本语法
  • CREATE TABLE 表名(
      列名1  类型 [NOT NULL,UNIQUE,DEFAULT value,PRIMARY KEY,CONSTRAINT foregin_name     
      foregin key(column) references  outer_table_name(column) on Delete RESTRICT|CASCADE|SET NULL]
);
  • 说明
  • 1.中括号中的内容为可选项目
  • 2.常用的列数据类型有 INT、SMALLINT、FLOAT、DATE、DATETIME、VARCHAR(n)、CHAR(n)等
  • 3.NOT NULL 设置值不能为NULL、UNIQUE设置值不能重复
  • 4.PRIMARY KEY设置为主键 只有外键可以设置为auto_increment
  • 5.CONSTRAINT外建名 references outer_table_name(列值) on Delete|Update RESTRICT|CASCADE|SET NULL] 设置外键 restrict主表中数据不能删除数据、CASCADE主表中被删除那么引用为外键值对的表格也删除、主表中的数据被删除那么引用为外键的表的数据设置为NULL
  • 举例--mysql测试
  • 1.student表 
    CREATE TABLE student(
       SNO CHAR(7) PRIMARY KEY,
       SNAME VARCHAR(8) NOT NULL,SEX CHAR(2) NOT NULL,
       BDATE DATE NOT NULL,
       HEIGHT DEC(5,2) DEFAULT 000.00); 
  • 2.SCOURSE表
  •  CREATE TABLE SCOURSE(
          CNO CHAR(6) PRIMARY KEY,
          LHOUR SMALLINT NOT NULL,
          CREDIT DEC(1,0) NOT NULL,
          SEMESTER CHAR(2) NOT NULL); 
  • 3.SC表---添加外键
  • CREATE TABLE sc(
       SNO CHAR(7) NOT NULL,
       constraint SNO_foregin_key foreign key(SNO) references student(SNO) ON delete cascade,
       CNO CHAR(7) not NULL,
       constraint cno_foreign_key foreign key(CNO) references scource(cno) on delete restrict,          
       grade dec(4,1) DEFAULT null);

3.修改表的结构

  • a.添加新列------ADD
  • alter table_name add 列名 类型  
  • b.修改列字段类型----modify
  • alter table_name modify 列名 类型　
  • c.修改列的名称或者属性-----change
  • alter table_name change name1 name2 类型
  • d.添加主键------add primary key(column);
  • alter table_name add primary key(column);　
  • alter table table_name drop primary key;//删除主键 注意：如果此列被设置为外建 on delete restrict，那么只有删除了外键之后才能删除主键
  • e.添加其他属性-------add unique(column)
  • alter table_name add unique(column)
  • g.重命名表名--------rename
  • alter table_name rename table_name table_name_new
  • h.外键设置------外建引用的列值必须唯一
  • alter table_name add constrints foreign_name foreign key(column) references outer_table(column) on delete set null;
  • alter table_name drop foreign key foreign_name;//删除外键
  • 索引设置—后续
  • alter table_name add index index_name(column)
  • alter table_name drop index index_name;//删除索引

4.MYSQL索引

  • MYSQL索引----加快查询速度
  • MySQL中，所有的数据类型都可以被索引。
  • MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引等。
  • 缺点：
  • 1.创建和维护索引需要消耗时间
  • 2.索引需要占用物理空间
  • 索引的分类
  • 普通索引— 没有任何约束条件的索引
  • 唯一索引— Unique(索引对应的值不能重复)
  • 全文索引—FullText(只能创建在CHAR、VARCHAR、TEXT)
  • 单列索引—索引对应一列值
  • 多列索引—索引对应多列值
  • 创建索引
  • 1.创建表的时候设置索引
  • 格式[UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name (column [length长度] [ASC|DESC]);
      • a.普通索引 index index_name(id) 
        • create table score(id int(11) primary Key not null, index id_index(id));//这里其实是多余的，主键上自动会添加索引
      • b.唯一索引
        • unique index index_name (column desc);
      • c.全文索引
        • FullText index index)name(info desc);
      • d.单列索引
        • Index index_name(tel(20));//tel 为varchar类型
      • e.多列索引
        • Index index_name(column1,column2);
        • 多列索引生效的条件是什么？
        • 查询条件包含column1
        • eg:alter table table_name add index index_name(column1,column2);
        • select * from table_name where column2="value";//索引是不会起作用的
  • 2.已经创建的表格添加索引
      • create [Unique|FULLTEXT|SPATIAL] Index index_name on table_name(column  length desc);
      • e.g.：create unique Index id_index on studentInfo(id);//id字段添加了自动索引
  • 3.修改表的结构并添加索引
      • Alter table table_name add [unique|fulltext] index index_name(column desc);
  • 4.删除索引
      • ALTER Table table_name drop Index index_name;
  • 5.查看添加的索引
      • show create table table_name;

5.SELECT语句

  •  5.1 表格数据

  • 表1.course表
  • 表2.student
  • 表3.sc

  • 5.2 查询语句联系

  • Q1:查询秋季学期有一门以上得分90以上的学生的名字
  • a.SELECT sname from student WHERE sno in (SELECT sno from sc WHERE grade>90 and cno in (SELECT cno from course WHERE semester="秋"));
  • b.SELECT sname  FROM student JOIN sc USING(`SNO`) JOIN course USING(cno) WHERE `SEMESTER`="秋" and grade>90;
  • Q2:查询只有一个人选修的课程号
  • a.SELECT cno from (SELECT cno,count(*) as count from sc GROUP BY `CNO`)a WHERE count=1;//from后的子查询必须添加别名
  • select CNO from sc group by CNO having count(*)=1;
  • b.SELECT `CNO` FROM sc WHERE cno not in (SELECT sc1.cno from sc as sc1,sc as sc2 WHERE sc1.sno!=sc2.`SNO` and sc1.cno=sc2.cno);
  • c.SELCT cno from sc as scx where cno not in (select cno from sc where sno!=scx.sno);//关联字查询
  • Q3:查询选修EE-1的学生的姓名
  • a.SELECT sname FROM student WHERE `SNO` IN (SELECT `SNO` from sc WHERE cno="EE-1");
  • b.select sname FROM student,sc WHERE student.`SNO`=sc.`SNO` and `CNO`="EE-1";//链接
  • c.SELECT `SNAME` FROM student  WHERE EXISTS (SELECT * from sc where sc.sno=student.`SNO`AND cno="EE-1");//使用Exists
  • Q4:查询学生的平均身高
  • SELECT AVG(`HEIGHT`) FROM student;
  • Q5:查询缺成绩的学生名和课程号
  • SELECT sname,cno FROM student JOIN sc USING(`SNO`) WHERE grade IS NULL;

  • 5.3 Group BY 和Order by的使用

  • Q1:查询CS开头的课程的平均成绩，最大成绩，最小成绩，除去成绩有NULL的科目，最终结果按照课程名进行排序
  • SELECT cno,MAX(grade),MIN(grade),avg(grade) FROM sc WHERE `CNO` LIKE "CS%" GROUP BY `CNO` HAVING `CNO` IN (SELECT `CNO` FROM sc WHERE grade is not NULL) ORDER BY `CNO` ASC;
  • 注意HAVING的条件

  • 5.4 Union的使用

  • 合并查询UNION—合并集合查询的结果(一般是同一列)
  • 关键字
      • 1.UNION       ——合并结果并去重
      • 2.UNION ALL——简单的合并结果
  • Q1:查询1983年出生的学生和选修EE专业的学生的学号
  •  SELECT sno from student WHERE year(`BDATE`)="1983" UNION SELECT `SNO` FROM sc WHERE cno like "EE%";

6.Insert、update、Delete语句 

  • 6.1 Insert语句

  • Insert into table(column1,coliumn2) values(values1,values2);

  • 6.2 Update语句

  • update table_name set column=value where 条件　

  • 6.3 Delete语句 

  • delete　from　table_name where　条件　　

 

 

posted @ 2017-02-16 23:59  疯狂的肉包  阅读(315)  评论(0)  编辑  收藏  举报