mysql

一：数据库和表
1、创建数据库
create database/schema if not exists test1;
default:指定默认值
character set:指定数据库字符集
collate:指定字符集的校对规则
2、使用已存在数据库
use test1;
在用create database语句创建了数据库后，该数据库不会自动成为当前数据库，要用use语句指定。
3、修改数据库参数
alter database test1
default character set gb2312
default collate gb2312_chinese_ci;
4、删除数据库
drop database if exists test1;






1、创建表
use test1;
create [temporary] table x;创建临时表
create table if not exists xs
(
学号   char(6)  not null primary key,
姓名   char(8)  not null,
专业名 char(10) not null
)engine=innodb|myisam;
engine=innodb表示采用的存储引擎是innodb.


index_definition:表索引项定义
table_option:用于表的描述
select_statement:可以在create table语句的末尾添加一个select语句，在一个表的基础上创建表
default default_value:为列指定默认值
primary key:主键


类型  tinyint [unsigned] [zerofill]
       small int   [unsigned] [zerofill]
       mediamint  [unsigned] [zerofill]
       int   [unsigned] [unsigned] [zerofill]
       integer  [unsigned] [zerofill]\                  bigint [unsigned] [zerofill]
         real [unsigned] [zerofill]
        double  [unsigned] [zerofill]
        float  [unsigned] [zerofill]
       decimal  [unsigned] [zerofill]
        numerical [unsigned] [zerofill]
       bit
       date
     time
     timestamp
    datetime
      char   varchar
   Tinyblob  ,  Blob  longblob
 text , tinytext mediumtext , long text.
 emum
Set   


2、修改表
alter table
alter [ignore]table xs
add 添加列
alter table t1 add column a tinyint null;
first|after col_name;表示在某列的前或后添加，不指定则添加到最后；
alter      {set default   |drop default}  修改默认值
change  对列重命名
alter table t1 change a b integer;
modify  修改列类型
alter table t1 modify b bigint nut null;
drop 删除列
rename 重命名该表
alter table a rename to b;
rename table a to b;
order by col_name 排序
convert to character set ...将字符集转换为二进制
[default]character set ...修改默认字符集
table options 列或表中索引项的增删改
例：use xscj;
alter table xs
add 奖学金等级 tinyint null;
drop column 姓名;


use xscj;
rename table table1 to student,table2 to course;


3、复制表;
create table test_copy1 link test;
test_copy1与test结构相同，但是空表；


create table test_copy2 as (select * from test);


二：表数据操作
1、插入表数据
insert 
1）use xscj;
insert into xs
   values('081101','王林','1','1990-02-10',50,null,null);


insert into xs(学号,姓名,性别,出生日期,总学分)
values('081101', '王林', 1, '1990-02-10', 50);


insert into xs
set 学号='081101',姓名='王林',专业=default,性别=1,出生日期='1990-02-10',总学分=50；


图片存储
插入图片存储路径
insert into xs
values('081102','程明','计算机',1,'1990-02-01',50,'d:\image\picture.jpg',null);
直接插入图片
insert into xs
values('081102','程明','计算机',1,'1990-02-01',50,load_file('d:\image\picture.jpg'),null);


2、删除表数据
delete
use xscj
delete from xs where 姓名='张三';


use xscj
delete from xs where 总学分<50;
从多个表中删除行
delete t1,t2
from t1,t2,t3
where t1.id=t2.id and t2.id-t3.id;


删除指定表中所有数据
truncate table table_name;
对于参与了索引和视图的表，不能使用truncate table删除数据；


3、修改表数据
update  set
update xs set 总学分=总学分+10;


update xs set 学号 ='081251',备注 = '转专业学习'  where 姓名='罗琳琳';


修改多个表
update tb1,tb2 set tb1.pwd='aaa',tb2.pwd='bbb' where tb1.id=tb2.id;


4、show语句
show tables/show tables from database_name;显示当前数据库中所有表的名称；


