创建数据库
create database 数据库的名字
create database daymy1;
-- 创建数据库的时候，指定字符集
create database 数据库的名字 character set 字符集;
create database day06 character set utf8;
-- 定义校验规则
create database 数据库的名字 character set 字符集 collate 校验规则;
create database daymy3 character set utf8 collate utf8_bin;
create database if not exists daymy3; // 判断有没有创建
查看数据库
-- 查看所有数据库
show create databases;
// 这几个数据库不可以删除
information_schema
mysql
performance_schemaVB
查看数据库定义语句
show create database 数据库的名字
show create database daymy3;
修改数据库的操作
--修改数据的字符集
alter database 数据库的名字 character set 字符集;
alter database daymy3 character set gbk;
删除数据库
drop database 数据库名字;
drop database daym1;
其它数据库指令
--切换数据库（选择中数据库）
use 数据库名字;
use daymy3;
--查看正在使用的数据库
select database();
创建表
查询所有的数据库
show databases;
create database daymy3;
use daymy3;
查看字符集
show create database daymy3;
修改字符集
alter database daymy3 character set 'utf8';
创建学生表
show tables;
create table student(
id int primary key auto_increment,
student_name varchar(50) not null,
age int not null,
telephone varchar(255)
)charset=utf8;
create table 表名(
列表1 列表名(长度) 约束
列表2 列表名(长度) 约束
);
列表的类型:
java sql
int int
char/string char/varchar
char: 固定长度
varchar: 可变长度
char(3): 用空格填充剩余的值
varchar(3): 不填充剩余的空格
长度代表空字符的个数
double double
float float
boolean boolean
date date: YYYY-MM--DD
time: hh:mm:ss
date: YYYY-MMM-DD hh:mm:ss 默认值是null
timestamp: YYYY-MM-DD hh:mm:ss 默认使用当前时间
text: 主要是用来存放文本
blob: 存放的是二进制
列第约束：
主键约束：PK primary key 唯一并且非空
唯一约束：UQ unique 唯一索引
非空约束：NN not null
自增：AI auto_increment
外键：FK
默认值：default
案例创建学生表：
1.学生ID
2.姓名
3.性别
4.年龄
5.创建时间
create table student(
sid int primary key,
sname varchar(31),
sex int,
age int,
sdate timestamp,
);
创建用户表
create table user(
id int primary key auto_increment,
username varchar(50) not null,
password varchar(50) not null
)charset=utf8;
// 多字段表
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`studentname` varchar(31) NOT NULL,
`password` varchar(20) NOT NULL,
`studentNo` varchar(30) NOT NULL,
`gender` char(10) NOT NULL,
`birth` date DEFAULT NULL,
`identityCode` varchar(18) NOT NULL,
`cellphoneNum1` varchar(11) DEFAULT NULL,
`cellphoneNum2` varchar(11) NOT NULL,
`address` varchar(100) NOT NULL,
`studentNumber` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `studentNo_UNIQUE` (`studentNo`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8
查看表
-- 查看所有的表
show tables;
-- 查看表的创建过程
show create table student;
-- 查看表结构
desc student;
修改表
添加列(add),修改列名(change),删除列(drop),修改表名(rename),修改表的字符集
添加列(add)
alter table 表名 add 列名 列的类型 列的约束
alter table student add avg int not null;
修改列(modify)
alter table student modify sex varchar(2);
修改列名(change)
alter table student change sex gender varchar(2);
删除列(drop)
alter table student drop avg;
修改表名(rename)
rename table stuent to students;
修改表的字符集
alter table students character set gbk;
// 计算时间差值
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');
删除表
drop table 表名
drop table student;
Sql完成对表中的数据操作
插入数据
insert into 表名(列名1,列名2,列名3) values(值1,值2,值3);
insert into stuent(sid,sname,sex,age) values(1,"zhangsan",1,23);
insert into t_employee(username,password,truename,gender,salary,telephone,address)
values('天真的张','123456','张三','男',8000,'1812984','北京朝阳区');
-- 简单写法
insert into student values(2,"zhangsan",1,23);
insert into student(sid,sname) values(2,"李四");
insert into student values(2,"李四"); // 这样插入会报错误
--批量插入
insert into student values
(5,'king',1,18),
(6,'key',1,22),
(7,'young',1,20),
(8,'skyu',1,23);
--单条插入和批量插入的效率
要是报错的就没法插入其他数据了
--查看表中数据
select * from student;
删除记录
delete from 表名 [where 条件];
delete from student where sid=10;
delete from student; // 如果没有指定条件，会将表中的数据一条条全部删除。
delete from student where id in (13,15); // 多ID删除in(id,id)
--面试问题：请说一下delete删除数据和truncate删除数据有什么差别？
delete: DML一条一条删除表中的数据。
truncate: DDL先删除表再重新建表；
关于那条执行效率高：具体要看表中数据，如果数据比较少，delete比较效率高，
如果数据比较多，truncate比较效率高。
更新表记录
update 表名 set 列名-列的值，列名2-列的值2 [where 条件];
--将sid为5的名字改成李四
--如果参数是字符串，日期要加上单引号
update student set sname='lisi' where sid=5;
update student set sname='king',sex=0;
查询记录
select [distinct] [*] [列名1,列名2] from 表名 [where 条件];
distinct: 清除重复的数据
--商品表category手机数码，皮包类--
1.分类的ID;
2.分类名称；
3.分类描述；
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
insert into category values(null,'手机数码','手机，电宝');
insert into category values(null,'衣服类','衣服裤子');
insert into category values(null,'水果类','苹果，西瓜');
insert into category values(null,'宠物类','企鹅，duck');
insert into category values(null,'蔬菜类','青椒，胡萝卜');
// 查询全部
select * from category;
select cname,cdesc from category;
商品和商品分类：所属关系
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int
);
update t_employee set salary=salary+123
where salary in (select a.salary FROM
(SELECT min(salary) salary from t_employee) a);
insert into product values(null,'华为手机',2768.8,null,1);
insert into product values(null,'花花公子',78.9,null,2);
insert into product values(null,'赣南脐橙',99.9,null,3);
insert into product values(null,'企鹅仔',389.78,null,4);
insert into product values(null,'青椒',5.89,null,5);
insert into product values(null,'草莓',10.8,null,3);
简单查询
--查询所有的商品：
select * from product;
--查询商品名称和商品价格：
select pname,price from product;
--别名查询 as 的关键字是可以省略
--表别名：select p.name,price from product p; (主要是用在多表查询);
select p.name,p.price from product as p;
--列别名：select pname as 商品名称,price as 商品价格 from prouct;
select pname as pname as 商品名称,price as 商品价格 from prouct;
去掉重复的值
-- 查询商品所有的价格
select distinct price from product;
--select运算查询：仅仅在查询上做了运算+-*/
select *,price*1.5 from product;
select *,price*1.5 as 打折后 from product;
--条件查询[where关键字]指定条件，确认要操作的记录
--查询商品价格>60元的所有商品信息
select * from product where price > 60;
where用法
--where 后的条件写法
--关系运算符：> , >= , < , <= , = , != , <>
<>：不等于：标准SQL语法
!=: 不等于: 非标准SQL语法
--查询商品价格在10到100之间
第一种方法：
select * from product where price > 10 and price < 100;
第二种方法：
select * from product where price between 10 and 100;
--逻辑运算：and,or,not
and 并且，or 或者，not 不等于
--- like:模糊查询
_ :代表的是一个字符
% :代表的是多个字符
--查询出名字中带鹅的所有商品 %鹅%
select * from product where pname like '%鹅%';
--查询第二名字是鹅的所有商品 '_鹅%';
select * from product where pname like '_鹅%';
-- in在一定范围获取得值
--查询出商品分类ID在1,4,5里面的所有商品
# select * from product where [要传的id名称] in (属性);
select * from product where cno in (1,4,5);
--排序查询：order by 关键字
asc: ascend 升序（默认的排序方式）
desc: descend 降序
--0 查询所有商品，按照价格进行排序
select * from product order by price asc;
--1 查询所有商品，按照价格进行降序
select * from product order by price desc;
--2 查询名称有鹅的商品，按照价格升序
#1.先查询出带鹅的商品
select * from product where pname like '%鹅%';
#2.进行排序得出结果
select * from product where pname like '%鹅%' order by price asc;
--聚合函数
sum() // 求和
avg() // 求平均值
count() // 统计数量
max() // 最大值
min() // 最小值
--1.获取所有商品价格的总和：
select sum(price) from product;
--2.获取所有商品平均价格：
select avg(price) from product;
--3.获取所有商品的个数
select count(*) from product;
--4.获取所有商品最大价格
select max(price) from product;
--注意：where条件后面不能接聚合函数
查询出所有商品的价格大于平局值
select * from product where price;
select avg(price) from product;
--子查询
select * from product where price > (select avg(price) from product);
--分组：group by
--1.根据cno字段分组，分组后统计商品的个数
select cno,count(*) from product group by cno;
--2.根据cno分组，分组统计没组商品的平局价格，并且商品平局价格 > 60
select cno,avg(price) from product group by cno
having avg(price) > 60;
--having 关键字可以结聚合函数，出现在分组以后
--where 关键字，她是不可以接聚合函数，出现在分组前
--编写顺序
-- S...F...W...G...H...o
select .. from .. where .. group by .. having .. order by
--执行顺序
F..w..G..H..S..9
from..where..group by..having..
// 数据备份
mysqldump --databases daymy3 > daymy3.sql
mysqldump -u root -padmin --databases daymy3 > daymy3.sql
-- 分页从哪条开始查询，查询几条
select * from student limit 2,2;
select e.username,e.address,d.departmentNmae from tbl_employee as
e,tbl_department as d where e.fk_depid = d.id;
select e.username,eaddress,d.departmentNmae from tbl_employee as e
inner join tbl_department as d on (e.fk_depid = d.id);
SQL创建多表及多表关系
技术分析：
- 多表之间的关系如果维护
外键约束：foreign key
给product中的cno添加一个外键约束
alter table product add foreign key(cno) references category(cid);
ALTER TABLE tbl_employee ADD CONSTRAINT 'fk_deptid'
FOREIGN KEY (fk_deptid) REFERENCES tbl_depat(id);
**错误提示(说明product表有catefory没有的ID,需要删除在添加)**
/*
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`daymy2`.`#sql-16d0_19`, CONSTRAINT `#sql-16d0_19_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `category` (`cid`))
*/
- 多表之间的建表原则
网上商城实力的分析：用户购物流程
-- 用户表（用户的ID,用户名，密码，手机）
create table user(
uid int primary key auto_increment,
username varchar(31),
password varchar(31),
phone varchar(11)
);
create table student_number(
stuNo int primary key auto_increment
)charset=utf8;
alter table student add foreign key(studentNumber) references student_number(stuNo);
insert into user values
(null,'张三','123456','18947784'),
(null,'李四','8888','175849573'),
(null,'王五','7891','134578947'),
(null,'皮尔','stuei','19478574');
-- 订单表（订单编号,总价,订单时间,地址,外键用户ID）
create table orders(
oid int primary key auto_increment,
sum int not null,
otime timestamp,
address varchar(100),
uno int,
foreign key(uno) references user(uid)
);
insert into orders values (null,200,null,'北京市',1);
-- 商品分类表（分类ID,分类名称,分类描述）
ccreate table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
-- 商品表（商品ID,商品名称,商品价格,外键cno）
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int,
foreign key(cno) references category(cid)
);
-- 订单项：中间表（订单ID,商品ID,商品数量,订单项总价）
create table orderitem(
ono int not null,
pno int not null,
count int,
sum double,
foreign key(ono) references orders(oid),
foreign key(pno) references product(pid)
);
insert into orderitem values(1,7,100,300);
发表评论