My SQL

My SQL知识超全梳理！本图包括SQL语句的3大类、MySQL登录的命令和一些参数、语法、约束、修改、查询、对数据进行操作等内容。

SQL

• SQL语句的3大类
  • DDL:create、alter、drop
  • 数据操作语言(DML)：
• MySQL登录的命令和一些参数
  • 1.命令行启动：win+R—>cmd,打开cmd后 (1)输入mysql -u root -p，然后输入该用户的密码即可登录，我的密码是123456，回车后即可登录MySQL 2.mysql -u root -h +IP地址，如果不知道本机的IP地址，可以用localhost即可。这条命令一般是用于连接其它主机，对其它主机进行操作的时候才会用到-h参数，链接本机的时候不许用用到
• 语法
  • 1.语句的整体要加分号。如show databases; 2.固定结构，MySQL不区分大小写。表名，数据库名当然是分大小写的 3.所有自定义名称不能和系统语句结构重名。如果重名了，在名字外面加上` `(就是左上角的波浪键)。注意不能弄成单引号、双引号
  • 2.创建数据库:create database shop，创建shop数据库 3.使用、对某个数据库进行操作时，首先要进入到要操作的数据库。使用use关键字。如use shop进入到shop数据库。执行成功会返回database changed 5.查看某个表有几列：desc+要查看的表的名称。执行效果如右图。注意SQL server的desc不是显示表信息的，而是用来降序排序的，这是二者的差别
  • 命令行创建表
    • 1.use 要使用的数据库 2.create table commoditytype( ct_id int primary key, ct_name varchar(50) not null这是括号前最后1条语句，括号前最后一条语句不能加逗号 ); 注意事项：1.除括号前的最后一条语句外，每一条语句以逗号分开。但最后一条语句后面不能有逗号，否则会报错 2.)后面要有分号，以结束这一整条语句
    • 语法： create table `表名`( `字段名` 列类型 [属性] [约束] [注释], `字段名` 列类型 [属性] [约束] [注释], `字段名` 列类型 [属性] [约束] [注释], .............................................................. `字段名` 列类型 [属性] [约束] [注释]注意括号前的最后一条语句不能有逗号 ); 注意：` `可以省略。但当名称和关键字重名时，必须加``。如我要起个表，表名叫order。但order是MySQL的关键字，此时就得写成create table `order`(语句段...);
  • 删除数据库
    • 命令：drop database 数据库名(写成`数据库名`也可以) 如果显示：Query OK, 0 rows affected也没有问题，因为如果数据库是空的，那么将其删除的时候就会显示0行被影响；如果数据库中有数据，那么就不是0行受影响了
  • 退出数据库/切换到其他数据库
    • (1)exit/quit;:退出所有数据库、并且断开MySQL连接。如果还要继续使用MySQL需要重新mysql -u root -p的进入 (2)退出当前数据库，想查看其他数据库。即：不断开连接、不退出MySQL：直接use +想进入的数据库;即可。别忘了MySQL要以;结尾
  • 主键
    • 1.每张表必须要有主键 2.主键的特点是值不能重复 3.如果某个字段设置成了primary key，那么可以不写not null。因为主键默认就是not null的。如：stu_id int primary key即可，不需要再写primary key了
    • 设置主键
      • create table test2( price int not null, "sum" int default 10 ) alter table test2 add primary key(price) 注意：如果设置主键要写在创建表的外面，那么要设置主键的列必须设置为not null，否则会报错
      • create table test2( price int primary key, "sum" int default 10 ) 如果在创建表的时候设置主键，那么就不需要写not null了。因为主键一定是not null的
    • 删除主键
      • MySQL：alter table 表名 drop primary key 因为每个表只能有1个primary key,所以删除primary key的时候不需要写primary key列的名称。但是当且仅当MySQL中可以这样写！SQL server不能这样写！
      • SQL server 1.如果设置主键的时候没有起别名(即使用alter table 表名 add primar key(要添加主键的列名)创建的)，那么首先需要找到数据库给该列设置的默认名称。即如果写的是alter table 表名 add primary key("pk_name")，删除主键的话不能用alter table 表名 drop primary key(pk_name)!!(很变态的设定) (1)找到数据库给主键列设置的默认名称：sp_helpconstraint 表名；结果如右图的红笔所示，即数据库默认创建的外键名称是PK__test2__BC02222C73D547F6 (2)找到默认名称后，使用alter table 表名 drop constraint PK__test2__BC02222C7D8C3A19 2.如果设置主键的时候起了别名(即alter table 表名 add constraint 自己起的名 primary key(要添加主键的列名))
  • 默认值
    • alter table 表 modify 要添加默认值的列 int(这个地方写其他类型也可，如varchar) default '默认值'
    • 删除默认值
      • 1.MySQL：alter table 表名 alter 带有默认值的列名 drop default; 这条语句是删除某列的默认值，而不是删除带有默认值的列 2.SQL server：
