sql笔记-1

一.数据库

1.创建数据库

create database [if not exists] db_name [character set xxx] [collate xxx]

*创建一个名为mydb1的数据库。

create database mydb1;

*创建一个使用utf-8字符集的mydb2数据库。

create database mydb2 character set utf8;

*创建一个使用utf8字符集，并带校对规则的mydb3数据库

create database mydb3 character set utf8 collate utf8_bin;

2.查看数据库

show databases; 查看所有数据库

show create database db_name; 查看数据库的创建方式

3.修改数据库

alter database db_name [character set xxx] [collate xxxx]

4.删除数据库

drop database [if exists] db_name;

5.使用数据库

切换数据库    use db_name;

查看当前使用的数据库  select database();


二.表

1.创建表

           create table tab_name(

                    field1 type，

                    field2 type,

                     ...

                    field3 type

                      )[character set xxx] [collate xxx];

****java和mysql的数据类型比较

String  ----------------------  char(n) varchar(n) 255  65535

byte short int long float double -------------- tinyint  smallint int bigint float double

boolean ------------------ bit 0/1

Date ------------------ Date、Time、DateTime、timestamp

FileInputStream FileReader  ------------------------------ Blob Text

*创建一个员工表employee

create table employee(

id int primary key auto_increment ,

name varchar(20),

gender bit default 1,

birthday date,

entry_date date,

job varchar(20),

salary double,

resume text

);

约束:

primary key

unique

not null

auto_increment 主键字段必须是数字类型。

外键约束

2.查看表信息

desc tab_name 查看表结构

show tables 查看当前数据库中的所有的表

show create table tab_name 查看当前数据库表建表语句

3.修改表结构

(1)增加一列

alter table tab_name add [column] 列名 类型;

(2)修改一列类型

alter table tab_name modify 列名 类型;

(3)修改列名

alter table tab_name change [column] 列名 新列名 类型;

(4)删除一列

alter table tab_name drop [column] 列名;

(5)修改表名

rename table 表名 to 新表名;

(6)修该表所用的字符集

alter table student character set utf8;

4.删除表

drop table tab_name;

三.表记录

1.增加一条记录insert

insert into tab_name (field1,filed2,.......) values (value1,value2,.......);

*插入的数据应与字段的数据类型相同。

*数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

*在values中列出的数据位置必须与被加入的列的排列位置相对应。

*字符和日期型数据应包含在单引号中'zhang' '2013-04-20'

*插入空值：不指定某列的值或insert into table value(null)，则该列取空值。

*如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表insert into tab_name values(value1,value2,......);

*练习：使用insert语句向表中插入三个员工的信息。

insert into emp (name,birthday,entry_date,job,salary) values ('张飞','1990-09-09','2000-01-01','打手',999);

insert into emp (name,birthday,entry_date,job,salary) values ('关羽','1989-08-08','2000-01-01','财神',9999);

insert into emp (name,birthday,entry_date,job,salary) values ('赵云','1991-07-07','2000-01-02','保安',888);

insert into emp values (7,'黄忠',1,'1970-05-05','2001-01-01','战神',1000,null);

2.修改表记录

update tab_name set field1=value1,field2=value2,......[where 语句]

*UPDATE语法可以用新值更新原有表行中的各列。

*SET子句指示要修改哪些列和要给予哪些值。

*WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

*实验：

将所有员工薪水修改为5000元。

update emp set salary=5000;

将姓名为’zs’的员工薪水修改为3000元。

update emp set salary=3000 where name='zs';

将姓名为’ls’的员工薪水修改为4000元,job改为ccc。

update emp set salary=4000,job='ccc' where name='zs';

将wu的薪水在原有基础上增加1000元。

update emp set salar=salary+4000 where name='wu';

3.删除表操作

delete from tab_name [where ....]

*如果不跟where语句则删除整张表中的数据

*delete只能用来删除一行记录，不能值删除一行记录中的某一列值（这是update操作）。

*delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop

*同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

*TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在事务中恢复。

*实验：

删除表中名称为’zs’的记录。

delete from emp where name='黄忠';

删除表中所有记录。

delete from emp;

使用truncate删除表中记录。

truncate table emp;

4.select操作

（1）select [distinct] *|field1，field2，......  from tab_name 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列列表明确指定要查找的列，distinct用来剔除重复行。

*实验：

查询表中所有学生的信息。

select * from exam;

查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

过滤表中重复数据。

select distinct english from exam;

（2）select 也可以使用表达式，并且可以使用 as 别名

在所有学生分数上加10分特长分显示。

select name,english+10,chinese+10,math+10 from exam;

统计每个学生的总分。

select name,english+chinese+math from exam;

使用别名表示学生总分。

select name,english+chinese+math as 总成绩 from exam;

select name,english+chinese+math 总成绩 from exam;