show databases:显示mysql中所有数据库的名称
show columns from table_name from database_name /show columns from database_name.table_name;显示表中列的名称；
show grants for user_name;显示一个用户的权限；
show index from table_name;显示表的索引；
show status:显示一些系统特定资源的信息；
show variables:显示系统变量的名称和值；
show processlist:显示系统中正在运行的所有进程；
show table status:显示当前使用或者指定的数据库中每个表的信息，信息包括表类型和表的更新时间；
show privileges:显示服务器所支持的不同权限；
show create database database_name;显示创建某一个数据库的create database语句。
show create table table_name;
show events:显示所有事件的列表；
show innodb status:显示innodb存储引擎的状态；
show warnings:显示最后一个执行的语句所产生的错误、警告和通知；
show errors:只显示最后一个执行语句所产生的错误；
show [storage]engines:显示安装后可用存储引擎和默认引擎；
show procedure status:显示数据库中所有存储过程基本信息，包括所属数据库、存储过程名称、创建时间等；
show create procedure sp_name:显示某一个存储过程的详细信息。


describe /desc等于 show columns from


三：数据库的查询和视图
1、选择
从表中选出满足条件的行形成一个新表作为运算结果


2、投影
从表中选出指定的属性值组成一个新表
。。。学号,姓名,总学分(学生)
3、连接
把两个表中的行按照给定的条件进行拼接而形成新表，RS


4、数据库的查询
选择列
use xscj
select 姓名,专业名,总学分 from xs;
定义列
select 学号 as number,姓名 as name,总学分 as mark from xs where 专业名='计算机';
替换查询结果中的数据
select 学号,姓名,
case
  where 总学分 is null then '尚未选课'
  where 总学分 <50 then '不及格'
  where 总学分 >=50 and 总学分<=52 then '合格'
  else '优秀'
  end as 等级
from xs
where 专业名 = '计算机';
5、计算列值
select 学号,课程号,成绩*1.20 as 成绩120
from xs_kc
where 学号='081101';
6、消除结果集中的重复行
select distinct|distinctrow column_name[,column_name...]
对结果集中的重复行只选择一个，保证行的唯一性。
例：select distinct 专业名,总学分 from xs;
7、聚合函数
常常用于对一组值进行计算，然后返回单个值。
函  数  名 说    明
COUNT 求组中项数，返回int类型整数
select count(*) as '学生总数' from xs;
select count(备注) as '备注不为空的学生数目' from xs;
select count(总学分)as '总学分50分以上的人数' from xs where 总学分>50;
MAX 求最大值
MIN 求最小值
select max(成绩),min(成绩) from xs_kc where 课程号 ='101';
SUM 返回表达式中所有值的和
AVG 求组中值的平均值
select sum(成绩) as '课程总成绩' from xs_kc where 学号 = '081101';
select avg(成绩) as '课程101平均成绩' from xs_kc where 课程号='101';
STD或STDDEV 返回给定表达式中所有值的标准差
VARIANCE 返回给定表达式中所有值的方差
select variance(成绩) from xs_kc where 课程号='101';
select stddev(成绩) from xs_kc where 课程号='101';
GROUP_CONCAT 返回由属于一组的列值连接组合而成的结果
select group_concat(学号) from xs_kc where 课程号='206';
BIT_AND 逻辑或 
BIT_OR 逻辑与
BIT_XOR 逻辑异或


from语句
引用一个表
select * from xs as student;
引用多个表
1)全连接
各个表用逗号隔开，新表为所有表的笛卡尔积
select distinct kc.课程名,xs_kc.课程名 from kc,xs_kc where kc.课程名=xs_kc.课程名;
2)join连接
内连接：指定了INNEER关键字的连接
应用了ON条件后的笛卡尔积
select distinct 课程名,xs_kc.课程名 from kc inner join xs_kc on (kc.课程名=xs_kc.课程号);
select 姓名,成绩 from xs join xs_kc on xs.学号=xs_kc.学号  where 课程号 = '206' and 成绩>=80;
内连接用于多个表的连接
select xs.学号,姓名,课程名,成绩 from xs join xs_kc on xs.学号 = xs_kc,学号
join kc on xs_kc.课程号=kc.课程号 where 课程名='计算机基础' and 成绩>=80;


自连接：在一个表中查找具有相同列值的行
select a.学号,a.课程号,b.课程号,a.成绩 from xs_kc as a join xs_kc as b on 
a.成绩=b.成绩 and a.学号=b.学号 and a.课程号！=b.课程号;
select 课程名 from ks inner join xs_kc using(课程号);
using(column_list)子句用于一系列的列进行命名，这些列必须同时在两个表中存在。


