1.SQL数据定义语言(基础)-CSDN博客

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%";