select name english from exam;

（3）使用where子句，进行过滤查询。

*练习：

查询姓名为XXX的学生成绩

select * from exam where name='张飞';

查询英语成绩大于90分的同学

select name,english from exam where english>90;

查询总分大于200分的所有同学

select name,math+english+chinese as 总成绩 from exam where math+english+chinese>200 ;

*where字句中可以使用：

*比较运算符：

> < >= <= <>

between 10 and 20 值在10到20之间

in(10,20,3)值是10或20或30

like '张pattern' pattern可以是%或者_，如果是%则表示任意多字符，此例中张三丰 张飞 张abcd ，如果是_则表示一个字符张_，张飞符合。张

Is null

*逻辑运算符

在多个条件直接可以使用逻辑运算符 and or not

*实验

查询英语分数在 80－100之间的同学。

select name ,english from exam where english between 80 and 100;

查询数学分数为75,76,77的同学。

select name ,math from exam where math in (75,76,77);

查询所有姓张的学生成绩。

select * from exam where name like '张%';

查询数学分>70，语文分>80的同学。

select name from exam where math>70 and chinese >80;

查找缺考数学的学生的姓名

select name from exam where math is null;

（4）Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。

Asc 升序、Desc 降序，其中asc为默认值

ORDER BY 子句应位于SELECT语句的结尾。

*练习：

对数学成绩排序后输出。

select * from exam order by math;

对总分排序按从高到低的顺序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam order by 总成绩 desc;

对姓张的学生成绩排序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam where name like '张%' order by 总成绩 desc;

（5）聚合函数：技巧,先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。

count(列名)

统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(*) from exam;

统计数学成绩大于70的学生有多少个？

select count(math) from exam where math>70;

统计总分大于250的人数有多少？

select count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>250;

sum(列名)

统计一个班级数学总成绩？先查出所有的数学成绩，再用sum包上

select sum(math) from exam;

统计一个班级语文、英语、数学各科的总成绩

select sum(math),sum(english),sum(chinese) from exam;

统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 总成绩 from exam;

统计一个班级语文成绩平均分

select sum(chinese)/count(*) from exam ;

注意：sum仅对数值起作用，否则会报错。

AVG(列名)：

求一个班级数学平均分？先查出所有的数学分，然后用avg包上。

select avg(ifnull(math,0)) from exam;

求一个班级总分平均分

select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;

Max、Min

求班级最高分和最低分（数值范围在统计中特别有用）

select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

（6）group by字句，其后可以接多个列名，也可以跟having子句对group by 的结果进行筛选。

练习：对订单表中商品归类后，显示每一类商品的总价

select product，sum(price) from orders group by product;

练习：查询购买了几类商品，并且每类总价大于100的商品

select product，sum(price) from orders group by product having sum(price)>100;

!~having 和 where 的差别： where语句用在分组之前的筛选，having用在分组之后的筛选，having中可以用合计函数，where中就不行。使用where的地方可以用having替代。

练习:查询商品列表中除了橘子以外的商品，每一类商品的总价格大于500元的商品的名字

select product,sum(price) from orders where product<>'桔子'group by product having sum(price)>500;

（7）重点：Select from where group by having order by

Mysql在执行sql语句时的执行顺序：from where select group by having order by

*分析：

select math+english+chinese as 总成绩 from exam where 总成绩 >250; ---- 不成功

select math+english+chinese as 总成绩 from exam having 总成绩 >250; --- 成功

select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; ----成功

select  math+english+chinese as 总成绩 from exam order by 总成绩;----成功

select * from exam as 成绩 where 成绩.math>85; ---- 成功

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,560评论 4赞 361
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,104评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,297评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,869评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,275评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,563评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,833评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,543评论 0赞 197
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,245评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,512评论 2赞 244
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,011评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,359评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,006评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,062评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,825评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,590评论 2赞 273
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,501评论 2赞 268

推荐阅读更多精彩内容

  • MySql使用
    1.操作数据库 (1)创建数据库 CREATE DATABASE [IF NOT EXISTS] db_name ...
    newdolphintime阅读 755评论 0赞 1
  • sql
    50个常用的sql语句Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname...
    哈哈海阅读 1,207评论 0赞 7
  • SQL语句大全
    50个常用的sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cna...
    最美的太阳WW阅读 3,110评论 0赞 23
  • MySql数据库基本语法
    数据库操作 cmd命令进入数据库 MySQL-u root -p 创建数据库 Create database [i...
    luweicheng24阅读 324评论 0赞 1
  • 虐菜
    “好啦好啦，别说啦，知道你今天吃了甲餐自我感觉良好，马上要上课了，走吧。”舞长空今天穿了一条灰白黑三色相间的格子裤...
    苏浩樱丶淡殇阅读 598评论 0赞 2