新增表
语法:
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
)[character set 字符集] [collate 校对规则]
field:指定列名 datatype:指定列类型
注意: 创建表时,要根据需保存的数据创建相应的列,并根据数据的类型定义相应的列类型。
创建表时,一般不需要指定字符集编码和校对规则,和数据库保持一致即可。
练习:
~创建employee表,表中字段信息如下:
建表语句1:
create table employee(
id int,
name varchar(50),
gender char(1),
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text
);
建表语句2:
create table employee(
id int primary key auto_increment,
name varchar(50) unique,
gender bit not null,
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text
);
查看表
语法:
查看表结构:
desc tabName
查看当前所有表:
show tables
查看当前数据库表建表语句
show create table tabName;
修改表
语法:
增加列:
ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype...];
修改列:
ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]...);
删除列:
ALTER TABLE tabname DROP [COLUMN] col_name;
修改表名:
ALTER TABLE old_tabname RENAME TO new_tabname;
或
RENAME TABLE old_tabname TO new_tabname;
修改列名称:
ALTER TABLE tabname CHANGE [COLUMN] old_col_name new_col_name datatype;
修改列的顺序:
ALTER TABLE tabname MODIFY col_name1 datatype AFTER col_name2;
修改表的字符集:
ALTER TABLE tabname CHARACTER SET character_name;
练习:
~在上面员工表的基础上增加一个image列。
alter table employee add image blob;
~修改job列,使其长度为60。
alter table employee modify job varchar(60);
~删除gender列。
alter table employee drop gender;
~表名改为user。
alter table employee rename to user;
或
rename table user to employee;
~列名name修改为username
alter table employee change name username varchar(100);
~将image插入到id列的后面
alter table employee modify image blob after id;
~修改表的字符集为GBK
alter table employee character set gbk;
删除表
语法: DROP TABLE tabname;
练习: ~删除employee表
drop table employee;
利用sql语句来操作数据库表记录
-INSERT
语法:
INSERT INTO tabname [(column [, column...])] VALUES (value [, value...]);
注意:
* 插入的数据应与字段的数据类型相同
例如: name列对应的值为字符串, 插入的数据类型也应该是字符串类型!
* 数据的大小应在列的规定范围内
例如: name列字符长度为10, 那么插入的数据长度不能超过10!
* 在values中列出的数据位置必须与被对应列的排列位置相对应
* 字符串和日期格式的数据要用单引号引起来
例如: '张飞', '2017-6-1'
*如果要插入所有字段可以省写列列表,直接按表中字段声明的顺序写值列表
练习:
~向员工表中插入三条数据
insert into employee (id, name, gender, birthday, entry_date, job, salary, resume) values(null, '刘备', '男', '1876-1-1', '1902-1-1', 'CEO', 100000, '很牛!!!');
insert into employee values(null, '张飞', '男' , '1879-1-1', '1905-1-1', 'java工程师', 20000, '还行吧!');
insert into employee values(null, '小乔', '女' , '1882-1-1', '1906-1-1', '程序员鼓励师', 40000, '美女!');
** 在MySQL中插入中文数据,或查询中文数据时的乱码问题:
**乱码问题:客户端发送的数据是GBK的,而服务器使用的utf-8的编码来处理客户端发来的数据,两端使用的编码不一致导致乱码。
**解决方法1:在客户端可以通过 set names gbk; 通知服务器使用指定码表来处理客户端发送的数据。这种方式只对当前cmd命令窗口有效,每次新开的窗口都要设置一次。
**解决方式2:可以通过修改MySQL安装目录下的my.ini文件(57行)中的配置,来指定服务器端使用的码表。这种方式一劳永逸。(注意: 配置完成后需要重启服务器!!);
查询数据库中编码: show variables like 'char%';
-UPDATE
语法:
UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。
练习:
~将所有员工薪水修改为30000元。
update employee set salary=30000;
~将姓名为’貂蝉’的员工薪水修改为11000元。
update employee set salary=11000 where name='貂蝉';
~将张飞的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name=’张飞’;
注意:mysql不支持+=符号
-DELETE
语法:
DELETE FROM tab_name [WHERE where_definition]
where用来筛选要删除的记录,如果不使用where子句,将删除表中所有数据。
delete语句不能删除某一列的值
delete语句仅删除记录,不删除表本身。如要删除表,使用drop table语句。
练习:
~删除表中名称为’张飞’的记录。
delete from employee where name='张飞';
~删除表中所有记录。
delete from employee;
-SELECT
基本的查询
语法:
SELECT [DISTINCT] * | {column1, column2. column3..} FROM table_name;
select 指定查询哪些列的数据。
column指定列名。
* 号代表查询所有列。
from指定查询哪张表。
DISTINCT可选,指显示结果时,是否剔除重复数据
练习:
执行下面的SQL,创建exam表并插入数据
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);
insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);
insert into exam values(null,'张三丰',85,79,null);
~查询表中所有学生的信息。
select * from exam;
~查询表中所有学生的姓名和对应的语文成绩。
select name, chinese from exam;
~过滤表中重复数据。
select distinct chinese from exam;
~在所有学生分数上加10分特长分显示。
select name,chinese+10,math+10,english+10 from exam;
或者
select name,chinese+10 as 语文, math+10 as 数学, english+10 英语 from exam;###通过as关键词指定别名, as可以省略不写!
或者
select name,chinese+10 '语文', math+10 数学, english+10 英语 from exam;###通过as关键词指定别名, as可以省略不写!
~统计每个学生的总分。(ifnull(colname, 0))
select name, chinese+math+english 总分 from exam;
select name, chinese+math+ifnull(english, 0) 总分 from exam;
使用where子句的查询
语法:
SELECT * | 列名 FROM tablename [WHERE where_definition]
练习:
~查询姓名为关羽的学生成绩
select * from exam where name='关羽';
~查询英语成绩大于90分的同学
select * from exam where english>90;
~查询总分大于230分的所有同学
select name, chinese+math+ifnull(english, 0) 总分 from exam where chinese+math+ifnull(english, 0)>230;###在where子句中不能使用列别名!!!
~查询语文分数在 80~100之间的同学。
select * from exam where chinese>80 and chinese <100;
或者
select * from exam where chinese between 80 and 100;#包括80和100
~查询数学分数为75,76,77的同学。再查询分数不在这个范围内的同学
select * from exam where math=75 or math=76 or math=77;
select * from exam where !(math=75 or math=76 or math=77);
select * from exam where not(math=75 or math=76 or math=77);
或者
select * from exam where math in(75, 76, 77);
select * from exam where math not in(75, 76, 77);
~查询数学分>70,语文分>80的同学。
select * from exam where math>70 and chinese>80;
like: 模糊查询
百分号(%): 匹配0个或者多个字符。 下划线(_):匹配一个字符
select * from exam where name like '张%';
select * from exam where name like '张_';
select * from exam where name like '%三%';
排序查询
语法:
SELECT column1, column2, column3.. FROM tablename order by column asc|desc;
Order by 指定排序的列,排序的列即可是表中的列名,也可以是select 语句后指定的列名。
Asc 升序(默认)、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。
练习:
~对数学成绩排序后输出。
select math from exam order by math;
~对总分排序按从高到低的顺序输出
select name, chinese+math+ifnull(english, 0) 总分 from exam order by 总分 desc;
~对姓张的学生总分排序输出
select name, chinese+math+ifnull(english, 0) 总分 from exam where name like '张%' order by 总分;
聚合函数
语法:
求符合条件的记录中指定列的记录数
select count(列名)… from tablename [WHERE where_definition]
求符合条件的记录中指定列的和值
select sum(列名)… from tablename [WHERE where_definition]
求符合条件的记录中指定列的平均值
select avg(列名)… from tablename [WHERE where_definition]
求符合条件的记录中指定列的最大值
select max(列名)… from tablename [WHERE where_definition]
求符合条件的记录中指定列的最小值
select min(列名)… from tablename [WHERE where_definition]
练习:
~统计一个班级共有多少学生?
select count(*) from exam;
~统计数学成绩大于75的学生有多少个?
select count(*) from exam where math>75;
~统计总分大于230的人数有多少?
select count(*) from exam where chinese+math+ifnull(english, 0)>230;
~统计一个班级数学总成绩?
select sum(math) 数学总成绩 from exam;
~统计一个班级语文、英语、数学三科成绩的总和
select sum(math)+sum(chinese)+sum(ifnull(english, 0)) 总成绩 from exam;
或者
select sum(chinese+math+ifnull(english, 0)) from exam;
~统计一个班级英语成绩平均分
select avg(english) from exam;
~求一个班级总分平均分?
select avg(chinese+math+ifnull(english, 0)) from exam;
~求班级总分最高分和最低分
select max(chinese+math+ifnull(english, 0)) from exam;
select min(chinese+math+ifnull(english, 0)) from exam;
分组查询
语法:
SELECT column1, column2. column3.. FROM tablename group by column having ...
练习:
执行下面的SQL,创建orders表并插入数据
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'小米手机',900);
insert into orders(id,product,price) values(2,'奥妙洗衣液',60);
insert into orders(id,product,price) values(3,'乐视TV',90);
insert into orders(id,product,price) values(4,'联想键盘',80);
insert into orders(id,product,price) values(5,'奥妙洗衣液',60);
insert into orders(id,product,price) values(6,'小米手机',900);
~对订单表中商品归类后,显示每一类商品的总价
select * from orders group by product;
select count(*) from orders group by product;
select sum(price) from orders group by product;
~查询购买了几类商品,并且每类总价大于100的商品
select product,sum(price) 总价 from orders group by product having sum(price)>100;
~查询单价小于100而总价大于100的商品的名称.
select * from orders where price<100 group by product having sum(price) > 100;