mysql命令对应代码，对应实时更新

mysql命令对应代码

1. show databases；

2. show create database mysql；

3. create database mydb1；

4. create database mydb2 character set utf8；

5. drop database mydb1；

6. alter database mydb2 character set gbk；

7. select database()；

8. use mydb2；

9. create table emp(

   name varchar(50),

   password int(10),

   sex varchar(10),

   birthday date

   );

10. desc emp；

11. create table emp2(

​ id int(10) primary key auto_increment,

​ username varchar(10) unique not null,

​ password int(10) not null,

​ sex varchar(10),

​ birthday date

​ );

12. alter table emp2 add salary double；

    alter table emp2 add age int(3);

13. alter table emp2 modify birthday date not null;

14. alter table emp2 modify username varchar(60);

15. alter table emp2 change username name varchar(60);

16. alter table emp2 drop age;

17. rename table emp2 to person;

18. alter table emp character set utf8；

19. show create database mydb2；

    show create table emp；

20. drop table emp；

21. show tables；

22. desc person；

23. show create table person；

24. insert into person(id,name,password,sex,birthday,salary)values(null,‘zhangsan’,‘123’,‘nan’,‘1995-9-7’,1500);

25. insert into person values(null,‘lisi’,‘123’,‘nv’,‘1998-9-7’,25000);

26. insert into person(name,password,birthday)values(‘wangwu’,‘123’,‘1985-8-1’);

27. update person set password=‘abcdef’;

28. update person set salary=88888 where name=‘zhangsan’;

29. update person set name=‘zhaoliu’,password=‘abc’ where id=3;

30. update person set name=‘赵六’ where id=3;

31. delete from person where name=‘lisi’;

32. delete from person;

33. RENAME TABLE C TO A;

黑窗口结束，开始sqlyog

1. 右击新建数据库叫mydb3

2. CREATE TABLE student(
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(50),
   age INT(3),
   gender VARCHAR(10),
   score DOUBLE,
   birthday DATE
   );

3. INSERT INTO student VALUES(1,‘zhangsan’,23,‘male’,98.99,‘1990-9-9’);
   INSERT INTO student VALUES(2,‘lisi’,23,‘男’,56.99,‘1990-2-9’);
   INSERT INTO student VALUES(3,‘王五’,24,‘女’,75.99,‘1988-1-1’);
   INSERT INTO student VALUES(4,‘赵六’,25,‘男’,80.99,‘1980-11-12’);
   INSERT INTO student VALUES(5,‘王思聪’,NULL,‘女’,84,NULL);

4. select * from student;

5. SELECT NAME,score FROM student;

6. SELECT * FROM student WHERE age>=24;

7. SELECT * FROM student WHERE age!=25;

8. SELECT * FROM student WHERE age>23 AND score>80;

9. SELECT * FROM student WHERE score>=80 AND score<=100;

10. SELECT * FROM student WHERE score BETWEEN 80 AND 100;

11. SELECT * FROM student WHERE age IN (18,23,25);

12. SELECT * FROM student WHERE NAME LIKE ‘%思%’;

13. SELECT * FROM student WHERE birthday IS NULL;

14. SELECT * FROM student WHERE age IS NOT NULL;

15. SELECT DISTINCT age FROM student;

16. SELECT * FROM student ORDER BY score DESC;

17. SELECT * FROM student ORDER BY age ASC;

18. SELECT * FROM student ORDER BY age DESC,score DESC;

19. SELECT age AS 年龄,score AS 成绩 FROM student;

20. SELECT age 年龄,score 成绩 FROM student;

21. SELECT COUNT(*) FROM student;

22. SELECT NAME,score FROM student WHERE score>80;

23. SELECT COUNT(score) FROM student WHERE score>80;

24. SELECT SUM(score) FROM student;

25. SELECT SUM(age),SUM(score) FROM student;

26. SELECT TRUNCATE(SUM(age)+SUM(score),2) FROM student;

27. SELECT TRUNCATE(AVG(age),2) FROM student;

28. SELECT MAX(score),MIN(score) FROM student;

29. 右击，新建mydb4数据库

30. CREATE TABLE student(
    NAME VARCHAR(50),
    sex VARCHAR(10),
    course VARCHAR(30),
    score DOUBLE
    );

31. INSERT INTO student VALUES(‘张三’,‘男’,‘语文’,93);
    INSERT INTO student VALUES(‘张三’,‘男’,‘数学’,96);
    INSERT INTO student VALUES(‘张三’,‘男’,‘英语’,99);
    INSERT INTO student VALUES(‘李四’,‘女’,‘语文’,90);
    INSERT INTO student VALUES(‘李四’,‘女’,‘数学’,85);
    INSERT INTO student VALUES(‘王五’,‘男’,‘语文’,88);
    INSERT INTO student VALUES(‘王五’,‘男’,‘数学’,75);
    INSERT INTO student VALUES(‘王五’,‘男’,‘英语’,70);

32. SELECT NAME,SUM(score) FROM student GROUP BY NAME;

33. SELECT NAME,AVG(score) FROM student GROUP BY NAME HAVING AVG(score)>80;

34. SELECT NAME,AVG(score) FROM student GROUP BY NAME;

35. SELECT NAME,SUM(score) FROM student GROUP BY NAME HAVING SUM(score)>250;

36. 右击，新建一个coder_project数据库

37. CREATE TABLE coder(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    canme VARCHAR(50),
    salary DOUBLE
    );

38. CREATE TABLE project(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(50)
    );

39. CREATE TABLE coder_project(
    coder_id INT,
    project_id INT
    );

