#创建指定字符集的数据库
#create database if not exists test_db character set = utf8;
#查看数据库
#show databases;
#使用数据库
#use hogwarts_stu;
#修改数据库的字符集
#alter database hogwarts_stu character set = GBK;
#删除数据库
#drop database if exists test_db;
#--------------------------------------------------------------------------------------------------------------------
#创建数据表
#create table user_wm (id int not null default 0 primary key, name varchar(20) not null default “姓名”, age int not null default 0, sex varchar(2) not null default “性别”, address varchar(20) null default “地址”);
#复制数据表
#create table user_wm1 like user_wm;
#查询所有数据库表
#show tables;
#查看数据表中所有列的数据属性
#describe user_wm;
#查看数据表中某列的数据属性
#describe user_wm name;
#添加新列
#alter table user_wm add email varchar(20) null default “邮箱”;
#修改列的数据类型
#alter table user_wm modify email char(20);
#修改列名
#alter table user_wm change email email1 char(20);
#修改列名并指定列的默认值
#alter table user_wm change email1 email char(20) default “邮箱1”;
#删除列
#alter table user_wm drop email;
#修改表名方式1
#alter table user_wm rename as user_wangmin;
#修改表名方式2
#rename table user_wangmin to user_wm;
#删除数据表
#drop table user_wm;
#-----------------------------------------------------------------------------------------------------------------------------
#单表查询
#select * from user_wm;
#字段查询
#select name, age, sex from user_wm;
#为表和字段取别名(别名临时性生效)
#select name as name1 , age as age1 , sex as sex1 from user_wm user_wm2;
#去重(查询结果去掉多个指定字段的重复行)
#select distinct sex, address from user_wm;
#查询结果参与运算
#select id , age+3 from user_wm;
#条件查询-比较大小(>,>=,=,<=,<,<>,!=)
#select name, sex, address from user_wm where age <> 27;
#条件查询-范围限定(between … and …)
#select name, sex, address from user_wm where age between 27 and 28;
#条件查询-子集限定
#select name, sex, address from user_wm where age in (27,28);
#条件查询-为空限定
#select name, sex, age from user_wm where address is null;
#条件查询-不为空限定
#select name, sex, age from user_wm where address is not null;
#条件查询-逻辑运算符-多个条件同时成立(and 或 &&)
#select name, age, sex from user_wm where sex = “男” and address is not null;
#条件查询-逻辑运算符-多个条件任一成立(or 或 ||)
#select name, age, sex from user_wm where sex = “男” or address is not null;
#条件查询-逻辑运算符-有个条件不成立(not)
#select name, age from user_wm where not sex = “男”;
#条件查询-通配符-条件内容为匹配任意多个字符
#select name, sex, address from user_wm where address like “%省%市%”;
#条件查询-通配符-条件内容为匹配唯一一个字符
#select name, sex, address from user_wm where address like ‘浙江省杭州_’;
#条件查询-对查询结果排序-单列排序-升序排序(asc,默认)
#select id, name, sex, address from user_wm where id between 1 and 5 order by id asc;
#条件查询-对查询结果排序-单列排序-降序排序(desc)
#select id, name, sex, address from user_wm where id between 1 and 5 order by id desc;
#条件查询-对查询结果排序-组合排序-降序排序(desc)-处理多个列有重复值下的情形
#select id, name, age, sex, address from user_wm where id between 0 and 1 and age in (26,27) order by id desc, age desc;
#条件查询-聚合函数-统计指定列不为null的记录行数(count()、count(列名)、count(distinct 列名))
#select count() from user_wm where sex = “男”;
#-----------------------------------------------------------------------------------------------------------------------------
#插入表中多条数据并写出完整列名
#insert into user_wm (id, name, age, sex, address) values (0, “wangmin”, 26, “男”, “浙江省杭州市”),(1, “liuyi”, 27, “女”, “湖南省湘潭市”),(2, “test2”, 28, “女”, null);
#插入表中多条数据，不带列名
#insert into user_wm values (2,“test2”, 31, “男”, “浙江省杭州市”), (3,“test3”, 32, “女”, “浙江省杭州市”);
#插入表一行中某几列的值
#insert into user_wm (id, name, age, sex) values (4, “test4”, 30, “女”);
#修改表中指定列对应的所有行数据(不带条件的修改)
#需要关闭sql_safe_updates模式
#set sql_safe_updates = 0;
#update user_wm set age = 25 , sex = “男”;
#一次修改多个列(不带条件的修改)
#update user_wm set name = “test0”, age = “0”, sex = “男”, address = “湖南省湘潭市”;
#修改表中指定列对应的指定列数据(带条件的修改)
#update user_wm set name = “wangmin” where sex = “男”;
#一次修改多个列(带条件的修改)
#update user_wm set name = “test4”, age = “30”, sex = “男”, address = “浙江省杭州市” where id = “0”;
#删除表中指定列对应的行数据(带条件的修改)
#delete from user_wm where id = 5;
#删除表中的全部数据
#truncate table user_wm;