sqlite语句

// 查询test表里名字叫zhangsan,年纪大于等于23
@Query("select * from test where name = zhangshan and age >= 23")
// 查询表test里面所有数据
@Query("select * from test")
// 查询test表里名字叫zhansan,年纪大于等于34的人 按照降序排列信息
@Query("select * from test where name = zhangsan and age >= 34 order by id desc")
// 查询test表里名字叫zhansan,年纪大于等于34或者小于60人 按照升序排列信息
@Query("select * from test where name = zhangsan and age >= 34 or age < 60 order by id asc")
// 查询test表名字叫zhangsan 年纪大于扥估34或者年纪小于60 按照升序排序取前4条
@Query("select * from test where name = zhangsan and age >= 34 or age < 60 order by id asc limit 0,4")

// 删除test表里面，，，
@Query("delete from test where name = zhangsan and age >= 34 or age < 60 order by id asc limit 0,4")

表里添加字段
ALTER TABLE Bean ADD COLUMN isDeleted INTEGER NOT NULL DEFAULT 0

登陆数据库
mysql -h服务器地址 -u用户名 -p密码(这样子是不安全的登录形式)
C:\Users\Administrator>mysql -hlocalhost -uroot -pabc

退出数据库
exit
mysql> exit

以一种相对安全的方式进行登陆
mysql -h服务器地址 -u用户名 -p回车+密码
C:\Users\Administrator>mysql -hlocalhost -uroot -p
Enter password: ***

================================================================================
查询所有的数据库
show databases;这里面一定要注意，这里有s

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

创建数据库
create database 数据库的名称 character set 编码

//沒有加编码：mysql> create database sunofbeaches;
//加编码的：mysql> create database my_dabatase character set gbk;

Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sunofbeaches |
| test |
+--------------------+
5 rows in set (0.00 sec)

查看当前数据库的编码
show create database 数据名称
mysql> show create database sunofbeaches;

修改数据库的编码
alter database 数据库名称 character set 编码形式
mysql> alter database my_dabatase character set utf8;
Query OK, 1 row affected (0.00 sec)

删除数据库
drop database 数据库名称
mysql> drop database my_dabatase;
Query OK, 0 rows affected (0.08 sec)

=======================================================================================

修改数据库名称
rename database 老的名称 to 新的数据库名称（5.5用不了）

我们可以这样子去修改数据名：
首先，我们要退出这個Mysql，然后，到这個数据存储的位置，直接找到文件夹修改。
再登陆我们的数据库。

切换数据库
use 数据库名称
mysql> use sunofbeaches;
Database changed

查看当前使用的数据库
select database();
mysql> select database();
+--------------+
| database() |
+--------------+
| sunofbeaches |
+--------------+
1 row in set (0.00 sec)

================================================================================
创建数据表
create table 数据表名称（字段1 数据类型，字段2 数据类型，字段3 数据类型...）;
mysql> create table employee(
-> _id int primary key auto_increment,
-> name varchar(20),
-> age int,
-> salary double,
-> sex varchar(5)
-> );
Query OK, 0 rows affected (0.10 sec)

查看所有数据表
show tables;
mysql> show tables;
+------------------------+
| Tables_in_sunofbeaches |
+------------------------+
| employee |
+------------------------+
1 row in set (0.00 sec)

查看数据表编码
show create table 数据表名
mysql> show create table employee;

修改数据表编码
alter table 数据表名称 character set 编码
mysql> alter table employee character set gb
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

查看数据表结构
desc 数据表名称
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| _id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| salary | double | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

修改数据表结构

1、增加列，添加一個字段
alter table 数据表名称 add 字段名称 字段类型
mysql> alter table employee add address varchar(300);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc employee;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| salary | double | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| address | varchar(300) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

2、修改长度/类型/约束
alter table 数据表的名称 modify 字段名称 新的类型（新的长度）;
mysql> alter table employee modify name varchar(50);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

3、修改列名（修改字段名称）
alter table 数据表名称 change 旧表名 新的表名 类型（长度）;
mysql> alter table employee change name username varchar(50);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

4、删除列（删除字段）
alter table 数据表名称 drop 字段名称

mysql> alter table employee drop address;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

============================================================
修改数据表名称
rename table 旧的数据表名称 to 新数据表名称
mysql> rename table employee to person;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+------------------------+
| Tables_in_sunofbeaches |
+------------------------+
| person |
+------------------------+
1 row in set (0.00 sec)

