一.数据表操作
1.基础操作:
--创建表(类似于excel表)
create table <tab_name> (
<field_name1> <TYPE(x)> [<完整性约束条件>],
<field_name2> <TYPE(x)>,
...
<field_namen> >TYPE>--注意:最后1个字段不加逗号!!!
) [character set <xxx>];
--参数说明:
xxx:字符编码方式
type(x):数据类型;x为最大位数(Date不需要);Numeric还需要1个参数d指定小数点后最大位数
--创建一个员工表employee:
create table employee (
id TINYINT primary key auto_increment ,
name VARCHAR(20),
gender boolean,--MySQL无boolean,自动转换为TINYINT(1)
--也可使用gender BIT
job VARCHAR(20),
salary DOUBLE(4,2) unsigned
);
--完整性约束条件:
primary key:声明是主键(自动要求非空且唯一)
foreign key:声明是外键
unique:唯一(不得出现重复值);默认可以重复
not null:非空;默认可以为空
auto_increment:该字段为自动编号(从1开始);如果手动给定为x,下一条为x+1(见下图)
default x:指定默认值为x;默认为NULL
--查看表信息
desc <tab_name>:查看表结构
mysql> desc employee
+------+-----------+----+---+-------+--------------+
|Field |Type |Null|Key|Default|Extra |
+------+-----------+----+---+-------+--------------+
|id |tinyint(4) |NO |PRI|NULL |auto_increment|
|name |varchar(25)|YES | |NULL | |
|gender|tinyint(1) |YES | |NULL | |
|job |varchar(20)|YES | |NULL | |
|salary|double(4,2)|YES | |NULL |unsigned |
+------+-----------+----+---+-------+--------------+
show columns from <tab_name>:查看表结构(同desc)
show tables:查看当前数据库中的所有的表
show create table <tab_name>:查看当前数据库表建表语句
--修改表结构
--增加字段
alter table <tab_name> add [column] <col_name type> [<完整性约束条件 position>];
--position:指定字段位置;默认为最后;可为first,after <col_name>
alter table user add addr varchar(20) not null unique first/after username;
--1次添加多个字段
alter table users2
add addr varchar(20),
add age int first,
add birth varchar(20) after name;
--修改列类型
alter table <tab_name> modify <col_nume type> [<完整性约束条件> default <x position>];
--default x:指定默认值为x;默认为NULL
--position:指定修改后字段位置;见 增加字段
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
--修改列名
alter table <tab_name> change [column] <col_name new_col_name type> [<完整性约束条件> default <x position>];
alter table users2 change age Age int default 28 first;
--删除列
alter table <tab_name> drop [column] <col_name>;
--1次删除多个字段
alter table users2
drop age,
drop birth;
--增加列并删除列
alter table users2
add salary float(6,2) unsigned not null after name,
drop addr;
--修改表名
rename table <tab_name> to <new_tab_name>;
rename table users2 to users
--修该表所用的字符集
alter table <tab_name> character set <xxx>
alter table student character set utf8;
--删除表
drop table <tab_name>;
--添加/删除主键
alter table <tab_name> add primary key (<field_name,...>)
alter table <tab_name> drop primary key;
--auto_increment:
create table test5(num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table test(num int primary key auto_increment);
--如何删除主键?
alter table test modify id int;--auto_increment没了,但这样写主键依然存在
--所以还要加上下面这句
alter table test drop primary key;--仅用这句也无法直接删除主键
--唯一索引
alter table <tab_name> add unique [index|key] [<索引名称>](<字段名称,...>)
alter table users add unique(name);--索引值默认为字段名show create table users
alter table users add unique key user_name(name);--索引值为user_name
--添加联合索引
alter table users add unique index name_age(name,age);#show create table users;
--删除唯一索引
alter table <tab_name> drop {index|key} <index_name>
- 创建文章表:
create table article (
id int primary key auto_increment,
title varchar(20),
publish_date INT,
click_num INT,
is_top TINYINT(1),
content TEXT
);
2.完整性约束条件之主键约束
- 一张表只能有一个主键
- 主键类型不一定是整型
(1)单字段主键:非空且唯一
<field type> primary key,
create table users (
id INT primary key,
name varchar(20),
city varchar(20)
);
(2)多字段联合主键:
primary key(<field1,field2...>)
create table users2 (
id INT,
name varchar(20),
city varchar(20),
primary key(name,id)
);
九.表记录操作
1.增删改:
--增加记录
insert [into] <tab_name> [(<field1,filed2...>)] values (<value1,value2...>);
--可以只插入1条记录的部分字段(其余需有默认值或可以为NULL)
--不加(field1...)时只能增加完整记录,不能空置某些字段
create table employee_new(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday varchar(20),
salary float(7,2)
);
insert into employee_new (id,name,birthday,salary)
values (1,'yuan','1990-09-09',9000);
insert into employee_new (name,salary)
values ('xialv',1000);
--插入多条记录
insert [into] <tab_name> values (<record1>) (<record2>)...;
--只能增加完整的记录,不能空置某些字段
insert into employee_new values
(4,'alvin1','1993-04-20',3000),
(5,'alvin2','1995-05-12',5000);
--set插入
insert [into] <tab_name> set <col_name>=<value>,...;
insert into employee_new set id=12,name="alvin3";
--修改表记录
update <tab_name> set <field1>=<value1>,<field2>=<value2>...[where <语句>]
--SET:修改哪些列/列的值
--WHERE 语句:更新哪些记录;默认更新所有
update employee_new set birthday="1989-10-24" WHERE id=1;
--将yuan的薪水在原有基础上增加1000元:
update employee_new set salary=salary+4000 where name='yuan';
--删除表纪录
delete from <tab_name> [where <语句>];
--where 语句:删除哪些记录;默认删除所有
--只能删除表中内容,不能删除表本身,用drop删除表
--删除表中name为"alex"的记录:
delete from employee_new where name='alex';
--删除表中id为2或3的记录:
delete from employee_new where id=2 or id=3;
--删除标准id为9且name为"y"的记录:
delete from employee_new where id=4 and name="y";
--删除表中所有记录:
delete from employee_new;--注意:auto_increment没有被重置:alter table employee auto_increment=1;
TRUNCATE TABLE <tab_name>;
--删除表中的所有数据;该方式删除的数据不能在事务中恢复
--与delete区别在于该方式先摧毁表,再新建同名表,故提示0条被影响
truncate table emp_new;
2.单表查询:
- having能否独立使用?
SELECT [ALL|DISTINCT] <field> [AS <别名>] FROM <tab_name> [WHERE <条件> GROUP BY <field> HAVING <条件> ORDER BY <field> <order> LIMIT <条数>]
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
JS DOUBLE,
Django DOUBLE,
OpenStack DOUBLE
);
INSERT INTO ExamResult VALUES
(1,"yuan",98,98,98),
(2,"xialv",35,98,67),
(3,"alex",59,59,62),
(4,"wusir",88,89,82),
(5,"alvin",88,98,67),
(6,"yuan",86,100,55);
--select查询
select [distinct|ALL] <field>... from <tab_name>...;
--from:从哪张表筛选
--all:查询选定范围内的所有记录;默认值,可省略
--field:查看哪些字段;*指所有字段;字段按命令中顺序显示
--如果非当前表的字段,field_应写为tab_.field_
--tab_name:指定数据源
--如果field来自多个表,为tab1_,tab2_...
--distinct:声明剔除重复记录(选定的所有字段重复就剔除,显示第1个)
--查询表中所有学生的信息:
select * from ExamResult;
--查询所有学生的姓名和JS成绩:
select name,JS from ExamResult;
--查询所有学生的姓名和JS成绩,并过滤重复数据:
select distinct JS,name from ExamResult;
--使用表达式(存储的仍是原数据)
--在所有学生分数上加10分特长分显示:
select name,JS+10,Django+10,OpenStack+10 from ExamResult;
--统计每个学生的总分:
select name,JS+Django+OpenStack from ExamResult;
--以别名显示
<field> as <别名>|<field 别名>
--以别名表示学生姓名和总分:
select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;
select name 姓名,JS+Django+OpenStack 总成绩 from ExamResult;
select name JS from ExamResult;--将JS当作name别名
--使用where进行过滤查询
--只能使用字段进行过滤,不能使用别名进行过滤
select <field> from <tab_name> [where <语句>];
--where字句中可以使用:
--比较运算符:>,<,>=,<=,<>,!=,between x_ and y_,in(x1_,...),like patt_
between 80 and 100--[10,20]
in(80,90,100)--值是10或20或30
like 'yuan%'
--文本的pattern可以是%(任意多字符),_(几个就匹配字符)
--逻辑运算符:and,or,not
--实例:
--查询姓名为XXX的学生成绩:
select * from ExamResult where name='yuan';
--查询英语成绩大于90分的同学:
select id,name,JS from ExamResult where JS>90;
--查询总分大于200分的所有同学:
select name,JS+Django+OpenStack as 总成绩 from
ExamResult where JS+Django+OpenStack>200;
--查询JS分数在[80,100]的同学:
select name ,JS from ExamResult where JS between 80 and 100;
--查询Django分数为75,76,77的同学:
select name ,Django from ExamResult where Django in (75,98,77);
--查询所有姓王的学生成绩:
select * from ExamResult where name like '王%';
--查询JS分>90,Django分>90的同学:
select id,name from ExamResult where JS>90 and Django >90;
--查找缺考数学的学生的姓名:
select name from ExamResult where Database is null;
--使用Order by指定排序的列
--排序的列可以是表中的列名,也可以是指定的别名
select <field> from <tab_name> order by <field> [<order>];
--默认按创建顺序排列
--field:按那1个字段进行排列
--order:如何排序;Asc(升序),Desc(降序);默认为Asc;NULL最小
--ORDER BY子句应位于SELECT/WHERE语句后
--实例:
--对JS成绩排序后输出:
select * from ExamResult order by JS;
--对总分排序按从高到低的顺序输出:
select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
总成绩 from ExamResult order by 总成绩 desc;
--对姓李的学生成绩排序输出:
select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))
总成绩 from ExamResult where name like 'a%'
order by 总成绩 desc;
CREATE TABLE order_menu(
id INT PRIMARY KEY auto_increment,
product_name VARCHAR (20),
price FLOAT(6,2),
born_date DATE,
class VARCHAR (20)
);
INSERT INTO order_menu (product_name,price,born_date,class) VALUES
("苹果",20,20170612,"水果"),
("香蕉",80,20170602,"水果"),
("水壶",120,20170612,"电器"),
("被罩",70,20170612,"床上用品"),
("音响",420,20170612,"电器"),
("床单",55,20170612,"床上用品"),
("草莓",34,20170612,"水果");
--使用group by进行分组查询
--注意,按分组条件分组后每一组只会显示第一条记录
SELECT <field> from <tab_name> group by <acc>;
--acc:按什么分组;字段(该字段值相同的记录为1组;可接多个字段),字段位置(按第n个字段)
--实例:
--按位置字段筛选:
select * from order_menu group by 5;--即按class
--对购物表按类名分组后显示每一组商品的价格总和:
select class,SUM(price) from order_menu group by class;
--使用having进行筛选:
SELECT <field> from <tab_name> group by <field> HAVING <条件语句>
--对购物表按类名分组后显示每一组商品价格总和超过150的商品:
select class,SUM(price) from order_menu
group by class HAVING SUM(price)>150;
--having和where都可以对查询结果进行过滤
--差别有:
·where语句只能用在分组前的筛选,having可用在分组后的筛选
·使用where语句的地方都可以用having进行替换
·但使用having必需使用group by
·having中可以用聚合函数,where中不行
·where效率高于having
--GROUP_CONCAT()
SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;
--聚合函数:(一般和分组查询配合使用)
SELECT <func>(<field>) from <tab_name> where <条件> group by <field>
--将要求的内容查出来再包上聚合函数即可
--<1>统计表中所有记录:
--COUNT(<field>):统计记录数
--注意:count(*)统计所有行;count(field_)不统计NULL值
--统计1个班级有多少学生:
select count(*) from ExamResult;--先查出所有的学生,再用count包上
--统计JS成绩大于70的学生有多少个:
select count(JS) from ExamResult where JS>70;
--统计总分大于280的学生有多少个:
select count(name) from ExamResult
where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
--SUM(<field>):统计该组该字段所有值的内容和
--注意:sum仅可用于数值(NULL相当于0),否则会报错
--统计一个班级JS总成绩:
select sum(JS) as JS总成绩 from ExamResult;--先查出所有的JS成绩,再用sum包上
--统计一个班级各科分别的总成绩:
select sum(JS) as JS总成绩,
sum(Django) as Django总成绩,
sum(OpenStack) as OpenStack from ExamResult;
--统计一个班级各科的成绩总和:
select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
as 总成绩 from ExamResult;
--统计一个班级JS成绩平均分:
select sum(JS)|count(*) from ExamResult;
--AVG(<field>):统计该组该字段所有值的平均值
--注意:AVG()不计入NULL,故结果不同于SUM()/COUNT()
--求一个班级JS平均分:
select avg(ifnull(JS,0)) from ExamResult;--先查出所有的JS分,然后用avg包上
--求一个班级总分平均分:
select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
from ExamResult ;
--Max(<field>)/Min(<field>):统计该组该字段中的最大/小值
--注意:仅可用于数值(不包括NULL,但又NULL不报错)
--求班级最高分和最低分(数值范围在统计中特别有用):
select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
最高分 from ExamResult;
select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
最低分 from ExamResult;
--求购物表中单价最高的商品名称及价格
SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?
--ifnull(<field>,<x>):如果值是NULL,转换为x,否则不变
--注意:NULL和所有数计算结果都是null,所以需要用ifnull将NULL转换为0!
--<2>统计分组后的组记录:
GROUP BY field_ WITH ROLLUP:对GROUP BY每个分组分别进行指定统计
--统计各类商品的总花费:
SELECT product_name,SUM(price) FROM order_menu GROUP BY class WITH ROLLUP;
--执行顺序:from-->where-->select-->group by-->having-->order by
select JS as JS成绩 from ExamResult where JS成绩 >70;--不成功
select JS as JS成绩 from ExamResult having JS成绩 >90;--成功
--使用limit限制最大显示记录数
SELECT <field> from <tab_name> limit <x>,<y>
--x:开始x条不显示,默认为0
--y:最多显示y条,默认为到结尾;只有1个参数时为y
--实例:
SELECT * from ExamResult limit 1;
SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
SELECT * from ExamResult limit 2,2;
--使用正则表达式查询
--效率低于LIKE
SELECT field_ FROM tab_name_ WHERE field_ REGEXP 表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu';
SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';