在介绍这些SQL语言之前，先罗列一下mysql的常用数据类型和数据类型修饰，供查询参考后面的带数字表示此类型的字段长度数值型：TINYINT1，SMALLINT2，MEDIUMINT3
(字段定义会丢失,数据会保留)
mysql>ALTER TABLE students RENAME TO stu;mysql>RENAME TABLE stu TO students;
添加一个外键约束
ALTER TABLE students ADD FOREIGN KEY foreign _cid (CID) REFERENCES course (CID);
创建索引
CREATE INDEX index_name ON TABLE (col_name[(length)] [ASC|DESC]) [USING {BTREE|HASH}];
删除索引
DROP INDEX index_name ON TBNAME;
查看表状态:SHOW STATUS LIKE ‘TBNAME’;
查看表的索引:SHOW INDEXES FROM TBNAME;
DML
—-Data Manipulation Language 数据操纵语言
如insert,delete,update,select(插入、删除、修改、检索)
插入修改数据
#如果每个字段都有值，不需要写字段名称,每组值用,隔开
mysql>INSERT INTO tb_name (col1,col2) VALUES (‘STRING’,NUM),(‘STRING’,NUM);
mysql>INSERT INTO tb_name SET col1=’string’,col2=’string’;
mysql>INSERT INTO tb_name (col1,col2,col3) SELECT…;
EXAMPLE:
mysql>INSERT INTO students (Name,Gender,teacher) VALUE (‘lujunyi’,’M’,’mage’),(‘wusong’,’M’,’zhuima’);mysql>INSERT INTO students SET,Gender=’M’,tearcher=’zhuima’;
更新数据
mysql>UPDATE tb_name SET column=value WHERE column=value;mysql>UPDATE students SET Course=’mysql’ WHERE;
#查询年龄大于平均年龄的数据
mysql>SELECT * FROM students WHERE Age > (SELECT AVG(Age) FROM students);
#在FROM中使用子查询
mysql>SELECT Name,Age FROM (SELECT * FROM students WHERE CID IN (2,3)) AS t WHERE Age>20;
#联合查询
mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
创建视图
CREATE VIEW VIEW_NAME AS SELECT….
DCL
—-Data Control Language 数据库控制语言
如grant,deny,revoke等，只有管理员才有这样的权限。
创建用户
mysql>CREATE USER ‘USERNAME’@’HOST’ IDENTIFIED BY ‘PASSWORD’
删除用户
mysql>DROP USER ‘USERNAME’@’HOSHOST支持通配符
_:任意单个字符
%:任意多个字符
授权
mysql>GRANT pri1,pri2…ON DB_NAME.TB_NAME TO ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘PASSWORD’]
取消授权
mysql>REVOKE pri1,pri2…ON DB_NAME.TB_NAME FROM ‘USERNAME’@’HOST’;
查看授权
mysql>SHOW GRANTS FOR ‘USERNAME’@’HOST’;
EXAMPLE:
mysql>CREATE USER ‘lujunyi’@’%’ IDENTIFIED BY ‘123456’;mysql>SHOW GRANTS FOR ‘lujunyi’@’%’;mysql>GRANT ALL PRIVILEGES ON testdb.* TO ‘lujunyi’@’%’;
本文出自 “lustlost-迷失在欲望之中” 博客，虚拟主机，香港服务器，请务必保留此出处
，美国空间
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1854653.html
