数据库（1）

SQL

• 什么是SQL：Structured Query Language 结构化查询语言，用于程序员和数据库管理系统进行交流的语言
  在终端或命令行中和数据库软件建立连接

mysql -uroot -p

数据库相关SQL

1. 查看所有数据库
   格式: show databases;
2. 创建数据库
   格式：create database 数据库名; create database db1;
3. 查看数据库详情
   格式：show create databse 数据库名; show create database db1;
4. 创建数据库指定字符集
   格式：create database 数据库名 character set utf8/gbk; create database db2 character set gbk;
5. 删除数据库
   格式： drop database 数据库名; drop database db1;
6. 使用数据库
   格式： use 数据库名; use db1;
7. 表相关的SQL 前提需要使用了某个数据库
8. 查询所有表 show tables;
9. 创建表
   格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型); create table person(name varchar(10),age int);
   练习：创建一个学生表student有学号id 姓名name 语文chinese数学math英语english create table student(id int,name varchar(10),chinese int,math int,english int);
   查看表详情 show create table person;
10. 表引擎：
    innodb：支持数据库的高级操作包括：事务、外键等
    myisam：只支持数据基础的增删改查操作
11. 创建表指定引擎和字符集
    格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型) engine=myisam/innodb charset=utf8/gbk; create table t1(name varchar(10),age int) engine=myisam charset=gbk;
    练习：创建2个数据库分别是 mydb1和mydb2 在第一个数据库里面创建员工表emp 里面有姓名name年龄age工资sal字段，在mydb2里面创建英雄表hero，字段有名字name 年龄age 英雄类型type 字符串类型 并且指定此表的引擎为myisam 字符集为gbk create database mydb1; use mydb1; create table emp(name varchar(10),age int,sal int); create database mydb2; use mydb2; create table hero(name varchar(10),age int,type varchar(10))engine=myisam charset=gbk;
12. 查看表字段
    格式：desc 表名; desc hero;
13. 删除表
    格式： drop table 表名; drop table hero;
14. 修改表名
    格式： rename table 原名 to 新名; create table t1(name varchar(10));
    rename table t1 to t2;
15. 修改表引擎和字符集
    格式： alter table 表名 engine=myisam/innodb charset=utf8/gbk; alter table t2 engine=myisam charset=gbk;
16. 添加表字段
    最后面添加格式： alter table 表名 add 字段名 字段类型;
    最前面添加格式： alter table 表名 add 字段名 字段类型 first;
    xxx后面添加格式： alter table 表名 add 字段名 字段类型 after xxx; alter table t2 add age int; alter table t2 add sal int first; alter table t2 add id int after name;
17. 删除表字段
    格式： alter table 表名 drop 字段名; alter table t2 drop id;
18. 修改字段名和类型
    格式： alter table 表名 change 原名 新名 新类型; alter table t2 change sal salary varchar(10);
19. 修改字段类型和位置
    格式： alter table 表名 modify 字段名 新类型 first/after xxx; alter table t2 modify salary int after age;
    数据相关的SQL

create table person(id int,name varchar(10),age int);

1. 插入数据

1. 全表插入格式： insert into 表名 values (值1,值2,值3); insert into person values(1,'Tom',20);
2. 指定字段格式： insert into 表名 (字段1,字段2) values(值1,值2); insert into person (id,name) values(2,'Jerry');
3. 批量插入： insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3); insert into 表名 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2); insert into person values(3,'a1',25),(4,'a2',26); insert into person (id,name) values(5,'b1'),(6,'b2');
4. 查询数据
5. 格式：select 字段信息 from 表名 where 条件; select * from person; select name from person; select name,age from person; select * from person where name='Tom';
6. 修改数据
   格式：update 表名 set 字段名=值,字段名=值 where 条件; update person set age=18 where name='Jerry'; -修改id大于4的年龄为35 update person set age=35 where id>4;
7. 删除数据
   格式：delete from 表名 where 条件; delete from person where name='a2'; -删除年龄大于25岁的数据 delete from person where age>25; -删除所有数据 delete from person;
   中文乱码问题

insert into person values(10,'张三',18);

