首页 / MYSQL / MYSQL基本操作语句
MYSQL基本操作语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL基本操作语句,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5235字,纯文字阅读大概需要8分钟。
内容图文
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);
|
原文:http://www.cnblogs.com/tiandao/p/4921537.html
内容总结
以上是互联网集市为您收集整理的MYSQL基本操作语句全部内容,希望文章能够帮你解决MYSQL基本操作语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。