外连接：指定了outer关键字的连接
左外连接；右外连接；自然连接
select xs.*,课程号 from xs left outer join xs_kc on xs.学号=xs_kc.学号；
select xs_kc.*,课程名 from xs_kc right join kc on xs_kc.课程名=kc.课程名;
select distinct 课程名,xs_kc.课程号 from kc natural join xs_kc;


交叉连接
select 学号,姓名,课程号,课程名 from xs cross join kc;


where语句
select 姓名,学号,总学分 from xs where 学号='081101';
select 姓名,学号,出生日期,总学分 from xs where 总学分>50;
select 姓名,学号,出生日期,总学分 from xs where 备注<=>null;
select 姓名,学号,性别,总学分 from xs where 专业名='计算机' and 性别=0;


模式匹配
like:一个字符串是否与指定的字符串相匹配
select 学号,姓名,性别 from xs where 姓名 like '王%';
%代表0个或多个字符，_代表单个字符
select 学号,姓名,专业名 from xs where 学号 like '%0_';
学号倒数第二个数字为0的学生。。。
select 学号,姓名 from xs where 学号 like '%#_%'escape'#';
查询xs表中名字包含下划线的学生学号和姓名。


regexp:正则表达式的缩写
特殊字符 含    义                 特殊字符      含    义
^ 匹配字符串的开始部分                   [abc] 匹配方括号里出现的字符串abc
$ 匹配字符串的结束部分                   [a-z] 匹配方括号里出现的a～z之间的1个字符
. 匹配任何一个字符（包括回车和新行）  [^a-z] 匹配方括号里出现的不在a～z之间的1个字符
* 匹配星号之前的0个或多个字符任何序列       | 匹配符号左边或右边出现的字符串
+ 匹配加号之前的1个或多个字符的任何序列 [[. .]] 匹配方括号里出现的符号（如空格、换行、括号、句号、冒号、加号、连字符等）
? 匹配问号之前0个或多个字符   [[:<:]和[[:>:]] 匹配一个单词的开始和结束
{n} 匹配括号前的内容出现n次的序列          [[: :] 匹配方括号里出现的字符中的任意一个字符
() 匹配括号里的内容


use xscj
select 学号,姓名,专业名 from xs where 姓名 regexp'^李';
select 学号,姓名,专业名 from xs where 学号 regexp'[4,5,6]';
select 学号,姓名,专业名 from xs where 学号 regexp'^08.*08$';


范围比较
between in
select 学号,姓名,专业名,出生日期 from xs where 出生日期 not between '1989-1-1'and'1989-12-31';
select * from xs where 专业名 in('计算机','通信工程','无线电');
select * from xs where 专业名 ='计算机' or 专业名='通信工程'or 专业名='无线电';


空值比较
select * from xs where 总学分 is null;


子查询
作为查询条件一部分的查询
1）in子查询
select 姓名,学号 from xs where 学号 in
(
select 学号 from xs_kc where 课程号='206'
);
先进行子查询，产生一个结果表，再执行查询；


select 姓名,学号,专业名 from xs where 学号 not in
(
select 学号 from xs_kc where 课程号 in
(
select 课程号 from kc where 课程名='离散数学'
）
）；
2）比较子查询
select 学号 from xs_kc where 课程号 =
(
select 课程号 from kc where 课程名 ='离散数学'
);
select 学号,姓名,专业名,出生日期 from xs where 出生日期<all
(
select 出生日期 from xs where 专业名 ='计算机'
);
select 学号 from xs_kc where 课程号='206' and 成绩>=any
(
select 成绩 from xs_kc where 课程号='101'
);
3)exists子查询
select 姓名 from xs where exists
(
select * from xs_kc where 学号=xs.学号 and 课程名='206'
);
select 姓名 from xs where not exists
(
select * from kc where not exists
(
select * from xs_kc where 学号=xs.学号 and 课程号=kc.课程号
)
);
select 姓名,学号,总学分 from 
(
select 姓名,学号,性别,总学分 from xs where 总学分>50
)as student where 性别='1';
select 学号,姓名,YEAR(出生日期)-YEAR(
(select 出生日期 from xs where 学号='081101')
)as 年龄差距 from xs where 性别='0';
select 学号,姓名 from xs where 

(性别,总学分)=(select 性别,总学分 from xs where 学号='081101');

group by 子句
主要用于根据字段对行分组
select 专业名 from xs group by 专业名；
select 专业名,count(*)as'学生数' from xs group by 专业名;
select 课程号,AVG(成绩) as'平均成绩'，count(学号)as'选修人数' from xs_kc group by 课程号；
select 专业名,性别,count(*)as'人数' from xs group by 专业名,性别 with rollup;
使用带rollup操作符的group by子句：指定在结果集内不仅包含由group by提供的正常行，还包含汇总行，按列的排列的逆序依次
进行汇总；
select 专业名,性别,count(*)as'人数' from xs group by 专业名,性别;
select 课程名,专业名,avg(成绩)as '平均成绩' from xs_kc,kc,xs where xs_kc.课程号 = kc.课程号 and xs_kc.学号=xs.学号
group by 课程名,专业名 with rollup;


having子句
目的与where子句类似，用来在group by子句后选择行；
select 学号,avg(成绩)as'平均成绩' from xs_kc group by 学号 having avg(成绩)>=85;
select 学号 from xs_kc where 成绩 >=80 group by 学号 having count(*)>2;
select 学号,avg(成绩)as'平均成绩' from xs_kc where 学号 in
( 
select 学号 from xs where 专业名 ='通信工程'
)group by 学号 having avg(成绩)>=85;


order by子句
select 学号,姓名,专业名,出生日期 from xs where 专业名 ='通信工程' order by 出生日期;
select 学号,姓名,专业名 from xs where 专业名='计算机' order by(
select avg(成绩) from xs_kc group by xs_kc.学号 having xs.学号=xs_kc.学号
);
limit子句限制被select语句返回的行数
select 学号,姓名,专业名,性别,出生日期,总学分 from xs order by 学号 limit 5;
最前面5行，limit3,5:从第四行开始返回5行。
select 学号,姓名,专业名,性别,出生日期,总学分 from xs order by 学号 limit 3,5;


union把来自许多select语句的结果组合到一个结果集中
select 学号,姓名,专业名,性别,出生日期,总学分 from xs where 学号='081210';


handler语句
use xscj
handler kc open;打开一个表
handler kc read first where 学分>4;用于浏览一个打开的表的行；
handler kc read next;
handler kc close;关闭该表；




数据库的视图
视图是从一个或多个表（或视图）导出的表。不同的角色有不同的需要，在物理的数据库上定义他们对数据库所要求的数据结构，
这种根据用户观点所定义的数据结构就是视图。视图是一个虚表，即视图所对应的数据不进行实际存储，数据库只存储视图的定义，
对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。


创建视图--create view
create or replace view xscj.cs_kc as select xs.学号,课程号,成绩 from xscj.xs,xscj.cs_kc where
xs.学号=xs_kc.学号 and xs.专业名='计算机' with check option;
or replace 替换已同名的视图
WITH CHECK OPTION：指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。
use xscj
create view cs_kc_avg(num,score_avg) as select 学号,avg(成绩) from cs_kc group by 学号；
查询视图
select 学号,课程号 from cs_kc;
create view xs_kc_avg(num,score_avg) as select 学号,avg(成绩) from xs_kc group by 学号;
select * from xs_kc_avg where score_avg>=80;
1)可更新视图
即可以通过视图更新基本表中的数据，即可以在insert,update,delete等语句中使用；包含下列结构的任何一种，就不可更新：
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；
（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。
2）插入数据
create or replace view cs_xs as select * from xs where 专业名='计算机' with check option;
insert into cs_xs values('081255','李牧','计算机',1,'1990-10-14',50,NULL,NULL);
当视图所依赖的基本表有多个时，不能向该视图插入数据；
3)修改数据
update cs_xs set 总学分 = 总学分+8;
一次修改只能变动一个基本表的数据
4)删除数据
delete from cs_kc where 性别=0;
对依赖多个基本表的视图，不能使用delete语句；


修改视图定义
alter
use xscj
alter view cs_xs as select 学号,姓名,总学分 from xs where 专业名='计算机';


删除视图
drop view cs_kc,cs_xs;