00 服务器与数据库操作
启动mysql服务器 net start/stop mysql3306
登录mysql数据库 mysql -uroot -p3306
创建数据库 create database test_db;
删除数据库 drop database test_db;
显示数据库 show databases;
选中数据库 use test_db;
01 创建表
CREATE TABLE "表格名" ("栏位1" "栏位1资料种类", "栏位2" "栏位2资料种类", ... );
create table customer (First_Name char(50), Last_Name char(50), Address char(50), Birth_Date date);
查看表基本结构 desc customer;
显示数据表的创建 show create table customer;
02 修改表
ALTER TABLE "表格名" [改变方式];
修改表名 alter table customer rename cust;
修改字段排列到指定位置 alter table cust modify Last_Name char(50) after Birth_Date;
修改字段排列到第一列 alter table cust modify Last_Name char(50) first;
修改字段数据类型 alter table cust modify Address varchar(25);
修改字段名 alter table cust change Address Addr varchar(25);
添加字段到指定位置 alter table cust add Hobby varchar(20) after Addr;
添加字段到第一列 alter table cust add Salary float first;
删除字段 alter table cust drop Salary;
INSERT INTO 插入数据
insert into cust values('jerry', 'marry', 'changsha','walking','2001-3-06'),('tom', 'smith', 'wuhan','ball','2005-4-5');
insert into cust(Last_Name,Addr) select Last_Name,Addr from cust;
UPDATE 更新数据
update cust set Addr = 'shenzhen' where Addr = "wuhan" and Birth_Date = '2005-4-5';
03 查询表
SELECT 筛选数据
select First_Name from cust;
DISTINCT 去重数据
select distinct First_Name from cust;
WHERE 条件限制
select First_Name from cust where Hobby = 'walking';
[AND|OR] 多个条件限制
SELECT "栏位名" FROM "表格名" WHERE "简单条件" {[AND|OR] "简单条件"}+ {};
SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275);
IN 不连续条件限制
SELECT "栏位名" FROM "表格名" WHERE "栏位名" IN ('值一', '值二', ...);
SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego');
BETWEEN 连续条件限制
SELECT "栏位名" FROM " 表格名" WHERE "栏位名" BETWEEN '值一' AND '值二';
SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';
ORDER BY 排序
SELECT "栏位名" FROM "表格名" [WHERE "条件"] ORDER BY "栏位名" [ASC, DESC];
SELECT store_name, Sales, Date FROM Store_Information ORDER BY 2 DESC;
栏位别名及表格别名
SELECT "表格别名"."栏位1" "栏位别名" FROM "表格名" "表格别名";
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name;
COUNT 计数
SELECT COUNT("栏位名") FROM "表格名";
SELECT COUNT(DISTINCT store_name) FROM Store_Information;
