MYSQL基础笔记(二)-SQL基本操作

时间:2023-01-04 18:41:40

SQL基本操作

基本操作:CRUD,增删改查

  将SQL的基本操作根据操作对象进行分类:

  1、库操作

  2、表操作

  3、数据操作

库操作:

对数据库的增删改查

  新增数据库:

  基本语法:

 Create database 数据库名字[库选项];

  库选项:用来约束数据库,分为两个选项:1、字符集设定:charset/character set 具体字符集(数据存储的编码格式,中文常用字符集gbk和utf8.)。2、校对集设定:collate 具体校对集(数据比较的规则)。

  例如:

  aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAoAAAACXCAIAAAAK431+AAAQLUlEQVR4nO3cXZazthKFYeY/Kg/tXGR9PrRUtbVLCAz2+1xkGSFKpR8kdyedbQMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHf2er1er9ens/i/T+Wzqt27jSf+w/wCuKO7bSifyidsdyKZu40n/sP8Agi8Ile2fqtQtzqARXk1zoOc1IV7rvMvmN8bpgR+WfIY73nqP1zWdFM4sSstWXB3O33DW6Kn0/mf8bp+cB6z4NmHs5Xmdy7OR1z/PUa4TyZ3wGg8z30mbC6T++S/UPYd5YwmzjiDL3vqPvFLLkjmW8cz/G76gTyucrfercrnKb8EOr3d+0zwPTfuTzl7ozlv3O45j3dbJ0/v7302xLvN7Fp36x0H8LkNNL9fev31LumDHP/F1D5+ltXw9dN59vWnU80GKuvCRBNhyZJxzsZzSb+umceqLJM+MdGuyP9gPllic5H9+fWj9eW6aR2kH+phSlm/RJDj/Q3TFvnrfvX5mJWd/MUIiH5lHewvq/lkj4g4ulDfCiuH3RHjkFU2+zujD913ssm1qZPVn8gkDKvjh/lnzy4hEljYtO7XSfGzy1K/7jmP91nn4vFPza8TxJmv8FIHqU6x/16s6u/EexGWO+ttLm1dX4zPxDr386nG0Yt/Yr01H64Z/7KwgdLErMp4Lr4/MavoF/Kaho63VRq3Ulv3nMf7rPNhQ0scH09dYSJ+s9M5ORxpd1V/J94Lvdiay7Wv+fHxWZVPdf3sbw17IRLLPlwz/mVLNqbGkkzM+P7ErDLxQi5v6HhbpXErtXXPebzPOh82tMTx8dQVwvjZ+Lwvj3fZ71d1vrIKE+9FttjCfPSYVIeoND6l8ol8xOCLIQrv+ust+3DN+JeJNWRmtirFufj+gltFxD+1rbX9Ko1b9a0LLz87j/dZ51lba+MfH8+D8xWWH59Tv1/V4E7+mzdlYflEfPFUZnp8huVz+fhx9MBOrLfmwzXjX1bamMJJOnuGqgOU5bnKxAu5pK21K8N/UattnT2P/cqcyEpEO3udiyAL11K2DZXil7o/nPdwqIc5ZK0M+7Wqv2LtiUU1LKy+F+ZYleZluM7FlA3zqfZLrBNd3wno1PfDTqzb2Ouf8FZYko2OCDWRSXbZL46wPMsz7NeRbMMMl01PN+vZZTVmlurxfuk8s/nKyrdoHic6LvLvC7N2s/yPEE0sCT6cXzOIM18i/ruk+TDX2f0jTYTs8kh/m8JXpK8ZNtrf0pd9f53MzfgvY52HFY7ko+OIRkV90cF9T4f1+8uwXbP7R4X9P73VFXSeT+nF2+MSXuWajn98nf/s/OKzWHg3Jb593H/OhnnevwuNxyW8xAXr7Sbr/DfnF5/1lP0cAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIDf9Hq9Dtbp72YlTlsTCWTVppszmUN3XhpZZNHikWQ+3t8jrszKn5d7jhWAi/gH8JFt5V1y6hl86gEcRssK3/qS/u5062G5+W1pmM/Z/b347BkOi8mJvGSyJjKZCOUHAfBH+DpVXzPnJXzJ4/M1u3HrFk06k7V7zUQvxLN+NH8Gj8/1qvpOf5dMynQ+c3Gc4FlbpfU8XNsH868GAe7i+q3E8c6h9K46mesNKGuu3038s9AcT9HTIxuWbs7fPff/1OkNGw3Lza282mLzyKn9XTIvmTCBcHD8OFnhsLN+4TD+MJpTWbcO3FG2u20fWs3ZFpPV2aZ+tdVsXuY++64mIvudCpMJe/QuEUFK/F2yudX3uhm6I60PZzm8dJq+sr8Hp0Zr1kC/dPv64XIN64sBd+qLwmH8YTSnsm4duK/7LOjwtZ/bmsOA2ZYUNudUdjoyfEqXN4ktmZS5Ga/uuaXKExu03/qV/T3vrXkvgGZBOr0b1ven5p3D/nOof8Ss3LTrh8q6ANxX6d0726s7ZvTe0b+c4tIpHDaxdVuh/0gmrF8N4sviZCMTbnPvz/7ep1eaJqINn7qyv6vmKMuqb8Xp3bB+qUdOJs1T1cvh7APnyjaUcF/oL/uFu4/QlOs0FnTG1meo947Siy0ChhtKX9ncFI4PWt9KOHcHmxiWhHeb9dYX+i1mt4bzmDXnN6Erb7P9Xf6+hK+tfil0nVLa2aCF74toZWJ++w++sC9ATbgKt3z5imUtNo7henUqhC/A3IsxfOdFZadr2bCIu06ELf/OJO6Gw7Lvb993fyQ1MU19E1mjpUxE5dI8+ushvHVqf5dMzTBy9nm6vhMkLM8Gc2JCs/h+VsBK/SoUHzb7LRqufv/uGbK30XkVna7poRM7i44wbNev0PQ360WTUmmmqvtaMwjDDddvVAScGPzSjJzR39IslPSD0KyTYf3+sy6sli+Z3yaUuAWcq1+F4sP7UuxZzqV56yTZniI2gtKLvX8qjD/cqrIITrZm5P2H9z+HG5Y5WWJkxHBtydSUVohf2alpduHi/pYGpCRcmcMl2ndfD4W//qvJlC7Fau/viikG5vUru1n3es1lL4a5p3xkQYe7XlgSlk90rWnR7LXep4ZBnFZEE8enRu/CYXnpkVK7c9UmulCqPNFffzQmDgy95PrCrL54NozgtyV6VH1Pq+9jqSZgqR7Azhs4fEoUnk1vGdObclgne8PDvaAZNB3BUe2LmeTBBHRhs4RCE+2KaDq+34VS5SP99aegOl9OcDNg1msxvE5Pdet67kS00pCaNYGx/Uu+/7CvkD2S7Q5NkKz+p5ZymEMpsVKFfki3aHCqERzOI6LjYnKPJFDaQ51H/Dir6mQPXtDf6lAsr28GdPqll3126Q++35w/Sp/atfCjphfcPVequd9NbJrvW/rc6jeRfpuYO/leHtGXYeWqd2f9lMwJ8ls/UqGv009fePek/lbHYXrc/PyzRqcH/5W8Gq9uoQ4DvkvMlITjEYAa52XTzz5ivU5sdhP9yraSuQgLicQ+Mn2lA+mMtoZ11iZzZX8vM336btGCzHaS/SZzpEXT0ycFAAAAAAAAAAAAAAAAAADMOfu/JgUAAH+IP0niDAYA4FwTf24LAACOWvv/tQAA4Nleka37vXF22f9uOft/0+hTljMYAPBz9sfq/kN2goqTtTmhs2o6DQAAfkJ2AIcfNvvf5vITMAAASvUA3v7+CrqP41yatwAA+FrDn3fNH17nDmBOXwDAj6oewAv/HTCnLwDgR+1/k7z/sK+QPRL+cNwHyepz+gIAkJo+JjlfAQCYMfy3v8NnOYMBAAAAAAAAAL/p4J8mD+uE/4XadHNHMgEA4EZOOoDFn3U5B/ArMZcMAAD30v/BVX93i/6Gyjkd98Gdv7wa1mkKs0O6dGYDAH5Cdkp9SvhTaVjY33Uqh+XZATlxAOuGAADY+vNmf+vqbKJ2+6NU/FCrf+LUt8IW/QjD/AEAaGWHxPWHh/gqMDyAwxJxWmfVxAkqqu3Lh8IHAQA/RxwJV54W4U+ZfSb6gDR/PG0+rD2Ah3UAALeWHSfN0ZJdhsdPf970l30aCzpjGx542YmbBTEjh/Gbu+GMNK33cTiGAeB5st08O2/EOdSc0Fk1nYaoEB5I4qBy2hoewM1dfTTq7oQDu/0driw3HYcDGACeR/w4NXEqmJf62bMdOYCzy3ehOJ7DgzOsI5rYx8mETwEA7qV6AG/Jb2WnD+DrD4z9QTg8gMUg6Pj+U+EhLc7UPgiHLgA8jz5uhz9RmeeT/mHuYsOcwy8cW3QuLvkiEh7ApSAcwADwPNUDWFzuI1QPnisNDzDnhBNfKfQBr0MND+DhV4H+OwEA4Hb2m/X+w75C9ki4y/dBsvofPCGqB3AzPlmQ5pGsFdF6/6G/DNvluAWAbzO9s9/8SMh+Xhx+BekfDL9b6DhZc9m3luxgzpIEADxY+DNc6dmHngrDHzf1s+EPr32c8NDN2h2m8dChBgAAAAAAAAAAAAAAAAAAAO5M/GfAAJ4o/Bs8ADciXtGvf2+X700P3ey+fhzuls8Zmj5W/1IOwMc85f8gsTwfM6Df7t1GzPT14/DZfFa1PnxPwz9bX5gAgPXE+3mrV5cD+CRfPw4/cgBnJXebDuABXpF9eVMtfKoPuBXfz9K2myXW56OTzAr1rVKSWT7VdpvCbTfOw/pOPsN+vSJ9i9nl14yDaCIcEFH5XaLzGeaZVRZph5dOo32/mg/9LQBK/xa99wVdrb9sXuasmk7DrCP2EZ1nFsQsqXZkSbvDzpqfs3zCBLJ8Dq6Tp49DFrZZ805/p9ftXNpH4ohyv2kALbEFmJtmWF7aIKq7nnNrmEC4dwxL/IPqYLtZ/Wr8ufkS9efWyfeNQzVO319n3ZrxnTfoSBydqt86gD+qG+v2byspbYh6Yy3lad56dZpb4YPZvhnGcZKZbjerPxFfDMJEv5wPc3mGJVn9j4+DLjfzyUJV8xym7eSpUyrFAWAZbqP61QqfGl4Oy0s1s117WHm4reh2nWSyUS21K5414+v6juo6+fpx0OVOPk7TZp4cwMAjrdpYmwjO+3lk7xvus8MNsU8yqybCDpM83m5TP4yp6+uGwsvekXXyTeMgmtPx9RAdzDOrpuOLlIapTowbgD9e/2y7t1FsW/tHsg20CZLVn3tjmzhOMuEu03Q5vDuM4yTZZ1tttw8yjGAOkd+vsEev0Wx+3ziEXTMv+w/DZEp5+vnvg2StHMzHH0kAqekXiTfwpzDd2GM9AIf4X6izZ3kJfwETjR5LAgAAAAAAAAAAAPhyX/MvBb+mIwCA7/dlh9aXdQfAJ93tP1ru8+n/cvF4tvfp73c7+Eeruv6n1i2LB8ACzTn3sTyiHJzPc03c6guHdrezp9q0+L80mEGy+tU4Cz1l8QC4L7E5foSzWa/K8BF76Nlnz/Tj1bMzKxnGMTvOGQzgYR53AC9M7xEbaJbkx7+FcAA/Yv0AuK9sf2x+xxhe7gv3l9nn/tLPx3m2ygkl+rLl4xB+MFsJg2eNikeGTUzEEY+IrukSZ3DCy2qczRvS7JFhYgBQI/bHiY1vv1XNbZTDfBbuen4ov1/NaDgNhY8MkywdbKIJP87EgVfN04kwnc/cehbxOYABHLL8ANatVHfJ8NlVG1/pAB4WZnnOHfO66bDcnK9VcZ5+AFfj+wEBwDJ3ADdENB1zLp9HHMDvkXFaEZVLB3A4LxMHsBPHHL1hj6pL4kg+2aKdGDezRQBINafFZmxwYt/Rt8wtMitpNs1hqIm2SjX1+Oz/6QcfHldLai6JMz16HzyAnThmQE5fAAvsd6LsMBA/GZjnonkAi4BObiUnHcCbsbmHD4YdmT6AnaELU3UOvNKYi1TNmKXczDSq6/ngeQ8Asdc/orw5g5tHXn+FTRzJp89EbI5+/OFBomtmt7LNXbey5YMcDkXY7jBV0brTrzBVp4NmoyKCmbyOsGTc/I4DgKu0q1YjnxH2guBY4mvm6Gs6AuD7nXeov+OfFxwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADzL/wDucPq7002l3wAAAABJRU5ErkJggg==" alt="" />

  其中:数据库名字不能用关键字(已经被使用的字符)或保留字。

  如果非要使用关键字或则保留字,那么必须使用反引号。(esc 下面的英文状态下输入`)。

当创建数据库的SQL语句执行之后,发生了什么?

  1、在数据库系统中,增加了对应的数据库信息

  2、会在保存数据的文件夹下:Data目录,创建一个对应数据库名字的文件夹。

  查看数据库

  1、查看所有数据库 

 show databases;

  2、查看指定部分的数据库:模糊查询

 show database like 'pattern';   

  --pattern是指匹配模式:%(百分号):表示匹配多个字符。_(下划线):表示匹配单个字符。

  3、查看数据库的创建语句

 show create database 数据库名字;

  更新数据库

  数据库名字是不可以修改的。

  数据库的修改仅限库选项:字符集和校对集(小堆积依赖字符集)。

 Alter database 数据库名字[库选项] chaerset XXX;

  删除数据库

 Dorp database 数据库名 ;

 上一节:MYSQL基础笔记(一)

下一节:MYSQL基础笔记(三)-表操作基础