创建表
方式1:
drop table if exists demotest;
create table demotest(
id int(5) primary key not null auto_increment,
pname varchar(50),
age int(5),
sex char(2));
方式2:
CREATE TABLE `Test1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`class` varchar(2) NOT NULL,
`NAME` varchar(50) NOT NULL,
`SEX` varchar(2) NOT NULL,
PRIMARY KEY (`ID`));
插入数据,对于带自动增长的表,可用两种方法插入,如下:
INSERT INTO demotest VALUES ('','张哥',28,'男');
INSERT INTO demotest VALUES ('','张2',25,'男');
INSERT INTO demotest VALUES ('','张3',24,'女');
INSERT INTO demotest VALUES ('','张4',22,'女');
INSERT INTO demotest VALUES ('','张5',23,'女');
INSERT INTO demotest VALUES ('','张6',27,'男');
INSERT INTO demotest VALUES ('','张7',28,'女');
INSERT INTO demotest VALUES ('','张8',29,'男');
insert into demotest (pname,age,sex) VALUES ('张1',28,'男');
insert into demotest (pname,age,sex) VALUES ('张2',25,'女');
insert into demotest (pname,age,sex) VALUES ('张3',24,'女');
insert into demotest (pname,age,sex) VALUES ('张4',22,'男');
insert into demotest (pname,age,sex) VALUES ('张5',23,'女');
insert into demotest (pname,age,sex) VALUES ('张6',27,'男');
insert into demotest (pname,age,sex) VALUES ('张7',28,'女');
insert into demotest (pname,age,sex) VALUES ('张8',29,'女');
insert ignore into demotest (pname,age,sex) VALUES ('张8',29,'女');
说明:1、此例id是主键,自动增长,插入数据的时候,可以不对其进行直接插入
2、insert ignore into demotest (pname,age,sex) VALUES ('张8',29,'女'); #ignore表示忽略,对于有unique的字段如果有相同数据存在则不提示错误
查看表
use database;
show tables;
修改表:更改表名、列名、列的类型、添加字段、删除字段
alter table demotest change pname name char(6); #change更改列名并改变列的类型及长度
alter table demotest modify id int(3) #modify不能改变列名,在modify后面可以加column也可以不加
rename table oldName to newName #对表进行重新命名
alter table demotest add class varchar(10) not null first; #在表开头添加一个字段
alter table demotest add major varchar(10) not null; #在表结尾添加一个字段
alter table demotest add interest varchar(10) not null after major; #在某个字段之后添加一个字段
alter table demotest drop class; #删除表中的一个字段
alter table demotest add unique(pname); #添加唯一健约束
更新表
update demotest set pname='东哥' where id=25; #用where设定更新条件
update demotest set pname=null,age=null #如果把已定义为not null的更新为null,则该列被设置到与其类型匹配的默认值
update 表名 set 字段名 = replace(字段名,'旧内容','新内容'); #用replace修改字段某一行的内容
查询表
1、用各种操作符来设定检索条件
select * from score where score>95; #显示所有分数在95分以上的信息
select last_name,first_name from president where last_name='Tom'; #找出所有姓tom的总统
select last_name,first_name,birth,state from president
where birth
where子句中可以使用的是算术操作符(+-*/%),比较操作符(<>=)以及逻辑运算符
2、NULL 值的特别处理
select last_name,first_name,birth,state from president where death is null; #找出所有没死的总统
3、查询结果进行排序
select last_name,first_name,birth,state from presidentorder by last_name; #让总统们的名字按字母顺序排列
select last_name,first_name from president order by state DESC,last_name ASC;#desc降序,asc升序
注意:如果结果中含有NULL 值,默认情况下他们总是出现在查询结果的开头。
4、限制查询结果中数据行个数
select last_name,first_name,birth,state from president order by birth limit 5; #只想看前5个
order by birth limit 10,5; #返回从第11个记录开始的5个记录(跳过了10个)
小技巧:
select last_name,first_name,birth,state from president order by rand() limit 1; #这是用了表达式求值的方法,在哪里都管用
5、对输出列进行求值和命名
Select 17,format(sqrt(3*3+4*4),0)) #输出:17 5;format()格式化数据,format(num,d)表示截取数值num到D位小数,format(120.120,0)=120
select concat(first_namem,’·‘,last_name) as name,concat(city,’,’,state) as 'birth place' from president; #concat(str1,str2,…) 连接字符串
6、和日期有关的问题
select * from event where date=’2002-10-01’ #看看这天有何事?
select last_name,first_name,birth,state from president where death>’1900-01-01’ and death
select last_name,first_name,birth from president where month(birth)=7 and dayofmonth(birth) =6; #谁生在7月6日?
select last_name,first_name,birth to_days(death)-to_days(birth) as age from president #函数to_days可以把日期转换为天数。
select last_name,first_name,expiration from member where (to_days(expiration)-to_days(curdate())<60; #有些人60天内需要花钱了!
7、模式匹配
select concat(first_namem,’·‘,last_name) as name where last_name like(or not like) 'W%' #找到以W开关的多字名的人名
select concat(first_namem,’·‘,last_name) as name where last_name like(or not like) 'W_' #找到以W开头的双字人名
8、设置和使用SQL变量
select @birth:=birth from president where last_name =’adsltiger’; #执行完成后我们就就会有一个@birth变量可用
用一下试试:
select concat(first_namem,’·‘,last_name) as name from president where birth
9、生成统计信息
select distinct state from president #看看美国总统们都来自那些州?(重复的不计)distinct表示去除重复
select count(*) from president; #count(*)计算所有的,NULL也要;count(数据列名称) NULL值不计算在内。
select sex,count(*) from student group by sex; #分别统计性别不同的人数,group by 分类统计
select state,count(*) as count from president group by state order by count desc limt4; //看看出生总统最多的前四个州是哪几个?
注:除了count(),我们还用其他一些统计函数,如求出最小值的min(),求最大值的max(),求和的sum(),求平均值的avg(),在实际工作中,这些函数时经常用到的!
10、从多个表提取信息
select scroe.student_id,event_date,score.score,event.type from event,score
where event.date=’2003-09-12’ and event.event_id=score.event_id; #查询某一天内的学生们的考试成绩,用学号列出
select student.name,event.name,score.score,event.type from event,score,student
where event.date=’2003-09-12’ and event.event_id= score.event_id and scroe.student_id=student.student_id;
#查询某一天内的学生们的考试成绩,用姓名列出
select student.student_id,student_name,count(absence.date) as absences from student,absence
where student.student_id=absence.student_id
group by student.student_id; #查询一下缺席学生的名字,学号,缺席次数
满足特定条件的查询
算术比较运算符
SQL语句中的算术比较运算符主要包括=(等于)、>=(大于等于)、<=(小于等于)、>(大于)、(不等于)、!>(不大于)、!
字段 比较运算符 值
BETWEEN…AND运算符查询指定条件范围的记录
BETWEEN…AND运算符可以用来查询指定条件范围的记录。使用BETWEEN…AND运算符查询时在BETWEEN运算符和AND运算符后面都需要给定一个值。
其语法格式如下:
字段 BETWEEN 值1 AND 值2
其中,字段表示数据表中需要查询的字段;值1为给定数值中较小的值;值2为给定数值中较大的值。其最终查询的结果也包括值1和值2本身的值。
提示:SQL中,可以使用NOT BETWEEN AND运算符来排除一些记录。例如要查询教师信息表中年龄不在30~50之间的教师信息,就可以通过NOT BETWEEN AND来实现。
IN运算符查询与列表匹配的记录
IN运算符查询用来查询与列表匹配的记录。使用IN运算符,可以将满足列表中满足指定表达式的任何一个值都查询出来。IN运算符后的属性值可以是一个,也可以有多个,多个属性值之间需要要用逗号分隔。其语法格式如下:
字段 IN(属性值1, 属性值2, 属性值3…)
其中字段表示数据表中需要查询的字段;属性值1, 属性值2, 属性值3分别表示需要查询的值。属性值既可以是数字类型的也可以是字符类型的值。如果属性值是字符类型的值,则需要使用单引号将其引住。
字符串比较
在使用SQL语句进行比较查询时,经常会遇到字符串比较问题。对字符串进行比较时,常用的数据库都可以使用比较运算符对字符串进行比较,
另外,在MySQL数据库中还可以使用关键字BINARY对字符串进行二进制比较。
使用比较运算符对字符串进行比较时,比较运算符的左右两侧字符值应该用单引号引住。
SELECT 'mysql' = 'MySQL'
如果希望比较的字符串区分大小写,可以使用BINARY关键字对字符串进行二进制比较。使用BINARY关键字,会把一个字符串(数字)转换成一个二进制对象。格式如下:
SELECT string1 比较运算符 BINARY string2 或
SELECT BINARY string1 比较运算符 string2 示例如下:
select * from userbinary where username like BINARY'%ABC%'; #区分大小写的比较
日期时间的比较
在WHERE子句中对日期值和时间进行比较时,要比较的日期和时间必须是数据库服务器可以接受的字符串格式。例如,在学生信息表(T_student)中,学生的出生日期被设置为DATETIME日期类型的变量。要想在WHERE子句中对学生的出生日期值进行比较,可以使用单引号将该日期值引住。
逻辑查询
在SQL语句中逻辑运算符主要包括AND、OR和NOT三种。其中AND运算符用来查询同时满足多个条件的记录,OR运算符用来查询多个条件中满足其中任一个条件的记录,NOT运算符用来查询满足相反条件的记录。
使用AND运算符查询同时满足多个条件的记录
在SQL的执行操作中,很多情况下,WHERE子句并不是只希望满足一个条件,而是希望最终查询的结果必须同时满足多个条件(两个或者两个以上)。这个时候就需要使用AND运算符。其语法格式如下:
条件1 AND 条件2
其中条件1,条件2是在WHERE子句中进行查询时需要满足的条件。如果希望使用AND运算符在WHERE子句中连接多个条件。可以使用下面的语法格式。
条件1 AND 条件2 AND条件3 …
这里使用两个AND运算符来连接3个条件。多个AND运算符进行连接操作时,每一个AND运算符两侧的值必须都为TRUE,也就是说这些条件都同时被满足的情况下,结果才会被显示出来。
说明:在Microsoft SQL Server数据库中,使用“&”符号代替AND运算符表示逻辑与运算。
使用OR运算符查询满足任一条件的记录
在使用SQL进行查询操作时,有些时只是希望查询的结果中满足多个条件中的任一条件即可。这个时候就需要使用OR运算符。使用OR运算符可以用来查询满足任一条件的记录。其 语法格式如下:
条件1 OR条件2
其中条件1,条件2是在WHERE子句中进行查询时需要的条件。这两个条件中,只要符合其中任何一个条件,则符合该条件的记录就会被检索出来。如果希望使用OR运算符在WHERE子句中连接多个条件。可以使用下面的语法格式
条件1 OR条件2 OR条件3 …
这里使用两个OR运算符来连接3个条件。多个OR运算符进行连接操作时,两侧的条件中任何一个条件为TRUE,满足该条件的记录就会被显示出来。
说明:在Microsoft SQL Server数据库中,使用“|”符号代替OR运算符表示逻辑与运算。
使用NOT运算符对查询条件的布尔值求反
有些时候,需要查询不满足指定条件的记录,这个时候就需要使用NOT运算符。NOT运算符对查询条件的布尔值求反。
说明:在Microsoft SQL Server数据库中,使用“~”符号代替NOT运算符表示逻辑与运算。
这3个逻辑运算符也可以放到一个SQL语句中混合使用。在这3个逻辑运算符中,NOT的优先级最高,AND的优先级要高于OR。为了便于理解,一般在混合使用这3个逻辑运算符时,可以使用括号将每一个部分括起来。
空值查询
在使用SQL语句执行查询操作时,还有一种是空值查询。在数据表中,如果一个表的行属性中不存在任何值的时候,也就是说该表的行属性中没有任何数据记录。那么就将其称之为空值。在SQL的查询中,NULL可以用来表示空值的含义。在SQL语句中,可以使用IS NULL或者IS NOT NULL关键字来判断空值。
删除表及内容
use database;
show tables;
drop table tablename; #一次性删除,彻底干净,删除整个表,内容、定义统统不保留
truncate table tablename; #一次性删除内容,但是保留表定义
delete from tablename where columnName='columnData' ; #逐条删除数据
关于表的基本操作先更新到这里,如后续有要加的内容则继续更新