MySQLデータベース操作『中』

一、

SELECT COUNT(id) FROM sys_student; 			#     
SELECT MAX(math) FROM sys_student;			#    
SELECT MIN(math) FROM sys_student;			#    
SELECT SUM(mant) FROM sys_student;			#   
SELECT AVG(IFNULL(math,0)) FROM sys_student;#     

二、

SELECT is_male, AVG(math) FROM sys_student GROUP BY is_male;	#     
SELECT is_male, AVG(math) FROM sys_student GROUP BY is_male HAVING COUNT(id)>2;	#     
-- MySql  
SELECT * FROM sys_student LIMIT 3,10;	#    ，    
SELECT * FROM sys_student LIMIT (    -1)*    ,    ;	#     

三、

ALTER TABLE sys_student MODIFY id INT PRIMARY KEY AUTO_INCREMENT; #      
ALTER TABLE sys_student MODIFY id INT;	#       

四、

ALTER TABLE sys_student MODIFY is_male TINYINT NOT NULL;	#       
ALTER TABLE sys_student MODIFY is_male TINYINT;		#       

五、

ALTER TABLE sys_student MODIFY id INT UNIQUE; 		#       
ALTER TABLE sys_student DROP INDEX name;	#       

六、

ALTER TABLE sys_student ADD CONSTRAINT FK_student_class FOREIGN KEY(class_id) REFERENCES  sys_class(id);	#       
ALTER TABLE sys_student DROP FOREIGN KEY FK_student_class;	#       

七、MySql

--   
mysqldump  -uroot -p  my_db  > e://mysql.sql
--   
mysql -uroot -p
CREATE DATABASE my_db2;
SOURCE e://mysql.sql;

： 、

CREATE TABLE sys_student (
	id INT,
	name VARCHAR(10),
	is_male  TINYINT,	# 1: 、0： 
	chinese DECIMAL(4,1),
	math DECIMAL(4,1),
	english DECIMAL(4,1)
);

CREATE TABLE sys_class(
	id INT,
	name VARCHAR(10)
);