SQL基础应用
MySQL内置的功能
- 连接数据库
-u
-p
-S
-h
-P
-e
<
本地登录：
mysql -uroot -p密码 -S /tmp/mysql.sock
远程登录：
mysql -u用户名 -p密码 -hMySQLIP地址 -P3306
免交互执行sql语句：
mysql -u用户名 -p密码 -e "show databases;"
恢复数据：
mysql -uroot -p123.com < /root/world.sql
- 内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql
\G 将数据竖起来显示
select user,host from mysql.user \G
source 恢复备份文件
将world.sql拖入/root目录
source /root/world.sql
SQL基础应用
- SQL介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
- SQL常用种类
DDL 数据定义语言 create drop alter
DCL 数据控制语言 grant revoke
DML 数据操作语言 insert delete update
DQL 数据查询语言 select
字符集 (charset)
相当于MySQL的密码本(编码表)
查看所有字符集：
show charset;
utf8 : 3个字节
utf8mb4 (建议): 4个字节,支持emoji
创建数据库，同时指定字符集：
create database hehe default charset utf8mb4;
修改默认字符集：
alter database hehe default charset utf8mb4;
排序规则（collation）
查看所有排序规则：
show collation;
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)
针对已有的数据库修改排序规则：
alter database hehe collate utf8mb4_bin;
数据类型介绍
- 数字
整数：
tinyint 0-255
int -2^32-2^32
浮点数：
float
- 字符串
char(100)：
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100)：
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间，会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
少于255个字符串长度,定长的列值,选择char
多于255字符长度,变长的字符串,可以选择varchar
enum 枚举数据类型,数据不能为数字
address enum('sz','sh','bj'.....)
1 2 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
DDL的应用
- 库定义规范
库名使用小写字符
库名不能以数字开头
不能是数据库内部的关键字
必须设置字符集和校对集.
- 库的定义
创建数据库：
create database zabbix charset utf8mb4 collate utf8mb4_bin;
查看库情况：
show databases;
show create database zabbix;
修改数据库字符集：
注意: 一定是从小往大了改,比如utf8--->utf8mb4
目标字符集一定是源字符集的严格超级
create database test;
show create database test;
alter database test charset utf8mb4 collate utf8mb4_bin;
show create database test;
删除数据库(不代表生产操作)：
drop database test;
- 表定义规范
表名小写字母,不能数字开头
不能是保留字符，使用和业务有关的表名
选择合适的数据类型及长度
每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
每个列设置注释
表必须设置存储引擎和字符集
主键列尽量是无关列数字列,最好是自增长
enum类型不要保存数字,只能是字符串类型
- 表的定义
列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,一般是配合数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列
注：默认自增列增量为1，初始值也为1，但可以用下列参数修改
auto_increment_increment=10 增量为10
auto_increment_offset=10 初始值为10
创建表：
use zabbix;
create table stu (
id int primary key not null auto_increment comment '学号',
sname varchar(255) not null comment '姓名',
age tinyint unsigned not null default 0 comment '年龄',
gender enum('m','f','n') not null default 'n' comment '性别',
intime datetime not null default now() comment '入学时间'
) engine innodb charset utf8mb4;
查询建表信息：
show tables; ——显示所有表
show create table stu; ——显示创表语法、存储引擎、字符集
desc stu; ——显示表的列结构
创建一个和stu表结构一样的表：
create table test like stu;
删表(不代表生产操作)：
drop table test;
修改：
在stu表中添加qq列：
alter table stu add qq varchar(20) not null comment 'qq号';
在sname后加微信列：
alter table stu add wechat varchar(64) not null unique comment ' 微信号' after sname;
在id列前加一个新列num：
alter table stu add num int not null unique comment '身份证' first;
desc stu;
把刚才添加的列都删掉(危险,不代表生产操作) ：
alter table stu drop num;
alter table stu drop qq;
alter table stu drop wechat;
desc stu;
修改sname数据类型的属性：
alter table stu modify sname varchar(64) not null comment '姓名';
将gender 改为 sex 数据类型改为 CHAR 类型：
alter table stu change gender sex char(4) not null comment '性别';
desc stu;
- DCL
创建用户并赋权：
grant all on hehe.* to zhangsan@'%' identified by '123.com';
撤销删除权限：
生产中屏蔽delete功能，使用revoke去除用户drop和delete权限
revoke drop,delete on hehe.* from zhangsan@'%';
查看权限：
show grants for zhangsan@'%';
查看用户：
select user,host from mysql.user;
- DML
insert：
desc stu;
偷懒方法：
insert stu values(1,'zs',18,'m',now());
规范方法：
insert into stu(id,sname,age,sex,intime) values(2,'ls',19,'f',now());
针对性的录入数据：
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条件)：
将所有的sname改为aaa：
update stu set sname='aaa';
select * from stu;
将id为6的sname改为bb：
update stu set sname='bb' where id=6;
select * from stu;
delete (一定要有where条件)：
删除指定id的行：
delete from stu where id=6;
select * from stu;
删除stu表：
delete from stu;
truncate和delete的区别：
都是删除表中行记录，但delete不会重置自增列，会生成日志
truncate不能删除某行，只会删除全部，不做日志，重置自增列。
truncate table stu;
语法练习
create database school default charset utf8mb4 collate utf8mb4_bin;
use school;
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;