//以上代码个别同学会出现执行出错的情况 通过在终端中执行 set names gbk; 解决 select * from person; - 如果出现的不是报错而是查询数据时出现乱码 把现有的数据库和现有的表删除掉重新创建数据库创建表 保证字符集全部为utf8

主键约束

• 什么是主键：用于表示数据唯一性的字段称为主键
• 什么是约束： 就是创建表的时候给字段添加的限制条件
• 主键约束： 插入数据必须是唯一且非空的
• 格式： create table t1(id int primary key,name varchar(10)); insert into t1 values(1,'刘三'); insert into t1 values(1,'关三'); //报错 不能重复 insert into t1 values(null,'关三');//报错 不能为null
  主键约束+自增

• 自增数值只增不减
• 从历史最大值基础上+1
• 格式： create table t2(id int primary key auto_increment, name varchar(10)); insert into t2 values(null,'悟空'); //1 insert into t2 values(null,'八戒'); //2 insert into t2 values(10,'八戒'); //10 insert into t2 values(null,'沙僧');//11 delete from t2 id>=10; insert into t2 values(null,'沙僧');//12
  注释

• 对表的字段进行描述 create table t3(id int primary key auto_increment comment '主键字段',name varchar(10) comment '这是姓名');
  '和`的区别

• ' 是用来修饰字符串的
• 是用来修饰表名和字段名的 可以省略 create tablet4(idint,name` varchar(10));
  数据冗余

• 如果数据库中的表设计不够合理，随着数据量的增长出现大量的重复数据，这种重复数据的现象称为数据冗余，通过拆分表的形式解决此问题
• 练习：请设计表保存以下两条数据
  1. 集团总部下的教学研发部下的Java一部的员工苍老师，年龄18岁，工资100，性别男
  2. 人事部下的员工小明工资5000，年龄30 -创建员工表 emp id,name,age,salary,gender,deptid create table emp(id int primary key autoincrement,name varchar(10),age int,salary int,gender varchar(5),deptid int); -创建部门表 dept id,name,parentid create table dept(id int primary key autoincrement,name varchar(10),parentid int); -往两个表插入数据 insert into dept values(null,'集团总部',null),(null,'教学研发部',1),(null,'Java一部',2),(null,'人事部',1); insert into emp values(null,'苍老师',18,100,'男',3),(null,'小明',30,5000,'男',4);
• 练习2：设计表保存以下数据
  1. 家电分类下电视机分类下的小米电视价格(price)2588,库存(num)500
  2. 办公用品分类下的打印机分类下的惠普打印机价格1500,库存100 创建商品表 分类表 create table item(id int primary key autoincrement,name varchar(10),price int,num int,categoryid int); create table category(id int primary key autoincrement,name varchar(10),parentid int); -插入数据 insert into category values(null,'家电',null),(null,'电视机',1),(null,'办公用品',null),(null,'打印机',3); insert into item values(null,'小米电视',2588,500,2),(null,'惠普打印机',1500,100,4);

事务

• 什么是数据库中的事务？ 事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL语句全部执行成功或者全部执行失败
• 事务相关指令：
• 开启事务 begin;
• 提交事务 commit;
• 回滚事务 rollback;
  create table user(id int primary key auto_increment,name varchar(10),money int,state varchar(5));
  insert into user values(null,'钢铁侠',5000,'正常'),(null,'绿巨人',500,'正常'),(null,'超人',100,'冻结');
• 钢铁侠给绿巨人转账1000 update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=2 and state='正常';
• 钢铁侠给超人转账1000 update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=3 and state='正常';
• 在事务保护下执行:钢铁侠给超人转账1000 begin; //开启事务 update user set money=money-1000 where id=1 and state='正常'; -在这个时间点再开一个窗口检查数据库里面的数据是否改变 update user set money=money+1000 where id=3 and state='正常'; rollback; //转账失败 回滚事务
• 在事务保护下执行:钢铁侠给绿巨人转账1000 begin; //开启事务 update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=2 and state='正常'; commit; //转账成功 提交事务
• savepoint; 保存回滚点 begin; update user set money=2001 where id=1; savepoint s1; update user set money=2002 where id=1; savepoint s2; update user set money=2003 where id=1; rollback to s2;