主键约束
什么是是主键的约束呢？
其实，主键的约束就是为了保证一個列，数据不重复。
一般來說，一個表里头只有一個主键。

primary key
可以在创建的时候，或者创建后修改。

mysql> alter table person add _id int primary key auto_increment;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

唯一约束
唯一约束，其实也是为了保证不重复，与主键不同的是可以控制多個字段不重复
mysql> create table student_info(
-> _id int primary key auto_increment
-> name varchar(50),
-> dna varchar(32) unique,
-> age int not null;
-> );
Query OK, 0 rows affected (0.10 sec)

非空约束
非空约束，就被约束的字段，必须有数据
mysql> alter table student_info modify name varchar(50) not null;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除主键约束/唯一约束/非空约束

1、删除主键
alter table 表名 drop primary key;
出这样的错误，是因为我们这个自动增长的主键
mysql> alter table student_info drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum
n and it must be defined as a key

要先修改掉自动增长
mysql> alter table student_info modify _id int;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

然后才能删除主键

mysql> alter table student_info drop primary key;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| _id | int(11) | NO | | 0 | |
| name | varchar(50) | NO | | NULL | |
| dna | varchar(32) | YES | UNI | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2、删除唯一的约束
alter table 表名 drop index 字段名称

mysql> alter table student_info drop index dna;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| _id | int(11) | NO | | 0 | |
| name | varchar(50) | NO | | NULL | |
| dna | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

3、删除非空约束
直接修改即可
mysql> alter table student_info modify name varchar(50);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| _id | int(11) | NO | | 0 | |
| name | varchar(50) | YES | | NULL | |
| dna | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除表
drop table 表名
mysql> drop table student_info;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+------------------------+
| Tables_in_sunofbeaches |
+------------------------+
| person |
+------------------------+
1 row in set (0.00 sec)
================================================================================
插入完整数据
insert into 表名(字段名称1，字段名称2....) values(值1，值2...);
mysql> insert into person(_id,name,age,salary,address) values(1,"billgates",60,1,"usa");
Query OK, 1 row affected (0.07 sec)
如果我们添加全部字段的数据，那么我们可以省略这個前面的字段名称。也就这样子：
insert into 表名 values(值1，值2...);
mysql> insert into person values(2,"larrypage",45,2,"conf");
Query OK, 1 row affected (0.06 sec)

插入部分数据
直接看例子吧，就是字段和这個值 对应即可
mysql> insert into person(name,age) values("job",56);
Query OK, 1 row affected (0.04 sec)

插入中文，发现报错
mysql> insert into person(name,age) values("阳光沙滩",2);
ERROR 1366 (HY000): Incorrect string value: '\xD1\xF4\xB9\xE2\xC9\xB3...' for column 'name' at row 1

怎么解决呢？其实，这個是编码的问题，我们的控制台是gbk的，而我们的服务器是这個utf-8的。
我们要修改一下这個服务器的编码，因为控制台我们修改不了。
打开这個Mysql的安装目录，找到配置文件，my.ini。修改这個编码，但是要注意的是，不能改錯。
修改之前一定要記得备份。
OK?
[mysql]
default-character-set=utf8
不要找錯，是这個[mysql]不是[mysqld]，不要搞錯哦!
然后修改成GBK
[mysql]、
default-character-set=GBK

保存起來，然后重启服务。重新进入我的们的mysql
mysql> insert into person(name,age) values("阳光沙滩",2);
Query OK, 1 row affected (0.09 sec)

mysql> select * from person;
+-----+-----------+------+--------+---------+
| _id | name | age | salary | address |
+-----+-----------+------+--------+---------+
| 1 | billgates | 60 | 1 | usa |
| 2 | larrypage | 45 | 2 | conf |
| 3 | job | 56 | NULL | NULL |
| 4 | 阳光沙滩 | 2 | NULL | NULL |
+-----+-----------+------+--------+---------+
4 rows in set (0.03 sec)

查询表中的数据（查）
select * from 表名
mysql> select * from person;
+-----+-----------+------+--------+---------+
| _id | name | age | salary | address |
+-----+-----------+------+--------+---------+
| 1 | billgates | 60 | 1 | usa |
+-----+-----------+------+--------+---------+
1 row in set (0.00 sec)

