mysql2308笔记

一、SQL

1、SQL（Structured Query Language）

结构化查询语言，用于访问和处理数据库的标准的计算机语言

2、语法特点

（1）SQL对关键字的大小写不敏感（windows）
（2）SQL语句可以单行或者多行书写，每行已分号结束
（3）SQL注释

#单行注释1，#后面没有空格
-- 单行注释2，--后面必须有空格


/*
多行注释
多行注释
多行注释
*/

3、数据库系统

Database Management System（DBMS）

（1）关系型数据库（RDBMS）

①Oracle
②MySQL：和mariadb
③SQL Server
④PostgreSQL
⑤SQLite

（2）非关系型数据库（NoSQL）

①Redis
②MongoDB
③ElasticSearch
④Cassandra
⑤Hbase

–库
—表
----数据

4、MySQL数据库基本操作-DDL

DDL（Data Defination Language）数据定义语言，包括对数据库的常用操作，对表结构的常用操作、修改表结构

（1）对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database [if not exists] mydb1 [charset = utf8]
切换（选择要操作的数据库）	use mydb1;
查看当前所在库	select database();
删除数据库	drop database [if exists] mydb1;
修改数据库编码	alter database mydb1 character set utf8

（2）对数据库的常用操作-创建表

创建表格式：

create table [if not exists] 表名(
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的一些设置]

create table emp(
	eid int(1) comment '员工编号',
	ename varchar(1) comment '员工姓名'
);

insert into emp VALUES
(-2147483648,'A'),
(21333,"B");

#查询emp表的所有数据
select * from emp;

注：
（1）常见的数据类型有：①数值类型；②日期和时间类型；③字符串类型
①数值类型

类型	大小	用途
TINYINT	1byte	小整数值
INT或INTEGER	4byte	大整数值
DOUBLE	8byte	浮点数值

tinyint范围：[0,255]
int范围：[-2147483648,2147483647]

②字符串类型

类型	大小	用途
CHAR	0-255byte	定长字符串
VARCHAR	0-65535byte	变长字符串

③日期类型

类型	大小	格式	用途
DATE	3byte	YYYY-MM-DD	日期值
DATATIMESTAMP	8byte	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4byte	YYYMMDDHHMMSS	混合日期和时间值，时间戳

（2）如果是字符串类型，会有宽度的限制，宽度填多少就代表字符串有多长；如果是数值类型，比如INT(1)，1不代表数据的长度，如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然显示出来

（3）对表结构的常用操作

功能	SQL
查看当前数据的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名

show tables;

show create table emp;

desc emp;

drop table emp;

（4）对表结构的常用操作-其他操作

①修改表添加列

语法格式：
alter table 表名 add 列名 类型(长度) [约束];

②修改表添加列

语法格式：
alter table 表名 change 旧列名 新列名 类型(长度) [约束];

③修改表删除列

语法格式：
alter table 表名 drop 列名;

④修改表名

语法格式：
rename table 表名 to 新表名;

create database if not exists mysql07;

create table if not exists student(
	sid int comment '学号',
	name varchar(20) comment '姓名',
	gender varchar(20) comment '性别',
	age int comment '年龄',
	birth date comment '出生日期',
	address varchar(20) comment '家庭地址'
);

#添加字段dept
alter table student add dept varchar(20);

#修改dept字段为department，并设置varchar(30)
alter table student change dept department varchar(30);

#删除department字段
alter table student drop department;

#将student表名改为stu
rename table student to stu;

5、MySQL数据库基本操作-DML

DML是指数据操作语言，Data Manipulation Language，用于对数据库表中数据记录进行更新、删除、插入等操作
关键字：insert（插入）、delete（删除）、update（更新）

（1）数据插入

语法格式：
#向表中指定某些列插入数据
insert into 表名 (列名1,列名2,列名3...) values (值1,值2,值3...);

#向表中所有列插入数据
insert into 表名 values (值1,值2,值3...);

#将表名stu改为student;
rename table stu to student;

#向student表中sid，name，gender，birth字段添加数据
insert into student(sid,name,gender,age,birth)
values (1001,'张三','男',20,'1994-12-01');

#向student表中所有字段添加数据
insert into student
values (1002,'赵敏','女',18,'1995-12-03','上海');

#向studnet表中所有字段添加多个数据
insert into student VALUES
(1003,'张三丰','男',88,'1918-08-13','北京'),
(1004,'张无忌','男',36,'1983-07-11','北京');

注：插入数据两种格式均可，SQL语句是以分号结尾，可以在一条insert语句中同时插入多个列表值，values只需要写一次，括号之间用逗号隔开

（2）数据修改

语法格式：

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

#将所有学生的地址改为重庆
update student set address = '重庆';

#将sid为1004的学生的地址修改为北京
update student set address = '北京' where sid = 1004;

insert into student values
(1005,'沙师弟','男',33,'1986-03-18','上海');

#将sid为1005学生的地址修改为北京，年龄修改为35，出生日期修改为1984-03-18
update student set address = '北京',age = 35,
birth = '1984-03-18' where sid = 1005;

（3）数据删除

语法格式：
delete from 表名 [where 条件];

truncate table 表名
或者
truncate 表名

#删除sid为1004的学生数据
delete from student where sid = 1004;

#删除student表所有的数据
delete from student;

insert into student VALUES
(1003,'张三丰','男',88,'1918-08-13','北京'),
(1004,'张无忌','男',36,'1983-07-11','北京');

#清空表数据
truncate table student;
truncate student;

注：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解将整个表删除，然后再创建该表；

