How to use MySQL

Show all databases: show databases;

Creating database: Create database name; Delete database: Drop databases name;  view table structure: describe (DESC) table name; View table Detail structure: Show create TABLE table name Modify table: 1. Modify the table name the old table name rename the new table name; 2. Modify the field ALTER TABLE name change old property name new property name new data Type 3. Add field Alter table  table name add property name 1 data class Type [FULL constraint] [first | After property name 2]4. Delete field ALTER TABLE name drop property name   CREATE TABLE: Syntax: CREATE TABLE Table name (property name data type [integrity constraint]); Example: Create Table T_booktype (   ID int primary key auto_increment,                     booktypename varchar,                     booktypedesc varchar ($)                   );               create table T_book (ID int primary KEY auto_increment,   bookname varchar),   author varchar (, )  price decimal (6,2),   booktypeid INT,&NBsp  constraint ' FK ' foreign Key (' Booktypeid ') references ' t_booktype ' (' id ')    );   constraint: Primary Key: Identifies the property as the primary key for the table and can uniquely identify the corresponding record. FOREIGN key: Identifies the property as the foreign key of the table, associated with a table's primary key. Not NULL: Identity This property cannot be empty. Unique: The value that identifies the property is unique. Auto_increment: The value that identifies the property is automatically incremented. Default: Sets the value for the property. 1. Query a table:     select * from table name;  2. Query the specified field: Select Field 1, Field 2, field 3....from table name;  3. Where Condition query:select  field 1, Field 2, Field 3 Frome table name where conditional expression; example: SELECT * from T_studect where id=1;      &nb Sp;select * from T_student where age>22; 4. With in keyword query:select  field 1, Field 2 Frome table name where field [Not]in (element 1, Element 2) For example: SELECT * from T_student where age in (21,23);       select * from T_student 21,23)  5. Range query with between and:select  Field 1, Field 2 Frome table name where field [Not]between value 1 and take value 2; Example: SELECT * Frome t_student where age between and 29;       select * Frome T_student where the age is not between and 29; 6. With Like fuzzy query:select  field 1, field 2 ... fRome table name where field [not] like ' string ';    '% ' for any character;    ' _ ' stands for single character; example: SELECT * Frome t_student where stuname Like ' Zhang San ';       select * Frome t_student where stuname like ' Zhang San% ';     &nbs P; select * Frome t_student where stuname like '% Zhang San% ';//any character containing Zhang San        select * Frome T_stu Dent where Stuname like ' Zhang San _ '  7. Null value query:select  field 1, field 2...frome table name where field  is[not] null; 8. Multi-criteria query with and:select  field 1, field 2...frome table name where conditional expression 1 and conditional expression 2 [and conditional expression N] Example: SELECT * Frome t_student where gradename= ' one year Level ' and age=23; 9. Multi-criteria query with or select  field 1, field 2...frome table name where conditional expression 1 or conditional expression 2 [or conditional expression N] Example: SELECT * Frome t_student w Here gradename= ' first grade ' or age=23;//or, if the condition satisfies a  10.distinct to repeat the query: the SELECT DISTINCT field name from the table name; sort the query result order By:select field 1, field 2...from table name order by property name [Asc|desc] Example: SELECT * Frome t_student ORDER by age desc;//descending, from big to small         select * Frome t_student OrderBy the Age asc;//ascending, ASC default can not write  12. Group query group Bygroup by property name [having conditional expression][with Rollup] Subquery 1. Subquery with in keyword (the condition of one query statement may fall in the query result of another SELECT statement) SELECT * from T_book where booktype in (select ID from T_booktype); SELECT * from T_book where booktype not in (select ID from T_booktype);  2. Subquery with comparison operator (the subquery can use the comparison operator) select * FROM T_book where price>= (select Price from T_pricelevel where pricelevel=1),  3. Subquery with exists keyword (join subquery query to record, The outer query is performed, otherwise the outer query is not executed) SELECT * FROM T_book where exists (SELECT * from T_booktype); select * FROM T_book where not ex Ists (SELECT * from T_booktype);  4. A subquery with the ANY keyword (any keyword that satisfies any of these conditions) SELECT * from T_book where Price>= any ( Select price from T_pricelevel);  5. A subquery with the ALL keyword (all keyword indicates that all conditions are met) SELECT * from T_book where Price>= all ( Select Price from t_pricelevel);   merge query  1.union using the Union keyword is that the database system merges all the query results together and then removes the same record; Select ID The From T_book union select ID from t_booktype; 2.union all uses union ALL and does not remove duplicate records; selThe ECT ID from the t_book union all select ID from the t_booktype;  insert operation inserts a record: Insert Into t_book values ( NULL, ' I love my home ', 20, ' Zhang San ', 1;  insert the specified field: Insert Into t_book (bookname,author)  values (' I love my home ', ' Zhang San ');  Insert Multiple values: Insert Into t_book (bookname,author)  values (' I love my home ', ' Zhang San '), (' My Home ', ' John Doe ') update one data in;  update: Update  t_book set bookname= ' Java programming ideas ', price=120 where id=1;  update several data: update t_book  Set bookname where bookname like '% i love my home% ';  Delete Deletes a piece of data:delete t_book where id=5;  Delete more than one data: Delete t_book where bookname= ' me ';  ALTER TABLE ：添加，修改，删除表的列，约束等表的定义。 查看列： desc 表名; 修改表名： alter table t_book rename  to bbb; 添加列： alter table 表名  add column 列名  varchar (30); 删除列： alter table 表名  drop column 列名; 修改列名MySQL：  alter table bbb change nnnnn hh  int ; 修改列名SQLServer： exec sp_rename ‘t_student.name‘ , ‘nn‘ , ‘column‘ ; 修改列名Oracle：lter  table bbb rename  column nnnnn  to hh  int ; 修改列属性： alter table t_book  modify name varchar (22); sp_rename：SQLServer 内置的存储过程，用与修改表的定义。 MySQL 查看约束，添加约束，删除约束 添加列，修改列，删除列 查看表的字段信息： desc 表名; 查看表的所有信息：show  create table 表名; 添加主键约束： alter table 表名  add constraint 主键 （形如：PK_表名）  primary key 表名(主键字段); 添加外键约束： alter table 从表  add constraint 外键（形如：FK_从表_主表）  foreign key 从表(外键字段)  references 主表(主键字段); 删除主键约束： alter table 表名  drop primary key ; 删除外键约束： alter table 表名  drop foreign key 外键（区分大小写）; 修改表名： alter table t_book rename  to bbb; 添加列： alter table 表名  add column 列名  varchar (30); 删除列： alter table 表名  drop column 列名; 修改列名MySQL：  alter table bbb change nnnnn hh  int ; 修改列名SQLServer： exec sp_rename ‘t_student.name‘ , ‘nn‘ , ‘column‘ ; 修改列名Oracle： alter table bbb rename  column nnnnn  to hh  int ; 修改列属性： alter table t_book  modify name varchar (22); sp_rename：SQLServer 内置的存储过程，用与修改表的定义。