修改数据（改）对数据进行更新
update 表名 set 字段 = 值 条件（where）;
mysql> update person set salary = 3 where name = "job";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

当判断为空的时候，不能使用=null，要用is null;
mysql> update person set age = 22 where salary is null;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

删除表中的数据（删）
delete from 表名 条件
mysql> delete from person where name = "job";
Query OK, 1 row affected (0.06 sec)

mysql> select * from person;
+-----+-----------+------+--------+---------+
| _id | name | age | salary | address |
+-----+-----------+------+--------+---------+
| 1 | billgates | 60 | 1 | usa |
| 2 | larrypage | 45 | 2 | conf |
| 4 | 阳光沙滩 | 22 | NULL | NULL |
+-----+-----------+------+--------+---------+
3 rows in set (0.00 sec)

=================================================================
数据表中数据的查询详细
select * from 表名 [where 条件] ;
mysql> select * from test_select where age = 23;
+------+----------+------+--------+
| _id | name | age | salary |
+------+----------+------+--------+
| 1 | zhangsan | 23 | 3000 |
| 3 | wangwu | 23 | 4600 |
| 6 | zhuba | 23 | 5000 |
| 8 | zhushi | 23 | 9000 |
+------+----------+------+--------+
4 rows in set (0.02 sec)

关于条件
我们有 > ,< ,>=, <=, = ,<>,is null(不能写成=null),is not null
逻辑运算: and,or,not

有条件的查询：
比如說，这個年龄大于23的。
mysql> select * from test_select where age>23;
+------+--------+------+--------+
| _id | name | age | salary |
+------+--------+------+--------+
| 2 | lisi | 28 | 5000 |
| 5 | tianqi | 29 | 12000 |
| 7 | zhujiu | 26 | 5000 |
+------+--------+------+--------+
3 rows in set (0.00 sec)

我们要把这個薪水从5000到10000的挑选出來
mysql> select * from test_select where salary >= 5000 and salary <=10000;
+------+--------+------+--------+
| _id | name | age | salary |
+------+--------+------+--------+
| 2 | lisi | 28 | 5000 |
| 6 | zhuba | 23 | 5000 |
| 7 | zhujiu | 26 | 5000 |
| 8 | zhushi | 23 | 9000 |
+------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from test_select where salary between 5000 and 10000;
+------+--------+------+--------+
| _id | name | age | salary |
+------+--------+------+--------+
| 2 | lisi | 28 | 5000 |
| 6 | zhuba | 23 | 5000 |
| 7 | zhujiu | 26 | 5000 |
| 8 | zhushi | 23 | 9000 |
+------+--------+------+--------+
4 rows in set (0.00 sec)

between...and...前面一定要比后面要小。

我们要把这個年龄为20，26，28 的挑选出來

mysql> select * from test_select where age in(20,26,28);
+------+---------+------+--------+
| _id | name | age | salary |
+------+---------+------+--------+
| 2 | lisi | 28 | 5000 |
| 4 | zhaoliu | 20 | 3000 |
| 7 | zhujiu | 26 | 5000 |
+------+---------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from test_select where age = 20 or age = 26 or age = 28;
+------+---------+------+--------+
| _id | name | age | salary |
+------+---------+------+--------+
| 2 | lisi | 28 | 5000 |
| 4 | zhaoliu | 20 | 3000 |
| 7 | zhujiu | 26 | 5000 |
+------+---------+------+--------+
3 rows in set (0.00 sec)

select 列名 , 列名 ..... from 表名 [where 条件];
这种形式的话，其实，有些字段是我们不需要看到的，这個时候，我们就可以列出來
mysql> select name,age from test_select;
+----------+------+
| name | age |
+----------+------+
| zhangsan | 23 |
| lisi | 28 |
| wangwu | 23 |
| zhaoliu | 20 |
| tianqi | 29 |
| zhuba | 23 |
| zhujiu | 26 |
| zhushi | 23 |
+----------+------+
8 rows in set (0.00 sec)

select 列名 , 列名 ... from 表名 order by 列名 asc （升序）| desc（降序）
排序，有两种，一种是升序，一种是降序。

