MySQL操作实践

SELECT yyy as 年 FROM employee_tb WHERE  name1 IN ('CHu','232')；UPDATE employee_tb SET daily_typing_pages =0  WHERE  name1 IN ('CHu','232')SELECT * FROM employee_tb WHERE id LIKE '%'SELECT * FROM employee_tb WHERE id LIKE '%' ORDER BY yyy ASC自连接SELECT a.name1 FROM employee_tb a LEFT JOIN employee_tb b ON a.name1=b.name1;ANDSELECT * FROM employee_tb WHERE daily_typing_pages= 322 AND name1 NOT IN('232');create table log(   logid int ( 4 ) primary key not  null  auto_increment,   logtitle varchar(32 ) not  null ,   logcontent varchar(160 ) not  null ,   logtime datetime not null ,   userip varchar(64 ) not  null    );     CREATE TABLE TV1(rid int PRIMARY KEY auto_increment NOT NULL,uid INT,channel VARCHAR(10));INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)SELECT * FROM tv WHERE channel='fujian' and uid NOT IN( SELECT a.uid from tv a LEFT JOIN tv b ON a.uid=b.uid WHERE a.channel='hunan' AND b.channel='fujian');use geek;set default_storage_engine=INNODB;CREATE TABLE table_0410(today datetime,name CHAR(20));use geek;INSERT into table_0408 VALUES(NOW(),'a');show TABLES;//展示表结构show GLOBAL VARIABLES LIKE 'innodb_data_file_path'show VARIABLES;FLUSH TABLES WITH READ LOCK;//备份时锁住数据库INSERT INTO table_0408 VALUES(NOW(),NULL);//此时会冲突UNLOCK TABLES;SHOW VARIABLES LIKE 'time_zone'USE geek;CREATE TABLE today(t1 datetime,t2 TIMESTAMP);SET time_zone='system';show VARIABLES LIKE 'time_zone';SELECT * FROM today;SET time_zone='+12:00';SHOW VARIABLES LIKE 'time_zone';SELECT * FROM today;UPDATE today SET t1=NOW();SELECT * FROM today;SELECT NOW(),CURTIME();USE geek;CREATE TABLE person(sex enum('男','女'),interest SET('听音乐','购物','旅游'));//枚举单选，set多选INSERT INTO persons (`sex`, `interest`) VALUES ('男', '听音乐,旅游')set sql_mode='strict_trans_tables';INSERT INTO persons (`sex`, `interest`) VALUES ('男', '音乐,旅游,购物')；//报错use geek;SET SQL_mode='ansi';INSERT INTO persons (`sex`, `interest`) VALUES ('男', '音乐,旅游,购物');//不报错设置主键use geek;CREATE TABLE nowadays(t1 datetime,t2 TIMESTAMP,PRIMARY KEY(t1,t2))//查看某个表的约束条件SELECT CONSTRAINT_name,CONSTRAINT_typeFROM information_schema.TABLE_CONSTRAINTSWHERE TABLE_SCHEMA='geek' and TABLE_NAME='nowadays';//查看表结构show INDEX FROM nowadaysCREATE TABLE Table_0411(sno CHAR(10),sname CHAR(10),CONSTRAINT s_pk PRIMARY KEY(sno),CONSTRAINT s_uq UNIQUE (sname))CREATE DATABASE choose;USE choose;CREATE TABLE teacher(teacher_no CHAR(10) PRIMARY KEY,teacher_name CHAR(10) NOT NULL,teacher_contact CHAR(10) NOT NULL) ENGINE=INNODB DEFAULT CHARSET=gbk;CREATE TABLE class(class_no int auto_increment PRIMARY KEY,class_name CHAR(20) NOT NULL UNIQUE,department_name CHAR(20) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=gbk;CREATE TABLE course(course_no int auto_increment PRIMARY KEY,course_name CHAR(10) not NULL,uplimit INT DEFAULT 60,description text NOT NULL,STATUSs CHAR(6) DEFAULT '未审核',teacher_no CHAR(10) NOT NULL UNIQUE,CONSTRAINT course_teacher_fk FOREIGN KEY (teacher_no) REFERENCES teacher(teacher_no))ENGINE=INNODB DEFAULT CHARSET=gbk;CREATE TABLE student(student_no CHAR(11) PRIMARY KEY,student_name CHAR(10) NOT NULL,student_contacts CHAR(20) NOT NULL,class_no INT,CONSTRAINT student_class_fk FOREIGN KEY(class_no) REFERENCES class(class_no))ENGINE=INNODB DEFAULT CHARSET=gbk;CREATE TABLE choose(choose_no INT auto_increment PRIMARY KEY,student_no CHAR(11) NOT NULL,course_no INT NOT NULL,score TINYINT UNSIGNED,choose_time datetime NOT NULL,CONSTRAINT choose_student_fk FOREIGN KEY(student_no) REFERENCES student(student_no),CONSTRAINT choose_course_fk FOREIGN KEY(course_no) REFERENCES course(course_no))ENGINE=INNODB DEFAULT CHARSET=gbk;INSERT INTO course VALUES(NULL,'java',120,'none','shenhe','003');复制一个表结构CREATE TABLE today_copy LIKE today;SHOW CREATE TABLE today_copy;SELECT * from today_copy;CREATE TABLE nowadays_cp SELECT * FROM nowadays;SHOW CREATE TABLE nowadays_cp;SELECT * FROM nowadays_cp;修改表结构ALTER TABLE persons DROP interest;ALTER TABLE persons ADD person_no INT auto_increment PRIMARY KEY FIRST;ALTER TABLE persons ADD person_name CHAR(10) NOT NULL;ALTER TABLE persons ENGINE=myISam;ALTER TABLE person RENAME human;RENAME TABLE persons to person;ALTER TABLE course MODIFY course_name CHAR(10) not NULL UNIQUE;//修改某一字段类型ALTER TABLE course CHANGE course_name course_name CHAR(10) not NULL UNIQUE;CREATE TABLE book(isbn CHAR(20) PRIMARY KEY,name1 CHAR(100) NOT NULL,brief_intro text NOT NULL,price DECIMAL(6,2),publish_time date NOT NULL,UNIQUE INDEX isbn_index (isbn),INDEX NAME_index (name1(20)),FULLTEXT INDEX brief_fulltext (name1,brief_intro),INDEX complex_index (price,publish_time))ENGINE=myisam DEFAULT CHARSET=gbk;插入索引ALTER TABLE course ADD FULLTEXT INDEX descip_index(description);CREATE FULLTEXT INDEX STATUSs_index ON course(STATUSs);DROP INDEX STATUSs_index ON course;删除唯一性约束show INDEX FROM nowadaysALTER TABLE course DROP INDEX course_name_2;注意：如果该字段是外键，需要先删除外键，然后再删除唯一性约束。之后添加外键约束。ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ; ALTER TABLE course DROP INDEX course_name_2;ALTER TABLE course ADD CONSTRAINT teacher_course_fk FOREIGN KEY(teacher_no) REFERENCES teacher(teacher_no)插入结果集INSERT INTO class_cp SELECT * FROM class;替换掉目标记REPLACE INTO class VALUES('1','zi','jidan');UPDATE class SET department_name='xingong' WHERE class_no<3;TRUNCATE TABLE class_cp1;delete from table class_cp;//不重置自加器SELECT DISTINCT department_name FROM class;SELECT * FROM  class LIMIT 0,1;//查询0-1记录A references B,A.key必须存在于B.key//joinSELECT student_no,student_name,student_contacts,student.class_no ,class_name,department_nameFROM student JOIN class ON student.class_no=class.class_no;SELECT student_no,student_name,student_contacts,s.class_no ,class_name,department_nameFROM  class AS c JOIN student AS s ON s.class_no=c.class_no;SELECT student_no,student_name,student_contacts,s.class_no ,class_name,department_nameFROM  class AS c LEFT JOIN student AS s ON s.class_no=c.class_no;/*检索所有班级的学生信息*/SELECT student_no,student_name,student_contacts,s.class_no ,class_name,department_nameFROM  class AS c  RIGHT JOIN student AS s ON s.class_no=c.class_no;/*检索所有学生的班级信息*/查询所有学生的成绩，未选课依然会显示SELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student LEFT JOIN choose ON student.student_no=choose.student_noLEFT JOIN course ON choose.course_no=course.course_no;查询所有学生的成绩，未选课不会显示SELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student LEFT JOIN choose ON student.student_no=choose.student_noinner JOIN course ON choose.course_no=course.course_no;查询没选课的同学，不可用inner joinSELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student LEFT JOIN choose ON student.student_no=choose.student_noLEFT JOIN course ON choose.course_no=course.course_noWHERE course.course_no IS NULL;设置字符集保持前后一致，否则会出现乱码show variables like'character%';SET CHARACTER_set_results=utf8;SELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student  left JOIN choose ON student.student_no=choose.student_no left JOIN course ON choose.course_no=course.course_noWHERE course.course_no IS NOT NULL AND course_name='java';模糊查询SELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student  left JOIN choose ON student.student_no=choose.student_no left JOIN course ON choose.course_no=course.course_noWHERE course.course_no IS NOT NULL AND course_name LIKE 'j_';按照学号排序，若学号同则按照课程排序SELECT student.student_no,student_name,course.course_no,course.course_name,choose.scoreFROM student  left JOIN choose ON student.student_no=choose.student_no left JOIN course ON choose.course_no=course.course_noWHERE course.course_no IS NOT NULL AND course_name LIKE 'j%'ORDER BY   student.student_no ASC,course.course_no ASC;聚合函数SELECT COUNT(*) 学生人数 FROM student;SELECT AVG(score) 总成绩 FROM choose WHERE student_no=201113;统计班级人数SELECT class_name,COUNT(student_no) 人数FROM class LEFT JOIN student ON student.class_no=class.class_noGROUP BY class.class_no;SELECT course_name 课程名称,COUNT(choose.student_no) 选课人数FROM course LEFT JOIN  choose ON course.course_no =choose.course_noGROUP BY course.course_nameSELECT choose.student_no 学号,student.student_name 姓名,AVG(score) 平均分FROM student LEFT JOIN choose ON student.student_no=choose.student_noGROUP BY student.student_noHAVING AVG(score)>50;SELECT class.class_name 班级,student.student_no 学号,GROUP_CONCAT(student.student_name) 姓名FROM class LEFT JOIN student ON class.class_no=student.class_noGROUP BY class.class_name;SELECT class.class_no,COUNT(student.student_no)FROM class LEFT JOIN student ON class.class_no=student.class_noGROUP BY class.class_no WITH ROLLUP;union all子查询SELECT student.student_no,student.student_name,class.class_name,course_name,scoreFROM student LEFT JOIN class ON student.class_no=class.class_noLEFT JOIN choose ON student.student_no=choose.student_noLEFT JOIN course ON course.course_no=choose.course_noWHERE score>(SELECT AVG(score)FROM choose INNER JOIN student ON choose.student_no=student.student_noGROUP BY student_nameHAVING student_name='chuyao')SELECT * FROM teacher WHERE teacher_no NOT IN (SELECT teacher.teacher_no FROM teacher JOIN course WHERE teacher.teacher_no=course.teacher_no)exists