mysql sql语句的基本用法

1.打开mysql

开始编写以下代码

a.创建一个user表：create table  table名(字段1 字符类型,字段2 字符类型,字段3 字符类型)

CREATE TABLE USER(userid INT,username VARCHAR(50),password VARCHAR(50) ,sex INT,flag VARCHAR(50));

b.插入数据：insert int table名(字段1 ,字段2,,字段3) values(值1,,值2,值3)

INSERT INTO `USER`(username,password,sex,flag) VALUES('zs','123456',1,"这是一次试验");
INSERT INTO `USER`(username,password,flag) VALUES('ls','123456',"这是二次试验");
INSERT INTO `USER`(username,password,sex,flag) VALUES('ls','123456',2,"这是二次试验");

c.查询数据：SELECT *from table名 where 字段名=值;

SELECT * FROM USER;
SELECT *from USER WHERE userid=1;
SELECT *from USER WHERE username='ls';

SELECT DISTINCT username FROM USER;

SELECT username,sex from USER;

d.更新数据UPDATE table名 SET 字段=值 where 字段=具体值;

UPDATE USER SET username='wf' where userid=2;
UPDATE USER SET username='er',password='181512' where userid=2;

e.删除数据DELETE FROM table名 WHERE 字段名=值

DELETE FROM USER WHERE flag='这是一次试验' and username='zs';

f.去掉重复的部分：SELECT DISTINCT 字段名 FROM table名;

SELECT DISTINCT username FROM USER;
SELECT DISTINCT password FROM USER;

g.给字段重命名:select 字段名 as 起名,字段名 as 起名 from table名;

select username as yonghu,password as mima from USER where sex=1;

h.以下给出了sql语句的计数，求平均值，不等于（<>和=！）大于，小于的用法，补充：SELECT sum(字段名) from table名 where 字段名=值;
SELECT COUNT(*) FROM USER where sex=1;
SELECT AVG(sex) from USER where password='123456';
SELECT * from USER WHERE username<>'ls';
SELECT * from USER WHERE username!='ls';
SELECT * from USER WHERE password<'181512';
SELECT * from USER WHERE password>'123456';

j.模糊查询：%和_
SELECT username,sex from USER where flag LIKE '%一%';//like如果不配合%和_相当于=
SELECT sex from USER where username LIKE '_s%';//一个_相当于一个字符
SELECT sex from USER where username LIKE '__%';//至少存在两个字符
SELECT sex from USER where username LIKE '%';//无意义，相当于满足所有的条件

SELECT username,sex from USER where flag LIKE '%\%%';//所查询的数据中有%的情况用\转义
SELECT username,sex from USER where flag LIKE '%\\%%';//所查询的数据中有\%的情况用\同时转义

k.降序和升序:
SELECT * from USER order by PASSWORD asc;//默认升序
SELECT * from USER order by PASSWORD desc;//降序
SELECT * from USER order by PASSWORD desc,sex ASC;//在满足第一个条件下的数据中再进行第二次排序
l.not，and，or的用法
SELECT username,sex from USER where sex NOT LIKE 1;
SELECT username,sex from USER where sex=1 and username='ls';
SELECT username,sex from USER where sex=1 OR username='ls';

m.in选择in（）中的字段值，not in 除了in()中的字段值
SELECT username,sex from USER where userid in (2,4,7);
SELECT username,sex from USER where userid not in (2,4,7);

SELECT DISTINCT username FROM USER;