1.启动,连接,停止,卸载mysql的语句:
net start mysql;
mysql-uroot;
net stop mysql;
mysql-remove;
2.请用create index语句在数据库db_test的表中创建一个升序索引
mysql>USER db_test;
Database changed mysql>CREATE INDEX index_subject->n content(subject(3)ASC);
3.请使用select into outfile 语句,备份数据到backupcontent.txt的文件中
SELECT * FROM db_test.content INTO OUTFILE 'C:/backup/backupcontent.txt';
4.郑州金水
(1)create database student;
use student;
create table student_web(s_id is not null,s_name varchar(12),s_fenshu int.s_hometown varchar(50),s_tuition int);
(2)insert into student_web(s_id,s_name,s_fenshu,s_hometown,s_tuition)values(1,'Jack Tomas',89,'郑州金水',2800)values(2,'Tom Joe',88,'洛阳涧西',3000)values(3,'Smiths',87,'郑州中原',2700)
(3)select*from student_web;
(4)select*from student where s_name like'%J%';
(5)select*from student_web where s_hometown=‘郑州%’;
(6)select*from student_web where s_tuition<(select avg(s_tuition) from student_web);
5.score表
select*from score where 姓名=‘张三’;
select*from score where 学科='英语'AND分数>90;
select 学号 FROM SCORE group by 学号HAVING SUM(分数)>180;
6.一张user表,有id,name,删除id为null的;讲null改为匿名
delete from user where id is null;
update user set name='匿名' where name is null;
7.查询表中class=3的所有信息
select * from student where class=3;
8.现有一个student表,表结构如下所示:
根据表结构编写建表语句
Creat table student(
Id int(4) primary key.
Name varchar(20) not null,
Grade float);
Insert into student values(5,'lily',100);
9.有部门表dept和员工表employee 查询存在年龄大于21的员工所对应的部门信息,采用自连接查询方式查询与王红在同一部门的员工
select*from dept where exsits(select did from employee where age >21)
select p1.* from employee p1 join employee p2 on p1.did =p2.did where p2.name =‘王红’
10.在sales 表上创建一个视图
create view view_sales as select first _half+latter_half from sale
11.请查询send表中当天的所有记录的sql语句,其中字段sendtime为datetime型
select * from send where datediff(dd,endtime,getdate())=0;
12.请按照以下要求设计sal 语句。假定student 表中有:id,name, grade,gender 四个字
段 要求如下:查询name 字段值以字符 “h”开始 或者gender 字段值为 “女” 或者 grade
宇段值为 100 的记录。上面三个条件只要一个条件成立就符合要求,写出一个sa1 语句实
现上述需求。
SELECT id, name, grade, gender FROM student WHERE name LIKE 'h%' or gender ='*'
or grade=100:
13.请查询出send 表中日期 (sendtime 字段)为当天的所有记录的sa1 语句,其中字段
sendtime 为 datetime 型。
SELECT * FROM send WHERE DATEDIFF (DD, sendtime. GETDATE O) =0:
14.己知数据库中有一张会员表,表中有字段会员编号、姓名、性别、入会时间,请查询出表中除了会员“小七
SELECT * from 会员where姓名!=小七’;
CREATE TABLE hpmcb(
Hpid INT (11) NOT NULL COMMENT'货品id' AUTO_INCREMENT,
hpmc VARCHAR(50) DEFAULT NULL COMMENT '货品名称',
jldw VARCHAR(10) DEFAULT NULL COMMENT '计量单位',
hptm VARCHAR(20) DEFAULT NULL COMMENT '货品条码',
lsj VARCHAR(50) DEFAULT NULL COMMENT '零售价',
hpzt TINYINT(4) DEFAULT 0 COMMENT '货品状态(0 正常,1 暂停进货,2 不再进货,)',
PRIMARY KEY (Hpid)
)
CREATE TABLE hpkcb(
kcid INT(11) NOT NULL AUTO_INCREMENT COMMENT '库存id',
hpid INT(11) DEFAULT NULL COMMENT '货品id',
kscl DECIMAL(18,3) DEFAULT '0.000' COMMENT '库存数量',
PRIMARY KEY (kcid)
)
CREATE VIEW V_hpkcb AS SELECT hpmcb.Hpid,hpmc,jldw,kscl FROM hpmcb,hpkcb
INSERT INTO hpmcb(hpmc,jldw,hptm,lsj) VALUE('可乐','元','123','3')
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `after`$$
CREATE
TRIGGER `after` AFTER INSERT ON `hpmcb`
FOR EACH ROW
BEGIN
INSERT INTO hpkcb(hpid) VALUE (new.hpid);
END;
$$
DELIMITER;
版权归原作者 长歌当哭-vo 所有, 如有侵权,请联系我们删除。