MySQL中sql语句

1.登录mysql：

mysql -u root -p

2.查看mysql中有多少数据库

show databases;

3.查看当前数据库名称

select database();

4.切换数据库

use databasename

5.查看数据库中有多少表格

show tables;

6.查看表结构

desc tablename;

7.创建数据库

create database databasename;

8.创建表格

create table tablename(
列名(字段) 数据类型，
列名(字段) 数据类型,
……
);

9.向表格增加数据

insert into tablename(列名1，列名2) values(值1,值2..),(值1，值2)(值1，值2);

10.向表格增加列

alter table tablename add column columnname 数据类型;

11.删除表格数据,不改变表结构（删除行？）

delete from tablename where (过滤条件)

12.删除表格的全部数据，不改变表结构

truncate tablename

13.删除表格

drop table tablename

14.删除列结构

alter tablename drop column columnname;

15.修改表名

rename table oldtablename to newtablename;

16.移动表格到其他数据库

rename table olddb.tablename to newdb.tablename;

17.修改列属性(新名同旧名，也能达到修改列名的目的)

alter table tablename change columnname_old columnname_new type;

18.修改列属性

alter table tablename modify columnname newtype

19.查询表格所有数据

select * from tablename

20.查询表格某项数据

select columnname from tablename where (过滤条件)

21.模糊查询

select * from tablename where columnname like “d%”;查询以d开头的数据，%表示多个字符

select * from tablename where columnname like “d_”;查看以d开的两个字符的数据，_表示一个字符

select * from kr where pname like ‘__‘;查询表中4隔字符的数据

22.空值查询

select * from tablename where columnname is null;查询值为空的数据

select * from tablename where columnname is not null;查询值不为空的数据

23.范围查询

select * from tablename where columnname in(‘gaga’,’baga’);#in与括号间没有空格

24.比较运算符
select pname from kr where price>260;大于
select * from kr where pname <>’heyga’;不等于
select * from kr where pname !=’heyga’;不等于

25.逻辑运算符:and ,or, between….and

select * from kr where price <=250 and pname=’simida’;
select * from kr where pname=’simida’ or pname=’heygor’;

select * from kr where price >=250 and price <=290;
select * from kr where price between 250 and 290;

26.多表查询

26.1内关联

select * from tablename1,tablename2 where tablename1.column=tablename2.column;

26.2左关联

select * from tableleft left join tableright where tableleft.column=tableright.column;

26.3右关联

select 列 from 左表 right join 右表 on 左表.列=右表.列;

27.子查询

select city from city where country_id=(select country_id from country where country=’China’);

28.分组查询，where不可接count(),max().等条件，having可以

select 列 from 表 [where 条件] group by 分组条件 [having 分组后过滤条件];

select count(first_name) from customer,country,address,city where customer.address_id=address.address_id and address.city_id=city.city_id and city.country_id=country.country_id group by country.country having country=’china’;

28.分组函数(聚合)
max()
min()
avg()
sum()
count()
分组函数通常和分组一起使用，也可以单独使用
例子：查询payment表中最高金额，最低金额，平均金额
select max(amount),min(amount),avg(amount) from payment;

29.查询表格第一行

select * from tablename limit 1;

30.查询表格第2,3行;limit n-1,m-n+1

select * from exam limit 1,2;

31.查看表格最后一行

select * from exam order by linux desc limit 1;

32.查询后几行
select * from exam order by linux desc limit n；//倒序排序，取前n行 id为自增形式
查询一条记录($id)的下一条记录 select * from table1 where id>20 order by id asc dlimit 1 ; //正序排序 查询一条记录($id)的上一条记录
select * from table1 where id