初认sql

操作数据库

mysql不区分大小写，建议小写

基本的命令行操作

1. 连接数据库（进入数据库）

mysql -uroot -p+密码

或

mysql -uroot -p (回车) 再输入密码

2. 创建数据库

create database 数据库名;

3. 查看数据库

show databases;（查看所有的数据库）

4. 使用数据库

use 数据库名(eg.use school)

注意：如果你的表名或者字段名是一个特殊字符，就需要带``(tab键上方的符号)

5.退出链接

exit;

6.注释

单行注释 --+空格

多行注释 /* */

数据库的列类型

数值

tinyint<smallint<mediumint<int<bigint<float<double<decimal(金融计算)

字符串

char<varchar<tinytext(微型文本)<text(大文本)

时间日期

1. date YYYY-MM-DD 日期格式
2. time HH:mm:ss 时间格式
3. datetime YYYY-MM-DD HH:mm:ss 常用
4. timestamp 时间戳 1970.1.1到现在的毫秒数
5. year 年份表达

null

• 没有值，未知
• 注意：不要使用null进行运算，结果为null

数据库的字段属性（！！）

Unsigned

• 无符号的整数
• 声明了该列不能为负数

zerofill

• 0填充
• 不足的位数，使用0来填充

eg. int(3) 5 ==> 005

自增 auto_increment

• 默认自动在上一条记录的基础上+1
• 通常用来设计唯一的主键（index），必须是整数类型
• 可以自定义设计主键的起始值和步长（高级）

非空 NULL/NOT NULL

• 若设置为 not null ，不给它赋值就会报错！
• 如果不填写值，默认就是null

默认 default

• 设置默认的值！如果不指定该列的值，则会有默认的值

扩展

(做项目)每一个表都必须存在以下的五个字段

1. id 主键
2. version 乐观锁
3. is_delete 伪删除
4. gmt_create 创造时间
5. gmt_update 修改时间

创建数据库表

格式：

create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
    ......
`字段名` 列类型 [属性] [索引] [注释],
    primary key(`字段名`)
)[表类型][字符集设置][注释]

例子：

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
`sex` VARCHAR(20) NOT NULL DEFAULT'女' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'生日',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

• auto_increment 自增 comment 注释 default 默认
• 使用英文的括号，表的名称和字段尽量用``括起来
• 所有语句后加, 最后一个语句不用

查看语句和表结构（逆向操作）

SHOW CREATE DATABASE school
--查看创建数据库的语句
SHOW CREATE TABLE student
--查看student数据表的定义语句(创表的过程)
DESC student
--显示表的结构

数据表的类型

关于数据库引擎

INNODB 现在默认使用

MYISAM 早些年使用的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DHtAjjjY-1686218796672)(C:\Users\邱\AppData\Roaming\Typora\typora-user-images\image-20230521150304488.png)]

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件就对应一个数据库，本质还是文件的存储！

Mysql引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8

• 不设置的话，会使mysql使用默认的字符集编码（不支持中文）
• MYSQL的默认编码是Latin1，不支持中文
• 也可以在my.ini中配置默认的编码（不建议）

修改和删除数据表字段

修改

ALTER TABLE teacher RENAME AS teacher1
-- 修改表名 alter table 旧表名 rename as 新表名

ALTER TABLE teacher1 ADD age INT(11)
-- 添加表的字段 alter table 表名 add 字段名 [列属性]

-- 修改表的字段（重命名和修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11)
-- 修改约束:alter table 表名 modify 字段名 [列属性]
ALTER TABLE teacher1 CHANGE age age1 INT(5)
-- 字段重命名:alter table 表名 change 旧字段名 新字段名 [列属性]

• change：用来字段重命名
• modify：不用来字段重命名，只能修改字段类型

删除

ALTER TABLE teacher1 DROP age1
-- 删除表的字段 alter table 表名 drop 字段名

DROP TABLE IF EXISTS teacher1
-- 删除表 drop table [if exists] 表名

数据库级别的外键

建表时无外键关系

（表已建完添加外键）

-- 1.向主表添加主表与外表相同的字段名（有共同之处两表才能相连）
ALTER TABLE student 
ADD `gradeid` INT(10) NOT NULL COMMENT'年纪id'

