您的位置:晶晶的博客>Linux>MySQL系统学习一:MySQL语法概要

MySQL系统学习一:MySQL语法概要

mysql

先来一组sql中的名词,别被吓着了;看着高大上实际就是一些单纯的名词罢了,理解清楚即可。

  • DDL—数据定义语言[Data Definition Language](Create,Alter,Drop,SHOW、DECLARE)
  • DML—数据操纵语言[Data Manipulation Language](Select,Delete,Update,Insert)
  • DCL—数据控制语言[Data Control Language](GRANT,REVOKE,COMMIT,ROLLBACK)

一、DDL语句

1.1、CREATE关键字

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

其中DATABASE或SCHEMA在mysql层面区别不大;CHARACTER设定数据库的字符集,COLLATE设定数据库的校队集(排序规则)。

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

创建数据表

CREATE TABLE tbl_name (column_define_list)
    [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]] [COMMENT = '表注释文字']

其中column_define_list是一些用于定义数据表字段名、字段类型、默认值等语句。

CREATE TABLE IF NOT EXISTS `demo_table`(
	`id` INT(10) NOT NULL AUTO_INCREMENT, 
	`title` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
	`ename` ENUM('y', 'n', 'x') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y', 
	`content` TEXT NOT NULL, 
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '文章表'

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
 
index_col_name:
    col_name [(length)] [ASC | DESC]

CREATE INDEX被映射到一个ALTER TABLE语句上,用于创建索引。create index语法仅需知晓,透彻了解alert table语法才是关键。

1.2、ALTER关键字

修改数据库全局特性

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

修改数据库相关全局特性,基本语法结构与create database类似,通过ALERT关键字更改database基础功能就是修改database的字符集和排序规则。

修改数据表

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

ALTER TABLE用于更改原有表的结构,是一个必须掌握的sql命令。可以增加或删减字段(列),创建或取消索引,更改原有字段(列)的类型,或重新命名字段(列)或表;还可以更改表(以及字段)的注释和表的类型。 

来几个示例:

sql
sql

二、DML语句

DML语句也就是使用频率非常高的“增(insert)、删(delete)、改(update)、查(select)”,一直以为自己会使用这四个关键字,而其实仅仅是了解了DML语句的初步用法,通过复杂的DML语法可以实现很多以前只会在程序中(例如PHP)实现的功能。

DML语句仅列出相关语法结构,有些语句需要单独列出来汇总学习。

2.1、SELECT关键字

select语句用于从一个或多个表中查询条件指定的行,并可以加入union语句和子查询。语法结构如下:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

最常用的select类型:select column_name1[ as alias1],column2[as alias2] from table_name where exp [order by DESC|ASC] [limit n,m] 

2.2、DELETE关键字

单表语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
多表语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]
或:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

2.3、UPDATE关键字

单表语法:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
多表语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

2.4、INSERT关键字

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

三、DCL语句

DCL语句是数据库控制功能语句,是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句,DCL语句不需要一般开发者非常了解,仅需知道有这么个语句即可。一般专业做DBA的数据库管理员才需要深入了解这些语句;默认情况下DCL语句只有系统管理员、数据库管理员才有权限执行。

转载请注明本文标题和链接:《MySQL系统学习一:MySQL语法概要

相关推荐

网友评论73