• 约束
  • 主键
    • 建立外键
      • 表已经建立好了，不能删除表的情况下加主键
        • create table customer( cu_id int comment "编号", cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null， ) alter table customer add primary key(cu_id)
      • 表还没有创建
        • 法I：在要设置主键的行上直接写primary key create table customer( cu_id int primary key comment "编号", cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null, )
        • 法II：在括号前的最后一行写primary key(`要设为主键的名称`)。其中``可以不写 create table customer( cu_id int comment "编号", cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null， primary key(cu_id) )
    • 删除外键
      • MySQL：drop table 表名 drop primary key。不需要写primary key列的名称 因为primary key每个表只有1个，所以不需要写明带有主键的列的名称
  • 外键约束
    • 子表的取值收父表约束。外键是设置在子表上的。外键的列必须是另一个表(即父表)的主键 如籍贯这一列是foreign key，使用1~25代表25个省，那么父表的primary key就是省份编号1~25
    • 外键的创建
      • 在建表的时候添加外键
        • create table student( stu_no int primary key, stu_pwd varchar(20) not null default '123456', stu_name varchar(50) not null, stu_sex char(5) not null, stu_grand_id int not null, stu_phone varchar(255), stu_address varchar(255) default '学生宿舍', stu_borndate date, stu_email varchar(50), stu_p int not null,--籍贯编号 constraint fk_id foreign key(stu_p) references province(p_id) )
      • 表已经建好了，不能删除表的情况添加外键——使用alter
        • alter table 要添加外键的表名(即子表名称) add constraint 给外键起个名字(名字随便起) foreign key(子表要添加外键的字段) references 父表(主键所在的行的名称)
          • 子主题
        • create table commoditytype( ct_id int primary key, ct_name varchar(50) not null, ) create table commodity( c_id int primary key, c_name varchar(50) not null, c_madein varchar(50) not null, c_type int,--把它设置成外键 c_inprice int not null, c_outprice int not null, c_sum int default 100 ); alter table commodity add constraint fk_c foreign key(c_type) references commoditytype(ct_id)
    • 外键的删除
      • 子主题
    • 注意事项
      • 如果表A是子表，表B是父表，子表上的外键和父表的主键之间建立了联系，那么在drop table 表A的时候会报错"无法删除对象 ‘*‘，因为该对象正由一个 FOREIGN KEY 约束引用"。此时有3种方法进行处理 若知道外键的名称(例如 alter table student add constraint fk_name foreign key reeferences 表A("stu_info"),则fk_name就是外键的名称)，则可以通过alter table 被引用的表名 drop constraint fk_name，先删除外键和主键之间的约束，然后再drop table 被引用的表就可以将其drop掉。 若不知道外键的名称，此时可以通过以下语句查看外键的名称：select fk.name,fk.object_id,OBJECT_NAME(fk.parent_object_id) as referenceTableName from sys.foreign_keys as fk join sys.objects as o on fk.referenced_object_id=o.object_id where o.name='被引用的表名' 这条语句中，只有"被引用的表名"是根据实际表进行替换，其他的语句不需要更改。这样可以查出来外键的名称(假如查询出的外键名称叫"fk_name")那么下面用alter taable xx drop constraint fk_name先删除主外键之间的关系，然后再drop table xxx即可删除 第三种方法就是先删除子表(就是有外键的那张表)，然后再删除主表(就是有主键的那张表)
  • 自增长
    • create table customer_test( cu_id int auto_increment, cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null, primary key(cu_id) ) 注意：只有MySQL中自增长是auto_increment，SQL server的自增长不是auto_increment
  • 唯一索引
    • 作用：避免值重复。它和主键的区别是主键每个表有且仅有1个，而唯一索引可以有多个。关键字是unique
    • 创建语句
      • create table customer_test( cu_id int , cu_name varchar(50) not null, cu_phone varchar(50) unique not null, cu_gender int default 1, cu_address varchar(100) not null, primary key(cu_id) ) alter table customer add unique(cu_phone) 或alert table costomer add constraint un_name(随便起个名字) unique(cu_phone)
      • create table customer_test( cu_id int , cu_name varchar(50) not null, cu_phone varchar(50) unique not null, cu_gender int default 1, cu_address varchar(100) not null, primary key(cu_id) )
    • 删除唯一索引
      • MySQL 1.如果是起了别名(alter table add constraint 起个名字 unique(某个字段)) alter table 表名 drop index 起的名字 2.如果没有起名字(alter table add unique)
  • 非空约束
    • 创建表的加
      • create table customer( cu_id int primary key ,这里就是注释 cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null )；
    • 建完表之后加
      • create table customer( cu_id int primary key comment "编号",这里就是注释 cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null )； alter table customer modify cu_name int not null
