目录
DML增删改(Data Modify Language数据库修改语言)
delete、truncate、drop、三种删除的区别(级别从高到低)
DQL查(Data Query Language数据库查询语言)
模糊查询:比较运算符is null,is not null,between and,like,in
联表查询join on(两个圆相交,根据不同共3个区域,根据不同组合7种)
聚合函数(重点,常用)以及having(聚合函数过滤)和group by(分组过滤)
字段属性
Unsigned
- 无符号整数
- 声明了改列不能声明为负数
zerofill
- 0填充
- 不足的位数,使用0来填充,int(3),5——005
自增
- auto_increment(默认自增1)
- 通常设计唯一主键,必须是整数类型
- 可以自定义设置主键自增的起始值和步长
null、not null
- 假设为not null,不赋值就会报错
- null,默认允许为null
defalut默认
- 设置默认值
- 如sex字段,默认为男
注释 --空格
例如:-- 这是注释
创建数据库
例子
-- comment 备注
-- 创建一个遍,有主键,且有存储引擎innodb,默认字符集utf-8
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
-- 格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型[属性][索引][备注comment],
`字段名` 列类型[属性][索引][备注comment],
...
`字段名` 列类型[属性][索引][备注comment]
)[表类型=存储引擎][字符集类型]
常用命令(好用)
可以逆向建数据库和数据表
SHOW CREATE DATABASE study2 -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示数据表的结构
数据表的类型——存储引擎
MYISAM(mysql 5.0之前使用)
INNODB(mysql 5.0之后默认使用)
物理空间存在的位置
所有的数据库文件都在data目录下
本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDBzai在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MyISM对应文件
- *.frm表结构文件
- *.MYD数据文件(data)
- *.MYI索引文件(index)
设置数据库表字符集编码
default chatset=utf8
不设置的话,会是mysql的默认字符集编码(不支持中文)
修改删除表(字段,名称之类)
-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 旧表名
ALTER TABLE student RENAME AS student1
ALTER TABLE student1 RENAME AS student
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher ADD age int(11)
-- 修改表的字段(重命名。修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE teacher MODIFY age VARCHAR(11) -- MODIFY修改约束,但无法重命名
-- ALTER TABLE 表名 CHANGE 旧表名 新表名 列属性
ALTER TABLE teacher CHANGE age age1 INT(11) -- CHANGE既可以修改约束,也可以重命名
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher DROP age1
-- 删除表
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS teacher
注意:
- 创建表、或是数据库,最好加上不存在判断——if not exists
- 删除表、或是数据库,最好加上存在判断——if exists
- 所有的字段名、表名、等加上``包括起来,避免奇奇怪怪的错误
- 注释:--空格 或 /**/
- sql关键字大小写不敏感,可以写小写,好识别
外键(了解)
-- 创建表的时候没有外键关系(给字段添加外键约束)
-- ALTER TABLE 表名
-- ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的字段) REFERENCES `外键所在的表名`(`对应字段名`)
ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
注意:
- 学生表引用年级表的id
- 主表就是被引用的表,从表引用别人主键的表(存在外键的表)
- 删除有外键关系存在的表的时候,必须先删除引用别人的表(从表),再删除被引用的表(zhu'bia)
- 删除一个年级表,如果这个年级表的主键在另一张学生表作为外键存在,就会报错,因为这张年级表被另外一张学生表引用,要先删除引用的学生表,才能删除年级表
- 以上外键操作都是物理外键,属于数据库级别的外键,外键太多,就是表之间的耦合度高,一旦数据表多就会很乱。(通过联表查询就是属于逻辑外键去实现多张表的关联)
- 使用navicat可视化软件,查看表结构只能点击设计表,或者使用查询(desc 表名)
DML增删改(Data Modify Language数据库修改语言)
增(insert)
-- 插入语句(添加)
-- INSERT INTO 表名([字段名1],[字段名2])
VALUES('字段1值','字段2值','值3'),('字段1值','字段2值','字段3值'),...
INSERT INTO `grade`(`gradeid`,`gradename`)
VALUES('1','张三'),('2','张三'),('3','张三'),('5','张三')
注意:
- 字段名可以不写,但是每个值要与字段一一对应
- 主键id字段可以不写,插入其它字段值,id会自动赋值一个自增的数
- 插入多条数据,之间用逗号隔开,最后一条不用逗号
改(update)
-- UPDATE 表名 SET 字段名=值 WHERE 条件
UPDATE `grade` SET `gradename`='大三' WHERE `gradename`='张三'
条件逻辑运算符
注意:
- 不加where条件的话,会修改表中改字段的所有数据
- 可以同时修改多个字段的值
- value,可以是具体一个值,也可以是一个变量
删除(delete)
delete命令
注意
- 会删除满足条件的数据
- 没有条件就会删除所有数据
-- DELETE FROM 表名 WHERE 条件
DELETE FROM `grade` WHERE `gradeid`='2'
truncate命令
作用:完全清空一个数据表,但表的结构和索引值不会变
delete、truncate、drop、三种删除的区别(级别从高到低)
delete
- 语法:delete from 表名 where 条件
- 作用:只删除内容,不删除定义(表结构),也不释放内存空间
- 应用场景:删除某些内容
- 注意:
- 可以用这个方法删除整张表的内容(没有where条件的情况下默认删除所有内容),且因为存在外键而无法删除的情况(但是这样很危险,删除内容都不知道)
- 自增计数会保留
- 如果是innodb存储引擎的表,删除完之后,重启数据库,自增列会清零(并从1开启),因为这里数据是存储在内存中,断电就没有了
- 如果是MyISAM存储引擎,还会保留自增(因为是存储在文件中,不会丢失)
truncate
- 语法:truncate table 表名
- 作用:删除内容,释放内存空间,但不删除定义(表结构)
- 应用场景:删除整张表,释放内存,但是保留表结构,
- 注意:
- 如果有外键关联无法删除,作为主表,被其它表引用的情况下
- 重新设置自增列,计数器会清零
- 不会影响事务
drop
- 语法:
- ALTER TABLE 表名 DROP 字段名
- DROP TABLE IF EXIST