第三章：sql基础应用（增删改查）
1.1 连接数据库
-u
-p
-S
-h
-P
-e
<
例子:
- mysql -uroot -p -S /tmp/mysql.sock
- mysql -uroot -p -h192.168.8.5 -P3306
- -e 免交互执行sql语句
mysql -uroot -p -e "show databases"
4. < 恢复数据
mysql -uroot -p123 < /root/world.sql
1.2 内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql
\G 将数据竖起来显示
mysql>select user,host from mysql.user \G
source 恢复备份文件
mysql>source world.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
2.SQL语句的基础应用
2.1 SQL介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
2.2 SQL常用种类 *****
DDL ：数据定义语言（create drop alter）创建、删除、修改
DCL ：数据控制语言（grant revoke） 授权和回收
DML：数据操作语言（insert delete update）增删改
DQL ：数据查询语言（select） 查
3.字符集 (charset)
相当于MySQL的密码本(编码表)
show charset;
utf8 : 3个字节
utf8mb4 (建议): 4个字节,支持emoji表情
ps:
创建数据库，同时指定字符集：
create database hehe default charset utf8mb4;
修改默认字符集：
alter database hehe default charset utf8mb4;
排序规则: collation
mysql> show collation;
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)
ps: 针对已有的数据库修改排序规则
alter database hehe collate utf8mb4_bin;
5.数据类型介绍
5.1 数字（编号、年龄、工资、报价、性别)
整数
tinyint 0-255 //年龄
int -2^32-2^32
浮点数 float（小数）金额
5.2 字符串 * （姓名、籍贯、产品名称、地址、订单信息、产品介绍）
char(100)
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100)
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
如何选择这两个数据类型?
少于255个字符串长度,定长的列值,选择char
多于255字符长度,变长的字符串,可以选择varchar
enum 枚举数据类型,数据不能为数字
address enum('sz','sh','bj'.....)
1 2 3
悬念,以上数据类型可能会影响到索引的性能
5.3 时间
（出生日期、创建日期、保质期、生产日期、付款日期、保单日期......)
datetime（少）
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
timestamp(多）
范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
6.DDL的应用
6.1 库的定义
创建数据库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
查看库情况
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;
删除数据库(不代表生产操作)
DROP DATABASE test;
修改数据库字符集
注意: 一定是从小往大了改,比如utf8--->utf8mb4.
目标字符集一定是源字符集的严格超级.
CREATE DATABASE test;
SHOW CREATE DATABASE test;
ALTER DATABASE test CHARSET utf8mb4 collate utf8mb4_bin;
6.2 关于库定义规范 *****
库名使用小写字符
库名不能以数字开头
不能是数据库内部的关键字
必须设置字符集和校对集
6.3 DDL-表定义
（表是由行和列组成的），每一列称为字段，行就是记录（数据）
建表
表名,列名,列属性,表属性
列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一. （编号、身份证、手机.....)
NOT NULL : 非空约束,不允许空值 ( 必填项）
UNIQUE KEY : 唯一键约束,不允许重复值 （编号、身份证、手机.....但是可以为空）
DEFAULT : 一般配合 NOT NULL 一起使用 （默认值）
UNSIGNED : 无符号,一般是配合数字列,非负数 （年龄、体重、身高、点赞数，评论数.....)
COMMENT : 注释
AUTO_INCREMENT :自增长的列
进入数据库test
use test
然后在创建表：
create table stu(
-> id int primary key auto_increment comment "学号",
-> snmae varchar(20) not null comment "姓名",
-> age tinyint unsigned not null default 0 comment "年龄",
-> gender enum('m','n') not null default 'n' comment "性别",
-> intime datetime not null default now() comment "入学时间"
-> ) charset utf8mb4;
mysql>show create table stu;
1 row in set (0.01 sec)
注：默认自增列增量为1，初始值也为1，但可以用下列参数修改。
auto_increment_increment=10 增量为10
auto_increment_offset=10 初始值为10
建表规范 *****
表名小写字母,不能数字开头,
不能是保留字符,使用和业务有关的表名
选择合适的数据类型及长度
每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
每个列设置注释
表必须设置存储引擎和字符集
主键列尽量是无关列数字列,最好是自增长
enum类型不要保存数字,只能是字符串类型
查询建表信息
SHOW TABLES; #显示所有表
SHOW CREATE TABLE stu; #显示创表语法、存储引擎、字符集
DESC stu; #显示表的列结构
创建一个表结构一样的表
CREATE TABLE test LIKE stu;
删表(不代表生产操作)
DROP TABLE test;
修改
在stu表中添加qq列 *****
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
pt-osc(了解)
MySQL原生的Online DDL还是有很多限制的，还是会遇到data meta lock的问题等诸多不便，通过pt-osc在线执行DDL。
这是测试自增列是否生效
insert into stu(sname,age,gender,intime) values('张津瑞','18','m','2024-7-10');
insert into stu(sname,age,gender,intime) values('张津瑞','18','m','2024-7-10');
工作原理：
创建一个和源表一样表结构的新表
在新表执行DDL语句（空表嘛，所以。。。）
在源表创建三个触发器分别对应insert、update、delete操作
从源表拷贝数据到新表，拷贝过程中源表通过触发器把新的DML操作更新到新表中
rename源表到old表中，把新表rename为源表，默认最后删除源表
在sname后加微信列 ***
ALTER TABLE stu ADD wechat VARCHAR(64) UNIQUE COMMENT '微信号' AFTER sname;
ALTER TABLE stu ADD wecha varchar(64) unique comment '微信号' after sname;
在id列前加一个新列num ***
ALTER TABLE stu ADD num INT UNIQUE COMMENT '身份证' FIRST ;
DESC stu;
把刚才添加的列都删掉(危险,不代表生产操作) ***
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP wechat;
DESC stu; 查看表的结构
修改sname数据类型的属性 ***
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
MODIFY:修改数值类型和长度
将gender 改为 sex 数据类型改为 CHAR 类型 ***
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
CHANGE：修改列名：change
6.4 DCL
创建用户并赋权：
grant all on hehe.* to zhangsan@'%' identified by '123.com';
撤销删除权限：
revoke drop,delete on hehe.* from zhangsan@'%';
查看权限：
show grants for zhangsan@'%';
查看用户：
select user,host from mysql.user;
针对性的录入数据
INSERT INTO stu(sname,age,sex)VALUES ('w5',11,'m');
一次性录入多行
INSERT INTO stu(sname,age,sex)
VALUES
('aa',11,'m'),
('bb',12,'f'),
('cc',13,'m');
update(一定要加where条件)
mysql>update stu set sname='zhansan' where id=1
delete (一定要有where条件)
DELETE FROM stu; 删除表中全部的数据
DELETE FROM stu WHERE id=2; 只删除id为2的一行
truncate和delete：
都是删除表中行记录
但delete不会重置自增列，会生成日志；
truncate不能删除某行，只会删除全部，不做日志，重置自增列。
truncate table stu;
生产中屏蔽delete功能
使用revoke去除用户drop和delete权限。
6.6 作业
create database lkw default charset utf8mb4 collate utf8mb4_bin;
use lkw
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
create table student (
sno int not null primary key auto_increment comment "学号",
sname varchar(20) not null comment "学生姓名",
sage int not null comment "学生年龄",
ssex char(4) not null comment "学生性别");
teacher ：教师表
tno： 教师编号
tname：教师名字
create table teacher (
tno int not null,
tname varchar(20)
);
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
create table course (
cno int not null,
cname varchar(20) not null,
tno int not null
);
sc ：成绩表
sno： 学号
cno： 课程编号
score：成绩
create table sc (
sno int not null,
cno int not null,
score int not null
);
添加数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');
INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
INSERT INTO student(sname,sage,ssex)
VALUES
('maliu',20,'m'),
('zhuqi',20,'f'),
('sunjiu',25,'m');
INSERT INTO teacher(tno,tname) VALUES
(101,'laowu'),
(102,'laoxia'),
(103,'laoli');
DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
然后查询
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
