mysql的基础语法&例子

即数据的有效性和准确性

数据库操作:
(1)操作数据库 -> 操作表 ->操作数据
<1>操作数据库的命令:
1)显示当前所有的数据库:show databases;
2)创建数据库:
create database 数据库名;
3)删除数据库:drop database 数据库名;
4)切换数据库:use 数据库名;
<2>创建数据表:
表的三要素:<1>表名<2>列名<3>数据类型
注:表名和列名都是自定义的,但是不要用中文或者纯数字命名
数据类型:
(1)整数:int(常用),smallint，bigint
(2)小数:double(常用),float
(3)字符串:char(长度)-----固定长度
varchar(长度)----可变长度
示例：
char(5)：‘abc__’ 长度为5
varchar(5):‘abc’ 长度为3(最大到5)
(4)日期类型：
date(年月日),time(时分秒),datetime(年月日时分秒)
(5)大数据类型(视频,声音,图片):clob,blob
(6)大文本类型:text(存放小说)
创建表的语法：
create table 表名(
列名 数据类型,
列名2 数据类型2
);
注意:多个列之间使用逗号间隔,最后一列的逗号省略
示例:创建学生信息表:
学号 varchar(10)
姓名 varchar(10)
性别 char(1)
身高 int
出生年月 date
班级名称 varchar(5)
create table studentinformation(
stuno varchar(10),
stuname varchar(10),
sex char(1),
height int,
birthday date,
classname varchar(5)
);

显示所有表:show tables;

<3>添加数据
insert into 表名(列名) values(列值);
注:多个列名和列值之间使用逗号间隔
添加值时,如果值所在的列是字符串或日期类型,则需要在值的两侧加单引号
列值和列名要一一对应
示例：
insert into studentinformation(stuname,sex,height,birthday)
values(‘李四’,‘男’,180,‘1989-1-1’);
<4>查看表中数据:
select * from 表名;

create table emoloyee(
empid varchar(4),
name varchar(5),
sex char(1),
title varchar(5),
birthday date,
depid char(3)
);

create table department(
depid char(3),
depname varchar(5)
);

create table salary(
empid varchar(4),
basesalary int,
titlesalary int,
deduction int
);

insert into emoloyee(empid,name,sex,title,birthday,depid)
values(‘1001’,‘张三’,‘男’,‘高级工程师’,‘1975-1-1’,‘111’);

---

修改表结构:
<1>查看表结构:
基本表结构:desc 表名;
详细表结构:show create table 表名;
<2>修改表结构:
(1)修改表名：
alter table 旧表名 rename 新表名;
(2)修改列:
alter table 表名 change 旧列名 新列名 新数据类型;
(3)添加列:
alter table 表名 add 新列名 新数据类型;
(4)删除列:
alter table 表名 drop 列名;
(5)删除表:
drop table 表名;

练习：
表名 card(会员卡表)
列名 数据类型
cardid int
cardnum varchar(20)
regDate date
需求：
(1)创建该表
create table card(
cardid int,
cardnum varchar(20),
regDate date
);
(2)将card表名修改为CardInfo
alter table card rename cardinfo
(3)添加delDate(注销时间) 列到表中
alter table cardinfo add delDate date;
(4)将cardnum改为varchar(30),必填列
alter table cardinfo change cardnum cardnum varchar(30);
(5)删除regDate列
alter table cardinfo drop regDate;
(6)删除cardInfo表
drop table cardinfo;

完整性:指数据的准确性和有效性
保证数据有效性的方式(约束条件):
注：约束条件是在创建表的时候添加
语法:
create table 表名(
列名 数据类型 [约束条件],
列名 数据类型 [约束条件]
);
约束条件:
(1)主键约束:primary key
主键列:设置了主键的列
特点:不允许重复,非空(不允许为null/必填)
注:通常情况下,每张表都会有一列主键列
(2)自增长: auto_increment
注:适用于int类型的列,和主键列一起使用
(3)非空约束:not null
特点:不允许为null(必填)
(4)默认值:default
语法: default 默认值 //默认值如果是日期或字符，需要加单引号
注:当用户不指定值的时候,执行默认值
(5)唯一约束:unique
特点:列值不允许重复

create table student(
studentno varchar(50) primary key,
loginPwd varchar(20) not null default ‘123456’,
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default ‘学生宿舍’,
borndate datetime,
email varchar(50)
);

sql中的运算符:
（1）算术运算符:+，-，*，/(除法),求余(%)
示例：5/2=2
5%2=1
2/5=0
2%5=2
（2）赋值运算符:=
注：赋值方向：从右往左赋值
示例： name=‘张三’
(3) 逻辑运算符：
and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
(4) 关系运算符：
>,<,>=,<=,!=(不等于),=(等于),<>(不等于)
数据操作语句:
操作:增(insert)删改查 ---->操作表中的数据
添加列，删除列:alter ---->操作表的结构

<1>添加(insert):
单行添加:
insert into 表名(列名) values(列值);
注:当插入的值是所有列的值时,那么列名可以省略,列值的顺序要和
创建表时列的顺序一致
多行插入:
insert into 表名(列名) values(列值),(列值);
注:多组列值之间使用逗号间隔
每组列值表示一行数据
<2>更新数据(修改数据):
update 表名 set 列名=列值 where 限定条件
//1.将所有用户(users)的密码(pass)改成123
update users set pass=‘123’
//2.将张三(users)的年龄(age)改成30
update users set age=30 where name=‘张三’
//3.将男性张三的年龄改为40
update users set age=40 where name=‘张三’ and sex=‘男’
//4.更新多列：
将男性张三的年龄改为40,性别改为女
update users set age=40,sex=‘女’ where name=‘张三’ and sex=‘男’
小结:多列之间使用逗号间隔,多个条件之间使用逻辑运算符间隔