-the first question queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student

--The second question queries all the teachers ' units that are not duplicates of the depart column.
Select distinct depart from Teacher

--the third question queries all records of the student table.
SELECT * FROM Student

--The fourth question queries the score table for all records from 60 to 80.
SELECT * from score where degree between and 80

--The fifth query score a record of 85, 86 or 88 in the table.
SELECT * from score where degree in (' 85 ', ' 86 ', ' 88 ')

--The Sixth Query student table in the "95031" class or sex for "female" students record.
SELECT * FROM student where class= ' 95031 ' or ssex= ' female '

--Question seventh queries all records of the student table in descending order of class.
SELECT * FROM student ORDER BY Class DESC

--Question eighth queries all records of the score table in CNO Ascending, degree descending order.
SELECT * FROM Score ORDER by Cno Asc,degree desc

--The number of students in the "95031" class is queried in question Nineth.
Select COUNT (*) from student where class= ' 95031 '--* can be converted to primary key value

--The tenth question inquires the student number and course number of the highest score in the score table. (sub-query or sort)
Select Sno,cno from score where degree= (select MAX (degree) from score)

--The 11th question inquires the average score of each course.
Select Cno,avg (degree) as average split from score group by Cno

Select Cname from Course where Cno in (select Cno A from score group by Cno)
Union
Select Cno,avg (degree) from score Group by Cno

