/*創(chuàng)建數(shù)據(jù)庫*/
create database if not EXISTS students character set utf8 collate utf8_general_ci;
/*創(chuàng)建表*/
use students;
create table if not EXISTS student
(
    stuID  int(5)  not null primary key,
    stuName  varchar(50)  not null,
    stuSex CHAR(10),
    stuAge smallint
);
CREATE TABLE if not EXISTS courses(
         couID int  not null primary key auto_increment COMMENT '學(xué)號(hào)',
         couName varchar(50) not null DEFAULT('大學(xué)英語'),
         couHours  smallint UNSIGNED COMMENT '學(xué)時(shí)',
         couCredit  float DEFAULT(2) COMMENT '學(xué)分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE  utf8mb4_0900_ai_ci;
CREATE TABLE if not EXISTS stu_cou(
         ID int not null primary key auto_increment,
         stuID int(5)  not null  COMMENT '學(xué)號(hào)',
         couID int  not null  COMMENT '課程編號(hào)',
         time timestamp not null DEFAULT(now())
);
 /*添加外鍵約束*/
 alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ;
  /*插入數(shù)據(jù)*/
 insert into student(stuID,stuName,stuSex,stuAge) values(1001,'張三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黃麗麗','女',18),(1005,'李曉輝','女',19),(1006,'張敏','女',18);
 insert into student VALUES(1007,'五條人','男',20),(1008,'胡五伍','女',19);
 insert into courses(couID,couName,couHours,couCredit) values(50,'大學(xué)英語',64,2),(60,'計(jì)算機(jī)基礎(chǔ)',78,2.5),(70,'Java程序設(shè)計(jì)',108,6),(80,'數(shù)據(jù)庫應(yīng)用',48,2.5);
 insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);

use students;
select * from student;

select stuID,stuName from student;

select stuID as '學(xué)號(hào)',stuName as '姓名' from student;

select * from student where stuID=1001;

select * from student where stuSex='男' and stuAge<19;

select * from student where stuSex='女' or stuAge=18;

SELECT * from student where stuID=1001 or stuID=1004;
SELECT * from student where stuID in(1001,1004);

SELECT * from student where stuID BETWEEN 1001 and 1004;

SELECT * from student where stuID not  BETWEEN 1001 and 1004;

SELECT * from student where stuName like '張%';

SELECT * from student where stuName like '%麗%';

SELECT * from student where stuName like '%五%';

SELECT * from student  ORDER BY stuAge asc;
SELECT * from student  ORDER BY stuAge desc;

SELECT * from student  ORDER BY stuAge asc,stuName desc;

select * from student ORDER BY stuAge asc,CONVERT(stuName using gbk) desc;

SELECT COUNT(*) as '學(xué)生總?cè)藬?shù)' from student; 
SELECT COUNT(stuID) as '學(xué)生總?cè)藬?shù)' from student;

SELECT sum(couCredit) as '總學(xué)分' from courses;

SELECT max(stuAge) as '年齡最大的同學(xué)' from student; 
SELECT min(stuAge) as '年齡最小的同學(xué)' from student;

SELECT avg(couHours) as '四門課程平均學(xué)時(shí)數(shù)' from courses;

alter table stu_cou add COLUMN grade FLOAT null;
UPDATE stu_cou set grade=(SELECT FLOOR(50 +RAND() * 50));

生成0-10的隨機(jī)數(shù)
SELECT FLOOR(RAND() * 10)
生成10-100的隨機(jī)數(shù)
SELECT FLOOR(10 +RAND() * 90)

SELECT stuSex as '性別' from student  GROUP  BY stuSex;

SELECT stuID,stuName from student  GROUP  BY stuID;

SELECT stuName,stuSex from student  GROUP  BY stuSex;

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'students.student.stuName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex;

select stuSex,AVG(stuAge) as '平均年齡',COUNT(stuID) as '人數(shù)',MAX(stuAge) as '最大年齡',min(stuAge) as '最小年齡' from student GROUP BY stuSex;

select stuSex,stuAge,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex,stuAge;

alter table student add COLUMN stuColleage varchar(100) null;
update student set stuColleage='大數(shù)據(jù)學(xué)院' where stuID BETWEEN 1001 and  1003;
update student set stuColleage='物流學(xué)院' where stuID BETWEEN 1004 and  1006;
update student set stuColleage='康養(yǎng)學(xué)院' where stuID BETWEEN 1007 and  1008;

select stuColleage,stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuColleage,stuSex;

select stuAge,GROUP_CONCAT(stuName) as '姓名列表',COUNT(stuID) as '人數(shù)' from student GROUP BY stuAge;

select stuID,GROUP_CONCAT(couID) as '課程號(hào)',sum(grade) as '總成績(jī)',avg(grade) as '平均分' from stu_cou GROUP BY stuID;

select stuID,GROUP_CONCAT(couID) as '課程號(hào)',sum(grade) as '總成績(jī)',avg(grade) as '平均分' from stu_cou GROUP BY stuID HAVING avg(grade)>75;

select stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex with rollup;

select * from student LIMIT 1,3;

select * from student LIMIT 4;/*limit 0,4*/

select DISTINCT stuSex from student;