Manager(管理员表)：
mid 编号 int （主键）
mname 名字 varchar(20)
age 年龄 int
sex 性别 char(2)
password 密码 varchar(20)
address 地址 varchar(20)
phone 电话 varchar(20)
数据：
1 王子 18 男 123 北京 110
2 公主 20 女 456 上海 220
3 太子 23 男 789 南京 330
需求：
(1)创建表
create table manager(
mid int primary key,
mname varchar(20),
age int,
sex char(2),
password varchar(20),
address varchar(20),
phone varchar(20)
);
(2)将数据插入到表中
insert into manager
values(1,‘王子’,18,‘男’,‘123’,‘北京’,‘110’),(),();
(3)将王子的年龄修改为24
update manager set age=24 where mname=‘王子’;
(4)将地址是南京的管理员改为天津
update manager set address=‘天津’ where address=‘南京’;
(5)将性别是女,并且年龄大于30的用户密码改为888888
update manager set password=‘888888’
where sex=‘女’ and age>30
(6)将所有用户的密码恢复最初设置111111
update manager set password=‘111111’
(7)将员工的电话中不是110的电话号码改为7654321
update manager set phone=‘7654321’
where phone!=‘110’
(8)将王子的年龄改为18，地址改为承德，性别改为女
update manager set age=18,address=‘承德’,sex=‘女’
where name=‘王子’

<3>删除数据：
delete from 表名 where　　限定条件
　 //删除名字是张三的用户信息
delete from users where name=‘张三’;
//多条件:删除男性张三的信息
delete from users where sex=‘男’ and name=‘张三’;

练习: (9) 删除王子的信息
delete from manager where mname=‘王子’;
(10)删除地址在南京并且年龄大于60的员工信息
delete from manager where address=‘南京’ and age>60
(11)删除不在北京的员工信息
delete from manager where address!=‘北京’
(12)删除地址在北京或上海的员工信息
delete from manager where address=‘北京’ or address=‘上海’
(13)删除电话号码是空的员工信息
delete from manager where phone is null or phone=’’
小结:
为空的条件：列名 is null or 列名=’’
注:两个单引号表示空字符串

日期类型值的区别:
date：yyyy-MM-dd （年月日）
time：hh:mm:ss (时分秒)
datetime:yyyy-MM-dd hhv:mm:ss (年月日时分秒)
获取当前系统时间:now()
<4>查询:
select 列名
from 表名
[ where 限定条件]
[ order by 排序的列 asc/desc ]
说明:(1)当查询所有列时，使用*代替。
(2)当查询某几列时,多个列名之间使用逗号间隔
(3)order by 是用于排序，asc-升序 desc–降序
操作1:给列起别名: 列名 as 别名
操作2:查询null列: 列名 is null
操作3:常量列: 列值 as 列名
示例：select name,java,mysql,java+mysql as ‘总分’
from score
使用场合:当需要在虚拟表中添加新列时使用
操作4:分页语句：
limit 开始位置,显示条数 //开始位置从0开始
– 查询学生表中前3条数据
select * from student limit 0,3
每页显示5条
第一页: limit 0,5
第二页: limit 5,5
第三页: limit 10,5
1.位置=第几条数据-1
2.pageIndex–页码 pageSize–显示条数
limit (pageIndex-1)*pageSize,pageSize
示例：//查询分数表中java成绩前三名的学生信息
select * from score
order by java desc
limit 0,3

练习:
查询的列? 限定条件?
(14)查询公主的所有信息
select * from manager where mname=‘公主’
(15)查询年龄在18-30之间的管理员姓名
select mname from manager where age>=18 and age<=30
(16)查询表中所有的用户名和电话
select mname,phone from manager
(17)查询性别是男，名字是王子的个人信息
select * from manager where sex=‘男’ and mname=‘王子’
(18)查询出地址在北京或上海的员工信息
select * from manager where address=‘北京’ or address=‘上海’

scores
stuid int 学生id
java int java成绩
mysql int mysql成绩
stuname varchar(20) 学生姓名
数据：
1 67 78 张三
2 87 55 李四
3 66 90 王五
4 98 78 赵六
5 80 88 田七
需求：
(1)对java成绩进行降序排序
select * from scores order by java desc
(2)得到mysql成绩前三名
select * from scores order by mysql desc limit 0,3
(3)得到java学生中最后一名的学生信息
select * from scores order by java asc limit 0,1
(4)查询出两门成绩都优秀(>=80)的学生姓名
select stuname from scores where java>=80 and mysql>=80
(5)查询出成绩在90分以上(>=90)的学生信息
select * from scores where java>=90 or mysql>=90
(6)查询出每名学员的java,mysql,总成绩
select java,mysql,java+mysql as ‘总分’ from scores;
select *,java+mysql as ‘总分’ from scores;
(7)显示出每名学生的总分以及姓名
select stuname,java+mysql as ‘总分’ from scores;