-The 12th question is the average score of a course in which at least 5 students are enrolled in the score table and begin with 3.
Select AVG (degree) from score where Cno like ' 3% ' GROUP by Cno have COUNT (Cno) >4

--The 13th Question query score is greater than 70, less than 90 of the SNO column.
Select Sno from score where degree between and 90

--Question 14th queries All students for sname, CNO and degree columns.
Select Sname,cno,degree from student joins score on student. Sno=score.sno

--Question 15th queries All students for SNO, CNAME, and degree columns.
Select Sno,cname,degree from score join Course on COURSE.CNO=SCORE.CNO

--Question 16th queries all students for sname, CNAME, and degree columns.
Select Student. Sname,cname,degree from student joins score on student. Sno=score.sno Join Course on COURSE.CNO=SCORE.CNO

--the 17th question inquires the average score of "95033" class students.
Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ')

Select AVG (degree) from score,student where student. Sno=score.sno and class= ' 95033 '

--The 18th question assumes that a grade table is created using the following command:
CREATE table grade (Low Int,upp Int,rank char (1))
Insert into grade values (90,100, ' A ')
Insert into grade values (80,89, ' B ')
Insert into grade values (70,79, ' C ')
Insert into grade values (60,69, ' D ')
Insert into grade values (0,59, ' E ')
--Sno, CNO and rank columns are now available for all students.

Select Sno,cno,degree,[rank] from grade join score on score.degree between Low and UPP

Select Sno,cno,degree,[rank] from Score,grade where degree between low and UPP

--The 19th query elective "3-105" course performance is higher than the "109" student scores of all the students record.
SELECT * from Student,score where score.cno= ' 3-105 ' and student. Sno=score.sno and score.degree> (select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

--the 20th query score the number of students who choose to learn more than the highest score of the record.
SELECT * from score a WHERE degree < (select MAX (degree) from score b where a.cno=b.cno) and Sno in (select Sno from Scor E GROUP BY Sno have Count (*) >1)

--The 21st query result is higher than the school number of "109", the course number is "3-105" of all records.
SELECT * from Student,score where student. Sno=score.sno and score.degree> (select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

Sno, Sname and Sbirthday of all students who were born in the same year as the 22nd query and student number 107.
Select Sno,sname,sbirthday from student where year (student. Sbirthday) = (select year (sbirthday) from student where sno= ' 107 ')

--The 23rd question inquires "Zhang Xu" the student achievement which the teacher teaches.
--select degree from Score,teacher,course where Teacher.tname= ' Zhang Xu ' and Teacher.tno=course.tno and Course.cno=score.cno

Select Sno,cno,degree from score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname= ' Zhang Asahi '))

--the 24th question is the name of the teacher who has more than 5 students in a course.
Select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score GROUP by Cno has COU NT (*) >5))

--The 25th question inquires the records of 95033 classes and 95031 classes of all students.
SELECT * FROM student where class= ' 95033 ' or class= ' 95031 '

--The 26th question query existence has 85 points above the course CNO.
Select distinct CNO from score where degree>85

--the 27th question inquires into the results table of the teachers ' courses taught by the "computer department".
Select Sno,cno, degree from score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where depart= ' Computer system '))

