MYSQL基本操作语句-白红宇

MYSQL基本操作语句

阅读量：5110 次

发布时间：2019-06-13

本文共 5255 字，大约阅读时间需要 17 分钟。

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); 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,SsexFROM StudentWHERE 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 StudentWHERE Sdept='hwx'UNIONSELECT *FROM StudentWHERE Sage>=20;

12、视图操作

/*创建视图*/CREATE VIEW V_StudentASSELECT Sno,Sname,SageFROM StudentWHERE Sdept='xj';/*删除视图*/DROP VIEW V_Student;/*查询视图*/SELECT Sno,SnameFROM V_StudentWHERE Sage>50;/*更新视图*/UPDATE V_StudentSET Sname='vname'WHERE Sno='20071089';INSERT INTO V_StudentVALUES ('20081010','vnew',36);

转载于:https://www.cnblogs.com/binmaizhai/archive/2013/03/18/2966016.html

你可能感兴趣的文章