查看当前所有存在的数据库
SHOW DATABASES;
创建测试数据库test_db
CREATE DATABASE test_db;
查看创建好的数据库test_db
SHOW CREATE DATABASE test_db;
删除数据库test_db
DROP DATABASE test_db;
查看数据库版本号
SELECT version();
查看系统表类型8.0开始，系统表全部换成事务型的InnoDB表
SELECT DISTINCT(ENGINE) FROM information_schema.tables;
create database zoo;
选择当前数据库
use zoo;
drop database zoo;
show databases;
create database test_db;
use test_db;
drop table tb_emp1;
create table tb_emp1(
id int(11),
name varchar(25),
deptId int(11),
salary float
);
查看数据表
show tables;
主键是唯一的，且不允许为空
方法一
create table tb_emp2(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float
);
方法二
定义完所有列之后指定主键
create table tb_emp3(
id int(11),
name varchar(25),
deptId int(11),
salary float,
primary key(id)
);
多字段联合主键
create table tb_emp4(
name varchar(25),
deptId int(11),
salary float,
primary key(name, deptId)
);
使用外键约束
子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配
create table tb_dept1(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
create table tb_em5(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
);
使用非空约束
create table tb_emp6(
id int(11) primary key,
name varchar(25) not null,
deptId int(11),
salary float
);
唯一性约束，一个表中可以有多个字段声明为unique
方法一
create table tb_dept2(
id int(11) primary key,
name varchar(22) unique,
location varchar(50)
);
方法二
create table tb_dept3(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint STH unique(name)
);
使用默认约束
create table tb_emp7(
id int(11) primary key,
name varchar(25) not null,
deptId int(11) default 1111,
salary float
);
设置表的属性值自动增加
create table tb_emp8(
id int(11) primary key auto_increment,
name varchar(25) not null,
deptId int(11),
salary float
);
show tables;
insert into tb_emp8(name, salary)
value('Lucy', 1000),
('Lura', 1200),
('Kevin', 1500);
select * from tb_emp8;
查看数据表结构
describe tb_emp8;
desc tb_emp8;
查看表详细结构语句
show create table tb_emp1;
修改表结构
修改表名
alter table tb_dept3 rename tb_deptment3;
show tables;
修改字段的数据类型
alter table tb_dept1 modify name varchar(30);
desc tb_dept1;
修改字段名，必须要写数据类型
alter table tb_dept1 change location loc varchar(50);
alter table tb_dept1 change loc location varchar(60);
4.3.4 添加字段
alter table tb_dept1 add managerId int(10);
alter table tb_dept1 add column1 varchar(12) not null;
alter table tb_dept1 add column2 int(11) first;
alter table tb_dept1 add column3 int(11) after name;
4.3.5 删除字段
alter table tb_dept1 drop column2;
4.3.6修改字段的排列位置
alter table tb_dept1 modify column1 varchar(12) first;
alter table tb_dept1 modify column1 varchar(12) after location;
4.3.7 更改表的存储引擎
show engines;
show create table tb_dept1;
alter table tb_dept1 engine=InnoDB;
4.3.8 删除表的外键约束
create table tb_dept9(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept1(id)
);
show create table tb_dept9;
alter table tb_dept9 drop foreign key fk_emp_dept;
4.4 删除数据表
drop table if exists tb_dept2;
show tables;
create table tb_dept2(
id int(11) primary key,
name varchar(22),
location varchar(50)
);
create table tb_emp(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept2(id)
);
drop table tb_dept2;
alter table tb_emp drop foreign key fk_emp_dept;
4.5 查看默认字符集
show variables like 'character_set_database';
4.7 综合案例
create database company;
use company;
create table offices(
officeCode int(10) primary key not null unique,
city varchar(50) not null,
address varchar(50),
country varchar(50) not null,
postalCode varchar(15) unique
);
create table employees(
employeeNumber int(11) primary key not null unique auto_increment,
lastName varchar(50) not null,
firstName varchar(50) not null,
mobile varchar(25) unique,
officeCode int(10) not null,
jobTitle varchar(50) not null,
birth datetime not null,
note varchar(255),
sex varchar(5),
constraint office_fk foreign key(officeCode) references offices(officeCode)
);
show tables;
alter table employees modify mobile varchar(255) after officeCode;
alter table employees change birth employee_birth datetime;
alter table employees modify sex char(1) not null;
alter table employees drop note;
alter table employees add favoriate_activity varchar(100);
alter table employees drop foreign key office_fk;
drop table offices;
alter table employees engine=MyISAM;
alter table employees rename employees_info;
4.9 经典习题
create database Market;
use Market;
create table customers(
c_num int(11) primary key not null unique auto_increment,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth datetime not null
);
desc customers;
alter table customers modify c_contact varchar(50) after c_birth;
alter table customers modify c_name varchar(70);
alter table customers change c_contact c_phone varchar(50);
alter table customers add c_gender char(1);
alter table customers rename customers_info;
show tables;
alter table customers_info drop c_city;
alter table customers_info engine=InnoDB;
create table orders(
o_num int(11) primary key not null unique auto_increment,
o_date date ,
c_id int(11),
constraint orders_fk foreign key (c_id) references customers_info(c_num)
);
show tables;
desc orders;
alter table orders drop foreign key orders_fk;
drop table orders;
7.1 基本查询语句
create table fruits(
f_id char(10) not null,
s_id int not null,
f_name char(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
insert into fruits
(f_id, s_id, f_name, f_price)
values
('a1', 101, 'apple', 5.2),
('b1', 101, 'blackberry', 10.2),
('bs1', 102, 'orange', 11.2),
('bs2', 105, 'melon', 8.2),
('t1', 102, 'banana', 10.3),
('t2', 102, 'grape', 5.3),
('o2', 103, 'coconut', 3.2),
('c0', 101, 'cherry', 3.2),
('a2', 103, 'apricot', 2.3),
('l2', 104, 'lemon', 6.4),
('b2', 104, 'berry', 7.6),
('m1', 106, 'mango', 15.7),
('m2', 105, 'xbabay', 2.6),
('t4', 107, 'xbababa', 3.6),
('m3', 105, 'xxtt',11.6),
('b5', 107, 'xxxx', 3.6);
select f_id, f_name from fruits;
7.2 单表查询
select * from fruits;
select f_id, s_id, f_name, f_price from fruits;
select f_name from fruits;
7.2.3 查询指定记录
select f_name, f_price from fruits where f_price = 10.2;
select f_name, f_price from fruits where f_name='apple';
select f_name, f_price from fruits where f_price < 10;
7.2.4 in 和 not in 查询
select s_id, f_name, f_price from fruits where s_id in (101, 102) order by f_name;
select s_id, f_name, f_price from fruits where s_id not in (101, 102) order by f_name;
7.2.5 between and
select f_name, f_price from fruits where f_price between 2.00 and 10.20;
7.2.6 like 查询
select f_id, f_name from fruits where f_name like 'b%';
select f_id, f_name from fruits where f_name like '%g%';
select f_id, f_name from fruits where f_name like 'b%y';
select f_id, f_name from fruits where f_name like '____y';
7.2.7 查询空值
create table customers(
c_id int not null auto_increment,
c_name char(50) not null,
c_address char (50) null,
c_city char(50) null,
c_zip char(10) null,
c_contact char(50) null,
c_email char(255) null,
primary key(c_id)
);
insert into customers
(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
values
(10001, 'RedHook', '200 Street', 'Tianjin', '300000', 'LiMing', 'LiMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', 'Jerry@hotmai.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', Null),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');
select c_id, c_name, c_email from customers where c_email is null;
select c_id, c_name, c_email from customers where c_email is not null;
7.2.8 带and 多条件查询
select f_id, f_price, f_name from fruits where s_id = 101 and f_price >= 5;
select f_id, f_price, f_name from fruits where s_id in (101, 102) and f_price >=5 and f_name='apple';
7.2.9 带or 的多条件查询
select s_id, f_name, f_price from fruits where s_id = 101 or s_id = 102;
and 的优先级比or的优先级高
7.2.10 查询结果不重复
select distinct s_id from fruits;
7.2.11 对查询结果排序
select f_name from fruits order by f_name;
select f_name, f_price from fruits order by f_name, f_price;
升序 asc 降序 desc
select f_name, f_price from fruits order by f_price desc;
select f_price, f_name from fruits order by f_price desc, f_name;
7.2.12 分组查询
select s_id, count(*) as Total from fruits group by s_id;
group_concat()
select s_id, group_concat(f_name) as Names from fruits group by s_id;
having 分组之后过滤
select s_id, group_concat(f_name) as Names from fruits group by s_id having count(f_name) > 1;
with rollup 不能与order by 同时使用
select s_id, coalesce(s_id, '总计'), count(*) as Total from fruits group by s_id with rollup;
多字段分组
select s_id, f_name from fruits group by s_id, f_name;
以下查询会报错
select * from fruits group by s_id, f_name;
select @@GLOBAL.sql_mode;
create table orderitems(
o_num int not null,
o_item int not null,
f_id char(10) not null,
quantity int not null,
item_price decimal(8,2) not null,
primary key (o_num, o_item)
);
insert into orderitems
(o_num, o_item, f_id, quantity, item_price)
values
(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 4, 14.99);
select o_num, SUM(quantity * item_price) AS orderTotal from orderitems group by o_num having SUM(quantity * item_price) >= 100;
select o_num, SUM(quantity * item_price) AS orderTotal from orderitems group by o_num having SUM(quantity * item_price) >= 100 order by orderTotal;
7.2.13 limit
前4条数据
select * from fruits limit 4;
从第5条开始的3条数据
select * from fruits limit 4,3;
select * from fruits limit 3 offset 4;
7.3.1 COUNT()函数
select count(*) as cust_num from customers;
select count(c_email) as email_num from customers;
select * from orderitems ;
select o_num, count(f_id) from orderitems group by o_num;
7.3.2 SUM()函数
select sum(quantity) as items_total from orderitems where o_num = 30005;
select o_num, sum(quantity) as items_total from orderitems group by o_num;
7.3.3 AVG() 函数
select avg(f_price) as avg_price from fruits where s_id=103;
select s_id, avg(f_price) as avg_price from fruits group by s_id;
7.3.4 MAX() 函数
select MAX(f_price) as max_price from fruits;
select s_id, max(f_price) as max_price from fruits group by s_id;
select max(f_name) from fruits;
7.3.5 MIN() 函数
select min(f_price) as min_price from fruits;
select s_id, min(f_price) as min_price from fruits group by s_id;
7.4.1 内连接查询
create table suppliers(
s_id int not null auto_increment,
s_name char(50) not null,
s_city char(50) null,
s_zip char(10) null,
s_call char(50) not null,
primary key(s_id)
);
insert into suppliers
(s_id, s_name, s_city, s_zip, s_call)
values
(101, 'FastFruit Inc.', 'Tianjin', '300000', '48075'),
(102, 'LT Supplies', 'Chongqing', '400000', '44333'),
(103, 'ACME', 'Shanghai', '200000', '90046'),
(104, 'FNK Inc.', 'Zhongshan','528437','11111'),
(105, 'Good Set','Taiyuan','030000','2222'),
(106,'Just Eat Ours', 'Beijing','010', '45678'),
(107, 'DK Inc', 'Zhengzhou', '450000','33332');
desc fruits;
desc suppliers;
select suppliers.s_id, s_name, f_name, f_price from fruits, suppliers where fruits.s_id = suppliers.s_id;
select f.f_name, f.f_price, s.s_id, s.s_name from fruits f inner join suppliers s on f.s_id = s.s_id;
自连接查询
不理解下面这句
select f1.f_id, f1.f_name from fruits f1, fruits f2 where f1.s_id = f2.s_id and f2.f_id = 'a1';
7.4.2 外连接查询
create table orders(
o_num int not null auto_increment,
o_date datetime not null,
c_id int not null,
primary key(o_num)
);
insert into orders (o_num, o_date, c_id)
values
(30001, '2008-09-01', 10001),
(30002, '2018-09-12', 10003),
(30003, '2018-09-30', 10004),
(30004, '2018-10-03', 10005),
(30005, '2018-10-08', 10001);
外连接分为左外连接或左连接和右外连接或右连接
左连接： 包含左表中的所有记录和右表中连接字段相等的记录
select customers.c_id, orders.o_num from customers left join orders on customers.c_id = orders.c_id;
右连接： 包含右表中的所有记录和左表中连接字段相等的记录
select customers.c_id, orders.o_num from customers right join orders on customers.c_id = orders.c_id;
7.4.3 复合条件连接查询
select customers.c_id, orders.o_num from customers inner join orders on customers.c_id = orders.c_id and customers.c_id=10001;
select s.s_id, s.s_name, f.f_name, f.f_price from fruits f inner join suppliers s on f.s_id = s.s_id order by s.s_id;
7.5 子查询
create table tbl1 (num1 int not null);
create table tbl2 (num2 int not null);
insert into tbl1 values(1), (5), (13), (27);
insert into tbl2 values(6), (14), (11), (20);
关键字ANY 或者SOME
select num1 from tbl1 where num1 > ANY (select num2 from tbl2 );
select num1 from tbl1 where num1 > SOME (select num2 from tbl2);
关键字 ALL
select num1 from tbl1 where num1 > ALL (select num2 from tbl2);
关键字 EXISTS
select * from fruits where exists (select s_name from suppliers where s_id = 107);
select * from fruits where f_price > 10.20 and exists (select s_name from suppliers where s_id = 107);
select * from fruits where not exists (select s_name from suppliers where s_id = 107);
关键字 IN
select c_id from orders where o_num in (select o_num from orderitems where f_id = 'c0');
select c_id from orders where o_num not in (select o_num from orderitems where f_id = 'c0');
7.5 带比较运算符的子查询
select s_id, f_name from fruits where s_id = (select s_id from suppliers where s_city = 'Tianjin');
select s_id, f_name from fruits where s_id <> (select s_id from suppliers where s_city = 'Tianjin');
7.6 合并查询结果（union）
删除重复的数据
select * from fruits where f_price < 9 union select * from fruits where s_id in (101, 103) ;
不删除重复的数据
select * from fruits where f_price < 9 union all select * from fruits where s_id in (101, 103) ;
7.7 为表和字段取别名 AS
7.7.1 为表取别名
select * from orders o where o.o_num = 30001;
7.7.2 为字段取别名
select f_name as fruit_name , f_price as fruit_price from fruits as f1 where f_price < 8;
select concat(TRIM(s_name), '(', TRIM(s_city), ')') as suppliers_title from suppliers order by s_name;
7.8 使用正则表达式查询
select * from fruits where f_name REGEXP '^b';
select * from fruits where f_name REGEXP '^be';
select * from fruits where f_name REGEXP 'y';
select * from fruits where f_name REGEXP 'a.g';
select * from fruits where f_name REGEXP '^ba*';
select * from fruits where f_name REGEXP '^ba+';
select * from fruits where f_name REGEXP 'on';
select * from fruits where f_name REGEXP 'on|ap';
like 如果字符串在文本中间出现匹配不到
select * from fruits where f_name like 'on';
select * from fruits where f_name REGEXP '[ot]';
select * from fruits where f_id REGEXP '[456]';
select * from fruits where f_id REGEXP '[^a-e1-2]';
select * from fruits where f_name REGEXP 'x{2,}';
select * from fruits where f_name REGEXP 'ba{1,3}';
