一、SQL介绍——基础
1.什么是SQL语句
-
SQL,Structured Query Language,中文结构化查询语言,是一种对关系数据库 数据定义和操作语言。
2.SQL分类
-
DDL DDL全拼Data Definition Language,数据定义语言 常用关键字:CREATE(创建)、ALTER(修改)、DROP(删除) 查看帮助 :?Data Definition 特点:增删改数据库的库、表、索引等。
-
DCL DCL全拼Data Control Language,数据控制语言,常用关键字:GRANT(用户授权)、REVOKE(权限回收)、COMMIT(提交)、 ROLLBACK(回滚)。 执行?Account Management可查相关帮助。
-
DML DML全拼Data Manipulation Language,数据操作语言 主要关键字为INSERT(增)、DELETE(删)、UPDATE(改),主要针对数据库 表里数据进行操作。 执行mysql> ? Data Manipulation可查相关帮助。
-
DQL DQL全拼Data Query Language,数据查询语言,作用是从表中获取数据。 相关常用保留字有WHERE、ORDER BY、GROUP BY和HAVING。
3.SQL语句实践
3.1 DDL语句管理数据库
-
默认创建数据库,默认字符集为utf8mb4
create databae rgq(数据库名);
-
制定数据库字符集和校对规则建库语法
create database rgq character set charset_name collate collation_name;
#例子:
create database A;
create database A default character set utf8 collate utf8_generl_ci;
-
查看字符集和校对规则
show charset; ##查看支持的字符集和校对规则
show collation; ##查看支持的校对规则
-
查看建库帮助
? create database
-
根据具体字符集和建库语句创建需要的库
create database rgq;
create set gbk collate gbk_chinese_ci; ###
-
查看数据库的常用命令(可help show获取帮助):
show databases; ##<==查询所有数据库
show databases like 'rgq'; #<==匹配rgq字符串的内容
show databases like 'rg%' ; #<==%为通配符,表示匹配以rg开头的所有内容。
use rgq ;##切换数据库 ==>相当于cd /rgq 进入到指定的数据库。
select database(); #查看当前用户所在库pwd.
-
修改数据库
#语法:
ALTER DATABASE [db_name]
CHARACTER SET charset_name
[COLLATE collation_name;]
#例子:修改字符集为utf8
mysql> alter database rgq charset utf8;
-
删除数据库
drop database rgq;
#注意
1.管理员谨慎使用drop命令。
2.业务用户中,禁止有drop等破坏元数据的权限。
3.数据库名不能用大写字母,不能是关键字,不能使数字开头。
4.创建数据库时应显示设置字符集,8.0可以忽略本条。
CREATE DATABASE rgq CHARACTER SET utf8mb4 COLLATE
utf8mb4_0900_ai_ci;
3.2 DDL语句之管理表
1.表数据类型
- 表的数据类型作用于列上的,用于控制存储数据的"格式"和规范。
a.整型
列类型 | 存储容量 | 正数数字范围 | 负数数字范围 | 说明 |
---|---|---|---|---|
tinyint(微小整型) | 1bytes | 0-255 | -128~127 | 最大3位数 |
int | 4bytes | 0-2^32-1 | -2^31~2^31-1 | 最大10位数 |
bigint | 8bytes | 0-2^64-1 | -2^63~2^63-1 | 最大20位数 |
b.浮点型(小数)
- 除了整数之外,还有浮点数,一般用于对于精度要求很高的业务,例如和钱有关的业务,例如:99.99。 可以使用放大倍数手法把浮点数变成整数存储,例如:99.99放大100倍,为9999,使用在缩小100倍。 提示:在数据库里以整数形式存储,提高读取效率.
c.字符类型
- char(10) 定长,浪费存储空间,作为条件列查询会更快。
- varchar(10) 变长,节省空间
d.时间类型
- 时间类型往往是数据表中的必备类型
列类型 | 存储容量 | 说明 |
---|---|---|
timestamp | 4字节 | 从1970-1-1到2038-1-19,秒数,存储用时间戳。 |
datatime | 8字节 | 从1000-1-1到9999-12-31,显示用时间格式。 |
PHP程序时间戳转成日期形式函数:date('Y-m-d H:i:s',$row['createtime'])
e.枚举类型
判断,选择:enum('N','Y')
性别:enum('F','M','N')
省份:固定多个简单值的内容。
2.表的约束属性
类型 | 说明 |
---|---|
PRIMARY KEY | 简写PK,设置于表主键列,非空且唯一,用于必填项且不能重复。 |
NOT NULL/NULL | 表示列的内容是否非空,空列不利于数据库优化。 |
UNIQUE KEY | 简写UK,表示列的内容唯一,#比如手机号,就不能重复。 |
FOREING KEY | 简写FK,表示表的外键,多个表之间关联用的 |
-
生产经验:
-
每张表务必设置一个主键列,建议是数字且自增,学号,身份证,订单编号。
-
表的每个列尽量设置非空,给默认值。
-
3.表的其它属性
类型 | 说明 |
---|---|
AUTO_INCREMENT [=] value | 设置主键自增长,一般为数字自增长,默认为1. |
COMMENT 'string' | 对列等信息进行注释 |
ENGINE [=] engine_name | 指定表存储引擎(MyQSL文件系统),默认为innodb |
CHARACTER SET charset_name | 指定表的字符集,默认为utf8mb4. |
COLLATE [=] collation_name | 指定表的校对规则 |
DEFAULT | 设定列的默认值,如果是时间列取当前时间。 |
unsigned | 无符号数字(非负数) |
4.建表
-
建库
create database A;
use A;
-
在A库创建表stu
create table <表名> (
<字段名1> <类型1> ,
<字段名2> <类型2> ,
…
<字段名n> <类型n>);
#例句
USE oldboy;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
#查看建表语句
mysql> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(64) NOT NULL COMMENT '姓名',
`age` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
`telnum` char(15) NOT NULL DEFAULT '0' COMMENT '手机号',
`state` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1存在,0不存在',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
1 row in set (0.00 sec)
-
查看表信息
#查看表信息
show tables; #或show tables from 库名;
#查看表结构
desc stu;
-
更改表名
方式1
rename table stu to test;
方式2
alter table test rename to stu;
-
增删改表字段
(1)添加列默认到所有列结尾,命令语法为:
alter table 表名 add 字段 类型 其他;
#1.在stu表中添加addr列,默认是最后一列
alter table stu add addr varchar(100) not null comment '地址';
#2.在sname列后插入dept列。使用after sname;
alter table stu add dept varchar(32) after sname;
#3.在首列插入qq列。使用first
alter table stu add qq varchar(15) first;
#4.若要同时添加以上两个字段,还可采用如下命令
alter table stu drop dept;
alter table stu drop qq;
alter table stu add dept varchar(32) after sname,add qq varchar(15) first;
#5.修改字段类型的命令如下:
alter table stu modify dept char(64); #<==将dept数据类型及长度改为char(64).
#6.删除列的命令如下
alter table stu drop qq;
alter table stu drop dept;
alter table stu drop addr;
-
删除表
drop table stu;
3.2 DML语句之管理表数据
建立A库,创建stu表的完成语句示例,表名为stu。
create database A;
USE A;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学
号',
sname VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性
别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
-
往表中插入数据使用INSERT关键字
#第一种:指定列插入
insert into stu(id,sname,age,gender,telnum) values(1,'A',28,'M','111');
#注意:数字不加引号,字符加引号
#第二种:按顺序插入,可以不指定列(重点)
insert into stu values(2,'b',25,'F','126');
#第三种:批量插入多行(节省IO),注意缩进。
INSERT INTO
stu
values
(3,'Jack',18,'M','189'),
(4,'Tim',35,'F','183');
#只插入部分列,需要按顺序指定列名,且其他列允许为空,不允许为空有默认值
INSERT INTO stu(id,sname) VALUES(2,'oldgril');
#通过分区插入数据
insert into table a partition(ds='{bizdate}') ## partition ==>表分区
select a.cons_no,
·············
-
修改表的数据--update
练习1:把名字为Tim的人的年龄改为29
update stu set age=29 where sname='Tim';
练习2:把id为2的行的名字改为girl
update stu set sname='girl' where id=2;
练习3:把手机号为189的行,性别改为N?
update stu set gender='N' where telnum='189';
4.修改数据导致的事故案例和解决方案 不加条件执行下面语句:
update stu set sname='oldboy';
恢复:mysql -uroot -poldboy123 oldboy
-
删除表的数据