• 修改
  • 修改表名 (1)MySQL：alter table 原名 rename as 新名字 (2)SQL server：
  • 添加字段：alter table 表名 add 列名 列类型 [属性](MySQL/SQL server语法相同)
  • 修改字段属性 1.MySQLalter table 表名 modify 列名 列类型 [属性](MySQL/SQL server语法相同)
  • 修改字段名称 1.MySQL：alter table 表名 change 原名称 新名称 字段类型 注意：SQL server中没有change这个关键词！ SQL server：
  • 删除字段 1.MySQL：alter table 表名 drop 要删除的列名
• 查询
  • 查看约束
    • MySQL：show keys from 表名/show index from 表名
• 对数据进行操作
  • 增加数据 1.一次性添加多条数据：insert into 表名(列1,列2...列n) values （列1对应的值，列2对应的值...列n对应的值),(列1对应的值，列2对应的值...列n对应的值),......,(列1对应的值，列2对应的值...列n对应的值)
  • 查询数据
    • 1.查询所有的数据:select * from 表名 2. 查询满足某些条件的内容 (1)单表查询:select field1 [as name1],......,field n [as name n] from table_name [where condition] field是想查询的列的名称，比如我想看name,score两列，那么就是select name，score from student where stu_id=1。 如果不需要条件，要看所有的列的话，可以把where condition去掉 as name可以将查询出的列结果起别名，as 后面的名称可以起中文，且不需要加""等符号。如果不需要起别名可以去掉 （2）distinct:如果表中有重复的数据，可以用distinct去掉重复的数据 语法：select distinct field1,...,field n from table_name ，就会将重复的内容去掉 （3）in:select distinct 姓名,目前薪资 from 员工 where 部门 in ("财务部","信息部") 3.like：模糊匹配查询student中姓名为薛开头的所有名字 （1）select * from 表 like "%薛":匹配0个或多个任意字符，"薛"之前由0个或任意多个字符都可以。如果要匹配薛开头的，那%就得放在后面。即select * from 表 like "薛%" （2）select * from 表 like "_某个字符":匹配单个字符
  • 删除数据
    • 1.删除某张表中的某条数据:delete from table_name where 条件 (1)selete from table_name condition1>某个值(小于号、等于号也可以) and condition2<某个值(小于号、等于号也可以) 2.删除某张表及其所有数据：delete from table_name 3.truncate：用于完全清除表数据，但是表结构、索引、约束等不变 truncate table table_name(在MySQL中，truncate语句可以不写table，SQL server必须要写table) 4.truncate和delete区别 （1）相同：都能删除是数据，不删除表结构，但truncate熟读更快 （2）不同 truncate会清楚自增(auto_increment/identity(x,y))计数器 truncate table 不会对事务有影响
  • 修改数据
    • 1.语法：update table_name set field1=value1,field2=value2,...fieldn=valuen where condition 例：要将sub_name为senior math的改成English update student set sub_name='English' where sub_name='senior math' update 员工 set 性别="男" where 性别 in("-1","false")
• MySQL与SQL Server的不同
  • 注释 1.MySQL的注释：在每一行的末尾用关键字comment，然后在comment后面写 "注释内容"。即comment "注释内容" 例子：create table customer( cu_id int primary key comment "编号",这里就是注释 cu_name varchar(50) not null, cu_phone varchar(50) not null, cu_gender int default 1, cu_address varchar(100) not null )； 2.SQL Server的注释 多行注释：/* */ 单行注释：--
  • 外键 1.MySQL的外键：constraint ` `
  • create table student( stu_no int primary key, stu_pwd varchar(20) not null default '123456', stu_name varchar(50) not null, stu_sex char(5) not null, stu_grand_id int not null, stu_phone varchar(255), stu_address varchar(255) default '学生宿舍', stu_borndate date, stu_email varchar(50), stu_p int not null,--籍贯编号 constraint fk_id foreign key(stu_p) references province(p_id) )
  • 查看表结构
    • 在MySQL中查询表结构是desc +要查询的表的名称 在SQL server 中查询表结构是sp_help+要查询的表的名称huo