以年龄排序，升序；
mysql> select * from test_select order by age asc;
+------+----------+------+--------+
| _id | name | age | salary |
+------+----------+------+--------+
| 4 | zhaoliu | 20 | 3000 |
| 1 | zhangsan | 23 | 3000 |
| 3 | wangwu | 23 | 4600 |
| 6 | zhuba | 23 | 5000 |
| 8 | zhushi | 23 | 9000 |
| 7 | zhujiu | 26 | 5000 |
| 2 | lisi | 28 | 5000 |
| 5 | tianqi | 29 | 12000 |
+------+----------+------+--------+
8 rows in set (0.00 sec)

以工资排序，降序
mysql> select * from test_select order by salary desc;
+------+----------+------+--------+
| _id | name | age | salary |
+------+----------+------+--------+
| 5 | tianqi | 29 | 12000 |
| 8 | zhushi | 23 | 9000 |
| 2 | lisi | 28 | 5000 |
| 6 | zhuba | 23 | 5000 |
| 7 | zhujiu | 26 | 5000 |
| 3 | wangwu | 23 | 4600 |
| 1 | zhangsan | 23 | 3000 |
| 4 | zhaoliu | 20 | 3000 |
+------+----------+------+--------+
8 rows in set (0.00 sec)

select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;
别名，它的好处呢，就是我们可以根据自己的爱好來显示字段。
mysql> select name as "名字",age as "年龄" from test_select;
+----------+------+
| 名字 | 年龄 |
+----------+------+
| zhangsan | 23 |
| lisi | 28 |
| wangwu | 23 |
| zhaoliu | 20 |
| tianqi | 29 |
| zhuba | 23 |
| zhujiu | 26 |
| zhushi | 23 |
+----------+------+
8 rows in set (0.00 sec)

分组(group by)
其实分组就是以某一個字段为组，然后相同的就折叠起來。

mysql> select * from test_select group by age;
+------+----------+------+--------+
| _id | name | age | salary |
+------+----------+------+--------+
| 4 | zhaoliu | 20 | 3000 |
| 1 | zhangsan | 23 | 3000 |
| 7 | zhujiu | 26 | 5000 |
| 2 | lisi | 28 | 5000 |
| 5 | tianqi | 29 | 12000 |
+------+----------+------+--------+
5 rows in set (0.00 sec)

需求：
统计出各处年龄有多少人。

mysql> select age,count(age) from test_select group by age;
+------+------------+
| age | count(age) |
+------+------------+
| 20 | 1 |
| 23 | 4 |
| 26 | 1 |
| 28 | 1 |
| 29 | 1 |
+------+------------+
5 rows in set (0.00 sec)

查询里要补充一個内容，模糊查找
比如說， 我们要把这前缀为zhu的都找出來，不管后面有多少個字幕
mysql> select * from test_select where name like "zhu%";
+------+--------+------+--------+
| _id | name | age | salary |
+------+--------+------+--------+
| 6 | zhuba | 23 | 5000 |
| 7 | zhujiu | 26 | 5000 |
| 8 | zhushi | 23 | 9000 |
+------+--------+------+--------+
3 rows in set (0.00 sec)
其中这個%是一個通配符

这里面的下划线是個占位符，找出前面是zhu后面是三個字符的人。
mysql> select * from test_select where name like "zhu___";
+------+--------+------+--------+
| _id | name | age | salary |
+------+--------+------+--------+
| 7 | zhujiu | 26 | 5000 |
| 8 | zhushi | 23 | 9000 |
+------+--------+------+--------+
2 rows in set (0.00 sec)

数据库备份/恢复

1、备份
mysqldump -h服务器地址 -u用户名 -p(密码回车写) 数据库名称 > 数据备份的路径
C:\Users\Administrator>mysqldump -hlocalhost -uroot -p sunofbeaches > d:\my_suno
fbeaches_dump.sql
Enter password: ***

2、恢复
mysql -u 用户名 -p(密码回车写) 新数据名称（如果不同的数据库服务器，那么可以是相同的名字） < 数据路径

//查询练习
create table test_select(
_id int,
name varchar(50),
age int,
salary double
);

insert into test_select values(1,"zhangsan",23,3000);
insert into test_select values(2,"lisi",28,5000);
insert into test_select values(3,"wangwu",23,4600);
insert into test_select values(4,"zhaoliu",20,3000);
insert into test_select values(5,"tianqi",29,12000);
insert into test_select values(6,"zhuba",23,5000);
insert into test_select values(7,"zhujiu",26,5000);
insert into test_select values(8,"zhushi",23,9000);