路人甲 表情
Ctrl+Enter快速提交
  1. #1

    <a href="http://www.moldedeps.com/pjs-parajumpers-parajumpersr">pjs parajumpers</a> <a href="http://www.ridderdrive.com/nike-lunar-presto-sort-bl%C3%A5-glow-obuve">nike lunar presto sort bl氓 glow</a> <a href="http://www.cartsdk.com/ralph-lauren-polo-team-usa-update-poloa">ralph lauren polo team usa update</a> <a href="http://www.cockaweek.com/fendi-handbag-styles-of-art-painting-fendib">fendi handbag styles of art painting</a> <a href="http://www.entrustengage.com/nobis-cindy-parka-womens-jacket-nobisb">nobis cindy parka womens jacket</a> <a href="http://www.hollerrealtor.com/eric-kush-womens-navy-blue-nike-nfl-chicago-bears-long-sleeve-t-shirt-backer-64-nfls">eric kush womens navy blue nike nfl chicago bears long sleeve t shirt backer 64</a>
    coberturatic http://www.coberturatic.com/

    coberturatic 约2小时前 回复
  2. #2

    <a href="http://www.maracamp.com/nike-air-huarache-ultra-fl%C3%A5den-obuvb">nike air huarache ultra fl氓den</a> <a href="http://www.offleaseford.com/nike-kd-gelb-braun-butya">nike kd gelb braun</a> <a href="http://www.suncreamclub.com/tory-burch-ballet-flats-nordstrom-toryburchr">tory burch ballet flats nordstrom</a> <a href="http://www.alusownia.com/nobis-jacket-weather-uk-nobisb">nobis jacket weather uk</a> <a href="http://www.breachurch.com/mackage-jacket-winnipeg-mackager">mackage jacket winnipeg</a> <a href="http://www.janetsher.com/nike-mennns-lunar-superbad-pro-detachable-football-cleats-kurpese">nike mennns lunar superbad pro detachable football cleats</a>
    haceryeni http://www.haceryeni.com/

    haceryeni 约2小时前 回复
  3. #3

    <a href="http://www.stonemartusa.com/chaussure-a-talon-bout-rond-bottew">chaussure a talon bout rond</a> <a href="http://www.brandstageinc.com/femmes-supra-tk-society-noir-or-sneakerj">femmes supra tk society noir or</a> <a href="http://www.clipartwow.com/jordan-super-fly-2-hvid-bl%C3%A5-butyf">jordan super fly 2 hvid bl氓</a> <a href="http://www.rugstv.com/nike-air-max-zero-2015-for-vendita-runningj">nike air max zero 2015 for vendita</a> <a href="http://www.modenvelope.com/adidas-damian-lillard-2-argento-marrone-trainerse">adidas damian lillard 2 argento marrone</a> <a href="http://www.getallmusic.com/cuissarde-santiag-bottey">cuissarde santiag</a>
    citraselular http://www.citraselular.com/

    citraselular 约4小时前 回复
  4. #4

    <a href="http://www.yesupmedia.com/zapatos-tac%C3%B3n-el-corte-ingl%C3%A9s-botaq">zapatos tac贸n el corte ingl茅s</a> <a href="http://www.catwalkprince.com/game-womens-arian-foster-black-jersey-nfl-nike-houston-texans-23-fashion-nfln">game womens arian foster black jersey nfl nike houston texans 23 fashion</a> <a href="http://www.nazkhaliq.com/buty-sportowe-ocieplane-tenisv">buty sportowe ocieplane</a> <a href="http://www.atthebutchers.com/cream-ribbed-midi-dress-dressp">cream ribbed midi dress</a> <a href="http://www.firsthelptech.com/zara-rebajas-vestidos-mujer-vestidost">zara rebajas vestidos mujer</a> <a href="http://www.ladyesah.com/botines-oto%C3%B1o-invierno-botaq">botines oto帽o invierno</a>
    kwesefied http://www.kwesefied.com/

    kwesefied 约4小时前 回复
  5. #5

    <a href="http://www.wmkiwanis.com/mens-fisherman-cap-capp">mens fisherman cap</a> <a href="http://www.ourkidsgalaxy.com/jordan-para-ni%C3%B1os-precios-chandalo">jordan para ni帽os precios</a> <a href="http://www.speedkuji.com/anna-leopard-shoes-bootsy">anna leopard shoes</a> <a href="http://www.estausaapply.com/nike-shirt-with-just-do-it-all-over-shirts">nike shirt with just do it all over</a> <a href="http://www.escadamyob.com/pochette-patrizia-pepe-argento-borsep">pochette patrizia pepe argento</a> <a href="http://www.bestofaacc.com/dfb-hose-kinder-hoseq">dfb hose kinder</a>
    lbennettcpa http://www.lbennettcpa.com/

    lbennettcpa 约5小时前 回复
  6. #6

    <a href="http://www.ankaraevent.com/gazelle-cutout-shoesq">gazelle cutout</a> <a href="http://www.ojoelectrico.com/catalogo-scarpe-nero-giardini-primavera-estate-2019-stivalip">catalogo scarpe nero giardini primavera estate 2019</a> <a href="http://www.thetiedyeguys.com/dinosaur-costume-hat-capp">dinosaur costume hat</a> <a href="http://www.finmedex.com/cr7-zapatillas-ni%C3%B1o-chandalo">cr7 zapatillas ni帽o</a> <a href="http://www.bysandrillon.com/brooks-ariel-size-9-bootsy">brooks ariel size 9</a> <a href="http://www.aminablue.com/womens-dri-fit-bermuda-shorts-shirtu">womens dri fit bermuda shorts</a>
    nickrileyband http://www.nickrileyband.com/

    nickrileyband 约5小时前 回复
  7. #7

    <a href="http://www.fmpinsurance.com/puma-anz%C3%BCge-hoseq">puma anz眉ge</a> <a href="http://www.arshopusa.com/nike-training-shirt-crossfit-shirto">nike training shirt crossfit</a> <a href="http://www.jurgendhaese.com/miami-heat-wade-jersey-city-edition-nflp">miami heat wade jersey city edition</a> <a href="http://www.ezartscreens.com/2-piece-dress-set-plus-size-dressu">2 piece dress set plus size</a> <a href="http://www.ultimatribo.com/nike-cortez-velours-blanc-chaussuresq">nike cortez velours blanc</a> <a href="http://www.simonbelling.com/trefoil-adidas-tank-shirtt">trefoil adidas tank</a>
    [url=http://www.fissorihome.com/]fissorihome[/url]

    fissorihome 约17小时前 回复
  8. #8

    <a href="http://www.squillantes.com/tacones-negros-tacon-grueso-botax">tacones negros tacon grueso</a> <a href="http://www.dcopars.com/adidas-falcon-raw-pink-dark-blue-shoesq">adidas falcon raw pink dark blue</a> <a href="http://www.highvibesclub.com/marca-scarpe-uomo-stivalip">marca scarpe uomo</a> <a href="http://www.qiaozeyang.com/bucket-hat-sun-protection-capp">bucket hat sun protection</a> <a href="http://www.medicalbdnews.com/tenis-nike-para-ni%C3%B1a-2019-chandalo">tenis nike para ni帽a 2019</a> <a href="http://www.mediumcheri.com/fade-rst-golf-shoes-bootsw">fade rst golf shoes</a>
    [url=http://www.uhipsik.com/]uhipsik[/url]

    uhipsik 约17小时前 回复
  9. #9

    <a href="http://www.floozyspeak.com/canada-goose-trillium-parka-6550l-canadagooser">canada goose trillium parka 6550l</a> <a href="http://www.rapamateur.com/mens-nike-new-orleans-saints-23-marshon-lattimore-limited-olivecamo-2017-salute-to-service-nfl-jersey-nflz">mens nike new orleans saints 23 marshon lattimore limited olivecamo 2017 salute to service nfl jersey</a> <a href="http://www.sarkariresurt.com/nobis-vest-uk-house-nobisb">nobis vest uk house</a> <a href="http://www.checkmyprices.com/nike-air-force-1-denim-low-footc">nike air force 1 denim low</a> <a href="http://www.oriturk.com/tory-burch-boots-size-12-toryburchr">tory burch boots size 12</a> <a href="http://www.parasolphoto.com/herren-nike-huarache-utility-gold-gr%C3%BCn-butyd">herren nike huarache utility gold gr眉n</a>
    gagnetoo http://www.gagnetoo.com/

    gagnetoo 约20小时前 回复
  10. #10

    <a href="http://www.cryptomtge.com/nike-hypervenom-mens-black-silver-foota">nike hypervenom mens black silver</a> <a href="http://www.eaudurable.com/parajumpers-sale-kids-parajumpersr">parajumpers sale kids</a> <a href="http://www.paleofasting.com/air-jordan-7-retro-hvit-gr%C3%A5-lilla-kush-obuvc">air jordan 7 retro hvit gr氓 lilla kush</a> <a href="http://www.seifenstein.com/ralph-lauren-polo-aftershave-miniatures-menu-poloa">ralph lauren polo aftershave miniatures menu</a> <a href="http://www.talentbschool.com/nike-lebron-13-p%C3%BArpura-reign-obuve">nike lebron 13 p煤rpura reign</a> <a href="http://www.deploygis.com/parajumpers-gobi-rust-game-parajumpersb">parajumpers gobi rust game</a>
    connorcabins http://www.connorcabins.com/

    connorcabins 约20小时前 回复
  11. 1 2 3 4 5 6 ... »