Java: Getting started from scratch, learning notes & lt; SQL _Server & gt; (21)

After the SQL _Server database is installed, you need to start the corresponding database. Before starting, you need to ensure that the data service is enabled: in service-"after a database is created in the running state, do not modify the computer name. Otherwise, the database cannot find the local server name after the computer name is modified. If you want to connect to other databases in the same ip segment, you should configure the database ip support. You need to enable the TCP/IP settings of the server and client, you also need to set the port number. The default port number of SQL Server is 1433, mysql port is 3306, and oracle port is 1521. The configuration process is as follows: 1: 2: 3: after the configuration of the Server is complete, the client configuration also needs to ensure that after the TCP/IP protocol is enabled, you need to make your configuration take effect, you need to restart the SQL Server service. If you want to connect to another person's database, you need to know the conditions: IP address of the database to be connected, name of the database to be connected, and password of the database to be connected. If the database fails to be installed, or need to be reinstalled? 1. reinstall the system to reinstall the database (not recommended) 2. to reinstall the database, follow these steps: 2. Stop all services of the database. In the add and delete programs, uninstall sqlserver's programs. 2. After uninstalling, delete sqlserver in the physical disk file directory 2 uninstall service 2 Uninstall Registry Information, regedit open registry editor find HKEY_CURRENT_USER and HKEY_LOCAL_MACHINE, delete all -related registry information in the Software folder. 2. After deletion, restart your computer and reinstall and install User Logon: You can use windows identity verification, use a computer name and a blank password for logon, or use sqlserver identity verification, use the default sa user and the set password to log on and change the password. If the sa user password is forgotten, you can use windows identity verification to log on and then change the sa password after Logon: you can see that in the database folder, there are database and database snapshot folders. In the system database folder, after the database is installed, the built-in four database masters control the user database and SQL server operation Msdb to store jobs, alarms, and other information. The Model database Temp temporary database, temporary information is used to store temporary information. By default, the current user logs on to the master database. You can also create your own database, which is stored in the folder of the current user, if you create a database, the database you create belongs to and cannot be accessed by others unless authorized by others. Create your own database: one is to use commands. The other is to use a graphical interface to enter sqlcmd-H host-U sa-P 123 in the Start-> Run-enter Sqlcmd-H host-U sa-P window. The command window sqlcmd-U sa press ENTER prompt is displayed. enter the password 123 Sqlcmd window. the user opens a dos window, database operations are all named, and SQL language functions are extremely powerful without a graphical interface. However, due to clever design and simple language, only nine verbs are used to complete core functions, the following table shows SQL function verb Data Query SELECT data definition DDLCREATE, ALTER, DROP data manipulation DMLINSERT, UPDATE, DELETE permission control common operations of DCLGRANT, REVOKE database language: -- A database, database Name: ibmcreate database ibm; -- if you want to operate on a database, you need to use ibm before the operation; -- delete a database ibm- -The database in use cannot be deleted. drop database ibm -- create a table in the ibm database -- you should know the data type in the table -- The table includes: field name, field type, primary key, unique key, foreign key, empty or not, default value -- field name: the field names in a table cannot be repeated, the field names cannot be database keywords, and the field names cannot contain invalid characters-data types: int, varchar (), date, money (), and varchar2 () and float -- primary keys are used to indicate the unique keys of fields in the table. They are generally associated with foreign keys, when two tables are connected, the primary key of the operation is generally set to automatically increase. The unique key is used to indicate that the value inserted by the field in the table exists only in the table. The foreign key is used to associate the two tables. Whether the table is empty or not, is used to set whether a field can be blank -- the default value is that if you do not assign a value to a field, this field uses the default value -- create a table named tb_student -- sid INTEGER (int) primary key auto-increment -- sname primary varchar (n) cannot be blank -- sb Irth datatime can be empty -- sclass varchar can be empty. If the class is not set, the default value is java1000 class -- create table name in the syntax format of table creation (field type modification, field type modification ......); create table tb_student (sid int identity (1, 1), sname varchar (16) not null, sbirth datetime, sclass varchar (16) default 'java1000', primarykey (sid )); -- insert a statement into the created table -- insert statement format: insert into Table Name (field, field, field) values ('value', 'value', 'value ') -- if a table has a primary key, the insert into tb_student (sname, sbirth, sclas S) values ('admin', getdate (), 'java1002 '); -- add a field aage int to the tb_student table. At this time, all the values of the added fields are empty. alter table tb_studentadd sage int; -- delete a field alter table tb_studentdrop column sage in the table; select * from tb_student; -- query the information of a column in the table select sname from tb_student; -- query the record of a column by id select * from tb_student where sid = 3; -- Query select * from tb_student where sclass = 'java1000' for students in java1000; -- display desc (descending) asc (ascending) according to the results queried by the sid value) select * From tb_student orderby sid desc; select * from tb_student orderby sid asc; -- fuzzy query like % (matching all) _ (occupying symbol) -- query the select * from tb_student where sclasslike 'java % 'For all classes whose names start with java; -- query the select * from tb_student where sclasslike' % 8080' for the named class '; select * from tb_student where sclasslike '____ 1002'; select * from tb_student where sclasslike 'net ____ '; -- update the update table name set field name = 'Modified value ', field name = 'Modified value' where condition -- set the id of the record's scla Change ss to net1205update tb_student set sclass = 'net1205' where sid = 5; -- can I change all the fields in a record to the primary key value at one time? Update tb_student set sname = 'ibm ', sbirth = getdate (), sclass = 'aaa' where sid = 1; -- delete from table name where condition indicates that the table's qualified records are deleted -- delete the record with sid = 8 from tb_studentwhere sid = 8; -- delete multiple records with the deletion id> 5 delete from tb_studentwhere sid> = 3; -- clear the data table in the table that still contains the knowledge data without truncate table tb_student; -- delete a table, drop table tb_student; select * from tb_student; common function indexes of SQL Server -- create a table tb_student (sid name, score, sclass, startschool ); create table tb_student (sid int identity (1, 1), sname varchar (32) not null, score int default 0, sclass varchar (32), startschool datetime, primary key (sid),); -- insert an insert into tb_student (sname, score, sclass, startschool) values ('els', 80, 'tizy ', getdate (); insert into tb_student (sname, score, sclass, startschool) values ('bs ', 90, 'natural disaster', getdate (); insert into tb_student (sname, score, sclass, startschool) values ('cs ', 30, 'near Wei', getdate (); insert into tb_student (sname, score, sclass, startschool) values ('as', 40, 'near Wei ', getdate (); -- aggregate function -- count is used to retrieve the number of records in the table select count (*) from tb_student; select count (*) as 'number of data entries 'from tb_student; select count (1) from; -- sum summation function sums the values of a column and select sum (score) as 'total score 'from tb_student; -- avg calculates the average value of a column. select avg (score) as 'average score' from tb_student; -- max calculates the maximum value for the value of a column. select max (score) as 'highest point' from tb_student; -- min calculates the minimum value for the value of a column. select min (score) as 'minute sub' from tb_student; -- Date and Time Function -- Obtain the system time select getdate () as 'time'; -- select DATEPART (yyyy, getdate () as 'Year' for some events that have obtained the system time; select DATEPART (MM, getdate () as 'month '; select DATEPART (hh, getdate () as 'hour'; select DATEPART (minute, getdate () as 'mine '; -- used to change the retrieved system time select DATEADD (yyyy,-20, getdate () as '20 years ago '; select DATEADD (dd,-1, getdate ()) as 'Yesterday '; -- DATEDIFFselect DATEDIFF (dd, '2017-1-28', '2017-2-28 '); -- how many days have you been born to select DATEDIFF (dd, '2014-2-1', '2014-2-28 ') as 'Day of birth'; -- character function -- CHAR returns the ASSIC code value of the corresponding character select char (68 ); -- replace ASCII with the number select ASCII ('A'); -- LEET ('', N); select LEFT ('ibm ', 3) as '123 '; select RIGHT ('ibm ', 3) -- returns the length of the specified string (string trailing space is not calculated) select len ('ibm'); select len ('I B m '); -- string truncation (indicating two string lengths starting from the third position, including the start position) select substring ('ibm ',); -- the usefulness of the index: -- index creation is generally used to improve data query efficiency. -- Set Data Validity -- unique index: set one or more fields in the read table to a unique index, the field cannot contain duplicate data. The syntax format for creating a unique index is -- create unique index name on -- table name (field, field ); -- create uniqueindex uname on tb_student (sname ); -- If you want to create a unique index for multiple columns at a time, you only need to add the index after sname to create uniqueindex uname on tb_student (sname, sclass ); -- after a unique index is created, the index names of the columns with the same value cannot be inserted repeatedly. The insert into tb_student (sname, score, sclass, startschool) values ('els', 80, 'neutral shift 1', getdate (); delete from tb_studentwhere sid = 5; -- clustered index: the order of clustering indexes is the same as the physical order in the table -- clustering indexes are generally created on the most frequently queried column, however, changing the record in a column will change the record Sequence Value -- clustered indexes are generally created on the primary key -- a table can only have one clustered index -- the syntax format for setting up a clustered Index -- create CLUSTERED index Cluster index name on table name (primary key column name ); create clusteredindex onlyindex on tb_student (sid); -- delete an index to delete the index of the specified table -- drop index table name. index name -- delete the newly created clustered index drop index tb_student.onlyindex; -- the index cannot be modified. Therefore, you need to modify the index of a table by deleting the index and then creating the corresponding index.