DML
插入
一、方式一
语法:
insert into 表名(字段名,...) values(值,...);
INSERT INTO admin(`username`,`password`) VALUES('1','2')
特点:
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
二、方式二
语法:
insert into 表名 set 字段=值,字段=值,...;
INSERT INTO admin SET `username` = 1,`password`=2
两种方式 的区别:
1.方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,..)】 values(值,..),(值,...),...;
INSERT INTO admin(`username`,`password`) VALUES('1','2'),('2','3'),('5','3');
2.方式一支持子查询,语法如下:
INSERT INTO admin(`username`,`password`) SELECT `username` , `password` FROM `admin`;
修改
一、修改单表的记录 ★
语法:
update 表名 set 字段=值,字段=值 【where 筛选条件】;
UPDATE admin SET username = 'lang' WHERE id > 3
二、修改多表的记录【补充】
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
UPDATE boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
SET b.`phone` = '114'
WHERE bo.`boyName` = '张无忌'
删除
方式一:使用delete
一、删除单表的记录★
语法:delete from 表名 【where 筛选条件】【limit 条目数】
二、级联删除[补充]
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二:使用truncate
语法:
truncate table 表名
两种方式的区别【面试题】★
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
DDL
库的管理
一、创建库
create database 【if not exists】 库名【 character set 字符集名】;
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8
二、修改库的字符集
alter database 库名 character set 字符集名;
ALTER DATABASE books CHARACTER SET utf8;
三、删除库
drop database 【if exists】 库名;
表的管理
一、创建表 ★
create table 【if not exists】 表名(
字段名 字段类型 【长度 ,约束】,
字段名 字段类型 【长度 ,约束】,
。。。
字段名 字段类型 【长度 ,约束】
)
二、修改表
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
三、删除表
drop table【if exists】 表名;
四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
练习:
# 创建表 dept1
CREATE TABLE IF NOT EXISTS dept1(
t_id INT(7),
t_name VARCHAR(20)
)
# 将表departments中的数据插入新表dept2中
CREATE TABLE dept2
SELECT `department_id`,`department_name` FROM `myemployees`.`departments`;
# 创建一个emp表 后 将last_name的长度增加到50
CREATE TABLE IF NOT EXISTS emp (
id INT(7),
first_name VARCHAR(20),
last_name VARCHAR(20),
dept_id INT(7)
)
ALTER TABLE emp MODIFY COLUMN last_name VARCHAR(50);
# 根据表employees创建employees2 只需要结构
CREATE TABLE employees2 LIKE myemployees.`employees`;
# 删除表emp
DROP TABLE IF EXISTS emp;
# 将表employees2重命名为emp
ALTER TABLE employees2 RENAME TO emp
# 在表dept1和emp中添加新列test_column
ALTER TABLE dept1 ADD COLUMN text_column INT;
ALTER TABLE emp ADD COLUMN text_column VARCHAR(10);
# 直接删除表emp中的列dept_id
ALTER TABLE emp DROP COLUMN IF EXISTS dept_id
# 修改emp中的text_column列名为 user_id
ALTER TABLE emp CHANGE COLUMN text_column user_id BIGINT;
数据类型
一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint 默认都为有符号
1 2 3 4 8
特点:
①都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
②如果超出了范围,会报out or range异常,插入临界值
③长度可以不指定,默认会有一个长度
长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
CREATE TABLE t_int(
t1 INT,
t2 INT UNSIGNED # 设置为无符号 ,如果为负数 则为0
);
2、浮点型
定点数:decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8
特点:
①M代表整数部位+小数部位的总长度,D代表小数点后显示的位数 超出会四舍五入
②如果超出范围,则报out or range异常,并且插入临界值
③M和D都可以省略,但对于定点数,M默认为10,D默认为0
④如果精度要求较高,则优先考虑使用定点数
mysql无符号和有符号的区别
无符号unsigned 表示设置的的数据为0或者正数;
有符号则可以是负数 -;
CREATE TABLE t_float(
t1 FLOAT(5,2), # float(M,D) M 代表一共的位数 D代表 小数的位数 这里小数是两位,所以小数点前就只剩三位了
t2 DOUBLE(5,2),
t3 DECIMAL(5,2)
);
二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1
varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
CREATE TABLE t_enum(
t1 ENUM('a','b','c')
)
INSERT INTO t_enum VALUES('a')
CREATE TABLE t_set(
t1 SET('a','b','c','d')
);
INSERT INTO t_set VALUES('a,b')
三、日期型
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间
约束
含义 : 一种限制,用于限制表中的数据。为了保证表中的数据的可靠性。
分类: 六大约束:
not null
非空 用于保证该字段的值不能为空。default
默认值,用于给该字段设置默认值。primary key
主键 用于保证该字段的值具有唯一性 并且具有非空性unique
唯一 用于保证该字段的值具有唯一性 可以为空foreign key
外键 用于限制两个表的关系的
分类:约束类型:
- 列级约束:
其中 外键不支持列级约束
CREATE TABLE stuinfo(
id INT PRIMARY KEY ,#主键
stuName VARCHAR(10) NOT NULL , #非空
gender CHAR(1) NOT NULL,
seat INT UNIQUE,#唯一元素
age INT DEFAULT 18 #默认
);
- 表级约束
其中
default
not null
不支持表级约束
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(10) NOT NULL , #非空
gender CHAR(1) NOT NULL,
seat INT,#唯一元素
age INT,#默认
majorId INT ,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键
#【CONSTRAINT name】可省略
);
通用写法:
# 通用写法
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(10) NOT NULL , #非空
enrollmentDate DATETIME DEFAULT NOW(),#默认
seatNo INT UNIQUE, #唯一
majorNameId INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorNameId) REFERENCES major(id) #外键
);
联合主键/联合唯一:
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(10) NOT NULL ,
CONSTRAINT pk PRIMARY KEY(id,stuName)#联合主键
);
约束修改
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(10)
);
# 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
# 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20);
# 添加外键
ALTER TABLE stuinfo ADD COLUMN majorid INT ;
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
# 外键 级联删除
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id) on delete cascade;
# 外键 级联置空
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id) on delete set null;
# 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY ;
# 删除唯一
ALTER TABLE stuinfo DROP INDEX name
# 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;
标识列
又叫自增长列 ,可以 不用手动的插入值,系统提供默认的序列值.
每张表只能存在一个,而且要与key搭配使用
CREATE TABLE stuinfo(
id INT PRIMARY KEY AUTO_INCREMENT,
stuName VARCHAR(10)
);
- 查看自动增长
SHOW VARIABLES LIKE '%auto_increment%';
给已存在的列添加自增
alter table tablename modify id int(11) auto_increment;
mysql中不支持设置起始值,但是可以设置步长
SET auto_increment_increment = 3;
- 修改标识列
#设置标识列
ALTER TABLE table_ MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;