SQL基础应用（一）
mysql内置的功能
连接数据库
-
-u 用户
-
-p 密码
-
-S socket本地连接
-
-h ip地址
-
-P 端口号
-
-e 非交互模式
例子
mysql -uroot -p -S /tmp/mysql.sock mysql -uroot -p -h10.0.0.51 -P3306
-e 免交互执行sql语句
mysql -uroot -p123 -e "show databases;"
恢复数据
mysql -uroot -p <world.sql
内置命令
-
help 打印mysql帮助
-
\c ctrl+c 结束上个命令运行
-
\q quit; exit; ctrl+d 退出mysql
-
\G 将数据竖起来显示
-
source 恢复备份文件
SQL基础应用
SQL介绍
-
结构化的查询语言
-
关系型数据库通用的命令
-
遵循SQL92的标准(SQL_MODE)
SQL常用种类
-
DDL 数据定义语言
-
DCL 数据控制语言
-
DML 数据操作语言
-
DQL 数据查询语言
SQL引入-数据库的逻辑结构
库
-
库名字
-
库属性:字符集,排序规则
表
-
表名
-
表属性:存储引擎类型,字符集,排序规则
-
列名
-
列属性:数据类型,约束,其他属性
-
数据行
字符集 (charset)
- 相当于MySQL的密码本(编码表)
-
show charset;
-
utf8 : 3个字节
-
utf8mb4 (建议): 4个字节,支持emoji
排序规则: collation
-
mysql> show collation;
-
对于英文字符串的,大小写的敏感
-
utf8mb4_general_ci 大小写不敏感
-
utf8mb4_bin 大小写敏感(存拼音,日文)
数据类型介绍
作用
保证数据的准确性和标准性。
数值类型
tinyint ： -128~127
int ：-2^31~2^31-1
字符类型
char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
如何选择这两个数据类型?
1. 少于255个字符串长度,定长的列值,选择char
2. 多于255字符长度,变长的字符串,可以选择varchar
enum('bj','tj','sh')： 枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
时间类型
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;
删除数据库
DROP DATABASE oldguo;
修改数据库字符集
-
--- 注意: 一定是从小往大了改,比如utf8--->utf8mb4.
-
--- 目标字符集一定是源字符集的严格超级.
CREATE DATABASE oldguo; SHOW CREATE DATABASE oldguo; ALTER DATABASE oldguo CHARSET utf8mb4;
关于库定义规范
-
库名使用小写字符
-
库名不能以数字开头
-
不能是数据库内部的关键字
-
必须设置字符集.
表定义
--- 建表
- 表名,列名,列属性,表属性
--- 列属性
-
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;
建表规范
-
表名小写字母,不能数字开头,
-
不能是保留字符,使用和业务有关的表名
-
选择合适的数据类型及长度
-
每个列设置 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号';
在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 '性别';
insert 插入数据
最偷懒
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;
