除了用;结束语句,还可以使用\G
启动MySQL服务
1 命令行形式管理window的Mysql
net start MySQL net stop MySQL
2 通过命令行直接进入MySQL目录下bin目录的mysqld.exe即可
3可以直接通过window提供的服务管理来操作MySQL服务。
通过命令行连接MySQL数据库
可以将mysql的bin目录添入命令汗的Path中,即可在命令行中直接操作。
右键我的电脑-属性-高级系统设置-高级-环境变量-找到变量Path-编辑-在结尾处添加一个分号和路径即可。
mysqld可以正常运行后,就可以使用客户端来访问操作了。
具体操作:
-h添上主机ip,-P(大写的P)写上端口号,-u用户名,-p(小写)密码,可以直接输入明文密码,也可以回车之后输入密文。
连接成功后:
后与直接操作mysql给我们提供的命令行操作工具是一样的。
SQL分类
SQL分类
1 数据操作(管理)语言 DML
查询,获得数据 DQL
管理,增加,删除,修改数据DML
2 数据定义语言(对保存数据的格式进行定义)DDL
3 数据库控制语言(针对数据库软件服务进行操作) DCL
标识符(数据库名)命名规则
大小写取决于当前操作系统。原则上认为是区分大小写的。
推荐使用下划线的方式,不用驼峰法,因为大小写的问题。
标识符的字符:
原则上可以使用任意字符,数字,符号,甚至是中文。但是一些特色的组合,例如纯数字组合,特殊符号,包括mysql是内部关键字,应该使用标识符限定符来包裹,就可以使用了。
限定符:反引号` 就是键盘1234左边的那个波浪线小写。
表的创建和操作
表的创建
可以直接通过语法,致命数据表所属的数据库
语法:库名.表名 这样写来代替原先的表名即可。
create table 表名 (结构)[表选项]
create table php_one.php_class (
class_no varchar(20),
date_start date
);
列定义:列名 列的数据类型[列的属性(约束)],
简单的字符串类型:varchar() 有一个参数,表示保存数据的最大字符长度(不是字节长度)。
日期类型: date
查看表
先选择数据库。
show tables;
依据表前缀查看表。
show tables [like ‘pattern’];
例如数据库中有exam_为前缀的表,则这样查看:
show tables like 'exam_%';
%通配符,表示任意字符的任意个数的组合。
数据库的查看也可以这样来。如show databases like ‘aaa_%’;
查看某个表的创建信息
show create table 表名;
查看数据库的创建信息同理。
当遇到客户端输出的结构比较混乱的时候。可以\G来代替;作为结束标记。
查看表结构
describe 表名;
可简写成:desc 表名;
删除表
drop table 表名;
drop table [if exists] 表名;
drop table if exists 表名;
上面同样使用与database。
修改表
修改表名
rename table old_tbl_name to new_tbl_name;
rename table old_aa to new_aa,old_bb tonew_bb;一条可以连续对多个表名进行修改
支持跨数据库重命名。
重命名相当于剪切粘贴。跨数据库重命名后,在本数据库中的表就没了,出现在了另外一个数据库。如:rename table php_class to shop php_c;
数据库不支持重命名。但是可以将旧数据库中的表都rename到新的数据库中,然后在删除旧的数据库即可。
修改列定义
增加一个新列
add
修改一个列的定义
modify
删除一个列
drop
重命名一个列(不提供直接的重命名操作)
change
修改表结构,上面的是子命令,上级是,alter table 表名 [add|drop|modify]
新增一列,身高
alter table php_class add height int;
删除一列,身高
alter table php_class drop height;
修改一个列的定义
alter table php_class modify class_novarchar(40);
重命名(不提供直接的重命名操作)
alter table php_class change class_noxuehao int;
交换两个表的名字
先建立一个中间表table3;
rename table table1 to table3,table2 totable1,table3 to table2;
修改表选项
alter table 新的表选项
alter table php_class character set utf8;
数据操作(CRUD)
create,read(retrieve) ,update ,delete
插入数据
insert into 表名 (字段列表) values (值列表);
也可以不写字段列表,但是需要值列表中的顺序与数据表的字段顺序一致。
insert into php_class(class_no,name,height) values ('php_001','xiaoming',175);
获得数据
select 字段 from 表名 where 条件。
select * from php_class; ó select * from php_class where 1; //1表示真。
若用*,则表示所有字段
查询条件可省略,表示所有的记录都获得。
删除数据
delete from 表名 where 条件;
delete from php_class where height>190;
删除不可逆,需要在逻辑上严格给条件,否则容易造成误操作。语法上可以没有where条件。如果需要删除所有数据,使用where 1;整张表全没了。
修改数据
update 表名 set 字段=新值,字段=新值,… where 条件;
update php_class set country='China' where1;
where 1即对所有数据操作。
校对规则
什么是校对规则?在当前编码下,字符间的比较关系。
show variables;
show variables like ‘character_set%’;
查看字符编码相关的变量。
每个字符集都支持不定数量的校对规则。
通常的命名规则是:字符集_语言名_ci|cs”bin
常见的ci不区分大小写。cs区分大小写。bin编码比较
每个字符集都有一个默认的校对规则,如果不指定,就使用该默认的规则。
可以通过如下指令:
show collation;查看校对规则
show collation like ‘utf8%’;查看有关utf8的校对规则。
order by
order by关键字,可以在获得数据时,将数据进行按照某个字段的排序。
select * from 表名 order by 字段名;
数值类型
数值型
数值型-整数类型
类型 | 字节 | 最小值(有符号/无符号) | 最大值(有符号/无符号) |
TINYINT | 1 | -128/0 | 127/255 |
SMALLINT | 2 | -32768/0 | 32767/65535 |
MEDIUMINT | 3 | -8388608/0 | 8388607/16777215 |
INT/INTEGE | 4 | -2147483648/0 | 2147483647 /4294967295 |
BIGINT | 8 | -9223372036854775808/0 | 9223372036854775807/18446744073709551615 |
可使用unsigned控制是否有正负,如tinyint unsigned 表示无符号的tinyint。
可以使用zerofill来进行前导零填充
也存在布尔bool类型,但是就是tinyint(1)的别名。
定义显示宽度
通过规定数据的显示宽度,达到统一显示的目的。
类型(M) 其中M就表示显示的最小宽度值。如tinyint(3);
需要配合使用前导零填充来达到目的zerofill。
字段名 tinyint(3) zerofill;
则显示的时候,不够3位宽度的前面用0来填充。超过最小宽度的按照真是宽度显示,不会被截取。
小数
数值型-小数类型
类型 | 存储空间(字节) | 最小值(理论) | 最大值(理论) |
FLOAT | 4 | -3.402823466E+38 | 3.402823466E+38 |
DOUBLE | 8 | -1.7976931348623157E+308 | 1.7976931348623157E+308 |
DECIMAL | 变长,大致是每9个数字,采用4个字节存储。整数和分数分开计算 | M,最大是65 D,最大是30 默认是10,2 -(65个9) | (+65个9) |
可以利用M,D控制数值范围;可以无符号;
可以zerofill;可以使用科学计数法
DECIMAL定点数,原封不动的存进去。用于高精度的数据。跟浮点数的存储方式不一样,浮点数存在精度丢失。
DECIMAL(M,D)
M总位数
D小数位数。
M默认为10,D默认为2。
浮点数:
类型(M,D);
M表示所有数值位数(不包括小数和符号)
D表示允许的小数位数。
float(5,2) 一共5位,小数点占了2位,double同理。位数不足不影响。超出了就不行。
以float(5,2)为例,123.12可以。1234.12不行,123.123不行。
浮点数支持科学计数法。
1.23E3 表示1.23*10^3。添加数据时可直接用上就行。
日期时间类型
日期时间类型
类型 | 显示格式 | 取值 | 存储空间 | 零值 |
DATETIME | YYYY-MM-DD HH:MM:SS | '1000-01-01 00:00:00'到'9999-12-31 23:59:59' | 8 | 0000-00-00 00:00:00 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 是‘1970-01-01 00:00:00’到2038-01-19 03:14:07 | 4 | 0000-00-00 00:00:00 |
DATE | YYYY-MM-DD | '1000-01-01'到'9999-12-31 | 3 | 0000-00-00 |
TIME | HH:MM:SS | -838:59:59'到'838:59:59' | 3 | 00:00:00 |
YEAR | YYYY | 1901到2155 | 1 | 0000 |
没有间隔福-也可以,或者使用其他的符号做间隔如‘:’,也可以正确输入数据。
支持两位数的年份,时间间隔是1970年到2069年。例如20-01-01表示2020年1月1日。89-01-01表示1989年1月1日。
即70-99 1900年+
00-69 2000年+
但是容易产生歧义,不建议使用。
如果datetime没有给定具体时间,默认就是00:00:00。
检索时字段+0,得到的结果是时间戳。
time类型
表示意义:
1可以表示一天中的时间
2也可以表示时间间隔,在表示间隔时,可以使用天来表示。格式:
D HH:MM:SS 如7 12:34:56 7天12小时34分在数据库中天会自动换成小时存储。
D表示天。
year类型
占用一个字节。范围1901-2155
字符串类型
字符串类型
类型 | 最大长度 | 备注 |
char | 255 | Char(M),M字符数 |
varchar | 65535,但需要1-2个保存信息,同时由于记录的限制,因此最大为65532 | 编码不同字符数不同: Gbk<=32767 Utf8<=21845 |
tinyText,text,mediumText,longtext | L + n。L为最大长度 2^8+1, 2^16+2, 2^24+3, 2^32+4 | 定义时,通常不用指定长度,可以自己计算。 |
enum | 1、2。枚举选项量(65535) | 内部存储是整型表示。 字段值只能是某一个 |
Set | 1、2、3、4、8。元素数量:64 | |
Binary,varbinary,blob | Binary(char),varbinary(varchar),blob(text) 做类比 | 二进制数据(字节而非字符) |
char - varchar
char(M) 固定长度
varchar(M) 可变长度
M表示允许的字符串长度。而不是字节数!!!但是,总的长度的使用是按照字节计算的。即char,最大的字节数是255字节。varchar是65535字节。即char中,M的最大取值与当前字符编码有关,M=255/当前编码一个字符占用的字节数。如双字节字符gbk占用2,即char中M最大值为127。varchar同理。通常得到的最大值需要-1,取不到最大值。
varchar的真实长度。varchar特点,当类型数据超过255个字符时,采用2个字节表示长度,即此时为65535-2。整条记录需要有个额外字节,用于保存当前字段的null值。除非所有的字段都不是null,才可以省略这个字节。所以在创建字段的时候如果添上notnull那么就可以多加一个字节。
M在varchar中的表示,是允许的最大长度。即实际用了多少就是多少。
char内的M表示严格限定的长度。不管用了多少,都是占用那么多空间。
varchar中会比实际字节多占用一个字节。需要一个字节保存字符串的总长度。
枚举类型
create table table1(
sex enum(‘famale’,’male’)
);
insert into table1 values(‘male’);
insert into table1 values(‘female’);
插入其他的数据类型就不行了。
虽然看着创建的枚举类型是字符串,但是其本质是字符串,第一个字符串是1,第二个是2。插入1与插入第一个字符串类型相同。插入2与插入第二个字符串类型相同。。。。。
最多允许65535个枚举量。
set类型
create table table1(
hobby set(‘basketball’ ,’football’ ,’pingpangqiu’,’swim’)
);
与枚举类型不同,这个可以多选,可以当选。
insert into table1 values (’football’ ,’pingpangqiu’);
列属性
设置多个列属性,用空格间隔就可以了。
null|notnull 是否为空
规定一个字段的值是否为null
defaultvalue 字段默认值
如:default ‘aaa’ 设置默认值为aaa
主键:PK ,primary key
可以唯一标识,某条记录的字段或者字段的集合,就是主键
主键可以是真实实体的属性。
但是常用的好的解决方案是:利用一个与实体信息不相关的属性,作为唯一标识。主键与业务逻辑不发生关系,只用来标识记录。
主键唯一,主键不能为空。
设置主键语法:
两种方案:
1字段上设置:
create table table1(
t_id int primary key,
t_name varchar(5),
class_name varchar(6),
days tinyint unsigned
);
2在定义完字段后,可以定义:
create table table1(
t_id int,
t_name varchar(5),
class_name varchar(6),
days tinyint unsigned
primary key (t_id)
);
这样定义,可以定义多列主键(组合主键)。primarykey (字段1,字段2…)
一个主键上包含了多个字段
而不是两个字段都是主键
两个字段一起组成了主键
一个表只能有唯一一个主键。
主键最好与实体没有联系,不是实体属性字段。
自动增长auto_increment
自动增长是从当前最小值增长1。
可以手动插入值,但如果是主键的话,不能重复
可以更新修改值。
为每条记录提供了一个唯一的标识。
每次插入记录时,将某个字段的值自动加1.
需要整型,还需要索引。
可以选择插入null,也可以不插入。插入null时,数据库中的值不是null。
自动增长的初始值是1,是可以设置的。
通过表选项:
alter table table1 auto_increment 10;
实体
1一对一
设计:
两个实体表内,存在相同的主键字段。
如果记录的主键值等于另一个关系表内记录的主键值
则两条记录一一对应。
2一对多
一个实体,对应多个其他实体
例如一个班级对应多个学生
设计:
在多的那端,增加一个字段,用于指向该实体所属的另外实体的标识
3多对多
设计:
典型的,利用一个中间表,表示实体之间的对应关系。
将两个关联表的主键合在一个表内就可以了。中间表的每个记录,表示一个关系。
外键 foreign key
概念:如果一个实体(php_student)的某个字段(student:class_id),指向(引用)另外一个实体(php_class)的主键,就称当前student实体的class_id是外键。
被指向的实体,称之为主实体(主表),也叫父实体(父表)。(php_class)
负责指向的实体,称为从实体(从表),也叫子实体(子表)。(php_student)
作用:
用于约束处于关系内的实体。
增加子表记录时,是否有与之对应的父表记录。
在删除或者更新主表记录时,从表应该如何处理相关的记录。
定义一个外键:
在从表上建立一个外键字段,指向主表的主键。
使用关键字foreign key。
foreignKey (外键字段) references 主表名(关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
set names gbk; //奇怪,没这个,汉字插不进去。
drop table if exists php_class;
create table php_class(
class_id int primary key auto_increment,
class_name varchar(10) not null default'itcast_php' comment '班级名称'
)characterset utf8 ;
create table php_student(
stu_id int primary key auto_increment,
stu_name varchar(10) not null default '',
class_id int,
foreign key (class_id) references php_class (class_id)
) character set utf8;
insert into php_class values (1,'php0331');
insert into php_student values (null,'张三丰',1);
设置级联操作
在主表数据发生改变时,与之关联的从表数据应该如何处理:
两大类:
主表更新
主表删除
使用关键字:
on update
on delete
来标识
允许的级联动作
cascade:关联操作,如果主表被更新或删除,那么从表也会执行相应的操作。
set null:设置为null,表示从表不指向任何主表记录。
restrict:拒绝主表的相关操作。
ondelete 和on update 是可以同时出现的。但是一个on delete或on update只能有一个级联动作。
- - - - - - - - - - - - - - - -- set null- - - - - - - - - - - - - - - - - - - -
修改这个外键:
先删除,在新建,通过修改表来完成。
alter tablephp_student drop foreign key php_student_ibfk_1;(通过show create table中的foreign信息获得)
执行完该操作后,show createtable中,这个信息就没了。
删除外键需要通过制定外键名称达到目的:
可以通过在创建外键时,制定名称,或者使用mysql默认生成的名称。
这样的操作不会删除数据,数据仍在,只是这个约束(规则没了,需要重新在定义)
alter table php_student add foreign key(class_id) references php_class (class_id) on delete set null;
执行完该语句,show create table又能查找到如下信息:
CONSTRAINT `php_student_ibfk_1` FOREIGN KEY(`class_id`) REFERENCES `php_class
` (`class_id`) ON DELETE SETNULL
on delete set null 当主表中的记录被删除后,从表中的对应记录的外键值变为null。
建议使用set null。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -- -- - - - - - -
- - - - - - - - - - - -- - - - -- - -cascade- - - - - - - - -- - - - - - - - --
大体同上
alter table php_student drop foreign keyphp_student_ibfk_1;
alter table php_student add foreign key(class_id) references php_class (class_id)on delete cascade;
on delete cascade当主表中的记录被删除后,从表中对应记录也被删除。
alter table php_student add foreign key(class_id) references php_class (class_id) on update restrict;
- - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - -- - - - - -
- - - - - - - - - - - - -- restrict- - - - -- - - - - - - - - - - - - -- - - - - - -- -
on update
指的是只有主表的主键发生变化,才会对从表发生影响。
alter table php_student add foreign key(class_id) references php_class (class_id) on update restrict;
这条语句,不允许主表中的相关操作。主表一更新就报错。
- - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - -- - - - - -
php程序员一般不使用外键关联。
存储引擎
修改表的存储引擎:
alter table 表名 engine 存储引擎名;
alter table table1 engine myisam;
或者在创建表的时候写上
create table table1(
……
) engine myisam或innodb;
count()
select count(*) from memberlist;
select count(id) from memberlist; //count(表达式)