1.表相关的SQL语句
- 操作表相关的SQL 必须先使用某个数据库
create database mydb1;
show create database mydb1;
use mydb1; - 如果默认不是utf8,以后创建数据库需要制定字符集为utf8
create database mydb1 character utf8;
- 创建表
- 格式: create table 表名(字段1名 类型,字段2名 类型);
create table person(name varchar(5),age int); - 制定字符集格式:create table 表名(字段1名 类型,字段2名 类型) charset=utf8/gbk;
create table student(name varchar(10),score int) charset=gbk;
create table hero(name varchar(10),money int) default charset=utf8;
- 查询所有表
- 格式: show tables;
- 查询表详情
- 格式: show create table 表名;
show create table person;
show create table hero;
- 查询表字段
- 格式: desc 表名;
desc person;
- 删除表
- 格式: drop table 表名;
drop table hero;
- 修改表名
- 格式: rename table 原名 to 新名;
rename table person to t_person;
- 添加表字段
- 最后添加格式:alter table 表名 add 字段名 类型;
alter table student add gender varchar(5); - 最前面添加格式:alter table 表名 add 字段名 类型 first;
alter table student add id int first; - 在某个字段的后面添加: alter table 表名 add 字段名 类型 after xxx;
alter table student add money int after name;
- 删除表字段
- 格式: alter table 表名 drop 字段名;
alter table student drop money;
- 修改表字段
- 格式: alter table 表名 change 原名 新名 新类型;
alter table student change gender money int;
2.表相关SQL回顾
- 创建表: create table t1(name varchar(10),age int)charset=utf8/gbk;
- 查询所有 show tables;
- 查询详情 show create table t1;
- 查询表字段 desc t1;
- 删除表 drop table t1;
- 修改表名 rename table t1 to t2;
- 添加表字段 alter table t1 add age int first/after xxx;
- 删除表字段 alter table t1 drop age;
- 修改表字段 alter table t1 change 原名 新名 新类型;
3. 表相关练习
- 创建mydb2 字符集utf8 并使用该数据库
create database mydb2 character set utf8;
use mydb2; - 在mydb2中创建员工表emp 字段有name 字符集utf8
create table emp(name varchar(10)) charset=utf8; - 最后面添加age字段 alter table emp add age int;
- 最前面添加id字段 alter table emp add id int first;
- name后面添加gender字段
alter table emp add gender varchar(5) after name; - 修改gender字段为工资salary
alter table emp change gender salary int; - 删除age字段 alter table emp drop age;
- 修改表名为 t_emp rename table emp to t_emp;
- 删除表 删除数据库
drop table t_emp;
drop database mydb2;
4.DML数据操作语言
- 执行操作数据的SQL必须保证已经使用了某个数据库,并且存在数据所对应的表
create database mydb3 character set utf8;
use mydb3;
create table person(name varchar(10),age int)charset=utf8;
- 插入数据(增)
- 全表插入格式(要求值的数量和顺序和表字段保持一致):
insert into 表名 values(值1,值2,值3);
insert into person values(“Tom”,18); - 指定字段插入格式(要求值得数量和顺序和指定的一致)
insert into 表名(字段名1,字段名2) values(值1,值2);
insert into person(name) values(‘Jerry’); - 批量插入:
insert into person values(‘Lucy’,20),(‘Lily’,21);
insert into person(name) values(‘Lilei’),(‘Hanmeimei’); - 插入中文
insert into person values(‘刘德华’,50);
如果执行上面SQL语句出现错误提示 提示中包含16进制内容,执行以下SQL
set names gbk;
insert into person values(‘刘德华’,50);
- 查询数据
- 格式: select 字段信息 from 表名 where 条件;
select name from person;
select name,age from person where age>20;
select * from person where age=50;
- 修改数据
- 格式: update 表名 set 字段名=xxx,字段名=xxx where 条件;
- 举例:
update person set age=8 where name=‘Tom’;
update person set age=10 where age is null;
- 删除数据
- 格式: delete from 表名 where 条件;
delete from person where age=10;
delete from person where name=‘刘德华’;
delete from person where age<=20;
delete from person;
5.操作数据相关SQL回顾
- 插入 insert into 表名(字段1,字段2) values(值1,值2),(值1,值2);
- 查询 select 字段信息 from 表名 where 条件;
- 修改 update 表名 set 字段名=xxx,字段名=xxx where 条件;
- 删除 delete from 表名 where 条件;
6.数据相关练习题
- 创建hero表 字段: id,name,job(职业),money
create table hero(id int,name varchar(10),job varchar(5),money int)charset=utf8; - 插入以下数据:
insert into hero values(1, ‘诸葛亮’, ‘法师’, 18888),(2, ‘周瑜’, ‘法师’, 13888),(3, ‘刘备’, ‘战士’, 8888),(4, ‘孙尚香’, ‘射手’, 6888),(5 ,‘黄忠’, ‘射手’, 8888); - 修改13888为28888 update hero set money=28888 where money=13888;
- 修改所有射手价格为3000
update hero set money=3000 where job=‘射手’; - 删除价格为18888的英雄 delete from hero where money=18888;
- 修改孙尚香为猪八戒
update hero set name=‘猪八戒’ where name=‘孙尚香’; - 删除价格低于5000的英雄
delete from hero where money<5000; - 给表添加gender字段在name字段后面
alter table hero add gender varchar(5) after name; - 修改所有英雄性别为男 update hero set gender=‘男’;
- 删除表里面所有数据 delete from hero;
- 删除表 drop table hero;
7.数据类型
- 整数类型: int(m)和bigint(m) 等效java中的long , m代表显示长度
create table t1(name varchar(10),age int(10) zerofill);
insert into t1 values(‘aaa’,18);
select * from t1; - 浮点数: double(m,d) m代表总长度 d代表小数长度 53.234 double(5,3) ,超高精度浮点数 decimal(m,d)精度远高于double 只有涉及超高精度运算时使用.
- 字符串:
- char(m): 固定长度, m=10 存abc 占10 , 优点:执行效率略高 ,最大长度255
- varchar(m): 可变长度,m=10 存abc 占3 , 优点:更节省空间,最大长度65535,但是建议存255以下
- text(m):可变长度, 最大长度65535, 建议保存长度大于255的
- 日期
- date: 只能保存年月日
- time: 只能保存时分秒
- datetime: 默认值为null ,最大值9999-12-31
- timestamp(时间戳:距1970年1月1号的毫秒数): 默认值为当前系统时间,最大值2038-1-19
- 举例:
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values(null,null,‘2020-6-12 16:19:20’,null);
insert into t_date values(‘2018-10-12’,‘15:30:20’,null,null);
select * from t_date;
8. *.sql文件
- 此文件是从数据库中导出的数据,里面有很多条的SQL语句.
- 把emp.sql文件解压出来后 赋值到某个盘的根目录(如果linux系统放在桌面)
- 通过以下SQL语句导入该文件
windows系统: source d:/emp.sql;
linux系统: source /home/soft01/桌面/emp.sql; - 提示一堆Query OK 说明导入完成
- 测试:
show tables;
select * from emp; 如果显示乱码 执行 set names gbk; 再查
9. sql中的一些关键语法
去重distinct
- 查询员工表中有哪些工作
select distinct job from emp; - 查询员工表中的部门编号deptno有哪些
select distinct deptno from emp;
is null和is not null
- 查询没有上级领导的员工信息
select * from emp where mgr is null; - 查询有上级领导的员工姓名 工资和工作
比较运算符 > < >= <= = !=和<>
- 查询工资小于等于3000的员工姓名和工资
select ename,sal from emp where sal<=3000; - 查询不是程序员的员工姓名和工作(两种写法)
select ename,job from emp where job!=‘程序员’;
select ename,job from emp where job<>‘程序员’;
and和or
- and类似java中的 && ,当需要同时满足多个条件的时候使用
- or类型java中的||, 当多个条件满足一个就行的时候使用
- 查询1号部门工资大于2000的员工信息
select * from emp where deptno=1 and sal>2000; - 查询工作是人事或者工资大于3000的员工姓名 工作和工资
select ename,job,sal from emp where job=‘人事’ or sql>3000;
in
- 当查询某个字段的值为多个值得时候使用
- 查询工资为,3000/1500/5000的员工信息
select * from emp where sal=3000 or sal=1500 or sal=5000;
select * from emp where sal in(3000,1500,5000);
#between x and y 包含xy
- 当查询某个字段的值在某两个值之间的时候使用
- 查询工资在1000-2000之前的员工姓名和工资
select ename,sal from emp where sal between 1000 and 2000;
内容回顾
- 数据库相关SQL
- create database db1 character set utf8/gbk;
- show databases;
- show create database db1;
- drop database db1;
- use db1;
- 表相关
- create table t1(name varchar(10),age int) charset=utf8/gbk;
- show tables;
- show create table t1;
- desc t1;
- drop table t1;
- rename table t1 to t2;
- alter table t1 add age int first/after xxx;
- alter table t1 drop age;
- alter table t1 change 原名 新名 新类型;
- 数据相关
- insert into t1(name,age) values(xxx,xxx),(xxx,xxx);
- select 字段信息 from t1 where 条件
- update t1 set xxx=xxx where 条件
- delete from t1 where 条件
- 去重 distinct
- is null 和 is not null
- 比较运算符 > < >= <= = !=和<>
- and 和 or
- in
- between x and y
综合练习
- 查询有上级领导并且是3号部门的员工信息
- 查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号
- 查询1号部门工资为800和1600的员工信息
- 查询1号和2号部门工资高于2000的员工信息
- 查询员工表中出现的部门编号有哪几个