0、修改密码:mysqladmin -u root -p password 123456
导出数据库:mysqldump -u root -p yunpay>yunpay.sql
导入数据库:mysql –u root -p yunpay < yunpay.sql
1、为mysql增加一个名为admin,主机名任意的网络用户,其通过密码'123'访问数据库,这个用户拥有对数据库的所有操作权限(ALL PRIVILEGES)
CREATE USER 'admin' @ '%' IDENTIFIED BY '123' ; GRANT ALL PRIVILEGES ON * . * TO 'admin' @ '%' IDENTIFIED BY '123' WITH GRANT OPTION; |
2、删除该用户
DROP USER 'wbhuang' @ '%' ; |
3、创建数据库
CREATE DATABASE school; USE school; SHOW TABLES; |
4、删除、创建数据表
删除数据表时,有如下语法
DROP TABLE <表名> [RESTRICT | CASCADE]; |
当选择RESTRICT:则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如CHECK,FOREIGN KEY等约束),不能有视图,触发器,存储过程和函数,否则不能删除。如果选择CASCADE:则删除基本表的同事,相关的依赖对象,例如视图,都将被一起删除。下面语句的"SET FOREGIN_KEY_CHECKS = 0;"为取消CHECKS依赖。可见MYSQL默认的删除表方式为RESTRICT(受约束的)。
SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS Student; DROP TABLE IF EXISTS Course; DROP TABLE IF EXISTS SC; CREATE TABLE Student (Sno CHAR (9) PRIMARY KEY, Sname CHAR (20) UNIQUE, Ssex CHAR (2), Sage SMALLINT, Sdept CHAR (20) ); CREATE TABLE Course (Cno CHAR (4) PRIMARY KEY, Cname CHAR (40), Cpno CHAR (4), /*先修课*/ Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) /*表级完整性约束条件,Cpno是外码,被参照表是Course,被参照列是Cno*/ ); CREATE TABLE SC (Sno CHAR (9), Cno CHAR (4), Grade SMALLINT, PRIMARY KEY (Sno,Cno), /*主码由两个属性构成,必须作为表级完整性进行定义*/ FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ); |
5、修改数据表
ALTER TABLE Student DROP COLUMN Sentry; ALTER TABLE Student ADD Sentrance DATE; ALTER TABLE Student CHANGE Sentrance Sentry DATE; /*为Student增加"入学时间"列*/ ALTER TABLE Student MODIFY COLUMN Sage INT ; /*将年龄的数据类型由字符型改为整形*/ ALTER TABLE Course ADD UNIQUE(Cname); /*增加课程名称必须取唯一值的约束条件*/ |
6、删除、创建索引表
DROP INDEX Stusno ON Student; DROP INDEX Coucno ON Course; DROP INDEX SCno ON SC; CREATE UNIQUE INDEX Stusno ON Student(Sno); /*按课程号升序建唯一索引*/ CREATE UNIQUE INDEX Coucno ON Course(Cno); /*SC表按学号升序和课程号降序建唯一索引*/ CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC); <br>SELECT * FROM INDEX Stusno; |
7、插入数据
INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES( '20071025' , 'wbhuang' , 'male' ,23, 'math' , '2007-09-01' ); INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES( '20071026' , 'dkluo' , 'male' ,24, 'math' , '2007-09-01' ); INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES( '20071005' , 'hlyang' , 'male' ,24, 'math' , '2007-09-01' ); INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES( '20071007' , 'ljhu' , 'male' ,24, 'math' , '2007-09-01' ); INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES( '20071024' , 'yluo' , 'male' ,24, 'math' , '2007-09-01' ); |
INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES( '100' , 'Chinese' , '100' ,4); INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES( '101' , 'English' , '100' ,3); INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES( '102' , 'Science' , '100' ,2); INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES( '103' , 'Math' , '100' ,5); |
8、修改、删除数据
UPDATE `school`.`student` SET `Sname` = 'wbhuang' ,`Sdept` = 'xinji' WHERE `student`.`Sno` = '20071025' ; DELETE FROM Student WHERE Sno= '20071025' ; |
9、普通查询和聚集函数
SELECT Sno,Sname,Ssex FROM Student WHERE Sno= '20071004' ; SELECT COUNT(DISTINCT Sname) FROM Student; SELECT AVG(Sage) FROM Student; SELECT Cno FROM Student ORDER BY Sage DESC; ORDER BY <列名|,列名> [ASC | DESC]; ASC升序,DESC降序 COUNT([DISTINCT | ALL] * ) 统计元素个数 COUNT([DISTINCT | ALL] <列名> ) 统计一列中元素个数 SUM([DISTINCT | ALL] <列名> ) 计算一列值的总和(数值型) AVG([DISTINCT | ALL] <列名> ) 计算一列值的平均值(数值型) MAX([DISTINCT | ALL] <列名> ) 计算一列值的最大值 MIX([DISTINCT | ALL] <列名> ) 计算一列值的最小值 GROUP BY 子句将查询结果按某一列或多列的值分组,值相等的为一组。 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; /*Cno值相等的为一组,计算各组的COUNT(Sno)*/ |
10、连接查询
/*等值,非等值连接:比较的连接谓词有=、<、>、>=、<=、!=(或<>)等*/ SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno; /*若在等值连接中把目标列的重复的属性去掉则为自然连接*/ SELECT Student.*,SC.Cno,SC.Grade FROM Student,SC WHERE Student.Sno=SC.Sno; /*自身连接:一个表与自己进行连接*/ SELECT FIRST.*,SECOND.* FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=SECOND.Cno; /*外连接:若某个Student没有选课,仍把舍弃的Student元组保存在结果中,其SC的属性全填NULL*/ SELECT * FROM Student LEFT JOIN SC ON (Student.Sno=SC.Sno); |
11、嵌套查询
/*去掉Student.Cname的UNIQUE*/ ALTER TABLE Student DROP INDEX Sname; /*带有比较运算符的子查询*/ SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = ( SELECT Sdept FROM Student WHERE Sno= '20071004' ); SELECT Sno,Sname,Sdept FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Grade>=5); /*带有ANY(SOME)或ALL谓词的子查询*/ SELECT Sno,Sname,Sage,Ssex FROM Student WHERE Sage>ANY ( SELECT Sage FROM Student WHERE Ssex= 'fe' ); /*带有[NOT] EXISTS谓词的子查询*/ /*EXISTS谓词的子查询不反悔任何数据,只产生逻辑真与假*/ /*拿外层的元组逐个放在内层中判断是否EXIST,如果为真则将元组放入结果集*/ SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '100' ); /*集合查询:UNION并集,INTERSECT交集,EXCEPT差集*/ SELECT Sno FROM Student WHERE Sdept= 'hwx' UNION SELECT * FROM Student WHERE Sage>=20; |
12、视图操作
/*创建视图*/ CREATE VIEW V_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'xj' ; /*删除视图*/ DROP VIEW V_Student; /*查询视图*/ SELECT Sno,Sname FROM V_Student WHERE Sage>50; /*更新视图*/ UPDATE V_Student SET Sname= 'vname' WHERE Sno= '20071089' ; INSERT INTO V_Student VALUES ( '20081010' , 'vnew' ,36); |