sql语句练习
sql练习2
MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option
删除无用缓存及垃圾文件
CRUD - create retrieve update delete 增查改删
1. SQL (Structured Query Language 结构化查询语言) :
- DDL (Data Definition Language 数据定义语言) -->操作表
create / drop / alter (创建 / 删除 / 改变 ) - DML (Data Manipulation Language 数据管理语言) -->操作数据
insert / delete / update (插入 / 删除 / 更新) - DQL (Data Query Language 数据查询语言) --> 重点
select (查找) - DCL (Data Control Langeuage 数据控制语言)
grant / revoke (授权 / 召回权限)
2. DDL (create / drop / alter)
2.1 create
- create database <name> default charset utf8 : 创建数据库*
- use <database_name> : 选中数据库
注意 : 选中数据库后才能创建表 - create table <name> : 创建表
2.2 drop
- drop user <user_name> : 删除用户
- drop database if exists <name> : 删除数据库
- truncate table <tb_name> : 删除表; truncate - 截断
2.3 alter
- alter table <name> <content> : 修改表
- cases :
- 给列添加唯一性约束 : alter table <tb_name> add constraint <new_name> unique (column_name);
- 添加主键约束 (primary key - 主键) : alter table <tb_name> add constraint <new_name> primary key (column_name);
- 添加外键约束 (foreign key - 外键; references - 参照) : alter table <tb_name> add constraint <new_name> foreign key (column_name) references <tb_name 2> (p_k_column_name);
- 自参照完整性约束
alter table TbEmp add constraint fk_mgr foreign key (mgr) references TbEmp(empno); - 修改字段类型：
alter table 表名 modify column 字段名 类型;
alter table 表名 change 原字段名 新字段名 类型；
2.4
- 查询表与表之间的关系(外健，索引) : select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA='movies';
3. DML (insert / delete / update)
3.1 insert
- insert into <tb_name> [column1, column2,...] values (content1), (content2)...;
- 导入&导出文件到CSV ：
MySQL中导入导出数据时，使用CSV格式时的命令行参数
在导出数据时使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;
在导入数据时使用：load data infile '/tmp/data.csv' into table ... $MYSQL_CSV_FORMAT;
MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"
CSV标准文档：RFC 4180 (Common Format and MIME Type for Comma-Separated Values (CSV) Files)，详细描述了CSV格式，其要点包括：
(1)字段之间以逗号分隔，数据行之间以\r\n分隔；
(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示 - cases :
3.2 delete
- delete from <tb_name> where <condition - 条件>;
- truncate
- cases :
3.3 update
- update <tb_name> set <content>
- cases :
4. DQL(select)
5. DCL(grant / revoke)
-- 创建用户
create user hellokitty@'%' identified by '123456';
-- 给hellokitty授权
grant to hellokitty@'%';
-- hrs.* - 将hrs库的所有文件的权限给hellokitty
grant all privileges on hrs.* to hellokitty@'%';
-- 从hellokitty召回权限
revoke all privileges on hrs.* from hellokitty@'%';
-- 召回对hrs.tbemp表的操作权限
6. 视图
create view vw_emp as select empno, ename, dno, sal from tbemp;
select ename from vw_emp;
update vw_emp set ename='胡二刀' where empno=1359;
7. 索引
-- 索引相当于是书的目录,它可以用来加速查询(先查目录定位搜索范围)
-- 这是典型的用空间换时间的策略, 所以索引要创建在用作查询筛选条件的列上(最常用)
-- 会延缓增加/删除/修改的速度
-- 需要表名和列
create index idx_emp_name on tbemp(ename);
-- unique --> 唯一索引
create unique index uni_emp_name on tbemp(enaem);
-- 删除索引(需要表名)
drop index idx_emp_name on tbemp;
8. 级联
触发器 - 不用,会造成锁表
级联(casade)操作 - on delete set null on update cascade; 默认 restrict - 不准操作
alter table TbEmp add constraint fk_dno foreign key (dno) references TbDept(deptno) on delete set null on update cascade;
9. 事务
-- 开启事务环境
begin:
-- 执行要做的多个操作(原子性操作)
delete from tbemp:
select * from tbemp;
-- ====回滚和提交选择 (结束事务)
-- 回滚 - 撤销事务
rollback;
-- 提交 - 让事务生效
commit;
2. ER图- Entity Relationship实体关系图
设计表与表之间的关系
矩形框 - 实体
椭圆 - 属性
线条 - 关系连接
棱形 - 声明关系
一对多 1 : m --> 外键约束
多对多 m : n --> 添加一个中间表 ,分成两个一对多, m : 1 and 1 : n
3. 操作
- 可以数据库里的所有文件备份,用于恢复或者提取sql语句
- 视图 - 查询结果的快照
- 有触发器 - 会造成锁表,最好不用
3.1. 创建数据库
-- 如果存在名为school的数据库就删除它
drop database if exists school;
-- 创建名为school的数据库,设置默认字符集为utf8,方便输入中文
create database school default charset utf8;
-- 切换到school数据库
use school;
3.1. 创建列表
-- ==================== 1.1创建学院表(tb_collage)
create table tb_college
(
-- comment - 注释 写在代码里
colid int not null comment '学院编号',
colname varchar(20) not null comment '学院名称',
coltel varchar(20) not null comment '联系电话',
colwebsite varchar(255) comment '网站地址',
选定主键,不能重复
primary key (colid)
);
给website添加唯一性约束
alter table tb_college add constraint uni_website unique (colwebsite);
-- ==================== 1.2 创建学生表(tb_student)
create table tb_student
(
stuid int not null comment '学号',
stuname varchar(4) not null comment '姓名',
stusex bit default 1 comment '性别',
stuaddr varchar(50) comment '籍贯',
-- colid - 外键(外来的主键)
colid int not null comment '学院'
);
-- 添加 主键(primary key)约束
alter table tb_student add constraint pk_stuid primary key (stuid);
-- 外键(foreign key)约束 参照(references)完整性
alter table tb_student add constraint fk_student_colid
foreign key (colid) references tb_college (colid);
alter table tb_student modify stuname varchar(10);
-- ====================1.3创建老师表(tb_teacher)
create table tb_teacher
(
thid int not null comment '工号',
thname varchar(4) not null comment '姓名',
thsex bit default 1 comment '性别',
thaddr varchar(50) comment '籍贯',
thage int comment '年龄',
primary key (thid)
);
-- =======================1.4 创建课程表(tb_course)
create table tb_course
(
cid int not null comment '课程编号',
cname varchar(20) not null comment '课程名称',
ccredit int not null comment '学分',
thid int not null comment '授课老师工号'
);
alter table tb_course add constraint pk_cid primary key (cid);
alter table tb_course add constraint fk_thid
foreign key (thid) references tb_teacher (thid);
学生和课程之间的中间表
-- =======================1.5 学生选课表(tb_sc)
create table tb_sc
(
-- 自增字段
scid int not null auto_increment comment '选课记录号',
sid int not null comment '学号',
cid int not null comment '课程编号',
-- 时间函数 now()
scdate datetime default now() comment '选课时间',
score decimal(4,1) comment '成绩',
-- 复合主键,一般不用
-- primary key (sid, cid)
primary key (scid)
);
-- 学号增加外键约束 ; references - 参照
alter table tb_sc add constraint fk_sc_sid
foreign key (sid) references tb_student(stuid);
-- 课程编号增加外键约束
alter table tb_sc add constraint fk_sc_cid
foreign key (cid) references tb_course(cid);
-- =======================1.6 老师任职表(tb_tcol)
create table tb_tcol
(
tcolid int not null auto_increment comment '任职记录',
colid int not null comment '学院',
thid int not null comment '教师',
tcoldate datetime default now() comment '任职时间',
primary key (tcolid)
);
alter table tb_tcol add constraint fk_tcol_colid
foreign key (colid) references tb_college (colid);
alter table tb_tcol add constraint fk_tcol_thid
foreign key (thid) references tb_teacher (thid);
3.4 DDL & DML
-- 如果存在名为tb_student的表就删除
-- drop table if exists tb_student;
-- 修改名为tb_student的表添加新列
alter table tb_student add column stutel char(11);
-- alter table tb_student drop column stutel
-- 插入数据到tb_student表
insert into tb_student values(1001, 'wh', 1, '四川成都', '12312345678');
-- default - 取默认值
insert into tb_student (stuid, stuname, stusex) values(1002, '元芳', default);
insert into tb_student (stuid, stuname) values (1003, '仁杰');
insert into tb_student values
(1004, '则天', 0, null, '12312345678'),
(1005, '冷面', 1, '开封', '13512345678');
-- 删除tb_student表中指定数据
注意删除和更新操作都要带上条件where,否则就对整个表操作(危险)
/*
delete from tb_student where stuid=1002;
delete from tb_student where stuid in (1003, 1004);
delete from tb_student where stuid=1001 or stuid=1003;
delete from tb_student where stuid between 1004 and 1010;
*/
-- 删除整个表 truncate - 截断
-- truncate table tb_student;
-- 修改表设置列 : 不允许重复 -->唯一约束/索引; constraint - 约束 ; unique() - 唯一的
alter table tb_student add constraint uni_tel unique(stutel);
-- 更新数据到tb_student表
update tb_student set stuaddr='湖南长沙', stutel='13812345678'
where stuid=1002 or stuid=1003;
update tb_student set stuaddr='长安' where stuname='则天';
