基础SQL语句
基本sql语句(增,删,改,查)库操作:
mysql> show databases; #列出所有的库
mysql> create database 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> selectfrom t1 order by age;#按照指定字段升序排序显示
mysql> select from t1 order by age desc;#desc逆序
mysql> selectfrom 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> selectfrom 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; #右连接,显示右表所有内容
页:
[1]