BusSysytem mysql

-- 创建数据库   BusSystem
drop database if exists BusSystem;
create database BusSystem;
use BusSystem;
-- 创建用户表
drop table if exists UserInfo;
create table UserInfo(
userId int not null PRIMARY key auto_increment,
nickname varchar(10),
userPass char(20) not null,
userSex char(2) ,
userTel char(11) not null unique,
userEmail varchar(20),
userAddress longtext,
userStatus int ,  --  1正常   2  禁用
regTime datetime
);
 use bussystem;
--  添加用户
select * from UserInfo where userStatus=1;
insert into UserInfo (nickname,userPass,userSex,userTel,userEmail,userAddress,userStatus,regTime) values('nick','123456','男','13678909876','','',1,now());
insert into UserInfo (nickname,userPass,userSex,userTel,userEmail,userAddress,userStatus,regTime) values('jn','123456','女','13678909876','','',1,now());
insert into UserInfo (nickname,userPass,userSex,userTel,userEmail,userAddress,userStatus,regTime) values('Joe','123456','男','13678909876','','',2,now());
update UserInfo set nickname="nn",userPass="123123",userSex="女",userTel="13456789098",userEmail="126@126.com",userAddress="" where userId=1;

update UserInfo set userPass='000000' where userId=5;

-- 留言表
drop table if exists LeftMessage;
create table LeftMessage(
id int not null PRIMARY key auto_increment,
userId int not null references UserInfo(userId),
content longtext,
leftTime datetime
);
--  添加留言
select * from LeftMessage , userinfo where leftmessage.userId=userinfo.userId and userinfo.userId=1;
insert into LeftMessage (userId,content,leftTime) values(1,"写什么呢",NOW());
insert into LeftMessage (userId,content,leftTime) values(1,"走过一定要留下痕迹",NOW());

update LeftMessage a  , userinfo b   set b.userTel='1356788' , b.nickname="夏利" , a.content="oo" where a.userId=b.userId and a.userId=1;

delete from LeftMessage where id=2;
select * from LeftMessage , userinfo where leftmessage.userId=userinfo.userId limit 1;
select COUNT(*) from LeftMessage , userinfo where leftmessage.userId=userinfo.userId;


-- 管理员表
drop table if exists admin;
create table admin(
id int not null PRIMARY key auto_increment,
adminPass varchar(8) not null,
adminName varchar(20) not null,
adminSex char(2),
adminTel char(11),
adminEmail varchar(100),
adminRemark longtext,
regTime datetime
);
-- 添加输
insert into admin (adminPass,adminName,adminSex,adminTel,adminEmail,regTime) values('123456','admin','女','13678909876','123456@126.com',now());
insert into admin (adminPass,adminName,adminSex,adminTel,adminEmail,regTime) values('123456','root','男','17678906756','123@163.com',now());

select * from admin where adminName="admin" and adminPass="123456";
update admin set adminPass="111111",adminName="jn",adminSex="男",adminTel="",adminEmail="",adminRemark="管理员" where id=1;


--    新闻表
drop table if exists news;
create table news(
newsId int not null primary key auto_increment,
newsType int not null,-- 0 网站公告    1 行业新闻
newsTitle varchar(50) not null,
newsSummary longtext,
newsContent longtext not null,
newsAuthor varchar(20),
newsImage varchar(100),
newsTime datetime,
newsStatus int  --  0 草稿  1  已发布  2  已下架
);
select * from news where newsId=1;
insert into news(newsType,newsTitle,newsSummary,newsContent,newsAuthor,newsTime,newsStatus)values(0,'客车管家会员服务协议','您在享用客车管家会本协议所称客车管家：（域名为：chebada.com），指为客车管家会员提供汽车票、火车票、机票、用车、酒店、景点门票等商品的综合性旅游出行网站，包括客户端、微信公众号、小程序和网站。','管理员1',NOW(),0);
insert into news(newsType,newsTitle,newsSummary,newsContent,newsAuthor,newsTime,newsStatus)values(0,'客车管务协议','的各类信息服务。','客车管','管理员1',NOW(),0);
select * from news where newsId=1;
update news set newsType=1,newsTitle='12',newsSummary='',newsContent='',newsAuthor='' where newsId=1;
delete from news where newsId=2;


--   票务班车信息表
drop table if exists ticketInfo;
create table ticketInfo(
id int not null primary key auto_increment,
carName varchar(20) ,
startPosition varchar(50) not null,
endPosition varchar(50) not null,
startTime datetime not null,
endTime datetime not null,
totalTicket int not null,
soldTicket int not null,
ticketPrice  int not null,
ticketStatus int ,  --  1正常   2  禁用
addTime datetime
);
--  添加票务信息
insert into ticketInfo (carName,startPosition,endPosition,startTime,endTime,totalTicket,soldTicket,ticketPrice,ticketStatus,addTime) values("B0001","郑州","北京","2020.7.1 12:00","2020.7.1 16:00",50,10,100,1,now());
insert into ticketInfo (carName,startPosition,endPosition,startTime,endTime,totalTicket,soldTicket,ticketPrice,ticketStatus,addTime) values("B0002","郑州","上海","2020.7.1 12:00","2020.7.1 16:00",50,10,100,2,now());
update ticketInfo set carName='B0001',startPosition='上海',endPosition='',startTime='',endTime='',totalTicket='',soldTicket='',ticketPrice='',ticketPrice='' where id=1;
update ticketInfo set ticketStatus='2' where id=1;
select * from ticketInfo where ticketStatus=1 and startPosition="郑州" and endPosition="周口" and startTime="2020-07-26 18:00:00";
select * from ticketInfo where id=1;
select count(*) from ticketInfo;
delete from ticketInfo where id=2;

--   乘客信息表
drop table if exists passengers;
create table passengers(
passengersId int not null primary key auto_increment,
passengersName varchar(12) not null,
passengersCard varchar(18) not null,
passengersTel varchar(11) not null
);
select * from passengers;
insert into passengers(passengersName,passengersCard,passengersTel)values("陈晨","412727202110030711","16808089999");
insert into passengers(passengersName,passengersCard,passengersTel)values("王旺","412727202111060711","16808086666");
update passengers set passengersName='磊磊',passengersCard='412727199910030720',passengersTel='1212' where passengersId=10;
delete from passengers where passengersId=10;