6、MySql约束

何为约束，contraint，约束实际上就是表中数据的限制条件
作用：为了保证表中记录完整性和有效性，比如用户表
的有些列（手机号）不能为空，有些列（身份证号）不能重复

分类：
（1）主键约束（primary key） PK
（2）自增长约束（auto_increment）
（3）非空约束（not null）
（4）唯一性约束（unique）
（5）默认约束（default）
（6）零填充约束（zerofill）
（7）外键约束（foreign key） FK

（1）主键约束

可以加到某个列上，也可以加到多个列上

①概念：

a.主键约束相当于唯一约束+非空约束的组合，即主键约束的列不允许重复，也不允许出现空值
b.每个表中最多允许一个主键

②添加单列主键：

a.在定义字段的同时指定主键

语法格式：
create table 表名(
	...
	<字段名><数据类型> primary key
	...
)

create table emp1(
	eid int primary key comment '编号',
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '工资'
);

#Field 'eid' doesn't have a default value
#设置了主键，该字段必须要给值
insert into emp1 (name,dept_id,salary) VALUES
('张三',8801,3600);

insert into emp1 VALUES
(60051,'张三丰',5003,5000);

#[Err] 1062 - Duplicate entry '60051' for key 'PRIMARY'
#eid不能重复
insert into emp1 values 
(60051,'王强',5005,8000.0);

b.在定义完字段之后指定主键

create table 表名(
	...
	[constraint <约束名>] primary key [字段名]
);

create table emp2 (
	eid int comment '编号',
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '薪资',
	constraint pk1 primary key(eid)
);

insert into emp2 values
(1001,'张安',10,6000),(1002,'杰森',40,8000);

③添加多列主键（联合主键）

联合主键：一张表中多个字段组成的，也是一个主键

语法格式：
create table 表名(
	...
	primary key (字段1,字段2,...,字段n)
);

create table emp3(
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '薪资',
	primary key (name,dept_id)
);

insert into emp3 values
('111',10,5000),
('112',10,6000),
('113',10,7000),
('111',20,8000),
('112',30,9000);

insert into emp3 values
('114',10,5000);

insert into emp3 values
('113',80,6000);

多个键作为主键时，其中的值不能完全相同，允许其中几个值相同，但是不能出现NULL即空值。

④通过修改表结构添加主键

语法格式：
alter table <表名> add primary key (字段列表);

create table emp4 (
	eid int,
	name varchar(20),	
	dept_id int,
	salary double
);

#给emp4添加主键
alter table emp4 add primary key (eid);

⑤删除主键

语法格式：
alter table <表名> drop primary key;

#删除emp1表中主键
alter table emp1 drop primary key;

#删除emp3表中的联合主键
alter table emp3 drop primary key;

#删除emp2表中的主键
alter table emp2 drop primary key;

（2）自增长约束

自增长约束，当字段设置为自增长约束以后，在插入数据时，不需要用户输入数据，而由数据库系统根据定义自动赋值，每增加一条记录，该字段就会以相同的步长进行增长，一般和主键共同使用

语法格式：
create table 表名(
	...
	字段名 数据类型 auto_increment
);

#创建madb1数据库
create database if not exists madb1 charset utf8;

create table t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

insert into t_user1 values
(NULL,'张三');

insert into t_user1 (name) values ('李四');

①特点

a.auto_increment的初始值1，每自增一条记录，字段值自动加1
b.一个表中只能有一个字段使用auto_increment
c.auto_increment约束的字段必须具备NOT NULL属性
d.支持的类型（TINYINT、SMALLINT、INT、BIGINT）等
e.如果最大值达到上限，auto_increment就会失效

②指定自增字段的初始值

方式1：创建表时指定
语法格式：
create table 表名(
	... primary key auto_increment,
	...
) auto_increment = 10;

create table t_user2(
	id int primary key auto_increment,
	name varchar(20)
) auto_increment = 100;

insert into t_user2 VALUES
(NULL,'aaa'),
(NULL,'bbb');

方式2：创建表之后指定
语法格式：
create table 表名(
	... primary key auto_increment,
	...
);

alter table 表名 auto_increment = 值;

create table t_user3(
	id int primary key auto_increment,
	name varchar(20)
);

insert into t_user3 VALUES
(NULL,'aac'),
(NULL,'add');

alter table t_user3 auto_increment = 20;

insert into t_user3 values 
(NULL,'ace');

alter table t_user3 auto_increment = 2147483647;

insert into t_user3 values
(NULL,'ade');

#[Err] 167 - Out of range value for column 'id' at row 1
#超过最大值，就不允许插入
insert into t_user3 VALUES
(NULL,'ddd');

③delete和truncate区别

delete数据之后自动增长是从断点处开始
truncate数据之后自动增长从默认起始值开始

create table t_user3(
	id int primary key auto_increment,
	name varchar(20)
);

alter table t_user3 auto_increment = 10;

insert into t_user3 VALUES
(NULL,'aac'),
(NULL,'add');

#delete操作
delete from t_user3;

insert into t_user3 VALUES
(NULL,'aae'),
(NULL,'aaf'),
(NULL,'aag');

#truncate操作
truncate t_user3;

insert into t_user3 VALUES
(NULL,'aac'),
(null,'aad');

create table t_user4(
	name varchar(10)
);

alter table t_user4 charset = latin1;
alter table t_user4 change name name varchar(10) charset latin1;

#delete操作
delete from t_user4;

insert into t_user4 values ('zhangsan');

#truncate操作
truncate t_user4;

insert into t_user4 values ('张三');