【MySQL】MySQL基础操作语句
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;
