mysql数据库要点总结_mysql数据库学习总结

mysql数据库要点总结由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“mysql数据库学习总结”。

查询(R)Selec子句

书写顺序

Select distinct：要返回的列或表达式 From：从中检索数据的表 Where：行级过滤/分组前过滤 group by：分组说明

having：组级过滤/分组后过滤 order by：输出排序顺序desc/asc limit start, count：要检索的行数 limit 3,4 从第3行开始的连续4行

SELECT order_num,sum(quantity*item_price)ordertotal FROM orderitems WHEREorder_numBETWEEN20005AND 20009GROUP BY order_num HAVINGsum(quantity*item_price)>=50ORDER BYordertotalDESCLIMIT4;执行顺序

From表名->inner/left/right/ join on->where->group by->select distinct *->having->order by->limit start, count 插入Insert into(C)INSERT INTO students(name)values(‘杨过’),(‘小龙女’);更新(U)Update UPDATE customers SETcust_email = ‘elmer@fudd.com’WHEREcust_id = 10005;删除(D)Delete DELETE FROM customers WHEREcust_id = 10006;Truncate删除原来的表并重新创建一个表，删除标的全部内容时效率高。

操作表

创建CREATE TABLE customers(Cust_idint not null auto_increment primary key,Cust_name char(50)not null,Cust_addrechar(50)null)engine = innoDB;更新ALTER TABLE删除DROP TABLEcustomers2;重命名RENAME TABLE customers2 TO customers;操作数据库

创建CREATE DATABASE xxx charset = utf8;删除DROP DATABASEXXX;切换USE XXX;查看SHOW DATABASES;关联查询

INNER/LEFT/RIGHTJOIN ON SELECT students.name,subjects.title,scores.scoreFROM scores INNER JOIN students ONscores.stuid = students.id INNER JOIN subjects ONscores.subid = subjects.id;