SQL基础应用,基本操作
SQL基础应用,基本操作
欢迎来到 来到大浪涛天的博客 !
一、SQL基础应用
1. mysql内置的功能
1-1. 连接数据库
-u: 用户
-p:密码
-S:后面接sock文件的路径
-h:接主机名或者ip地址,添加-h的时候一定要在数据库里做好授权远程登陆的用户
-P:接MySQL的端口号,使用-P前面一定要加-h
-e:免交互式登陆,-e后面可以接MySQL的命令,而不需要登陆数据库里面
<:恢复数据使用,可以在shell下将sql文件导入数据库
例如:
1. mysql -uroot -p -S /tmp/mysql.sock
2. mysql -uroot -p -h10.0.0.51 -P3306
3. -e 免交互执行sql语句
[root@db01 ~]# mysql -uroot -p -e "show databases;"
4. < 恢复数据
[root@db01 ~]# mysql -uroot -p123 /root/world.sql
1-2. MySQL内置命令
- help 打印mysql帮助
- \c ctrl+c 结束上个命令运行
- \q quit; exit; ctrl+d 退出mysql
- \G 将数据竖起来显示
- source 恢复备份文件
mysql> source /root/t100w.sql;
Query OK, 0 rows affected (0.02 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 数据定义语言
- DCL 数据控制语言
- DML 数据操作语言
- DQL 数据查询语言
2-3. SQL引入-数据库的逻辑结构
- 库
- 库名字
- 库属性:字符集,排序规则
- 表
- 表名
- 表属性:存储引擎类型,字符集,排序规则
- 列名
- 列属性:数据类型,约束,其他属性
- 数据行
2-4. 字符集 (charset)
相当于MySQL的密码本(编码表)
show charset;
utf8 : 3个字节
utf8mb4 (建议): 4个字节,支持emoji
2-5. 排序规则: collation
mysql> show collation;
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)
3. 数据类型介绍
3-1. 数字
整数
- tinyint (0-255或者-127-128)
- int (-231-231 只能存10位数)
- 浮点数,略
3-2. 字符串
- char(100)
- 定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
- varchar(100)
- 变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
- 会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
- 如何选择这两个数据类型?
- 少于255个字符串长度,定长的列值,选择char
- 多于255字符长度,变长的字符串,可以选择varchar
3-3. enum 枚举数据类型
address enum('sz','sh','bj'.....)
1 2 3
以上数据类型可能会影响到索引的性能
3-4. 时间
- 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。
3-5. DDL的应用
库的定义:
- 创建test01库,要求utf8,区分大小写
create database test01 charset utf8mb4 collate utf8mb4_bin;
- 查看数据库
show databases;
- 查看创建数据库的具体情况
show create database test01;
- 删除数据库
drop database zabbix;
- 字符集修改,要注意一定要从小往大改,比如只能utf8改为utf8mb4,而不能utf8mb4改为utf8,不然会出现乱码
show create database wordpress;
alter database wordpress charset utf8mb4;
show create database wordpress;
- 关于库定义规范
- 库名使用小写字符
- 库名不能以数字开头
- 库名不能是MySQL内部的关键字
- 建库必须设置字符集
3-6. DDL-表定义
- 建表
- 表名,列名,列属性,表属性
- 列属性
- PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
- NOT NULL : 非空约束,不允许空值
- UNIQUE KEY : 唯一键约束,不允许重复值
- DEFAULT : 一般配合 NOT NULL 一起使用.
- UNSIGNED : 无符号,一般是配合数字列,非负数
- COMMENT : 注释
- AUTO_INCREMENT : 自增长的列
建表事例:
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;
-
建表规范 *****
--- 1. 表名小写字母,不能数字开头,
--- 2. 不能是保留字符,使用和业务有关的表名
--- 3. 选择合适的数据类型及长度
--- 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
--- 5. 没个列设置注释
--- 6. 表必须设置存储引擎和字符集
--- 7. 主键列尽量是无关列数字列,最好是自增长
--- 8. enum类型不要保存数字,只能是字符串类型 -
查询建表信息
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;
- 创建一个表结构一样的表
CREATE TABLE test LIKE stu;
- 删表(不代表生产操作)
DROP TABLE test;
- 修改表中的列, 注意在alter语句中,在线操作的话均会进行锁表,会影响业务,如果要影响最小最好是凌晨操作,因为锁表时间是按数据量的多少来确认锁表多久,如果要减缓锁表可以用第三方工具如pt-osc等
--- 在stu表中添加qq列 *****
DESC stu;
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;
DESC stu;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
--- 修改sname数据类型的属性 ***
DESC stu;
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
--- 将gender 改为 sex 数据类型改为 CHAR 类型 ***
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
3-7. DCL
3-7-1. grant
- 创建一个管理员用户root,可以通过10网段,管理数据库.
grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;
- 创建一个应用用户wordpress,可以通过10网段,wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.
grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';
3-7-2. revoke
show grants for wordpress@'10.0.0.%';
mysql> revoke delete on wordpress.* from 'wordpress'@'10.0.0.%';
mysql> show grants for wordpress@'10.0.0.%';
3-8. DML
3-8-1. insert
插入数据先要查看表结构,DESC stu;
- 最简单,但是要严格按顺序来插入数值
INSERT stu VALUES(1,'zs',18,'m',NOW());
SELECT * FROM stu;
- 最规范,可以按表名后面需要的字段顺序来写
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条件)
UPDATE stu SET sname='aaa';
SELECT * FROM stu;
UPDATE stu SET sname='bb' WHERE id=6;
- delete (一定要有where条件)
DELETE FROM stu;
DELETE FROM stu WHERE id=9;
- 生产中屏蔽delete功能
使用update替代delete
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;