sql day3

from example, database name MYSQL21, table name CHECKS

1. enter mysql from terminal:

/usr/local/mysql/bin/mysql -u root -p

2. create database TEST; drop database TEST;

show databases; use MYSQL21; show tables; use CHECKS;

create table CHECKS(

    -> id int not null auto_increment primary key,

    -> payee char(10) not null,

    -> amount char(10) not null,

    -> remarks char(10) not null);

mysql> insert into CHECKS(payee,amount,remarks)values('MaBell','150','Haveson'),('MaBell','200.32','Cellular');

mysql> select* from CHECKS;

mysql> select payee, remarks, amount, id from CHECKS; （改变了表显示的顺序）

mysql> select amount from CHECKS;

mysql> select DISTINCT amount from CHECKS;（选择不重复的数据）

mysql> select * from CHECKS where payee='MaBell';

mysql> select * from CHECKS where amount>200;

mysql> select id, payee, amount, amount+15 from CHECKS; （会多显示一个amount+15的列）

mysql> select payee name, amount, amount+15 retail from CHECKS; （改变了列名）

mysql> select id, amount, (amount/2)discount from CHECKS;

（加减乘除的运算同理）

mysql> select * from CHECKS where payee like '%a%'; (所有name里面包含a的)

-和%是一样的表达方式，c_表示c开头的所有信息

_L%找到第二个字母为L

字符合并 ||

mysql> select lastname from vocation where year>=5 and leavetaken>20; (and,or连接两个条件)

mysql>select （not为取反）

有一些同时在两个表里的人

sql>select * from football intersect select * from softball

仅仅在一个表里的人

sql>select * from softball minus select * from football

特定查找

sql>select * from friends where state='ca' or state='co' or state='la';(等同于)  sql>select * from friends where state in ('ca','co','la');

sql>select * from price where wholesale>0.25 and wholesale<0.75; (等同于) sql>select * from price where wholesale between 0.25 and 0.75;