SQL-Basics
-- Database: A warehouse where data is stored,
-- Database
--Data table
--column name (field-field)
--data(value)
--Create table
--(Defining data types when creating tables in sqlite database does not limit the role of data types.)
--Because sqlite's data has a weak number of data types, it will automatically identify and add data based on the data entered by the user
--to the database table, like most databases, sqlite is case-insensitive )
-- Five types of data in the sqlite database: 1, integer 2, floating-point (decimal) - real
-- 3, text-text 4, binary file (picture, sound is typical)-blob 5, null type-null
--
--
--CREATE TABLE table name
--(
--column name1 data type,
--column name2 data type,
--column name3 data type,
--....
--)
-- Fields are directly separated from fields with ",", and some attributes of the field are separated by spaces.
-------------------------------------------------------------------------------------------------
--Add table (create table, table name: person)
create table person(
id integer not null,
name text, --name.
age integer, --age.
height real -- height.
)
--------------------------------------------------------------------------------------------------
-- Delete table
drop table person
--------------------------------------------------------------------------------------------------
--Operations on databases are nothing more than CRUD
-- C: Add (increase) - Create
-- D: Delete (delete) - Delete
-- R: Read (check) -Retrieve
-- U: Update (change) - Update
--------------------------------------------------------------------------------------------------
--check: Get all the data in the table
select * from person
---------------------------------------------------------------------------------------------------
--Add: Add data -- The "text class" type should be enclosed in double quotation marks ("") or single quotes ('').
--insert into table values (value 1, value 2, value 3...)
-- or specify the content to be added
-- insert into table(Field 1, Field 2, ...) values (Value 1, Value 2, Value 3...)
-- Note: If the field type is defined as numeric, if the text type is inserted, the error will not be reported, but it cannot be inserted, and the displayed value may be 0
insert into person values (20150102, 'Lao Wang', 68,170.3)
insert into person(id, name) values (20150101, 'Zhang San')
----------------------------------------------------------------------------------------------------
--delete: delete data (cannot delete a certain data)
--format: delete from table where . . .
--Like MySQL can also be used: delete * from table where ... (More "*" than sqlite)
-- Be cautious when deleting the operation, remember to add condition + add condition + add condition. Otherwise, the data for the entire table will be deleted
delete from person where id=20150102
----------------------------------------------------------------------------------------------------
-- Change: Modify the data
-- update table set column name = new value, column name = new value... where column name = a value
-- Remember the modified conditions, if you want to change all the values of the changed fields (column names) in this table to be the same, you can leave no conditions
update person set name='Xiao Wang', age=8 where id=20150102
-----------------------------------------------------------------------------------------------------
-- Check: Query data (key points + difficulties)
-- format select * from table
-- Query all data
select * from person
--------------------------------------------------------------------------------------------------------
-- Make sure that there are no duplicates in the results (e.g. ID)
select distinct id from person --distinct different, unique.
--------------------------------------------------------------------------------------------------------
-- Fuzzy query: %: all characters; _Single character
select * from person where name like'old_' --like Search for a specified pattern. --wehere where to choose.
--------------------------------------------------------------------------------------------------------
-- Sort: Descending: order by field desc, ASC ascending: order by field desc
select * from person order by age desc --order by order
--------------------------------------------------------------------------------------------------------
-- Statistics: Total count(*); max (field); min (field); avg average (field)
select max(age) from person
--------------------------------------------------------------------------------------------------------
-- Select the first few items: limit start, quantity; Don't put parentheses, start with 0, usually placed at the end of the statement, and also use the top value
-- eg: Get the first three
select * from person limit 0,3 --limit is the limit. --eg such as.
--------------------------------------------------------------------------------------------------------
-- Grouping: group by field
-- eg: Count the number of people with different IDs; The same fields are divided into a group
select count(*) from person group by id --group by group by.
--------------------------------------------------------------------------------------------------------
-- Compare >, <, =, !=
select * from person where id=2015102
--------------------------------------------------------------------------------------------------------
--At.. Between : between value 1 and value 2, the data range between the two values. These values can be numeric, text, or date
select * from person where id between 201501 and 20151110 --between in ... between
--------------------------------------------------------------------------------------------------------
-- Contains : field in(value 1, value 2...). )
select * from person where id in(1001,1002,1003,20150101) --in inside
-------------------------------------------------------------------------------------------------------- |
[Copy link]
Posted on 10/4/2014 3:23:00 PM
|
|
|
Landlord|
Posted on 11/28/2021 10:16:47 PM
|
Posted on 10/5/2014 10:17:37 PM
|
