mysql学习

create database [if not exists] 数据库名;
drop database 数据库名;
use 数据库名;
desc tables;
desc 表名;
mysql -u 用户名 -p 密码 -h 主机名 --default-character-set=utf8
mysql有两种存储机制 (MyISAM InnoDB(默认, 支持事物))

1. DML 数据操作语言 insert update delete
2. DDL 数据定义语言 create alter drop truncate(先删表,再重建表)
3. DCL 数据控制语言 grant revoke
4. 事务控制语言 commit rollback savepoint

关键字
table 表 constraint 约束 view 视图 index 索引 function 函数 procedure 存储过程 trigger 触发器
数据字典(系统表, 存储数据库相关信息的表
create table [模式名.]表名(
columnName1 datatype [default expr],
…
) engine=InnoDB
mysql支持的数据类型 jdbcType(javaType)
tinyiint(boolean)
smallint
mediumint
int(int)
bigint(long)
float(float) double(double)
decimal(BigDecimal)
datetime(date localDateTime)
timestamp(date localDateTime)
year month day hour minute second
char varchar(String)
text mediumtext longtext tinytext(String) 文本对象
binary varbinary tinyblob blob mediumblob longblob(byte[]) 二进制大对象(存储图片,音乐等)二进制数据
enum(‘value1’,‘value2’,…) (String) 枚举类型
set(‘value1’,‘value2’,…) (String) 集合类型

模式

1. schema（模式）就是database(数据库)的代名词。
2. schema强调数据库中表（或其他对象）表示（存储）内容的方向。
   create schema sc1;
   show schemas;
   drop schema sc3;

修改基本表
ALTER TABLE Student ADD column_name 类型 约束条件; //新增一列 add后面可以用(),字段用,分割
ALTER TABLE Student MODIFY column_name 新的类型 新的约束条件; //修改某一列
ALTER TABLE Student DROP KEY 名;删除唯一性要求 //修改某一列
ALTER TABLE Student ADD UNIQUE(名);添加唯一性要求 //修改某一列
ALTER TABLE Student DROP COLUMN 名; //删除某一列

alter table wawa change bbb ddd int  //将wawa表中的bbb字段重命名为ddd,类型为int

数据库约束
数据库的null不等于null
null (可以并默认为空, default ''aa" 修改默认为aa) not null 非空约束 unique 唯一约束(可以有多个null) primary key 主键约束 check 检查约束(能用,但对mysql不起作用)
foreign key 外键约束
单列约束/多列约束
指定约束时机: 建表时, 建表后创建(以修改表的形式 alter table xx add(…);
列级约束语法/表级约束语法
列级约束语法 id int unique
表级约束语法 unique(id)
test_id int not null (约束名与列名相同/建表语句)
unique(test_name) //对test_name字段添加唯一约素/建表语句时使用
[constaint 约束名] 约束定义
constraint 约束名 unique(test_name) //对test_name字段添加唯一约束/建表语句时使用
多列添加约束 unique(字段名1, 字段名2) //对字段1和字段2组合添加唯一约束, 组合必须唯一

约束定义
unique(字段名)
unique(字段名1,字段名2)
not null(字段名)
not null(字段名1,字段名2)
primary key(字段名)
primary key((字段名1,字段名2)

id int auto_increment primary key //建表时部分语句, id主键自增

外键约束 foreign key 主表和从表
增加外键列的表称为从表, 删除主表记录前先删从表记录,或者级联删除
比如, 一个学生对应多门课程得分, 创建student表, 得分表score, 可以在score表中添加一个外键列
(关联studen_id), 先建主表,再建从表
得分表建表部分语句,

//方式1
create table score(
	id int, 
	student_id, 
	foreign key(student_id)           references student(id)   //外键约束名与字段名一致
)
//方式2
create table score(
	id int, 
	student_id, 
	constraint student_score_fk foreign key(student_id)        references student(id) //自定义外键约束名
)

//方式3
alter table score
add foreign key(student_id) references student(id)
删除外键约束
alter table score 
drop foreign key student_id;
级联删除可以设置在建表语句中

索引
创建索引的两种方式
1.自动, 在表上定义主键, 唯一约束 外键约束时, 系统会自动创建对应的索引
2.手动, create index …
删除索引的方式
1.自动, 表被删除时,自动删除索引
2.手动, drop index …

create index 索引名
on 表名(字段1, 字段2,...)  //对多列同时建立索引

drop index 索引名
on 表名    //删除索引

视图
视图看上去像一个数据表,实际不是, 只是一个或多个表中数据的逻辑展示
create  or replace view 视图名
as 
subquery  //如果不存在则创建, 存在则替换,  subquery是表查询出来的结果,类似于select * from a;
create or replace view student_view
as select id,name from student;  最后如果添加with check option 则不允许修改视图的数据
drop view student_view 删除相应视图

sql通配符  _  任意一个字符  %任意个字符(>=0个)
如果不希望sql表下划线和百分号当成通配符, 可以在前面加\

order by column_name1 [desc], column_name2...    //[]正则匹配, 可以写中扩号中指定格式的内容, 也可以不写


**函数**
分组和组函数
count()
如果分组的字段值有多个不同的, 查询的列又不是组函数 则最终结果是只展示每个列的第一条数据
id   age  name loc
1     11    aa     01
2     12    bb     01
3     13    cc     02
4     14    dd     02
5     15    ee     03
6     16    ff       03
select * from xxx group by loc
1     11    aa     01
3     13    cc     02
5     15    ee     03
select count(age) from xxx group by loc
11+12
13+14
15+16

goup by column_name1,column_name2   //字段1和字段2完全相同为一组
注意:
1. 不能在where子句中过滤组, where子句仅用于过滤行, 过滤组必须使用having子句
2. 不能在where子句中使用组函数(比如count,sum等), having子句中才可以使用组函数

一个学生对应多个老师,  按老师分组,  过滤出teacher_id(一个老师)对应多于3个(>2)学生(记录)的
过滤出的组,每组对应的记录都大于2, 但只展示每组的第一条记录
select *
from student
group by teacher_id
#对组进行过滤
having count(*) >2

多表连接 92和99标准的外连接相反(92左外连接相当于99的右外连接, 92右外连接相当于99的左外连接
sql 92标准
等值连接 非等值连接 外连接 广义笛卡尔积
from a,b where a.id=b.a_id (等值连接) 后面还可以添加and or之类的语句
from a,b where a.number >b.id (非等值连接)
自连接 一张表当两张表使用 该表有两个字段进行外键关联

sql 99标准
交叉连接 自然连接 使用using子句连接 使用on子句连接 on后面为连接条件
全外连接 左外连接 右外连接
交叉连接(cross join) 就是笛卡尔积
自然连接(natural join) 如果有同名列, 则按同名列连接, 否则就是笛卡尔积
using子句连接 selec * from a join b using(column_name) 两个表都有的column_name字段, 以该字段作为连接条件
on子句连接 后面可以跟等值或者非等值连接
selec * from a join b on a.id=b.id 或者on a.id > b.id

全外连接 select * from a full join b on a.id=b.id //a.id=b.id是连接条件
sql99会将两表中不满足连接条件的记录都列出
mysql中使用全外连接会提醒语法错误, 不支持!

三种连接
内连接 inner join 取两表的交集部分 使用on进行字段关联
左(外)连接 left join 左表记录全展示, 右边没有数据以NULL填充
理解: 把左表中所有不满足连接条件的记录全部列出
右(外)连接 right join 右表记录全展示, 左边没有数据以NULL填充
查询表1 table1和表2 table2
table1的字段table2_id月table2的字段id关联, 此时表2可能对于表1是一对多关系
select * table1 left join table2 on table1.table2_id = table2.id;

子查询
定义: 在一个查询中嵌套另一个查询
出现位置: from语句后面, 当成数据表,称为内视图, 本质就是一个临时视图 可以起别名
where语句后面, 作为过滤条件的值

集合查询
结果集1 运算[intersect | union | minus) 结果集2
示例: select id, name from table1 union select index, loc from table2;
理解: 将两条select语句查询的结果集进行运算(取交集intersect 取并集union 取差 minus),
得到新的结果集(结果集1和结果集2记录相同的部分, 结果集2追加到结果集1记录后面,
结果集1拿掉和结果集2相同的部分)
注意:
1.结果集1和结果集2列数必须相等(不想等无法进行比较, 因为将查出来的每条记录作为整体进行比较运算
2.结果集1和结果集2列的类型必须一一对应, 列名是否相同不重要
mysql不支持intersect和minus, 但可以通过join后者子查询实现相同功能

代码示例

mysql -uusername -ppassword
show databases
use db_01
desc employee
select * from employee;
create table student{
字段名1 字段类型(字段长度) 索引,                                      //列级约束语法
字段名2 字段类型(字段长度),
字段名3 字段类型(字段长度),
unique(字段名2)  //给字段名2添加唯一约束     "约束定义"   表级约束语法
constraint xx not null(字段名3)  //给字段名3添加非空约束,约束名xx
) engine=InnoDB charCode=utf-8
insert into employee(id, name, age) values(),();
delete from empoyee where id=1
update employee set name="xxx" where age>10;