-- 2.添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UBHkYUgo-1686218796673)(C:\Users\邱\AppData\Roaming\Typora\typora-user-images\image-20230522094526173.png)]

/*总结
alter table `student`
add constraint `FK_xxxxx` （约束名 FK_默认要加的 后加名）
foreign key 作为外键的列
references 引用外键所在的表（`字段名`）
*/

建表时添加外键

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年纪id',
`gradename` VARCHAR(50) NOT NULL COMMENT'年纪名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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(20) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `gradeid` INT(10) NOT NULL COMMENT'年纪id',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  key `FK_gradeid`(`gradeid`),
  constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

• 以上操作都是物理外键，数据库级别的外键，不建议使用（数据库过多造成困扰，了解即可）
• 最佳实践：
  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 若想使用多张表的数据，想使用外键让程序去实现

DML语言（!!!）

插入insert

一般写插入语句，我们一定要数据和字段一一对应!!!!

INSERT INTO `grade`(`gradename`) VALUES ('大三')
-- 插入多个字段
INSERT INTO `grade` (`gradename`) VALUES('大一'),('大二')

INSERT INTO `student01` (`name`) VALUES ('李四')
INSERT INTO `student01` (`name`,`pwd`,`sex`) VALUES ('张三','123212','男'),('赵六','234165','女')

INSERT INTO `student01` VALUES(4,'王五','223344','男','1999-12-26',3,'长沙','@qq.com')
INSERT INTO `student01` VALUES(5,'叶问','223323','男','1990-11-23',NULL,NULL,NULL)

语法：

insert into 表名 [字段名1,字段名2,字段名3...] values ('值1','值2','值3',...)
-- 插入多个字段：
insert into 表名 [字段名1,字段名2] values ('值1','值2'),('值1','值2'),(...)

注意：

1. 字段可以省略，但后面的值必须要一一对应，一个都不能少
2. 可以同时插入多条数据，values后面的值需要用英文逗号隔开即可 values(),(),(),…

修改update

-- 语法：
update 表名 set `列名`=new value[,列名`=new value...] where 条件(定位某行)

-- eg:
UPDATE `student01` SET `name`='trix'
-- 要带筛选条件(where),否则会将全表进行修改
UPDATE `student01` SET `name`='paipai' WHERE id = 1
-- 修改多个属性，用英文逗号隔开
UPDATE `student01` SET `name`='zix',`address`='344312321@qq.com' WHERE id =1

-- 操作符
-- <>和!=都为不等于 以此类推 = < > <= =>
UPDATE `student01` SET `name`='paipai' WHERE id<>3
UPDATE `student01` SET `name`='zix' WHERE id <=2
-- Between..and...表范围
UPDATE `student01` SET `name`='zix' WHERE id BETWEEN 2 AND 3
-- AND 相当于&& 我和你 (通过多个条件定位数据)
UPDATE `student01` SET `name`='qiuqiu' WHERE id=4 AND `gradeid`=3
-- OR 相当于|| 我或你 (通过多个条件定位数据)
UPDATE `student01` SET `name`='adawa' WHERE `pwd`=123212 OR `sex`='女'

-- value也可以是一个变量
UPDATE `student01` SET `birthday`=CURRENT_TIME WHERE `name`= 'zix' AND `pwd`=123456

注意：

1. 筛选的条件如果没有指定，则会修改所有的列
2. value 可以是一个变量，也可以是一个具体的值
3. 多个设置的属性之间用英文逗号隔开

删除delete

-- 语法:delete from 表名 [where 条件]

-- 删除指定数据
DELETE FROM `student01` WHERE `id`=3

-- 删除全部数据
DELETE FROM `student01`

-- 完全清空一个数据库（表的结构和索引约束不会变）
TRUNCATE `student01`

delete与truncate的区别：

• 相同点：都能删除数据，并且都不会删除表结构
• 不同点
  • truncate 会重新设置自增列 计数器会归零（清空后自增从1开始）
  • truncate 不会影响事务

了解：delete删除的问题，（重启数据库现象）

• InnoDB 自增列会从1开始（存在内存中的，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）