mysqlåºç¡æä½è¯å¥ï¼å æ¬æ°æ®åºçå¢ãå ãåæ¢ï¼ä»¥å表çå¢ãå ãæ¹ãæ¥ãå¤å¶ã
å建æ°æ®åº
mysql> create database tem;
使ç¨æ°æ®åº
mysql> use tem;
å é¤æ°æ®åº
mysql> drop database drop_test;
å建æ°æ®è¡¨
student表
mysql> CREATE TABLE student (
-> id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
-> name VARCHAR(20) NOT NULL ,
-> sex VARCHAR(4) ,
-> birth YEAR,
-> department VARCHAR(20) ,
-> address VARCHAR(50)
-> );
å建score表
mysql> CREATE TABLE score (
-> id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
-> stu_id INT(10) NOT NULL ,
-> c_name VARCHAR(20) ,
-> grade INT(10)
-> );
æå ¥è¡¨æ°æ®
student表
mysql> INSERT INTO student VALUES( 001,'RooneyMara', 'F',1985,'Psychology', 'American');
mysql> INSERT INTO student VALUES( 002,'ChrisHemsworth', 'M',1983,'CS', 'Australia');
mysql> INSERT INTO student VALUES( 003,'EllenPage', 'F',1987,'Music', 'Canada');
mysql> INSERT INTO student VALUES( 004,'TomHolland', 'M',1996,'CS', 'England');
mysql> INSERT INTO student VALUES( 005,'ScarlettJohansson', 'F',1984,'Music', 'American');
mysql> INSERT INTO student VALUES( 006,'BenedictCumberbatch','M',1976,'Psychology', 'England');
score表
mysql> INSERT INTO score VALUES(NULL,001, 'Psychology',98);
mysql> INSERT INTO score VALUES(NULL,001, 'Music', 80);
mysql> INSERT INTO score VALUES(NULL,002, 'Psychology',65);
mysql> INSERT INTO score VALUES(NULL,002, 'CS',88);
mysql> INSERT INTO score VALUES(NULL,003, 'CS',95);
mysql> INSERT INTO score VALUES(NULL,004, 'Psychology',70);
mysql> INSERT INTO score VALUES(NULL,004, 'Music',92);
mysql> INSERT INTO score VALUES(NULL,005, 'Music',94);
mysql> INSERT INTO score VALUES(NULL,006, 'Psychology',90);
mysql> INSERT INTO score VALUES(NULL,006, 'Music',85);
æ¥è¯¢è¡¨æ°æ®
æ¥è¯¢student表ä¸çå ¨é¨æ°æ®
mysql> select * from student;
æ¥è¯¢student表ç第3æ¡å°5æ¡è®°å½
mysql> select * from student limit 2,3;
æ¥è¯¢student表çææå¦ççidãnameådepartmentä¿¡æ¯
mysql> select id,name,department from student;
æ¥è¯¢student表ä¸department为CSåMusicçå¦çä¿¡æ¯
mysql> select * from student where department in ('CS','Music');
æ¥è¯¢student表ä¸å¹´é¾ä¸º23~32å²çå¦çä¿¡æ¯
mysql> select id,name,sex,2019-birth as age,department,address
-> from student
-> where 2019-birth between 23 and 32;
æ¥è¯¢student表ä¸æ¯ä¸ªdepartmentæå¤å°äºº
mysql> select department,count(id) from student group by department;
æ¥è¯¢score表ä¸æ¯ä¸ªç§ç®(c_name)çæé«å(grade)
mysql> select c_name,max(grade) from score group by c_name;
æ¥è¯¢âRooneyMaraâçç§ç®(c_name)åæ绩(grade)
mysql> select c_name,grade from score
-> where stu_id=(select id from student where name='RooneyMara');
æ¥è¯¢ææå¦ççä¿¡æ¯åèè¯ä¿¡æ¯(ç¨è¿æ¥çæ¹å¼)
mysql> select student.id,name,sex,birth,department,address,c_name,grade
-> from student,score
-> where student.id=score.stu_id;
计ç®æ¯ä¸ªå¦ççæ»æ绩
mysql> select student.id,name,sum(grade) from student,score
-> where student.id=score.stu_id
-> group by id;
计ç®æ¯ä¸ªèè¯ç§ç®çå¹³åæ绩
mysql> select c_name,avg(grade) from score group by c_name;
æ¥è¯¢Psychologyæ绩ä½äº95çå¦çä¿¡æ¯
mysql> select * from student
-> where id in (
-> select stu_id from score
-> where c_name='Psychology' and grade<95);
æ¥è¯¢åæ¶åå PsychologyåCSèè¯çå¦ççä¿¡æ¯
æ¹æ³1ï¼
mysql> select * from student
-> where id =any
-> (select stu_id from score where stu_id in
-> (select stu_id from score where c_name='Psychology')
-> and c_name='CS');
æ¹æ³2ï¼
mysql> select a.* from student a,score b,score c
-> where a.id=b.stu_id
-> and b.c_name='Psychology'
-> and a.id=c.stu_id
-> and c.c_name='CS';
å°Psychologyèè¯æ绩æä»é«å°ä½è¿è¡æåº
mysql> select stu_id ,grade from score where c_name='Psychology'
-> order by grade desc;
æ¥è¯¢student表åscore表ä¸å¦ççå¦å·(id)ï¼ç¶åå并æ¥è¯¢ç»æ
mysql> select id from student union select stu_id from score;
æ¥è¯¢nameä¸å¼å¤´åæ¯æ¯âRâæè âEâçåå¦çå§å(name)ãé¢ç³»(department)åèè¯ç§ç®(c_name)åæ绩(grade)
mysql> select student.id,name,sex,birth,department,address,c_name,grade
-> from student,score
-> where (name like 'R%' or name like 'E%')
-> and student.id=score.stu_id;
æ¥è¯¢addressé½æ¯Englandçå¦ççå§å(name)ãå¹´é¾ãé¢ç³»(department)åèè¯ç§ç®(c_name)åæ绩(grade)
mysql> select student.id,name,sex,2019-birth as age,department,address,c_name,grade
-> from student,score
-> where address='England'
-> and student.id=score.stu_id;
å é¤è¡¨æ°æ®
mysql> delete from score where id=10;
æ´æ°è¡¨æ°æ®
mysql> update score set c_name='CS' where id=11;
å¤å¶è¡¨æ ¼å¼
1.æ¥çæ°æ®è¡¨çå®æ´ç»æ
mysql> show create table student;
2. ä¿®æ¹æ°æ®è¡¨åï¼å¹¶æ§è¡SQLè¯å¥
mysql> CREATE TABLE `clone_student` (
-> `id` int(10) NOT NULL,
-> `name` varchar(20) NOT NULL,
-> `sex` varchar(4) DEFAULT NULL,
-> `birth` year(4) DEFAULT NULL,
-> `department` varchar(20) DEFAULT NULL,
-> `address` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `id` (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
å¤å¶è¡¨æ°æ®
mysql> insert into clone_student(id,name,sex,birth,department,address)
-> select id,name,sex,birth,department,address from student;