First, the database login
- Server name:. Contact this machine
- Authentication: Windows Authentication (Administrator rights) and SQL Server Authentication
- Login name: SA (default login name)
II. Database Conventions
- The names of databases, libraries, and tables are all in English nouns, with the first letter capitalized
- Create TABLE, function cannot cross
- String must be single quotation marks
Third, the establishment of the table
- Primary key: Unique identification of a row of data
- Data type:
- int integer type
- Float decimal Type
- Bit Boolean type
- Datetime
- String type
- char (10) can only put 5 Chinese, fixed-length, if only one byte, followed by 9 spaces (a Chinese 2 bytes
- NCHAR (10) Put 10 Chinese, fixed length
- varchar (10) Put 5 Chinese, grow longer, if only one byte, just a byte
- nvarchar (10) put 10 Chinese or 10 characters, grow longer
- Building a table from a statement building database
Create database library (database name)
CREATE table Users (table name)
UserName nvarchar () NOT NULL
Userpwd nvarchar () NOT NULL
............
Iv. Query Statements
Single-Table Query
- SELECT * FROM Userinfor--query all data in Userinfor table
- Select Username,qq from Useinfor--query USERNAME,QQ data in userinfor table
- SELECT * from userinfor where sex= ' man '--query all boys in Userinfor table
- SELECT * from userinfor where sex= ' man ' and Place= ' Wuhan '--Find all the boys in the Userinfor table in Wuhan
- SELECT * from userinfor where sex= ' man ' and (place= ' Wuhan ' or place= ' Hangzhou ')--Find all the boys in Wuhan or Hangzhou in the Userinfor table
- Select COUNT (*) as Pcount from Userinfor where place= ' Wuhan '--Query the number of people who are located in Wuhan userinfor table and alias Pcount
- SELECT * from Userinfor where age>=25 and age<=30 or select * from userinfor where age between and 30-- Query userinfor in the table for users aged between 25 and 30
- SELECT * from Userinfor ORDER BY age ASC--Sort by ages from small to large ASC default can not write select * from Userinfor ORDER BY age DESC--sort from oldest to Small
- Select top userinfor ORDER BY age DESC--query for users up to three years old and sorted from large to small
- Select Top 3 username,pwd,qq from Userinfor ORDER BY age DESC--Query the user name, password, and QQ of the first three users and sort by large to small
- Select COUNT (*), place from Userinfor Group by place-Displays the number of users per region via place grouping
- Select COUNT (*) as Pcount,place from Userinfor Group by places having Count (*) >1--query group information that is greater than 1 for users of the same location after place grouping
- Fuzzy query
- SELECT * from userinfor where UserName like ' zh% '--queries users that begin with the user name zh letter
- SELECT * from userinfor where UserName like '%NP '--queries users ending with a user name NP letter
- SELECT * from userinfor where UserName like '%n% '--queries users with n letters in user name
- Select min from userinfor--Query the minimum age value
- Select Max (age) from userinfor--query for the oldest value
- Select sum from userinfor--Query the total value of the user's age
- Select avg from Userinfor--query the average age of the user
- SELECT * from Userinfor where ages (select AVG (age) from userinfor)--queries all users older than the average age
- Select distinct UserName from userinfor--query for username without duplicate name
Database SQL Server
