mysql学习(九)sql语句

时间:2022-06-18 21:27:02

SQL种类:

DDL:数据定义语言

DML:数据操作语言

DQL:数据查询语言

DCL:数据控制语言

DDL:

  1. show databases; //查询数据库
  2. create database if not exists sqldb;//创建一个数据库
  3. use sqldb; //使用一个sqldb
  4. show tables; //显示表
  5. create table if not exists cats(id int not null auto_increment, pid int not null default '0', name varchar(16) not null default '', desn text not null default '',  primay key(id), key name(name, pid)); //创建数据表
  6. create table if not null exists products(id int not null auto_increment, cid int not null default '0', name varchar(16) not null default '', num int not null default '0', price double(10, 2) not null default '0.00', desn text not null default '', primary key(id), key name(name, price));//创建表

DML:数据操作语言 insert update delete

  1. desc products; //查看表结构
  2. insert into products(cid, name, price, num, desn) values('0', 'dda', '10.20', '2', 'this is a java');// 插入记录
  3. insert into products values(null, '0', 'java', '10', '10.10', 'this is a java!');
  4. update procudts set num = 1 where id = 1;//更新语句
  5. update products set name = 'php', desn = 'this is php' where id = 2; //更新语句
  6. delete from products where id = 1;//删除id = 1的记录

DQL:数据查询语句

select [all | distinct]

[字段名 as 别名]

from

表名

[where  条件语句]

[group by ....]

[having....]

[order by...]

[limit count]

  1. where 语句 select update delete

逻辑运算(多个条件)

&&   ||  !

and  or  not

比较运算

<=>

>

<

=

!= / <>

>=

<=

IS NULL

NOT NULL

BETWEEN AND

NOT BETWEEN AND

IN

LIKE 模糊查询  _:一个任意字符 %:0个或者多个字符

NOT LIKE:

REGEXP 正则表达式