MySql 基础语句

1.开启关闭

net start mysql

net stop mysql

mysql -u root -p

// 准备退出mysql时一定要exit。
exit

2.数据定义语言 DDL

DDL（data definition language）用于定义、撤销、修改关系模式，如表、视图、索引。

// 查看当前登录用户（；是表示sql语句结束，不能少）
select user()；

// 数据库操作
drop database db_test;        // 删除数据库db_test
create database if not exists db_test;     // 创建一个名为db_test的数据库
show databases;                // 查看数据库列表 
use db_test;                // 选择db_test做为当前数据库，后续操作默认都在此数据库
mysql -uroot -proot db_test        // 直接在进入mysql时指定数据库

// 数据表操作
// 创建数据表tb_a
create table if not exists tb_a(
    id int(11) primary key auto_increment,
    name varchar(255),
    foreign key (name) 
        references tb_b,   // 外键name引用的是tb_b表的主键name
        on delete cascade  // 表示当主表中删除了某一个主键时，基表中引用此主键的行也随之被删除
        on delete restrict     // 凡是被基表所引用的主键，不得被删除，默认
    )engine=InnoDB auto_increment=1 charset=utf8;

// 查看数据库
show create table tb_a;        // 查看刚刚创建数据表的语句
show tables;    //查看存在的表
desc tb_a;        //查看数据表结构
select * from tb_a;

alter

// alter 修改表结构
// 表字段修改
alter table tb_a rename tb_newname;        // 重命名tb_a
alter table tb_a modify id int(12);        // 修改id字段数据类型
alter table tb_a change id newID int(11);    // 重命名id字段为newID，并修改数据类型。（需要旧的列名称）
alter table tb_a add sex enum('男', '女') after name;    // 在name后增加一个字段
alter table tb_a drop sex;    //删除字段sex

// 约束修改
// 系统会自动在主键上建立索引，当插入元组时，会进行唯一性检查，大量插入时，会影响系统效率
// 暂时撤销主键，完成插入后再补充定义主键，可以提高效率。
alter table tb_a add primary key(name);

// 定义外键后，也需要引用完整性检查，会影响系统性能，必要时可暂时撤销
alter tabel tb_a drop primary key;

索引

// 索引
create [unique] [cluster] index_a on tb_a(id desc, newID asc);
// []为可选内容
// unique表示此索引的每一个索引值只对应唯一的数据记录
// cluster表示要建立的索引是簇集索引
// asc表示升序，desc表示降序，默认是升序。

// 索引只能删除，不能修改
drop index index_a;

单引号和反引号

• 反引号是为了区分MySQL的保留字与普通字符而引入的符号。
• 引号一般用在字段的值，如果字段值是字符或字符串，则要加引号.

删除表内数据

• delete from tb_a 【可选 where id=1】;

  delete删除表内数据，表结构不变，删除操作作为事务记录在日志中保存以便进行进行回滚操作。数据空间不释放，因为需回滚。对 table 和 view 都能操作。速度慢。

• optimize table tb_a;

  释放tb_a的空间

• truncate table tb_a;

  一次性地从表中删除所有的数据，释放存储表数据所用的数据页来删除数据，并不把单独的删除操作记录记入日志保存(只在事务日志中记录 页的释放)，因此也不能回滚，不能恢复数据，在删除的过程中不会激活与表有关的删除触发器，占用资源更加少，速度更快。数据空间会释放，这个表和索引所占用的空间会恢复到初始大小。只能操作没有关联视图的 table。不能用于参与了索引视图的表。

• drop table tb_a;

  删除的是整个表，包括表的结构，数据，定义。永久抹去，空间释放。对 table 和 view 都能操作。

3.查询语言 QL

select [all |  distinct] <目标列表达式>[别名] [, <目标列表达式>[别名] ]...
from <表名或视图名>[, <表名或视图名>]...
[ where <条件表达式>]
[ group by <属性列1> [having <条件表达式> ] ]
[ order by <属性列2> [asc | desc] ];


select * from student;
select id, name from student;
select distinct name from student;    //消除重复
select id, name 
    from  student
    where sex='女';

• all 表示查询结果不消除重复元组。

• distinct表示消除重复元组。[]表示可选项

• group by 将结果根据属性列1的值进行分组

• having是分组必须满足的符加条件。必须跟在group by后面

• order by 将结果根据属性列2的值进行排序，asc升序（默认），desc降序。（必须在group by的后面）

where常用的查询条件

