常用数据库命令大全

讯享网

#3.创建两个数据库，一个名为自己姓名首字母，一个名为老师名字首字母的数据库 CREATE DATABASE IF NOT EXISTS lgp; #使用数据库 USE lgp; CREATE DATABASE IF NOT EXISTS dsj; USE dsj; #4.使用SHOW语句来查看已经存在的数据库 SHOW DATABASES; #5.查看用自己姓名创建数据库的信息 SHOW CREATE DATABASE lgp; #6.修改名为自己数据库的字符集为gbk ALTER DATABASE lgp CHARACTER SET utf8; #7.删除数据库 DROP DATABASE dsj; DROP DATABASE lgp; #8.查看正在使用的数据库 SELECT DATABASE(); #9.使用/切换数据库切换到自己姓名数据库 USE lgp; #10.创建一个表，使用存在就重新删除创建 DROP TABLE IF EXISTS tb_department; CREATE TABLE tb_department ( dep_name VARCHAR ( 20 ) NOT NULL COMMENT '系名', dep_phone VARCHAR ( 20 ) NOT NULL UNIQUE COMMENT '电话', PRIMARY KEY ( dep_name ) ) DEFAULT CHARSET = utf8; INSERT INTO tb_department VALUES ( '大数据学院', '10086' ), ( '大健康学院', '110' ), ( '工程学院', '119' ), ( '体育学院', '123' ), ( '学院', '206' ); #查询表创建是否成功 SELECT * FROM tb_department; DROP TABLE IF EXISTS tb_course; CREATE TABLE tb_course ( cou_id VARCHAR ( 20 ) NOT NULL COMMENT '课程号', cou_name VARCHAR ( 20 ) NOT NULL COMMENT '课程名', PRIMARY KEY ( cou_id ) ) DEFAULT CHARSET = utf8; #添加表的内容 INSERT INTO tb_course VALUES ( 'kc01', '技术与应用' ), ( 'kc02', 'JAVA' ), ( 'kc03', 'JAVAWEB' ), ( 'kc04', '概论' ), ( 'kc', '技术' ); SELECT * FROM tb_course; DROP TABLE IF EXISTS tb_student; CREATE TABLE IF NOT EXISTS tb_student ( S_id VARCHAR ( 20 ) NOT NULL UNIQUE COMMENT '学号', S_name VARCHAR ( 20 ) NOT NULL COMMENT '姓名',#S_sex VARCHAR(2) COMMENT'性别' NOT NULL CHECK(S_sex IN('M','F')), S_sex ENUM ( '男', '女' ) DEFAULT '男', S_age INT ( 2 ) NOT NULL COMMENT '年龄', S_dep_name VARCHAR ( 20 ) NOT NULL COMMENT '系名', PRIMARY KEY ( S_id ), CONSTRAINT FK_ID_1 FOREIGN KEY ( S_dep_name ) REFERENCES tb_department ( dep_name ) ON UPDATE CASCADE ON DELETE CASCADE ) DEFAULT CHARACTER SET utf8; INSERT INTO tb_student VALUES ( 'xs01', '钟', '男', '20', '大数据学院' ), ( 'xs02', '何', '男', '20', '大健康学院' ), ( 'xs03', '黄', '女', '19', '工程学院' ), ( 'xs04', '冯', '男', '21', '体育学院' ), ( 'xs', '刘', '男', '20', '学院' ); SELECT * FROM tb_student; DROP TABLE IF EXISTS tb_techer; CREATE TABLE tb_techer ( t_id VARCHAR ( 20 ) NOT NULL COMMENT '教师号', t_name VARCHAR ( 20 ) NOT NULL COMMENT '姓名', t_sex ENUM ( '男', '女' ) NOT NULL COMMENT '性别', t_technical VARCHAR ( 20 ) NOT NULL COMMENT '职称', S_dep_name VARCHAR ( 20 ) NOT NULL COMMENT '系名', PRIMARY KEY ( t_id ), CONSTRAINT FK_ID_2 FOREIGN KEY ( S_dep_name ) REFERENCES tb_department ( dep_name ) ON UPDATE CASCADE ON DELETE CASCADE ) DEFAULT CHARACTER SET utf8; INSERT INTO tb_techer VALUES ( 'js01', '钟', '男', '高级教师', '大数据学院' ), ( 'js02', '何', '女', '一级教师', '大健康学院' ), ( 'js03', '黄', '男', '一级教师', '体育学院' ), ( 'js04', '何', '女', '二级教师', '工程学院' ), ( 'js', '刘', '男', '一级教师', '学院' ); SELECT * FROM tb_techer; DROP TABLE IF EXISTS tb_tech; CREATE TABLE tb_tech ( t_id VARCHAR ( 20 ) NOT NULL COMMENT '教师号', cou_id VARCHAR ( 20 ) NOT NULL COMMENT '课程号', PRIMARY KEY ( t_id, cou_id ), CONSTRAINT FK_ID_3 FOREIGN KEY ( t_id ) REFERENCES tb_techer ( t_id ) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT FK_ID_4 FOREIGN KEY ( cou_id ) REFERENCES tb_course ( cou_id ) ON UPDATE CASCADE ON DELETE CASCADE ) DEFAULT CHARSET = utf8; INSERT INTO tb_tech VALUES ( 'js01', 'kc01' ), ( 'js02', 'kc02' ), ( 'js03', 'kc03' ), ( 'js04', 'kc04' ), ( 'js', 'kc' ); SELECT * FROM tb_tech; DROP TABLE IF EXISTS tb_record; CREATE TABLE tb_record ( s_id VARCHAR ( 20 ) NOT NULL COMMENT '学号', cou_id VARCHAR ( 20 ) NOT NULL COMMENT '课程号', grade FLOAT NOT NULL COMMENT '成绩', PRIMARY KEY ( s_id, cou_id ), CONSTRAINT FK_ID_5 FOREIGN KEY ( s_id ) REFERENCES tb_student ( S_id ) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT FK_ID_6 FOREIGN KEY ( cou_id ) REFERENCES tb_course ( cou_id ) ON UPDATE CASCADE ON DELETE CASCADE ) DEFAULT CHARSET = utf8; INSERT INTO tb_record VALUES ( 'xs01', 'kc01', '55' ), ( 'xs02', 'kc02', '88' ), ( 'xs03', 'kc03', '90' ), ( 'xs04', 'kc04', '91' ), ( 'xs', 'kc', '100' ); SELECT * FROM tb_record; #11、 查看数据库中的所有表 SHOW TABLES; #12、 查看表结构 DESC tb_course; #13、 查看创建表的 SQL 语句 SHOW CREATE TABLE tb_course; CREATE TABLE `tb_course` ( `cou_id` VARCHAR(20) NOT NULL COMMENT '课程号', `cou_name` VARCHAR(20) NOT NULL COMMENT '课程名', PRIMARY KEY (`cou_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 #14、 添加数据，每个表5条模拟数据，其中有一条是自己的数据,倒回去截图 #INSERT INTO 表名 VALUES(),(),(),(),(); #SELECT * FROM 表名 #15、 复制学生表，表名用自己姓名首字母 -- create table 新表 select * from 旧表 CREATE TABLE tb_lgp LIKE tb_student; SELECT * FROM tb_lgp; #16.将tb_student表的数据蠕虫复制到用自己姓名创建表中 INSERT INTO tb_lgp SELECT * FROM tb_student; SELECT * FROM tb_lgp; #复制表2的所有列到表1中 -- INSERT INTO 表名 1 SELECT * FROM 表名 2; #17、 为复制的表添加一个电话字段s_telephone,数据类型为varchar(12) ALTER TABLE tb_lgp ADD s_telephone VARCHAR(12); SELECT * FROM tb_lgp; DESC tb_lgp; #18、 修改复制的表里面字段s_telephone，数据类型为int(20) ALTER TABLE tb_lgp MODIFY s_telephone INT(20); DESC tb_lgp; #修改列名：ALTER TABLE 表名 CHANGE 旧列名 新列名 类型; #删除列：ALTER TABLE 表名 DROP 列名; DESC tb_lgp; #19.修改复制的表字符集为gbk: ALTER TABLE 表名 CHARACTER SET 字符集; ALTER TABLE tb_lgp CHARACTER SET gbk; #20.修改复制表的表名为tb_原表名 RENAME TABLE tb_lgp TO tb_liuguoping; SELECT * FROM tb_liuguoping; #21、 删除列复制表里面字段s_telephone ALTER TABLE tb_liuguoping DROP s_telephone; #查看 DESC tb_liuguoping; #22、 为复制的表添加一个老师的数据 INSERT INTO tb_liuguoping VALUES ( 'xs06', 'dsj', '男', '30', '大数据学院' ); SELECT * FROM tb_liuguoping; #23、 修改复制表里面大数据的年龄为100 #UPDATE 表名 SET 字段名=值 WHERE 字段名=值; #注：修改复制的表里面字段S_age，数据类型为int(3) ALTER TABLE tb_liuguoping MODIFY S_age INT(20); UPDATE tb_liuguoping SET S_age ='100' WHERE tb_liuguoping.S_id='xs06'; #24、 删除复制表里面老师的数据 DELETE FROM tb_liuguoping WHERE S_id='xs06'; SELECT * FROM tb_liuguoping; #25、 使用 truncate 删除复制表中所有记录 TRUNCATE TABLE tb_liuguoping; #26、 删除复制的表 DROP TABLE tb_liuguoping; SHOW TABLES; #27、 查询学生表(tb_student)表里面数据，要求只显示中文别名学号和姓名字段的数据 SELECT S_id AS 学号, S_name AS 姓名 FROM tb_student; #28、 (DISTINCT清除重复值)查询学生表(tb_student)里面性别的种类 SELECT DISTINCT S_sex FROM tb_student; #29、 查询选修表(tb_record)里面成绩整体上浮50分的结果 SELECT grade+50 FROM tb_record; #30、 查询学生表(tb_student)自己的那条数据 SELECT * FROM tb_student WHERE S_id='xs'; #31、 查询学生表(tb_student)年龄大于等于18岁同学 SELECT * FROM tb_student WHERE S_age>18; #32、 查询学生表(tb_student)，年龄在18，19的同学 SELECT * FROM tb_student WHERE S_age=18 || S_age=19; #33、 查询教师表(tb_techer)姓钟的老师信息 SELECT * FROM tb_techer WHERE t_name LIKE '%钟%'; #34、 用count()函数统计tb_student表中记录的条数 SELECT COUNT(0) FROM tb_student; #35、 查询学生表、课程表、选修表中的数据,要求只显示姓名,课程名，成绩 SELECT tb_student.S_name,tb_course.cou_name,tb_record.grade FROM tb_student,tb_course,tb_record WHERE tb_student.S_id=tb_record.s_id AND tb_course.cou_id=tb_record.cou_id; #36、 创建视图cj_view，视图内容要求包含id,姓名,课程名，成绩 CREATE VIEW cj_view (学号,姓名,课程名,成绩 )AS SELECT tb_student.S_id,tb_student.S_name,tb_course.cou_name,tb_record.grade FROM tb_student,tb_course,tb_record WHERE tb_student.S_id=tb_record.s_id AND tb_course.cou_id=tb_record.cou_id ; #查询 SELECT * FROM cj_view; #37、 用sum函数统计cj_view视图中总成绩，要求显示总分字段 SELECT SUM(成绩) AS 总分 FROM cj_view WHERE cj_view.成绩; #38、 用avg函数统计cj_view视图中平均分 SELECT AVG(成绩) AS 平均分 FROM cj_view WHERE cj_view.成绩; #39、 用min函数统计cj_view视图中最低分 SELECT MIN(成绩) AS 最低分 FROM cj_view WHERE cj_view.成绩; #40、 用max函数统计cj_view视图中最高分 SELECT MAX(成绩) AS 最高分 FROM cj_view; #41、 查询cj_view视图中所有记录，并按照成绩进行降序排序 #select * from stu order by age asc; #order by：是排序的关键字 #asc：代表的是升序 #desc: 代表降序 #age:表示的是age字段 SELECT * FROM cj_view ORDER BY 成绩 DESC; #42、 将tb_student中数据按照s_sex进行分组查询,计算出每组各有多少名学生 #简单的分组查询：SELECT * FROM tb_student GROUP BY S_sex; SELECT COUNT(0)FROM tb_student GROUP BY S_sex; #43、 查询tb_student表中第2位到第4位学生信息 SELECT * FROM tb_student WHERE S_id IN ('xs02','xs04'); #-- 查询 id 不是 1 或 3 或 5 的学生 #select * from student3 where id not in(1,3,5); #44、 用交叉连接查询tb_student和tb_department所有数据 SELECT * FROM tb_student,tb_department; SELECT * FROM tb_student CROSS JOIN tb_department; #45、 查询分数大于60分学生姓名 SELECT * FROM tb_student,tb_record WHERE tb_student.S_id=tb_record.s_id AND tb_record.grade>60; #46、 创建存储过程cj_proc,作用是查询姓名,课程名，成绩 DELIMITER // CREATE PROCEDURE cj_proc(IN 姓名1 VARCHAR(20),OUT 课程名1 VARCHAR(20),成绩1 FLOAT) BEGIN SELECT 姓名,课程名,成绩 FROM cj_view; END// DELIMITER ; #查询存储过程 CALL cj_proc(@姓名1,@课程名1,@成绩1); #查询视图 SELECT * FROM cj_view; DESC tb_student; DESC tb_course; DESC tb_record; DROP PROCEDURE cj_proc; #删除存储过程 #1.drop procedure 存储过程名称；2.drop procedure if exists 存储过程名称； DROP PROCEDURE cj_proc1; #47、 创建存储过程cj_proc1,作用是输入查询学生的学号后返回一个成绩 DELIMITER // CREATE PROCEDURE cj_proc1(IN S_id VARCHAR(20) , OUT grade VARCHAR(20)) BEGIN SELECT 成绩 INTO grade FROM cj_view WHERE 学号=S_id; END// DELIMITER ; CALL cj_proc1('xs01',@grade); SELECT @grade; #48、 为tb_student表创建一个名为insert_zxg触发器,触发器作用查询tb_student中自己姓名和年龄 DELIMITER // CREATE TRIGGER insert_zxg AFTER INSERT ON tb_student FOR EACH ROW BEGIN UPDATE tb_techer SET t_technical='教授'; END // DELIMITER ; SELECT * FROM tb_techer; #执行该条并触发 INSERT INTO tb_student VALUES('xs011', 'll', '女', '18', '大数据学院' ); SELECT * FROM tb_student; #查询触发器 SHOW TRIGGERS; #删除触发器 DROP TRIGGER insert_dsj; #49、在C盘用自己姓名首字母名命一个文件夹，将用自己创建的数据库备份到该文件夹,文件名命名方式为：姓名首字母+当前日期 #cmd下： mysqldump -u root -p lgp > c:/lgp/lgp1228.SQL #50、 在sqlyog创建一个dsj数据库,将上一题备份数据库恢复到名为dsj数据库里面 CREATE DATABASE dsj; USE dsj; DROP DATABASE dsj; #注： cmd下：mysql -u root -p dsj< c:/lgp1220.SQL cmd进入MySQL并使用需要用来备份上一个数据库的命令：source C:/LGP/lgp1210.SQL;