基础SQL语句

xyzjr

　　基本sql语句(增,删,改,查)
　　库操作:
　　mysql> show databases;               #列出所有的库
　　mysql> create database  [if not exists] db1;       #创建数据库
　　mysql> use db1;                   #切换到数据库db1
　　mysql> select database();    #查看当前所在的库
　　mysql> show create database db1;  #查看库创建属性
　　mysql> drop database db1;              #删除库
　　表操作:
　　mysql> show tables;  #列出所有的表
　　mysql> create table t1(name char(50));  #创建表,至少要有一个字段
　　mysql> desc t1;   #查看表结构
　　mysql> show create table t1;  #查看创建表属性

　　mysql>>
　　mysql>>
　　mysql>>
　　mysql>>
　　mysql>>
　　mysql>>
　　mysql>>　　mysql> drop table t2;    #删除表
　　DML语句:
　　mysql> insert into t1 set name='tom';    #添加一条记录
　　mysql> insert into t1 set name='jim',sex='male',age=18; #添加一条记录
　　mysql> insert into t1(name,age) values('lily',17);
　　mysql> insert into t1(name,age) values('lala',17),('lele',16); #添加多条记录
　　mysql> insert into t1 values('lala','feamle',17);  #添加有所有值的记录
　　mysql> update t1 set sex='female' where age=16;  #修改记录
　　mysql> update t1 set sex='male' where name='jim' && age=16;
　　#多条件定位  and(&&)   or(||)
　　mysql> update t1 set age=null where name='jim';
　　mysql> delete from t1 where age=18;  #删除记录
　　mysql> delete from t1 where name='';  #删除空值
　　mysql> delete from t1 where age is null;  #删除null      
　　mysql> delete from t1 where age > 16;
　　mysql> delete from t1 where sex != 'female';
　　mysql> delete from t1;  #清空记录,如果有自增约束,再添加记录会从之前的值自增
　　mysql> truncate t1;       #清空表,从头开始  注: 此语句属DDL语句
　　DQL语句:
　　mysql> select from t1;              #查看t1表所有的内容
　　mysql> select name from t1;
　　mysql> select name,sex from t1;
　　mysql> select  from t1 order by age;  #按照指定字段升序排序显示
　　mysql> select from t1 order by age desc;  #desc逆序
　　mysql> select  from t1 order by age desc limit 2;  #只显示前2条
　　mysql> select name,age from t1 where age=(select max(age) from t1); #子
　　mysql> select sum(age) from t1;   #求和
　　mysql> select sum(age) as total from t1;   #as:别名
　　mysql> select name,chinese+math+english as total from score order by total desc;   #按照总成绩从高到低排列
　　mysql> select count() from t1; #统计记录数
　　mysql> select sex,count() from t1 group by sex; #分组统计
　　mysql> select from t1 where name like '%i%';  #模糊  %:所有 _:任意单个字符
　　mysql> select  from t1 where name regexp 'm$'; #正则匹配
　　mysql> select t1.name,t1.sex,score.chinese,score.math from t1 join score on t1.name=score.name and chinese > 80;  #多表连接查询
　　mysql> select a.name,a.sex,b.chinese,b.math from t1 as a left join score as b on a.name=b.name and math=100;   #左连接,显示左表所有内容
　　mysql> select a.name,a.sex,b.chinese,b.math from t1 as a right join score as b on a.name=b.name and math=100;   #右连接,显示右表所有内容