查询条件	谓词
比较	=，<, >, <=, >=, <>, !=, !>, !<
确定范围	between…and… , not between… and…
确定集合	in , not in
字符匹配	like, not like
空值	is null , is not null
多重条件	and , or

// 1.查询课程号为001的课程的学生学号、姓名
select cource.id, name
from student, cource
where student.id = cource.id    // 由于id在两个表都有出现，因此引用id时需要注明基表名
    and cid = '001';

谓词 in

// 2.查询同时选修001和002两门课程的学生学号、姓名（and） 
select id, name
from student
where id in
    (select x.id
     from cource as x, cource as y        // 为了区别两个课程，引入别名x、y加以限定
     where x.id = y.id
         and x.cid = '001'
         and y.cid = '002');

// 3.查询缺考的学生学号、课程号（is null）
select id, cid, grade
from cource
where grade is null;    // 空值是状态不是值，不能写成grade=null

// 4.查询只有一个人选修的课程号（not in）
select cid
from cource sc        // cource的别名sc，区别不同层次上对同一个表的查询
where cid not in
    (select cid
     from cource
     where id <> sc.id); 

// 聚合函数方式
select cid
from cource
group by cid
having count(*) = 1;

谓词 exists

带有exists的子查询不返回任何数据，只返回布尔值 true或者false。

// 嵌套查询逐次求解、层次分明，而且执行效率也比连接查询效率高
// 5.查询没有选修001课程的学生姓名
select name
from student
where not exists
    (select *
     from cource
     where id=student.id and cid='001');

谓词 like 模糊查询

like 进行全部或部分字符串的匹配。%匹配0或多个字符；

// 6.查询课程名为计算机开头的课程
select * 
from course
where cname like '计算机%';

between… and… 范围查询

// 7.查询不是1990年到2020年出生的学生姓名
select name
from student
where year(bdate) 
    not between 1990 and 2020;

聚集函数查询

聚集函数通常能够减少比较的次数，查询效率较高。

• 计数

  count( [distinct | all ] <属性列>) 统计某属性中值的个数

• 计算总和

  sum( [distinct | all ] <属性列>) 计算某属性值的总和

• 计算平均值

  avg( [distinct | all ] <属性列>) 计算某属性值的平均值

• 最大值

  max( [distinct | all ] <属性列>)

• 最小值

  min( [distinct | all ] <属性列>)

// 8.查询选修了课程的学生人数,用num字段显示
select count(distinct id) as num    // 一个学生可以选多门课程，但统计时不能重复计算，用distinct去重 
from cource;

// 9.查询每门课对应的选课人数num，结果集：cid num
select cid, count(id) as num
from cource
group by cid;    // 按课程号分组。属性列名必须在select中出现

// 10.查询选修了三门以上课程的学生学号
select id
from cource
group by id
having count(*) >= 3;    // having指定选择组的条件，只有满足条件的才会出现在结果

// 11.查询计算机相关所有课程的最高分、最低分、平均分。有缺考课程不予统计。结果按cid升序
select cid, max(grade) as maxgrade, min(grade) as mingrade, avg(grade) as avggrade
from cource
where cid like '计算机%'
group by cid    // 按cid分组
having cid not in    // 不统计有缺考的课程
    (select cid
        from cource
        where grade is null)
order by cid;    // 结果按cid升序

集合查询

并、交、 差分别对应：union、 intersect（and）、 except

// 12.查询1999年出生的或者选修计算机相关课程的学生学号
(select id
from student
where yaer(bdate) = 1999)
 union
(select id
from cource
where cid like '计算机%');

查询分页

// 从数据表tb_a 的第4行开始，返回6行数据
select * from tb_a limit 3,6;

4.数据操作语言 DML

insert

insert into tbname [(<>, <>)]
values (<>, <>...),(<>, <>...)... ;

// 向student表插入一行数据
insert into student
values ('001', '张三', '男');

• 如果没有指定属性列则表示要插入的是一条完整的元组，且与表中定义顺序一致。
• 如果指定部分属性列，其余属性列取空值。

update

执行修改语句时会检查是否会破坏表的完整性规则，如果破坏会给出提示，且修改操作会失败。

update table_name
set <属性列名>=<表达式> [, <>=<>]
[where <条件>];

// 将cource表所有学生成绩清零，缺考的除外
update cource
set  grade=0
where grade is not null;

delete

执行删除语句时会检查是否会破坏表的完整性规则，如果破坏会给出提示，且删除操作会失败。

delete 
from table_name
[where <条件>];

// 删除学号为001的学生
delete from sutdent
where id='001';