40. INSERT INTO coder(cid,canme,salary)
    VALUES(1,‘张三’,15000);
    INSERT INTO coder(cid,canme,salary)
    VALUES(2,‘李四’,25000);
    INSERT INTO coder(cid,canme,salary)
    VALUES(3,‘王五’,35000);

41. INSERT INTO project(pid,pname)
    VALUES(1,‘QQ项目’);
    INSERT INTO project(pid,pname)
    VALUES(2,‘微信项目’);

42. INSERT INTO coder_project(coder_id,project_id)
    VALUES(1,1);
    INSERT INTO coder_project(coder_id,project_id)
    VALUES(1,2);
    INSERT INTO coder_project(coder_id,project_id)
    VALUES(2,1);
    INSERT INTO coder_project(coder_id,project_id)
    VALUES(2,2);
    INSERT INTO coder_project(coder_id,project_id)
    VALUES(3,2);

43. DELETE FROM coder;
    DELETE FROM project;
    DELETE FROM coder_project;

44. – 教师表

    CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50)
    );

    – 课程表

    CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    teacher_id INT,
    FOREIGN KEY(teacher_id) REFERENCES teacher(id)
    );

    – 学生表

    CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50)
    );

    – 中间表

    CREATE TABLE student_course(
    student_id INT,
    course_id INT,
    score DOUBLE,
    FOREIGN KEY(student_id) REFERENCES student(id),
    FOREIGN KEY(course_id) REFERENCES course(id)
    );

45. 1. SELECT * FROM a;
    2. SELECT * FROM b;
    3. SELECT * FROM a,b;（笛卡尔积）
    4. SELECT a.,b. FROM a,b WHERE a.A_ID=b.A_ID;
    5. SELECT a.,b. FROM a INNER JOIN b ON a.A_ID=b.A_ID;
    6. SELECT a.,b. FROM a LEFT JOIN b ON a.A_ID=b.A_ID;
    7. SELECT a.,b. FROM a RIGHT JOIN b ON a.A_ID=b.A_ID;
    8. SELECT a.,b. FROM a LEFT JOIN b ON a.A_ID=b.A_ID
       UNION ALL
       SELECT a.,b. FROM a RIGHT JOIN b ON a.A_ID=b.A_ID;
    9. SELECT a.,b. FROM a LEFT JOIN b ON a.A_ID=b.A_ID
       UNION
       SELECT a.,b. FROM a RIGHT JOIN b ON a.A_ID=b.A_ID;
46. 1. SELECT * FROM student
       WHERE id IN(SELECT student_id
       FROM studentcourse WHERE score<60);
    2. SELECT *
       FROM student
       WHERE EXISTS (SELECT student_id
       FROM studentcourse
       WHERE score<60
       AND student.id=studentcourse.student_id);
    3. SELECT *
       FROM student
       WHERE id=(SELECT student_id
       FROM studentcourse
       WHERE score=(SELECT MAX(score)
       FROM studentcourse));
    4. SELECT *
       FROM student
       WHERE id IN (SELECT student_id
       FROM studentcourse
       WHERE course_id=2
       AND score>(SELECT MAX(score)
       FROM studentcourse
       WHERE course_id=1));
    5. SELECT student.name,temp.score FROM student,(SELECT *
       FROM studentcourse
       WHERE course_id=2
       AND score>(SELECT MAX(score)
       FROM studentcourse
       WHERE course_id=1)) temp WHERE student.id=temp.student_id;
    6. SELECT student_id,
       TRUNCATE(AVG(score),2) AS AVG
       FROM studentcourse
       GROUP BY student_id
       HAVING AVG(score)>70;
    7. SELECT student_id,student.name,temp.cou,temp.s
       FROM student,(SELECT student_id,COUNT(*) AS cou,SUM(score) AS s
       FROM studentcourse
       GROUP BY student_id) AS temp
       WHERE student.id=temp.student_id;
    8. SELECT * FROM student
       WHERE id IN(SELECT student_id FROM studentcourse
       WHERE course_id=(SELECT id FROM course
       WHERE teacher_id=(SELECT id FROM teacher WHERE NAME=‘赵云’))) ;
    9. SELECT id,NAME
       FROM student
       WHERE id NOT IN(SELECT student_id
       FROM studentcourse
       WHERE course_id IN(SELECT id
       FROM course
       WHERE teacher_id IN(SELECT id
       FROM teacher
       WHERE NAME=‘关羽’))) ;
    10. SELECT id,NAME
        FROM student
        WHERE id IN (SELECT student_id
        FROM studentcourse GROUP BY student_id HAVING COUNT(*)<=3);
    11. SELECT course_id,MAX(studentcourse.score),MIN(studentcourse.score)
        FROM studentcourse LEFT JOIN course
        ON studentcourse.course_id=course.id
        GROUP BY id;
    12. SELECT student_id,AVG(studentcourse.score)
        FROM studentcourse LEFT JOIN student
        ON studentcourse.student_id=student.id G
        ROUP BY id;
    13. SELECT city ,COUNT(city)
        FROM student WHERE city IN (‘上海’,‘北京’)
        GROUP BY city;
    14. SELECT * FROM (SELECT student.*,studentcourse.course_id,studentcourse.score
        FROM student LEFT JOIN studentcourse ON student.id=studentcourse.student_id
        WHERE score<60 GROUP BY id)
        temp LEFT JOIN course ON course.id=temp.course_id;
    15. SELECT NAME,COUNT()
        FROM studentcourse LEFT JOIN course
        ON studentcourse.course_id=course.id
        GROUP BY course_id HAVING COUNT()>4 ;

47. SELECT NOW();

48. SELECT CURRENT_DATE();

49. SELECT YEAR(NOW());