--The 28th query "computer Department" and "Electronic Engineering department" different titles of teachers Tname and Prof. Working with related sub-queries
Select Tname,prof from Teacher a where Prof not in (select Prof from Teacher b where A.depart!=b.depart)

--the 29th question inquires into the CNO, Sno and degree of the students with the elective number "3-105" and the grade at least higher than the elective number "3-245" course, and is sorted by degree from highest to lowest order.
Select Cno,sno,degree from Score a where (select degree from score b where cno= ' 3-105 ' and B.sno=a.sno) >= (select degree From score C where cno= ' 3-245 ' and C.sno=a.sno] ORDER by degree DESC

SELECT * FROM score where cno= ' 3-105 ' and degree >any (select degree from score where cno= ' 3-245 ')

-the 30th question inquires into the CNO, Sno and degree of the students with the elective number "3-105" and higher than the elective number "3-245" course.
Select Cno,sno,degree from Score a where (select degree from score b where cno= ' 3-105 ' and B.sno=a.sno) > (select degree From score C where cno= ' 3-245 ' and C.sno=a.sno)

--Question 31st queries all teachers and classmates for name, sex and birthday.
SELECT DISTINCT Sname as name,ssex as sex,sbirthday as birthday from student
Union
SELECT DISTINCT Tname as name,tsex as sex,tbirthady as birthday from Teacher

--the 32nd question inquires into the name, sex and birthday of all "female" teachers and "female" classmates.
SELECT DISTINCT Sname as name,ssex as sex,sbirthday as birthday from student where ssex= ' women '
Union
SELECT DISTINCT Tname as name,tsex as sex,tbirthady as birthday from Teacher where tsex= ' women '

-The 33rd question is the result table of the students who have a lower average score than the course.
Select Sno,cno,degree from Score a where a.degree< (select AVG (degree) from score b where a.cno=b.cno)

--The 34th question inquires the tname and depart of all the teachers in the classroom.
Select Tname,depart from Teacher where tname in (select distinct tname from Teacher,course,score where Teacher.tno=course. Tno and Course.cno=score.cno)

Select Tname,depart from Teacher where TNO in (select Tno from Course where Cno in (select distinct Cno from score))

--The 35th question inquires the tname and depart of all teachers who have not lectured.
Select Tname,depart from Teacher where Tname not in (select distinct tname from Teacher,course,score where Teacher.tno=cou Rse. Tno and Course.cno=score.cno)
--the 36th question inquires at least 2 boys ' class number.
Select Class from student where ssex= ' man ' GROUP by Class has COUNT (*) >1

--the 37th question inquires student The student record that does not have surname "Wang" in the table.
SELECT * FROM student where Sname don't like (' King% ')

--Question 38th queries the name and age of each student in the student table.
Select Sname,year (GETDATE ())-year (sbirthday) from student

--Question 39th queries the maximum and minimum sbirthday date values in the student table.
Select Max (sbirthday) as Maximum, Min (sbirthday) as min from student

--The 40th question is to query all records in the student table in order of class number and age from large to small.
SELECT * FROM Student ORDER by Class Desc,sbirthday ASC

-The 41st question is about "male" teachers and their courses.
Select Tname,cname from Teacher,course where tsex= ' man ' and Teacher.tno=course.tno

--The 42nd question query the highest score classmate's Sno, CNO and degree column.
Select Sno,cno,degree from score where degree= (select MAX (degree) from score)

Select top 1* from score order BY degree DESC

--The 43rd query and "Li June" with the sex of all students sname.
Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ')

--The 44th query and "Li June" with the same sex and classmates sname.
Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ') and class= (select Class from student where Sname= ' Li June ')

--The 45th question inquires all the "male" students ' grades in the course of "Introduction to Computer".
Select Sno,degree from score where Sno in (select Sno from student where ssex= ' man ') and Cno in (select Cno from Course wher E cname= ' Introduction to Computers ')

How to use MySQL